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