def Get_Quotes(self, start, end, stk_id=None):
p = Profiler()
self.log.info(
"[START] {cn}.Get_Quotes(), start: {st}, end: {et}, stk_id: {s}".
format(cn=type(self).__name__, st=start, et=end, s=stk_id))
sql = "select * from tw_stock_quote where q_date between {s} and {e} "
if stk_id is not None:
s = ""
if type(stk_id).__name__ == "list":
s = ",".join(stk_id)
elif type(stk_id).__name__ == "str":
s = stk_id
sql = sql + (" and stk_id = " + s)
sql = sql + " order by stk_id, q_date "
sql = sql.format(s=start, e=end)
self.log.debug("sql: " + sql)
session = self.sessionFactory.GetSession()
ls = session.execute(sql)
session.close()
self.log.info(
"[END] {cn}.GetQuotes(), exec TIME: {t} ms, start: {st}, end: {et}, stk_id: {stk}"
.format(cn=type(self).__name__,
st=start,
et=end,
stk=stk_id,
t=p.executeTime()))
return ls
def update_updown_value(self, stk_id, q_date, updown, updown_limit):
p = Profiler()
self.log.info(
"[START] {cn}.update_updown_value(), stk_id: {id}, q_date: {qd}, updown: {u}, updown_limit: {ul}"
.format(cn=type(self).__name__,
id=stk_id,
qd=q_date,
u=updown,
ul=updown_limit))
sql = "update tw_stock_quote set updown = {u}, updown_limit = {ul} where stk_id = '{id}' and q_date = '{qd}'".format(
u=updown, ul=updown_limit, id=stk_id, qd=q_date)
self.log.debug("update updown sql: " + sql)
session = self.sessionFactory.GetSession()
session.execute(sql)
session.commit()
session.close()
self.log.info(
"[end] {cn}.update_updown_value(), stk_id: {id}, q_date: {qd}, updown: {u}, updown_limit: {ul}, exec TIME: {t}"
.format(cn=type(self).__name__,
id=stk_id,
qd=q_date,
u=updown,
ul=updown_limit,
t=p.executeTime()))
pass
def Get_Stock_Info(self, stk_id):
p = Profiler()
self.log.info(
"[START] {cname}.Get_Stock_Info(), stk_id: {stk_id}".format(
cname=type(self).__name__, stk_id=stk_id))
sql = """
select STOCK_ID, STOCK_NAME, NAME, q.close, q.q_date
from TW_STOCK_LIST a
inner join TW_STOCK_INDUSTRY b on a.INDUSTRY = b.SERIAL_NO
inner join (select stk_id, close, q_date from tw_stock_quote where stk_id = '{id}' order by q_date desc limit 1) q on a.STOCK_ID = q.stk_id
where a.STOCK_ID = '{id}'
order by b.SERIAL_NO, a.STOCK_ID, close
"""
sql = sql.format(id=stk_id)
self.log.debug("exec sql statement: {sql}".format(sql=sql))
session = self.sessionFactory.GetSession()
stk_list = session.execute(sql)
session.close()
self.log.info(
"[END] {cname}.Get_Stock_Info(), exec TIME: {t} ms., stk_no: {sn}".
format(cname=type(self).__name__, t=p.executeTime(), sn=stk_id))
return stk_list
def GetQuote_from_Yahoo(self, start, end, callback=None):
pr = Profiler()
self.log.info(
'[START] {cname}.GetQuote_from_Yahoo(), start: {start}, end: {end}'
.format(cname=type(self).__name__, start=start, end=end))
try:
f = web.DataReader(self.stock_no + '.TW', 'yahoo', start, end)
fcsv = f.to_csv()
rs = []
i = 0
sp = fcsv.splitlines()
for row in sp:
self.log.debug('row: ' + row)
if i == 0:
i = i + 1
continue
if ',,,' in row:
self.log.info('empty data: ' + row)
continue
d = row.split(',')
p = {}
p['date'] = d[0]
p['high'] = d[1]
p['low'] = d[2]
p['open'] = d[3]
p['close'] = d[4]
p['volumn'] = d[5]
p['adj_close'] = d[6]
rs.append(p)
self.log.info(
"[END] {cn}.GetQuote_from_Yahoo(), exec TIME: {t} ms, start: {s}, end: {e}, stk_id: {id}"
.format(cn=type(self).__name__,
t=pr.executeTime(),
s=start,
e=end,
id=self.stock_no))
if callback is not None:
callback(self.stock_no, rs)
else:
return self.stock_no, rs
except Exception as e:
msg = str(e)
self.log.error(msg)
if msg.find("No data fetched for symbol") >= 0:
return None, None
else:
self.log.error('happen error. repeat get data, stock_no: ' +
self.stock_no + ', repeat times: ' +
str(5 - self.MAX_REPEAT_TIMES + 1))
self.MAX_REPEAT_TIMES = self.MAX_REPEAT_TIMES - 1
if self.MAX_REPEAT_TIMES > 0:
return self.GetQuote_from_Yahoo(start, end, callback)
else:
self.MAX_REPEAT_TIMES = 5
return None, None
pass
def Get_Last_Updown(self, stk_id):
p = Profiler()
self.log.info("[START] {cn}.Get_Last_Updown(), stk_id: {id}".format(
cn=type(self).__name__, id=stk_id))
sql = "select * from tw_stock_quote where stk_id = '{id}' and updown is not null order by q_date desc limit 1".format(
id=stk_id)
session = self.sessionFactory.GetSession()
rs = session.execute(sql).fetchone()
self.log.info(
"[END] {cn}.Get_Last_Updown(), exec TIME: {t} ms, stk_id: {id}".
format(cn=type(self).__name__, t=p.executeTime(), id=stk_id))
return rs
def Get_Uncalcute_Updown(self, stk_id):
p = Profiler()
self.log.info("[START] {cn}.Calcute_UpDown(), stk_id: {id}".format(
cn=type(self).__name__, id=stk_id))
sql1 = "select * from tw_stock_quote where stk_id = '{id}' and updown is null order by q_date".format(
id=stk_id)
session = self.sessionFactory.GetSession()
ls = session.execute(sql1)
self.log.info(
"[END] {cn}.Calcute_UpDown(), exec TIME: {t} ms, stk_id: {id}".
format(cn=type(self).__name__, t=p.executeTime(), id=stk_id))
return ls
def Get_Noncalcute_EWMA(self, stk_id):
p = Profiler()
self.log.info(
"[START] {cn}.Get_Noncalcute_EWMA(), stk_id: {id}".format(
cn=type(self).__name__, id=stk_id))
sql = "select * from tw_stock_quote where stk_id = '{id}' and ewma is null order by q_date desc".format(
id=stk_id)
session = self.sessionFactory.GetSession()
ls = session.execute(sql)
session.close()
self.log.info(
"[END] {cn}.Get_Noncalcute_EWMA(), exec TIME: {t} ms, stk_id: {id}"
.format(cn=type(self).__name__, t=p.executeTime(), id=stk_id))
return ls
def Get_Last_Quote(self, stk_id):
p = Profiler()
self.log.info("[START] {cname}.Get_Last_Quote(), stk_no: {sn}".format(
cname=type(self).__name__, sn=stk_id))
sql = "select * from tw_stock_quote where stk_id = {stk} and q_date = (select max(q_date) from tw_stock_quote where stk_id = {stk}) ".format(
stk=stk_id)
self.log.debug("query last date sql: " + sql)
session = self.sessionFactory.GetSession()
stk_list = session.execute(sql)
session.close()
self.log.info(
"[END] {cname}.Get_Last_Quote(), exec TIME: {t} ms., stk_no: {sn}".
format(cname=type(self).__name__, t=p.executeTime(), sn=stk_id))
return stk_list
def saveBeans(self, beans):
p = Profiler()
self.log.info("[START] {cn}.saveBeans(), beans count: {c}".format(
cn=type(self).__name__, c=len(beans)))
session = None
try:
session = self.sessionFactory.GetSession()
session.add_all(beans)
session.commit()
except Exception as e:
self.log.error(str(e))
finally:
session.close()
self.log.info("[END] {cn}.saveBeans(), exec TIME: {t} ms.".format(
cn=type(self).__name__, t=p.executeTime()))
def updateBeans(self, key, value):
p = Profiler()
self.log.info(
"[START] {cn}.updateBeans()".format(cn=type(self).__name__))
session = None
try:
session = self.sessionFactory.GetSession()
#更新資料
session.filter(key).update(value)
session.commit()
except Exception as e:
self.log.error(str(e))
finally:
session.close()
self.log.info("[END] {cn}.updateBeans(), exec TIME: {t} ms.".format(
cn=type(self).__name__, t=p.executeTime()))
def Get_up_Stk(self, d, up_or_down=1):
p = Profiler()
self.log.info(
"[START] {cn}.Get_up_Stk(), date: '{date}', up_or_down: {u}".
format(cn=type(self).__name__, date=d, u=up_or_down))
sql = ""
if up_or_down >= 0:
sql = "select * from tw_stock_quote where updown_limit >= 0"
else:
sql = "select * from tw_stock_quote where updown_limit < 0"
sql = sql + " and q_date = '{d}'"
sql = sql.format(d=d)
self.log.debug("sql: " + sql)
session = self.sessionFactory.GetSession()
rs = session.execute(sql)
session.close()
self.log.info(
"[END] {cn}.Get_up_Stk(), exec TIME: {t} ms, date: '{date}', up_or_down: {u}"
.format(cn=type(self).__name__,
date=d,
u=up_or_down,
t=p.executeTime()))
return rs
def updateEWMA(self, stk_id, q_date, A, ewma):
p = Profiler()
self.log.info(
"[START] {cn}.updateEWMA(), stk_id: {id}, q_date: {d}, A: {A}, EWMA: {ewma}"
.format(cn=type(self).__name__,
id=stk_id,
d=q_date,
A=A,
ewma=ewma))
sql = "update tw_stock_quote set A = {A}, ewma = {ewma} where stk_id = '{s}' and q_date = '{d}'".format(
A=A, ewma=ewma, s=stk_id, d=q_date)
session = self.sessionFactory.GetSession()
session.execute(sql)
session.commit()
session.close()
self.log.info(
"[end] {cn}.updateEWMA() exec TIME: {t} ms, stk_id: {id}, q_date: {d}, A: {A}, EWMA: {ewma}"
.format(cn=type(self).__name__,
id=stk_id,
d=q_date,
A=A,
ewma=ewma,
t=p.executeTime()))
def ReverseQuote(self, stk_id = None):
p = Profiler()
self.log.info("[START] {cn}.ReverseQuote(), stk_id: {sn}".format(cn = type(self).__name__, sn = stk_id))
stks = self.gdo.Get_Stock(stk_id)
preStkId = ""
preDate = ""
for stk in stks:
sid =stk["STOCK_ID"]
si = StockInfo(sid)
ss = self.gdo.Get_Last_Quote(sid)
fo = ss.fetchone()
start = None
end = dt.datetime.now().strftime("%Y%m%d")
if fo is None:
#無該商品報價存在db，從頭開始抓
start = self.START_QUOTE_DATE
else:
#該商品已有報價存在db中且不是當日(今天已抓過)，從隔一天開始抓
if fo["q_date"].strftime("%Y%m%d") != dt.datetime.now().strftime("%Y%m%d"):
start = fo["q_date"] + dt.timedelta(days=1)
if start is None:
#要確定盤後才可以抓取
self.log.info("stk: {s} >> {d} 已抓取過盤後資料，往下一檔前進".format(s = sid, d = start))
continue
d1 = dt.datetime.combine(start, dt.time())
d2 = dt.datetime.now().replace(hour = 16, minute = 0, second = 0)
self.log.debug("d1: " + d1.isoformat())
self.log.debug("d2: " + d2.isoformat())
if d1 > d2:
continue
self.log.debug("to get stk: {id} quotes between {s} and {e}".format(id = sid, s = start, e = end))
id, qs = si.GetQuote_from_Yahoo(start, end)
if qs is None:
#Yahoo中無該檔股票
continue
beans = []
for q in qs:
quote_date = dt.datetime.strptime(q["date"], "%Y-%m-%d")
bean = TWStockQuote(
stk_id = id,
q_date = quote_date,
open = float(q["open"]),
high = float(q["high"]),
low = float(q["low"]),
close = float(q["close"]),
volumn = int(float(q["volumn"]))
)
if preStkId != "" and bean.stk_id == preStkId and bean.q_date == preDate:
continue
else:
preStkId = id
preDate = quote_date
beans.append(bean)
self.gdo.saveBeans(beans)
self.log.info("[END] {cn}.ReverseQuote(), exec TIME: {t} ms, stk_id: {sn}".format(cn = type(self).__name__, t = p.executeTime(), sn = stk_id))