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