コード例 #1
0
def update_options_price_database_from_cme_files(**kwargs):

    if 'settle_date' in kwargs.keys():
        settle_date = kwargs['settle_date']
    else:
        settle_date = int(time.strftime('%Y%m%d'))
        kwargs['settle_date'] = settle_date

    if 'con' not in kwargs.keys():
        close_connection_before_exit = True
        con = msu.get_my_sql_connection(**kwargs)
        kwargs['con'] = con
    else:
        close_connection_before_exit = False

    if not exp.is_business_day(double_date=settle_date):
        if close_connection_before_exit:
            con.close()
        return

    data_read_out = {}
    data_read_out['commodity'] = rcf.read_cme_settle_txt_files(
        file_name='commodity', report_date=settle_date)
    data_read_out['equity'] = rcf.read_cme_settle_txt_files(
        file_name='equity', report_date=settle_date)
    data_read_out['fx'] = rcf.read_cme_settle_txt_files(
        file_name='fx', report_date=settle_date)
    data_read_out['interest_rate'] = rcf.read_cme_settle_txt_files(
        file_name='interest_rate', report_date=settle_date)

    options_frame = cl.generate_liquid_options_list_dataframe(**kwargs)

    for i in range(len(options_frame.index)):

        ticker = options_frame['ticker'].iloc[i]
        expiration_date = options_frame['expiration_date'].iloc[i]

        update_options_price_database_from_cme_files_4ticker(
            ticker=ticker,
            expiration_date=expiration_date,
            data_read_out=data_read_out,
            **kwargs)

    if close_connection_before_exit:
        con.close()
コード例 #2
0
def update_options_price_database_from_cme_files(**kwargs):

    if 'settle_date' in kwargs.keys():
        settle_date = kwargs['settle_date']
    else:
        settle_date = int(time.strftime('%Y%m%d'))
        kwargs['settle_date'] = settle_date

    if 'con' not in kwargs.keys():
        close_connection_before_exit = True
        con = msu.get_my_sql_connection(**kwargs)
        kwargs['con'] = con
    else:
        close_connection_before_exit = False

    if not exp.is_business_day(double_date=settle_date):
        if close_connection_before_exit:
            con.close()
        return

    data_read_out = {}
    data_read_out['commodity'] = rcf.read_cme_settle_txt_files(file_name='commodity', report_date=settle_date)
    data_read_out['equity'] = rcf.read_cme_settle_txt_files(file_name='equity', report_date=settle_date)
    data_read_out['fx'] = rcf.read_cme_settle_txt_files(file_name='fx', report_date=settle_date)
    data_read_out['interest_rate'] = rcf.read_cme_settle_txt_files(file_name='interest_rate', report_date=settle_date)

    options_frame = cl.generate_liquid_options_list_dataframe(**kwargs)

    for i in range(len(options_frame.index)):

        ticker = options_frame['ticker'].iloc[i]
        expiration_date = options_frame['expiration_date'].iloc[i]
        print(ticker)

        update_options_price_database_from_cme_files_4ticker(ticker=ticker,
                                                             expiration_date=expiration_date,
                                                             data_read_out=data_read_out,
                                                             **kwargs)

    if close_connection_before_exit:
        con.close()
コード例 #3
0
def update_options_price_database_from_cme_files_4ticker(**kwargs):

    ticker = kwargs['ticker']
    contract_specs_output = cmi.get_contract_specs(ticker)
    ticker_head = contract_specs_output['ticker_head']
    ticker_month_num = contract_specs_output['ticker_month_num']
    ticker_year = contract_specs_output['ticker_year']

    if 'settle_date' in kwargs.keys():
        settle_date = kwargs['settle_date']
        kwargs['report_date'] = settle_date
    else:
        settle_date = int(time.strftime('%Y%m%d'))
        kwargs['settle_date'] = settle_date
        kwargs['report_date'] = settle_date

    if not exp.is_business_day(double_date=settle_date,
                               reference_tickerhead=ticker_head):
        return

    if 'expiration_date' in kwargs.keys():
        expiration_date = kwargs['expiration_date']
    else:
        expiration_date = exp.get_options_expiration(ticker)
        expiration_date = expiration_date.date()

    settle_datetime = cu.convert_doubledate_2datetime(settle_date)

    if 'cal_dte' in kwargs.keys():
        cal_dte = kwargs['cal_dte']
    else:
        cal_dte = (expiration_date - settle_datetime.date()).days

    if 'tr_dte' in kwargs.keys():
        tr_dte = kwargs['tr_dte']
    else:
        bday_us = CustomBusinessDay(
            calendar=exp.get_calendar_4ticker_head(ticker_head))
        dts = pd.date_range(start=settle_datetime,
                            end=expiration_date,
                            freq=bday_us)
        tr_dte = len(
            [x for x in dts if x.to_pydatetime().date() < expiration_date])

    data_vendor_id = 2
    now = dt.datetime.now()
    con = msu.get_my_sql_connection(**kwargs)

    process_output = pco.process_cme_options_4ticker(**kwargs)

    if process_output['success']:
        settle_frame = process_output['settle_frame']
    else:
        if 'con' not in kwargs.keys():
            con.close()
        return

    column_names = settle_frame.columns.tolist()

    option_type_indx = column_names.index('option_type')
    strike_indx = column_names.index('strike')
    settle_indx = column_names.index('settle')
    volume_indx = column_names.index('volume')
    interest_indx = column_names.index('interest')

    tuples = [
        tuple([
            data_vendor_id, ticker_head, ticker_month_num, ticker_year, ticker,
            x[option_type_indx], x[strike_indx],
            settle_datetime.date(), cal_dte, tr_dte, now, now,
            None if np.isnan(x[settle_indx]) else x[settle_indx],
            None if np.isnan(x[volume_indx]) else x[volume_indx],
            None if np.isnan(x[interest_indx]) else x[interest_indx]
        ]) for x in settle_frame.values
    ]

    column_str = "data_vendor_id, ticker_head, ticker_month, ticker_year, ticker, " \
                 " option_type, strike, price_date, cal_dte, tr_dte, " \
                 " created_date,last_updated_date, close_price, volume, open_interest"

    insert_str = ("%s, " * len(column_str.split(',')))[:-2]
    final_str = "REPLACE INTO daily_option_price (%s) VALUES (%s)" % (
        column_str, insert_str)
    msu.sql_execute_many_wrapper(final_str=final_str, tuples=tuples, con=con)

    if 'con' not in kwargs.keys():
        con.close()
コード例 #4
0
def update_futures_price_database_from_cme_file(**kwargs):

    ticker_head_list = cmi.cme_futures_tickerhead_list

    import time
    con = msu.get_my_sql_connection(**kwargs)

    if 'settle_date' in kwargs.keys():
        run_date = kwargs['settle_date']
    else:
        run_date = int(time.strftime('%Y%m%d'))

    #run_date = 20160225
    data_vendor_id = 2
    now = datetime.datetime.now()
    run_datetime = cu.convert_doubledate_2datetime(run_date)

    for ticker_head in ticker_head_list:
        #print(ticker_head)

        contract_list = []

        bday_us = CustomBusinessDay(calendar=exp.get_calendar_4ticker_head(ticker_head))

        if not exp.is_business_day(double_date=run_date, reference_tickerhead=ticker_head):
            continue

        cme_output = pcf.process_cme_futures_4tickerhead(ticker_head=ticker_head, report_date=run_date)
        settle_frame = cme_output['settle_frame']

        for ticker_month in cmi.futures_contract_months[ticker_head]:
            ticker_month_num = cmi.letter_month_string.find(ticker_month)+1
            max_cal_dte = cmi.get_max_cal_dte(ticker_head=ticker_head, ticker_month=ticker_month_num)

            contract_list.extend(cl.get_db_contract_list_filtered(expiration_date_from=run_date,
                                                            expiration_date_to=cu.doubledate_shift(run_date, -max_cal_dte),
                                                            ticker_head=ticker_head, ticker_month=ticker_month_num, con=con,
                                                                  instrument='futures'))

        contract_frame = pd.DataFrame(contract_list, columns=['symbol_id', 'ticker', 'expiration_date'])
        merged_frame = pd.merge(contract_frame,settle_frame, how='inner', on='ticker')
        merged_frame.sort('expiration_date', ascending=True, inplace=True)

        column_names = merged_frame.columns.tolist()

        symbol_id_indx = column_names.index('symbol_id')
        ticker_month_indx = column_names.index('ticker_month')
        open_indx = column_names.index('open')
        high_indx = column_names.index('high')
        low_indx = column_names.index('low')
        settle_indx = column_names.index('settle')
        volume_indx = column_names.index('volume')
        interest_indx = column_names.index('interest')
        expiration_indx = column_names.index('expiration_date')

        dts = pd.date_range(start=run_datetime, end=merged_frame['expiration_date'].iloc[-1], freq=bday_us)

        tuples = [tuple([data_vendor_id, x[symbol_id_indx],
                     ticker_head,
                     x[ticker_month_indx],
                     run_datetime.date(),
                    (x[expiration_indx]-run_datetime.date()).days,
                     len([y for y in dts if y.to_datetime().date() < x[expiration_indx]]),
                     now, now,
                     None if np.isnan(x[open_indx]) else x[open_indx],
                     None if np.isnan(x[high_indx]) else x[high_indx],
                     None if np.isnan(x[low_indx]) else x[low_indx],
                     None if np.isnan(x[settle_indx]) else x[settle_indx],
                     None if np.isnan(x[volume_indx]) else x[volume_indx],
                     None if np.isnan(x[interest_indx]) else x[interest_indx]]) for x in merged_frame.values]

        column_str = "data_vendor_id, symbol_id, ticker_head, ticker_month, price_date,cal_dte, tr_dte, created_date,last_updated_date, open_price, high_price, low_price, close_price, volume, open_interest"
        insert_str = ("%s, " * 15)[:-2]
        final_str = "REPLACE INTO daily_price (%s) VALUES (%s)" % (column_str, insert_str)
        msu.sql_execute_many_wrapper(final_str=final_str, tuples=tuples, con=con)

    if 'con' not in kwargs.keys():
        con.close()
コード例 #5
0
def load_pnls_4settle_date(**kwargs):

    if 'con' not in kwargs.keys():
        close_connection_before_exit = True
        con = msu.get_my_sql_connection(**kwargs)
        kwargs['con'] = con
    else:
        close_connection_before_exit = False
        con = kwargs['con']

    if not exp.is_business_day(double_date=kwargs['settle_date']):
        if close_connection_before_exit:
            con.close()
        return

    settle_date_ex = exp.doubledate_shift_bus_days(
        double_date=kwargs['settle_date'], shift_in_days=7)

    indicator_frame = ops.get_option_ticker_indicators(
        settle_date=settle_date_ex,
        delta=0.5,
        column_names=['id', 'ticker', 'price_date', 'tr_dte', 'ticker_head'])

    if indicator_frame.empty:
        if close_connection_before_exit:
            con.close()
        return

    indicator_frame.reset_index(drop=True, inplace=True)

    delta_vol_output = [
        ops.calc_delta_vol_4ticker(ticker=x,
                                   settle_date=settle_date_ex,
                                   delta_target=0.5)
        for x in indicator_frame['ticker']
    ]

    delta_vol_output = pd.DataFrame(delta_vol_output)

    indicator_frame['option_pnl5'] = delta_vol_output['option_pnl5']
    indicator_frame['delta_pnl5'] = delta_vol_output['delta_pnl5']

    indicator_frame.dropna(inplace=True)

    column_names = indicator_frame.columns.tolist()

    #indicator_frame['option_pnl5'] = indicator_frame['option_pnl5'].astype(object)
    #indicator_frame['delta_pnl5'] = indicator_frame['delta_pnl5'].astype(object)

    id_indx = column_names.index('id')
    option_pnl_indx = column_names.index('option_pnl5')
    delta_pnl_indx = column_names.index('delta_pnl5')

    tuples = [
        tuple([x[option_pnl_indx], x[delta_pnl_indx], x[id_indx]])
        for x in indicator_frame.values
    ]

    final_str = "UPDATE option_ticker_indicators SET option_pnl5 = %s,  delta_pnl5 = %s WHERE id=%s"

    msu.sql_execute_many_wrapper(final_str=final_str, tuples=tuples, con=con)

    if close_connection_before_exit:
        con.close()
コード例 #6
0
def load_ticker_signals_4settle_date(**kwargs):

    if 'con' not in kwargs.keys():
        close_connection_before_exit = True
        con = msu.get_my_sql_connection(**kwargs)
        kwargs['con'] = con
    else:
        close_connection_before_exit = False
        con = kwargs['con']

    if not exp.is_business_day(double_date=kwargs['settle_date']):
        if close_connection_before_exit:
            con.close()
        return

    options_frame = cl.generate_liquid_options_list_dataframe(**kwargs)

    futures_data_dictionary = {
        x: gfp.get_futures_price_preloaded(ticker_head=x)
        for x in cmi.get_tickerhead_list('cme_futures')
    }

    real_vol_output = [
        ops.calc_realized_vol_4options_ticker(
            ticker=x,
            futures_data_dictionary=futures_data_dictionary,
            settle_date=kwargs['settle_date']) for x in options_frame['ticker']
    ]

    delta_vol_output = [
        ops.calc_delta_vol_4ticker(ticker=x,
                                   settle_date=kwargs['settle_date'],
                                   delta_target=0.5)
        for x in options_frame['ticker']
    ]

    volume_interest_output = [
        ops.calc_volume_interest_4ticker(ticker=x,
                                         settle_date=kwargs['settle_date'])
        for x in options_frame['ticker']
    ]

    atm_vol_frame = pd.concat([
        options_frame,
        pd.DataFrame(delta_vol_output),
        pd.DataFrame(volume_interest_output)
    ],
                              axis=1)
    atm_vol_frame['real_vol'] = real_vol_output

    contract_specs_output = [
        cmi.get_contract_specs(x) for x in atm_vol_frame['ticker']
    ]

    atm_vol_frame['ticker_head'] = [
        x['ticker_head'] for x in contract_specs_output
    ]
    atm_vol_frame['ticker_month'] = [
        x['ticker_month_num'] for x in contract_specs_output
    ]
    atm_vol_frame['ticker_year'] = [
        x['ticker_year'] for x in contract_specs_output
    ]

    column_names = atm_vol_frame.columns.tolist()

    ticker_indx = column_names.index('ticker')
    cal_dte_indx = column_names.index('cal_dte')
    tr_dte_indx = column_names.index('tr_dte')
    ticker_head_indx = column_names.index('ticker_head')
    ticker_month_indx = column_names.index('ticker_month')
    ticker_year_indx = column_names.index('ticker_year')
    imp_vol_indx = column_names.index('delta_vol')
    strike_indx = column_names.index('strike')
    theta_indx = column_names.index('theta')
    open_interest_indx = column_names.index('open_interest')
    volume_indx = column_names.index('volume')
    real_vol_indx = column_names.index('real_vol')

    now = dt.datetime.now()
    settle_datetime = cu.convert_doubledate_2datetime(kwargs['settle_date'])

    tuples = [
        tuple([
            x[ticker_indx], x[ticker_head_indx], x[ticker_month_indx],
            x[ticker_year_indx],
            None if np.isnan(x[cal_dte_indx]) else x[cal_dte_indx],
            None if np.isnan(x[tr_dte_indx]) else x[tr_dte_indx],
            None if np.isnan(x[imp_vol_indx]) else 100 * x[imp_vol_indx], 0.5,
            None if np.isnan(x[theta_indx]) else x[theta_indx],
            None if np.isnan(x[strike_indx]) else x[strike_indx],
            None if np.isnan(x[real_vol_indx]) else x[real_vol_indx],
            None if np.isnan(x[open_interest_indx]) else x[open_interest_indx],
            None if np.isnan(x[volume_indx]) else x[volume_indx],
            settle_datetime.date(), now, now
        ]) for x in atm_vol_frame.values
    ]

    column_str = "ticker, ticker_head, ticker_month, ticker_year, " \
                 " cal_dte, tr_dte, imp_vol, delta, theta, strike, " \
                 "close2close_vol20, open_interest, volume, price_date, created_date, last_updated_date"

    insert_str = ("%s, " * len(column_str.split(',')))[:-2]
    final_str = "REPLACE INTO option_ticker_indicators (%s) VALUES (%s)" % (
        column_str, insert_str)

    msu.sql_execute_many_wrapper(final_str=final_str, tuples=tuples, con=con)

    load_pnls_4settle_date(**kwargs)

    if close_connection_before_exit:
        con.close()
コード例 #7
0
def update_options_price_database_from_cme_files_4ticker(**kwargs):

    ticker = kwargs['ticker']

    contract_specs_output = cmi.get_contract_specs(ticker)
    ticker_head = contract_specs_output['ticker_head']
    ticker_month_num = contract_specs_output['ticker_month_num']
    ticker_year = contract_specs_output['ticker_year']

    if 'settle_date' in kwargs.keys():
        settle_date = kwargs['settle_date']
        kwargs['report_date'] = settle_date
    else:
        settle_date = int(time.strftime('%Y%m%d'))
        kwargs['settle_date'] = settle_date
        kwargs['report_date'] = settle_date

    if not exp.is_business_day(double_date=settle_date, reference_tickerhead=ticker_head):
        return

    if 'expiration_date' in kwargs.keys():
        expiration_date = kwargs['expiration_date']
    else:
        expiration_date = exp.get_options_expiration(ticker)
        expiration_date = expiration_date.date()

    settle_datetime = cu.convert_doubledate_2datetime(settle_date)

    if 'cal_dte' in kwargs.keys():
        cal_dte = kwargs['cal_dte']
    else:
        cal_dte = (expiration_date-settle_datetime.date()).days

    if 'tr_dte' in kwargs.keys():
        tr_dte = kwargs['tr_dte']
    else:
        bday_us = CustomBusinessDay(calendar=exp.get_calendar_4ticker_head(ticker_head))
        dts = pd.date_range(start=settle_datetime, end=expiration_date, freq=bday_us)
        tr_dte = len([x for x in dts if x.to_datetime().date() < expiration_date])

    data_vendor_id = 2
    now = dt.datetime.now()
    con = msu.get_my_sql_connection(**kwargs)

    process_output = pco.process_cme_options_4ticker(**kwargs)

    if process_output['success']:
        settle_frame = process_output['settle_frame']
    else:
        if 'con' not in kwargs.keys():
            con.close()
        return

    column_names = settle_frame.columns.tolist()

    option_type_indx = column_names.index('option_type')
    strike_indx = column_names.index('strike')
    settle_indx = column_names.index('settle')
    volume_indx = column_names.index('volume')
    interest_indx = column_names.index('interest')

    tuples = [tuple([data_vendor_id, ticker_head, ticker_month_num, ticker_year,
                     ticker, x[option_type_indx],x[strike_indx],settle_datetime.date(),
                     cal_dte, tr_dte, now, now,
                    None if np.isnan(x[settle_indx]) else x[settle_indx],
                    None if np.isnan(x[volume_indx]) else x[volume_indx],
                    None if np.isnan(x[interest_indx]) else x[interest_indx]]) for x in settle_frame.values]

    column_str = "data_vendor_id, ticker_head, ticker_month, ticker_year, ticker, " \
                 " option_type, strike, price_date, cal_dte, tr_dte, " \
                 " created_date,last_updated_date, close_price, volume, open_interest"

    insert_str = ("%s, " * len(column_str.split(',')))[:-2]
    final_str = "REPLACE INTO daily_option_price (%s) VALUES (%s)" % (column_str, insert_str)
    msu.sql_execute_many_wrapper(final_str=final_str, tuples=tuples, con=con)

    if 'con' not in kwargs.keys():
        con.close()