stock_features['update_datetime'] = dt.datetime.today()
# 写入数据库
dtype_dict = {
'stock_code': VARCHAR(8),
'trade_date': VARCHAR(8),
'b': DECIMAL(20, 6),
'c': DECIMAL(20, 6),
'u': DECIMAL(20, 6),
'l': DECIMAL(20, 6),
'ol': DECIMAL(20, 6),
'cl': DECIMAL(20, 6),
'ac': DECIMAL(20, 6),
'update_datetime': DATETIME
}
save_into_db(stock_features,
'LDH_features_source',
dtype_dict,
'gb',
if_exists='replace')
sql_merge = '''
MERGE INTO LDH_features as T
USING LDH_features_source as S
ON (T.trade_date = S.trade_date AND T.stock_code = S.stock_code)
WHEN NOT MATCHED BY TARGET
THEN INSERT
(stock_code,trade_date,b,c,u,l,ol,cl,ac,update_datetime)
VALUES
(S.stock_code,S.trade_date,S.b,S.c,S.u,S.l,S.ol,S.cl,S.ac,S.update_datetime);
'''
execute_session(sql_merge, 'gb')
def save_state(self, state):
strategy_name = state['strategy_name']
position_hist = state['position_hist']
rebalance_hist = state['rebalance_hist']
refresh_rate = state['refresh_rate']
refresh_counter = state['refresh_counter']
daily_ret = state['daily_ret']
# position
position_hist_list = []
for each in position_hist:
for key, val in each[1].items():
position_hist_list.append([each[0], strategy_name, key, val])
position_hist_df = pd.DataFrame(position_hist_list,
columns=[
'trade_date', 'strategy_name',
'fund_code', 'fund_weight'
])
save_into_db(
position_hist_df, 'FOF_position_source', {
'trade_date': VARCHAR(8),
'strategy_name': VARCHAR(32),
'fund_code': VARCHAR(32),
'fund_weight': DECIMAL(10, 6)
}, 'xiaoyi', 'replace')
execute_session(SQL_MERGE_POSITION, 'xiaoyi')
# rebalance
rebalance_hist_list = []
for each in rebalance_hist:
for key, val in each[1].items():
if val > 0:
direction = 'LONG'
elif val < 0:
direction = 'SHORT'
rebalance_hist_list.append(
[each[0], strategy_name, key, direction, val])
rebalance_hist_df = pd.DataFrame(rebalance_hist_list,
columns=[
'trade_date', 'strategy_name',
'fund_code', 'direction',
'weight_change'
])
save_into_db(
rebalance_hist_df, 'FOF_rebalance_source', {
'trade_date': VARCHAR(8),
'strategy_name': VARCHAR(32),
'fund_code': VARCHAR(32),
'direction': VARCHAR(8),
'weight_change': DECIMAL(10, 6)
}, 'xiaoyi', 'replace')
execute_session(SQL_MERGE_REBALANCE, 'xiaoyi')
# refresh
refresh_state_df = pd.DataFrame([[
strategy_name, refresh_rate, refresh_counter,
dt.datetime.today().strftime('%Y%m%d')
]],
columns=[
'strategy_name', 'refresh_rate',
'refresh_counter', 'update_date'
])
save_into_db(
refresh_state_df, 'FOF_refresh_state_source', {
'strategy_name': VARCHAR(32),
'refresh_rate': INT,
'refresh_counter': INT,
'update_date': VARCHAR(32)
}, 'xiaoyi', 'replace')
execute_session(SQL_MERGE_REFRESH_STATE, 'xiaoyi')
# daily_ret
daily_ret_df = pd.DataFrame(daily_ret, columns=['trade_date', 'ret'])
daily_ret_df.loc[:, 'strategy_name'] = strategy_name
save_into_db(
daily_ret_df, 'FOF_daily_ret_source', {
'strategy_name': VARCHAR(32),
'trade_date': VARCHAR(8),
'ret': DECIMAL(10, 6)
}, 'xiaoyi', 'replace')
execute_session(SQL_MERGE_DAILY_RET, 'xiaoyi')
SELECT
max(target_date) as target_date,
stock_code
FROM LDH_pattern_pred_minute
GROUP BY stock_code) b
ON a.target_date = b.target_date AND a.stock_code = b.stock_code
'''
intra_last_signal = get_data(SQL_GET_LAST_SIGNAL_MINUTE, 'gb')
dtype_dict = {
'stock_code': VARCHAR(10),
'target_date': VARCHAR(16),
'pred_dt': DATETIME,
'signal_type': INT, # (0为震荡,1为趋势)
'signal_prob': DECIMAL(10, 6)
}
save_into_db(intra_last_signal, 'Yi_mgt_pattern_pred_minute_source',
dtype_dict, 'xiaoyi40', 'replace')
SQL_MERGE_SIGNAL_MINUTE = '''
MERGE INTO Yi_mgt_pattern_pred_minute as T
USING Yi_mgt_pattern_pred_minute_source as S
ON (T.target_date = S.target_date AND T.stock_code = S.stock_code)
WHEN NOT MATCHED BY TARGET
THEN INSERT
(stock_code,target_date,pred_dt,signal_type,signal_prob)
VALUES
(S.stock_code,S.target_date,S.pred_dt,S.signal_type,S.signal_prob)
WHEN NOT MATCHED BY SOURCE
THEN DELETE;
'''
execute_session(SQL_MERGE_SIGNAL_MINUTE, 'xiaoyi40')
#%% 日间
# ---------- 股票特征写入数据库 ----------------
dtype_dict = {
'stock_code': VARCHAR(8),
'trade_date': VARCHAR(8),
'b': DECIMAL(20, 6),
'c': DECIMAL(20, 6),
'u': DECIMAL(20, 6),
'l': DECIMAL(20, 6),
'ol': DECIMAL(20, 6),
'cl': DECIMAL(20, 6),
'ac': DECIMAL(20, 6),
'update_datetime': DATETIME
}
save_into_db(stock_feature.drop_duplicates(['stock_code', 'trade_date']),
'LDH_features_source',
dtype_dict,
'gb',
if_exists='replace')
sql_merge = '''
MERGE INTO LDH_features as T
USING LDH_features_source as S
ON (T.trade_date = S.trade_date AND T.stock_code = S.stock_code)
WHEN NOT MATCHED BY TARGET
THEN INSERT
(stock_code,trade_date,b,c,u,l,ol,cl,ac,update_datetime)
VALUES
(S.stock_code,S.trade_date,S.b,S.c,S.u,S.l,S.ol,S.cl,S.ac,S.update_datetime);
'''
execute_session(sql_merge, 'gb')
end_time = time.time()
print '[FEATURE CALCULATION]%s cost: %s' % (stk, (end_time - start_time))
continue # 放弃更新此股票信号
y_pred = model.predict(X)
y_proba = model.predict_proba(X)
y_max_proba = y_proba.max(axis = 1)
stock_label = pd.DataFrame([target_date_list,
y_pred.tolist(),
y_max_proba.tolist()],index = ['target_date','signal_type','signal_prob']).T
stock_label['stock_code'] = stk
stock_label['pred_dt'] = today
signals = pd.concat([signals,stock_label])
print '%s stock finished'%stk
dtype_dict = {'stock_code':VARCHAR(10),
'target_date':VARCHAR(16),
'pred_dt':DATETIME,
'signal_type':INT, # (0为震荡,1为趋势)
'signal_prob':DECIMAL(10,6)}
save_into_db(signals,'LDH_pattern_pred_minute_source',dtype_dict,'gb','replace')
SQL_MERGE_SIGNALS = '''
MERGE INTO LDH_pattern_pred_minute as T
USING LDH_pattern_pred_minute_source as S
ON (T.target_date = S.target_date AND T.stock_code = S.stock_code)
WHEN NOT MATCHED BY TARGET
THEN INSERT
(stock_code,target_date,pred_dt,signal_type,signal_prob)
VALUES
(S.stock_code,S.target_date,S.pred_dt,S.signal_type,S.signal_prob);
'''
execute_session(SQL_MERGE_SIGNALS,'gb')