def copy_eod_market(tday, tag, src_db = dbaccess.dbconfig, dst_db = dbaccess.mktsnap_dbconfig):
for table_name in all_asset_list:
src_conn = dbaccess.connect(**src_db)
stmt = "select * from {table} where date like'{date}%'".format(table = table_name,date = tday.strftime('%Y-%m-%d'))
df = pd.read_sql(stmt, src_conn)
src_conn.close()
df['date'] = tag
dst_conn = dbaccess.connect(**dst_db)
df.to_sql(table_name, dst_conn, flavor='sqlite', if_exists='append', index = False)
dst_conn.close()
def main():
settings._filename = get_settings_filename()
dbaccess.connect()
interpreter = Interpreter()
print_init_message()
try:
interpreter.cmdloop('')
except KeyboardInterrupt:
print('')
sys.exit(0)
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 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_btest_res(sim_names, dbtable = 'bktest_output'):
cnx = dbaccess.connect(**dbaccess.bktest_dbconfig)
stmt = "select * from {dbtable} where sim_name in ('{qlist}')".format( \
dbtable=dbtable, qlist="','".join(sim_names))
df = pd.read_sql(stmt, cnx)
cnx.close()
return df
def breakeven_vol_by_spot(spotID, start_d, end_d, periods = 1, tenor = '-1m'):
data = {'is_dtime': False,
'data_column': 'close',
'data_freq': 'd',
'xs': [0.5],
'xs_names': ['atm'],
'xs_func': 'bs_delta_to_strike',
'rehedge_period': 1,
'term_tenor': tenor,
}
option_input = {'otype': True,
'ir': 0.0,
'end_vol': 0.0,
'ref_vol': 0.4,
'pricer_func': 'bsopt.BSFwd',
'delta_func': 'bsopt.BSFwdDelta',
'is_dtime': data['is_dtime'],
}
cnx = dbaccess.connect(**dbaccess.dbconfig)
p_str = '-' + str(int(tenor[1:-1]) * periods) + tenor[-1]
d_end = end_d
res = {}
df = dbaccess.load_daily_data_to_df(cnx, 'spot_daily', spotID, day_shift(start_d, p_str), end_d, index_col=None, field = 'spotID')
df['date'] = pd.to_datetime(df['date'])
while d_end > start_d:
d_start = day_shift(d_end, p_str)
xdf = df[(df['date'] <= d_end) & (df['date'] >= d_start)]
option_input['expiry'] = datetime.datetime.combine(d_end, datetime.time(0, 0))
data['dataframe'] = xdf
vol_df = realized_termstruct(option_input, data)
res[d_start] = vol_df['atm'].values
d_end = day_shift(d_end, tenor)
cnx.close()
df = pd.DataFrame.from_dict(res, orient = 'index').sort_index()
return df
def comfix_db_loader(market_data, spotID, dep_tenors = []):
cnx = dbaccess.connect(**dbaccess.dbconfig)
spot_id, tab_name = spotID.split('.')
df = dbaccess.load_daily_data_to_df(cnx, tab_name, spot_id, min(dep_tenors), max(dep_tenors), index_col = None, field='spotID')
if len(df) > 0 and isinstance(df['date'][0], basestring):
df['date'] = df['date'].apply(lambda x: datetime.datetime.strptime(x,"%Y-%m-%d %H:%M:%S").date())
return df[['date', 'close']].values.tolist()
def contract_expiry(cont, hols='db'):
if type(hols) == list:
exch = inst2exch(cont)
mth = int(cont[-2:])
if cont[-4:-2].isdigit():
yr = 2000 + int(cont[-4:-2])
else:
yr = 2010 + int(cont[-3:-2])
cont_date = datetime.date(yr, mth, 1)
expiry = cont_date_expiry(cont_date, exch)
else:
cnx = dbaccess.connect(**dbaccess.dbconfig)
cursor = cnx.cursor()
stmt = "select expiry from contract_list where instID='{inst}' ".format(
inst=cont)
cursor.execute(stmt)
out = [exp for exp in cursor]
if len(out) > 0:
expiry = out[0][0]
if isinstance(expiry, basestring):
expiry = datetime.datetime.strptime(expiry, "%Y-%m-%d").date()
else:
expiry = contract_expiry(cont, CHN_Holidays)
cnx.close()
return expiry
def load_btest_res(sim_names, dbtable='bktest_output'):
cnx = dbaccess.connect(**dbaccess.bktest_dbconfig)
stmt = "select * from {dbtable} where sim_name in ('{qlist}')".format( \
dbtable=dbtable, qlist="','".join(sim_names))
df = pd.read_sql(stmt, cnx)
cnx.close()
return df
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 __init__(self, allow_unsafe_cursors=True):
super(Database, self).__init__()
self.__connection = dbaccess.connect()
self.__commit_callbacks = []
self.__allow_unsafe_cursors = allow_unsafe_cursors
self.__updating_cursor = None
self.unsafe_queries = False
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 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 processLoop():
db = dbaccess.connect()
cursor = db.cursor()
while True:
cursor.execute("""SELECT DISTINCT roles.uid, batches.id
FROM extensionroles_processchanges AS roles
JOIN batches ON (batches.id > roles.skip)
JOIN reviewusers ON (reviewusers.review=batches.review AND reviewusers.uid=roles.uid)
LEFT OUTER JOIN extensionprocessedbatches AS processed ON (processed.batch=batches.id AND processed.role=roles.id)
WHERE processed.batch IS NULL""")
queue = cursor.fetchall()
if not queue:
# Nothing to do right now; sleep a little to avoid a tight loop of
# DB queries. (Not that the query will be expensive at all in the
# foreseeable future, but)
time.sleep(POLLING_INTERVAL)
else:
print repr(queue)
for user_id, batch_id in queue:
output = cStringIO.StringIO()
if extensions.executeProcessChanges(db, user_id, batch_id,
output):
pending_mails = review.mail.sendExtensionOutput(
db, user_id, batch_id, output.getvalue())
review.mail.sendPendingMails(pending_mails)
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 processLoop():
db = dbaccess.connect()
cursor = db.cursor()
while True:
cursor.execute("""SELECT DISTINCT roles.uid, batches.id
FROM extensionroles_processchanges AS roles
JOIN batches ON (batches.id > roles.skip)
JOIN reviewusers ON (reviewusers.review=batches.review AND reviewusers.uid=roles.uid)
LEFT OUTER JOIN extensionprocessedbatches AS processed ON (processed.batch=batches.id AND processed.role=roles.id)
WHERE processed.batch IS NULL""")
queue = cursor.fetchall()
if not queue:
# Nothing to do right now; sleep a little to avoid a tight loop of
# DB queries. (Not that the query will be expensive at all in the
# foreseeable future, but)
time.sleep(POLLING_INTERVAL)
else:
print repr(queue)
for user_id, batch_id in queue:
output = cStringIO.StringIO()
if extensions.executeProcessChanges(db, user_id, batch_id, output):
pending_mails = reviewing.mail.sendExtensionOutput(db, user_id, batch_id, output.getvalue())
reviewing.mail.sendPendingMails(pending_mails)
def __init__(self, allow_unsafe_cursors=True):
super(Database, self).__init__()
self.__connection = dbaccess.connect()
self.__commit_callbacks = []
self.__allow_unsafe_cursors = allow_unsafe_cursors
self.__updating_cursor = None
self.unsafe_queries = False
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_data(spotID,
start,
end,
spot_table='spot_daily',
name=None,
index_col='date',
fx_pair=None,
field='spotID',
args=None):
cnx = dbaccess.connect(**dbaccess.dbconfig)
if args:
args['start_date'] = start
args['end_date'] = end
df = misc.nearby(spotID, **args)
else:
df = dbaccess.load_daily_data_to_df(cnx,
spot_table,
spotID,
start,
end,
index_col=None,
field=field)
if isinstance(df[index_col][0], basestring):
if len(df[index_col][0]) > 12:
df[index_col] = df[index_col].apply(
lambda x: datetime.datetime.strptime(x, "%Y-%m-%d %H:%M:%S"
).date())
else:
df[index_col] = df[index_col].apply(
lambda x: datetime.datetime.strptime(x, "%Y-%m-%d").date())
df = df.set_index(index_col)
if name:
col_name = name
else:
col_name = spotID
if field == 'ccy':
df = df[df.tenor == '0W']
data_field = 'rate'
elif field == 'spotID':
data_field = 'close'
df = df[[data_field]]
df.rename(columns={data_field: col_name}, inplace=True)
if fx_pair:
fx = dbaccess.load_daily_data_to_df(cnx,
'fx_daily',
fx_pair,
start,
end,
index_col=None,
field='ccy')
fx = fx[fx['tenor'] == '0W']
if isinstance(fx[index_col][0], basestring):
fx[index_col] = fx[index_col].apply(
lambda x: datetime.datetime.strptime(x, "%Y-%m-%d %H:%M:%S"
).date())
fx = fx.set_index(index_col)
fx = fx.set_index(index_col)
df[col_name] = df[col_name] / fx['rate']
return df
def export_tick_data(tday, folder='', tick_id=300000):
all_insts, prods = dbaccess.load_alive_cont(tday)
cnx = dbaccess.connect(**dbaccess.dbconfig)
for inst in all_insts:
stmt = "select * from fut_tick where instID='{prod}' and date='{cdate}' and tick_id>='{tick}'".format(
prod=inst, cdate=tday.strftime('%Y-%m-%d'), tick=tick_id)
df = pd.io.sql.read_sql(stmt, cnx)
df.to_csv(folder + inst + '.csv', header=False, index=False)
def create_db( db_path = DB_PATH):
conn = dbaccess.connect(database = db_path)
cursor = conn.cursor()
cursor.execute(fut_daily)
cursor.execute(spot_daily)
cursor.execute(fx_daily)
conn.commit()
conn.close()
def get_book_from_db(book_name, status):
cnx = dbaccess.connect(**dbaccess.trade_dbconfig)
df = dbaccess.load_deal_data(cnx, dbtable = 'deals', book = book_name, deal_status = status)
deal_list = df.to_dict(orient = 'record')
book_data = {'book': book_name, 'owner': 'harvey', 'reporting_ccy': 'USD', \
'status': CMQBookStatus.Prod, 'deal_list': deal_list }
book_obj = CMQBook(book_data)
return book_obj
def load_csv_to_db(edate,
save_loc='C:\\dev\\data\\',
freq='m',
is_replace=False):
cnx = dbaccess.connect(**dbaccess.dbconfig)
cont_list = misc.filter_main_cont(edate, False)
if freq not in ['m', 'd']:
return False
for cont in cont_list:
if freq == 'm':
filename = save_loc + cont + '_min.csv'
else:
filename = save_loc + cont + '_daily.csv'
if not os.path.isfile(filename):
continue
data_reader = csv.reader(file(filename, 'rb'))
mindata_list = []
for idx, line in enumerate(data_reader):
if idx > 0:
if 'nan' in [line[0], line[2], line[3], line[4], line[6]]:
continue
min_data = {}
min_data['volume'] = int(float(line[0]))
if min_data['volume'] <= 0: continue
dtime = datetime.datetime.strptime(line[1],
'%Y-%m-%d %H:%M:%S.%f')
if freq == 'm':
min_data['datetime'] = dtime.replace(microsecond=0)
else:
min_data['date'] = dtime.date()
min_data['high'] = float(line[2])
min_data['low'] = float(line[3])
min_data['close'] = float(line[4])
if line[5] == 'nan':
oi = 0
else:
oi = int(float(line[5]))
min_data['openInterest'] = oi
min_data['open'] = float(line[6])
if freq == 'm':
min_data['min_id'] = get_min_id(dtime)
trading_date = dtime.date()
if min_data['min_id'] < 600:
trading_date = misc.day_shift(trading_date, '1b')
min_data['date'] = trading_date
mindata_list.append(min_data)
else:
print cont
dbaccess.insert_daily_data(cont,
min_data,
is_replace=is_replace,
dbtable='fut_daily')
if freq == 'm':
print cont
dbaccess.bulkinsert_min_data(cont,
mindata_list,
is_replace=is_replace)
return True
def load_hist_tick(db_table, instID, sdate, edate):
stmt = "select instID, dtime, date, hour, min, sec, msec, price, dvol, openInterest from {dbtable} where instID='{inst}' ".format(
dbtable=db_table, inst=instID)
stmt += "and date >= '%s' " % sdate.strftime('%Y-%m-%d')
stmt += "and date <= '%s' " % edate.strftime('%Y-%m-%d')
stmt += "order by dtime;"
cnx = dbaccess.connect(**dbaccess.hist_dbconfig)
df = pd.io.sql.read_sql(stmt, cnx, index_col='dtime')
return df
def inst_to_exch(inst):
key = inst2product(inst)
cnx = dbaccess.connect(**dbaccess.dbconfig)
cursor = cnx.cursor()
stmt = "select exchange from trade_products where product_code='{prod}' ".format(prod=key)
cursor.execute(stmt)
out = [exchange for exchange in cursor]
cnx.close()
return str(out[0][0])
def inst_to_exch(inst):
key = inst2product(inst)
cnx = dbaccess.connect(**dbaccess.dbconfig)
cursor = cnx.cursor()
stmt = "select exchange from trade_products where product_code='{prod}' ".format(prod=key)
cursor.execute(stmt)
out = [exchange for exchange in cursor]
cnx.close()
return str(out[0][0])
def load_hist_min(db_table, instID, sdate, edate):
stmt = "select instID, exch, datetime, date, min_id, open, high, low, close, volume, openInterest from {dbtable} where instID='{inst}' ".format(
dbtable=db_table, inst=instID)
stmt += "and date >= '%s' " % sdate.strftime('%Y-%m-%d')
stmt += "and date <= '%s' " % edate.strftime('%Y-%m-%d')
stmt += "order by date, min_id;"
cnx = dbaccess.connect(**dbaccess.hist_dbconfig)
df = pd.io.sql.read_sql(stmt, cnx, index_col='datetime')
return df
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 = copy.deepcopy(dbaccess.dbconfig)
dbconfig['database'] = database
cnx = dbaccess.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 = misc.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_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 comdv_db_loader(market_data, fwd_index, dep_tenors = [], spd_key = 'DV1'):
mkt_db = market_data['market_db']
mdate = market_data['market_date']
mkey = market_data['market_key']
cnx = dbaccess.connect(**mkt_db)
df = dbaccess.load_cmdv_curve(cnx, fwd_index, spd_key, mkey)
if len(df) > 0 and isinstance(df['date'][0], basestring):
df['date'] = df['date'].apply(lambda x: datetime.datetime.strptime(x,"%Y-%m-%d").date())
df['expiry'] = df['expiry'].apply(lambda x: datetime.datetime.strptime(x, "%Y-%m-%d").date())
return df[['date', 'expiry', 'vol']].values.tolist()
def comfwd_db_loader(market_data, fwd_index, dep_tenors=[]):
curve_info = cmq_crv_defn.COM_Curve_Map[fwd_index]
prod_code = curve_info['instID']
mdate = market_data['market_date']
cnx = dbaccess.connect(**dbaccess.dbconfig)
df = dbaccess.load_fut_curve(cnx, prod_code, mdate)
if len(df) == 0:
print "COMFwd data is not available for %s on %s" % (fwd_index, mdate)
df['date'] = df['instID'].apply(lambda x: misc.inst2cont(x))
df['expiry'] = df['instID'].apply(lambda x: misc.contract_expiry(x, []))
return df[['date', 'expiry', 'close']].values.tolist()
def run_all_assets(self):
for idx, asset in enumerate(self.sim_assets):
output = self.load_curr_results(idx)
if len(output.keys()) == len(self.scenarios):
continue
self.set_config(idx)
self.load_data(idx)
for ix, s in enumerate(self.scenarios):
if str(ix) in output:
continue
res = {'asset': '_'.join(asset), 'scen_id': ix,\
'sim_name': self.sim_name, 'sim_class': self.sim_class.__name__, 'sim_func': self.sim_func,
'end_date': str(self.config['end_date'])}
for i in range(5):
res['par_name' + str(i)] = ''
res['par_value' + str(i)] = 0
for i, (key, seq) in enumerate(zip(self.scen_keys, s)):
self.config[key] = self.scen_param[key][seq]
res['par_name' + str(i)] = key
res['par_value' + str(i)] = str(self.scen_param[key][seq])
self.prepare_data(idx, cont_idx=0)
sim_strat = self.sim_class(self.config)
sim_dfs, closed_trades = getattr(sim_strat, self.sim_func)()
(res_pnl, ts) = get_pnl_stats(sim_dfs,
self.config['marginrate'],
self.sim_freq,
self.pnl_tenors,
cost_ratio=self.cost_ratio)
res_trade = get_trade_stats(closed_trades)
res.update(dict(res_pnl.items() + res_trade.items()))
file_prefix = self.file_prefix + '_' + '_'.join(
[self.sim_mode] + asset)
res['trade_file'] = file_prefix + '_' + str(ix) + '_trades.csv'
res['pnl_file'] = file_prefix + '_' + str(
ix) + '_dailydata.csv'
output[str(ix)] = res
all_trades = {}
for i, tradepos in enumerate(closed_trades):
all_trades[i] = trade_position.tradepos2dict(tradepos)
trades = pd.DataFrame.from_dict(all_trades).T
trades.to_csv(res['trade_file'])
ts.to_csv(res['pnl_file'])
fname = file_prefix + '_stats.json'
with open(fname, 'w') as ofile:
json.dump(output, ofile)
cnx = dbaccess.connect(**self.dbconfig)
#cnx.set_converter_class(mysql_helper.NumpyMySQLConverter)
dbaccess.insert_row_by_dict(cnx,
self.dbtable,
res,
is_replace=True)
cnx.close()
print 'The results for asset = %s, scen = %s are saved' % (
asset, str(ix))
def fxfwd_db_loader(market_data, fwd_index, dep_tenors = []):
curve_info = cmq_crv_defn.FX_Curve_Map[fwd_index]
mkey = market_data['market_key']
mdate = market_data['market_date']
mkt_db = market_data['market_db']
cnx = dbaccess.connect(**mkt_db)
df = dbaccess.load_fut_curve(cnx, fwd_index, mkey, dbtable = 'fx_daily', field = 'ccy')
if len(df) == 0:
print "FXFwd data is not available for %s on %s" % (fwd_index, mdate)
df['expiry'] = df['tenor'].apply(lambda x: misc.day_shift(mdate, x.lower()))
return df[['tenor', 'expiry', 'rate']].values.tolist()
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):
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)
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:
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)
return df
def volgrid_hist_slice(underlier, strike_list, curr_date, tick_id, accr = 'COMN1', ir = 0.03, ostyle = 'EU', \
iv_tol=1e-5, iv_steps = 100):
if ostyle == 'EU':
iv_func = qlib.BlackImpliedVol
else:
iv_func = qlib.AmericanImpliedVol
cnx = dbaccess.connect(**dbaccess.dbconfig)
df = dbaccess.load_tick_to_df(cnx,
'fut_tick',
underlier,
curr_date,
curr_date,
start_tick=300000,
end_tick=tick_id)
slice_tick = df['tick_id'].iat[-1]
under_bid = df['bidPrice1'].iat[-1]
under_ask = df['askPrice1'].iat[-1]
under_mid = (under_bid + under_ask) / 2.0
opt_expiry = get_opt_expiry(underlier, inst2contmth(underlier),
inst2exch(underlier))
nBusDays = qlib.NumBusDays(date2xl(curr_date), date2xl(opt_expiry.date()),
qlib.CHN_Holidays)
day_frac = qlib.GetDayFraction(min2time(tick_id / 1000), accr)
time2exp = (nBusDays - day_frac) / BDAYS_PER_YEAR
res = {}
for strike in strike_list:
res[strike] = {}
for otype in ['C', 'P']:
opt_inst = get_opt_name(underlier, otype, strike)
xdf = dbaccess.load_tick_to_df(cnx,
'fut_tick',
opt_inst,
curr_date,
curr_date,
start_tick=300000,
end_tick=slice_tick)
if len(xdf) == 0:
print opt_inst
else:
for lbl, tag in zip(['bidPrice1', 'askPrice1'],
['bvol', 'avol']):
quote_p = xdf[lbl].iat[-1]
iv_args = (quote_p, under_mid, strike, ir, time2exp, otype,
iv_tol)
if ostyle == 'AM':
iv_args = tuple(list(iv_args) + [iv_steps])
ivol = iv_func(*iv_args)
key = otype + '-' + tag
res[strike][key] = ivol
cnx.close()
return pd.DataFrame.from_dict(
res, orient='index')[['C-bvol', 'C-avol', 'P-bvol', 'P-avol']]
def import_tick_data(tday, folder=''):
all_insts, prods = dbaccess.load_alive_cont(tday)
cnx = dbaccess.connect(**dbaccess.dbconfig)
cursor = cnx.cursor()
for inst in all_insts:
data_file = folder + inst + '.csv'
if os.path.isfile(data_file):
stmt = "load data local infile '{data_file}' replace into table fut_tick fields terminated by ',';".format(
data_file=data_file)
cursor.execute(stmt)
cnx.commit()
print inst
cnx.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
def ircurve_db_loader(market_data, fwd_index, dep_tenors = []):
curve_info = cmq_crv_defn.IR_Curve_Map[fwd_index]
ir_idx = curve_info['ir_index']
mdate = market_data['market_date']
mkey = market_data['market_key']
mkt_db = market_data['market_db']
cnx = dbaccess.connect(**mkt_db)
df = dbaccess.load_fut_curve(cnx, ir_idx, mkey, dbtable='ir_daily', field='ir_index')
if len(df) == 0:
print "IRCurve data is not available for %s on %s" % (ir_idx, mkey)
df = df[~df['tenor'].isin(IR_Exclusion_Tenors)]
df['expiry'] = df['tenor'].apply(lambda x: misc.day_shift(mdate, x.lower()))
df['rate'] = df['rate']/100.0
return df[['tenor', 'expiry', 'rate']].values.tolist()
def write_mkt_data(self, event):
inst = event.dict['instID']
type = event.dict['type']
data = event.dict['data']
self.db_conn = dbaccess.connect(**dbaccess.dbconfig)
if type == EVENT_MIN_BAR:
dbaccess.insert_min_data(self.db_conn, inst, data, dbtable = self.min_db_table)
elif type == EVENT_TICK:
dbaccess.bulkinsert_tick_data(self.db_conn, inst, data, dbtable = self.tick_db_table)
elif type == EVENT_MKTDATA_EOD:
dbaccess.insert_daily_data(self.db_conn, inst, data, dbtable = self.daily_db_table)
else:
pass
self.db_conn.close()
def comfwd_db_loader(market_data, fwd_index, dep_tenors = []):
curve_info = cmq_crv_defn.COM_Curve_Map[fwd_index]
prod_code = curve_info['instID']
mkt_db = market_data['market_db']
mdate = market_data['market_date']
mkey = market_data['market_key']
cnx = dbaccess.connect(**mkt_db)
df = dbaccess.load_fut_curve(cnx, prod_code, mkey)
if len(df) == 0:
print "COMFwd data is not available for %s on %s" % (fwd_index, mkey)
df['date'] = df['instID'].apply(lambda x: misc.inst2cont(x))
df['expiry'] = df['instID'].apply(lambda x: misc.contract_expiry(x, []))
df = df[pd.to_datetime(df.date).dt.month.isin(curve_info['active_mths'])]
return df[['date', 'expiry', 'close']].values.tolist()
def write_mkt_data(self, event):
inst = event.dict['instID']
type = event.dict['type']
data = event.dict['data']
self.db_conn = dbaccess.connect(**dbaccess.dbconfig)
if type == EVENT_MIN_BAR:
dbaccess.insert_min_data(self.db_conn, inst, data, dbtable = self.min_db_table)
elif type == EVENT_TICK:
dbaccess.bulkinsert_tick_data(self.db_conn, inst, data, dbtable = self.tick_db_table)
elif type == EVENT_MKTDATA_EOD:
dbaccess.insert_daily_data(self.db_conn, inst, data, dbtable = self.daily_db_table)
else:
pass
self.db_conn.close()
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 load_btest_pnl(sim_keys, dbtable = 'bktest_output'):
cnx = dbaccess.connect(**dbaccess.bktest_dbconfig)
df_list = []
for sim_key in sim_keys:
stmt = "select * from {dbtable} where sim_name = '{name}' and asset = '{asset}' and scen_id = {scen}".format( \
dbtable=dbtable, name = sim_key[1], asset = sim_key[0], scen = int(sim_key[2]))
tdf = pd.read_sql(stmt, cnx)
pnl_file = tdf['pnl_file'][0]
xdf = pd.read_csv(pnl_file)
xdf['date'] = xdf['date'].apply(lambda x: datetime.datetime.strptime(x, "%Y-%m-%d").date())
xdf = xdf[['date', 'daily_pnl']].set_index('date')
xdf.rename(columns = {'daily_pnl': '-'.join([ str(k) for k in sim_key])}, inplace = True)
df_list.append(xdf)
df = ts_tool.merge_df(df_list)
return df
def comvol_db_loader(market_data, fwd_index, dep_tenors = []):
curve_info = cmq_crv_defn.COM_Curve_Map[fwd_index]
prod_code = curve_info['instID']
mkt_db = market_data['market_db']
mkey = market_data['market_key']
cnx = dbaccess.connect(**mkt_db)
vol_tbl = dbaccess.load_cmvol_curve(cnx, prod_code, mkey)
if len(vol_tbl) == 0:
print "COMVol data is not available for %s on %s" % (prod_code, mkey)
return {}
vol_tbl = vol_tbl.reset_index()
vol_dict = {}
for field in ['COMVolATM', 'COMVolV10', 'COMVolV25', 'COMVolV75', 'COMVolV90']:
vol_dict[field] = [[x, y, z] for x, y, z \
in zip(vol_tbl['tenor_label'], vol_tbl['expiry_date'], vol_tbl[field])]
return vol_dict
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 get_data(spotID, start, end, spot_table = 'spot_daily', name = None, index_col = 'date', fx_pair = None, field = 'spotID', args = None):
cnx = dbaccess.connect(**dbaccess.dbconfig)
if args:
args['start_date'] = start
args['end_date'] = end
df = misc.nearby(spotID, **args)
df = df.reset_index()
else:
df = dbaccess.load_daily_data_to_df(cnx, spot_table, spotID, start, end, index_col = None, field = field)
if isinstance(df[index_col][0], basestring):
if len(df[index_col][0])> 12:
df[index_col] = df[index_col].apply(lambda x: datetime.datetime.strptime(x, "%Y-%m-%d %H:%M:%S").date())
else:
df[index_col] = df[index_col].apply(lambda x: datetime.datetime.strptime(x, "%Y-%m-%d").date())
df = df.set_index(index_col)
if name:
col_name = name
else:
col_name = spotID
if field == 'ccy':
df = df[df.tenor=='0W']
data_field = 'rate'
elif field == 'spotID':
data_field = 'close'
elif field == 'instID':
data_field = 'close'
df = df[[data_field]]
df.rename(columns = {data_field: col_name}, inplace = True)
if fx_pair:
fx = fx_pair.split('/')
direction = misc.get_mkt_fxpair(fx[0], fx[1])
if direction < 0:
mkt_pair = '/'.join([fx[1],fx[0]])
else:
mkt_pair = fx_pair
fx = dbaccess.load_daily_data_to_df(cnx, 'fx_daily', mkt_pair, start, end, index_col = None, field = 'ccy')
fx = fx[fx['tenor']=='0W']
if isinstance(fx[index_col][0], basestring):
fx[index_col] = fx[index_col].apply(lambda x: datetime.datetime.strptime(x, "%Y-%m-%d %H:%M:%S").date())
fx = fx.set_index(index_col)
if direction >= 0:
df[col_name] = df[col_name]/fx['rate']
else:
df[col_name] = df[col_name]*fx['rate']
return df
def run_all_assets(self):
for idx, asset in enumerate(self.sim_assets):
output = self.load_curr_results(idx)
if len(output.keys()) == len(self.scenarios):
continue
self.set_config(idx)
self.load_data(idx)
for ix, s in enumerate(self.scenarios):
if str(ix) in output:
continue
res = {'asset': '_'.join(asset), 'scen_id': ix,\
'sim_name': self.sim_name, 'sim_class': self.sim_class.__name__, 'sim_func': self.sim_func,
'end_date': str(self.config['end_date'])}
for i in range(5):
res['par_name' + str(i)] = ''
res['par_value' + str(i)] = 0
for i, (key, seq) in enumerate(zip(self.scen_keys, s)):
self.config[key] = self.scen_param[key][seq]
res['par_name' + str(i)] = key
res['par_value' + str(i)] = str(self.scen_param[key][seq])
self.prepare_data(idx, cont_idx = 0)
sim_strat = self.sim_class(self.config)
sim_dfs, closed_trades = getattr(sim_strat, self.sim_func)()
(res_pnl, ts) = get_pnl_stats( sim_dfs, self.config['marginrate'], self.sim_freq, self.pnl_tenors, cost_ratio = self.cost_ratio)
res_trade = get_trade_stats(closed_trades)
res.update(dict( res_pnl.items() + res_trade.items()))
file_prefix = self.file_prefix + '_' + '_'.join([self.sim_mode] + asset)
res['trade_file'] = file_prefix + '_'+ str(ix) + '_trades.csv'
res['pnl_file'] = file_prefix + '_'+ str(ix) + '_dailydata.csv'
output[str(ix)] = res
all_trades = {}
for i, tradepos in enumerate(closed_trades):
all_trades[i] = trade_position.tradepos2dict(tradepos)
trades = pd.DataFrame.from_dict(all_trades).T
trades.to_csv(res['trade_file'])
ts.to_csv(res['pnl_file'])
fname = file_prefix + '_stats.json'
with open(fname, 'w') as ofile:
json.dump(output, ofile)
cnx = dbaccess.connect(**self.dbconfig)
#cnx.set_converter_class(mysql_helper.NumpyMySQLConverter)
dbaccess.insert_row_by_dict(cnx, self.dbtable, res, is_replace=True)
cnx.close()
print 'The results for asset = %s, scen = %s are saved' % (asset, str(ix))
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 volgrid_hist_slice(underlier, strike_list, curr_date, tick_id, accr = 'COMN1', ir = 0.03, ostyle = 'EU', \
iv_tol=1e-5, iv_steps = 100):
if ostyle == 'EU':
iv_func = qlib.BlackImpliedVol
else:
iv_func = qlib.AmericanImpliedVol
cnx = dbaccess.connect(**dbaccess.dbconfig)
df = dbaccess.load_tick_to_df(cnx, 'fut_tick', underlier, curr_date, curr_date, start_tick=300000, end_tick = tick_id)
slice_tick = df['tick_id'].iat[-1]
under_bid = df['bidPrice1'].iat[-1]
under_ask = df['askPrice1'].iat[-1]
under_mid = (under_bid + under_ask)/2.0
opt_expiry = get_opt_expiry(underlier, inst2contmth(underlier), inst2exch(underlier))
nBusDays = qlib.NumBusDays(date2xl(curr_date), date2xl(opt_expiry.date()), qlib.CHN_Holidays)
day_frac = qlib.GetDayFraction(min2time(tick_id / 1000), accr)
time2exp = (nBusDays - day_frac)/BDAYS_PER_YEAR
res = {}
for strike in strike_list:
res[strike] = {}
for otype in ['C', 'P']:
opt_inst = get_opt_name(underlier, otype, strike)
xdf = dbaccess.load_tick_to_df(cnx, 'fut_tick', opt_inst, curr_date, curr_date, start_tick=300000,
end_tick=slice_tick)
if len(xdf) == 0:
print opt_inst
else:
for lbl, tag in zip(['bidPrice1', 'askPrice1'], ['bvol', 'avol']):
quote_p = xdf[lbl].iat[-1]
iv_args = (quote_p, under_mid, strike, ir, time2exp, otype, iv_tol)
if ostyle == 'AM':
iv_args = tuple(list(iv_args) + [iv_steps])
ivol = iv_func(*iv_args)
key = otype + '-' + tag
res[strike][key] = ivol
cnx.close()
return pd.DataFrame.from_dict(res, orient = 'index')[['C-bvol','C-avol','P-bvol','P-avol']]
def contract_expiry(cont, hols='db'):
if type(hols) == list:
exch = inst2exch(cont)
mth = int(cont[-2:])
if cont[-4:-2].isdigit():
yr = 2000 + int(cont[-4:-2])
else:
yr = 2010 + int(cont[-3:-2])
cont_date = datetime.date(yr, mth, 1)
expiry = cont_date_expiry(cont_date, exch)
else:
cnx = dbaccess.connect(**dbaccess.dbconfig)
cursor = cnx.cursor()
stmt = "select expiry from contract_list where instID='{inst}' ".format(inst=cont)
cursor.execute(stmt)
out = [exp for exp in cursor]
if len(out) > 0:
expiry = out[0][0]
if isinstance(expiry, basestring):
expiry = datetime.datetime.strptime(expiry, "%Y-%m-%d").date()
else:
expiry = contract_expiry(cont, CHN_Holidays)
cnx.close()
return expiry
# execute after the service manager restart. Because of this the
# following 3 line workaround was necessary:
if os.path.exists(configuration.paths.RUN_DIR):
os.chown(configuration.paths.RUN_DIR, installation.system.uid, installation.system.gid)
if not installation.initd.start():
abort()
if not installation.apache.start():
abort()
data["sha1"] = new_critic_sha1
with installation.utils.as_critic_system_user():
import dbaccess
db = dbaccess.connect()
cursor = db.cursor()
cursor.execute("UPDATE systemidentities SET installed_sha1=%s, installed_at=NOW() WHERE name=%s", (new_critic_sha1, arguments.identity))
if not arguments.dry_run:
db.commit()
for module in installation.modules:
try:
if hasattr(module, "finish"):
module.finish("upgrade", arguments, data)
except:
print >>sys.stderr, "WARNING: %s.finish() failed" % module.__name__
traceback.print_exc()
installation.utils.write_install_data(arguments, data)
installation.utils.clean_root_pyc_files()
def load_db_to_xlmkt(tag, xlfile = MKT_LATEST_XLFILE):
if os.path.isfile(xlfile):
book = openpyxl.load_workbook(xlfile)
writer = pd.ExcelWriter(xlfile, engine='openpyxl')
writer.book = book
writer.sheets = dict((ws.title, ws) for ws in book.worksheets)
else:
writer = pd.ExcelWriter(xlfile)
cnx = dbaccess.connect(**dbaccess.mktsnap_dbconfig)
req_data = {'i':None, 'fef':None, 'USD/CNY':None}
prod_map = dict([(prod, cont) for (prod, exch, cont) in live_asset_list])
xl_structure = {"fut_daily": "COMM", "fx_daily": "FX", "ir_daily": "IR"}
for tab_key in ['fut_daily', 'fx_daily', 'ir_daily']:
for idx, prod_code in enumerate(all_asset_list[tab_key]):
if tab_key == 'fut_daily':
df = dbaccess.load_fut_curve(cnx, prod_code, tag)
df['product_code'] = prod_code
df['tenor_label'] = df['instID'].apply(lambda x: misc.inst2cont(x))
df['expiry_date'] = df['instID'].apply(lambda x: misc.contract_expiry(x, []))
df['exch'] = misc.prod2exch(prod_code)
df.rename(columns = {'close': 'COMFwd'}, inplace = True)
if prod_code in prod_map:
df = df[pd.to_datetime(df['tenor_label']).dt.month.isin(prod_map[prod_code])]
if prod_code in option_markets:
vol_tbl = dbaccess.load_cmvol_curve(cnx, prod_code, tag)
vol_tbl = vol_tbl.set_index('tenor_label')
vol_tbl.drop(['expiry_date'], axis = 1,inplace = True)
df = df.set_index('tenor_label')
df = pd.concat([df, vol_tbl], axis = 1)
df = df.reset_index()
df.rename(columns = {'index': 'tenor_label'}, inplace = True)
else:
for key in ['COMVolATM', 'COMVolV90', 'COMVolV75', 'COMVolV25', 'COMVolV10']:
if key == 'COMVolATM':
df[key] = 0.2
else:
df[key] = 0.0
df = df[['product_code', 'instID', 'exch', 'tenor_label', 'expiry_date', 'COMFwd', \
'COMVolATM', 'COMVolV90', 'COMVolV75', 'COMVolV25', 'COMVolV10']].fillna(method = 'ffill')
df['CalSpread'] = (df['COMFwd'] - df['COMFwd'].shift(-1)).fillna(method = 'ffill')
multi = 13
elif tab_key == 'fx_daily':
df = dbaccess.load_fut_curve(cnx, prod_code, tag, dbtable='fx_daily', field='ccy')
df = df[df['rate']>0]
df['fwd_points'] = df['rate'] - df['rate'][0]
df = df[['ccy', 'tenor', 'rate', 'fwd_points']]
multi = 5
elif tab_key == 'ir_daily':
df = dbaccess.load_fut_curve(cnx, prod_code, tag, dbtable='ir_daily', field='ir_index')
df = df[['ir_index', 'tenor', 'rate']]
multi = 4
df.to_excel(writer, xl_structure[tab_key], index = False, startcol = idx * multi, startrow = 1)
if prod_code in req_data:
req_data[prod_code] = df
#do the SGX-DCE spread calc
#try:
tday = datetime.date.today()
if tday >= req_data['i']['tenor_label'][0]:
dce_prompt = req_data['i']['instID'][1]
else:
dce_prompt = req_data['i']['instID'][0]
sgx_prompt = 'fef' + dce_prompt[-4:]
sgx_price = float(req_data['fef'].loc[req_data['fef']['instID'] == sgx_prompt, 'COMFwd'])
dce_price = float(req_data['i'].loc[req_data['i']['instID'] == dce_prompt, 'COMFwd'])
fx = float(req_data['USD/CNY']['rate'][0])
sgx_dce_spd = sgx_price - (dce_price - 30.0/0.92)/1.16/fx
wb = writer.book
wb['COMM']['F1'] = sgx_dce_spd
#except:
# print "failed to update SGX-DCE spread"
writer.save()
def __init__(self):
super(Database, self).__init__()
self.__connection = dbaccess.connect()
def save_xlmkt_to_db(tag, xlfile = MKT_LATEST_XLFILE, snap_live = False, input_data = {}):
comm_curves = 9
fx_curves = 1
ir_curves = 1
if snap_live:
cont_dict, quotes = snap_curr_market()
if 'SGX_DCE_spd' in input_data:
quotes['SGX_DCE_spd'] = input_data['SGX_DCE_spd']
else:
try:
wbook = openpyxl.load_workbook(xlfile, data_only=True, read_only=True)
quotes['SGX_DCE_spd'] = float(wbook['COMM']['F1'].value)
except:
pass
if 'FX' in input_data:
quotes['FX'] = input_data['FX']
else:
cont_dict = {}
quotes = {}
conn = dbaccess.connect(**dbaccess.mktsnap_dbconfig)
fx_df = pd.DataFrame()
for idx in range(fx_curves):
df = pd.read_excel(xlfile, sheetname="FX", header=1, index_col=None, parse_cols=range(idx * 5, idx * 5 + 4)).dropna()
if snap_live and ('FX' not in quotes):
quotes['FX'] = float(df['rate'][0])
df['date'] = tag
df['src'] = xlfile
cdf = df[['date', 'ccy', 'tenor', 'rate', 'src']]
fx_df = fx_df.append(cdf)
ir_df = pd.DataFrame()
for idx in range(ir_curves):
df = pd.read_excel(xlfile, sheetname="IR", header=1, index_col=None, parse_cols=range(idx * 4, idx * 4 + 3)).dropna()
df['date'] = tag
df['src'] = xlfile
rdf = df[['date', 'tenor', 'rate', 'ir_index', 'src']]
ir_df = ir_df.append(rdf)
# loading commod curves
opt_df = pd.DataFrame()
fut_df = pd.DataFrame()
for idx in range(comm_curves):
df = pd.read_excel(xlfile, sheetname="COMM", header = 1, index_col = None, parse_cols = range(idx * 13, idx * 13 + 12)).dropna()
if snap_live:
if df['product_code'][0] == 'fef':
tday = datetime.date.today()
tenor = str((tday.year - 2000) + tday.month)
if cont_dict['i'][0][-4:] == tenor:
dce_instID = cont_dict['i'][1]
else:
dce_instID = cont_dict['i'][0]
dce_quote = (quotes[dce_instID]['bidPrice1'] + quotes[dce_instID]['askPrice1'])/2.0
fef_quote = quotes['SGX_DCE_spd'] + (dce_quote - 30/0.92)/1.16/quotes['FX']
fef_tenor = 'fef'+ dce_instID[-4:]
diff = fef_quote - float(df.loc[df['instID'] == fef_tenor, 'COMFwd'])
df.loc[1:, 'COMFwd'] = (df.loc[1:, 'COMFwd'] + diff).round(2)
elif df['product_code'][0] in cont_dict:
for instID in cont_dict[df['product_code'][0]]:
df.loc[df['instID'] == instID, 'COMFwd'] = (quotes[instID]['askPrice1'] + quotes[instID]['bidPrice1'])/2.0
df['date'] = tag
fdf = df[['instID', 'exch', 'date', 'COMFwd']]
fdf.rename(columns={'COMFwd': 'close'}, inplace = True)
fut_df = fut_df.append(fdf)
if df['product_code'][0] in option_markets:
for delta, col_name in zip([0.5, 0.1, 0.25, -0.25, -0.1], ['COMVolATM', 'COMVolV10', 'COMVolV25', 'COMVolV75', 'COMVolV90']):
xdf = df[['date', 'tenor_label', col_name, 'expiry_date', 'exch', 'product_code']]
xdf['tenor_label'] = xdf['tenor_label'].apply(lambda x: x.date())
xdf['expiry_date'] = xdf['expiry_date'].apply(lambda x: x.date())
xdf.rename(columns={col_name: 'vol'}, inplace = True)
if (delta != 0.5):
xdf.loc[:, 'vol'] = xdf.loc[:, 'vol'] + df.loc[:, 'COMVolATM']
xdf.loc[:, 'delta'] = delta
opt_df = opt_df.append(xdf)
fut_df.to_sql('fut_daily', conn, flavor='sqlite', if_exists='append', index=False)
opt_df.to_sql('cmvol_daily', conn, flavor='sqlite', if_exists='append', index=False)
fx_df.to_sql('fx_daily', conn, flavor='sqlite', if_exists='append', index=False)
ir_df.to_sql('ir_daily', conn, flavor='sqlite', if_exists='append', index=False)
return quotes
