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 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 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_data_hist_fq():
print('start refleshing hfq & qfq values on table [data_hist] ...')
ps = Postgresql()
rs = ps.fetchall("SELECT DISTINCT code FROM data_hist ORDER BY code;")
j, jj, s = 0, 0, len(rs)
for r in rs:
starttime = datetime.datetime.now()
c = r[0]
j += 1
date = '1975-09-02'
#r = ps.fetchone("SELECT date FROM data_hist WHERE code='%s' AND hfq>0 ORDER BY date DESC LIMIT 1;" % c)
r = ps.fetchone(
"SELECT max(date) FROM data_hist WHERE code='%s' AND hfq>0;" % c)
if r[0] is not None: # if r: = if r is not None:
date = r[0]
else:
tmp = ps.fetchone(
"SELECT min(date) FROM data_hist WHERE code='%s';" % c)
if tmp[0] is None: continue
date = tmp[0]
ps.execute(
"UPDATE data_hist SET hfq=1 WHERE code='%s' AND date='%s';" %
(c, date))
# start set hfq value
b_cls, b_hfq, hfq, i, sql = 0, 0, 0, 0, ''
rs = ps.fetchall(
"SELECT date, close, price_change, hfq FROM data_hist WHERE code='%s' AND date>='%s' ORDER BY date;"
% (c, date))
if rs is not None:
for R in rs:
i += 1
cls, pcg = float(R[1]), float(R[2])
if i == 1:
hfq = R[3]
else:
if cls - pcg - b_cls < -0.02:
hfq = b_cls / (cls - pcg) * b_hfq
else:
hfq = b_hfq
sql += "UPDATE data_hist SET hfq=%f WHERE code='%s' AND date='%s';" % (
hfq, c, R[0])
b_cls = cls
b_hfq = hfq
# start set qfq value
mr = ps.fetchone(
"SELECT hfq FROM data_hist WHERE code='%s' AND qfq is null ORDER BY date DESC"
% c) #second newly hfq
if mr is not None:
if hfq == mr[0]:
sql += "UPDATE data_hist SET qfq=hfq/%f WHERE code='%s' AND qfq is null;" % (
hfq, c)
else:
sql += "UPDATE data_hist SET qfq=hfq/%f WHERE code='%s';" % (
hfq, c)
if sql != '': ps.execute(sql)
endtime = datetime.datetime.now()
print(
'[%d/%d=%.1f%%] [%s] hfq & qfq values is computed and saved! time=[%s]'
% (j, s, float(j) / float(s) * 100, c, endtime - starttime))
print('all hfq & qfq data on table [data_hist] computed and saved!')