def get_tickers_4date(**kwargs): data_out = cl.generate_futures_list_dataframe(**kwargs) data_out = data_out[(data_out['tr_dte'] >= 40)] data_out.sort(['ticker_head', 'tr_dte'], ascending=[True, True], inplace=True) data_out.drop_duplicates(subset=['ticker_head'], take_last=False, inplace=True) data_out.reset_index(drop=True, inplace=True) return data_out[['ticker', 'ticker_head', 'ticker_class']]
def generate_underlying_proxy_report(**kwargs): con = msu.get_my_sql_connection(**kwargs) report_date = kwargs['report_date'] futures_dataframe = cl.generate_futures_list_dataframe(date_to=report_date) futures_flat_curve = futures_dataframe[ futures_dataframe['ticker_class'].isin( ['FX', 'Metal', 'Treasury', 'Index'])] futures_flat_curve.reset_index(drop=True, inplace=True) futures_flat_curve['tr_days_2roll'] = futures_flat_curve.apply( lambda x: exp.get_days2_roll(ticker=x['ticker'], instrument='Futures', date_to=report_date, con=con)['tr_days_2roll'], axis=1) futures_data_dictionary = { x: gfp.get_futures_price_preloaded(ticker_head=x) for x in futures_flat_curve['ticker_head'].unique() } proxy_output_list = [ get_underlying_proxy_ticker( ticker=futures_flat_curve['ticker'].iloc[x], settle_date=report_date, con=con, futures_data_dictionary=futures_data_dictionary) for x in range(len(futures_flat_curve.index)) ] futures_flat_curve['proxy_ticker'] = [ x['ticker'] for x in proxy_output_list ] futures_flat_curve['add_2_proxy'] = [ x['add_2_proxy'] for x in proxy_output_list ] ta_output_dir = dn.get_dated_directory_extension(folder_date=report_date, ext='ta') writer = pd.ExcelWriter(ta_output_dir + '/proxy_report.xlsx', engine='xlsxwriter') futures_flat_curve = futures_flat_curve[[ 'ticker', 'ticker_head', 'ticker_class', 'volume', 'tr_days_2roll', 'proxy_ticker', 'add_2_proxy' ]] futures_flat_curve.to_excel(writer, sheet_name='proxy_report') writer.save() if 'con' not in kwargs.keys(): con.close()
def get_futures_butterflies_4date(**kwargs): futures_dataframe = cl.generate_futures_list_dataframe(**kwargs) if 'volume_filter' in kwargs.keys(): volume_filter = kwargs['volume_filter'] futures_dataframe = futures_dataframe[futures_dataframe['volume']>volume_filter] futures_dataframe.reset_index(drop=True, inplace=True) unique_ticker_heads = cmi.futures_butterfly_strategy_tickerhead_list tuples = [] for ticker_head_i in unique_ticker_heads: ticker_head_data = futures_dataframe[futures_dataframe['ticker_head'] == ticker_head_i] ticker_head_data.sort(['ticker_year','ticker_month'], ascending=[True, True], inplace=True) if len(ticker_head_data.index) >= 3: tuples = tuples + [(ticker_head_data.index[i-1], ticker_head_data.index[i],ticker_head_data.index[i+1]) for i in range(1, len(ticker_head_data.index)-1)] if len(ticker_head_data.index) >= 5: tuples = tuples + [(ticker_head_data.index[i-2], ticker_head_data.index[i],ticker_head_data.index[i+2]) for i in range(2, len(ticker_head_data.index)-2)] if len(ticker_head_data.index) >= 7: tuples = tuples + [(ticker_head_data.index[i-3], ticker_head_data.index[i],ticker_head_data.index[i+3]) for i in range(3, len(ticker_head_data.index)-3)] return pd.DataFrame([(futures_dataframe['ticker'][indx[0]], futures_dataframe['ticker'][indx[1]], futures_dataframe['ticker'][indx[2]], futures_dataframe['ticker_head'][indx[0]], futures_dataframe['ticker_class'][indx[0]], futures_dataframe['tr_dte'][indx[0]], futures_dataframe['tr_dte'][indx[1]], futures_dataframe['tr_dte'][indx[2]], futures_dataframe['multiplier'][indx[0]], futures_dataframe['aggregation_method'][indx[0]], futures_dataframe['contracts_back'][indx[0]]) for indx in tuples], columns=['ticker1','ticker2','ticker3','tickerHead','tickerClass','trDte1','trDte2','trDte3','multiplier','agg','cBack'])
def get_futures_butterflies_4date(**kwargs): futures_dataframe = cl.generate_futures_list_dataframe(**kwargs) if 'volume_filter' in kwargs.keys(): volume_filter = kwargs['volume_filter'] futures_dataframe = futures_dataframe[ futures_dataframe['volume'] > volume_filter] futures_dataframe.reset_index(drop=True, inplace=True) unique_ticker_heads = cmi.futures_butterfly_strategy_tickerhead_list tuples = [] for ticker_head_i in unique_ticker_heads: ticker_head_data = futures_dataframe[futures_dataframe['ticker_head'] == ticker_head_i] ticker_head_data.sort_values(['ticker_year', 'ticker_month'], ascending=[True, True], inplace=True) if len(ticker_head_data.index) >= 3: tuples = tuples + [ (ticker_head_data.index[i - 1], ticker_head_data.index[i], ticker_head_data.index[i + 1]) for i in range(1, len(ticker_head_data.index) - 1) ] if len(ticker_head_data.index) >= 5: tuples = tuples + [ (ticker_head_data.index[i - 2], ticker_head_data.index[i], ticker_head_data.index[i + 2]) for i in range(2, len(ticker_head_data.index) - 2) ] if len(ticker_head_data.index) >= 7: tuples = tuples + [ (ticker_head_data.index[i - 3], ticker_head_data.index[i], ticker_head_data.index[i + 3]) for i in range(3, len(ticker_head_data.index) - 3) ] return pd.DataFrame([(futures_dataframe['ticker'][indx[0]], futures_dataframe['ticker'][indx[1]], futures_dataframe['ticker'][indx[2]], futures_dataframe['ticker_head'][indx[0]], futures_dataframe['ticker_class'][indx[0]], futures_dataframe['tr_dte'][indx[0]], futures_dataframe['tr_dte'][indx[1]], futures_dataframe['tr_dte'][indx[2]], futures_dataframe['multiplier'][indx[0]], futures_dataframe['aggregation_method'][indx[0]], futures_dataframe['contracts_back'][indx[0]]) for indx in tuples], columns=[ 'ticker1', 'ticker2', 'ticker3', 'tickerHead', 'tickerClass', 'trDte1', 'trDte2', 'trDte3', 'multiplier', 'agg', 'cBack' ])
def get_overnight_spreads_4date(**kwargs): date_to = kwargs['date_to'] datetime_to = cu.convert_doubledate_2datetime(date_to) kwargs['date_from'] = cu.doubledate_shift(date_to, 6) futures_dataframe = cl.generate_futures_list_dataframe(**kwargs) futures_dataframe.sort_values(['ticker', 'settle_date'], ascending=[True, True], inplace=True) grouped = futures_dataframe.groupby(['ticker']) mean_frame = pd.DataFrame() mean_frame['averege_volume'] = grouped['volume'].mean() mean_frame['ticker'] = grouped['ticker'].last() mean_frame.index.names = [ 'ticker_index' if x is 'ticker' else x for x in mean_frame.index.names ] mean_frame['averege_volume'] = mean_frame['averege_volume'].round() futures_dataframe = futures_dataframe[futures_dataframe['settle_date'] == datetime_to] futures_dataframe = pd.merge(futures_dataframe, mean_frame, on='ticker', how='left') if 'volume_filter' in kwargs.keys(): volume_filter = kwargs['volume_filter'] futures_dataframe = futures_dataframe[ futures_dataframe['averege_volume'] > volume_filter] futures_dataframe.reset_index(drop=True, inplace=True) unique_ticker_heads = cmi.futures_butterfly_strategy_tickerhead_list tuples = [] for ticker_head_i in unique_ticker_heads: ticker_head_data = futures_dataframe[futures_dataframe['ticker_head'] == ticker_head_i] ticker_head_data.sort_values(['ticker_year', 'ticker_month'], ascending=[True, True], inplace=True) if len(ticker_head_data.index) >= 2: tuples = tuples + [ (ticker_head_data.index[i], ticker_head_data.index[i + 1]) for i in range(len(ticker_head_data.index) - 1) ] return pd.DataFrame([(futures_dataframe['ticker'][indx[0]], futures_dataframe['ticker'][indx[1]], futures_dataframe['ticker_head'][indx[0]], futures_dataframe['ticker_class'][indx[0]], futures_dataframe['tr_dte'][indx[0]], futures_dataframe['tr_dte'][indx[1]], min([ futures_dataframe['averege_volume'][indx[0]], futures_dataframe['averege_volume'][indx[1]] ]), futures_dataframe['multiplier'][indx[0]], futures_dataframe['aggregation_method'][indx[0]], futures_dataframe['contracts_back'][indx[0]]) for indx in tuples], columns=[ 'ticker1', 'ticker2', 'tickerHead', 'tickerClass', 'trDte1', 'trDte2', 'min_avg_volume', 'multiplier', 'agg', 'cBack' ])
def get_spreads_4date(**kwargs): futures_dataframe = cl.generate_futures_list_dataframe(**kwargs) if 'volume_filter' in kwargs.keys(): volume_filter = kwargs['volume_filter'] futures_dataframe = futures_dataframe[futures_dataframe['volume'] > volume_filter] futures_dataframe.reset_index(drop=True, inplace=True) futures_dataframe['yearMonth'] = 12*futures_dataframe['ticker_year']+futures_dataframe['ticker_month'] futures_dataframe['yearMonthMerge'] = futures_dataframe['yearMonth'] futures_dataframe = futures_dataframe[['ticker','yearMonth','yearMonthMerge','ticker_head','volume']] spread_frame = pd.read_excel(dna.get_directory_name(ext='python_file') + '/opportunity_constructs/user_defined_spreads.xlsx') output_frame = pd.DataFrame() for i in range(len(spread_frame.index)): tickerhead1 = spread_frame['tickerHead1'].iloc[i] tickerhead2 = spread_frame['tickerHead2'].iloc[i] tickerhead3 = spread_frame['tickerHead3'].iloc[i] tickerhead4 = spread_frame['tickerHead4'].iloc[i] frame1 = futures_dataframe[futures_dataframe['ticker_head'] == tickerhead1] frame2 = futures_dataframe[futures_dataframe['ticker_head'] == tickerhead2] frame3 = futures_dataframe[futures_dataframe['ticker_head'] == tickerhead3] frame4 = futures_dataframe[futures_dataframe['ticker_head'] == tickerhead4] merged11 = pd.merge(frame1, frame2, how='inner', on='yearMonthMerge') frame2['yearMonthMerge'] = frame2['yearMonth']+1 merged12 = pd.merge(frame1, frame2, how='inner', on='yearMonthMerge') frame2['yearMonthMerge'] = frame2['yearMonth']-1 merged10 = pd.merge(frame1, frame2, how='inner', on='yearMonthMerge') if frame3.empty: output_frame2 = pd.concat([merged11, merged12, merged10]) spread_i = pd.DataFrame() spread_i['contract1'] = output_frame2['ticker_x'] spread_i['contract2'] = output_frame2['ticker_y'] spread_i['contract3'] = None spread_i['ticker_head1'] = tickerhead1 spread_i['ticker_head2'] = tickerhead2 spread_i['ticker_head3'] = None spread_i['volume1'] = output_frame2['volume_x'] spread_i['volume2'] = output_frame2['volume_y'] spread_i['volume3'] = None output_frame = pd.concat([output_frame, spread_i]) elif frame4.empty: frame3 = futures_dataframe[futures_dataframe['ticker_head'] == tickerhead3] merged111 = pd.merge(merged11, frame3, how='inner', on='yearMonthMerge') merged121 = pd.merge(merged12, frame3, how='inner', on='yearMonthMerge') merged101 = pd.merge(merged10, frame3, how='inner', on='yearMonthMerge') frame3['yearMonthMerge'] = frame3['yearMonth']+1 merged112 = pd.merge(merged11, frame3, how='inner', on='yearMonthMerge') merged122 = pd.merge(merged12, frame3, how='inner', on='yearMonthMerge') frame3['yearMonthMerge'] = frame3['yearMonth']-1 merged110 = pd.merge(merged11, frame3, how='inner', on='yearMonthMerge') merged100 = pd.merge(merged10, frame3, how='inner', on='yearMonthMerge') output_frame3 = pd.concat([merged111,merged121,merged101, merged112,merged122,merged110,merged100],sort=True) spread_i = pd.DataFrame() spread_i['contract1'] = output_frame3['ticker_x'] spread_i['contract2'] = output_frame3['ticker_y'] spread_i['contract3'] = output_frame3['ticker'] spread_i['ticker_head1'] = tickerhead1 spread_i['ticker_head2'] = tickerhead2 spread_i['ticker_head3'] = tickerhead3 spread_i['volume1'] = output_frame3['volume_x'] spread_i['volume2'] = output_frame3['volume_y'] spread_i['volume3'] = output_frame3['volume'] output_frame = pd.concat([output_frame,spread_i]) output_frame['spread_description'] = output_frame.apply(lambda x: x['ticker_head1']+ '_' +x['ticker_head2'] if x['ticker_head3'] is None else x['ticker_head1']+ '_' +x['ticker_head2'] + '_' + x['ticker_head3'] , axis=1) output_frame['min_volume'] = output_frame.apply(lambda x: min(x['volume1'],x['volume2']) if x['ticker_head3'] is None else min(x['volume1'],x['volume2'],x['volume3']),axis=1) output_frame.sort_values(['spread_description','min_volume'],ascending=[True, False],inplace=True) output_frame.drop_duplicates('spread_description',inplace=True) output_frame.reset_index(drop=True,inplace=True) return output_frame
def get_spreads_4date(**kwargs): futures_dataframe = cl.generate_futures_list_dataframe(**kwargs) if 'volume_filter' in kwargs.keys(): volume_filter = kwargs['volume_filter'] futures_dataframe = futures_dataframe[futures_dataframe['volume'] > volume_filter] futures_dataframe.reset_index(drop=True, inplace=True) futures_dataframe['yearMonth'] = 12*futures_dataframe['ticker_year']+futures_dataframe['ticker_month'] futures_dataframe['yearMonthMerge'] = futures_dataframe['yearMonth'] futures_dataframe = futures_dataframe[['ticker','yearMonth','yearMonthMerge','ticker_head','volume']] spread_frame = pd.read_excel(dn.get_directory_name(ext='python_file') + '/opportunity_constructs/user_defined_spreads.xlsx') output_frame = pd.DataFrame() for i in range(len(spread_frame.index)): tickerhead1 = spread_frame['tickerHead1'].iloc[i] tickerhead2 = spread_frame['tickerHead2'].iloc[i] tickerhead3 = spread_frame['tickerHead3'].iloc[i] tickerhead4 = spread_frame['tickerHead4'].iloc[i] frame1 = futures_dataframe[futures_dataframe['ticker_head'] == tickerhead1] frame2 = futures_dataframe[futures_dataframe['ticker_head'] == tickerhead2] frame3 = futures_dataframe[futures_dataframe['ticker_head'] == tickerhead3] frame4 = futures_dataframe[futures_dataframe['ticker_head'] == tickerhead4] merged11 = pd.merge(frame1, frame2, how='inner', on='yearMonthMerge') frame2['yearMonthMerge'] = frame2['yearMonth']+1 merged12 = pd.merge(frame1, frame2, how='inner', on='yearMonthMerge') frame2['yearMonthMerge'] = frame2['yearMonth']-1 merged10 = pd.merge(frame1, frame2, how='inner', on='yearMonthMerge') if frame3.empty: output_frame2 = pd.concat([merged11, merged12, merged10]) spread_i = pd.DataFrame() spread_i['contract1'] = output_frame2['ticker_x'] spread_i['contract2'] = output_frame2['ticker_y'] spread_i['contract3'] = None spread_i['ticker_head1'] = tickerhead1 spread_i['ticker_head2'] = tickerhead2 spread_i['ticker_head3'] = None spread_i['volume1'] = output_frame2['volume_x'] spread_i['volume2'] = output_frame2['volume_y'] spread_i['volume3'] = None output_frame = pd.concat([output_frame, spread_i]) elif frame4.empty: frame3 = futures_dataframe[futures_dataframe['ticker_head'] == tickerhead3] merged111 = pd.merge(merged11, frame3, how='inner', on='yearMonthMerge') merged121 = pd.merge(merged12, frame3, how='inner', on='yearMonthMerge') merged101 = pd.merge(merged10, frame3, how='inner', on='yearMonthMerge') frame3['yearMonthMerge'] = frame3['yearMonth']+1 merged112 = pd.merge(merged11, frame3, how='inner', on='yearMonthMerge') merged122 = pd.merge(merged12, frame3, how='inner', on='yearMonthMerge') frame3['yearMonthMerge'] = frame3['yearMonth']-1 merged110 = pd.merge(merged11, frame3, how='inner', on='yearMonthMerge') merged100 = pd.merge(merged10, frame3, how='inner', on='yearMonthMerge') output_frame3 = pd.concat([merged111,merged121,merged101, merged112,merged122,merged110,merged100]) spread_i = pd.DataFrame() spread_i['contract1'] = output_frame3['ticker_x'] spread_i['contract2'] = output_frame3['ticker_y'] spread_i['contract3'] = output_frame3['ticker'] spread_i['ticker_head1'] = tickerhead1 spread_i['ticker_head2'] = tickerhead2 spread_i['ticker_head3'] = tickerhead3 spread_i['volume1'] = output_frame3['volume_x'] spread_i['volume2'] = output_frame3['volume_y'] spread_i['volume3'] = output_frame3['volume'] output_frame = pd.concat([output_frame,spread_i]) output_frame.reset_index(drop=True,inplace=True) return output_frame