def prepare_raw_data(self,stock_code, ind_codes):
ind_cod = ""
for i in ind_codes:
ind_cod += "," + i
ind_cod = ind_cod[1:]
in_str = '('
for x in range(len(stock_code)):
if x != len(stock_code) - 1:
in_str += str('\'') + str(stock_code[x]) + str('\',')
else:
in_str += str('\'') + str(stock_code[x]) + str('\')')
db = ToMysql()
sql_select = "select state_dt,stock_code,"+\
ind_cod+" from factor_mon where stock_code in %s and state_dt = '%s'" %(in_str,self.trdate)
done_select = db.select(sql_select)
state_dt = [x[0] for x in done_select]
stock_code = [x[1] for x in done_select]
data = []
for code in range(len(ind_codes)):
indata =[x[code+2] for x in done_select]
data.append(indata)
tuples = list(zip(*[state_dt,stock_code]))
index = pd.MultiIndex.from_tuples(tuples, names=['state_dt', 'stock_code'])
raw_data = pd.DataFrame(data= data, columns=ind_codes,index= index)
return raw_data
def afterbc(self):
db = ToMysql()
sharp, c_std = statistics.get_sharp_rate()
print('Sharp Rate : ' + str(sharp))
print('Risk Factor : ' + str(c_std))
sql_bench_btc = "select * from stock_index a where a.stock_code = '000905.SH' and a.state_dt >= '%s' and a.state_dt <= '%s'" % (
self.start_date, self.end_date)
done_set_bench_btc = db.select(sql_bench_btc)
sql_capital = "select * from my_capital"
done_set_show_btc = db.select(sql_capital)
benchmark = [
x[3] / done_set_bench_btc[0][3] for x in done_set_bench_btc
]
nav = [x[4] / done_set_show_btc[0][4] for x in done_set_show_btc]
time = [x[0] for x in done_set_show_btc]
net = list(map(lambda x: x[0] - x[1] + 1, zip(nav, benchmark)))
data = list(map(list, zip(*[time, nav, benchmark, net])))
nav_df = pd.DataFrame(data,
columns=['time', 'nav', 'benchmark', 'net'])
nav_df["nav"].astype("float")
nav_df["benchmark"].astype("float")
nav_df["net"].astype("float")
return nav_df
def mon():
month = w.tdays("2007-01-01", "2018-12-06", "Period=M").Data[0]
mon = [datetime.strftime(x, '%Y-%m-%d') for x in month]
db = ToMysql()
for i in mon:
sql = "INSERT IGNORE INTO month (state_dt) VALUES ('%s')" % (i)
db.execute(sql)
return mon
def day():
d = w.tdays("2007-01-01", "2018-12-06", "Period=D").Data[0]
days = [datetime.strftime(x, '%Y-%m-%d') for x in d]
db = ToMysql()
for i in days:
sql = "INSERT IGNORE INTO day (state_dt) VALUES ('%s')" % (i)
db.execute(sql)
return days
def get_sharp_rate():
db = ToMysql()
sql_cap = "select * from my_capital a order by date"
done_exp = db.select(sql_cap)
cap_list = [float(x[4]) / 100000000 for x in done_exp]
return_list = []
base_cap = float(done_exp[0][4])
for i in range(len(cap_list)):
if i == 0:
return_list.append(float(1.00))
else:
ri = (float(done_exp[i][4]) - float(done_exp[0][4])) / float(
done_exp[0][4])
return_list.append(ri)
std = float(np.array(return_list).std())
exp_portfolio = (float(done_exp[-1][4]) - float(done_exp[0][4])) / float(
done_exp[0][4])
exp_norisk = 0.04 * (len(cap_list) / 250)
sharp_rate = (exp_portfolio - exp_norisk) / (std)
return sharp_rate, std
def put_winddata_tosql(self,stock, ind_codes):
db = ToMysql()
data = self.get_winddata(stock,ind_codes)
for code in ind_codes:
if data[code.upper()].dtypes == object:
for i in range(len(data)):
sql = "insert into factor_mon (state_dt,stock_code, %s) " \
"VALUES ('%s','%s','%s') on DUPLICATE key update %s = '%s'"\
%(code,self.trdate, data.index[i], data[code.upper()][i],code,data[code.upper()][i])
db.execute(sql)
else:
for i in range(len(data)):
sql = "insert into factor_mon (state_dt,stock_code, %s) " \
"VALUES ('%s','%s',%f) on DUPLICATE key update %s = %f" \
% (code, self.trdate, data.index[i], data[code.upper()][i], code, data[code.upper()][i])
db.execute(sql)
def get_bars(self, trdate):
ts.set_token(
'502bcbdbac29edf1c42ed84d5f9bd24d63af6631919820366f53e5d4')
pro = ts.pro_api()
db = ToMysql()
# 清空行情源表，并插入新的相关股票的行情数据。该操作是为了提高回测计算速度而剔除行情表(stock_all)中的冗余数据。
sql_wash4 = 'truncate table stock_info'
db.execute(sql_wash4)
in_str = '('
for x in range(len(self.securities)):
if x != len(self.securities) - 1:
in_str += str('\'') + str(self.securities[x]) + str('\',')
else:
in_str += str('\'') + str(self.securities[x]) + str('\')')
sql_insert = "insert into stock_info(select * from stock_all a where a.stock_code in %s and a.state_dt = '%s')" \
% (in_str,trdate)
db.execute(sql_insert)
# 数据完整性检查
sql_select = "select * from stock_info"
bars = db.select(sql_select)
bar_list = []
for i in bars:
bar_list.append(i[1])
needupdatelist = list(set(self.securities) - set(bar_list))
for i in needupdatelist:
try:
df = pro.daily(ts_code=i, trade_date=trdate)
time.sleep(0.3)
c_len = df.shape[0]
except Exception as aa:
print(aa)
print('No DATA Code: ' + str(i))
continue
for j in range(c_len):
resu0 = list(df.iloc[c_len - 1 - j])
resu = []
for k in range(len(resu0)):
if str(resu0[k]) == 'nan':
resu.append(-1)
else:
resu.append(resu0[k])
state_dt = (datetime.datetime.strptime(
resu[1], "%Y%m%d")).strftime('%Y-%m-%d')
try:
sql_insert = "INSERT INTO stock_all(state_dt,stock_code,open,close,high,low,vol,amount,pre_close,amt_change,pct_change) VALUES ('%s', '%s', '%.2f', '%.2f','%.2f','%.2f','%i','%.2f','%.2f','%.2f','%.2f')" % (
state_dt, str(resu[0]), float(resu[2]), float(resu[5]),
float(resu[3]), float(resu[4]), float(
resu[9]), float(resu[10]), float(
resu[6]), float(resu[7]), float(resu[8]))
sql_insert2 = "INSERT INTO stock_info(state_dt,stock_code,open,close,high,low,vol,amount,pre_close,amt_change,pct_change) VALUES ('%s', '%s', '%.2f', '%.2f','%.2f','%.2f','%i','%.2f','%.2f','%.2f','%.2f')" % (
state_dt, str(resu[0]), float(resu[2]), float(resu[5]),
float(resu[3]), float(resu[4]), float(
resu[9]), float(resu[10]), float(
resu[6]), float(resu[7]), float(resu[8]))
db.execute(sql_insert)
db.execute(sql_insert2)
except Exception as err:
continue
db.close()
def initialize(self):
db = ToMysql()
# 先清空之前的测试记录,并创建中间表
sql_wash1 = 'delete from my_capital'
db.execute(sql_wash1)
sql_wash2 = 'delete from my_position'
db.execute(sql_wash2)
sql_wash3 = 'delete from stock_info'
db.execute(sql_wash3)
sql_wash4 = 'delete from orders'
db.execute(sql_wash4)
sql_setCash = "INSERT INTO my_capital VALUES (%s, %s, 0, 0, %s)"\
% (repr(self.date_seq[0]), self.capital, self.capital)
db.execute(sql_setCash)
sql_insert = "insert into my_position(trdate,code,cost_price,revenue,volume,amount,margin,side) " \
"VALUES ('%s','%s',%.2f,%.2f,%.2f,%.2f,%.2f,'%s')" \
% (self.date_seq[0], "cash", 1, 0, self.capital, self.capital, 0,"buy")
db.execute(sql_insert)
db.close()
self.get_bars(self.date_seq[0])
def update_daily(self, state_dt_1, state_dt):
db = ToMysql()
deal_daily = Deal.Deal(state_dt_1)
new_holding_value = 0
# 更新position表
for i in deal_daily.stock_pool + ["cash"]:
if i == "cash":
new_trdate = state_dt
new_amount = deal_daily.stock_amount["cash"]
new_cost_price = 1
new_revenue = 0
new_margin = 0
new_side = "buy"
new_volume = deal_daily.stock_volume["cash"]
sql_insert = "insert into my_position(trdate,code,cost_price,revenue,volume,amount,margin,side) " \
"VALUES ('%s','%s',%.2f,%.2f,%.2f,%.2f,%.2f,'%s')" \
% (new_trdate, "cash", new_cost_price, new_revenue, new_volume, new_amount, new_margin, new_side)
db.execute(sql_insert)
else:
sql_position_delete = "DELETE FROM my_position WHERE amount = 0"
db.execute(sql_position_delete)
sql_bars = "select * from stock_info a where a.state_dt = '%s' and a.stock_code = '%s'" % (
state_dt_1, i)
done_set_buy = db.select(sql_bars)
if len(done_set_buy) == 0:
print("缺少持仓股票 " + str(i) + " " + str(state_dt_1) +
" 行情数据")
opdate2 = (datetime.strptime(
state_dt_1, "%Y-%m-%d")).strftime('%Y%m%d')
resu = self.pro.daily(ts_code=i, trade_date=opdate2)
if len(resu) != 0:
print("已经从互联网获取" + str(i) + " " + str(state_dt_1) +
" 行情数据")
new_price = resu["close"][0]
pct_change = resu["pct_chg"][0] / 100 + 1
sql_insert = "INSERT IGNORE INTO stock_all(state_dt,stock_code,open,close,high,low,vol,amount,pre_close,amt_change,pct_change) VALUES ('%s', '%s', '%.2f', '%.2f','%.2f','%.2f','%i','%.2f','%.2f','%.2f','%.2f')" % (
state_dt_1, str(resu.iloc[0][0]),
float(resu.iloc[0][2]), float(resu.iloc[0][5]),
float(resu.iloc[0][3]), float(resu.iloc[0][4]),
float(resu.iloc[0][9]), float(resu.iloc[0][10]),
float(resu.iloc[0][6]), float(
resu.iloc[0][7]), float(resu.iloc[0][8]))
db.execute(sql_insert)
else:
print(str(i) + ' 停牌')
new_price = deal_daily.stock_amount[
i] / deal_daily.stock_volume[i]
pct_change = 1
else:
pct_change = done_set_buy[0][10] / 100 + 1
new_price = done_set_buy[0][3]
new_trdate = state_dt
new_amount = pct_change * deal_daily.stock_amount[i]
new_cost_price = deal_daily.stock_cost_price[i]
new_revenue = (pct_change - 1) * deal_daily.stock_amount[
i] + deal_daily.stock_revenue[i]
new_margin = deal_daily.stock_margin[i]
new_side = deal_daily.stock_side[i]
new_volume = new_amount / new_price
new_holding_value = float(new_holding_value) + float(
new_amount)
sql_insert = "insert into my_position(trdate,code,cost_price,revenue,volume,amount,margin,side)" \
" VALUES ('%s','%s',%.2f,%.2f,%.2f,%.2f,%.2f,'%s')"\
% (new_trdate, i, new_cost_price, new_revenue,new_volume, new_amount, new_margin, new_side)
db.execute(sql_insert)
# 更新账户表my_capital
new_available_fund = deal_daily.cur_available_fund # 现金不变。
new_capital = float(new_available_fund) + float(new_holding_value)
new_margin = 0 # 先不填这个坑
sql_insert = "insert into my_capital(date,available_fund,holding_value,margin,total_asset) " \
"VALUES ('%s',%.2f,%.2f,%.2f,%.2f)" \
% (state_dt, new_available_fund, new_holding_value, new_margin, new_capital)
db.execute(sql_insert)
return 1
def buy(stock_code, opdate, buy_money, trade_side):
# 建立数据库连接
db = ToMysql()
deal_buy = Deal.Deal(opdate)
ts.set_token('502bcbdbac29edf1c42ed84d5f9bd24d63af6631919820366f53e5d4')
pro = ts.pro_api()
if deal_buy.cur_available_fund + 5 >= buy_money: # 现金要充足
sql_buy = "select * from stock_info a where a.state_dt = '%s' and a.stock_code = '%s'" % (
opdate, stock_code)
done_set_buy = db.select(sql_buy)
if len(done_set_buy) == 0:
print("缺少买入股票当日行情数据" + str(stock_code) + str(opdate))
opdate2 = (datetime.strptime(opdate,
"%Y-%m-%d")).strftime('%Y%m%d')
resu = pro.daily(ts_code=stock_code, trade_date=opdate2)
if len(resu) != 0:
print("已经从互联网获取数据" + str(stock_code) + str(opdate))
buy_price = resu["pre_close"][0]
sql_insert = "INSERT INTO stock_all(state_dt,stock_code,open,close,high,low,vol,amount,pre_close,amt_change,pct_change) VALUES ('%s', '%s', '%.2f', '%.2f','%.2f','%.2f','%i','%.2f','%.2f','%.2f','%.2f')" % (
opdate, str(resu.iloc[0][0]), float(resu.iloc[0][2]),
float(resu.iloc[0][5]), float(resu.iloc[0][3]),
float(resu.iloc[0][4]), float(resu.iloc[0][9]),
float(resu.iloc[0][10]), float(resu.iloc[0][6]),
float(resu.iloc[0][7]), float(resu.iloc[0][8]))
db.execute(sql_insert)
else:
return
else:
buy_price = float(done_set_buy[0][8])
if buy_price <= 0:
print("买入价格异常" + str(stock_code) + str(opdate))
vol, rest = divmod(
min(deal_buy.cur_available_fund, buy_money * 1.0005),
buy_price * 100)
vol = vol * 100
if vol == 0:
print("买入数量为 " + str(buy_money) + " " + str(stock_code) +
str(opdate))
print("买入数量为 " + str(buy_money) + " " + str(stock_code) +
str(opdate))
# 更新账户表my_capital
new_capital = deal_buy.cur_total_asset - vol * buy_price * 0.0005 # 手续费为万5，直接减少净资产
new_available_fund = deal_buy.cur_available_fund - vol * buy_price * 1.0005 # 减少相应的现金。
new_holding_value = deal_buy.cur_holding_value + vol * buy_price # 增加持仓市值
new_margin = 0 # 先不填这个坑
sql_buyorder_update = "UPDATE my_capital SET available_fund = %.2f," \
"holding_value = %.2f," \
"margin = %.2f," \
"total_asset = %.2f " \
"WHERE date = '%s' " % (new_available_fund, new_holding_value, new_margin, new_capital,opdate)
db.execute(sql_buyorder_update)
# 更新position cash
sql_position_cash = "UPDATE my_position SET code = '%s',cost_price = %.2f,revenue = %.2f," \
"volume = %.2f,amount = %.2f,margin= %.2f,side='%s' WHERE code = '%s' AND trdate = '%s' " \
% ('cash', 1, 0, float(new_available_fund),float(new_available_fund), 0, 'buy','cash', opdate)
db.execute(sql_position_cash)
# 更新orders表
new_stock_code = stock_code
new_order_time = opdate
new_trade_side = trade_side
new_volume = vol
new_price = buy_price
new_amount = vol * buy_price
new_err_msg = 1
sql_order_update = "insert into orders(stock_code,order_time,trade_side,volume,price,amount,err_msg) " \
"VALUES ('%s','%s','%s',%.2f,%.2f,%.2f,'%s')" \
% (new_stock_code,new_order_time,new_trade_side,new_volume,new_price,new_amount,new_err_msg
)
db.execute(sql_order_update)
# 更新position表
#判断是不是在持仓里面
if stock_code in deal_buy.stock_pool:
new_code = stock_code
new_amount = deal_buy.stock_amount[stock_code] + vol * buy_price
new_revenue = deal_buy.stock_revenue[stock_code]
new_volume = new_amount / buy_price
new_cost_price = (new_amount - new_revenue) / new_volume
new_margin = 0
new_side = "buy"
sql_position_update = "UPDATE my_position SET code = '%s'," \
"cost_price = %.2f,revenue = %.2f," \
"volume = %.2f,amount = %.2f,margin= %.2f,side='%s' WHERE code = '%s' AND trdate = '%s' "\
% (new_code, new_cost_price,new_revenue,new_volume,new_amount,new_margin,new_side,new_code,opdate)
db.execute(sql_position_update)
else:
new_code = stock_code
new_amount = vol * buy_price
new_revenue = 0
new_cost_price = buy_price
new_volume = vol
new_margin = 0
new_side = "buy"
sql_position_insert = "insert into my_position(trdate,code,cost_price,revenue,volume,amount,margin,side) " \
"VALUES ('%s','%s',%.2f,%.2f,%.2f,%.2f,%.2f,'%s')" \
% (opdate,new_code, new_cost_price, new_revenue, new_volume, new_amount, new_margin, new_side
)
db.execute(sql_position_insert)
return 1
else:
print("买入现金余额不足 " + str(stock_code) + str(opdate) + str(buy_money))
db.close()
return 0
def sell(stock_code, opdate, sell_money, trade_side):
# 建立数据库连接
db = ToMysql()
ts.set_token('502bcbdbac29edf1c42ed84d5f9bd24d63af6631919820366f53e5d4')
pro = ts.pro_api()
deal_sell = Deal.Deal(opdate)
sql_sell_select = "select * from stock_info a where a.state_dt = '%s' and a.stock_code = '%s'" \
% (opdate, stock_code)
done_set_sell_select = db.select(sql_sell_select)
if len(done_set_sell_select) == 0:
print("缺少卖出股票当日行情数据" + str(stock_code) + " " + str(opdate))
opdate2 = (datetime.strptime(opdate, "%Y-%m-%d")).strftime('%Y%m%d')
resu = pro.daily(ts_code=stock_code, trade_date=opdate2)
if len(resu) != 0:
print("已经从互联网获取数据" + str(stock_code) + " " + str(opdate))
sell_price = resu["pre_close"][0]
else:
print(
str(stock_code) + " 停牌无法卖出" + str(stock_code) + " " +
str(opdate))
return
sql_insert = "INSERT INTO stock_all(state_dt,stock_code,open,close,high,low,vol,amount,pre_close,amt_change,pct_change) VALUES ('%s', '%s', '%.2f', '%.2f','%.2f','%.2f','%i','%.2f','%.2f','%.2f','%.2f')" % (
opdate, str(resu.iloc[0][0]), float(resu.iloc[0][2]),
float(resu.iloc[0][5]), float(resu.iloc[0][3]),
float(resu.iloc[0][4]), float(resu.iloc[0][9]),
float(resu.iloc[0][10]), float(resu.iloc[0][6]),
float(resu.iloc[0][7]), float(resu.iloc[0][8]))
db.execute(sql_insert)
else:
sell_price = float(done_set_sell_select[0][8])
if sell_money <= deal_sell.stock_amount[stock_code]:
print("卖出数量为 " + str(sell_money) + " " + str(stock_code) +
str(opdate))
vol = sell_money / sell_price
# 更新账户表my_capital######
new_capital = deal_sell.cur_total_asset # 卖出净资产不变
new_available_fund = deal_sell.cur_available_fund + sell_money # 增加相应的现金。
new_holding_value = deal_sell.cur_holding_value - sell_money # 减少持仓市值
new_margin = 0 # 先不填这个坑
sql_sellorder_update = "UPDATE my_capital SET available_fund = %.2f," \
"holding_value = %.2f," \
"margin = %.2f," \
"total_asset = %.2f " \
"WHERE date = '%s' " % (
new_available_fund, new_holding_value, new_margin, new_capital, opdate)
db.execute(sql_sellorder_update)
# 更新position cash
sql_position_cash = "UPDATE my_position SET code = '%s'," \
"cost_price = %.2f,revenue = %.2f," \
"volume = %.2f,amount = %.2f,margin= %.2f,side='%s' WHERE code = '%s' AND trdate = '%s' " \
% ("cash", 1, 0, float(new_available_fund),float(new_available_fund), 0, "buy",
"cash", opdate)
db.execute(sql_position_cash)
# 更新orders表###
new_stock_code = stock_code
new_order_time = opdate
new_trade_side = trade_side
new_volume = vol
new_price = sell_price
new_amount = vol * sell_price
new_err_msg = 1
sql_order_insert = "insert into orders(stock_code,order_time,trade_side,volume,price,amount,err_msg) " \
"VALUES ('%s','%s','%s',%.2f,%.2f,%.2f,'%s')" \
% (
new_stock_code, new_order_time, new_trade_side, new_volume, new_price, new_amount, new_err_msg
)
db.execute(sql_order_insert)
# 更新position表
new_code = stock_code
new_amount = deal_sell.stock_amount[stock_code] - vol * sell_price
new_revenue = deal_sell.stock_revenue[stock_code]
new_volume = new_amount / sell_price
if new_volume != 0:
new_cost_price = (new_amount - new_revenue) / new_volume
else:
new_cost_price = 0
new_margin = 0
new_side = "buy"
if new_amount == 0:
sql_position_delete = "DELETE FROM my_position WHERE code = '%s' AND trdate = '%s'" % (
stock_code, opdate)
db.execute(sql_position_delete)
else:
sql_position_update = "UPDATE my_position SET code = '%s'," \
"cost_price = %.2f,revenue = %.2f," \
"volume = %.2f,amount = %.2f,margin= %.2f,side='%s'" \
"WHERE code = '%s' AND trdate = '%s'" \
% (new_code, new_cost_price, new_revenue, new_volume, new_amount, new_margin,
new_side,new_code,opdate)
db.execute(sql_position_update)
else:
print("卖出金额超限" + str(stock_code) + " " + str(opdate))
db.close()
return 0