def generate_historic_risk_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')
historic_risk_output = hr.get_historical_risk_4open_strategies(**kwargs)
strategy_risk_frame = historic_risk_output['strategy_risk_frame']
ticker_head_risk_frame = historic_risk_output['ticker_head_risk_frame']
writer = pd.ExcelWriter(ta_output_dir + '/risk.xlsx', engine='xlsxwriter')
strategy_risk_frame.to_excel(writer, sheet_name='strategies')
ticker_head_risk_frame.to_excel(writer, sheet_name='tickerHeads')
worksheet_strategies = writer.sheets['strategies']
worksheet_ticker_heads = writer.sheets['tickerHeads']
worksheet_strategies.freeze_panes(1, 0)
worksheet_ticker_heads.freeze_panes(1, 0)
worksheet_strategies.autofilter(0, 0, len(strategy_risk_frame.index),
len(strategy_risk_frame.columns))
worksheet_ticker_heads.autofilter(0, 0, len(ticker_head_risk_frame.index),
len(ticker_head_risk_frame.columns))
writer.save()
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_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
