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()

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)

2019年9月10日 星期二

setup items for retrieving twse info

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

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

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

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


database fields :
from twse :

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

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