def load_tagged_tt_trades(**kwargs):
assign_output = get_tagged_tt_fills(**kwargs)
aggregate_trades = assign_output['aggregate_trades']
con = msu.get_my_sql_connection(**kwargs)
final_alias_dictionary = {}
unique_alias_list = aggregate_trades['alias'].unique()
open_strategy_frame = ts.get_open_strategies()
for i in range(len(unique_alias_list)):
if ~open_strategy_frame['alias'].str.contains(unique_alias_list[i]).any():
print('Need to create ' + unique_alias_list[i])
if '_ocs' in unique_alias_list[i]:
gen_output = ts.generate_db_strategy_from_alias_and_class(alias=unique_alias_list[i],
strategy_class='ocs')
final_alias_dictionary[unique_alias_list[i]] = gen_output['alias']
else:
final_alias_dictionary[unique_alias_list[i]] = unique_alias_list[i]
aggregate_trades['alias_final'] = [final_alias_dictionary[x] for x in aggregate_trades['alias']]
aggregate_trades['alias'] = aggregate_trades['alias_final']
ts.load_trades_2strategy(trade_frame=aggregate_trades,con=con,**kwargs)
if 'con' not in kwargs.keys():
con.close()
def get_expiration_report(**kwargs):
con = msu.get_my_sql_connection(**kwargs)
portfolio_frame = get_portfolio_expirations(**kwargs)
strategy_frame = ts.get_open_strategies(con=con,
as_of_date=kwargs['report_date'])
expiration_list = [
get_strategy_expiration(con=con,
alias=strategy_frame['alias'].iloc[x],
as_of_date=kwargs['report_date'])
for x in range(len(strategy_frame.index))
]
expiration_list.append(portfolio_frame)
if 'con' not in kwargs.keys():
con.close()
expiration_list = [x for x in expiration_list if not x.empty]
expiration_frame = pd.concat(expiration_list, sort=True)
return expiration_frame.sort_values('tr_days_2roll',
ascending=True,
inplace=False)
def get_daily_pnl_snapshot(**kwargs):
if "as_of_date" not in kwargs.keys():
as_of_date = exp.doubledate_shift_bus_days()
kwargs["as_of_date"] = as_of_date
else:
as_of_date = kwargs["as_of_date"]
ta_output_dir = dn.get_dated_directory_extension(folder_date=as_of_date, ext="ta")
if os.path.isfile(ta_output_dir + "/portfolio_pnl.pkl"):
strategy_frame = pd.read_pickle(ta_output_dir + "/portfolio_pnl.pkl")
return strategy_frame
strategy_frame = ts.get_open_strategies(**kwargs)
pnl_output = [tapnl.get_strategy_pnl(alias=x, **kwargs) for x in strategy_frame["alias"]]
strategy_frame["daily_pnl"] = [x["daily_pnl"] for x in pnl_output]
strategy_frame["total_pnl"] = [x["total_pnl"] for x in pnl_output]
strategy_frame = strategy_frame[["alias", "daily_pnl", "total_pnl"]]
strategy_frame.sort("daily_pnl", ascending=False, inplace=True)
strategy_frame.loc[max(strategy_frame.index) + 1] = [
"TOTAL",
strategy_frame["daily_pnl"].sum(),
strategy_frame["total_pnl"].sum(),
]
strategy_frame.to_pickle(ta_output_dir + "/portfolio_pnl.pkl")
return strategy_frame
def get_delta_strategy_alias(**kwargs):
# inputs:
# as_of_date
strategy_frame = tas.get_open_strategies(**kwargs)
strategy_frame.sort_values('open_date', ascending=True, inplace=True)
strategy_class_list = [
sc.convert_from_string_to_dictionary(
string_input=strategy_frame['description_string'].iloc[x])
['strategy_class'] for x in range(len(strategy_frame.index))
]
if ('delta' not in strategy_class_list):
datetime_now = dt.datetime.now()
delta_strategy_alias = 'delta_' + datetime_now.strftime('%b%y')
generate_strategy_output = tas.generate_db_strategy_from_alias(
alias=delta_strategy_alias,
description_string='strategy_class=delta')
delta_alias_final = generate_strategy_output['alias']
else:
strategy_frame['class'] = strategy_class_list
delta_frame = strategy_frame[strategy_frame['class'] == 'delta']
delta_alias_final = delta_frame['alias'].iloc[-1]
return delta_alias_final
def generate_spread_carry_followup_report(**kwargs):
if 'as_of_date' in kwargs.keys():
as_of_date = kwargs['as_of_date']
else:
as_of_date = exp.doubledate_shift_bus_days()
kwargs['as_of_date'] = as_of_date
con = msu.get_my_sql_connection(**kwargs)
ta_output_dir = dn.get_dated_directory_extension(folder_date=as_of_date,
ext='ta')
if 'writer' in kwargs.keys():
writer = kwargs['writer']
else:
writer = pd.ExcelWriter(ta_output_dir + '/followup.xlsx',
engine='xlsxwriter')
strategy_frame = ts.get_open_strategies(**kwargs)
strategy_class_list = [
sc.convert_from_string_to_dictionary(
string_input=strategy_frame['description_string'][x])
['strategy_class'] for x in range(len(strategy_frame.index))
]
spread_carry_indx = [x == 'spread_carry' for x in strategy_class_list]
spread_carry_frame = strategy_frame[spread_carry_indx]
if spread_carry_frame.empty:
return writer
results = [
sf.get_results_4strategy(
alias=spread_carry_frame['alias'].iloc[x],
strategy_info_output=spread_carry_frame.iloc[x],
con=con) for x in range(len(spread_carry_frame.index))
]
results_frame_list = [
results[x]['results_frame'] for x in range(len(results))
if results[x]['success']
]
spread_carry_followup_frame = pd.concat(results_frame_list)
spread_carry_followup_frame.to_excel(writer, sheet_name='sc')
worksheet_sc = writer.sheets['sc']
worksheet_sc.freeze_panes(1, 0)
worksheet_sc.autofilter(0, 0, len(spread_carry_followup_frame.index),
len(spread_carry_followup_frame.columns))
if 'con' not in kwargs.keys():
con.close()
return writer
def main():
app = algo.Algo()
con = msu.get_my_sql_connection()
date_now = cu.get_doubledate()
datetime_now = dt.datetime.now()
report_date = exp.doubledate_shift_bus_days()
ticker_head_list = ['HO', 'LC', 'FC', 'NQ'] # avoid HO on wednesdays for now!!
data_list = [gfp.get_futures_price_preloaded(ticker_head=x, settle_date=report_date) for x in ticker_head_list]
ticker_frame = pd.concat(data_list)
ticker_frame.sort(['ticker_head','volume'], ascending=[True, False], inplace=True)
ticker_frame.drop_duplicates(subset=['ticker_head'], take_last=False, inplace=True)
ticker_list = list(ticker_frame['ticker'])
theme_name_list = set([x + '_long' for x in ticker_list]).union(set([x + '_short' for x in ticker_list]))
alias_portfolio = aup.portfolio(ticker_list=theme_name_list)
latest_trade_entry_datetime = dt.datetime(datetime_now.year, datetime_now.month, datetime_now.day, 12, 0, 0)
latest_livestock_exit_datetime = dt.datetime(datetime_now.year, datetime_now.month, datetime_now.day, 13, 55, 0)
latest_macro_exit_datetime = dt.datetime(datetime_now.year, datetime_now.month, datetime_now.day, 15, 55, 0)
daily_sd_dictionary = {}
for i in range(len(ticker_list)):
daily_data_frame = gfp.get_futures_price_preloaded(ticker=ticker_list[i], settle_date_to=report_date)
daily_data_frame['close_diff'] = daily_data_frame['close_price'].diff()
daily_sd_dictionary[ticker_list[i]] = np.std(daily_data_frame['close_diff'].iloc[-40:])
db_alias = 'itf_' + datetime_now.strftime('%b_%y')
strategy_frame = ts.get_open_strategies(as_of_date=int(datetime_now.strftime('%Y%m%d')), con=con)
if db_alias not in strategy_frame.values:
db_strategy_output = ts.generate_db_strategy_from_alias(alias=db_alias, description_string='strategy_class=itf',con=con)
db_alias = db_strategy_output['alias']
app.ticker_list = ticker_list
app.db_alias = db_alias
app.tick_size_dictionary = {x:cmi.tick_size[cmi.get_contract_specs(x)['ticker_head']] for x in ticker_list}
app.daily_sd_dictionary = daily_sd_dictionary
app.contract_multiplier_dictionary = {x:cmi.contract_multiplier[cmi.get_contract_specs(x)['ticker_head']] for x in ticker_list}
app.ticker_head_dictionary = {x:cmi.get_contract_specs(x)['ticker_head'] for x in ticker_list}
app.latest_trade_entry_datetime = latest_trade_entry_datetime
app.latest_trade_exit_datetime_dictionary = {'HO': latest_macro_exit_datetime, 'LC': latest_livestock_exit_datetime, 'FC': latest_livestock_exit_datetime, 'NQ': latest_macro_exit_datetime}
app.alias_portfolio = alias_portfolio
app.output_dir = ts.create_strategy_output_dir(strategy_class='itf', report_date=report_date)
app.log = lg.get_logger(file_identifier='ib_itf',log_level='INFO')
app.con = con
app.connect(client_id=4)
app.run()
def generate_spread_carry_followup_report(**kwargs):
if 'as_of_date' in kwargs.keys():
as_of_date = kwargs['as_of_date']
else:
as_of_date = exp.doubledate_shift_bus_days()
kwargs['as_of_date'] = as_of_date
con = msu.get_my_sql_connection(**kwargs)
ta_output_dir = dn.get_dated_directory_extension(folder_date=as_of_date, ext='ta')
if 'writer' in kwargs.keys():
writer = kwargs['writer']
else:
writer = pd.ExcelWriter(ta_output_dir + '/followup.xlsx', engine='xlsxwriter')
strategy_frame = ts.get_open_strategies(**kwargs)
strategy_class_list = [sc.convert_from_string_to_dictionary(string_input=strategy_frame['description_string'][x])['strategy_class']
for x in range(len(strategy_frame.index))]
spread_carry_indx = [x == 'spread_carry' for x in strategy_class_list]
spread_carry_frame = strategy_frame[spread_carry_indx]
results = [sf.get_results_4strategy(alias=spread_carry_frame['alias'].iloc[x],
strategy_info_output=spread_carry_frame.iloc[x],
con=con)
for x in range(len(spread_carry_frame.index))]
results_frame_list = [results[x]['results_frame'] for x in range(len(results)) if results[x]['success']]
spread_carry_followup_frame = pd.concat(results_frame_list)
spread_carry_followup_frame.to_excel(writer, sheet_name='sc')
worksheet_sc = writer.sheets['sc']
worksheet_sc.freeze_panes(1, 0)
worksheet_sc.autofilter(0, 0, len(spread_carry_followup_frame.index),
len(spread_carry_followup_frame.columns))
if 'con' not in kwargs.keys():
con.close()
return writer
def strategy_hedge_report(**kwargs):
current_date = cu.get_doubledate()
con = msu.get_my_sql_connection(**kwargs)
strategy_frame = tas.get_open_strategies(as_of_date=current_date,con=con)
strategy_class_list = [sc.convert_from_string_to_dictionary(string_input=strategy_frame['description_string'][x])['strategy_class']
for x in range(len(strategy_frame.index))]
hedge_indx = [x in ['vcs', 'scv','optionInventory'] for x in strategy_class_list]
hedge_frame = strategy_frame[hedge_indx]
#hedge_frame = hedge_frame[hedge_frame['alias'] != 'LNZ16V16VCS']
[hedge_strategy_against_delta(alias=x, con=con) for x in hedge_frame['alias']]
if 'con' not in kwargs.keys():
con.close()
def get_expiration_report(**kwargs):
con = msu.get_my_sql_connection(**kwargs)
portfolio_frame = get_portfolio_expirations(**kwargs)
strategy_frame = ts.get_open_strategies(con=con, as_of_date=kwargs["report_date"])
expiration_list = [
get_strategy_expiration(con=con, alias=strategy_frame["alias"].iloc[x], as_of_date=kwargs["report_date"])
for x in range(len(strategy_frame.index))
]
expiration_list.append(portfolio_frame)
if "con" not in kwargs.keys():
con.close()
expiration_list = [x for x in expiration_list if not x.empty]
expiration_frame = pd.concat(expiration_list)
return expiration_frame.sort("tr_dte", ascending=True, inplace=False)
def strategy_hedge_report(**kwargs):
current_date = cu.get_doubledate()
con = msu.get_my_sql_connection(**kwargs)
strategy_frame = tas.get_open_strategies(as_of_date=current_date, con=con)
strategy_class_list = [
sc.convert_from_string_to_dictionary(
string_input=strategy_frame['description_string'][x])
['strategy_class'] for x in range(len(strategy_frame.index))
]
hedge_indx = [
x in ['vcs', 'scv', 'optionInventory'] for x in strategy_class_list
]
hedge_frame = strategy_frame[hedge_indx]
#hedge_frame = hedge_frame[(hedge_frame['alias'] == 'BPX2018_short_scv')]
#hedge_frame = hedge_frame[(hedge_frame['alias'] != 'CLZ17H18VCS')]
if 'intraday_price_frame' in kwargs.keys():
[
hedge_strategy_against_delta(
alias=x,
intraday_price_frame=kwargs['intraday_price_frame'],
delta_alias=kwargs['delta_alias'],
con=con) for x in hedge_frame['alias']
]
else:
[
hedge_strategy_against_delta(alias=x,
delta_alias=kwargs['delta_alias'],
con=con) for x in hedge_frame['alias']
]
if 'con' not in kwargs.keys():
con.close()
def get_daily_pnl_snapshot(**kwargs):
if 'as_of_date' not in kwargs.keys():
as_of_date = exp.doubledate_shift_bus_days()
kwargs['as_of_date'] = as_of_date
else:
as_of_date = kwargs['as_of_date']
ta_output_dir = dn.get_dated_directory_extension(folder_date=as_of_date,
ext='ta')
file_name = '/portfolio_pnl_' + kwargs['name'] + '.pkl'
if os.path.isfile(ta_output_dir + file_name):
strategy_frame = pd.read_pickle(ta_output_dir + file_name)
return strategy_frame
strategy_frame = ts.get_open_strategies(**kwargs)
pnl_output = [
tapnl.get_strategy_pnl(alias=x, **kwargs)
for x in strategy_frame['alias']
]
strategy_frame['daily_pnl'] = [x['daily_pnl'] for x in pnl_output]
strategy_frame['total_pnl'] = [x['total_pnl'] for x in pnl_output]
strategy_frame = strategy_frame[['alias', 'daily_pnl', 'total_pnl']]
strategy_frame.sort_values('daily_pnl', ascending=False, inplace=True)
if len(strategy_frame.index) > 0:
strategy_frame.loc[max(strategy_frame.index) + 1] = [
'TOTAL', strategy_frame['daily_pnl'].sum(),
strategy_frame['total_pnl'].sum()
]
strategy_frame.to_pickle(ta_output_dir + file_name)
return strategy_frame
def generate_futures_butterfly_followup_report(**kwargs):
con = msu.get_my_sql_connection(**kwargs)
if 'as_of_date' in kwargs.keys():
as_of_date = kwargs['as_of_date']
else:
as_of_date = exp.doubledate_shift_bus_days()
kwargs['as_of_date'] = as_of_date
if 'writer' in kwargs.keys():
writer = kwargs['writer']
else:
ta_output_dir = dn.get_dated_directory_extension(folder_date=as_of_date, ext='ta')
writer = pd.ExcelWriter(ta_output_dir + '/followup.xlsx', engine='xlsxwriter')
strategy_frame = ts.get_open_strategies(**kwargs)
strategy_class_list = [sc.convert_from_string_to_dictionary(string_input=strategy_frame['description_string'][x])['strategy_class']
for x in range(len(strategy_frame.index))]
futures_butterfly_indx = [x == 'futures_butterfly' for x in strategy_class_list]
futures_butterfly_frame = strategy_frame[futures_butterfly_indx]
results = [sf.get_results_4strategy(alias=futures_butterfly_frame['alias'].iloc[x],
strategy_info_output=futures_butterfly_frame.iloc[x])
for x in range(len(futures_butterfly_frame.index))]
butterfly_followup_frame = pd.DataFrame(results)
butterfly_followup_frame['alias'] = futures_butterfly_frame['alias'].values
pnl_frame = pm.get_daily_pnl_snapshot(as_of_date=as_of_date, con=con)
risk_output = hr.get_historical_risk_4open_strategies(as_of_date=as_of_date, con=con)
merged_frame1 = pd.merge(butterfly_followup_frame,pnl_frame, how='left', on='alias')
merged_frame2 = pd.merge(merged_frame1, risk_output['strategy_risk_frame'], how='left', on='alias')
butterfly_followup_frame = merged_frame2[['alias', 'ticker_head', 'holding_tr_dte', 'short_tr_dte',
'z1_initial', 'z1', 'QF_initial', 'QF',
'total_pnl', 'downside','recommendation']]
butterfly_followup_frame.rename(columns={'alias': 'Alias', 'ticker_head': 'TickerHead',
'holding_tr_dte': 'HoldingTrDte', 'short_tr_dte': 'ShortTrDte',
'z1_initial': 'Z1Initial', 'z1': 'Z1',
'QF_initial': 'QFInitial','total_pnl': 'TotalPnl',
'downside': 'Downside','recommendation':'Recommendation'}, inplace=True)
butterfly_followup_frame.sort('QF', ascending=False,inplace=True)
butterfly_followup_frame['Z1'] = butterfly_followup_frame['Z1'].round(2)
butterfly_followup_frame.to_excel(writer, sheet_name='butterflies')
worksheet_butterflies = writer.sheets['butterflies']
worksheet_butterflies.set_column('B:B', 26)
worksheet_butterflies.freeze_panes(1, 0)
worksheet_butterflies.autofilter(0, 0, len(butterfly_followup_frame.index),
len(butterfly_followup_frame.columns))
if 'con' not in kwargs.keys():
con.close()
return writer
def generate_vcs_followup_report(**kwargs):
if 'as_of_date' in kwargs.keys():
as_of_date = kwargs['as_of_date']
else:
as_of_date = exp.doubledate_shift_bus_days()
kwargs['as_of_date'] = as_of_date
ta_output_dir = dn.get_dated_directory_extension(folder_date=as_of_date, ext='ta')
con = msu.get_my_sql_connection(**kwargs)
if 'writer' in kwargs.keys():
writer = kwargs['writer']
else:
writer = pd.ExcelWriter(ta_output_dir + '/followup.xlsx', engine='xlsxwriter')
strategy_frame = ts.get_open_strategies(**kwargs)
strategy_class_list = [sc.convert_from_string_to_dictionary(string_input=strategy_frame['description_string'][x])['strategy_class']
for x in range(len(strategy_frame.index))]
vcs_indx = [x == 'vcs' for x in strategy_class_list]
vcs_frame = strategy_frame[vcs_indx]
results = [sf.get_results_4strategy(alias=vcs_frame['alias'].iloc[x],
strategy_info_output=vcs_frame.iloc[x])
for x in range(len(vcs_frame.index))]
vcs_followup_frame = pd.DataFrame(results)
vcs_followup_frame['alias'] = vcs_frame['alias'].values
pnl_frame = pm.get_daily_pnl_snapshot(**kwargs)
merged_frame1 = pd.merge(vcs_followup_frame,pnl_frame, how='left', on='alias')
vcs_followup_frame = merged_frame1[['alias', 'last_adjustment_days_ago','min_tr_dte', 'long_short_ratio',
'net_oev', 'net_theta', 'long_oev', 'short_oev', 'favQMove', 'total_pnl','recommendation']]
vcs_followup_frame['long_short_ratio'] = vcs_followup_frame['long_short_ratio'].round()
vcs_followup_frame['net_oev'] = vcs_followup_frame['net_oev'].round(1)
vcs_followup_frame['long_oev'] = vcs_followup_frame['long_oev'].round(1)
vcs_followup_frame['short_oev'] = vcs_followup_frame['short_oev'].round(1)
vcs_followup_frame['net_theta'] = vcs_followup_frame['net_theta'].round(1)
vcs_followup_frame.sort('total_pnl', ascending=False, inplace=True)
vcs_followup_frame.reset_index(drop=True,inplace=True)
vcs_followup_frame.loc[len(vcs_followup_frame.index)] = ['TOTAL', None, None, None, None, vcs_followup_frame['net_theta'].sum(),
None, None, None, vcs_followup_frame['total_pnl'].sum(), None]
vcs_followup_frame.to_excel(writer, sheet_name='vcs')
worksheet_vcs = writer.sheets['vcs']
worksheet_vcs.set_column('B:B', 18)
worksheet_vcs.freeze_panes(1, 0)
worksheet_vcs.autofilter(0, 0, len(vcs_followup_frame.index),
len(vcs_followup_frame.columns))
if 'con' not in kwargs.keys():
con.close()
writer.save()
def generate_ocs_followup_report(**kwargs):
if 'as_of_date' in kwargs.keys():
as_of_date = kwargs['as_of_date']
else:
as_of_date = exp.doubledate_shift_bus_days()
kwargs['as_of_date'] = as_of_date
broker = kwargs['broker']
ta_output_dir = dn.get_dated_directory_extension(folder_date=as_of_date,
ext='ta')
con = msu.get_my_sql_connection(**kwargs)
if 'writer' in kwargs.keys():
writer = kwargs['writer']
else:
writer = pd.ExcelWriter(ta_output_dir + '/followup.xlsx',
engine='xlsxwriter')
strategy_frame = ts.get_open_strategies(**kwargs)
strategy_class_list = [
sc.convert_from_string_to_dictionary(
string_input=strategy_frame['description_string'][x])
['strategy_class'] for x in range(len(strategy_frame.index))
]
ocs_indx = [x == 'ocs' for x in strategy_class_list]
ocs_frame = strategy_frame[ocs_indx]
if ocs_frame.empty:
writer.save()
return
results = [
sf.get_results_4strategy(alias=ocs_frame['alias'].iloc[x],
strategy_info_output=ocs_frame.iloc[x],
con=con,
broker=broker,
date_to=as_of_date)
for x in range(len(ocs_frame.index))
]
ocs_followup_frame = pd.DataFrame(results)
ocs_followup_frame['alias'] = ocs_frame['alias'].values
kwargs['name'] = 'final'
pnl_frame = pm.get_daily_pnl_snapshot(**kwargs)
merged_frame1 = pd.merge(ocs_followup_frame,
pnl_frame,
how='left',
on='alias')
ocs_followup_frame = merged_frame1[[
'alias', 'dollar_noise', 'time_held', 'daily_pnl', 'total_pnl', 'notes'
]]
ocs_followup_frame.reset_index(drop=True, inplace=True)
ocs_followup_frame.loc[max(ocs_followup_frame.index) + 1] = [
'TOTAL', np.nan, np.nan, ocs_followup_frame['daily_pnl'].sum(),
ocs_followup_frame['total_pnl'].sum(), ''
]
date_from30 = cu.doubledate_shift(as_of_date, 30)
history_frame = ts.select_strategies(close_date_from=date_from30,
close_date_to=as_of_date,
con=con)
strategy_class_list = [
sc.convert_from_string_to_dictionary(
string_input=history_frame['description_string'][x])
['strategy_class'] for x in range(len(history_frame.index))
]
ocs_indx = [x == 'ocs' for x in strategy_class_list]
ocs_history_frame = history_frame[ocs_indx]
pnl_past_month = ocs_history_frame['pnl'].sum()
as_of_datetime = cu.convert_doubledate_2datetime(as_of_date)
date_from7 = as_of_datetime + dt.timedelta(days=-7)
ocs_short_history_frame = ocs_history_frame[
ocs_history_frame['close_date'] >= date_from7]
pnl_past_week = ocs_short_history_frame['pnl'].sum()
ocs_followup_frame.loc[max(ocs_followup_frame.index) + 1] = [
'WEEKLY PERFORMANCE', np.nan, np.nan, np.nan, pnl_past_week, ''
]
ocs_followup_frame.loc[max(ocs_followup_frame.index) + 1] = [
'MONTHLY PERFORMANCE', np.nan, np.nan, np.nan, pnl_past_month, ''
]
ocs_followup_frame['total_pnl'] = ocs_followup_frame['total_pnl'].astype(
int)
ocs_followup_frame.to_excel(writer, sheet_name='ocs')
worksheet_ocs = writer.sheets['ocs']
worksheet_ocs.freeze_panes(1, 0)
worksheet_ocs.set_column('B:B', 26)
worksheet_ocs.autofilter(0, 0, len(ocs_followup_frame.index),
len(ocs_followup_frame.columns))
if 'con' not in kwargs.keys():
con.close()
writer.save()
def generate_vcs_followup_report(**kwargs):
if 'as_of_date' in kwargs.keys():
as_of_date = kwargs['as_of_date']
else:
as_of_date = exp.doubledate_shift_bus_days()
kwargs['as_of_date'] = as_of_date
ta_output_dir = dn.get_dated_directory_extension(folder_date=as_of_date,
ext='ta')
con = msu.get_my_sql_connection(**kwargs)
if 'writer' in kwargs.keys():
writer = kwargs['writer']
else:
writer = pd.ExcelWriter(ta_output_dir + '/followup.xlsx',
engine='xlsxwriter')
strategy_frame = ts.get_open_strategies(**kwargs)
strategy_class_list = [
sc.convert_from_string_to_dictionary(
string_input=strategy_frame['description_string'][x])
['strategy_class'] for x in range(len(strategy_frame.index))
]
vcs_indx = [x == 'vcs' for x in strategy_class_list]
vcs_frame = strategy_frame[vcs_indx]
if len(vcs_frame.index) == 0:
return writer
results = [
sf.get_results_4strategy(alias=vcs_frame['alias'].iloc[x],
strategy_info_output=vcs_frame.iloc[x])
for x in range(len(vcs_frame.index))
]
vcs_followup_frame = pd.DataFrame(results)
vcs_followup_frame['alias'] = vcs_frame['alias'].values
kwargs['name'] = 'final'
pnl_frame = pm.get_daily_pnl_snapshot(**kwargs)
merged_frame1 = pd.merge(vcs_followup_frame,
pnl_frame,
how='left',
on='alias')
vcs_followup_frame = merged_frame1[[
'alias', 'last_adjustment_days_ago', 'min_tr_dte', 'long_short_ratio',
'net_oev', 'net_theta', 'long_oev', 'short_oev', 'favQMove',
'total_pnl', 'recommendation'
]]
vcs_followup_frame['long_short_ratio'] = vcs_followup_frame[
'long_short_ratio'].round()
vcs_followup_frame['net_oev'] = vcs_followup_frame['net_oev'].round(1)
vcs_followup_frame['long_oev'] = vcs_followup_frame['long_oev'].round(1)
vcs_followup_frame['short_oev'] = vcs_followup_frame['short_oev'].round(1)
vcs_followup_frame['net_theta'] = vcs_followup_frame['net_theta'].round(1)
vcs_followup_frame.sort_values('total_pnl', ascending=False, inplace=True)
vcs_followup_frame.reset_index(drop=True, inplace=True)
vcs_followup_frame.loc[len(vcs_followup_frame.index)] = [
'TOTAL', None, None, None, None, vcs_followup_frame['net_theta'].sum(),
None, None, None, vcs_followup_frame['total_pnl'].sum(), None
]
vcs_followup_frame.to_excel(writer, sheet_name='vcs')
worksheet_vcs = writer.sheets['vcs']
worksheet_vcs.set_column('B:B', 18)
worksheet_vcs.freeze_panes(1, 0)
worksheet_vcs.autofilter(0, 0, len(vcs_followup_frame.index),
len(vcs_followup_frame.columns))
if 'con' not in kwargs.keys():
con.close()
return writer
def generate_futures_butterfly_followup_report(**kwargs):
con = msu.get_my_sql_connection(**kwargs)
if 'as_of_date' in kwargs.keys():
as_of_date = kwargs['as_of_date']
else:
as_of_date = exp.doubledate_shift_bus_days()
kwargs['as_of_date'] = as_of_date
if 'writer' in kwargs.keys():
writer = kwargs['writer']
else:
ta_output_dir = dn.get_dated_directory_extension(
folder_date=as_of_date, ext='ta')
writer = pd.ExcelWriter(ta_output_dir + '/followup.xlsx',
engine='xlsxwriter')
strategy_frame = ts.get_open_strategies(**kwargs)
strategy_class_list = [
sc.convert_from_string_to_dictionary(
string_input=strategy_frame['description_string'][x])
['strategy_class'] for x in range(len(strategy_frame.index))
]
futures_butterfly_indx = [
x == 'futures_butterfly' for x in strategy_class_list
]
futures_butterfly_frame = strategy_frame[futures_butterfly_indx]
results = [
sf.get_results_4strategy(
alias=futures_butterfly_frame['alias'].iloc[x],
strategy_info_output=futures_butterfly_frame.iloc[x])
for x in range(len(futures_butterfly_frame.index))
]
butterfly_followup_frame = pd.DataFrame(results)
butterfly_followup_frame['alias'] = futures_butterfly_frame['alias'].values
pnl_frame = pm.get_daily_pnl_snapshot(as_of_date=as_of_date,
con=con,
name='final')
risk_output = hr.get_historical_risk_4open_strategies(
as_of_date=as_of_date, con=con)
merged_frame1 = pd.merge(butterfly_followup_frame,
pnl_frame,
how='left',
on='alias')
merged_frame2 = pd.merge(merged_frame1,
risk_output['strategy_risk_frame'],
how='left',
on='alias')
butterfly_followup_frame = merged_frame2[[
'alias', 'ticker_head', 'holding_tr_dte', 'short_tr_dte', 'z1_initial',
'z1', 'QF_initial', 'QF', 'total_pnl', 'downside', 'recommendation'
]]
butterfly_followup_frame.rename(columns={
'alias': 'Alias',
'ticker_head': 'TickerHead',
'holding_tr_dte': 'HoldingTrDte',
'short_tr_dte': 'ShortTrDte',
'z1_initial': 'Z1Initial',
'z1': 'Z1',
'QF_initial': 'QFInitial',
'total_pnl': 'TotalPnl',
'downside': 'Downside',
'recommendation': 'Recommendation'
},
inplace=True)
butterfly_followup_frame.sort_values('QF', ascending=False, inplace=True)
butterfly_followup_frame['Z1'] = butterfly_followup_frame['Z1'].round(2)
butterfly_followup_frame.to_excel(writer, sheet_name='butterflies')
worksheet_butterflies = writer.sheets['butterflies']
worksheet_butterflies.set_column('B:B', 26)
worksheet_butterflies.freeze_panes(1, 0)
worksheet_butterflies.autofilter(0, 0, len(butterfly_followup_frame.index),
len(butterfly_followup_frame.columns))
if 'con' not in kwargs.keys():
con.close()
return writer
def get_intraday_data_contract_frame(**kwargs):
current_date = cu.get_doubledate()
con = msu.get_my_sql_connection(**kwargs)
strategy_frame = tas.get_open_strategies(as_of_date=current_date, con=con)
strategy_class_list = [
sc.convert_from_string_to_dictionary(
string_input=strategy_frame['description_string'][x])
['strategy_class'] for x in range(len(strategy_frame.index))
]
hedge_indx = [
x in ['vcs', 'scv', 'optionInventory'] for x in strategy_class_list
]
hedge_frame = strategy_frame[hedge_indx]
options_frame_list = []
for i in range(len(hedge_frame.index)):
position_frame = tas.get_net_position_4strategy_alias(
alias=hedge_frame['alias'].iloc[i],
as_of_date=current_date,
con=con)
options_frame = position_frame[position_frame['instrument'] == 'O']
options_frame['underlying_ticker'] = [
omu.get_option_underlying(ticker=x)
for x in options_frame['ticker']
]
options_frame_list.append(options_frame)
merged_frame = pd.concat(options_frame_list)
underlying_ticker_list = list(merged_frame['underlying_ticker'].unique())
delta_alias = get_delta_strategy_alias(con=con)
delta_position_frame = tas.get_net_position_4strategy_alias(
alias=delta_alias, as_of_date=current_date, con=con)
contract_frame = pd.DataFrame()
contract_frame['ticker'] = list(
set(delta_position_frame['ticker'].unique())
| set(underlying_ticker_list))
contract_specs_output_list = [
cmi.get_contract_specs(x) for x in contract_frame['ticker']
]
contract_frame['ticker_head'] = [
x['ticker_head'] for x in contract_specs_output_list
]
contract_frame['ticker_class'] = [
x['ticker_class'] for x in contract_specs_output_list
]
contract_frame['cont_indx'] = [
x['cont_indx'] for x in contract_specs_output_list
]
contract_frame['is_spread_q'] = False
contract_frame.sort_values(['ticker_head', 'cont_indx'],
ascending=[True, True],
inplace=True)
contract_frame.reset_index(drop=True, inplace=True)
non_flat_frame = contract_frame[~contract_frame['ticker_class'].
isin(flat_curve_ticker_class_list)]
unique_ticker_head_list = non_flat_frame['ticker_head'].unique()
for i in range(len(unique_ticker_head_list)):
ticker_head_frame = non_flat_frame[non_flat_frame['ticker_head'] ==
unique_ticker_head_list[i]]
if len(ticker_head_frame.index) > 1:
for j in range(len(ticker_head_frame.index) - 1):
for k in range(j + 1, len(ticker_head_frame.index)):
spread_ticker = ticker_head_frame['ticker'].iloc[
j] + '-' + ticker_head_frame['ticker'].iloc[k]
contract_frame.loc[len(contract_frame.index)] = [
spread_ticker,
ticker_head_frame['ticker_head'].iloc[j],
ticker_head_frame['ticker_class'].iloc[j],
ticker_head_frame['cont_indx'].iloc[j], True
]
if 'con' not in kwargs.keys():
con.close()
return contract_frame
def get_historical_risk_4open_strategies(**kwargs):
if 'as_of_date' in kwargs.keys():
as_of_date = kwargs['as_of_date']
else:
as_of_date = exp.doubledate_shift_bus_days()
kwargs['as_of_date'] = as_of_date
ta_output_dir = dn.get_dated_directory_extension(folder_date=as_of_date, ext='ta')
if os.path.isfile(ta_output_dir + '/portfolio_risk.pkl'):
with open(ta_output_dir + '/portfolio_risk.pkl','rb') as handle:
portfolio_risk_output = pickle.load(handle)
return portfolio_risk_output
con = msu.get_my_sql_connection(**kwargs)
strategy_frame = ts.get_open_strategies(**kwargs)
futures_data_dictionary = {x: gfp.get_futures_price_preloaded(ticker_head=x) for x in cmi.ticker_class.keys()}
strategy_risk_frame = pd.DataFrame()
historical_risk_output = [get_historical_risk_4strategy(alias=x,
as_of_date=as_of_date,
con=con,
futures_data_dictionary=futures_data_dictionary)
for x in strategy_frame['alias']]
if 'con' not in kwargs.keys():
con.close()
strategy_risk_frame['alias'] = strategy_frame['alias']
strategy_risk_frame['downside'] = [x['downside'] for x in historical_risk_output]
strategy_risk_frame.sort_values('downside', ascending=True, inplace=True)
ticker_head_list = su.flatten_list([list(x['ticker_head_based_pnl_5_change'].keys()) for x in historical_risk_output if x['downside'] != 0])
unique_ticker_head_list = list(set(ticker_head_list))
ticker_head_aggregated_pnl_5_change = {ticker_head: sum([x['ticker_head_based_pnl_5_change'][ticker_head] for x in historical_risk_output
if x['downside'] != 0 and ticker_head in x['ticker_head_based_pnl_5_change'].keys()]) for ticker_head in unique_ticker_head_list}
percentile_vector = [stats.get_number_from_quantile(y=ticker_head_aggregated_pnl_5_change[ticker_head],
quantile_list=[1, 15],
clean_num_obs=max(100, round(3*len(ticker_head_aggregated_pnl_5_change[ticker_head].values)/4)))
for ticker_head in unique_ticker_head_list]
ticker_head_risk_frame = pd.DataFrame()
ticker_head_risk_frame['tickerHead'] = unique_ticker_head_list
ticker_head_risk_frame['downside'] = [(x[0]+x[1])/2 for x in percentile_vector]
ticker_head_risk_frame.sort_values('downside', ascending=True, inplace=True)
strategy_risk_frame['downside'] = strategy_risk_frame['downside'].round()
ticker_head_risk_frame['downside'] = ticker_head_risk_frame['downside'].round()
portfolio_risk_output = {'strategy_risk_frame': strategy_risk_frame,
'ticker_head_aggregated_pnl_5_change': ticker_head_aggregated_pnl_5_change,
'ticker_head_risk_frame':ticker_head_risk_frame}
with open(ta_output_dir + '/portfolio_risk.pkl', 'wb') as handle:
pickle.dump(portfolio_risk_output, handle)
return portfolio_risk_output
def get_historical_risk_4open_strategies(**kwargs):
if 'as_of_date' in kwargs.keys():
as_of_date = kwargs['as_of_date']
else:
as_of_date = exp.doubledate_shift_bus_days()
kwargs['as_of_date'] = as_of_date
ta_output_dir = dn.get_dated_directory_extension(folder_date=as_of_date, ext='ta')
if os.path.isfile(ta_output_dir + '/portfolio_risk.pkl'):
with open(ta_output_dir + '/portfolio_risk.pkl','rb') as handle:
portfolio_risk_output = pickle.load(handle)
return portfolio_risk_output
con = msu.get_my_sql_connection(**kwargs)
strategy_frame = ts.get_open_strategies(**kwargs)
futures_data_dictionary = {x: gfp.get_futures_price_preloaded(ticker_head=x) for x in cmi.ticker_class.keys()}
strategy_risk_frame = pd.DataFrame()
historical_risk_output = [get_historical_risk_4strategy(alias=x,
as_of_date=as_of_date,
con=con,
futures_data_dictionary=futures_data_dictionary)
for x in strategy_frame['alias']]
if 'con' not in kwargs.keys():
con.close()
strategy_risk_frame['alias'] = strategy_frame['alias']
strategy_risk_frame['downside'] = [x['downside'] for x in historical_risk_output]
strategy_risk_frame.sort('downside', ascending=True, inplace=True)
ticker_head_list = su.flatten_list([list(x['ticker_head_based_pnl_5_change'].keys()) for x in historical_risk_output if x['downside'] != 0])
unique_ticker_head_list = list(set(ticker_head_list))
ticker_head_aggregated_pnl_5_change = {ticker_head: sum([x['ticker_head_based_pnl_5_change'][ticker_head] for x in historical_risk_output
if x['downside'] != 0 and ticker_head in x['ticker_head_based_pnl_5_change'].keys()]) for ticker_head in unique_ticker_head_list}
percentile_vector = [stats.get_number_from_quantile(y=ticker_head_aggregated_pnl_5_change[ticker_head],
quantile_list=[1, 15],
clean_num_obs=max(100, round(3*len(ticker_head_aggregated_pnl_5_change[ticker_head].values)/4)))
for ticker_head in unique_ticker_head_list]
ticker_head_risk_frame = pd.DataFrame()
ticker_head_risk_frame['tickerHead'] = unique_ticker_head_list
ticker_head_risk_frame['downside'] = [(x[0]+x[1])/2 for x in percentile_vector]
ticker_head_risk_frame.sort('downside', ascending=True, inplace=True)
strategy_risk_frame['downside'] = strategy_risk_frame['downside'].round()
ticker_head_risk_frame['downside'] = ticker_head_risk_frame['downside'].round()
portfolio_risk_output = {'strategy_risk_frame': strategy_risk_frame,
'ticker_head_aggregated_pnl_5_change': ticker_head_aggregated_pnl_5_change,
'ticker_head_risk_frame':ticker_head_risk_frame}
with open(ta_output_dir + '/portfolio_risk.pkl', 'wb') as handle:
pickle.dump(portfolio_risk_output, handle)
return portfolio_risk_output
