def Comp_gs(code):
t_name_a = 'a' + code
t_name_b = 'b' + code
if me.IsTableExist(t_name_a, G_DBengine) == False:
return
###
sql = 'select * from ' + t_name_a
df = pd.read_sql_query(sql, G_DBengine)
#df = pd.read_sql_table(t_name_a,G_DBengine)
###
num_a = df.index.size
if me.IsTableExist(t_name_b, G_DBengine) == False:
num_b = 0
else:
if Is_ComForAll:
num_b = 0
else:
num_b = me.GetNumFromTable(t_name_b, G_DBengine)
if (num_a == num_b):
print "[Warning]Comp_gs no new data............" + code
return
else:
print "[.......]Comp_gs will add %d days data............" % (num_a -
num_b)
df = df.sort_values('date', ascending=True)
# date 0, close 1, money 2
df['qd30'] = 0.0 #3
df['je30'] = 0.0 #4
df['je250'] = 0.0 #5
df['sy30'] = 0.0 #6
df['sy250'] = 0.0 #7
df['syl30'] = 0.0 #8
df['syl250'] = 0.0 #9
df['tmp'] = 0.0 #10
df['p30'] = 0.0 #11
df['p30d2'] = 0.0 #12
df['p120'] = 0.0 #13
df['p120d2'] = 0.0 #14
Comp_gs_syl30(df, num_b)
Comp_gs_syl250(df, num_b)
Comp_gs_p30d2(df, num_b)
Comp_gs_p120d2(df, num_b)
Comp_gs_QD30(df, num_b)
if Is_ComForAll:
df.to_sql(t_name_b, G_DBengine, if_exists='replace')
else:
if (num_b != 0):
df = df.drop(range(num_b))
df.to_sql(t_name_b, G_DBengine, if_exists='append')
def Chy_date(date2): global HYdf global Hycode is_first = True for i in range(Hycode.index.size): code = Hycode.loc[i, 'code'] t_name = 'b'+code if me.IsTableExist(t_name, G_DBengine) == False: continue sql = 'select * from ' + t_name + ' where date =' + '\'' + str(date2) + '\'' df = pd.read_sql_query(sql, G_DBengine) if is_first == True and df.index.size != 0: df_t = df HYdf = HYdf.append(df) #################### is_first = False elif df.index.size != 0: df_t = df_t.append(df) if is_first == True: return False dm = df_t.mean() ds = df_t.sum() ind = HYdf.index.size - 1 HYdf.iat[ind,md.BI_je30+1] = ds.je30 HYdf.iat[ind,md.BI_je250+1] = ds.je250 HYdf.iat[ind,md.BI_sy30+1] = ds.sy30 HYdf.iat[ind,md.BI_sy250+1] = ds.sy250 if ds.je30 != 0: HYdf.iat[ind,md.BI_syl30+1] = (ds.sy30 / ds.je30) * 100 if ds.je250 != 0: HYdf.iat[ind,md.BI_syl250+1] = (ds.sy250 / ds.je250) * 100 HYdf.iat[ind,md.BI_p30d2+1] = dm.p30d2 HYdf.iat[ind,md.BI_p120d2+1] = dm.p120d2 return True
def down_dk_all(code, i): global G_CODE is_succ = False t_name = 'a' + code if me.IsTableExist(t_name, G_DBengine) == False: s_date = '2013-01-01' else: s_date = me.GetLatestDateFromTable(t_name, G_DBengine) while is_succ == False: try: df = ts.get_h_data(code, autype='hfq', start=s_date) if str(type(df)) == '<class \'pandas.core.frame.DataFrame\'>': print s_date, df.index.size del df['open'] del df['high'] del df['low'] del df['volume'] df['amount'] = df['amount'] / 10000 df = df.drop(df.index.values[df.index.size-1]) G_CODE.iat[i,4] = df.index.size if df.index.size != 0: df.to_sql(t_name, G_DBengine, if_exists='append') is_succ = True except ValueError, e: print 'ValueError:', e
def ShowMoney(com1, com2, com3):
if True:
tname = 'money'
if me.IsTableExist(tname, G_DBengine) == False:
print 'No table ....%s' % tname
return
df = pd.read_sql_table(tname, G_DBengine)
df = df.sort_values('num', ascending=False)
me.PinghuaDF(df, 18, 5)
me.PinghuaDF(df, 19, 5)
me.PinghuaDF(df, 20, 5)
df[['fm2', 'fm1']].plot(linewidth=LW)
df['m1dm2'].plot(color='red', secondary_y=True, linewidth=LW)
plt.title(com1 + ' ' + com2 + ' ' + str(max(df['month'])))
View_10X(plt, df, '321')
#plt.xticks(range(0, df.index.size, df.index.size/10))
#ax=plt.gca()
#size = df.index.size
#d_size = size / 9
#ax.set_xticklabels([df.iat[size-1,1], df.iat[size-1-d_size,1], df.iat[size-1-d_size*2,1], df.iat[size-1-d_size*3,1], df.iat[size-1-d_size*4,1], \
# df.iat[size-1-d_size*5,1],df.iat[size-1-d_size*6,1], df.iat[size-1-d_size*7,1], df.iat[size-1-d_size*8,1] ,df.iat[0,1]])
plt.show()
plt.close()
def read_fin(year, season): global Fin t_name = 'f'+str(year)+str(season) print t_name if me.IsTableExist(t_name, G_DBengine) == False: return df = pd.read_sql_table(t_name,G_DBengine) Fin = Fin.append(df)
def fin_read_hy(hy): is_first = True for i in range(Codes.index.size): code = Codes.loc[i, 'code'] t_name = 'f'+code if me.IsTableExist(t_name,G_DBengine) == False: continue if (is_first): fin = pd.read_sql_table(t_name,G_DBengine) is_first = False else: df = pd.read_sql_table(t_name,G_DBengine) fin = fin.append(df) print '...fin_read_hy:' + hy + '.......[%d of %d]'%(i,Codes.index.size) return fin
Fin.iat[i,G_jzc] = me.MyDiv(Fin.iat[i,6], Fin.iat[i,3]) * 100 + 1
if is_debug:
fin_com_gs('000002')
else:
t_name_f = ' '
for i in range(Codes.index.size):
code = Codes.loc[i, 'code']
print me.GetNowTime() + 'start fin compute.........[%d of %d] '%(i,Codes.index.size) , code
t_name_f = 'f'+code
fin_com_gs(code)
#compute hy fin
if opr_type == 2 or opr_type == 9:
for i in range(len(md.HYL)):
t_name = 'hy' + md.HYL[i][0]
if me.IsTableExist(t_name,G_DBengine) == False:
print '[Warning] %s is not exist.....'%t_name
continue
if me.IsTableExist('fhy'+md.HYL[i][0], G_DBengine) == True and is_append_hy == True:
print '[Warning] %s is alread exist for Append mode.....'%md.HYL[i][0]
continue
Codes = pd.read_sql_table(t_name,G_DBengine)
Fin = fin_read_hy(md.HYL[i][1])
print md.HYL[i][1] + ' size ....', Fin.index.size
hy_fin = fin_com_hy(md.HYL[i][0])
hy_fin.to_sql('fhy'+md.HYL[i][0], G_DBengine, if_exists='replace')
from sqlalchemy import create_engine
import tushare as ts
import numpy as np
import mylib as me
import mydef as md
global G_DBengine
G_DBengine = create_engine('mysql://*****:*****@127.0.0.1/mystock?charset=utf8')
global Codes
Codes = pd.read_csv("..\\data\\codename.txt", converters={'code':str})
Codes['qd30'] = 0.0
Codes['qdpm'] = 0.0
for i in range(Codes.index.size):
code = Codes.loc[i, 'code']
print me.GetNowTime() + 'read qd data .........[%d of %d] '%(i,Codes.index.size) , code
t_name = 'b' + code
if me.IsTableExist(t_name, G_DBengine) == False:
Codes.loc[i, 'qd30'] = -99.99
print '......' + t_name + ' is not exist....'
continue
sql = 'SELECT * FROM ' + t_name + ' ORDER BY date DESC limit 1'
df = pd.read_sql_query(sql, G_DBengine)
Codes.loc[i, 'qd30'] = df.loc[0,'qd30']
Codes = Codes.sort_values('qd30', ascending=False)
for i in range(Codes.index.size):
Codes.iat[i, 7] = 100 - float(i) / Codes.index.size * 100
Codes.to_csv('../data/qdpm.txt')
def Chy_Com_hy(hyname): global HYdf global Hycode is_table_exist = False t_name = 'hyb' + hyname if me.IsTableExist(t_name, G_DBengine) == True: is_table_exist = True for i in range(Hytime.index.size): date2 = Hytime.iat[i, 0] print 'Chy_Com_hy.....%s...'%hyname, Hycode.index.size, date2 if hyname == '102' and i < 500: continue if is_table_exist: if me.Is_dateInTable(t_name, G_DBengine, date2) == True: continue if Chy_date(date2) == True: HYdf.to_sql(t_name, G_DBengine, if_exists='append') is_table_exist = True HYdf = HYdf.drop(0) def Chy_date(date2): global HYdf global Hycode is_first = True for i in range(Hycode.index.size): code = Hycode.loc[i, 'code'] t_name = 'b'+code if me.IsTableExist(t_name, G_DBengine) == False: continue sql = 'select * from ' + t_name + ' where date =' + '\'' + str(date2) + '\'' df = pd.read_sql_query(sql, G_DBengine) if is_first == True and df.index.size != 0: df_t = df HYdf = HYdf.append(df) #################### is_first = False elif df.index.size != 0: df_t = df_t.append(df) if is_first == True: return False dm = df_t.mean() ds = df_t.sum() ind = HYdf.index.size - 1 HYdf.iat[ind,md.BI_je30+1] = ds.je30 HYdf.iat[ind,md.BI_je250+1] = ds.je250 HYdf.iat[ind,md.BI_sy30+1] = ds.sy30 HYdf.iat[ind,md.BI_sy250+1] = ds.sy250 if ds.je30 != 0: HYdf.iat[ind,md.BI_syl30+1] = (ds.sy30 / ds.je30) * 100 if ds.je250 != 0: HYdf.iat[ind,md.BI_syl250+1] = (ds.sy250 / ds.je250) * 100 HYdf.iat[ind,md.BI_p30d2+1] = dm.p30d2 HYdf.iat[ind,md.BI_p120d2+1] = dm.p120d2 return True ########################################################## for i in range(len(md.HYL)): t_name = 'hy' + md.HYL[i][0] if me.IsTableExist(t_name,G_DBengine) == False: print '[Warning] %s is not exist.....'%t_name continue Hycode = pd.read_sql_table(t_name,G_DBengine) if Hycode.index.size == 0: continue Chy_Com_hy(md.HYL[i][0])
def ShowGS(com1, com2, com3):
df2 = Codes[Codes['code'].isin([com1])]
print df2
if com2 == 'syl30':
tname = 'b' + com1
if me.IsTableExist(tname, G_DBengine) == False:
print 'No table ....%s' % tname
return
df = pd.read_sql_table(tname, G_DBengine)
if df.index.size > 250:
df1 = df.drop(range(df.index.size - 250))
me.PinghuaDF(df1, md.BI_syl30 + 1, 5)
plt.title(com1 + ' ' + com2 + ' ' + str(max(df1['date'])))
plt.fill_between(df1.index,
df1['syl30'],
0,
where=df1['syl30'] > 0,
facecolor='red')
plt.fill_between(df1.index,
df1['syl30'],
0,
where=df1['syl30'] <= 0,
facecolor='green')
elif com2 == 'syl250':
tname = 'b' + com1
if me.IsTableExist(tname, G_DBengine) == False:
print 'No table ....%s' % tname
return
df = pd.read_sql_table(tname, G_DBengine)
me.PinghuaDF(df, md.BI_syl250 + 1, 30)
plt.fill_between(df.index,
df['syl250'],
0,
where=df['syl250'] > 0,
facecolor='red')
plt.fill_between(df.index,
df['syl250'],
0,
where=df['syl250'] <= 0,
facecolor='green')
plt.title(com1 + ' ' + com2 + ' ' + str(max(df['date'])))
View_10X(plt, df, '123')
elif com2 == 'hb':
tname = 'f' + com1
if me.IsTableExist(tname, G_DBengine) == False:
print 'No table ....%s' % tname
return
df = pd.read_sql_table('f' + com1, G_DBengine)
df[1:df.index.size][['sjsrhb', 'sjlrhb']].plot(kind='bar',
color={'red', 'green'})
df[1:df.index.size]['nhgdqyl'].plot(color='blue',
secondary_y=True,
linewidth=LW)
plt.title(com1 + ' ' + com2 + ' ' +
str(df.loc[df.index.size - 1, 'year']) + ' ' +
str(df.loc[df.index.size - 1, 'season']))
elif com2 == 'sr':
tname = 'f' + com1
if me.IsTableExist(tname, G_DBengine) == False:
print 'No table ....%s' % tname
return
df = pd.read_sql_table('f' + com1, G_DBengine)
df[1:df.index.size]['sjsr'].plot(kind='bar', color='green')
df[1:df.index.size]['sjlr'].plot(color='red',
secondary_y=True,
linewidth=LW)
plt.title(com1 + ' ' + com2 + ' ' +
str(df.loc[df.index.size - 1, 'year']) + ' ' +
str(df.loc[df.index.size - 1, 'season']))
else:
print '[Error] input error ...'
return
plt.show()
plt.close()
def ShowHY(com1, com2, com3):
if com2 == 'syl30':
tname = 'hyb' + com1
if me.IsTableExist(tname, G_DBengine) == False:
print 'No table ....%s' % tname
return
df = pd.read_sql_table(tname, G_DBengine)
if df.index.size > 250:
df = df.drop(range(df.index.size - 250))
me.PinghuaDF(df, md.BI_syl30 + 2, 5)
plt.fill_between(df.index,
df['syl30'],
0,
where=df['syl30'] > 0,
facecolor='red')
plt.fill_between(df.index,
df['syl30'],
0,
where=df['syl30'] <= 0,
facecolor='green')
plt.title(com1 + ' ' + com2 + ' ' + str(max(df['date'])))
elif com2 == 'syl250':
tname = 'hyb' + com1
if me.IsTableExist(tname, G_DBengine) == False:
print 'No table ....%s' % tname
return
df = pd.read_sql_table(tname, G_DBengine)
me.PinghuaDF(df, md.BI_syl250 + 2, 30)
plt.fill_between(df.index,
df['syl250'],
0,
where=df['syl250'] > 0,
facecolor='red')
plt.fill_between(df.index,
df['syl250'],
0,
where=df['syl250'] <= 0,
facecolor='green')
plt.title(com1 + ' ' + com2 + ' ' + str(max(df['date'])))
View_10X_Hy(plt, df, '123')
elif com2 == 'p120d':
tname = 'hyb' + com1
if me.IsTableExist(tname, G_DBengine) == False:
print 'No table ....%s' % tname
return
df = pd.read_sql_table(tname, G_DBengine)
me.PinghuaDF(df, md.BI_p120d2 + 2, 30)
plt.fill_between(df.index,
df['p120d2'],
0,
where=df['p120d2'] > 0,
facecolor='red')
plt.fill_between(df.index,
df['p120d2'],
0,
where=df['p120d2'] <= 0,
facecolor='green')
plt.title(com1 + ' ' + com2 + ' ' + str(max(df['date'])))
View_10X_Hy(plt, df, '123')
elif com2 == 'p30d':
tname = 'hyb' + com1
if me.IsTableExist(tname, G_DBengine) == False:
print 'No table ....%s' % tname
return
df = pd.read_sql_table(tname, G_DBengine)
if df.index.size > 250:
df = df.drop(range(df.index.size - 250))
me.PinghuaDF(df, md.BI_p30d2 + 2, 5)
plt.fill_between(df.index,
df['p30d2'],
0,
where=df['p30d2'] > 0,
facecolor='red')
plt.fill_between(df.index,
df['p30d2'],
0,
where=df['p30d2'] <= 0,
facecolor='green')
plt.title(com1 + ' ' + com2 + ' ' + str(max(df['date'])))
elif com2 == 'sr':
tname = 'fhy' + com1
if me.IsTableExist(tname, G_DBengine) == False:
print 'No table ....%s' % tname
return
df = pd.read_sql_table(tname, G_DBengine)
df[1:df.index.size]['sjsr'].plot(kind='bar', color='green')
df[1:df.index.size]['sjlr'].plot(color='red',
secondary_y=True,
linewidth=LW)
plt.title(com1 + ' ' + com2 + ' ' +
str(df.loc[df.index.size - 1, 'year']) + ' ' +
str(df.loc[df.index.size - 1, 'season']))
elif com2 == 'gbsr':
tname = 'fhy' + com1
if me.IsTableExist(tname, G_DBengine) == False:
print 'No table ....%s' % tname
return
df = pd.read_sql_table(tname, G_DBengine)
df[1:df.index.size]['sjsrhb'].plot(kind='bar', color='green')
df[1:df.index.size]['sjlrhb'].plot(color='red',
secondary_y=True,
linewidth=LW)
plt.title(com1 + ' ' + com2 + ' ' +
str(df.loc[df.index.size - 1, 'year']) + ' ' +
str(df.loc[df.index.size - 1, 'season']))
else:
print '[Error] input error ...'
return
plt.show()
plt.close()