def filter_main(stock_new,state_dt,predict_dt,poz):
# 建立数据库连接
##################################################
# 建立数据库连接,设置tushare的token,定义一些初始化参数
env=get_env()
db,cursor,pro=env.db,env.cursor,env.pro
##################################################
#先更新持股天数
sql_update_hold_days = 'update my_stock_pool w set w.hold_days = w.hold_days + 1'
cursor.execute(sql_update_hold_days)
db.commit()
#先卖出
deal = Deal.Deal(state_dt)
stock_pool_local = deal.stock_pool
for stock in stock_pool_local:
sql_predict = "select predict from model_ev_resu a where a.state_dt = '%s' and a.stock_code = '%s'"%(predict_dt,stock)
cursor.execute(sql_predict)
done_set_predict = cursor.fetchall()
predict = 0
if len(done_set_predict) > 0:
predict = int(done_set_predict[0][0])
print "predict: %s,predictvalue: %d " % (stock ,predict)
ans = Operator.sell(stock,state_dt,predict)
print "sell: %s" % stock
#后买入
for stock_index in range(len(stock_new)):
deal_buy = Deal.Deal(state_dt)
# # 如果模型f1分值低于50则不买入
# sql_f1_check = "select * from model_ev_resu a where a.stock_code = '%s' and a.state_dt < '%s' order by a.state_dt desc limit 1"%(stock_new[stock_index],state_dt)
# cursor.execute(sql_f1_check)
# done_check = cursor.fetchall()
# db.commit()
# if len(done_check) > 0:
# if float(done_check[0][4]) < 0.5:
# print('F1 Warning !!')
# continue
# macd模型，如果预测值为1则买入
# sql_macd_check="select * from model_macd_resu where stock_code= '%s' and state_dt= '%s' " % (stock_new[stock_index],state_dt)
# cursor.execute(sql_macd_check)
# done_check = cursor.fetchall()
# print done_check
# db.commit()
# if len(done_check) > 0:
# if done_check[0][2] <> '1': # 不可买
# print(state_dt,': ',stock_new[stock_index],'macd forbidden !')
# continue
ans = Operator.buy(stock_new[stock_index],state_dt,float(poz[stock_index])*deal_buy.cur_money_rest)
print "stock_new[stock_index],state_dt,poz[stock_index]*deal_buy.cur_money_rest", stock_new[stock_index],state_dt,poz[stock_index]*deal_buy.cur_money_rest
del deal_buy
db.close()
def model_eva(stock,state_dt,para_window,para_dc_window):
# 建立数据库连接，设置tushare token
##################################################
# 建立数据库连接,设置tushare的token,定义一些初始化参数
env=get_env()
db,cursor,pro=env.db,env.cursor,env.pro
##################################################
# print 'into_eva'*10
# 建评估时间序列, para_window参数代表回测窗口长度
model_test_date_start = (datetime.datetime.strptime(state_dt, '%Y-%m-%d') - datetime.timedelta(days=para_window)).strftime(
'%Y%m%d')
model_test_date_end = datetime.datetime.strptime(state_dt,('%Y-%m-%d')).strftime('%Y%m%d')
print "model_test_date_start,model_test_date_end: ",model_test_date_start,model_test_date_end
df = pro.trade_cal(exchange_id='', is_open = 1,start_date=model_test_date_start, end_date=model_test_date_end)
date_temp = list(df.iloc[:,1])
model_test_date_seq = [(datetime.datetime.strptime(x, "%Y%m%d")).strftime('%Y-%m-%d') for x in date_temp]
# 清空评估用的中间表model_ev_mid
sql_truncate_model_test = 'truncate table model_ev_mid'
cursor.execute(sql_truncate_model_test)
db.commit()
# print 'after_commit_'*10
return_flag = 0
# 开始回测，其中para_dc_window参数代表建模时数据预处理所需的时间窗长度
for d in range(len(model_test_date_seq)):
model_test_new_start = (datetime.datetime.strptime(model_test_date_seq[d], '%Y-%m-%d') - datetime.timedelta(days=para_dc_window)).strftime('%Y-%m-%d')
model_test_new_end = model_test_date_seq[d]
print "model_test_new_start,model_test_new_end: ",model_test_new_start,model_test_new_end
try:
dc = DC.data_collect(stock, model_test_new_start, model_test_new_end)
if len(set(dc.data_target)) <= 1:
continue
except Exception as exp:
print("DC Errrrr")
return_flag = 1
break
train = dc.data_train
target = dc.data_target
test_case = [dc.test_case]
model = svm.SVC() # 建模
model.fit(train, target) # 训练
ans2 = model.predict(test_case) # 预测
# 将预测结果插入到中间表
# print 'predict'*10
sql_insert = "insert into model_ev_mid(state_dt,stock_code,resu_predict)values('%s','%s','%.2f')" % (model_test_new_end, stock, float(ans2[0]))
# print sql_insert
cursor.execute(sql_insert)
db.commit()
if return_flag == 1:
acc = recall = acc_neg = f1 = 0
return -1
else:
# 在中间表中刷真实值
for i in range(len(model_test_date_seq)):
sql_select = "select * from stock_all a where a.stock_code = '%s' and a.state_dt >= '%s' order by a.state_dt asc limit 2" % (stock, model_test_date_seq[i])
cursor.execute(sql_select)
done_set2 = cursor.fetchall()
if len(done_set2) <= 1:
break
resu = 0
if float(done_set2[1][3]) / float(done_set2[0][3]) > 1.00:
resu = 1
sql_update = "update model_ev_mid w set w.resu_real = '%.2f' where w.state_dt = '%s' and w.stock_code = '%s'" % (resu, model_test_date_seq[i], stock)
cursor.execute(sql_update)
db.commit()
# 计算查全率
sql_resu_recall_son = "select count(*) from model_ev_mid a where a.resu_real is not null and a.resu_predict = 1 and a.resu_real = 1"
cursor.execute(sql_resu_recall_son)
recall_son = cursor.fetchall()[0][0]
sql_resu_recall_mon = "select count(*) from model_ev_mid a where a.resu_real is not null and a.resu_real = 1"
cursor.execute(sql_resu_recall_mon)
recall_mon = cursor.fetchall()[0][0]
if recall_mon == 0:
acc = recall = acc_neg = f1 = 0
else:
recall = float(recall_son) / recall_mon
# 计算查准率
sql_resu_acc_son = "select count(*) from model_ev_mid a where a.resu_real is not null and a.resu_predict = 1 and a.resu_real = 1"
cursor.execute(sql_resu_acc_son)
acc_son = cursor.fetchall()[0][0]
sql_resu_acc_mon = "select count(*) from model_ev_mid a where a.resu_real is not null and a.resu_predict = 1"
cursor.execute(sql_resu_acc_mon)
acc_mon = cursor.fetchall()[0][0]
if acc_mon == 0:
acc = recall = acc_neg = f1 = 0
else:
acc = float(acc_son) / acc_mon
# 计算查准率(负样本)
sql_resu_acc_neg_son = "select count(*) from model_ev_mid a where a.resu_real is not null and a.resu_predict = -1 and a.resu_real = -1"
cursor.execute(sql_resu_acc_neg_son)
acc_neg_son = cursor.fetchall()[0][0]
sql_resu_acc_neg_mon = "select count(*) from model_ev_mid a where a.resu_real is not null and a.resu_predict = -1"
cursor.execute(sql_resu_acc_neg_mon)
acc_neg_mon = cursor.fetchall()[0][0]
if acc_neg_mon == 0:
acc_neg_mon = -1
acc_neg = -1
else:
acc_neg = float(acc_neg_son) / acc_neg_mon
# 计算 F1 分值
if acc + recall == 0:
acc = recall = acc_neg = f1 = 0
else:
f1 = (2 * acc * recall) / (acc + recall)
sql_predict = "select resu_predict from model_ev_mid a where a.state_dt = '%s'" % (model_test_date_seq[-1])
cursor.execute(sql_predict)
done_predict = cursor.fetchall()
predict = 0
if len(done_predict) != 0:
predict = int(done_predict[0][0])
# 将评估结果存入结果表model_ev_resu中
sql_final_insert = "insert into model_ev_resu(state_dt,stock_code,acc,recall,f1,acc_neg,bz,predict)values('%s','%s','%.4f','%.4f','%.4f','%.4f','%s','%s')" % (state_dt, stock, acc, recall, f1, acc_neg, 'svm', str(predict))
cursor.execute(sql_final_insert)
db.commit()
db.close()
print(str(state_dt) + ' Precision : ' + str(acc) + ' Recall : ' + str(recall) + ' F1 : ' + str(f1) + ' Acc_Neg : ' + str(acc_neg))
return 1
#coding:utf8
'''
This module used for back_test parament settings
author:michael 2018.12.05
'''
from init_env import get_env
# stock_pool_9=['600729.SH', '600733.SH', '600740.SH', '600754.SH', '600755.SH', '600760.SH', '600783.SH', '600790.SH', '600825.SH', '600874.SH','000576.SZ', '000582.SZ', '000592.SZ', '000603.SZ', '000622.SZ', '000638.SZ', '000651.SZ', '000666.SZ', '000722.SZ']
env = get_env()
db, cursor, pro = env.db, env.cursor, env.pro
class back_test(object):
def __init__(self,
para_window=90,
start_time='2018-08-01',
end_time='2018-12-01',
hold_days=7,
strategy='',
torlence_profit=0.05,
torlence_lost=0.10,
operate_days=5,
operate_tm=''):
self.para_window = para_window
self.start_time = start_time
self.end_time = end_time
self.hold_days = hold_days
self.strategy = strategy
self.torlence_lost = torlence_lost
self.torlence_profit = torlence_profit
self.operate_days = operate_days
self.operate_tm = operate_tm
def buy(stock_code, opdate, buy_money):
##################################################
# 建立数据库连接,设置tushare的token,定义一些初始化参数
env = get_env()
db, cursor, pro = env.db, env.cursor, env.pro
##################################################
deal_buy = Deal.Deal(opdate)
print "deal_buy info:deal_buy.rest:%s,deal_buy.cur_capital:%s" % (str(
deal_buy.cur_money_rest), str(deal_buy.cur_capital))
print 'buy_money: ', buy_money
#后买入
if deal_buy.cur_money_rest + 1 >= buy_money:
print "deal_buy.cur_money_rest+1 : ", deal_buy.cur_money_rest + 1, " buy_money: ", buy_money
sql_buy = "select * from stock_all_plus a where a.state_dt = '%s' and a.stock_code = '%s'" % (
opdate, stock_code)
cursor.execute(sql_buy)
done_set_buy = cursor.fetchall()
print done_set_buy, 'donesetbuy'
if len(done_set_buy) == 0:
return -1
#剔除涨跌停因素
if done_set_buy[0][10] > 9.9:
print "涨停无法买入:%s" % str(done_set_buy)
return -1
buy_price = float(done_set_buy[0][3])
print "buy_price", buy_price
# if buy_price >= 195:
# return 0
vol, rest = divmod(min(deal_buy.cur_money_rest, buy_money),
buy_price * 100)
print "vol, rest:", vol, rest
vol = vol * 100
if vol == 0:
return 0
new_capital = deal_buy.cur_capital - vol * buy_price * 0.0005
new_money_lock = deal_buy.cur_money_lock + vol * buy_price
new_money_rest = deal_buy.cur_money_rest - vol * buy_price * 1.0005
sql_buy_update2 = "insert into my_capital(capital,money_lock,money_rest,deal_action,stock_code,stock_vol,state_dt,deal_price)VALUES ('%.2f', '%.2f', '%.2f','%s','%s','%i','%s','%.2f')" % (
new_capital, new_money_lock, new_money_rest, 'buy', stock_code,
vol, opdate, buy_price)
print "sql_buy_update2: ", sql_buy_update2
cursor.execute(sql_buy_update2)
db.commit()
if stock_code in deal_buy.stock_all:
new_buy_price = (deal_buy.stock_map1[stock_code] *
deal_buy.stock_map2[stock_code] + vol * buy_price
) / (deal_buy.stock_map2[stock_code] + vol)
new_vol = deal_buy.stock_map2[stock_code] + vol
sql_buy_update3 = "update my_stock_pool w set w.buy_price = (select '%.2f' from dual) where w.stock_code = '%s'" % (
new_buy_price, stock_code)
sql_buy_update3b = "update my_stock_pool w set w.hold_vol = (select '%i' from dual) where w.stock_code = '%s'" % (
new_vol, stock_code)
sql_buy_update3c = "update my_stock_pool w set w.hold_days = (select '%i' from dual) where w.stock_code = '%s'" % (
1, stock_code)
cursor.execute(sql_buy_update3)
cursor.execute(sql_buy_update3b)
cursor.execute(sql_buy_update3c)
db.commit()
else:
sql_buy_update3 = "insert into my_stock_pool(stock_code,buy_price,hold_vol,hold_days) VALUES ('%s','%.2f','%i','%i')" % (
stock_code, buy_price, vol, int(1))
cursor.execute(sql_buy_update3)
db.commit()
db.close()
return 1
db.close()
return 0
def sell(stock_code, opdate, predict):
##################################################
# 建立数据库连接,设置tushare的token,定义一些初始化参数
env = get_env()
db, cursor, pro = env.db, env.cursor, env.pro
##################################################
deal = Deal.Deal(opdate)
init_price = deal.stock_map1[stock_code]
hold_vol = deal.stock_map2[stock_code]
hold_days = deal.stock_map3[stock_code]
sql_sell_select = "select * from stock_all_plus a where a.state_dt = '%s' and a.stock_code = '%s'" % (
opdate, stock_code)
cursor.execute(sql_sell_select)
done_set_sell_select = cursor.fetchall()
if len(done_set_sell_select) == 0:
return -1
sell_price = float(done_set_sell_select[0][3])
#跌停无法卖出
if done_set_sell_select[0][10] < -9.9:
print "跌停无法卖出:%s" % str(done_set_sell_select)
return -1
if sell_price > init_price * (1 + bt.torlence_profit) and hold_vol > 0:
new_money_lock = deal.cur_money_lock - sell_price * hold_vol
if new_money_lock < 0:
print "deal.cur_money_lock:%f,new_money_lock:%f,sell_price:%f,hold_vol:%d" % (
deal.cur_money_lock, new_money_lock, sell_price, hold_vol)
x = raw_input('profit error....')
new_money_rest = deal.cur_money_rest + sell_price * hold_vol
new_capital = deal.cur_capital + (sell_price - init_price) * hold_vol
new_profit = (sell_price - init_price) * hold_vol
new_profit_rate = sell_price / init_price
sql_sell_insert = "insert into my_capital(capital,money_lock,money_rest,deal_action,stock_code,stock_vol,profit,profit_rate,bz,state_dt,deal_price)values('%.2f','%.2f','%.2f','%s','%s','%.2f','%.2f','%.2f','%s','%s','%.2f')" % (
new_capital, new_money_lock, new_money_rest, 'SELL', stock_code,
hold_vol, new_profit, new_profit_rate, 'GOODSELL', opdate,
sell_price)
cursor.execute(sql_sell_insert)
db.commit()
sql_sell_update = "delete from my_stock_pool where stock_code = '%s'" % (
stock_code)
cursor.execute(sql_sell_update)
db.commit()
db.close()
return 1
elif sell_price < init_price * (1 - bt.torlence_lost) and hold_vol > 0:
new_money_lock = deal.cur_money_lock - sell_price * hold_vol
if new_money_lock < 0:
print "deal.cur_money_lock:%f,new_money_lock:%f,sell_price:%f,hold_vol:%d" % (
deal.cur_money_lock, new_money_lock, sell_price, hold_vol)
x = raw_input('lost error....')
new_money_rest = deal.cur_money_rest + sell_price * hold_vol
new_capital = deal.cur_capital + (sell_price - init_price) * hold_vol
new_profit = (sell_price - init_price) * hold_vol
new_profit_rate = sell_price / init_price
sql_sell_insert2 = "insert into my_capital(capital,money_lock,money_rest,deal_action,stock_code,stock_vol,profit,profit_rate,bz,state_dt,deal_price)values('%.2f','%.2f','%.2f','%s','%s','%.2f','%.2f','%.2f','%s','%s','%.2f')" % (
new_capital, new_money_lock, new_money_rest, 'SELL', stock_code,
hold_vol, new_profit, new_profit_rate, 'BADSELL', opdate,
sell_price)
cursor.execute(sql_sell_insert2)
db.commit()
sql_sell_update2 = "delete from my_stock_pool where stock_code = '%s'" % (
stock_code)
cursor.execute(sql_sell_update2)
db.commit()
# sql_ban_insert = "insert into ban_list(stock_code) values ('%s')" %(stock_code)
# cursor.execute(sql_ban_insert)
# db.commit()
db.close()
return 1
elif hold_days >= bt.hold_days and hold_vol > 0:
new_money_lock = deal.cur_money_lock - sell_price * hold_vol
if new_money_lock < 0:
print "deal.cur_money_lock:%f,new_money_lock:%f,sell_price:%f,hold_vol:%d" % (
deal.cur_money_lock, new_money_lock, sell_price, hold_vol)
x = raw_input('hold days error....')
new_money_rest = deal.cur_money_rest + sell_price * hold_vol
new_capital = deal.cur_capital + (sell_price - init_price) * hold_vol
new_profit = (sell_price - init_price) * hold_vol
new_profit_rate = sell_price / init_price
sql_sell_insert3 = "insert into my_capital(capital,money_lock,money_rest,deal_action,stock_code,stock_vol,profit,profit_rate,bz,state_dt,deal_price)values('%.2f','%.2f','%.2f','%s','%s','%.2f','%.2f','%.2f','%s','%s','%.2f')" % (
new_capital, new_money_lock, new_money_rest, 'OVERTIME',
stock_code, hold_vol, new_profit, new_profit_rate, 'OVERTIMESELL',
opdate, sell_price)
cursor.execute(sql_sell_insert3)
db.commit()
sql_sell_update3 = "delete from my_stock_pool where stock_code = '%s'" % (
stock_code)
cursor.execute(sql_sell_update3)
db.commit()
db.close()
return 1
elif predict == -1:
new_money_lock = deal.cur_money_lock - sell_price * hold_vol
new_money_rest = deal.cur_money_rest + sell_price * hold_vol
new_capital = deal.cur_capital + (sell_price - init_price) * hold_vol
new_profit = (sell_price - init_price) * hold_vol
new_profit_rate = sell_price / init_price
sql_sell_insert4 = "insert into my_capital(capital,money_lock,money_rest,deal_action,stock_code,stock_vol,profit,profit_rate,bz,state_dt,deal_price)values('%.2f','%.2f','%.2f','%s','%s','%.2f','%.2f','%.2f','%s','%s','%.2f')" % (
new_capital, new_money_lock, new_money_rest, 'Predict', stock_code,
hold_vol, new_profit, new_profit_rate, 'PredictSell', opdate,
sell_price)
cursor.execute(sql_sell_insert4)
db.commit()
sql_sell_update3 = "delete from my_stock_pool where stock_code = '%s'" % (
stock_code)
cursor.execute(sql_sell_update3)
db.commit()
db.close()
return 1
db.close()
return 0
def get_portfolio(stock_list,state_dt,para_window):
print "stock_list,state_dt,para_window: ",stock_list,state_dt,para_window
# 建数据库连接，设置Tushare的token
##################################################
# 建立数据库连接,设置tushare的token,定义一些初始化参数
env=get_env()
db,cursor,pro=env.db,env.cursor,env.pro
##################################################
portfilio = stock_list
# 建评估时间序列, para_window参数代表回测窗口长度
model_test_date_start = (datetime.datetime.strptime(state_dt, '%Y-%m-%d') - datetime.timedelta(days=para_window)).strftime(
'%Y-%m-%d')
model_test_date_end = (datetime.datetime.strptime(state_dt, "%Y-%m-%d")).strftime('%Y-%m-%d')
# df = pro.trade_cal(exchange_id='', is_open=1, start_date=model_test_date_start, end_date=model_test_date_end) #此处切换为本地获取，防止回测日期天数过多导致调用接口崩溃 2018.12.13 12:06
# print df
sql_trade="select * from trade_day where cal_date>='%s' and cal_date<='%s'" % (model_test_date_start,model_test_date_end)
cursor.execute(sql_trade)
db.commit()
df=pd.DataFrame(list(cursor.fetchall()))
print (df)
date_temp = list(df.iloc[:, 1])
model_test_date_seq = [(datetime.datetime.strptime(x, "%Y-%m-%d")).strftime('%Y-%m-%d') for x in date_temp]
list_return = []
for i in range(len(model_test_date_seq)-4):
ti = model_test_date_seq[i]
ri = []
for j in range(len(portfilio)):
sql_select = "select * from stock_all_plus a where a.stock_code = '%s' and a.state_dt >= '%s' and a.state_dt <= '%s' order by state_dt asc" % (portfilio[j], model_test_date_seq[i], model_test_date_seq[i + 4])
cursor.execute(sql_select)
done_set = cursor.fetchall()
db.commit()
temp = [x[3] for x in done_set]
base_price = 0.00
after_mean_price = 0.00
if len(temp) <= 1:
r = 0.00
else:
base_price = temp[0]
after_mean_price = np.array(temp[1:]).mean()
r = (float(after_mean_price/base_price)-1.00)*100.00
ri.append(r)
del done_set
del temp
del base_price
del after_mean_price
list_return.append(ri)
# 求协方差矩阵
cov = np.cov(np.array(list_return).T)
# 求特征值和其对应的特征向量
ans = np.linalg.eig(cov)
# 排序，特征向量中负数置0，非负数归一
ans_index = copy.copy(ans[0])
ans_index.sort()
resu = []
# print ans
for k in range(len(ans_index)):
con_temp = []
con_temp.append(ans_index[k])
content_temp1 = ans[1][np.argwhere(ans[0] == ans_index[k])[0][0]]
content_temp2 = []
content_sum = np.array([x for x in content_temp1 if x >= 0.00]).sum()
for m in range(len(content_temp1)):
if content_temp1[m] >= 0 and content_sum > 0:
content_temp2.append(content_temp1[m]/content_sum)
else:
content_temp2.append(0.00)
con_temp.append(content_temp2)
# 计算夏普率
sharp_temp = np.array(copy.copy(list_return)) * content_temp2
sharp_exp = sharp_temp.mean()
sharp_base = 0.04
sharp_std = np.std(sharp_temp)
if sharp_std == 0.00:
sharp = 0.00
else:
sharp = (sharp_exp - sharp_base) / sharp_std
con_temp.append(sharp)
resu.append(con_temp)
return resu