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