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_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 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_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_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_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 EWMA(self, stk_id):
p = Profiler()
self.log.info(p.startLog("stk_id: {}", stk_id))
last_quote_obj = self.gdo.Get_Last_EWMA(stk_id)
Ay = last_quote_obj["A"]
lambdaa = 0.06
ls = self.gdo.Get_Noncalcute_EWMA(stk_id)
for obj in ls:
r = obj["updown_limit"]
Ai = round(pow(r, 2) * lambdaa + Ay * (1-lambdaa), 2)
ewma = round(pow(Ai*250, 0.5), 2)
self.gdo.updateEWMA(stk_id, obj["q_date"], Ai, ewma)
self.log.info(p.endLog("stk_id: {}", stk_id))
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 Get_Stock(self, stk_id=None):
p = Profiler()
#self.log.info("[START] {cname}.Get_Stock(), stk_id: {stk_id}".format(cname = type(self).__name__, stk_id = stk_id))
self.log.info(p.startLog("stk_id: {}", stk_id))
sql = "select distinct a.* from TW_STOCK_LIST a left join tw_stock_quote b on a.STOCK_ID = b.stk_id where a.DELISTING_DATE is null "
if stk_id is not None:
sql = sql + (" and a.STOCK_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(), exec TIME: {t} ms., stk_no: {sn}".format(cname = type(self).__name__, t = p.executeTime(), sn = stk_id))
self.log.info(p.endLog("stk_id: {}", stk_id))
return stk_list
def Save_Daily_Exchange(self, a, b, c, d, e, f, g, h, i, j):
p = Profiler()
self.log.info(
p.startLog(
"商品代碼: {}, 交易日期: {}, 券商代碼: {}, 券商名稱: {}, 買量: {}, 賣量: {}, 買價: {}, 賣價: {}, 買賣超: {}, 均價: {}",
a, b, c, d, e, f, g, h, i, j))
sql = "insert into tw_stock_daily_exchange (stk_id, date, company_id, company_name, buy_quantity, sell_quantity, buy_price, sell_price, over_quantity, avg_price) values ('{a}', '{b}', '{c}', '{d}', '{e}', '{f}', '{g}', '{h}', '{i}', '{j}')"
sql = sql.format(a=a, b=b, c=c, d=d, e=e, f=f, g=g, h=h, i=i, j=j)
session = self.sessionFactory.GetSession()
session.execute(sql)
session.commit()
session.close()
self.log.info(
p.endLog(
"商品代碼: {}, 交易日期: {}, 券商代碼: {}, 券商名稱: {}, 買量: {}, 賣量: {}, 買價: {}, 賣價: {}, 買賣超: {}, 均價: {}",
a, b, c, d, e, f, g, h, i, j))
pass
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 GetExangeDailyFromWantgoo(self, d, cb=None):
p = Profiler()
self.log.info(p.startLog("date: {}", d))
if d is None:
d = datetime.datetime.now().strftime("%Y%m%d")
url = "https://www.wantgoo.com/stock/astock/agentstat_ajax?StockNo={id}&Types=3.5&StartDate={ds}&EndDate={de}&Rows=35"
url = url.format(id=self.stock_no, ds=d, de=d)
self.log.debug("get wantgoo daily exchange url: " + url)
resp = requests.get(url)
dailyReport = resp.json()
code = dailyReport["code"]
message = dailyReport["message"]
returnValues = json.loads(dailyReport["returnValues"])
if cb is not None:
cb(returnValues)
else:
return returnValues
def Get_Quote_from_TWSE(self, yyyymm, callback=None):
p = Profiler()
self.log.info(
'[START] {cname}.Get_Quote_from_TWSE(), stk_id: {stk}, date: {d}'.
format(cname=type(self).__name__, stk=self.stock_no, d=yyyymm))
ymd = yyyymm + '01'
url = self.STOCK_DAY.format(d=ymd, stk=self.stock_no)
try:
resp = requests.get(url)
json = resp.json()
self.log.debug(json)
stat = json['stat']
self.log.debug('url: {url}, stat: {stat}'.format(url=url,
stat=stat))
if stat != 'OK':
self.log.info('stk: {stk}, ymd: {ymd} IS NOT EXIST'.format(
stk=self.stock_no, ymd=ymd))
return None
else:
resp_date = json['date']
data_json = json['data']
self.log.debug('date: {d}, data size: {s}'.format(
s=len(data_json), d=resp_date))
rs = []
for data in data_json:
dt = data[0]
volumn = data[1]
price = data[2]
o = data[3]
h = data[4]
l = data[5]
c = data[6]
p_distance = data[7] if data[7].find("X") == -1 else 0
d_count = data[8]
dd = {
'date': dt,
'volumn': volumn,
'price': price,
'open': o,
'high': h,
'low': l,
'close': c,
'p_distance': p_distance,
'total_count': d_count
}
rs.append(dd)
if (callback is not None):
callback(self.stock_no, rs)
else:
return self.stock_no, rs
except:
self.log.error('TESE拒絕連線，等待' + str(self.MAX_REPEAT_TIMES) +
'分鐘後再試')
time.sleep(self.MAX_REPEAT_TIMES * 60)
self.Get_Quote_from_TWSE(yyyymm, callback)
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 Calcute_UpDown(self, stk_id):
p = Profiler()
#self.log.info("[START] {cn}.Calcute_UpDown(), stk_id: {id}".format(cn = type(self).__name__, id = stk_id))
self.log.info(p.startLog("stk_id: {}", stk_id))
#取出未計算漲跌的資料
ls = self.gdo.Get_Uncalcute_Updown(stk_id)
#取出最後一筆有計算漲跌的資料
updownObj = self.gdo.Get_Last_Updown(stk_id)
close = updownObj["close"]
for obj in ls:
distance = obj["close"] - close
updown = distance
updown_limit = round((distance / close) * 100, 2)
self.log.debug("stk_id: {id}, q_date: {qd}, updown: {upd}, updown_limit: {updl}".format(id = stk_id, qd = obj["q_date"], upd = updown, updl = updown_limit))
self.gdo.update_updown_value(
stk_id = stk_id,
q_date = obj["q_date"].strftime("%Y-%m-%d"),
updown = updown,
updown_limit = updown_limit
)
close = obj["close"]
self.log.info(p.endLog("stk_id: {}", stk_id))
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 GetExchangeDailyReport(self, stk_id, d):
p = Profiler()
self.log.info(p.startLog("商品代碼: {}, 交易日期", stk_id, d))
stk_spider = StockInfo(stk_id)
rs = stk_spider.GetExangeDailyFromWantgoo(d)
for r in rs:
c1 = r["券商名稱"]
bq1 = r["買量"]
sq1 = r["賣量"]
bp1 = r["買價"]
sp1 = r["賣價"]
overbs1 = r["買賣超"]
avg1 = r["均價"]
c2 = r["券商名稱2"]
bq2 = r["買量2"]
sq2 = r["賣量2"]
bp2 = r["買價2"]
sp2 = r["賣價2"]
overbs2 = r["買賣超2"]
avg2 = r["均價2"]
if c1 is not None:
id = c1[-5:-1]
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))
def GetExangeDailySummaryFromWantgoo(self, d, cb=None):
p = Profiler()
url = "https://www.wantgoo.com/stock/astock/agentstat_total_ajax?StockNo={id}&StartDate={d}&EndDate={d}&Rows=35"
self.log.info(p.startLog("date: {}", d))
if d is None:
d = datetime.datetime.now().strftime("%Y%m%d")