def creat_time(self):
if self.date == None:
sql = "select DATE_FORMAT(max(trade_date),'%Y-%m-%d') as last_date from stock_trade_data "
self.date = pub_uti_a.select_from_db(sql=sql)[0][0]
self.sql_start_date = (
datetime.datetime.strptime(self.date, '%Y-%m-%d') -
datetime.timedelta(days=self.sql_range_day)).strftime('%Y-%m-%d')
def get_bk_relation():
bk_map = {}
sql = "select distinct bk_name,bk_code from bankuai_day_data"
res = pub_uti_a.select_from_db(sql)
for tup in res:
bk_map[tup[0]] = tup[1]
return bk_map
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 history(start_date, end_date):
sql = "select distinct date_format(trade_date ,'%Y-%m-%d') as trade_date from stock_trade_data where trade_date>= '{}' and trade_date <= '{}'".format(
start_date, end_date)
date_tuple = pub_uti_a.select_from_db(
sql=sql) #(('2021-06-14',),('2021-06-15',))
date_list = list(chain.from_iterable(date_tuple))
p = Pool(8)
for i in range(0, len(date_list)):
st_buff = stock_buffer(date_list[i])
p.apply_async(st_buff.init_buffer)
# p.apply_async(main, args=('1',date,))
print('Waiting for all subprocesses done...')
p.close()
p.join()
print('All subprocesses done.')
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 main(date):
if date == None:
sql = "select DATE_FORMAT(max(trade_date),'%Y-%m-%d') as last_date from stock_trade_data "
date = pub_uti_a.select_from_db(sql=sql)[0][0]
date_time = datetime.datetime.strptime(date, '%Y-%m-%d')
print("date_time:", date_time)
start_t = (date_time - datetime.timedelta(days=90)).strftime('%Y-%m-%d')
# day_delta = 40
db_config = read_config('db_config')
print('db_config:', db_config)
db = pymysql.connect(host=db_config["host"],
user=db_config["user"],
password=db_config["password"],
database=db_config["database"])
sql = "select stock_id,stock_name,trade_date,close_price,increase,turnover_rate from stock_trade_data " \
"where stock_id not like '688%' and stock_id not like '300%' and trade_date >= '{0}' and trade_date <= '{1}' " \
"and stock_name not like 'ST%' and stock_name not like '%ST%' ".format(start_t,date)#and stock_id in ('002940','000812')
time_start = datetime.datetime.now()
df = get_df_from_db(sql, db)
time_end = datetime.datetime.now()
print('df_len:', len(df))
print('time_delta:', time_end - time_start)
df = core(df, date)
save(db, df)
def get_stock_list(self):
sql = "select distinct stock_id,stock_name from stock_trade_data "
self.info_tuple = pub_uti_a.select_from_db(sql)
