Пример #1
0
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']]
Пример #2
0
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