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 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 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 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 rolling_swap(prod_code,
start_date,
end_date,
start_mth=3,
end_mth=15,
calebdar='PLIO'):
cnx = dbaccess.connect(**dbaccess.dbconfig)
num_days = (end_date - start_date).days + 1
tenors = []
roll_swap = []
for d in range(num_days):
ref_date = start_date + datetime.timedelta(days=d)
if misc.is_workday(ref_date, 'PLIO'):
df = dbaccess.load_fut_curve(cnx,
prod_code,
ref_date,
dbtable='fut_daily',
field='instID')
if len(df) > 0:
tenors.append(ref_date)
roll_swap.append(df['close'].values[start_mth:end_mth].mean())
return pd.DataFrame(roll_swap, index=tenors)
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()