def fix_daily_by_tick(contlist, sdate, edate, is_forced=False):
res = {}
for inst in contlist:
product = misc.inst2product(inst)
start_tick= 1500000
end_tick = 2100000
if product in misc.night_session_markets:
start_tick = 300000
elif product in ['IF','TF']:
start_tick = 1515000
end_tick = 2115000
ddf = mysqlaccess.load_daily_data_to_df('fut_daily', inst, sdate, edate)
tdf = mysqlaccess.load_tick_to_df('fut_tick', inst, sdate, edate, start_tick=start_tick, end_tick = end_tick)
for d in list(set(tdf.date)):
if (is_forced) or (d not in ddf.index) or (ddf.ix(d, 'open')==0):
df = tdf[tdf['date']==d].sort(['tick_id'])
ddata = {}
ddata['date'] = d
ddata['open'] = float(df.iloc[0].price)
ddata['close'] = float(df.iloc[-1].price)
ddata['high'] = float(df.iloc[-1].high)
ddata['low'] = float(df.iloc[-1].low)
ddata['volume'] = int(df.iloc[-1].volume)
ddata['openInterest'] = int(df.iloc[-1].openInterest)
print inst, ddata
res[(inst,d)] = ddata
mysqlaccess.insert_daily_data(inst, ddata, is_forced)
return res
def fix_daily_by_tick(contlist, sdate, edate, is_forced=False):
res = {}
for inst in contlist:
product = misc.inst2product(inst)
start_tick = 1500000
end_tick = 2100000
if product in misc.night_session_markets:
start_tick = 300000
elif product in ['IF', 'TF']:
start_tick = 1515000
end_tick = 2115000
ddf = mysqlaccess.load_daily_data_to_df('fut_daily', inst, sdate,
edate)
tdf = mysqlaccess.load_tick_to_df('fut_tick',
inst,
sdate,
edate,
start_tick=start_tick,
end_tick=end_tick)
for d in list(set(tdf.date)):
if (is_forced) or (d not in ddf.index) or (ddf.ix(d, 'open') == 0):
df = tdf[tdf['date'] == d].sort(['tick_id'])
ddata = {}
ddata['date'] = d
ddata['open'] = float(df.iloc[0].price)
ddata['close'] = float(df.iloc[-1].price)
ddata['high'] = float(df.iloc[-1].high)
ddata['low'] = float(df.iloc[-1].low)
ddata['volume'] = int(df.iloc[-1].volume)
ddata['openInterest'] = int(df.iloc[-1].openInterest)
print inst, ddata
res[(inst, d)] = ddata
mysqlaccess.insert_daily_data(inst, ddata, is_forced)
return res
def conv_db_htick2min(db_table,
inst_file,
out_table='hist_fut_min',
database='hist_data',
dstep=10):
conf_dict = {}
instIDs = []
if inst_file == '':
instIDs = get_col_dist_values(database + '.' + db_table, 'instID', {})
conf_dict = {'instIDs': instIDs}
try:
inst_file = 'instID_file.json'
with open(inst_file, 'w') as ofile:
json.dump(conf_dict, ofile)
except:
pass
else:
with open(inst_file, 'r') as infile:
conf_dict = json.load(infile)
instIDs = conf_dict['instIDs']
dbconfig = {
'user': '******',
'password': '******',
'host': 'localhost',
'database': database
}
cnx = mysqlconn.connect(**dbconfig)
for inst in instIDs:
field_dict = {'instID': "\'" + inst + "\'"}
datestr_list = get_col_dist_values(database + '.' + db_table, 'date',
field_dict)
mdata = pd.DataFrame()
prod = misc.inst2product(inst)
exch = misc.inst2exch(inst)
num_run = (len(datestr_list) + dstep - 1) / dstep
for idx in range(num_run):
s_idx = idx * dstep
e_idx = min((idx + 1) * dstep - 1, len(datestr_list) - 1)
sdate = datetime.datetime.strptime(datestr_list[s_idx],
"%Y-%m-%d").date()
edate = datetime.datetime.strptime(datestr_list[e_idx],
"%Y-%m-%d").date()
df = load_hist_tick(db_table, inst, sdate, edate)
mdf = conv_ohlc_freq(df, '1Min')
mdf['min_id'] = (
(mdf.index.hour + 6) % 24) * 100 + mdf.index.minute
mdf = backtest.cleanup_mindata(mdf, prod)
mdf.index.name = 'datetime'
mdf['instID'] = inst
mdf['exch'] = exch
mdf = mdf.reset_index()
mdf.set_index(['instID', 'exch', 'datetime'], inplace=True)
mdf.to_sql(name=out_table,
flavor='mysql',
con=cnx,
if_exists='append')
cnx.commit()
print inst, sdate, edate, len(mdf)
cnx.close()
return
def get_wind_data(inst_list, start_date, end_date, save_loc='C:\\dev\\data\\', freq='m'):
exch_map = {v: k for k, v in wind_exch_map.items()}
for instID in inst_list:
exch = misc.inst2exch(instID)
ex = exch_map[exch]
ticker = instID + '.' + ex
product = misc.inst2product(instID)
sdate = start_date
edate = end_date
stime = datetime.time(9, 0, 0)
etime = datetime.time(15, 0, 0)
if product in ['T', 'TF']:
stime = datetime.time(9, 15, 0)
etime = datetime.time(15, 15, 0)
elif product in misc.night_session_markets:
stime = datetime.time(21, 0, 0)
sdate = misc.day_shift(sdate, '-1b')
smin = datetime.datetime.combine(sdate, stime)
emin = datetime.datetime.combine(edate, etime)
fields = 'open,high,low,close,volume,oi'
try:
if freq == 'm':
outfile = save_loc + instID + '_min.csv'
if os.path.isfile(outfile):
continue
raw_data = w.wsi(ticker, fields, smin, emin)
if len(raw_data.Data) > 1:
output = {'datetime': raw_data.Times,
'open': raw_data.Data[0],
'high': raw_data.Data[1],
'low': raw_data.Data[2],
'close': raw_data.Data[3],
'volume': raw_data.Data[4],
'openInterest': raw_data.Data[5]}
dump2csvfile(output, outfile)
else:
print "no min data obtained for ticker=%s" % ticker
elif freq == 'd':
outfile = save_loc + instID + '_daily.csv'
if os.path.isfile(outfile):
continue
raw_data = w.wsd(ticker, fields, start_date, end_date)
if len(raw_data.Data) > 1:
output = {'datetime': raw_data.Times,
'open': raw_data.Data[0],
'high': raw_data.Data[1],
'low': raw_data.Data[2],
'close': raw_data.Data[3],
'volume': raw_data.Data[4],
'openInterest': raw_data.Data[5]}
dump2csvfile(output, outfile)
else:
print "no daily data obtained for ticker=%s" % ticker
except ValueError:
pass
w.stop()
return True
def filter_main_cont(sdate):
insts, prods = mysqlaccess.load_alive_cont(sdate)
main_cont = {}
for pc in prods:
main_cont[pc], exch = mysqlaccess.prod_main_cont_exch(pc)
main_insts = []
for inst in insts:
pc = misc.inst2product(inst)
mth = int(inst[-2:])
if mth in main_cont[pc]:
main_insts.append(inst)
return main_insts
def filter_main_cont(sdate):
insts, prods = mysqlaccess.load_alive_cont(sdate)
main_cont = {}
for pc in prods:
main_cont[pc], exch = mysqlaccess.prod_main_cont_exch(pc)
main_insts = []
for inst in insts:
pc = misc.inst2product(inst)
mth = int(inst[-2:])
if mth in main_cont[pc]:
main_insts.append(inst)
return main_insts
def create_strat_json(df,
inst_list,
asset_keys,
common_keys,
capital=4000.0,
strat_class="strat_ma_system.MASystemTrader"):
xdf = df.dropna(subset=['name'])
inst_dict = dict([(misc.inst2product(instID), instID)
for instID in inst_list])
xdf['instID'] = xdf['asset'].apply(lambda x: inst_dict[x])
output = OrderedDict()
sim_names = xdf['sim_name'].unique()
sim_dict = load_sim_config(sim_names, config_folder=sim_config_folder)
for idx, row in xdf.iterrows():
if row['name'] not in output:
output[row['name']] = {'class': strat_class,
'config': OrderedDict([('name', row['name']), ('num_tick', 1), ('daily_close_buffer', 5), \
('pos_scaler', 1.0), ('trade_valid_time', 600), ]),}
for key in common_keys:
if key in xdf:
if isinstance(row[key],
basestring) and ('[' in row[key]
and ']' in row[key]):
output[row['name']]['config'][key] = json.loads(
row[key])
else:
output[row['name']]['config'][key] = row[key]
elif key in sim_dict[row['sim_name']]:
output[row['name']]['config'][key] = sim_dict[
row['sim_name']][key]
elif key in sim_dict[row['sim_name']]['config']:
output[row['name']]['config'][key] = sim_dict[
row['sim_name']]['config'][key]
output[row['name']]['config']['assets'] = []
conf_dict = OrderedDict()
conf_dict["underliers"] = [row['instID']]
for key in asset_keys:
if key == 'alloc_w':
conf_dict[key] = round(
capital / row['std_unit'] * row['w_sharp'], 1)
elif key in xdf:
if isinstance(row[key], basestring) and ('[' in row[key]
and ']' in row[key]):
conf_dict[key] = json.loads(row[key])
else:
conf_dict[key] = row[key]
elif key in sim_dict[row['sim_name']]:
conf_dict[key] = sim_dict[row['sim_name']][key]
elif key in sim_dict[row['sim_name']]['config']:
conf_dict[key] = sim_dict[row['sim_name']]['config'][key]
output[row['name']]['config']['assets'].append(conf_dict)
return output
def conv_db_htick2min(db_table, inst_file, out_table = 'hist_fut_min', database = 'hist_data', dstep = 10):
conf_dict = {}
instIDs = []
if inst_file == '':
instIDs = get_col_dist_values(database + '.' + db_table, 'instID',{})
conf_dict = {'instIDs': instIDs}
try:
inst_file = 'instID_file.json'
with open(inst_file, 'w') as ofile:
json.dump(conf_dict, ofile)
except:
pass
else:
with open(inst_file, 'r') as infile:
conf_dict = json.load(infile)
instIDs = conf_dict['instIDs']
dbconfig = {'user': '******', 'password':'******', 'host':'localhost', 'database': database}
cnx = mysqlconn.connect(**dbconfig)
for inst in instIDs:
field_dict = {'instID': "\'"+inst+"\'"}
datestr_list = get_col_dist_values(database + '.' + db_table, 'date', field_dict)
mdata = pd.DataFrame()
prod = misc.inst2product(inst)
exch = misc.inst2exch(inst)
num_run = (len(datestr_list)+dstep-1)/dstep
for idx in range(num_run):
s_idx = idx * dstep
e_idx = min((idx + 1) *dstep - 1, len(datestr_list)-1)
sdate = datetime.datetime.strptime(datestr_list[s_idx], "%Y-%m-%d").date()
edate = datetime.datetime.strptime(datestr_list[e_idx], "%Y-%m-%d").date()
df = load_hist_tick(db_table, inst, sdate, edate)
mdf = conv_ohlc_freq(df, '1Min')
mdf['min_id'] = ((mdf.index.hour + 6) % 24) * 100 + mdf.index.minute
mdf = backtest.cleanup_mindata(mdf, prod)
mdf.index.name = 'datetime'
mdf['instID'] = inst
mdf['exch'] = exch
mdf = mdf.reset_index()
mdf.set_index(['instID', 'exch', 'datetime'], inplace = True)
mdf.to_sql(name = out_table, flavor = 'mysql', con = cnx, if_exists='append')
cnx.commit()
print inst, sdate, edate, len(mdf)
cnx.close()
return
def create_strat_json(df, inst_list, asset_keys, common_keys, capital = 4000.0, strat_class = "strat_ma_system.MASystemTrader"):
xdf = df.dropna(subset = ['name'])
inst_dict = dict([(misc.inst2product(instID), instID) for instID in inst_list])
xdf['instID'] = xdf['asset'].apply(lambda x: inst_dict[x])
output = OrderedDict()
sim_names = xdf['sim_name'].unique()
sim_dict = load_sim_config(sim_names, config_folder = sim_config_folder)
for idx, row in xdf.iterrows():
if row['name'] not in output:
output[row['name']] = {'class': strat_class,
'config': OrderedDict([('name', row['name']), ('num_tick', 1), ('daily_close_buffer', 5), \
('pos_scaler', 1.0), ('trade_valid_time', 600), ]),}
for key in common_keys:
if key in xdf:
if isinstance(row[key], basestring) and ('[' in row[key] and ']' in row[key]):
output[row['name']]['config'][key] = json.loads(row[key])
else:
output[row['name']]['config'][key] = row[key]
elif key in sim_dict[row['sim_name']]:
output[row['name']]['config'][key] = sim_dict[row['sim_name']][key]
elif key in sim_dict[row['sim_name']]['config']:
output[row['name']]['config'][key] = sim_dict[row['sim_name']]['config'][key]
output[row['name']]['config']['assets'] = []
conf_dict = OrderedDict()
conf_dict["underliers"] = [row['instID']]
for key in asset_keys:
if key == 'alloc_w':
conf_dict[key] = round(capital/row['std_unit'] * row['w_sharp'], 1)
elif key in xdf:
if isinstance(row[key], basestring) and ('[' in row[key] and ']' in row[key]):
conf_dict[key] = json.loads(row[key])
else:
conf_dict[key] = row[key]
elif key in sim_dict[row['sim_name']]:
conf_dict[key] = sim_dict[row['sim_name']][key]
elif key in sim_dict[row['sim_name']]['config']:
conf_dict[key] = sim_dict[row['sim_name']]['config'][key]
output[row['name']]['config']['assets'].append(conf_dict)
return output