原來的 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月30日 星期三
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()
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()
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()
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
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)
訂閱:
文章 (Atom)