def load_trades_2strategy(**kwargs): trade_frame = kwargs['trade_frame'] con = msu.get_my_sql_connection(**kwargs) trade_frame['strategy_id'] = [ get_strategy_id_from_alias(alias=trade_frame['alias'][x], con=con) for x in range(len(trade_frame.index)) ] trade_frame['strike_price'] = trade_frame['strike_price'].astype('float64') now_time = dt.datetime.now() now_date = now_time.date() if 'trade_date' in kwargs.keys(): trade_date = cu.convert_doubledate_2datetime(kwargs['trade_date']) else: trade_date = now_date column_str = "ticker, option_type, strike_price, strategy_id, trade_price, trade_quantity, trade_date, instrument, real_tradeQ, created_date, last_updated_date" insert_str = ("%s, " * 11)[:-2] final_str = "INSERT INTO trades (%s) VALUES (%s)" % (column_str, insert_str) column_names = trade_frame.columns.tolist() ticker_indx = column_names.index('ticker') option_type_indx = column_names.index('option_type') strike_price_indx = column_names.index('strike_price') trade_price_indx = column_names.index('trade_price') trade_quantity_indx = column_names.index('trade_quantity') instrument_indx = column_names.index('instrument') real_tradeQ_indx = column_names.index('real_tradeQ') strategy_id_indx = column_names.index('strategy_id') tuples = [ tuple([ x[ticker_indx], None if pd.isnull(x[option_type_indx]) else x[option_type_indx], None if pd.isnull(x[strike_price_indx]) else x[strike_price_indx], x[strategy_id_indx], x[trade_price_indx], x[trade_quantity_indx], trade_date, x[instrument_indx], x[real_tradeQ_indx], now_time, now_time ]) for x in trade_frame.values ] msu.sql_execute_many_wrapper(final_str=final_str, tuples=tuples, con=con) if 'con' not in kwargs.keys(): con.close()
def load_trades_2strategy(**kwargs): trade_frame = kwargs['trade_frame'] con = msu.get_my_sql_connection(**kwargs) trade_frame['strategy_id'] = [get_strategy_id_from_alias(alias=trade_frame['alias'][x],con=con) for x in range(len(trade_frame.index))] trade_frame['strike_price'] = trade_frame['strike_price'].astype('float64') now_time = dt.datetime.now() now_date = now_time.date() if 'trade_date' in kwargs.keys(): trade_date = cu.convert_doubledate_2datetime(kwargs['trade_date']) else: trade_date = now_date column_str = "ticker, option_type, strike_price, strategy_id, trade_price, trade_quantity, trade_date, instrument, real_tradeQ, created_date, last_updated_date" insert_str = ("%s, " * 11)[:-2] final_str = "INSERT INTO trades (%s) VALUES (%s)" % (column_str, insert_str) column_names = trade_frame.columns.tolist() ticker_indx = column_names.index('ticker') option_type_indx = column_names.index('option_type') strike_price_indx = column_names.index('strike_price') trade_price_indx = column_names.index('trade_price') trade_quantity_indx = column_names.index('trade_quantity') instrument_indx = column_names.index('instrument') real_tradeQ_indx = column_names.index('real_tradeQ') strategy_id_indx = column_names.index('strategy_id') tuples = [tuple([x[ticker_indx],x[option_type_indx], None if np.isnan(x[strike_price_indx]) else x[strike_price_indx], x[strategy_id_indx],x[trade_price_indx], x[trade_quantity_indx], trade_date,x[instrument_indx], x[real_tradeQ_indx],now_time,now_time]) for x in trade_frame.values] msu.sql_execute_many_wrapper(final_str=final_str, tuples=tuples, con=con) if 'con' not in kwargs.keys(): con.close()
def move_position_from_strategy_2_strategy(**kwargs): strategy_from = kwargs['strategy_from'] strategy_to = kwargs['strategy_to'] now_time = dt.datetime.now() con = msu.get_my_sql_connection(**kwargs) if 'con' not in kwargs.keys(): kwargs['con'] = con 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 'pricing_date' in kwargs.keys(): pricing_date = kwargs['pricing_date'] else: pricing_date = as_of_date net_position_frame = get_net_position_4strategy_alias(alias=strategy_from, **kwargs) target_strategy_id = get_strategy_id_from_alias(alias=strategy_to, **kwargs) source_strategy_id = get_strategy_id_from_alias(alias=strategy_from, **kwargs) futures_position_frame = net_position_frame[ net_position_frame['instrument'] == 'F'] options_position_frame = net_position_frame[ net_position_frame['instrument'] == 'O'] futures_position_frame['trade_price'] = \ [float(gfp.get_futures_price_4ticker(ticker=x,date_from=pricing_date,date_to=pricing_date,con=con)['close_price'][0]) for x in futures_position_frame['ticker']] if not options_position_frame.empty: options_position_frame['trade_price'] = options_position_frame.apply( lambda row: gop.get_options_price_from_db( ticker=row['ticker'], strike=row['strike_price'], option_type=row['option_type'], con=con, return_nan_if_emptyQ=True, settle_date=pricing_date)['close_price'][0], axis=1) net_position_frame = pd.concat( [futures_position_frame, options_position_frame]) else: net_position_frame = futures_position_frame column_names = net_position_frame.columns.tolist() ticker_indx = column_names.index('ticker') option_type_indx = column_names.index('option_type') strike_price_indx = column_names.index('strike_price') trade_price_indx = column_names.index('trade_price') trade_quantity_indx = column_names.index('qty') instrument_indx = column_names.index('instrument') column_str = "ticker, option_type, strike_price, strategy_id, trade_price, trade_quantity, trade_date, instrument, real_tradeQ, created_date, last_updated_date" insert_str = ("%s, " * 11)[:-2] final_str = "INSERT INTO trades (%s) VALUES (%s)" % (column_str, insert_str) tuples_target = [ tuple([ x[ticker_indx], x[option_type_indx], None if np.isnan(x[strike_price_indx]) else x[strike_price_indx], target_strategy_id, x[trade_price_indx], x[trade_quantity_indx], as_of_date, x[instrument_indx], True, now_time, now_time ]) for x in net_position_frame.values ] msu.sql_execute_many_wrapper(final_str=final_str, tuples=tuples_target, con=con) tuples_source = [ tuple([ x[ticker_indx], x[option_type_indx], None if np.isnan(x[strike_price_indx]) else x[strike_price_indx], source_strategy_id, x[trade_price_indx], -x[trade_quantity_indx], as_of_date, x[instrument_indx], True, now_time, now_time ]) for x in net_position_frame.values ] msu.sql_execute_many_wrapper(final_str=final_str, tuples=tuples_source, con=con) if 'con' not in kwargs.keys(): con.close()
def move_position_from_strategy_2_strategy(**kwargs): strategy_from = kwargs['strategy_from'] strategy_to = kwargs['strategy_to'] now_time = dt.datetime.now() con = msu.get_my_sql_connection(**kwargs) if 'con' not in kwargs.keys(): kwargs['con'] = con 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 net_position_frame = get_net_position_4strategy_alias(alias=strategy_from, **kwargs) target_strategy_id = get_strategy_id_from_alias(alias=strategy_to, **kwargs) source_strategy_id = get_strategy_id_from_alias(alias=strategy_from, **kwargs) futures_position_frame = net_position_frame[net_position_frame['instrument'] == 'F'] options_position_frame = net_position_frame[net_position_frame['instrument'] == 'O'] futures_position_frame['trade_price'] = \ [float(gfp.get_futures_price_4ticker(ticker=x,date_from=as_of_date,date_to=as_of_date,con=con)['close_price'][0]) for x in futures_position_frame['ticker']] if not options_position_frame.empty: options_position_frame['trade_price'] = options_position_frame.apply(lambda row: gop.get_options_price_from_db(ticker=row['ticker'], strike=row['strike_price'], option_type=row['option_type'],con=con, return_nan_if_emptyQ=True, settle_date=as_of_date)['close_price'][0], axis=1) net_position_frame = pd.concat([futures_position_frame,options_position_frame]) else: net_position_frame = futures_position_frame column_names = net_position_frame.columns.tolist() ticker_indx = column_names.index('ticker') option_type_indx = column_names.index('option_type') strike_price_indx = column_names.index('strike_price') trade_price_indx = column_names.index('trade_price') trade_quantity_indx = column_names.index('qty') instrument_indx = column_names.index('instrument') column_str = "ticker, option_type, strike_price, strategy_id, trade_price, trade_quantity, trade_date, instrument, real_tradeQ, created_date, last_updated_date" insert_str = ("%s, " * 11)[:-2] final_str = "INSERT INTO trades (%s) VALUES (%s)" % (column_str, insert_str) tuples_target = [tuple([x[ticker_indx],x[option_type_indx], None if np.isnan(x[strike_price_indx]) else x[strike_price_indx], target_strategy_id, x[trade_price_indx], x[trade_quantity_indx], as_of_date,x[instrument_indx], True,now_time,now_time]) for x in net_position_frame.values] msu.sql_execute_many_wrapper(final_str=final_str, tuples=tuples_target, con=con) tuples_source = [tuple([x[ticker_indx],x[option_type_indx], None if np.isnan(x[strike_price_indx]) else x[strike_price_indx], source_strategy_id, x[trade_price_indx], -x[trade_quantity_indx], as_of_date,x[instrument_indx], True,now_time,now_time]) for x in net_position_frame.values] msu.sql_execute_many_wrapper(final_str=final_str, tuples=tuples_source, con=con) if 'con' not in kwargs.keys(): con.close()
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()
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()
def load_price_data_4ticker(load_price_data_input): ticker = load_price_data_input['ticker'] expiration_date = load_price_data_input['expiration_date'] data_vendor_id = load_price_data_input['data_vendor_id'] symbol_id = load_price_data_input['symbol_id'] quandl_input = {'ticker': ticker} if 'date_to' in load_price_data_input.keys(): quandl_input['date_to'] = load_price_data_input['date_to'] if 'date_from' in load_price_data_input.keys() and load_price_data_input['date_from'] is not None: quandl_input['date_from'] = load_price_data_input['date_from'] quandl_out = gdq.get_daily_historic_data_quandl(**quandl_input) if not quandl_out['success']: return price_data = quandl_out['data_out'] if price_data.empty: print('Empty Results For ' + ticker) return contract_specs_output = cmi.get_contract_specs(ticker) if contract_specs_output['ticker_head'] == 'JY': price_multiplier = 10 else: price_multiplier = 1 bday_us = CustomBusinessDay(calendar=exp.get_calendar_4ticker_head(contract_specs_output['ticker_head'])) dts = pd.date_range(start=price_data.index[0], end=expiration_date, freq=bday_us) dts = [x.date() for x in dts] now = datetime.datetime.now() price_data['price_date'] = pd.Series(price_data.index, index=price_data.index) column_names = price_data.columns.tolist() 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('Open Interest') date_indx = column_names.index('price_date') tuples = [tuple([data_vendor_id, symbol_id, contract_specs_output['ticker_head'], contract_specs_output['ticker_month_num'], x[date_indx].to_datetime().date(), (expiration_date-x[date_indx].to_datetime().date()).days, len([y for y in dts if y > x[date_indx].to_datetime().date()]), now, now, None if np.isnan(x[open_indx]) else price_multiplier*x[open_indx], None if np.isnan(x[high_indx]) else price_multiplier*x[high_indx], None if np.isnan(x[low_indx]) else price_multiplier*x[low_indx], None if np.isnan(x[settle_indx]) else price_multiplier*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 price_data.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) con = msu.get_my_sql_connection(**load_price_data_input) msu.sql_execute_many_wrapper(final_str=final_str, tuples=tuples, con=con) if 'con' not in load_price_data_input.keys(): con.close()
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()
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()
def update_options_greek_database_4ticker(**kwargs): if 'con' not in kwargs.keys(): con = msu.get_my_sql_connection(**kwargs) kwargs['con'] = con close_connection_before_exit = True else: close_connection_before_exit = False con = kwargs['con'] ticker = kwargs['ticker'] print(ticker) option_greeks = osc.cal_greeks_4option_maturity(**kwargs) if option_greeks.empty: return column_names = option_greeks.columns.tolist() option_type_indx = column_names.index('option_type') strike_indx = column_names.index('strike') settle_indx = column_names.index('close_price') volume_indx = column_names.index('volume') interest_indx = column_names.index('open_interest') delta_indx = column_names.index('delta') gamma_indx = column_names.index('gamma') implied_vol_indx = column_names.index('implied_vol') theta_indx = column_names.index('theta') vega_indx = column_names.index('vega') cal_dte = int(option_greeks['cal_dte'].iloc[0]) tr_dte = int(option_greeks['tr_dte'].iloc[0]) data_vendor_id = 2 now = dt.datetime.now() settle_datetime = cu.convert_doubledate_2datetime(kwargs['settle_date']) contract_specs_output = cmi.get_contract_specs(ticker) x = option_greeks.values[0] option_greeks['close_price'] = option_greeks['close_price'].astype( 'float64') option_greeks['volume'] = option_greeks['volume'].astype('int_') option_greeks['open_interest'] = option_greeks['open_interest'].astype( 'int_') tuples = [ tuple([ data_vendor_id, contract_specs_output['ticker_head'], contract_specs_output['ticker_month_num'], contract_specs_output['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[implied_vol_indx]) else x[implied_vol_indx], None if np.isnan(x[delta_indx]) else x[delta_indx], None if np.isnan(x[gamma_indx]) else x[gamma_indx], None if np.isnan(x[theta_indx]) else x[theta_indx], None if np.isnan(x[vega_indx]) else x[vega_indx], None if np.isnan(x[volume_indx]) else int(x[volume_indx]), None if np.isnan(x[interest_indx]) else int(x[interest_indx]) ]) for x in option_greeks.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, imp_vol, delta, gamma, theta, vega, " \ "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 close_connection_before_exit: con.close()
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()
def update_options_greek_database_4ticker(**kwargs): if 'con' not in kwargs.keys(): con = msu.get_my_sql_connection(**kwargs) kwargs['con'] = con close_connection_before_exit = True else: close_connection_before_exit = False con = kwargs['con'] ticker = kwargs['ticker'] #print(ticker) option_greeks = osc.cal_greeks_4option_maturity(**kwargs) if option_greeks.empty: return column_names = option_greeks.columns.tolist() option_type_indx = column_names.index('option_type') strike_indx = column_names.index('strike') settle_indx = column_names.index('close_price') volume_indx = column_names.index('volume') interest_indx = column_names.index('open_interest') delta_indx = column_names.index('delta') gamma_indx = column_names.index('gamma') implied_vol_indx = column_names.index('implied_vol') theta_indx = column_names.index('theta') vega_indx = column_names.index('vega') cal_dte = int(option_greeks['cal_dte'].iloc[0]) tr_dte = int(option_greeks['tr_dte'].iloc[0]) data_vendor_id = 2 now = dt.datetime.now() settle_datetime = cu.convert_doubledate_2datetime(kwargs['settle_date']) contract_specs_output = cmi.get_contract_specs(ticker) x = option_greeks.values[0] option_greeks['close_price'] = option_greeks['close_price'].astype('float64') option_greeks['volume'] = option_greeks['volume'].astype('int_') option_greeks['open_interest'] = option_greeks['open_interest'].astype('int_') tuples = [tuple([data_vendor_id, contract_specs_output['ticker_head'], contract_specs_output['ticker_month_num'], contract_specs_output['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[implied_vol_indx]) else x[implied_vol_indx], None if np.isnan(x[delta_indx]) else x[delta_indx], None if np.isnan(x[gamma_indx]) else x[gamma_indx], None if np.isnan(x[theta_indx]) else x[theta_indx], None if np.isnan(x[vega_indx]) else x[vega_indx], None if np.isnan(x[volume_indx]) else int(x[volume_indx]), None if np.isnan(x[interest_indx]) else int(x[interest_indx])]) for x in option_greeks.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, imp_vol, delta, gamma, theta, vega, " \ "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 close_connection_before_exit: con.close()