def select_data(self):
zhuang_sql = "SELECT stock_id,stock_name,zhuang_section " \
" FROM com_zhuang " \
" WHERE zhuang_long > 0 "
self.zhuang_df = pub_uti_a.creat_df(zhuang_sql)
self.id_set = set(self.zhuang_df['stock_id'].to_list())
data_sql = "SELECT * " \
" FROM stock_trade_data " \
" WHERE stock_id "
self.data_df = pub_uti_a.creat_df(data_sql,ascending=True)
self.create_section()
def create_tradedate_list(self):
sql = "select distinct trade_date from stock_trade_data where trade_date >= '{}' and trade_date <= '{}'".format(
self.start_date,self.end_date
)
trade_data_df = pub_uti_a.creat_df(sql,ascending=True)
self.trade_date_list = trade_data_df['trade_date'].to_list()
print('trade_date_list:',self.trade_date_list)
def main():
start_t = None
end_t = None
if start_t != None and end_t != None:
sql = "SELECT trade_code,stock_id,stock_name,trade_date,open_price,close_price,high_price,low_price,increase FROM stock_trade_data \
where trade_date >= '{0}' and trade_date <= '{1}'".format(
start_t, end_t)
else:
sql = "SELECT trade_code,stock_id,stock_name,trade_date,open_price,close_price,high_price,low_price,increase " \
"FROM stock_trade_data "
#test
sql = "SELECT trade_code,stock_id,stock_name,trade_date,open_price,close_price,high_price,low_price,increase " \
"FROM stock_trade_data where stock_id = '000001'"
df = pub_uti_a.creat_df(sql, ascending=True)
id_set = set(df['stock_id'].to_list())
signal_df = pd.DataFrame(columns=[
'stock_id', 'stock_name', 'grade', 'trade_date', 'trade_code', 'conf',
'mark'
])
count = 1
for id in id_set:
if id[0:3] in ('688', '300'):
continue
print('id', count, id)
count += 1
single_df = df[df.stock_id == id]
single_df.reset_index(inplace=True, drop=True)
signal_df = create_signal(single_df, signal_df)
signal_df.to_csv('../factor_verify_res/limit_fall_signal_test.csv',
index=False)
def select_signal_from_db(self):
sql = "select trade_code,trade_date,stock_id,stock_name,grade" \
" from limit_up_single " \
" where trade_date >= '{}' and trade_date <='{}' and grade > 0".format(self.start_date,self.end_date)
#test
# sql = "select trade_code,trade_date,stock_id,stock_name,grade" \
# " from limit_up_single " \
# " where trade_date >= '{}' and trade_date <='{}' and grade > 0 and stock_id='002528'".format(self.start_date,self.end_date)
# sql = "select trade_code,trade_date,stock_id,stock_name,0 as grade " \
# " from stock_trade_data " \
# " where trade_date >= '{}' and trade_date <='{}' ".format(self.start_date,self.end_date)
#验证新版单涨停信号
# sql = "select trade_code,trade_date,stock_id,stock_name,grade " \
# " from limit_up_single_validate " \
# " where trade_date >= '{}' and trade_date <='{}' and grade > 1 and type = 'wave' ".format(self.start_date,self.end_date)
#验证热门回撤
sql = "select trade_code,trade_date,stock_id,stock_name,grade" \
" from remen_retracement " \
" where trade_date >= '{}' and trade_date <='{}' and grade > 0".format(self.start_date, self.end_date)
self.df = pub_uti_a.creat_df(sql, ascending=True)
self.df['mark'] = ''#占位
print('signals select completed.', datetime.datetime.now())
def main(num, start_t, end_t):
num = str(num)
if start_t != None and end_t != None:
sql = "SELECT stock_id,stock_name,trade_date,open_price,close_price,high_price,low_price,increase FROM stock_trade_data \
where trade_date >= '{0}' and trade_date <= '{1}' and stock_id like '%{2}'".format(
start_t, end_t, num)
else:
sql = "SELECT stock_id,stock_name,trade_date,open_price,close_price,high_price,low_price,increase " \
"FROM stock_trade_data where stock_id like '%{0}' ".format(num)
df = pub_uti_a.creat_df(sql, ascending=True)
id_set = set(df['stock_id'].to_list())
s = pub_uti_a.save()
for id in id_set:
single_df = df[df.stock_id == id]
single_df.reset_index(drop=True, inplace=True)
print('single_df:', single_df)
single_df = deal_df_data(single_df)
zhuang_date, zhuang_grade, yidong, zhuang_long, max_avg_rate, lasheng_flag, lastest_target = compt_core(
single_df)
insert_sql = "insert into com_zhuang(stock_id,stock_name,zhuang_grade,zhuang_section,yidong,zhuang_long,max_avg_rate,lasheng_flag,lastest_target) " \
"values('{0}','{1}','{2}',\"{3}\",\"{4}\",'{5}','{6}','{7}','{8}') " \
"ON DUPLICATE KEY UPDATE stock_id='{0}',stock_name='{1}',zhuang_grade='{2}',zhuang_section=\"{3}\"," \
"yidong=\"{4}\",zhuang_long = '{5}' ,max_avg_rate = '{6}',lasheng_flag='{7}',lastest_target='{8}' " \
"".format(id, single_df.loc[0,'stock_name'], zhuang_grade, zhuang_date, yidong, zhuang_long, max_avg_rate, lasheng_flag ,lastest_target)
s.add_sql(insert_sql)
s.commit()
def get_vali_stock(self):
sql = "select * from {0} where trade_date >='{1}' and trade_date <='{2}' and grade >= '{3}'".format(
self.vali_table, self.vali_start, self.vali_end, self.grade)
# sql = "select * from {0} where trade_date >='{1}' and trade_date <='{2}' and stock_id = '600844' and grade >= '{3}'".format(
# self.vali_table,self.vali_start,self.vali_end,self.grade)
self.vali_df = pub_uti_a.creat_df(sql)
self.stcoK_set = set(self.vali_df['stock_id'].to_list())
def sel_data_from_db(date):
if date == None:
sql = "select DATE_FORMAT(max(trade_date),'%Y-%m-%d') as last_date from monitor "
date = pub_uti_a.select_from_db(sql=sql)[0][0]
print('date:', date)
sql = "select stock_id,monitor_type from monitor where trade_date = '{}'".format(
date)
type_dic = {
'zhuang': '庄线',
'remen_xiaoboxin': '小波形',
'remen_boxin': '波形',
'single_limit_retra': '单涨停',
'remen_retra': '回撤'
}
stock_dict = {}
for type in type_dic:
stock_dict[type_dic[type]] = []
df = pub_uti_a.creat_df(sql=sql)
df.apply(lambda raw: stock_dict[type_dic[raw['monitor_type']]].append(raw[
'stock_id']),
axis=1)
print(len(stock_dict), stock_dict)
for key in stock_dict:
print(key, len(stock_dict[key]))
return stock_dict
def select_longhu_info(self):
longhu_sql = "select trade_date,stock_id from longhu_info where trade_date >= '{0}' and trade_date <= '{1}'".format(
self.sql_start_date, self.date)
self.longhu = pub_uti_a.creat_df(sql=longhu_sql)
self.longhu['count_longhu'] = 1
self.longhu = self.longhu.groupby(
'stock_id', as_index=False)['count_longhu'].sum()
print(self.longhu)
def select_info(self):
trade_sql = "select stock_id,stock_name,high_price,low_price,close_price,trade_date,wave_data,point_type " \
" FROM stock_trade_data " \
"where trade_date >= '{0}' and trade_date <= '{1}' ".format(self.sql_start_date,self.date)
print('trade_sql:{}'.format(trade_sql))
self.trade_df = pub_uti_a.creat_df(sql=trade_sql)
self.trade_df.fillna('', inplace=True)
self.id_set = set(self.trade_df['stock_id'].tolist())
def select_info(self):
if start_t != None and end_t != None:
sql = "SELECT stock_id,stock_name,trade_date,open_price,close_price,high_price,low_price,increase FROM stock_trade_data \
where trade_date >= '{0}' and trade_date <= '{1}' and stock_id like '%{2}'".format(start_t, end_t,self.num)
else:
sql = "SELECT stock_id,stock_name,trade_date,open_price,close_price,high_price,low_price,increase " \
"FROM stock_trade_data where stock_id like '%{0}' ".format(self.num)
df = pub_uti_a.creat_df(sql, ascending=True)
self.id_set = set(df['stock_id'].to_list())
def select_lasheng(self):
sql = "select T.stock_id,T.stock_name,T.zhangting_count," \
"T.trade_date as lasheng_date,S.trade_date," \
"S.close_price,S.high_price,S.low_price " \
" FROM tongji_dalasheng T " \
" LEFT JOIN stock_trade_data S " \
" ON T.stock_id = S.stock_id " \
" WHERE T.zhangting_count >= {} ".format(self.zhangting_count)#AND T.stock_id = '000665'
self.df = pub_uti_a.creat_df(sql, ascending=True)
def select_info(self):
trade_sql = "select stock_id,stock_name,high_price,low_price,open_price,close_price,trade_date,increase,turnover_rate " \
" FROM stock_trade_data " \
"where trade_date >= '{0}' and trade_date <= '{1}' " \
"AND stock_id NOT LIKE 'ST%' AND stock_id NOT LIKE '%ST%' " \
"AND stock_id NOT like '300%' AND stock_id NOT like '688%'".format(self.sql_start_date,self.date)
print('trade_sql:{}'.format(trade_sql))
self.trade_df = pub_uti_a.creat_df(sql=trade_sql)
self.trade_df.fillna('', inplace=True)
self.id_set = set(self.trade_df['stock_id'].tolist())
def select(self):
#standard v_rebound double_limit wave
# sql = "select s.trade_date,s.stock_id,s.high_price,s.low_price,s.close_price,s.increase,l.grade,l.monitor,l.type,l.factor " \
# " from stock_trade_data s " \
# " LEFT JOIN (select * from limit_up_single_validate where type = 'v_rebound' and trade_date >='{0}' and trade_date <='{1}') l " \
# " ON s.trade_code = l.trade_code " \
# " where s.trade_date >='{0}' and s.trade_date <='{1}' ".format(self.start_date,self.end_date)
sql = "select s.trade_date,s.stock_id,s.high_price,s.low_price,s.close_price,s.increase,l.zhuang_grade,l.monitor,l.zhuang_section from stock_trade_data s " \
" INNER JOIN (select * from com_zhuang where zhuang_grade>=1000 ) l " \
" ON s.stock_id = l.stock_id " \
" where s.trade_date >='{0}' and s.trade_date <='{1}' ".format(self.start_date,self.end_date)
self.sel_df = pub_uti_a.creat_df(sql)
self.sel_df.to_csv('record_sel.csv')
def select(self):
#standard v_rebound double_limit wave
sql = "select s.trade_date,s.stock_id,s.high_price,s.low_price,s.close_price,s.increase,l.grade,l.monitor,l.type,l.factor " \
" from stock_trade_data s " \
" LEFT JOIN (select * from limit_up_single_validate where type = 'wave' and trade_date >='{0}' and trade_date <='{1}') l " \
" ON s.trade_code = l.trade_code " \
" where s.trade_date >='{0}' and s.trade_date <='{1}' ".format(self.start_date,self.end_date)
# sql = "select s.trade_date,s.stock_id,s.high_price,s.low_price,s.close_price,s.increase,l.grade,l.monitor from stock_trade_data s " \
# " LEFT JOIN (select * from remen_xiaoboxin where trade_date >='{0}' and trade_date <='{1}') l " \
# " ON s.trade_code = l.trade_code " \
# " where s.trade_date >='{0}' and s.trade_date <='{1}' ".format(self.start_date,self.end_date)
self.sel_df = pub_uti_a.creat_df(sql)
self.sel_df.to_csv('record_sel.csv')
def add_rank_fun(start_date,end_date):
sql = "select bk_id,trade_date,bk_name,redu from bankuai_day_data where trade_date >= '{}' and trade_date <= '{}'".format(start_date,end_date)
df = pub_uti_a.creat_df(sql)
date_set = set(df['trade_date'].to_list())
print('date_set',date_set)
for date in date_set:
single_df = df[df.trade_date == date]
# print('single:',single_df)
single_df = single_df.sort_values(axis=0, ascending=False, by='redu', na_position='last')
single_df.reset_index(inplace=True)
single_df['ranks'] = single_df.index + 1
print('single_df:',single_df)
update_data(single_df)
def select_trade_info(self):
trade_sql = "select stock_id,stock_name,high_price,low_price,open_price,close_price,trade_date,wave_data,point_type,turnover_rate,increase " \
" FROM stock_trade_data " \
"where trade_date >= '{0}' and trade_date <= '{1}' " \
" AND stock_id not like '300%' AND stock_id not like '688%' " \
" AND stock_name not like 'ST%' AND stock_name not like '*ST%' ".format(self.sql_start_date,self.date)
print('trade_sql:{}'.format(trade_sql))
self.trade_df = pub_uti_a.creat_df(sql=trade_sql)
self.trade_df.fillna('', inplace=True)
#标价涨停
self.trade_df['limit_flag'] = self.trade_df['increase'].apply(
lambda x: 1 if x >= 9.75 else 0)
self.trade_df = self.trade_df.groupby(
['stock_id', 'stock_name'], as_index=False)['limit_flag'].sum()
def com_lastest_point():
sql = "select stock_id,zhuang_section from com_zhuang where zhuang_grade > 0"
df = pub_uti_a.creat_df(sql)
s = pub_uti_a.save()
def map(raw):
zhuang_section = eval(raw['zhuang_section'])
if len(zhuang_section) == 0:
return raw
sql = "update com_zhuang set lastest_target= '{0}' where stock_id ='{1}'".format(
zhuang_section[0][0], raw['stock_id'])
s.add_sql(sql)
return raw
df.apply(map, axis=1)
s.commit()
def select_buffer(self):
sql = "SELECT D.trade_code,D.stock_id,D.stock_name,D.zhangting_count," \
" D.arise_date,S.trade_date,S.value_abnormal " \
" FROM tongji_dalasheng D " \
"LEFT JOIN stock_trade_data S " \
"ON D.stock_id = S.stock_id "
self.df = pub_uti_a.creat_df(sql,ascending=True)
stock_id_set = set(self.df['stock_id'].to_list())
for id in stock_id_set:
single_df = self.df[self.df['stock_id'] == id]
arise_date_set = set(single_df['arise_date'].to_list())
for lasheng_date in arise_date_set:
single_df_sun = single_df[single_df['arise_date'] == lasheng_date]
s = stock(single_df_sun,lasheng_date)
save_sql = "update tongji_dalasheng set count_single= '{}',lastest_info = '{}' " \
"WHERE trade_code = '{}'".format(s.count_single,s.lastest_info,s.trade_code)
self.save.add_sql(save_sql)
print(s.stock_name,s.arise_date,s.zhangting_count,s.count_single,s.lastest_info)
self.save.commit()
def com_volume_signal(date=None, long=120, avg_roll=10, signal_threshold=2):
if date == None:
sql = "select DATE_FORMAT(max(trade_date),'%Y-%m-%d') from stock_trade_data"
date = pub_uti_a.select_from_db(sql)[0][0]
print('date:', date)
start_date = datetime.datetime.strftime(
(datetime.datetime.strptime(date[0:10], '%Y-%m-%d') -
datetime.timedelta(days=long)), '%Y-%m-%d')
trade_sql = "select T.stock_id,T.trade_date,T.turnover_rate " \
" from (select stock_id from com_zhuang where lastest_target>= '{0}') Z " \
"LEFT JOIN stock_trade_data T " \
"ON Z.stock_id = T.stock_id " \
"WHERE T.trade_date >= '{0}' and T.trade_date<= '{1}'".format(start_date,date)
df = pub_uti_a.creat_df(trade_sql, ascending=True)
id_set = set(df['stock_id'].to_list())
volume_signal_map = {}
clean_sql = "delete from zhuang_day_grade where com_date = '{}'".format(
date)
pub_uti_a.commit_to_db(clean_sql)
s = pub_uti_a.save()
for id in id_set:
single_df = df[df.stock_id == id]
single_df.reset_index(drop=True, inplace=True)
single_df['avg'] = single_df['turnover_rate'].rolling(avg_roll).mean()
single_df['avg'] = single_df['avg'].shift(1)
single_df['avg'].fillna(100, inplace=True)
single_df[
'volume_signal'] = single_df['turnover_rate'] / single_df['avg']
# print('single_df', single_df)
index_list = single_df[
single_df['volume_signal'] >= signal_threshold].index.to_list()
print('index_list:', index_list)
if len(index_list) != 0 and index_list[0] >= (len(single_df) - 3):
trade_code = re.sub('-', '', date) + id
grade = 50
sql = "insert into zhuang_day_grade (trade_code,com_date,stock_id,grade) " \
"VALUES ('{0}','{1}','{2}',{3})".format(trade_code,date,id,grade)
print('sql:', sql)
s.add_sql(sql)
volume_signal_map[id] = grade
s.commit()
print('volume_signal_map:', volume_signal_map)
def comput_increase(self):
sql = "select trade_code,trade_date,stock_id,stock_name,close_price from stock_trade_data " \
"where trade_date >= '{}' and trade_date <= '{}'".format(self.start_date,self.end_date)
df = pub_uti_a.creat_df(sql, ascending=True)
id_set = set(df['stock_id'].to_list())
s = pub_uti_a.save()
count = 0
for id in id_set:
print('id:', id, count)
single_df = df[df.stock_id == id]
single_df['pre_close'] = single_df['close_price'].shift(1)
single_df.dropna(inplace=True)
single_df.reset_index(inplace=True, drop=True)
print('singel index:', single_df.index)
single_df['increase'] = (
single_df['close_price'] / single_df['pre_close'] - 1) * 100
for idx, raw in single_df.iterrows():
sql = "update stock_trade_data set increase={} where trade_code = '{}'".format(
raw['increase'], raw['trade_code'])
print(sql)
s.add_sql(sql)
count += 1
s.commit()
def select_data(self):
sql = "select trade_date,stock_id,stock_name,close_price,increase " \
"FROM stock_trade_data " \
"WHERE trade_date >='{0}' AND trade_date <='{1}'".format(self.start_date,self.end_date)
self.df = pub_uti_a.creat_df(sql, ascending=True)
self.stock_id_set = set(self.df['stock_id'].to_list())
def select_market(self):
sql = "select trade_code,trade_date,stock_id,stock_name,open_price,close_price,high_price,low_price,increase " \
" from stock_trade_data " \
"where trade_date >= '{}' and trade_date <= '{}' ".format(self.start_date,self.end_date)
self.market_df = pub_uti_a.creat_df(sql)
print('market select complete.', datetime.datetime.now())
def get_trade_date(self):
sql = "select * from stock_trade_data where trade_date >= '{0}' and trade_date <= '{1}'".format(
self.trade_date_start, self.trade_date_end)
self.trade_df = pub_uti_a.creat_df(sql)
self.trade_df['mod_price'] = (self.trade_df['high_price'] +
self.trade_df['low_price']) / 2
