def get_cont_data(asset,
start_date,
end_date,
freq='1m',
nearby=1,
rollrule='-10b'):
cnx = dbaccess.connect(**dbaccess.hist_dbconfig)
if nearby == 0:
mdf = dbaccess.load_min_data_to_df(cnx,
'fut_min',
asset,
start_date,
end_date,
minid_start=300,
minid_end=2114,
database='hist_data')
mdf['contract'] = asset
else:
mdf = misc.nearby(asset,
nearby,
start_date,
end_date,
rollrule,
'm',
need_shift=True,
database='hist_data')
mdf = backtest.cleanup_mindata(mdf, asset)
xdf = dh.conv_ohlc_freq(mdf,
freq,
extra_cols=['contract'],
bar_func=dh.bar_conv_func2)
return xdf
def validate_db_data(tday, filter=False):
all_insts = filter_main_cont(tday, filter)
data_count = {}
inst_list = {'min': [], 'daily': []}
cnx = dbaccess.connect(**dbaccess.dbconfig)
for instID in all_insts:
df = dbaccess.load_daily_data_to_df(cnx, 'fut_daily', instID, tday,
tday)
if len(df) <= 0:
inst_list['daily'].append(instID)
elif (df.close[-1] == 0) or (df.high[-1]
== 0) or (df.low[-1]
== 0) or df.open[-1] == 0:
inst_list['daily'].append(instID)
df = dbaccess.load_min_data_to_df(cnx,
'fut_min',
instID,
tday,
tday,
minid_start=300,
minid_end=2115,
database='blueshale')
if len(df) <= 100:
output = instID + ':' + str(len(df))
inst_list['min'].append(output)
elif df.min_id < 2055:
output = instID + ': end earlier'
inst_list['min'].append(output)
cnx.close()
print inst_list
def load_data(self, assets):
contlist = {}
exp_dates = {}
dbconfig = self.config.get('dbconfig', dbaccess.hist_dbconfig)
cnx = dbaccess.connect(**dbconfig)
for i, prod in enumerate(assets):
cont_mth, exch = dbaccess.prod_main_cont_exch(prod)
self.contlist[prod], _ = misc.contract_range(prod, exch, cont_mth, self.start_date, self.end_date)
self.exp_dates[prod] = [misc.contract_expiry(cont) for cont in contlist[prod]]
edates = [ misc.day_shift(d, self.config['rollrule']) for d in exp_dates[prod] ]
sdates = [ misc.day_shift(d, self.sim_period) for d in exp_dates[prod] ]
self.data_store[prod] = {}
for cont, sd, ed in zip(contlist[prod], sdates, edates):
if self.sim_freq == 'd':
tmp_df = dbaccess.load_daily_data_to_df(cnx, 'fut_min', cont, sd, ed)
else:
minid_start = 1500
minid_end = 2114
if prod in misc.night_session_markets:
minid_start = 300
tmp_df = dbaccess.load_min_data_to_df(cnx, 'fut_min', cont, sd, ed, minid_start, minid_end)
misc.cleanup_mindata(tmp_df, prod)
tmp_df['contract'] = cont
self.data_store[prod][cont] = tmp_df
cnx.close()
def load_data(self, assets):
contlist = {}
exp_dates = {}
dbconfig = self.config.get('dbconfig', dbaccess.hist_dbconfig)
cnx = dbaccess.connect(**dbconfig)
for i, prod in enumerate(assets):
cont_mth, exch = dbaccess.prod_main_cont_exch(prod)
self.contlist[prod], _ = misc.contract_range(
prod, exch, cont_mth, self.start_date, self.end_date)
self.exp_dates[prod] = [
misc.contract_expiry(cont) for cont in contlist[prod]
]
edates = [
misc.day_shift(d, self.config['rollrule'])
for d in exp_dates[prod]
]
sdates = [
misc.day_shift(d, self.sim_period) for d in exp_dates[prod]
]
self.data_store[prod] = {}
for cont, sd, ed in zip(contlist[prod], sdates, edates):
if self.sim_freq == 'd':
tmp_df = dbaccess.load_daily_data_to_df(
cnx, 'fut_min', cont, sd, ed)
else:
minid_start = 1500
minid_end = 2114
if prod in misc.night_session_markets:
minid_start = 300
tmp_df = dbaccess.load_min_data_to_df(
cnx, 'fut_min', cont, sd, ed, minid_start, minid_end)
misc.cleanup_mindata(tmp_df, prod)
tmp_df['contract'] = cont
self.data_store[prod][cont] = tmp_df
cnx.close()
def load_data(self, idx):
asset = self.sim_assets[idx]
for prod in asset:
if prod in self.data_store:
continue
ticker = prod
if '$' not in ticker:
ticker_sp = [ticker, 'spot']
else:
ticker_sp = ticker.split('$')
ticker = ticker_sp[0]
postfix = '_daily'
if self.sim_freq == 'm':
postfix = '_min'
dbtable = ticker_sp[-1] + postfix
if ticker_sp[-1] in ['spot']:
field_id = 'spotID'
elif ticker_sp[-1] in ['ccy']:
field_id = 'instID'
if len(ticker_sp) > 2:
nb = int(ticker_sp[1])
if len(ticker_sp) > 3:
rollrule = ticker_sp[2]
else:
rollrule = '-1b'
df = misc.nearby(ticker,
nb,
self.config['start_date'],
self.config['end_date'],
rollrule,
self.sim_freq,
need_shift=self.need_shift,
database=self.config.get(
'dbconfig',
dbaccess.dbconfig)['database'])
else:
cnx = dbaccess.connect(
**self.config.get('dbconfig', dbaccess.dbconfig))
if self.sim_freq == 'd':
df = dbaccess.load_daily_data_to_df(
cnx,
dbtable,
ticker,
self.config['start_date'],
self.config['end_date'],
index_col='date',
field=field_id)
else:
minid_start = 1500
minid_end = 2114
if ticker in misc.night_session_markets:
minid_start = 300
df = dbaccess.load_min_data_to_df(
cnx, dbtable, ticker, self.config['start_date'],
self.config['end_date'], minid_start, minid_end)
df['contract'] = ticker
if self.sim_freq == 'm':
df = misc.cleanup_mindata(df, ticker)
df.columns = [(prod, col) for col in df.columns]
self.data_store[prod] = df
def spd_ratiovol_by_product(products, start_d, end_d, periods=12, tenor='-1m'):
cont_mth, exch = dbaccess.prod_main_cont_exch(products)
contlist = contract_range(products, exch, cont_mth, start_d, end_d)
exp_dates = [get_opt_expiry(cont, inst2contmth(cont)) for cont in contlist]
data = {
'is_dtime': True,
'data_column': 'close',
'data_freq': '30min',
'xs': [0.5, 0.25, 0.75],
'xs_names': ['atm', 'v25', 'v75'],
'xs_func': 'bs_delta_to_strike',
'rehedge_period': 1,
'term_tenor': tenor,
'database': 'hist_data'
}
option_input = {
'otype': True,
'ir': 0.0,
'end_vol': 0.0,
'ref_vol': 0.5,
'pricer_func': 'bsopt.BSOpt',
'delta_func': 'bsopt.BSDelta',
'is_dtime': data['is_dtime'],
}
freq = data['data_freq']
for cont, expiry in zip(contlist, exp_dates):
expiry_d = expiry.date()
if expiry_d > end_d:
break
p_str = '-' + str(int(tenor[1:-1]) * periods) + tenor[-1]
d_start = day_shift(expiry_d, p_str)
cnx = dbaccess.connect(**dbaccess.dbconfig)
if freq == 'd':
df = dbaccess.load_daily_data_to_df(cnx,
'fut_daily',
cont,
d_start,
expiry_d,
index_col=None)
else:
mdf = dbaccess.load_min_data_to_df(cnx,
'fut_min',
cont,
d_start,
expiry_d,
minid_start=300,
minid_end=2115,
index_col=None)
mdf = cleanup_mindata(mdf, products, index_col=None)
mdf['bar_id'] = dh.bar_conv_func2(mdf['min_id'])
df = dh.conv_ohlc_freq(mdf,
freq,
bar_func=dh.bar_conv_func2,
extra_cols=['bar_id'],
index_col=None)
cnx.close()
option_input['expiry'] = expiry
data['dataframe'] = df
vol_df = realized_termstruct(option_input, data)
print cont, expiry_d, vol_df
def get_cont_data(asset, start_date, end_date, freq = '1m', nearby = 1, rollrule = '-10b'):
cnx = dbaccess.connect(**dbaccess.hist_dbconfig)
if nearby == 0:
mdf = dbaccess.load_min_data_to_df(cnx, 'fut_min', asset, start_date, end_date, minid_start = 300, minid_end = 2114, database = 'hist_data')
mdf['contract'] = asset
else:
mdf = misc.nearby(asset, nearby, start_date, end_date, rollrule, 'm', need_shift=True, database = 'hist_data')
mdf = misc.cleanup_mindata(mdf, asset)
xdf = dh.conv_ohlc_freq(mdf, freq, extra_cols = ['contract'], bar_func = dh.bar_conv_func2)
return xdf
def nearby(prodcode, n, start_date, end_date, roll_rule, freq, need_shift=False, database='C:/Users/j291414/Desktop/market_data.db'):
if start_date > end_date:
return None
cont_mth, exch = dbaccess.prod_main_cont_exch(prodcode)
contlist = contract_range(prodcode, exch, cont_mth, start_date, day_shift(end_date, roll_rule[1:]))
exp_dates = [day_shift(contract_expiry(cont), roll_rule) for cont in contlist]
# print contlist, exp_dates
sdate = start_date
is_new = True
# dbconf = copy.deepcopy(dbaccess.dbconfig)
# dbconf['database'] = database
# cnx = dbaccess.connect(**dbconf)
cnx = sqlite3.connect(database)
for idx, exp in enumerate(exp_dates):
if exp < start_date:
continue
elif sdate > end_date:
break
nb_cont = contlist[idx + n - 1]
if freq == 'd':
new_df = dbaccess.load_daily_data_to_df(cnx, 'fut_daily', nb_cont, sdate, min(exp, end_date))
else:
minid_start = 1500
minid_end = 2114
if prodcode in night_session_markets:
minid_start = 300
new_df = dbaccess.load_min_data_to_df(cnx, 'fut_min', nb_cont, sdate, min(exp, end_date), minid_start,
minid_end)
if len(new_df.shape) == 0:
continue
nn = new_df.shape[0]
if nn > 0:
new_df['contract'] = pd.Series([nb_cont] * nn, index=new_df.index)
else:
continue
if is_new:
df = new_df
is_new = False
else:
if need_shift:
if isinstance(df.index[-1], datetime.datetime):
last_date = df.index[-1].date()
else:
last_date = df.index[-1]
tmp_df = dbaccess.load_daily_data_to_df(cnx, 'fut_daily', nb_cont, last_date, last_date)
shift = tmp_df['close'][-1] - df['close'][-1]
for ticker in ['open', 'high', 'low', 'close']:
df[ticker] = df[ticker] + shift
df = df.append(new_df)
sdate = min(exp, end_date) + datetime.timedelta(days=1)
cnx.close()
return df
def hist_cso_by_product(prodcode, start_d, end_d, periods = 24, tenor = '-1w', max_spd = 2, writeDB = False, mode = 'n'):
cont_mth, exch = dbaccess.prod_main_cont_exch(prodcode)
contlist, _ = contract_range(prodcode, exch, cont_mth, start_d, end_d)
exp_dates = [get_opt_expiry(cont, inst2contmth(cont)) for cont in contlist]
if mode == 'n':
xs_func = 'bachelier_delta_to_strike'
pricer_func = 'bsopt.BSFwdNormal'
delta_func = 'bsopt.BSFwdNormalDelta'
else:
xs_func = 'bs_delta_to_strike'
pricer_func = 'bsopt.BSOpt'
delta_func = 'bsopt.BSDelta'
data = {'is_dtime': True,
'data_column': 'close',
'data_freq': '30min',
'xs': [0.5, 0.25, 0.75],
'xs_names': ['atm', 'v25', 'v75'],
'xs_func': xs_func,
'rehedge_period': 1,
'term_tenor': tenor,
'database': 'hist_data'
}
option_input = {'otype': True,
'ir': 0.0,
'end_vol': 0.0,
'ref_vol': 0.5,
'pricer_func': pricer_func,
'delta_func': delta_func,
'is_dtime': data['is_dtime'],
}
freq = data['data_freq']
dbconfig = copy.deepcopy(**dbaccess.hist_dbconfig)
dbconfig['database'] = data['database']
cnx = dbaccess.connect(**dbconfig)
for cont, expiry in zip(contlist, exp_dates):
expiry_d = expiry.date()
if expiry_d > end_d:
break
p_str = '-' + str(int(tenor[1:-1]) * periods) + tenor[-1]
d_start = day_shift(expiry_d, p_str)
if freq == 'd':
df = dbaccess.load_daily_data_to_df(cnx, 'fut_daily', cont, d_start, expiry_d, index_col = None)
else:
mdf = dbaccess.load_min_data_to_df(cnx, 'fut_min', cont, d_start, expiry_d, minid_start=300,
minid_end=2115, index_col = None)
mdf = cleanup_mindata(mdf, prodcode, index_col = None)
mdf['bar_id'] = dh.bar_conv_func2(mdf['min_id'])
df = dh.conv_ohlc_freq(mdf, freq, bar_func=dh.bar_conv_func2, extra_cols=['bar_id'], index_col = None)
cnx.close()
option_input['expiry'] = expiry
data['dataframe'] = df
vol_df = realized_termstruct(option_input, data)
print cont, expiry_d, vol_df
def nearby(prodcode, n = 1, start_date = None, end_date = None, roll_rule = '-20b', freq = 'd', need_shift=False, database = None):
contlist, exp_dates, _ = cont_expiry_list(prodcode, start_date, end_date, roll_rule)
sdate = start_date
is_new = True
dbconf = copy.deepcopy(dbaccess.dbconfig)
if database:
dbconf['database'] = database
cnx = dbaccess.connect(**dbconf)
for idx, exp in enumerate(exp_dates):
if exp < start_date:
continue
elif sdate > end_date:
break
nb_cont = contlist[idx + n - 1]
if freq == 'd':
new_df = dbaccess.load_daily_data_to_df(cnx, 'fut_daily', nb_cont, sdate, min(exp, end_date))
else:
minid_start = 1500
minid_end = 2114
if prodcode in night_session_markets:
minid_start = 300
new_df = dbaccess.load_min_data_to_df(cnx, 'fut_min', nb_cont, sdate, min(exp, end_date), minid_start,
minid_end)
if len(new_df.shape) == 0:
continue
nn = new_df.shape[0]
if nn > 0:
new_df['contract'] = pd.Series([nb_cont] * nn, index=new_df.index)
else:
continue
if is_new:
df = new_df
is_new = False
else:
if need_shift > 0:
if isinstance(df.index[-1], datetime.datetime):
last_date = df.index[-1].date()
else:
last_date = df.index[-1]
tmp_df = dbaccess.load_daily_data_to_df(cnx, 'fut_daily', nb_cont, last_date, last_date)
if need_shift == 1:
shift = tmp_df['close'][-1] - df['close'][-1]
for ticker in ['open', 'high', 'low', 'close']:
df[ticker] = df[ticker] + shift
else:
shift = float(tmp_df['close'][-1])/float(df['close'][-1])
for ticker in ['open', 'high', 'low', 'close']:
df[ticker] = df[ticker] * shift
df = df.append(new_df)
sdate = exp + datetime.timedelta(days=1)
return df
def rolling_hist_data(product,
n,
start_date,
end_date,
cont_roll,
freq,
win_roll='-20b',
database='hist_data'):
if start_date > end_date:
return None
cnx = dbaccess.connect(**dbaccess.dbconfig)
cursor = cnx.cursor()
stmt = "select exchange, contract from trade_products where product_code='{prod}' ".format(
prod=product)
cursor.execute(stmt)
out = [(exchange, contract) for (exchange, contract) in cursor]
exch = str(out[0][0])
cont = str(out[0][1])
cont_mth = [month_code_map[c] for c in cont]
cnx.close()
contlist = contract_range(product, exch, cont_mth, start_date, end_date)
exp_dates = [
day_shift(contract_expiry(cont), cont_roll) for cont in contlist
]
# print contlist, exp_dates
sdate = start_date
all_data = {}
i = 0
dbconfig = copy.deepcopy(dbaccess.dbconfig)
dbconfig['database'] = database
cnx = dbaccess.connect(**dbconfig)
for idx, exp in enumerate(exp_dates):
if exp < start_date:
continue
elif sdate > end_date:
break
nb_cont = contlist[idx + n - 1]
if freq == 'd':
df = dbaccess.load_daily_data_to_df(cnx, 'fut_daily', nb_cont,
day_shift(sdate, win_roll),
min(exp, end_date))
else:
df = dbaccess.load_min_data_to_df(cnx, 'fut_min', nb_cont,
day_shift(sdate, win_roll),
min(exp, end_date))
all_data[i] = {'contract': nb_cont, 'data': df}
i += 1
sdate = min(exp, end_date) + datetime.timedelta(days=1)
cnx.close()
return all_data
def process_trade_date(cnx, db_table='fut_min', col_name='instID'):
stmt = 'select distinct({colname}) from {dbtable}'.format(colname=col_name,
dbtable=db_table)
cursor = cnx.cursor()
cursor.execute(stmt)
inst_list = []
for line in cursor:
inst_list.append(str(line[0]))
for inst in inst_list:
print "processing instID = %s" % inst
df = dbaccess.load_min_data_to_df(cnx, db_table, inst, index_col=None)
df['datetime'] = df['datetime'].apply(
lambda x: datetime.datetime.strptime(x, "%Y-%m-%d %H:%M:%S"))
df = process_min_id(df)
df.to_sql('fut_min', cnx, 'sqlite', if_exists='append', index=False)
def load_data(self, idx):
asset = self.sim_assets[idx]
for prod in asset:
if prod in self.data_store:
continue
ticker = prod
if '$' not in ticker:
ticker_sp = [ticker, 'spot']
else:
ticker_sp = ticker.split('$')
ticker = ticker_sp[0]
postfix = '_daily'
if self.sim_freq == 'm':
postfix = '_min'
dbtable = ticker_sp[-1] + postfix
if ticker_sp[-1] in ['spot']:
field_id = 'spotID'
elif ticker_sp[-1] in ['ccy']:
field_id = 'instID'
if len(ticker_sp) > 2:
nb = int(ticker_sp[1])
if len(ticker_sp) > 3:
rollrule = ticker_sp[2]
else:
rollrule = '-1b'
df = misc.nearby(ticker, nb, self.config['start_date'], self.config['end_date'], rollrule,
self.sim_freq, need_shift = self.need_shift,
database = self.config.get('dbconfig', dbaccess.dbconfig)['database'])
else:
cnx = dbaccess.connect(**self.config.get('dbconfig', dbaccess.dbconfig))
if self.sim_freq == 'd':
df = dbaccess.load_daily_data_to_df(cnx, dbtable, ticker,
self.config['start_date'], self.config['end_date'], index_col='date',
field = field_id)
else:
minid_start = 1500
minid_end = 2114
if ticker in misc.night_session_markets:
minid_start = 300
df = dbaccess.load_min_data_to_df(cnx, dbtable, ticker, self.config['start_date'],
self.config['end_date'], minid_start, minid_end)
df['contract'] = ticker
if self.sim_freq == 'm':
df = misc.cleanup_mindata(df, ticker)
df.columns = [(prod, col) for col in df.columns]
self.data_store[prod] = df
def validate_db_data(tday, filter = False):
all_insts = misc.filter_main_cont(tday, filter)
data_count = {}
inst_list = {'min': [], 'daily': [] }
cnx = dbaccess.connect(**dbaccess.dbconfig)
for instID in all_insts:
df = dbaccess.load_daily_data_to_df(cnx, 'fut_daily', instID, tday, tday)
if len(df) <= 0:
inst_list['daily'].append(instID)
elif (df.close[-1] == 0) or (df.high[-1] == 0) or (df.low[-1] == 0) or df.open[-1] == 0:
inst_list['daily'].append(instID)
df = dbaccess.load_min_data_to_df(cnx, 'fut_min', instID, tday, tday, minid_start=300, minid_end=2115)
if len(df) <= 100:
output = instID + ':' + str(len(df))
inst_list['min'].append(output)
elif df.min_id < 2055:
output = instID + ': end earlier'
inst_list['min'].append(output)
print inst_list
def rolling_hist_data(product, n, start_date, end_date, cont_roll, freq, win_roll='-20b', database='hist_data'):
if start_date > end_date:
return None
cnx = dbaccess.connect(**dbaccess.dbconfig)
cursor = cnx.cursor()
stmt = "select exchange, contract from trade_products where product_code='{prod}' ".format(prod=product)
cursor.execute(stmt)
out = [(exchange, contract) for (exchange, contract) in cursor]
exch = str(out[0][0])
cont = str(out[0][1])
cont_mth = [month_code_map[c] for c in cont]
cnx.close()
contlist, _ = contract_range(product, exch, cont_mth, start_date, end_date)
exp_dates = [day_shift(contract_expiry(cont), cont_roll) for cont in contlist]
sdate = start_date
all_data = {}
i = 0
dbconfig = copy.deepcopy(dbaccess.dbconfig)
dbconfig['database'] = database
cnx = dbaccess.connect(**dbconfig)
for idx, exp in enumerate(exp_dates):
if exp < start_date:
continue
elif sdate > end_date:
break
nb_cont = contlist[idx + n - 1]
if freq == 'd':
df = dbaccess.load_daily_data_to_df(cnx, 'fut_daily', nb_cont, day_shift(sdate, win_roll),
min(exp, end_date))
else:
df = dbaccess.load_min_data_to_df(cnx, 'fut_min', nb_cont, day_shift(sdate, win_roll), min(exp, end_date))
all_data[i] = {'contract': nb_cont, 'data': df}
i += 1
sdate = min(exp, end_date) + datetime.timedelta(days=1)
cnx.close()
return all_data
def prepare_data_env(self, inst, mid_day = True):
if self.instruments[inst].ptype == instrument.ProductType.Option:
return
self.db_conn = dbaccess.connect(**dbaccess.dbconfig)
if self.daily_data_days > 0 or mid_day:
#self.logger.debug('Updating historical daily data for %s' % self.scur_day.strftime('%Y-%m-%d'))
daily_start = workdays.workday(self.scur_day, -self.daily_data_days, CHN_Holidays)
daily_end = self.scur_day
ddf = dbaccess.load_daily_data_to_df(self.db_conn, 'fut_daily', inst, daily_start, daily_end, index_col = None)
if len(ddf) > 0:
self.instruments[inst].price = self.instruments[inst].mid_price = ddf['close'].iloc[-1]
self.instruments[inst].last_update = 0
self.instruments[inst].prev_close = ddf['close'].iloc[-1]
for fobj in self.day_data_func[inst]:
ts = fobj.sfunc(ddf)
if type(ts).__name__ == 'Series':
if ts.name in ddf.columns:
self.logger.warning('TimeSeries name %s is already in the columns for inst = %s' % (ts.name, inst))
ddf[ts.name]= ts
elif type(ts).__name__ == 'DataFrame':
for col_name in ts.columns:
if col_name in ddf.columns:
self.logger.warning('TimeSeries name %s is already in the columns for inst = %s' % (col_name, inst))
ddf[col_name] = ts[col_name]
self.day_data[inst] = data_handler.DynamicRecArray(dataframe = ddf)
if self.min_data_days > 0 or mid_day:
#self.logger.debug('Updating historical min data for %s' % self.scur_day.strftime('%Y-%m-%d'))
d_start = workdays.workday(self.scur_day, -self.min_data_days, CHN_Holidays)
d_end = self.scur_day
min_start = int(self.instruments[inst].start_tick_id/1000)
min_end = int(self.instruments[inst].last_tick_id/1000)+1
mdf = dbaccess.load_min_data_to_df(self.db_conn, 'fut_min', inst, d_start, d_end, minid_start=min_start, minid_end=min_end, index_col = None)
mdf = cleanup_mindata(mdf, self.instruments[inst].product, index_col = None)
mdf['bar_id'] = self.conv_bar_id(mdf['min_id'])
if len(mdf)>0:
min_date = mdf['date'].iloc[-1]
if (len(self.day_data[inst])==0) or (min_date > self.day_data[inst].data['date'][-1]):
ddf = data_handler.conv_ohlc_freq(mdf, 'd', index_col = None)
self.cur_day[inst]['open'] = float(ddf.open[-1])
self.cur_day[inst]['close'] = float(ddf.close[-1])
self.cur_day[inst]['high'] = float(ddf.high[-1])
self.cur_day[inst]['low'] = float(ddf.low[-1])
self.cur_day[inst]['volume'] = int(ddf.volume[-1])
self.cur_day[inst]['openInterest'] = int(ddf.openInterest[-1])
self.cur_min[inst]['datetime'] = pd.datetime(*mdf['datetime'].iloc[-1].timetuple()[0:-3])
self.cur_min[inst]['date'] = mdf['date'].iloc[-1]
self.cur_min[inst]['open'] = float(mdf['open'].iloc[-1])
self.cur_min[inst]['close'] = float(mdf['close'].iloc[-1])
self.cur_min[inst]['high'] = float(mdf['high'].iloc[-1])
self.cur_min[inst]['low'] = float(mdf['low'].iloc[-1])
self.cur_min[inst]['volume'] = self.cur_day[inst]['volume']
self.cur_min[inst]['openInterest'] = self.cur_day[inst]['openInterest']
self.cur_min[inst]['min_id'] = int(mdf['min_id'].iloc[-1])
self.cur_min[inst]['bar_id'] = self.conv_bar_id(self.cur_min[inst]['min_id'])
self.instruments[inst].price = self.instruments[inst].mid_price = float(mdf['close'].iloc[-1])
self.instruments[inst].last_update = 0
#self.logger.debug('inst=%s tick data loaded for date=%s' % (inst, min_date))
if 1 not in self.min_data_func[inst]:
self.min_data[inst][1] = data_handler.DynamicRecArray(dataframe = mdf)
for m in sorted(self.min_data_func[inst]):
if m != 1:
mdf_m = data_handler.conv_ohlc_freq(mdf, str(m)+'min', index_col = None, bar_func = self.conv_bar_id, extra_cols = ['bar_id'])
else:
mdf_m = mdf
for fobj in self.min_data_func[inst][m]:
ts = fobj.sfunc(mdf_m)
if type(ts).__name__ == 'Series':
if ts.name in mdf_m.columns:
self.logger.warning('TimeSeries name %s is already in the columns for inst = %s' % (ts.name, inst))
mdf_m[ts.name]= ts
elif type(ts).__name__ == 'DataFrame':
for col_name in ts.columns:
if col_name in mdf_m.columns:
self.logger.warning('TimeSeries name %s is already in the columns for inst = %s' % (col_name, inst))
mdf_m[col_name] = ts[col_name]
self.min_data[inst][m] = data_handler.DynamicRecArray(dataframe = mdf_m)
#print inst, self.min_data[inst][m].data['date'][-1] < self.cur_min[inst]['date']
self.db_conn.close()
def prepare_data_env(self, inst, mid_day=True):
if self.instruments[inst].ptype == instrument.ProductType.Option:
return
self.db_conn = dbaccess.connect(**dbaccess.dbconfig)
if self.daily_data_days > 0 or mid_day:
#self.logger.debug('Updating historical daily data for %s' % self.scur_day.strftime('%Y-%m-%d'))
daily_start = workdays.workday(self.scur_day,
-self.daily_data_days, CHN_Holidays)
daily_end = self.scur_day
ddf = dbaccess.load_daily_data_to_df(self.db_conn,
'fut_daily',
inst,
daily_start,
daily_end,
index_col=None)
if len(ddf) > 0:
self.instruments[inst].price = self.instruments[
inst].mid_price = ddf['close'].iloc[-1]
self.instruments[inst].last_update = 0
self.instruments[inst].prev_close = ddf['close'].iloc[-1]
for fobj in self.day_data_func[inst]:
ts = fobj.sfunc(ddf)
if type(ts).__name__ == 'Series':
if ts.name in ddf.columns:
self.logger.warning(
'TimeSeries name %s is already in the columns for inst = %s'
% (ts.name, inst))
ddf[ts.name] = ts
elif type(ts).__name__ == 'DataFrame':
for col_name in ts.columns:
if col_name in ddf.columns:
self.logger.warning(
'TimeSeries name %s is already in the columns for inst = %s'
% (col_name, inst))
ddf[col_name] = ts[col_name]
self.day_data[inst] = data_handler.DynamicRecArray(dataframe=ddf)
if self.min_data_days > 0 or mid_day:
#self.logger.debug('Updating historical min data for %s' % self.scur_day.strftime('%Y-%m-%d'))
d_start = workdays.workday(self.scur_day, -self.min_data_days,
CHN_Holidays)
d_end = self.scur_day
min_start = int(self.instruments[inst].start_tick_id / 1000)
min_end = int(self.instruments[inst].last_tick_id / 1000) + 1
mdf = dbaccess.load_min_data_to_df(self.db_conn,
'fut_min',
inst,
d_start,
d_end,
minid_start=min_start,
minid_end=min_end,
index_col=None)
mdf = cleanup_mindata(mdf,
self.instruments[inst].product,
index_col=None)
mdf['bar_id'] = self.conv_bar_id(mdf['min_id'])
if len(mdf) > 0:
min_date = mdf['date'].iloc[-1]
if (len(self.day_data[inst]) == 0) or (
min_date > self.day_data[inst].data['date'][-1]):
ddf = data_handler.conv_ohlc_freq(mdf, 'd', index_col=None)
self.cur_day[inst]['open'] = float(ddf.open[-1])
self.cur_day[inst]['close'] = float(ddf.close[-1])
self.cur_day[inst]['high'] = float(ddf.high[-1])
self.cur_day[inst]['low'] = float(ddf.low[-1])
self.cur_day[inst]['volume'] = int(ddf.volume[-1])
self.cur_day[inst]['openInterest'] = int(
ddf.openInterest[-1])
self.cur_min[inst]['datetime'] = pd.datetime(
*mdf['datetime'].iloc[-1].timetuple()[0:-3])
self.cur_min[inst]['date'] = mdf['date'].iloc[-1]
self.cur_min[inst]['open'] = float(mdf['open'].iloc[-1])
self.cur_min[inst]['close'] = float(mdf['close'].iloc[-1])
self.cur_min[inst]['high'] = float(mdf['high'].iloc[-1])
self.cur_min[inst]['low'] = float(mdf['low'].iloc[-1])
self.cur_min[inst]['volume'] = self.cur_day[inst]['volume']
self.cur_min[inst]['openInterest'] = self.cur_day[inst][
'openInterest']
self.cur_min[inst]['min_id'] = int(mdf['min_id'].iloc[-1])
self.cur_min[inst]['bar_id'] = self.conv_bar_id(
self.cur_min[inst]['min_id'])
self.instruments[inst].price = self.instruments[
inst].mid_price = float(mdf['close'].iloc[-1])
self.instruments[inst].last_update = 0
#self.logger.debug('inst=%s tick data loaded for date=%s' % (inst, min_date))
if 1 not in self.min_data_func[inst]:
self.min_data[inst][1] = data_handler.DynamicRecArray(
dataframe=mdf)
for m in sorted(self.min_data_func[inst]):
if m != 1:
mdf_m = data_handler.conv_ohlc_freq(
mdf,
str(m) + 'min',
index_col=None,
bar_func=self.conv_bar_id,
extra_cols=['bar_id'])
else:
mdf_m = mdf
for fobj in self.min_data_func[inst][m]:
ts = fobj.sfunc(mdf_m)
if type(ts).__name__ == 'Series':
if ts.name in mdf_m.columns:
self.logger.warning(
'TimeSeries name %s is already in the columns for inst = %s'
% (ts.name, inst))
mdf_m[ts.name] = ts
elif type(ts).__name__ == 'DataFrame':
for col_name in ts.columns:
if col_name in mdf_m.columns:
self.logger.warning(
'TimeSeries name %s is already in the columns for inst = %s'
% (col_name, inst))
mdf_m[col_name] = ts[col_name]
self.min_data[inst][m] = data_handler.DynamicRecArray(
dataframe=mdf_m)
#print inst, self.min_data[inst][m].data['date'][-1] < self.cur_min[inst]['date']
self.db_conn.close()
def nearby(prodcode,
n=1,
start_date=None,
end_date=None,
roll_rule='-20b',
freq='d',
need_shift=False,
database=None):
contlist, exp_dates, _ = cont_expiry_list(prodcode, start_date, end_date,
roll_rule)
sdate = start_date
is_new = True
dbconf = copy.deepcopy(dbaccess.dbconfig)
if database:
dbconf['database'] = database
cnx = dbaccess.connect(**dbconf)
for idx, exp in enumerate(exp_dates):
if exp < start_date:
continue
elif sdate > end_date:
break
nb_cont = contlist[idx + n - 1]
if freq == 'd':
new_df = dbaccess.load_daily_data_to_df(cnx, 'fut_daily', nb_cont,
sdate, min(exp, end_date))
else:
minid_start = 1500
minid_end = 2114
if prodcode in night_session_markets:
minid_start = 300
new_df = dbaccess.load_min_data_to_df(cnx, 'fut_min', nb_cont,
sdate, min(exp, end_date),
minid_start, minid_end)
if len(new_df.shape) == 0:
continue
nn = new_df.shape[0]
if nn > 0:
new_df['contract'] = pd.Series([nb_cont] * nn, index=new_df.index)
else:
continue
if is_new:
df = new_df
is_new = False
else:
if need_shift > 0:
if isinstance(df.index[-1], datetime.datetime):
last_date = df.index[-1].date()
else:
last_date = df.index[-1]
tmp_df = dbaccess.load_daily_data_to_df(
cnx, 'fut_daily', nb_cont, last_date, last_date)
if need_shift == 1:
shift = tmp_df['close'][-1] - df['close'][-1]
for ticker in ['open', 'high', 'low', 'close']:
df[ticker] = df[ticker] + shift
else:
shift = float(tmp_df['close'][-1]) / float(df['close'][-1])
for ticker in ['open', 'high', 'low', 'close']:
df[ticker] = df[ticker] * shift
df = df.append(new_df)
sdate = exp + datetime.timedelta(days=1)
return df
