2019年10月18日 星期五

update average

from collections import deque
from datetime import date
import sqlite3
import math
import sys

databasefile='d:/archive/stock/twstock.db'
dblastdate='20160901'

def check_workingday(conn, orddate):
    cc=conn.execute('select strdate from datalog where orddate='+str(orddate))
    res=cc.fetchone()
    if res!=None:
        return True
    return False

def get_price(conn, orddate, st_id):  # return price, if no value, return -1.0, if pass database limit return 0.0
    if (orddate<=dblastorddate):
        return 0.0
    cc=conn.execute('select close from stock where orddate is '+str(orddate)+' and stock_id is '+st_id)
    res=cc.fetchone()
    if res==None:
        return -1.0
    if res[0]==None:
        #print('get a NaN in orddate', orddate,'stock id',st_id)
        return -1.0
    return res[0]

def cal_averages(dque):
    sum=0.0
    for i in range(0,61):
        if i==5:     # 5 days average
            avg5=sum/5.0
        if i==10:    # 10 days average
            avg10=sum/10.0
        if i==20:    # 20 days average
            avg20=sum/20.0
        if i==60:    # 60 days average
            avg60=sum/60.0
            continue
        sum=sum+dque[i][1]
    return (avg5, avg10, avg20, avg60)

def cal_deviation(dque, avg20):
    sum=0.0
    for i in range(0, 20):
        diff = dque[i][1]-avg20
        sum=sum+abs(diff*diff)
    return math.sqrt(sum/20.0)

argv1='20190820'
argv2='20190820'
#stock_id='2317'
lastdate=date.fromisoformat(dblastdate[:4]+'-'+dblastdate[4:6]+'-'+dblastdate[6:8])
d1=date.fromisoformat(argv1[:4]+'-'+argv1[4:6]+'-'+argv1[6:8])
d2=date.fromisoformat(argv2[:4]+'-'+argv2[4:6]+'-'+argv2[6:8])

dblastorddate=lastdate.toordinal()
d1ord=d1.toordinal()
d2ord=d2.toordinal()

dbconn=sqlite3.connect(databasefile)
start_day=d2ord

# find the first working day
while (check_workingday(dbconn, start_day)==False):
    start_day=start_day-1
   
if (start_day<d1ord):
    print('no working days within the range')
    sys.exit()

cc=dbconn.execute('select stock_id from stock where orddate is '+str(d2ord))
st_idlist=cc.fetchall()

for st_id in st_idlist:
   
    cur_day=start_day
    dque=deque()
   
    # fill up 60 working days' data
    for j in range(0,60):
        price=-1.0
        while (price<0.0):
            price=get_price(dbconn, cur_day, st_id[0])
            if (price<0.0):
                cur_day=cur_day-1
        dque.append((cur_day, price))
        cur_day=cur_day-1

    avgs=cal_averages(dque)  # return avg5, avg10, avg20, avg60
    deviation=cal_deviation(dque, avgs[2])
#    print(dque)
    print(st_id, cur_day, avgs, deviation)

    while (dque[0][0]> d1ord):     # if the queue top day is still larger than loop end
    # shift one day
        dque.popleft()
        price=-1.0
        while (price<0.0):
            price=get_price(dbconn, cur_day, st_id[0])
            if (price<0.0):
                cur_day=cur_day-1
        dque.append((cur_day, price))
        cur_day=cur_day-1
        avgs=cal_averages(dque)
        deviation=cal_deviation(dque, avgs[2])
        print(dque)
        print(st_id, cur_day, avgs, deviation)


dbconn.close()

2019年10月9日 星期三

create database file

import sqlite3

conn = sqlite3.connect('twstock.db')

c = conn.cursor()

c.execute("CREATE TABLE stock ( \
    stock_id     text, \
    orddate      int, \
    name         text, \
    shares       int, \
    transactions int, \
    amount       int, \
    open         real, \
    high         real, \
    low          real, \
    close        real, \
    diff         real, \
    lastbuy      real, \
    lastbuys     int, \
    lastsell     real, \
    lastsells    int, \
    eps          real, \
    allshares    int, \
    deviation    real, \
    avg_5        real, \
    avg_10       real, \
    avg_20       real, \
    avg_60       real, \
    PRIMARY KEY (stock_id, orddate) \
    )")

conn.commit()

c.execute("CREATE TABLE datalog (\
    orddate int PRIMARY KEY,\
    twse_update int,\
    tpex_update int\
    )")
conn.commit()

conn.close()

update saved data into database

from datetime import date
import time
import pandas as pd
from io import StringIO
import sqlite3

def update_twse_db(orddate):

    dd=date.fromordinal(orddate)
    datestr=dd.strftime("%Y%m%d")

    f=open("d:/archive/stock/twse/"+datestr, "rt", encoding="utf8")
    print("fetching ", datestr)
    content=f.read()
    f.close()

    # if content is empty, the stock market is not open
    if content=='':
      return None

    # clear all '=' in some lines
    content=content.replace('=','')

    # filter all lines without 10 fields of data
    lines=content.split('\n')
    lines=list(filter(lambda l:len(l.split('",')) > 10, lines))

    # join all lines with carriage return
    content = "\n".join(lines) 

    # use pd to read content as csv file
    df=pd.read_csv(StringIO(content))

 

    # remove all element str and remove ','
    df = df.astype(str)
    df = df.apply(lambda s: s.str.replace(',', ''))

    # add 'date' field to the sheet
    df['orddate']=pd.to_numeric(orddate) 

    df = df.rename(columns={'證券代號':'stock_id'})
    df = df.rename(columns={'證券名稱':'name'})
    df = df.rename(columns={'成交股數':'shares'})
    df = df.rename(columns={'成交筆數':'transactions'})
    df = df.rename(columns={'成交金額':'amount'})
    df = df.rename(columns={'開盤價':'open'})
    df = df.rename(columns={'最高價':'high'})
    df = df.rename(columns={'最低價':'low'})
    df = df.rename(columns={'收盤價':'close'})
    df = df.rename(columns={'漲跌(+/-)':'sign'})
    df = df.rename(columns={'漲跌價差':'diff'})
    df = df.rename(columns={'最後揭示買價':'lastbuy'})
    df = df.rename(columns={'最後揭示買量':'lastbuys'})
    df = df.rename(columns={'最後揭示賣價':'lastsell'})
    df = df.rename(columns={'最後揭示賣量':'lastsells'})
    df = df.rename(columns={'本益比':'eps'})


    # drop stock id not equal to 4 chars
    df = df[df.stock_id.str.len()==4]
   
    # set index as stock_id and date
    df = df.set_index(['stock_id', 'orddate']) 
    df = df.apply(lambda ss:pd.to_numeric(ss, errors='coerce') if ((ss.name!='name') and (ss.name!='sign')) else ss)

    # remove the last empty field

    # df = df[df.columns[df.isnull().all() == False]]
    df = df.drop(columns=['Unnamed: 16','sign'])

    return df



def update_tpex_db(orddate):

    dd=date.fromordinal(orddate)
    datestr=dd.strftime("%Y%m%d")

    f=open("d:/archive/stock/tpex/"+datestr, "rt", encoding="utf8")

    print("fetching ", datestr)

    content=f.read()
    f.close()

    # clear all '=' in some lines
    content=content.replace('=','')

    # filter all lines without 10 fields of data
    lines=content.split('\n')
    lines=list(filter(lambda l:len(l.split(',')) > 10, lines))

    # join all lines with carriage return
    content = "\n".join(lines)

    # if content is less than 100 lines, the stock is not open
    if content.count('\n')<100:
      return None

    # use pd to read content as csv file
    df=pd.read_csv(StringIO(content))

    # remove all element str and remove ','
    df = df.astype(str)
    df = df.apply(lambda s: s.str.replace(',', ''))

    # add 'date' field to the sheet
    df['orddate']=pd.to_numeric(orddate)

    # remove all space char in column name
    for i in df.columns:
        df = df.rename(columns={i : i.replace(' ', '')})

    df = df.rename(columns={'代號':'stock_id'})
    df = df.rename(columns={'名稱':'name'})
    df = df.rename(columns={'收盤':'close'})
    df = df.rename(columns={'漲跌':'skip_1'})
    df = df.rename(columns={'開盤':'open'})
    df = df.rename(columns={'最高':'high'})
    df = df.rename(columns={'最低':'low'})
    df = df.rename(columns={'均價':'skip_2'})
    df = df.rename(columns={'成交股數':'shares'})
    df = df.rename(columns={'成交金額(元)':'amount'})
    df = df.rename(columns={'成交筆數':'transactions'})
    df = df.rename(columns={'最後買價':'lastbuy'})
    df = df.rename(columns={'最後賣價':'lastsell'})
    df = df.rename(columns={'發行股數':'allshares'})
    df = df.rename(columns={'次日參考價':'skip_3'})
    df = df.rename(columns={'次日漲停價':'skip_4'})
    df = df.rename(columns={'次日跌停價':'skip_5'})

    # drop stock id not equal to 4 chars (including '代號')
    df = df[df.stock_id.str.len()==4]

    # set index as stock_id and date
    df = df.set_index(['stock_id', 'orddate'])

    # convert all field to numbers except name
    df = df.apply(lambda s:pd.to_numeric(s, errors='coerce') if (s.name!='name') else s)

    # drop all columns to be skipped
    df = df.drop(columns=['skip_1','skip_2','skip_3','skip_4','skip_5'])

    return df

argv1='20190703'
argv2='20190731'

d1=date.fromisoformat(argv1[:4]+'-'+argv1[4:6]+'-'+argv1[6:8])
d2=date.fromisoformat(argv2[:4]+'-'+argv2[4:6]+'-'+argv2[6:8])

d1ord=d1.toordinal()
d2ord=d2.toordinal()

conn=sqlite3.connect('d:/archive/stock/twstock.db')

for i in range(d1ord, d2ord+1):

    # test if datalog contains orddate data
    cc = conn.execute("select twse_update from datalog where orddate="+str(i))
    res = cc.fetchone()

    if res!=None :
        continue

    # this orddate data is not updated yet, go on update the database
    df=update_twse_db(i)

    # if the content is empty, the day is off, skip
    if (type(df)!=pd.core.frame.DataFrame):
        continue

    df.to_sql('stock', conn, if_exists='append')
   
    # updata datalog for orddate
    cc = conn.execute("insert into datalog (orddate, twse_update) values ("+str(i)+",1)")   
    conn.commit()
   
    df=update_tpex_db(i)

    if (type(df)!=pd.core.frame.DataFrame):
        continue

    df.to_sql('stock', conn, if_exists='append')
   
    # update datalog for orddate
    cc = conn.execute("update datalog set tpex_update = 1 where orddate="+str(i))
    conn.commit()

conn.close()

2019年10月7日 星期一

crawl twse and tpex data into files

crawl_save.py


from datetime import date
import requests
import dill

def crawl_date(ordinaldate):
    dd = date.fromordinal(ordinaldate)
    datestr = dd.strftime("%Y%m%d")
    fetchstr = 'http://www.twse.com.tw/exchangeReport/MI_INDEX?response=csv&date=' + datestr + '&type=ALLBUT0999'
    print("fetching",fetchstr)
    r = requests.post(fetchstr)
    f = open("twse/"+datestr, "wt", encoding="utf8", newline='\n')
    f.write(r.text)
    f.close()
    datestr2 = dd.strftime("%Y/%m/%d")
    datestr2 = datestr2.replace(datestr2[0:4], str(int(datestr2[0:4])-1911))
    fetchstr = 'http://www.tpex.org.tw/web/stock/aftertrading/daily_close_quotes/stk_quote_download.php?l=zh-tw&d=' + datestr2 + '&s=0,asc,0'
    print("fetching tpex ", fetchstr)
    r = requests.post(fetchstr)
    f = open("tpex/"+datestr, "wt", encoding="utf8", newline='\n')
    f.write(r.text)
    f.close()
    return 0

crawl_test.py
from datetime import date
import crawl_save
import time

if __name__ == "__main__":
    import sys
    if ((not sys.argv[1]) or (not sys.argv[2])):
        print("need 2 parameters\n");
        sys.exit(0)
    d1=date.fromisoformat(sys.argv[1][:4]+'-'+sys.argv[1][4:6]+'-'+sys.argv[1][6:8])
    d2=date.fromisoformat(sys.argv[2][:4]+'-'+sys.argv[2][4:6]+'-'+sys.argv[2][6:8])
    d1ord=d1.toordinal()
    d2ord=d2.toordinal()
    for dd in range(d1ord, d2ord+1):
        crawl_save.crawl_date(dd)
    time.sleep(10)

2019年9月10日 星期二

setup items for retrieving twse info

key knowledge :
- 2 URLs for stock info
- determine which day is off
- determine database fields
- how to determine boolean tunnel

modules :
- http engine
- stock info parsing engine
- daily update engine
- fields data generation

twse URL:
https://www.twse.com.tw/exchangeReport/MI_INDEX?response=csv&date=20190924&type=ALLBUT0999

tpex URL:
http://www.tpex.org.tw/web/stock/aftertrading/daily_close_quotes/stk_quote_download.php?l=zh-tw&d=107/02/09&s=0,asc,0


database fields :
from twse :

stock_id
orddate
name             object
shares           int64
transactions      int64
amount            int64
open            float64
high            float64
low             float64
close           float64
diff            float64
lastbuy         float64
lastbuys          int64
lastsell        float64
lastsells         int64
eps             float64
allshares         int64

derived data :
deviation            float64
avg_5                 float64
avg_10               float64
avg_20               float64
avg_60               float64



2016年12月21日 星期三

utorrent 換電腦完整轉移

動物機常常遇到系統故障,
需要換硬碟/升級系統,
但是要保留原來所有種子的狀態,
剛剛試過, 下列步驟就可以完整保留所有下載狀態,

1. 所有檔案掛載的磁碟代號, 都要保持一樣,
    例如你原來 D: 及 E: 兩顆硬碟掛載來下載檔案,
    這兩顆硬碟的磁碟代號及所有檔案, 都要保持一樣

2. 把原來硬碟中的 "c:\Program Files(x86)\utorrent"
    copy 到你新的硬碟中, 裏頭有你原來 utorrent 執行程式

3. "c:\使用者\使用者名稱\AppData\Roaming\utorrent"
    目錄也要 copy 到新硬碟中, 裏頭包含你所有分享還有
    正在下載的種子, 及相關的資料, 但是 AppData 目錄是
    隱藏的, 所以先到 "c:\使用者\使用者名稱"目錄底下,

 選擇"資料夾和搜尋選項"之後, 把"顯示隱藏的檔案、資料夾及磁碟機"
選項打開, 就可以看到 AppData 資料夾了, 同樣在新硬碟上, 也需要
打開隱藏資料夾, 把目錄 copy 過去
    XP 或者 Win 10 最前面的名稱, 也許是 "Documents and Settings"
   或其他目錄名稱, 請自行調整目錄名稱, XP 的目錄路徑,
   AppData 底下就直接是 utorrent 目錄, 沒有 Roaming 這個目錄

4.在新的硬碟及系統中, 直接到 "c:\Program Files(x86)\utorrent"
   底下, 直接執行 utorrent.exe, 會重新安裝 utorrent, 在目錄選項中
   , 記得選擇指定到剛剛 "c:\使用者\使用者名稱\AppData\Roaming\utorrent"
  中, 當安裝好之後, 你執行 utorrent, 就可以看到所有之前的
  分享與下載一半的檔案, 都完整的出現了.

2016年7月4日 星期一

Using ffmpeg to transcode DVD KTV files into VCD format

The main issue is about to convert DVD dual stereo audio channels
into one stereo audio in VCD format.

A good reference can be found here .

The command line parameters for the transcoding is :

ffmpeg -i input.mpg -target ntsc-vcd -filter_complex
"[0:a:0][0:a:1]amerge=inputs=2,pan=stereo|c0<c0+c1|c1<c2+c3" output.mpg

However, the mpeg1 codec in ffmpeg is poor. The video quality
is not good enough in my opinion.