def reflesh_profit_prices(codes=getinfo.get_codes(), start='2015-05-01'):
print('start reflashing newly hightest and lowest price on table [profit]')
ps = Postgresql()
SQL, i, s = '', 0, len(codes)
for c in codes:
i += 1
sql = "SELECT p_change, close, volume, turnover FROM data_hist WHERE code='%s' AND date>='%s' ORDER BY date DESC LIMIT 1" % (
c, start)
r = ps.fetchone(sql)
if r is None: continue
pc, np, vol, turn = r[0], r[1], r[2], r[3]
sql = "SELECT max(high*qfq), min(low*qfq) FROM data_hist WHERE code='%s' AND date>='%s'" % (
c, start)
r = ps.fetchone(sql)
hp, lp = r[0], r[1]
SQL += "UPDATE profit SET hp=%.2f, lp=%.2f, np=%.2f, lhr=%f, nlr=%f, nhr=%f, vol=%f, turn=%f, pc=%f "\
"WHERE code='%s';"%(hp, lp, np, lp/hp, (np-lp)/lp, np/hp, vol, turn, pc, c)
print('[%d/%d=%.1f%%] [%s] stock prices info computed!' %
(i, s, float(i) / float(s) * 100, c))
ps.execute(SQL)
print('all stock newly prices info is saved into table [profit]')
fb.write('start reflashing pe & pb ... ')
SQL = "UPDATE profit SET pb=np/bvps WHERE bvps!=0;"
SQL += "UPDATE profit SET pe=np/esp WHERE esp!=0;"
ps.execute(SQL)
print(' is done!')
def fetch_data_hist():
today = datetime.date.today()
weekday = today.weekday()
if weekday == 5: today -= datetime.timedelta(days=1)
elif weekday == 6: today -= datetime.timedelta(days=2)
ps = Postgresql()
cs = getinfo.get_codes(fields=['tradable'])
i, s = 0, len(cs)
for c in cs:
starttime = datetime.datetime.now()
i += 1
r = ps.fetchone("SELECT max(date) FROM data_hist WHERE code='%s'" % c)
if r[0]:
date = r[0] + datetime.timedelta(days=1)
if date > today:
print('[%d/%d=%.1f%%] %s price is newly!' %
(i, s, float(i) / float(s) * 100, c))
continue
try:
df = ts.get_hist_data(c, start=str(date))
except Exception, data:
print(data)
if df is None:
print('[%d/%d=%.1f%%] %s price is newly!' %
(i, s, float(i) / float(s) * 100, c))
continue
else:
try:
df = ts.get_hist_data(c)
except Exception, data:
print(data)
def fetch_data_hist():
today = datetime.date.today()
weekday = today.weekday()
if weekday == 5: today -= datetime.timedelta(days=1)
elif weekday == 6: today -= datetime.timedelta(days=2)
ps = Postgresql()
cs = getinfo.get_codes(fields=['tradable'])
i, s = 0, len(cs)
for c in cs:
starttime = datetime.datetime.now()
i += 1
r = ps.fetchone("SELECT max(date) FROM data_hist WHERE code='%s'" % c)
if r[0]:
date = r[0] + datetime.timedelta(days=1)
if date > today:
print('[%d/%d=%.1f%%] %s price is newly!' %
(i, s, float(i) / float(s) * 100, c))
continue
try:
df = ts.get_hist_data(c, start=str(date))
except Exception as data:
print(data)
if df is None:
print('[%d/%d=%.1f%%] %s price is newly!' %
(i, s, float(i) / float(s) * 100, c))
continue
else:
try:
df = ts.get_hist_data(c)
except Exception as data:
print(data)
df.insert(0, 'code', pd.Series([c], index=df.index))
df.to_sql('data_hist', ENGINE, if_exists='append')
endtime = datetime.datetime.now()
print('[%d/%d=%.1f%%] fetching %s stock prices! time=[%s]' %
(i, s, float(i) / float(s) * 100, c, endtime - starttime))
ps.close()
print('stock histroy prices is fetched!')
def reflesh_profit_peaks_botts():
ps = Postgresql()
cs = getinfo.get_codes()
SQL, i, s = '', 0, len(cs)
for c in cs:
i += 1
r = ps.fetchone(
"SELECT count(ispeak), count(isbott) FROM data_hist WHERE code='%s';"
% c)
ra = ps.fetchone(
"select count(*) from data_hist where code='%s' and date>= (select max(date) from data_hist where code='%s' and ispeak)"
% (c, c))
rb = ps.fetchone(
"select count(*) from data_hist where code='%s' and date>= (select max(date) from data_hist where code='%s' and isbott)"
% (c, c))
SQL += "UPDATE profit SET pks=%f, bts=%f, pds=%f, bds=%f WHERE code='%s';" % (
r[0], r[1], ra[0], rb[0], c)
print("[%d/%d=%.1f%%] [%s] the number of peak or bott is counted!" %
(i, s, float(i) / float(s) * 100, c))
ps.execute(SQL)
ps.close()
print('is ok!')
def identify_data_hist_price_wave(periods=10, start='2015-05-01'):
ps = Postgresql()
cs = getinfo.get_codes()
i, s = 0, len(cs)
for c in cs:
starttime = datetime.datetime.now()
i += 1
sql = "SELECT date, close, qfq, high, low FROM data_hist WHERE code='%s' AND date>='%s' ORDER BY date DESC" % (
c, start)
rs = ps.fetchall(sql)
rows = len(rs)
if rows <= periods:
continue
sql = "UPDATE data_hist SET ispeak=null, isbott=null WHERE (ispeak OR isbott) AND code='%s';" % c
for j in range(rows - periods):
ispeak = isbott = True
for k in range(1, periods + 1):
j_k = max([j - k, 0])
hn = rs[j][3] * rs[j][2]
hb = rs[j + k][3] * rs[j + k][2]
ha = rs[j_k][3] * rs[j_k][2]
ln = rs[j][4] * rs[j][2]
lb = rs[j + k][4] * rs[j + k][2]
la = rs[j_k][4] * rs[j_k][2]
if hn <= hb or hn < ha: ispeak = False
if ln >= lb or ln > la: isbott = False
if ispeak:
sql += "UPDATE data_hist SET ispeak=true WHERE code='%s' AND date='%s';" % (
c, rs[j][0])
if isbott:
sql += "UPDATE data_hist SET isbott=true WHERE code='%s' AND date='%s';" % (
c, rs[j][0])
ps.execute(sql)
endtime = datetime.datetime.now()
print(
"[%d/%d=%.1f%%] [%s] ispeak and isbott are identified! time=[%s]" %
(i, s, float(i) / float(s) * 100, c, endtime - starttime))
ps.close()
print('is ok')