要讓 X370 上的 raid 正常運作, 有幾個關鍵問題要突破 :
1. 要讓 BIOS 在完全的 UEFI mode, 打開 RAID mode 之後, UEFI RAID utility 才能使用,
用來 create RAID
2. BIOS 中要將 SATA 設定為 RAID mode, 進入 Win10 之後, 安裝 AMD RAID driver
才能成功(問題是, 開機碟還沒裝 RAID driver 之前, 直接用 RAID mode 開機,
會無法開機, 變成雞生蛋蛋生雞的問題)
- MSI X370 的 BIOS, 需要將 windows OS configuration 改成 UEFI(或者非 CSM mode),
或者是 enable WIN10 WHQL
- 為了要讓 BIOS 跑在純 UEFI mode, 需要
- VGA card 要 support GOP
- 開機碟要是 GPT format
- 為了要讓 HDD 原本是 MBR format, 可以在 win10 開機的狀態, 執行
c:/windows/system32/mbr2gpt.exe /convert /disk:0 /allowfullos
程式會將 c: 的 partition size 縮小後, 再 create 一個小 partition 當 GPT info partition
- 如果系統已經有 4 個 partitions 了, mbr2gpt.exe 會有
" disk layout validation failed for disk 0" 的錯誤, 則要先將 primary partition
降到 3 個或以下, 才能成功轉換 mbr->gpt
- 轉換完 mbr->gpt 之後, 重新開機就可以在 BIOS 中 windows OS configuration
中, 將模式改成 UEFI
- 重新開機之後, 接下來要將 SATA 改成 RAID mode, BIOS menu 就會出現 RAID utility,
就可以先 configure 好 RAID 的設定
- 因為主機板的設定, SATA 設定為 RAID mode, 會讓開機碟也進入 RAID mode,
但是如果原來 AHCI Win10 沒有灌 RAID driver, 會無法正確開機,
所以為了要灌 RAID driver, 可以 :
1. 將開機 HDD 改接到 PCI-e 的 SATA card, BIOS 中的 Windows OS configuration
可能要改成 CSM mode, 才能讓 PCI-e card 上的 BIOS 作用, 才能開機
2. 開機 HDD 改接成 USB HDD 開機(這個方法原理上應該 OK, 但我沒實際試)
- 所以想辦法讓開機 HDD 用別的 SATA interface 開機(or USB), 主機板的 SATA
只有接 RAID HDD, 然後 BIOS 中 SATA mode 設定為 RAID, Windows OS configuration
是在 UEFI or CSM 無所謂
- 重新開機進入 Win 10 之後, 硬體管理員的存放控制器(storage controller)應該有 3 個
不明的 RAID controller, 這種狀態就可以正確安裝 RAID driver
- MSI 網站可以下載的是 :
1. chipset driver
2. RAID driver(這是讓安裝 Win10 開始時手動載入第三方 driver 用的 RAID driver)
AMD 網站可以下載的是 :
1. chipset driver
2. RAID software
3. RAID driver(這也是給安裝 Win10 開始時用的 RAID driver)
可以下載 MSI 網站的 chipset driver 安裝好之後, 重開機, 然後再安裝 AMD 網站的
RAID software, 就會將 RAID driver 與 RAIDXpert2 管理程式都安裝好,
如果 BIOS RAID mode 沒打開, RAID software 無法正常安裝
- 重開機後, 打開 RAIDXpert2 軟體, 就可以開始管理 RAID
- 關機後, 可以將開機 HDD 移回主機板的 SATA, 並且將 BIOS windows OS configuration
模式設定為 UEFI, 在移回之前最好先將 Win10 改回安全模式,
2019年11月24日 星期日
2019年11月21日 星期四
data mining project GUI
'''
Data Mining Project :
'''
#======================
# imports
#======================
import tkinter as tk
from tkinter import ttk
from tkinter import scrolledtext
from tkinter import Menu
from tkinter import messagebox as msg
from tkinter import Spinbox
from time import sleep # careful - this can freeze the GUI
from tkcalendar import Calendar, DateEntry
from tkinter import filedialog as fd
import os
import sys
from datetime import date
import time
import requests
import dill
import pandas as pd
from io import StringIO
import sqlite3
import math
from collections import deque
# should use auto calculation to get the last stock date
dblastdate='20160901'
# Create instance
win = tk.Tk()
# Add a title
win.title("Data Mining Project")
tabControl = ttk.Notebook(win) # Create Tab Control
tab1 = ttk.Frame(tabControl) # Create a tab
tabControl.add(tab1, text='database maintenance') # Add the tab
tab2 = ttk.Frame(tabControl) # Add a second tab
tabControl.add(tab2, text='mining target') # Make second tab visible
tabControl.pack(expand=1, fill="both") # Pack to make visible
#
# configuration area
#
area_config = ttk.LabelFrame(tab1, text=' configure ')
area_config.grid(column=0, row=0, padx=8, pady=4)
ttk.Label(area_config, text="Start Date:").grid(column=0, row=0, sticky=tk.W)
start_date = tk.StringVar()
start_Entry = DateEntry(area_config, font='Arial 12', width=12, background='darkblue',
foreground='white', borderwidth=2, date_pattern='y-mm-dd', textvariable=start_date)
start_Entry.grid(column=1, row=0, sticky=tk.W)
ttk.Label(area_config, text="End Date:").grid(column=0, row=1, sticky=tk.W)
end_date = tk.StringVar()
end_Entry = DateEntry(area_config, font='Arial 12', width=12, background='darkblue',
foreground='white', borderwidth=2, date_pattern='y-mm-dd', textvariable=end_date)
end_Entry.grid(column=1, row=1, sticky=tk.W)
def getDirName():
fDir = os.path.dirname('__file__')
fName = fd.askdirectory(parent=win, title='choose stock data dir', initialdir=fDir)
filedir.set(fName)
def getDatabaseFileName():
fDir = os.path.dirname('__file__')
fName = fd.askopenfilename(parent=win, title='select database file', initialdir=fDir)
dbfilename.set(fName)
def checkDataDir():
if not os.path.exists(filedir.get()+'/twse'):
check_datadir_l.configure(text='directory is not created')
return
if not os.path.exists(filedir.get()+'/tpex'):
check_datadir_l.configure(text='directory is not created')
return
filelist=os.listdir(filedir.get()+'/twse')
total_twse_item=len(filelist)
if (total_twse_item==0):
check_datadir_l.configure(text='directory is empty')
return
filelist.sort()
firstord=date.fromisoformat(filelist[0][:4]+'-'+filelist[0][4:6]+'-'+filelist[0][6:8]).toordinal()
lastord=date.fromisoformat(filelist[-1][:4]+'-'+filelist[-1][4:6]+'-'+filelist[-1][6:8]).toordinal()
if (firstord==0):
check_datadir_l.configure(text='illegal file name in twse directory')
return
if ((lastord-firstord+1)==total_twse_item):
check_datadir_l.configure(text='file complete '+date.fromordinal(firstord).isoformat()+ \
' to '+date.fromordinal(lastord).isoformat())
return
check_datadir_l.configure(text=date.fromordinal(firstord).isoformat()+' to '+ \
date.fromordinal(lastord).isoformat()+' short of '+ \
str(total_twse_item-lastord+firstord-1)+' files')
return
def checkDatabase():
if not os.path.exists(dbfilename.get()):
check_db_l.configure(text='database file has not created yet')
return
# check data on 2330
conn=sqlite3.connect('d:/archive/stock/twstock.db')
cc=conn.execute('select orddate from datalog')
ordlist=cc.fetchall()
ordlist.sort()
check_db_l.configure(text='database from '+date.fromordinal(ordlist[0][0]).isoformat()+ \
' to '+date.fromordinal(ordlist[-1][0]).isoformat())
conn.close()
def checkAVGstatus():
if not os.path.exists(dbfilename.get()):
check_avg_l.configure(text='database file has not created yet')
return
# check data on 2330
conn=sqlite3.connect('d:/archive/stock/twstock.db')
cc=conn.execute('select orddate from stock where stock_id is 2330 and avg_5 is not NULL')
ordlist=cc.fetchall()
ordlist.sort()
check_avg_l.configure(text='Averages valid from '+date.fromordinal(ordlist[0][0]).isoformat()+ \
' to '+date.fromordinal(ordlist[-1][0]).isoformat())
conn.close()
ttk.Button(area_config, text="data dir", command=getDirName).grid(column=0, row=2, sticky=tk.W)
filedir = tk.StringVar()
filedirLen = 40
filedirEntry = ttk.Entry(area_config, width=filedirLen,textvariable=filedir)
filedirEntry.grid(column=1, row=2, sticky=tk.W)
ttk.Button(area_config, text="database file", command=getDatabaseFileName).grid(column=0, row=3, stick=tk.W)
dbfilename = tk.StringVar()
dbfilenameLen=40
dbfilenameEntry = ttk.Entry(area_config, width=dbfilenameLen, textvariable=dbfilename)
dbfilenameEntry.grid(column=1, row=3, sticky=tk.W)
ttk.Button(area_config, text="check data dir", command=checkDataDir).grid(column=0, row=4, sticky=tk.W)
check_datadir_l=ttk.Label(area_config, text='--')
check_datadir_l.grid(column=1, row=4, padx=8, sticky=tk.W)
ttk.Button(area_config, text="check database", command=checkDatabase).grid(column=0, row=5, sticky=tk.W)
check_db_l=ttk.Label(area_config, text='--')
check_db_l.grid(column=1, row=5, padx=8, sticky=tk.W)
ttk.Button(area_config, text="check averages", command=checkAVGstatus).grid(column=0, row=6, sticky=tk.W)
check_avg_l=ttk.Label(area_config, text='--')
check_avg_l.grid(column=1, row=6, padx=8, sticky=tk.W)
#
# database maintenance routines
#
def crawl_date(orddate, save_dir):
datestr = date.fromordinal(orddate).strftime("%Y%m%d")
fetchstr = 'http://www.twse.com.tw/exchangeReport/MI_INDEX?response=csv&date=' + datestr + '&type=ALLBUT0999'
r = requests.post(fetchstr)
f = open(save_dir+"/twse/"+datestr, "wt", encoding="utf8", newline='\n')
f.write(r.text)
f.close()
datestr2 = date.fromordinal(orddate).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'
r = requests.post(fetchstr)
f = open(save_dir+"/tpex/"+datestr, "wt", encoding="utf8", newline='\n')
f.write(r.text)
f.close()
return 0
def create_db_file(dbfname):
conn = sqlite3.connect(dbfname)
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,\
strdate text, \
twse_update int,\
tpex_update int\
)")
conn.commit()
conn.close()
def update_twse_db(orddate, datapath):
datestr=date.fromordinal(orddate).strftime("%Y%m%d")
f=open(datapath+"/twse/"+datestr, "rt", encoding="utf8")
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, datapath):
datestr=date.fromordinal(orddate).strftime("%Y%m%d")
f=open(datapath+"/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
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)
#
# database maintenance area
#
def fetch_data():
print("fetch stock data")
# ensure dir for twse and tpex exist
if not os.path.exists(filedir.get()+'/twse'):
os.mkdir(filedir.get()+'/twse')
if not os.path.exists(filedir.get()+'/tpex'):
os.mkdir(filedir.get()+'/tpex')
d1=date.fromisoformat(start_date.get())
d2=date.fromisoformat(end_date.get())
d_start=d1.toordinal()
d_end=d2.toordinal()
print("fetch date ",d_start, d_end)
total_d = d_end - d_start + 1;
fetch_data_l.configure(text='fetching data')
for ordd in range(d_start, d_end+1):
status_line_l.configure(text='fetching '+date.fromordinal(ordd).isoformat())
progress_b["value"]=int((ordd-d_start+1)*100/total_d)
progress_b.update()
crawl_date(ordd,filedir.get())
time.sleep(10)
fetch_data_l.configure(text='done '+start_date.get()+' to '+end_date.get())
status_line_l.configure(text='idle')
progress_b["value"]=0
progress_b.update()
def update_database():
print("update database")
# make sure db file exists
if not os.path.exists(dbfilename.get()):
create_db_file(dbfilename.get())
d1=date.fromisoformat(start_date.get())
d2=date.fromisoformat(end_date.get())
d_start=d1.toordinal()
d_end=d2.toordinal()
print("fetch date ",d_start, d_end)
total_d = d_end - d_start + 1;
update_db_l.configure(text='updating data')
conn=sqlite3.connect(dbfilename.get())
for ordd in range(d_start, d_end+1):
status_line_l.configure(text='updating '+date.fromordinal(ordd).isoformat())
progress_b["value"]=int((ordd-d_start+1)*100/total_d)
progress_b.update()
# test if datalog contains orddate data
cc = conn.execute("select twse_update from datalog where orddate="+str(ordd))
res = cc.fetchone()
if res!=None :
continue
# this orddate data is not updated yet, go on update the database
df=update_twse_db(ordd, filedir.get())
# 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
dd=date.fromordinal(ordd)
cc = conn.execute("insert into datalog (orddate, strdate, twse_update) values ("+str(ordd)+","+dd.strftime('%Y%m%d')+",1)")
conn.commit()
df=update_tpex_db(ordd, filedir.get())
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(ordd))
conn.commit()
conn.close()
update_db_l.configure(text='done '+start_date.get()+' to '+end_date.get())
status_line_l.configure(text='idle')
progress_b["value"]=0
progress_b.update()
def cal_averages():
print("cal averages")
d1=date.fromisoformat(start_date.get())
d2=date.fromisoformat(end_date.get())
d_start=d1.toordinal()
d_end=d2.toordinal()
lastdate=date.fromisoformat(dblastdate[:4]+'-'+dblastdate[4:6]+'-'+dblastdate[6:8])
dblastorddate=lastdate.toordinal()
total_d = d_end - d_start + 1;
cal_avgs_l.configure(text='calculating averages')
dbconn=sqlite3.connect(dbfilename.get())
start_day=d_end
# find the first working day
while (check_workingday(dbconn, start_day)==False):
start_day=start_day-1
if (start_day<d_start):
print('no working days within the range')
cal_avgs_l.configure(text='no working days found')
return -1
print("start day=",start_day)
cc=dbconn.execute('select stock_id from stock where orddate is '+str(start_day))
st_idlist=cc.fetchall()
for st_id in st_idlist:
#for st_id in (('2377',),):
print(st_id)
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])
if (dque[0][0]>=d_start):
#update database
updatestr='update stock set avg_5='+str(avgs[0])+', avg_10='+str(avgs[1])+\
', avg_20='+str(avgs[2])+', avg_60='+str(avgs[3])+\
', deviation='+str(deviation)+' where orddate is '+str(dque[0][0])+\
' and stock_id is "'+st_id[0]+'"'
dbconn.execute(updatestr)
dbconn.commit()
while (dque[0][0]> d_start): # if the queue top day is still larger than loop end
status_line_l.configure(text='updating '+date.fromordinal(dque[0][0]).isoformat())
progress_b["value"]=100-int((dque[0][0]-d_start+1)*100/total_d)
progress_b.update()
# 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])
if (dque[0][0]>=d_start):
#update database
updatestr='update stock set avg_5='+str(avgs[0])+', avg_10='+str(avgs[1])+\
', avg_20='+str(avgs[2])+', avg_60='+str(avgs[3])+\
', deviation='+str(deviation)+' where orddate is '+str(dque[0][0])+\
' and stock_id is "'+st_id[0]+'"'
dbconn.execute(updatestr)
dbconn.commit()
dbconn.close()
cal_avgs_l.configure(text='done '+start_date.get()+' to '+end_date.get())
status_line_l.configure(text='idle')
progress_b["value"]=0
progress_b.update()
return 0
# update area UI part
area_update = ttk.LabelFrame(tab1, text=' maintenance ')
area_update.grid(column=0, row=4, padx=8, pady=4, sticky=tk.W)
ttk.Button(area_update, text='fetch data', command=fetch_data).grid(column=0, row=0, sticky=tk.W)
fetch_data_l=ttk.Label(area_update, text='--')
fetch_data_l.grid(column=1, row=0, padx=8, sticky=tk.W)
ttk.Button(area_update, text='update database', command=update_database).grid(column=0, row=1, sticky=tk.W)
update_db_l=ttk.Label(area_update, text='--')
update_db_l.grid(column=1, row=1, padx=8, sticky=tk.W)
ttk.Button(area_update, text='calculate AVGs', command=cal_averages).grid(column=0, row=2, sticky=tk.W)
cal_avgs_l=ttk.Label(area_update, text='--')
cal_avgs_l.grid(column=1, row=2, padx=8, sticky=tk.W)
#
# status area
#
area_status = ttk.LabelFrame(tab1, text=' status ')
area_status.grid(column=0, row=8, padx=8, pady=4, sticky=tk.W)
ttk.Label(area_status, text="Status :").grid(column=0, row=0, sticky=tk.W)
status_line_l=ttk.Label(area_status, text='idle')
status_line_l.grid(column=1, row=0, sticky=tk.W)
ttk.Label(area_status, text="Progress :").grid(column=0, row=1, sticky=tk.W)
progress_b = ttk.Progressbar(area_status, orient='horizontal', length=300, mode='determinate')
progress_b.grid(column=1, row=1)
# Tab Control 2 refactoring ---------------------------------------------------------
# We are creating a container frame to hold all other widgets -- Tab2
mighty2 = ttk.LabelFrame(tab2, text=' The Snake ')
mighty2.grid(column=0, row=0, padx=8, pady=4)
# Creating three checkbuttons
chVarDis = tk.IntVar()
check1 = tk.Checkbutton(mighty2, text="Disabled", variable=chVarDis, state='disabled')
check1.select()
check1.grid(column=0, row=0, sticky=tk.W)
chVarUn = tk.IntVar()
check2 = tk.Checkbutton(mighty2, text="UnChecked", variable=chVarUn)
check2.deselect()
check2.grid(column=1, row=0, sticky=tk.W)
chVarEn = tk.IntVar()
check3 = tk.Checkbutton(mighty2, text="Enabled", variable=chVarEn)
check3.deselect()
check3.grid(column=2, row=0, sticky=tk.W)
# GUI Callback function
def checkCallback(*ignoredArgs):
# only enable one checkbutton
if chVarUn.get(): check3.configure(state='disabled')
else: check3.configure(state='normal')
if chVarEn.get(): check2.configure(state='disabled')
else: check2.configure(state='normal')
# trace the state of the two checkbuttons
chVarUn.trace('w', lambda unused0, unused1, unused2 : checkCallback())
chVarEn.trace('w', lambda unused0, unused1, unused2 : checkCallback())
# First, we change our Radiobutton global variables into a list
colors = ["Blue", "Gold", "Red"]
# We have also changed the callback function to be zero-based, using the list
# instead of module-level global variables
# Radiobutton Callback
def radCall():
radSel=radVar.get()
if radSel == 0: mighty2.configure(text='Blue')
elif radSel == 1: mighty2.configure(text='Gold')
elif radSel == 2: mighty2.configure(text='Red')
# create three Radiobuttons using one variable
radVar = tk.IntVar()
# Next we are selecting a non-existing index value for radVar
radVar.set(99)
# Now we are creating all three Radiobutton widgets within one loop
for col in range(3):
curRad = tk.Radiobutton(mighty2, text=colors[col], variable=radVar,
value=col, command=radCall)
curRad.grid(column=col, row=1, sticky=tk.W) # row=6
# Add a Progressbar to Tab 2
progress_bar = ttk.Progressbar(tab2, orient='horizontal', length=286, mode='determinate')
progress_bar.grid(column=0, row=3, pady=2)
# update progressbar in callback loop
def run_progressbar():
progress_bar["maximum"] = 100
for i in range(101):
sleep(0.05)
progress_bar["value"] = i # increment progressbar
progress_bar.update() # have to call update() in loop
progress_bar["value"] = 0 # reset/clear progressbar
def start_progressbar():
progress_bar.start()
def stop_progressbar():
progress_bar.stop()
def progressbar_stop_after(wait_ms=1000):
win.after(wait_ms, progress_bar.stop)
# Create a container to hold buttons
buttons_frame = ttk.LabelFrame(mighty2, text=' ProgressBar ')
buttons_frame.grid(column=0, row=2, sticky='W', columnspan=2)
# Add Buttons for Progressbar commands
ttk.Button(buttons_frame, text=" Run Progressbar ", command=run_progressbar).grid(column=0, row=0, sticky='W')
ttk.Button(buttons_frame, text=" Start Progressbar ", command=start_progressbar).grid(column=0, row=1, sticky='W')
ttk.Button(buttons_frame, text=" Stop immediately ", command=stop_progressbar).grid(column=0, row=2, sticky='W')
ttk.Button(buttons_frame, text=" Stop after second ", command=progressbar_stop_after).grid(column=0, row=3, sticky='W')
for child in buttons_frame.winfo_children():
child.grid_configure(padx=2, pady=2)
for child in mighty2.winfo_children():
child.grid_configure(padx=8, pady=2)
# Exit GUI cleanly
def _quit():
win.quit()
win.destroy()
exit()
# Creating a Menu Bar
menu_bar = Menu(win)
win.config(menu=menu_bar)
# Add menu items
file_menu = Menu(menu_bar, tearoff=0)
file_menu.add_command(label="New")
file_menu.add_separator()
file_menu.add_command(label="Exit", command=_quit)
menu_bar.add_cascade(label="File", menu=file_menu)
# Display a Message Box
def _msgBox():
msg.showinfo('Python Message Info Box', 'A Python GUI created using tkinter:\nThe year is 2017.')
# Add another Menu to the Menu Bar and an item
help_menu = Menu(menu_bar, tearoff=0)
help_menu.add_command(label="About", command=_msgBox) # display messagebox when clicked
menu_bar.add_cascade(label="Help", menu=help_menu)
#======================
# Start GUI
#======================
print('argv0=',sys.argv[0],sys.argv[1])
win.mainloop()
Data Mining Project :
'''
#======================
# imports
#======================
import tkinter as tk
from tkinter import ttk
from tkinter import scrolledtext
from tkinter import Menu
from tkinter import messagebox as msg
from tkinter import Spinbox
from time import sleep # careful - this can freeze the GUI
from tkcalendar import Calendar, DateEntry
from tkinter import filedialog as fd
import os
import sys
from datetime import date
import time
import requests
import dill
import pandas as pd
from io import StringIO
import sqlite3
import math
from collections import deque
# should use auto calculation to get the last stock date
dblastdate='20160901'
# Create instance
win = tk.Tk()
# Add a title
win.title("Data Mining Project")
tabControl = ttk.Notebook(win) # Create Tab Control
tab1 = ttk.Frame(tabControl) # Create a tab
tabControl.add(tab1, text='database maintenance') # Add the tab
tab2 = ttk.Frame(tabControl) # Add a second tab
tabControl.add(tab2, text='mining target') # Make second tab visible
tabControl.pack(expand=1, fill="both") # Pack to make visible
#
# configuration area
#
area_config = ttk.LabelFrame(tab1, text=' configure ')
area_config.grid(column=0, row=0, padx=8, pady=4)
ttk.Label(area_config, text="Start Date:").grid(column=0, row=0, sticky=tk.W)
start_date = tk.StringVar()
start_Entry = DateEntry(area_config, font='Arial 12', width=12, background='darkblue',
foreground='white', borderwidth=2, date_pattern='y-mm-dd', textvariable=start_date)
start_Entry.grid(column=1, row=0, sticky=tk.W)
ttk.Label(area_config, text="End Date:").grid(column=0, row=1, sticky=tk.W)
end_date = tk.StringVar()
end_Entry = DateEntry(area_config, font='Arial 12', width=12, background='darkblue',
foreground='white', borderwidth=2, date_pattern='y-mm-dd', textvariable=end_date)
end_Entry.grid(column=1, row=1, sticky=tk.W)
def getDirName():
fDir = os.path.dirname('__file__')
fName = fd.askdirectory(parent=win, title='choose stock data dir', initialdir=fDir)
filedir.set(fName)
def getDatabaseFileName():
fDir = os.path.dirname('__file__')
fName = fd.askopenfilename(parent=win, title='select database file', initialdir=fDir)
dbfilename.set(fName)
def checkDataDir():
if not os.path.exists(filedir.get()+'/twse'):
check_datadir_l.configure(text='directory is not created')
return
if not os.path.exists(filedir.get()+'/tpex'):
check_datadir_l.configure(text='directory is not created')
return
filelist=os.listdir(filedir.get()+'/twse')
total_twse_item=len(filelist)
if (total_twse_item==0):
check_datadir_l.configure(text='directory is empty')
return
filelist.sort()
firstord=date.fromisoformat(filelist[0][:4]+'-'+filelist[0][4:6]+'-'+filelist[0][6:8]).toordinal()
lastord=date.fromisoformat(filelist[-1][:4]+'-'+filelist[-1][4:6]+'-'+filelist[-1][6:8]).toordinal()
if (firstord==0):
check_datadir_l.configure(text='illegal file name in twse directory')
return
if ((lastord-firstord+1)==total_twse_item):
check_datadir_l.configure(text='file complete '+date.fromordinal(firstord).isoformat()+ \
' to '+date.fromordinal(lastord).isoformat())
return
check_datadir_l.configure(text=date.fromordinal(firstord).isoformat()+' to '+ \
date.fromordinal(lastord).isoformat()+' short of '+ \
str(total_twse_item-lastord+firstord-1)+' files')
return
def checkDatabase():
if not os.path.exists(dbfilename.get()):
check_db_l.configure(text='database file has not created yet')
return
# check data on 2330
conn=sqlite3.connect('d:/archive/stock/twstock.db')
cc=conn.execute('select orddate from datalog')
ordlist=cc.fetchall()
ordlist.sort()
check_db_l.configure(text='database from '+date.fromordinal(ordlist[0][0]).isoformat()+ \
' to '+date.fromordinal(ordlist[-1][0]).isoformat())
conn.close()
def checkAVGstatus():
if not os.path.exists(dbfilename.get()):
check_avg_l.configure(text='database file has not created yet')
return
# check data on 2330
conn=sqlite3.connect('d:/archive/stock/twstock.db')
cc=conn.execute('select orddate from stock where stock_id is 2330 and avg_5 is not NULL')
ordlist=cc.fetchall()
ordlist.sort()
check_avg_l.configure(text='Averages valid from '+date.fromordinal(ordlist[0][0]).isoformat()+ \
' to '+date.fromordinal(ordlist[-1][0]).isoformat())
conn.close()
ttk.Button(area_config, text="data dir", command=getDirName).grid(column=0, row=2, sticky=tk.W)
filedir = tk.StringVar()
filedirLen = 40
filedirEntry = ttk.Entry(area_config, width=filedirLen,textvariable=filedir)
filedirEntry.grid(column=1, row=2, sticky=tk.W)
ttk.Button(area_config, text="database file", command=getDatabaseFileName).grid(column=0, row=3, stick=tk.W)
dbfilename = tk.StringVar()
dbfilenameLen=40
dbfilenameEntry = ttk.Entry(area_config, width=dbfilenameLen, textvariable=dbfilename)
dbfilenameEntry.grid(column=1, row=3, sticky=tk.W)
ttk.Button(area_config, text="check data dir", command=checkDataDir).grid(column=0, row=4, sticky=tk.W)
check_datadir_l=ttk.Label(area_config, text='--')
check_datadir_l.grid(column=1, row=4, padx=8, sticky=tk.W)
ttk.Button(area_config, text="check database", command=checkDatabase).grid(column=0, row=5, sticky=tk.W)
check_db_l=ttk.Label(area_config, text='--')
check_db_l.grid(column=1, row=5, padx=8, sticky=tk.W)
ttk.Button(area_config, text="check averages", command=checkAVGstatus).grid(column=0, row=6, sticky=tk.W)
check_avg_l=ttk.Label(area_config, text='--')
check_avg_l.grid(column=1, row=6, padx=8, sticky=tk.W)
#
# database maintenance routines
#
def crawl_date(orddate, save_dir):
datestr = date.fromordinal(orddate).strftime("%Y%m%d")
fetchstr = 'http://www.twse.com.tw/exchangeReport/MI_INDEX?response=csv&date=' + datestr + '&type=ALLBUT0999'
r = requests.post(fetchstr)
f = open(save_dir+"/twse/"+datestr, "wt", encoding="utf8", newline='\n')
f.write(r.text)
f.close()
datestr2 = date.fromordinal(orddate).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'
r = requests.post(fetchstr)
f = open(save_dir+"/tpex/"+datestr, "wt", encoding="utf8", newline='\n')
f.write(r.text)
f.close()
return 0
def create_db_file(dbfname):
conn = sqlite3.connect(dbfname)
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,\
strdate text, \
twse_update int,\
tpex_update int\
)")
conn.commit()
conn.close()
def update_twse_db(orddate, datapath):
datestr=date.fromordinal(orddate).strftime("%Y%m%d")
f=open(datapath+"/twse/"+datestr, "rt", encoding="utf8")
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, datapath):
datestr=date.fromordinal(orddate).strftime("%Y%m%d")
f=open(datapath+"/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
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)
#
# database maintenance area
#
def fetch_data():
print("fetch stock data")
# ensure dir for twse and tpex exist
if not os.path.exists(filedir.get()+'/twse'):
os.mkdir(filedir.get()+'/twse')
if not os.path.exists(filedir.get()+'/tpex'):
os.mkdir(filedir.get()+'/tpex')
d1=date.fromisoformat(start_date.get())
d2=date.fromisoformat(end_date.get())
d_start=d1.toordinal()
d_end=d2.toordinal()
print("fetch date ",d_start, d_end)
total_d = d_end - d_start + 1;
fetch_data_l.configure(text='fetching data')
for ordd in range(d_start, d_end+1):
status_line_l.configure(text='fetching '+date.fromordinal(ordd).isoformat())
progress_b["value"]=int((ordd-d_start+1)*100/total_d)
progress_b.update()
crawl_date(ordd,filedir.get())
time.sleep(10)
fetch_data_l.configure(text='done '+start_date.get()+' to '+end_date.get())
status_line_l.configure(text='idle')
progress_b["value"]=0
progress_b.update()
def update_database():
print("update database")
# make sure db file exists
if not os.path.exists(dbfilename.get()):
create_db_file(dbfilename.get())
d1=date.fromisoformat(start_date.get())
d2=date.fromisoformat(end_date.get())
d_start=d1.toordinal()
d_end=d2.toordinal()
print("fetch date ",d_start, d_end)
total_d = d_end - d_start + 1;
update_db_l.configure(text='updating data')
conn=sqlite3.connect(dbfilename.get())
for ordd in range(d_start, d_end+1):
status_line_l.configure(text='updating '+date.fromordinal(ordd).isoformat())
progress_b["value"]=int((ordd-d_start+1)*100/total_d)
progress_b.update()
# test if datalog contains orddate data
cc = conn.execute("select twse_update from datalog where orddate="+str(ordd))
res = cc.fetchone()
if res!=None :
continue
# this orddate data is not updated yet, go on update the database
df=update_twse_db(ordd, filedir.get())
# 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
dd=date.fromordinal(ordd)
cc = conn.execute("insert into datalog (orddate, strdate, twse_update) values ("+str(ordd)+","+dd.strftime('%Y%m%d')+",1)")
conn.commit()
df=update_tpex_db(ordd, filedir.get())
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(ordd))
conn.commit()
conn.close()
update_db_l.configure(text='done '+start_date.get()+' to '+end_date.get())
status_line_l.configure(text='idle')
progress_b["value"]=0
progress_b.update()
def cal_averages():
print("cal averages")
d1=date.fromisoformat(start_date.get())
d2=date.fromisoformat(end_date.get())
d_start=d1.toordinal()
d_end=d2.toordinal()
lastdate=date.fromisoformat(dblastdate[:4]+'-'+dblastdate[4:6]+'-'+dblastdate[6:8])
dblastorddate=lastdate.toordinal()
total_d = d_end - d_start + 1;
cal_avgs_l.configure(text='calculating averages')
dbconn=sqlite3.connect(dbfilename.get())
start_day=d_end
# find the first working day
while (check_workingday(dbconn, start_day)==False):
start_day=start_day-1
if (start_day<d_start):
print('no working days within the range')
cal_avgs_l.configure(text='no working days found')
return -1
print("start day=",start_day)
cc=dbconn.execute('select stock_id from stock where orddate is '+str(start_day))
st_idlist=cc.fetchall()
for st_id in st_idlist:
#for st_id in (('2377',),):
print(st_id)
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])
if (dque[0][0]>=d_start):
#update database
updatestr='update stock set avg_5='+str(avgs[0])+', avg_10='+str(avgs[1])+\
', avg_20='+str(avgs[2])+', avg_60='+str(avgs[3])+\
', deviation='+str(deviation)+' where orddate is '+str(dque[0][0])+\
' and stock_id is "'+st_id[0]+'"'
dbconn.execute(updatestr)
dbconn.commit()
while (dque[0][0]> d_start): # if the queue top day is still larger than loop end
status_line_l.configure(text='updating '+date.fromordinal(dque[0][0]).isoformat())
progress_b["value"]=100-int((dque[0][0]-d_start+1)*100/total_d)
progress_b.update()
# 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])
if (dque[0][0]>=d_start):
#update database
updatestr='update stock set avg_5='+str(avgs[0])+', avg_10='+str(avgs[1])+\
', avg_20='+str(avgs[2])+', avg_60='+str(avgs[3])+\
', deviation='+str(deviation)+' where orddate is '+str(dque[0][0])+\
' and stock_id is "'+st_id[0]+'"'
dbconn.execute(updatestr)
dbconn.commit()
dbconn.close()
cal_avgs_l.configure(text='done '+start_date.get()+' to '+end_date.get())
status_line_l.configure(text='idle')
progress_b["value"]=0
progress_b.update()
return 0
# update area UI part
area_update = ttk.LabelFrame(tab1, text=' maintenance ')
area_update.grid(column=0, row=4, padx=8, pady=4, sticky=tk.W)
ttk.Button(area_update, text='fetch data', command=fetch_data).grid(column=0, row=0, sticky=tk.W)
fetch_data_l=ttk.Label(area_update, text='--')
fetch_data_l.grid(column=1, row=0, padx=8, sticky=tk.W)
ttk.Button(area_update, text='update database', command=update_database).grid(column=0, row=1, sticky=tk.W)
update_db_l=ttk.Label(area_update, text='--')
update_db_l.grid(column=1, row=1, padx=8, sticky=tk.W)
ttk.Button(area_update, text='calculate AVGs', command=cal_averages).grid(column=0, row=2, sticky=tk.W)
cal_avgs_l=ttk.Label(area_update, text='--')
cal_avgs_l.grid(column=1, row=2, padx=8, sticky=tk.W)
#
# status area
#
area_status = ttk.LabelFrame(tab1, text=' status ')
area_status.grid(column=0, row=8, padx=8, pady=4, sticky=tk.W)
ttk.Label(area_status, text="Status :").grid(column=0, row=0, sticky=tk.W)
status_line_l=ttk.Label(area_status, text='idle')
status_line_l.grid(column=1, row=0, sticky=tk.W)
ttk.Label(area_status, text="Progress :").grid(column=0, row=1, sticky=tk.W)
progress_b = ttk.Progressbar(area_status, orient='horizontal', length=300, mode='determinate')
progress_b.grid(column=1, row=1)
# Tab Control 2 refactoring ---------------------------------------------------------
# We are creating a container frame to hold all other widgets -- Tab2
mighty2 = ttk.LabelFrame(tab2, text=' The Snake ')
mighty2.grid(column=0, row=0, padx=8, pady=4)
# Creating three checkbuttons
chVarDis = tk.IntVar()
check1 = tk.Checkbutton(mighty2, text="Disabled", variable=chVarDis, state='disabled')
check1.select()
check1.grid(column=0, row=0, sticky=tk.W)
chVarUn = tk.IntVar()
check2 = tk.Checkbutton(mighty2, text="UnChecked", variable=chVarUn)
check2.deselect()
check2.grid(column=1, row=0, sticky=tk.W)
chVarEn = tk.IntVar()
check3 = tk.Checkbutton(mighty2, text="Enabled", variable=chVarEn)
check3.deselect()
check3.grid(column=2, row=0, sticky=tk.W)
# GUI Callback function
def checkCallback(*ignoredArgs):
# only enable one checkbutton
if chVarUn.get(): check3.configure(state='disabled')
else: check3.configure(state='normal')
if chVarEn.get(): check2.configure(state='disabled')
else: check2.configure(state='normal')
# trace the state of the two checkbuttons
chVarUn.trace('w', lambda unused0, unused1, unused2 : checkCallback())
chVarEn.trace('w', lambda unused0, unused1, unused2 : checkCallback())
# First, we change our Radiobutton global variables into a list
colors = ["Blue", "Gold", "Red"]
# We have also changed the callback function to be zero-based, using the list
# instead of module-level global variables
# Radiobutton Callback
def radCall():
radSel=radVar.get()
if radSel == 0: mighty2.configure(text='Blue')
elif radSel == 1: mighty2.configure(text='Gold')
elif radSel == 2: mighty2.configure(text='Red')
# create three Radiobuttons using one variable
radVar = tk.IntVar()
# Next we are selecting a non-existing index value for radVar
radVar.set(99)
# Now we are creating all three Radiobutton widgets within one loop
for col in range(3):
curRad = tk.Radiobutton(mighty2, text=colors[col], variable=radVar,
value=col, command=radCall)
curRad.grid(column=col, row=1, sticky=tk.W) # row=6
# Add a Progressbar to Tab 2
progress_bar = ttk.Progressbar(tab2, orient='horizontal', length=286, mode='determinate')
progress_bar.grid(column=0, row=3, pady=2)
# update progressbar in callback loop
def run_progressbar():
progress_bar["maximum"] = 100
for i in range(101):
sleep(0.05)
progress_bar["value"] = i # increment progressbar
progress_bar.update() # have to call update() in loop
progress_bar["value"] = 0 # reset/clear progressbar
def start_progressbar():
progress_bar.start()
def stop_progressbar():
progress_bar.stop()
def progressbar_stop_after(wait_ms=1000):
win.after(wait_ms, progress_bar.stop)
# Create a container to hold buttons
buttons_frame = ttk.LabelFrame(mighty2, text=' ProgressBar ')
buttons_frame.grid(column=0, row=2, sticky='W', columnspan=2)
# Add Buttons for Progressbar commands
ttk.Button(buttons_frame, text=" Run Progressbar ", command=run_progressbar).grid(column=0, row=0, sticky='W')
ttk.Button(buttons_frame, text=" Start Progressbar ", command=start_progressbar).grid(column=0, row=1, sticky='W')
ttk.Button(buttons_frame, text=" Stop immediately ", command=stop_progressbar).grid(column=0, row=2, sticky='W')
ttk.Button(buttons_frame, text=" Stop after second ", command=progressbar_stop_after).grid(column=0, row=3, sticky='W')
for child in buttons_frame.winfo_children():
child.grid_configure(padx=2, pady=2)
for child in mighty2.winfo_children():
child.grid_configure(padx=8, pady=2)
# Exit GUI cleanly
def _quit():
win.quit()
win.destroy()
exit()
# Creating a Menu Bar
menu_bar = Menu(win)
win.config(menu=menu_bar)
# Add menu items
file_menu = Menu(menu_bar, tearoff=0)
file_menu.add_command(label="New")
file_menu.add_separator()
file_menu.add_command(label="Exit", command=_quit)
menu_bar.add_cascade(label="File", menu=file_menu)
# Display a Message Box
def _msgBox():
msg.showinfo('Python Message Info Box', 'A Python GUI created using tkinter:\nThe year is 2017.')
# Add another Menu to the Menu Bar and an item
help_menu = Menu(menu_bar, tearoff=0)
help_menu.add_command(label="About", command=_msgBox) # display messagebox when clicked
menu_bar.add_cascade(label="Help", menu=help_menu)
#======================
# Start GUI
#======================
print('argv0=',sys.argv[0],sys.argv[1])
win.mainloop()
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
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()
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)