2019年10月30日 星期三

Buffalo Linkstation resources

原來的 buffal-nas central 已經沒了,
backup site 為 https://buffalonas.miraheze.org/wiki/Main_Page

要打開 telnet , 可以裝 acp_commander gui 版本,
https://www.gry.ch/Java/styled/

會自動掃 LAN 的 linkstation,
輸入 password 之後, 可以執行欄填 "telnetd", 然後按 "Go",
就可以將 telnetd 執行起來.

當 raid 硬碟有問題, 機器會自動 shutdown,
可以透過 "auto power mode" -> manual
"sleep timer" -> disable
"Disk management - RAID setup - disable "Auto shutdown for RAID errors" -> disable


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)