def calc_delta_vol_4ticker(**kwargs): delta_target = kwargs['delta_target'] delta_max_deviation = 0.15 skew_output = gop.get_options_price_from_db(column_names=['delta', 'imp_vol', 'strike', 'theta', 'cal_dte', 'tr_dte'], **kwargs) if skew_output.empty: tr_dte = np.NaN cal_dte = np.NaN else: tr_dte = skew_output['tr_dte'][0] cal_dte = skew_output['cal_dte'][0] output_dict = {'delta_vol': np.NaN, 'strike': np.NaN, 'theta': np.NaN, 'cal_dte': cal_dte, 'tr_dte': tr_dte} skew_output = skew_output[(skew_output['imp_vol'].notnull())] delta_band = [delta_target*x for x in [1-delta_max_deviation, 1+delta_max_deviation]] skew_output = skew_output[(skew_output['delta'] <= max(delta_band)) & (skew_output['delta'] >= min(delta_band))] if skew_output.empty: return output_dict skew_output['delta_diff'] = abs(skew_output['delta']-delta_target) skew_output.sort('delta_diff', ascending=True, inplace=True) output_dict['delta_vol'] = skew_output['imp_vol'].iloc[0] output_dict['strike'] = skew_output['strike'].iloc[0] output_dict['theta'] = skew_output['theta'].iloc[0] return output_dict
def get_greeks_4strategy_4date(**kwargs): con = msu.get_my_sql_connection(**kwargs) alias = kwargs['alias'] if 'as_of_date' in kwargs.keys(): as_of_date = kwargs['as_of_date'] else: as_of_date = exp.doubledate_shift_bus_days() position_frame = tas.get_net_position_4strategy_alias(alias=alias,as_of_date=as_of_date,con=con) options_frame = position_frame[position_frame['instrument'] == 'O'] if options_frame.empty: if 'con' not in kwargs.keys(): con.close() return {'ticker_portfolio': pd.DataFrame(columns=['total_oev', 'theta','dollar_vega','ticker']), 'strike_portfolio': pd.DataFrame()} unique_ticker_list = options_frame['ticker'].unique() result_list = [] contract_specs_output_list = [cmi.get_contract_specs(x) for x in unique_ticker_list] contract_multiplier_list = [cmi.contract_multiplier[x['ticker_head']] for x in contract_specs_output_list] for i in range(len(unique_ticker_list)): skew_output = gop.get_options_price_from_db(ticker=unique_ticker_list[i], settle_date=as_of_date, column_names=['option_type', 'strike', 'theta', 'vega', 'delta']) skew_output.reset_index(drop=True,inplace=True) skew_output['delta_diff'] = abs(skew_output['delta']-0.5) atm_point = skew_output.loc[skew_output['delta_diff'].idxmin()] skew_output.rename(columns={'strike': 'strike_price'}, inplace=True) merged_data = pd.merge(options_frame[options_frame['ticker'] == unique_ticker_list[i]], skew_output, how='left', on=['option_type','strike_price']) merged_data['oev'] = merged_data['vega']/atm_point['vega'] merged_data['total_oev'] = merged_data['oev']*merged_data['qty'] merged_data['dollar_theta'] = merged_data['theta']*merged_data['qty']*contract_multiplier_list[i] merged_data['dollar_vega'] = merged_data['vega']*merged_data['qty']*contract_multiplier_list[i]/100 result_list.append(merged_data) strike_portfolio = pd.concat(result_list) grouped = strike_portfolio.groupby('ticker') ticker_portfolio = pd.DataFrame() ticker_portfolio['ticker'] = (grouped['ticker'].first()).values ticker_portfolio['total_oev'] = (grouped['total_oev'].sum()).values ticker_portfolio['theta'] = (grouped['dollar_theta'].sum()).values ticker_portfolio['dollar_vega'] = (grouped['dollar_vega'].sum()).values if 'con' not in kwargs.keys(): con.close() return {'ticker_portfolio': ticker_portfolio, 'strike_portfolio': strike_portfolio }
def cal_greeks_4option_maturity(**kwargs): option_prices = gop.get_options_price_from_db(**kwargs) if option_prices.empty: return pd.DataFrame() option_prices = option_prices[option_prices['strike'] > 0] option_prices.reset_index(drop=True, inplace=True) contract_specs_out = cmi.get_contract_specs(kwargs['ticker']) exercise_type = cmi.get_option_exercise_type(**contract_specs_out) underlying_ticker = oput.get_option_underlying(**kwargs) futures_price_output = gfp.get_futures_price_preloaded( ticker=underlying_ticker, settle_date=kwargs['settle_date']) if futures_price_output.empty: return pd.DataFrame() underlying_price = futures_price_output['close_price'].iloc[0] expiration_datetime = exp.get_expiration_from_db(instrument='options', **kwargs) expiration_date = int(expiration_datetime.strftime('%Y%m%d')) interest_rate = grfs.get_simple_rate( as_of_date=kwargs['settle_date'], date_to=expiration_date)['rate_output'] option_greeks = [ qom.get_option_greeks(underlying=underlying_price, option_price=float( option_prices['close_price'].iloc[x]), strike=float(option_prices['strike'].iloc[x]), risk_free_rate=interest_rate, expiration_date=expiration_date, calculation_date=kwargs['settle_date'], option_type=option_prices['option_type'].iloc[x], exercise_type=exercise_type) for x in range(len(option_prices.index)) ] greek_frame = pd.DataFrame(option_greeks) return pd.concat([ greek_frame[['delta', 'gamma', 'implied_vol', 'theta', 'vega']], option_prices ], axis=1)
def calc_intrday_pnl_from_prices(**kwargs): if 'as_of_date' in kwargs.keys(): as_of_date = kwargs['as_of_date'] else: as_of_date = exp.doubledate_shift_bus_days() net_position_frame = tas.get_net_position_4strategy_alias(alias=kwargs['alias'], as_of_date=as_of_date) net_position_frame['ticker_head'] = [cmi.get_contract_specs(x)['ticker_head'] for x in net_position_frame['ticker']] net_position_frame['contract_multiplier'] = [cmi.contract_multiplier[x] for x in net_position_frame['ticker_head']] con = msu.get_my_sql_connection(**kwargs) option_frame = net_position_frame[net_position_frame['instrument'] == 'O'] #option_frame = option_frame[(option_frame['strike_price'] == 54)|(option_frame['strike_price'] == 60)] #option_frame = option_frame[option_frame['strike_price'] == 112] #option_frame['qty'].loc[option_frame['ticker']=='LNV2016'] = -20 option_frame['close_price'] = [gop.get_options_price_from_db(ticker=option_frame['ticker'].iloc[x], strike=option_frame['strike_price'].iloc[x], option_type=option_frame['option_type'].iloc[x], con=con,settle_date=as_of_date)['close_price'][0] for x in range(len(option_frame.index))] structure_quantity = abs(option_frame['qty']).unique()[0] structure_multiplier = option_frame['contract_multiplier'].unique()[0] structure_price = sum(option_frame['close_price']*option_frame['qty'])/structure_quantity if structure_price<0: structure_quantity = -structure_quantity structure_price = -structure_price structure_pnl = structure_quantity*structure_multiplier*(kwargs['structure_price']-structure_price) futures_frame = net_position_frame[net_position_frame['instrument'] == 'F'] futures_frame['close_price'] = [gfp.get_futures_price_preloaded(ticker=x, settle_date=as_of_date)['close_price'].iloc[0] for x in futures_frame['ticker']] futures_frame['intraday_price'] = [kwargs[x] for x in futures_frame['ticker']] futures_frame['intraday_pnl'] = (futures_frame['intraday_price']-futures_frame['close_price'])*futures_frame['qty']*futures_frame['contract_multiplier'] if 'con' not in kwargs.keys(): con.close() return {'structure_pnl': structure_pnl, 'futures_pnl': futures_frame['intraday_pnl'].sum(),'structure_settle': structure_price}
def calc_intraday_structure_pnl_from_prices(**kwargs): if 'as_of_date' in kwargs.keys(): as_of_date = kwargs['as_of_date'] else: as_of_date = exp.doubledate_shift_bus_days() con = msu.get_my_sql_connection(**kwargs) structure_type = kwargs['structure_type'] structure_price = kwargs['structure_price'] ticker_list = kwargs['ticker_list'] strike_list = kwargs['strike_list'] underlying_price_list = kwargs['underlying_price_list'] qty = kwargs['qty'] if structure_type == 'straddle_spread': option_frame = pd.DataFrame.from_items([('ticker', [ticker_list[0], ticker_list[0], ticker_list[1], ticker_list[1]]), ('option_type', ['C', 'P', 'C', 'P']), ('strike_price', [strike_list[0], strike_list[0], strike_list[1],strike_list[1]]), ('qty', [-1, -1, 1, 1])]) option_price_output = [gop.get_options_price_from_db(ticker=option_frame['ticker'].iloc[x], strike=option_frame['strike_price'].iloc[x], option_type=option_frame['option_type'].iloc[x], con=con,settle_date=as_of_date,column_names=['close_price','delta']) for x in range(len(option_frame.index))] option_frame['delta'] = [option_price_output[x]['delta'][0] for x in range(len(option_frame.index))] option_frame['close_price'] = [option_price_output[x]['close_price'][0] for x in range(len(option_frame.index))] option_frame['PQ'] = option_frame['close_price']*option_frame['qty'] option_frame['signed_delta'] = option_frame['delta']*option_frame['qty'] delta_list = [option_frame[option_frame['ticker'] == x]['signed_delta'].sum() for x in ticker_list] ticker_head = cmi.get_contract_specs(ticker_list[0])['ticker_head'] contract_multiplier = cmi.contract_multiplier[ticker_head] structure_price_yesterday = option_frame['PQ'].sum() structure_pnl = qty*(structure_price-structure_price_yesterday)*contract_multiplier underlying_ticker_list = [oputil.get_option_underlying(ticker=x) for x in ticker_list] underlying_price_list_yesterday = [gfp.get_futures_price_preloaded(ticker=x, settle_date=as_of_date)['close_price'].iloc[0] for x in underlying_ticker_list] delta_pnl = contract_multiplier*sum([-delta_list[x]*qty*(underlying_price_list[x]-underlying_price_list_yesterday[x]) for x in range(len(delta_list))]) return {'total_pnl': structure_pnl+delta_pnl, 'structure_pnl': structure_pnl, 'delta_pnl': delta_pnl, 'structure_price_yesterday': structure_price_yesterday }
def calc_delta_vol_4ticker(**kwargs): delta_target = kwargs['delta_target'] del kwargs['delta_target'] delta_max_deviation = 0.15 skew_output = gop.get_options_price_from_db(column_names=['delta', 'imp_vol', 'strike', 'theta', 'cal_dte', 'tr_dte'], **kwargs) if skew_output.empty: tr_dte = np.NaN cal_dte = np.NaN else: tr_dte = skew_output['tr_dte'][0] cal_dte = skew_output['cal_dte'][0] output_dict = {'delta_vol': np.NaN, 'strike': np.NaN, 'theta': np.NaN, 'cal_dte': cal_dte, 'tr_dte': tr_dte} skew_output = skew_output[(skew_output['imp_vol'].notnull())] delta_band = [delta_target*x for x in [1-delta_max_deviation, 1+delta_max_deviation]] skew_output_select = skew_output[(skew_output['delta'] <= max(delta_band)) & (skew_output['delta'] >= min(delta_band))] if skew_output_select.empty: delta_target = -(1-delta_target) delta_band = [delta_target*x for x in [1-delta_max_deviation, 1+delta_max_deviation]] skew_output_select = skew_output[(skew_output['delta'] <= max(delta_band)) & (skew_output['delta'] >= min(delta_band))] if skew_output_select.empty: return output_dict skew_output_select['delta_diff'] = abs(skew_output_select['delta']-delta_target) skew_output_select.sort_values('delta_diff', ascending=True, inplace=True) output_dict['delta_vol'] = skew_output_select['imp_vol'].iloc[0] output_dict['strike'] = skew_output_select['strike'].iloc[0] output_dict['theta'] = skew_output_select['theta'].iloc[0] return output_dict
def cal_greeks_4option_maturity(**kwargs): option_prices = gop.get_options_price_from_db(**kwargs) if option_prices.empty: return pd.DataFrame() contract_specs_out = cmi.get_contract_specs(kwargs['ticker']) exercise_type = cmi.get_option_exercise_type(**contract_specs_out) underlying_ticker = oput.get_option_underlying(**kwargs) futures_price_output = gfp.get_futures_price_preloaded(ticker=underlying_ticker, settle_date=kwargs['settle_date']) if futures_price_output.empty: return pd.DataFrame() underlying_price = futures_price_output['close_price'].iloc[0] expiration_datetime = exp.get_expiration_from_db(instrument='options', **kwargs) expiration_date = int(expiration_datetime.strftime('%Y%m%d')) interest_rate = grfs.get_simple_rate(as_of_date=kwargs['settle_date'], date_to=expiration_date)['rate_output'] #print(kwargs['settle_date']) #print(expiration_date) option_greeks = [qom.get_option_greeks(underlying=underlying_price, option_price=float(option_prices['close_price'].iloc[x]), strike=float(option_prices['strike'].iloc[x]), risk_free_rate=interest_rate, expiration_date=expiration_date, calculation_date=kwargs['settle_date'], option_type=option_prices['option_type'].iloc[x], exercise_type=exercise_type) for x in range(len(option_prices.index))] greek_frame = pd.DataFrame(option_greeks) return pd.concat([greek_frame[['delta', 'gamma', 'implied_vol', 'theta', 'vega']], option_prices], axis=1)
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 get_strategy_pnl_4day(**kwargs): alias = kwargs['alias'] pnl_date = kwargs['pnl_date'] if 'shift_in_days' in kwargs.keys(): shift_in_days = kwargs['shift_in_days'] else: shift_in_days = 1 if 'broker' in kwargs.keys(): broker = kwargs['broker'] else: broker = 'abn' #print(pnl_date) pnl_datetime = cu.convert_doubledate_2datetime(pnl_date) con = msu.get_my_sql_connection(**kwargs) if 'trades_frame' in kwargs.keys(): trades_frame = kwargs['trades_frame'] ticker_head_list = [ cmi.get_contract_specs(x)['ticker_head'] for x in trades_frame['ticker'] ] else: trades_frame = ts.get_trades_4strategy_alias(alias=alias, con=con) ticker_head_list = [ cmi.get_contract_specs(x)['ticker_head'] for x in trades_frame['ticker'] ] trades_frame['contract_multiplier'] = [ cmi.contract_multiplier[x] for x in ticker_head_list ] trades_frame['t_cost'] = [ cmi.get_t_cost(ticker_head=x, broker=broker) for x in ticker_head_list ] trades_frame['ticker_head'] = ticker_head_list option_indx = trades_frame['instrument'] == 'O' trades_frame['generalized_ticker'] = trades_frame['ticker'] trades_frame['generalized_ticker'][option_indx] = trades_frame['ticker'][option_indx] + '-' + \ trades_frame['option_type'][option_indx] + '-' + \ trades_frame['strike_price'][option_indx].astype(str) position_frame_aux = trades_frame[ trades_frame['trade_date'] < pnl_datetime] intraday_frame_aux = trades_frame[trades_frame['trade_date'] == pnl_datetime] grouped = position_frame_aux.groupby(['generalized_ticker']) net_position = pd.DataFrame() net_position['qty'] = grouped['trade_quantity'].sum() net_position['generalized_ticker'] = grouped['generalized_ticker'].first() net_position = net_position[abs(net_position['qty']) > 0.1] useful_generalized_ticker_list = list( set(net_position['generalized_ticker'].values) | set(intraday_frame_aux['generalized_ticker'].unique())) trades_frame = trades_frame[trades_frame['generalized_ticker'].isin( useful_generalized_ticker_list)] if 'futures_data_dictionary' in kwargs.keys(): futures_data_dictionary = kwargs['futures_data_dictionary'] else: unique_ticker_head_list = list(set(ticker_head_list)) futures_data_dictionary = { x: gfp.get_futures_price_preloaded(ticker_head=x) for x in unique_ticker_head_list } pnl_date_1 = exp.doubledate_shift_bus_days(double_date=pnl_date, shift_in_days=shift_in_days) underlying_frame = trades_frame[trades_frame['instrument'] == 'F'] option_frame = trades_frame[trades_frame['instrument'] == 'O'] futures_price_out_1 = [ gfp.get_futures_price_preloaded( ticker=x, futures_data_dictionary=futures_data_dictionary, settle_date=pnl_date_1) for x in underlying_frame['ticker'] ] futures_price_out = [ gfp.get_futures_price_preloaded( ticker=x, futures_data_dictionary=futures_data_dictionary, settle_date=pnl_date) for x in underlying_frame['ticker'] ] underlying_frame['price_1'] = [ np.NaN if x.empty else x['close_price'].values[0] for x in futures_price_out_1 ] underlying_frame['price'] = [ np.NaN if x.empty else x['close_price'].values[0] for x in futures_price_out ] #underlying_frame['price_1'] = [gfp.get_futures_price_preloaded(ticker=x, # futures_data_dictionary=futures_data_dictionary, # settle_date=pnl_date_1)['close_price'].values[0] for x in underlying_frame['ticker']] #underlying_frame['price'] = [gfp.get_futures_price_preloaded(ticker=x, # futures_data_dictionary=futures_data_dictionary, # settle_date=pnl_date)['close_price'].values[0] for x in underlying_frame['ticker']] option_frame['price_1'] = [ gop.get_options_price_from_db( ticker=option_frame['ticker'].iloc[x], strike=option_frame['strike_price'].iloc[x], option_type=option_frame['option_type'].iloc[x], con=con, settle_date=pnl_date_1, return_nan_if_emptyQ=True)['close_price'].values[0] for x in range(len(option_frame.index)) ] option_frame['price'] = [ gop.get_options_price_from_db( ticker=option_frame['ticker'].iloc[x], strike=option_frame['strike_price'].iloc[x], option_type=option_frame['option_type'].iloc[x], con=con, settle_date=pnl_date, return_nan_if_emptyQ=True)['close_price'].values[0] for x in range(len(option_frame.index)) ] trades_frame = pd.concat([option_frame, underlying_frame]) position_frame = trades_frame[trades_frame['trade_date'] < pnl_datetime] nan_price_q = position_frame['price'].isnull().values.any() intraday_frame = trades_frame[trades_frame['trade_date'] == pnl_datetime] position_pnl_per_ticker = pd.DataFrame(columns=['ticker', 'pnl_position']) intraday_pnl_per_ticker = pd.DataFrame(columns=['ticker', 'pnl_intraday']) position_pnl_per_tickerhead = pd.DataFrame( columns=['ticker_head', 'pnl_position']) intraday_pnl_per_tickerhead = pd.DataFrame( columns=['ticker_head', 'pnl_intraday']) if len(position_frame) == 0: position_pnl = 0 else: position_frame['pnl'] = position_frame['contract_multiplier']*\ position_frame['trade_quantity']*\ (position_frame['price']-position_frame['price_1']) position_pnl = position_frame['pnl'].sum() position_grouped_per_ticker = position_frame.groupby('ticker') position_grouped_per_tickerhead = position_frame.groupby('ticker_head') position_pnl_per_ticker['pnl_position'] = ( position_grouped_per_ticker['pnl'].sum()).values position_pnl_per_ticker['ticker'] = ( position_grouped_per_ticker['ticker'].first()).values position_pnl_per_tickerhead['pnl_position'] = ( position_grouped_per_tickerhead['pnl'].sum()).values position_pnl_per_tickerhead['ticker_head'] = ( position_grouped_per_tickerhead['ticker_head'].first()).values if len(intraday_frame) == 0: intraday_pnl = 0 t_cost = 0 else: intraday_frame['pnl'] = intraday_frame['contract_multiplier']*\ intraday_frame['trade_quantity']*\ (intraday_frame['price']-intraday_frame['trade_price']) intraday_frame['pnl_wtcost'] = intraday_frame['pnl'] - abs( intraday_frame['trade_quantity'] * intraday_frame['t_cost']) intraday_pnl = intraday_frame['pnl'].sum() t_cost = (abs(intraday_frame['trade_quantity'] * intraday_frame['t_cost'])).sum() intraday_grouped_per_ticker = intraday_frame.groupby('ticker') intraday_grouped_per_tickerhead = intraday_frame.groupby('ticker_head') intraday_pnl_per_ticker['pnl_intraday'] = ( intraday_grouped_per_ticker['pnl_wtcost'].sum()).values intraday_pnl_per_ticker['ticker'] = ( intraday_grouped_per_ticker['ticker'].first()).values intraday_pnl_per_tickerhead['pnl_intraday'] = ( intraday_grouped_per_tickerhead['pnl_wtcost'].sum()).values intraday_pnl_per_tickerhead['ticker_head'] = ( intraday_grouped_per_tickerhead['ticker_head'].first()).values pnl_per_ticker = pd.merge(position_pnl_per_ticker, intraday_pnl_per_ticker, how='outer', on='ticker') intraday_zero_indx = [ x not in intraday_pnl_per_ticker['ticker'].values for x in pnl_per_ticker['ticker'] ] position_zero_indx = [ x not in position_pnl_per_ticker['ticker'].values for x in pnl_per_ticker['ticker'] ] pnl_per_ticker['pnl_position'][position_zero_indx] = 0 pnl_per_ticker['pnl_intraday'][intraday_zero_indx] = 0 pnl_per_ticker['pnl_total'] = pnl_per_ticker[ 'pnl_position'] + pnl_per_ticker['pnl_intraday'] pnl_per_ticker.set_index('ticker', drop=True, inplace=True) pnl_per_tickerhead = pd.merge(position_pnl_per_tickerhead, intraday_pnl_per_tickerhead, how='outer', on='ticker_head') intraday_zero_indx = [ x not in intraday_pnl_per_tickerhead['ticker_head'].values for x in pnl_per_tickerhead['ticker_head'] ] position_zero_indx = [ x not in position_pnl_per_tickerhead['ticker_head'].values for x in pnl_per_tickerhead['ticker_head'] ] pnl_per_tickerhead['pnl_position'][position_zero_indx] = 0 pnl_per_tickerhead['pnl_intraday'][intraday_zero_indx] = 0 pnl_per_tickerhead['pnl_total'] = pnl_per_tickerhead[ 'pnl_position'] + pnl_per_tickerhead['pnl_intraday'] pnl_per_tickerhead.set_index('ticker_head', drop=True, inplace=True) if 'con' not in kwargs.keys(): con.close() return { 'total_pnl': int(position_pnl + intraday_pnl - t_cost), 'position_pnl': int(position_pnl), 'intraday_pnl': int(intraday_pnl), 't_cost': int(t_cost), 'nan_price_q': nan_price_q, 'pnl_per_ticker': pnl_per_ticker, 'pnl_per_tickerhead': pnl_per_tickerhead }
def get_hedge_4strategy(**kwargs): con = msu.get_my_sql_connection(**kwargs) current_date = cu.get_doubledate() settle_price_date = exp.doubledate_shift_bus_days(double_date=current_date, shift_in_days=1) position_frame = tas.get_net_position_4strategy_alias(alias=kwargs['alias'],as_of_date=current_date,con=con) intraday_price_frame = gip.get_cme_direct_prices() intraday_price_frame.rename(columns={'ticker': 'underlying_ticker'},inplace=True) intraday_price_frame['ticker_head'] = [cmi.get_contract_specs(x)['ticker_head'] for x in intraday_price_frame['underlying_ticker']] intraday_price_frame['mid_price'] = (intraday_price_frame['bid_price'] + intraday_price_frame['ask_price'])/2 intraday_price_frame['mid_price'] = [tfl.convert_trade_price_from_cme_direct(ticker_head=intraday_price_frame['ticker_head'].iloc[x], price=intraday_price_frame['mid_price'].iloc[x]) for x in range(len(intraday_price_frame.index))] options_frame = position_frame[position_frame['instrument'] == 'O'] futures_frame = position_frame[position_frame['instrument'] == 'F'] if options_frame.empty: futures_frame.rename(columns={'ticker': 'underlying_ticker', 'qty': 'underlying_delta'},inplace=True) futures_frame = futures_frame[['underlying_ticker', 'underlying_delta']] net_position = pd.merge(futures_frame, intraday_price_frame, how='left', on='underlying_ticker') net_position['hedge_price'] = net_position['mid_price'] net_position['hedge'] = -net_position['underlying_delta'] return net_position imp_vol_list = [gop.get_options_price_from_db(ticker=options_frame['ticker'].iloc[x], settle_date=settle_price_date, strike=options_frame['strike_price'].iloc[x], column_names=['imp_vol'], con=con)['imp_vol'] for x in range(len(options_frame.index))] options_frame['imp_vol'] = [imp_vol_list[x][1] if (np.isnan(imp_vol_list[x][0]) and len(imp_vol_list[x]) > 1) else imp_vol_list[x][0] for x in range(len(options_frame.index))] options_frame['underlying_ticker'] = [omu.get_option_underlying(ticker=x) for x in options_frame['ticker']] options_frame = pd.merge(options_frame, intraday_price_frame, how='left', on='underlying_ticker') options_frame['ticker_head'] = [cmi.get_contract_specs(x)['ticker_head'] for x in options_frame['ticker']] options_frame['exercise_type'] = [cmi.get_option_exercise_type(ticker_head=x) for x in options_frame['ticker_head']] options_frame['strike_price'] = options_frame['strike_price'].astype('float64') options_frame['delta'] = [omu.option_model_wrapper(ticker=options_frame['ticker'].iloc[x], calculation_date=current_date, interest_rate_date=settle_price_date, underlying=options_frame['mid_price'].iloc[x], strike=options_frame['strike_price'].iloc[x], implied_vol=options_frame['imp_vol'].iloc[x], option_type=options_frame['option_type'].iloc[x], exercise_type=options_frame['exercise_type'].iloc[x], con=con)['delta'] for x in range(len(options_frame.index))] options_frame['total_delta'] = options_frame['qty']*options_frame['delta'] grouped = options_frame.groupby('underlying_ticker') net_position = pd.DataFrame() net_position['underlying_ticker'] = (grouped['underlying_ticker'].first()).values net_position['hedge_price'] = (grouped['mid_price'].first()).values net_position['option_delta'] = (grouped['total_delta'].sum()).values net_position['option_delta'] = net_position['option_delta'].round(2) if futures_frame.empty: net_position['total_delta'] = net_position['option_delta'] else: futures_frame.rename(columns={'ticker': 'underlying_ticker', 'qty': 'underlying_delta'},inplace=True) futures_frame = futures_frame[['underlying_ticker', 'underlying_delta']] isinOptions = futures_frame['underlying_ticker'].isin(net_position['underlying_ticker']) futures_frame_w_options = futures_frame[isinOptions] futures_frame_wo_options = futures_frame[~isinOptions] if futures_frame_w_options.empty: net_position['underlying_delta'] = 0 net_position['total_delta'] = net_position['option_delta'] else: net_position = pd.merge(net_position, futures_frame_w_options, how='outer', on='underlying_ticker') net_position['total_delta'] = net_position['option_delta']+net_position['underlying_delta'] if not futures_frame_wo_options.empty: net_position_futures = pd.merge(futures_frame_wo_options, intraday_price_frame, how='left', on='underlying_ticker') net_position_futures['hedge_price'] = net_position_futures['mid_price'] net_position_futures['option_delta'] = 0 net_position_futures['total_delta'] = net_position_futures['underlying_delta'] net_position = pd.concat([net_position,net_position_futures[['underlying_ticker','hedge_price','option_delta','underlying_delta','total_delta']]]) net_position['hedge'] = -net_position['total_delta'] if 'con' not in kwargs.keys(): con.close() return net_position
def get_strategy_pnl_4day(**kwargs): alias = kwargs['alias'] pnl_date = kwargs['pnl_date'] #print(pnl_date) pnl_datetime = cu.convert_doubledate_2datetime(pnl_date) con = msu.get_my_sql_connection(**kwargs) if 'trades_frame' in kwargs.keys(): trades_frame = kwargs['trades_frame'] ticker_head_list = [cmi.get_contract_specs(x)['ticker_head'] for x in trades_frame['ticker']] else: trades_frame = ts.get_trades_4strategy_alias(alias=alias,con=con) ticker_head_list = [cmi.get_contract_specs(x)['ticker_head'] for x in trades_frame['ticker']] trades_frame['contract_multiplier'] = [cmi.contract_multiplier[x] for x in ticker_head_list] trades_frame['t_cost'] = [cmi.t_cost[x] for x in ticker_head_list] trades_frame['ticker_head'] = ticker_head_list if 'futures_data_dictionary' in kwargs.keys(): futures_data_dictionary = kwargs['futures_data_dictionary'] else: unique_ticker_head_list = list(set(ticker_head_list)) futures_data_dictionary = {x: gfp.get_futures_price_preloaded(ticker_head=x) for x in unique_ticker_head_list} pnl_date_1 = exp.doubledate_shift_bus_days(double_date=pnl_date) underlying_frame = trades_frame[trades_frame['instrument'] == 'F'] option_frame = trades_frame[trades_frame['instrument'] == 'O'] futures_price_out_1 = [gfp.get_futures_price_preloaded(ticker=x, futures_data_dictionary=futures_data_dictionary, settle_date=pnl_date_1) for x in underlying_frame['ticker']] futures_price_out = [gfp.get_futures_price_preloaded(ticker=x, futures_data_dictionary=futures_data_dictionary, settle_date=pnl_date) for x in underlying_frame['ticker']] underlying_frame['price_1'] = [np.NaN if x.empty else x['close_price'].values[0] for x in futures_price_out_1] underlying_frame['price'] = [np.NaN if x.empty else x['close_price'].values[0] for x in futures_price_out] #underlying_frame['price_1'] = [gfp.get_futures_price_preloaded(ticker=x, # futures_data_dictionary=futures_data_dictionary, # settle_date=pnl_date_1)['close_price'].values[0] for x in underlying_frame['ticker']] #underlying_frame['price'] = [gfp.get_futures_price_preloaded(ticker=x, # futures_data_dictionary=futures_data_dictionary, # settle_date=pnl_date)['close_price'].values[0] for x in underlying_frame['ticker']] option_frame['price_1'] = [gop.get_options_price_from_db(ticker=option_frame['ticker'].iloc[x], strike=option_frame['strike_price'].iloc[x], option_type=option_frame['option_type'].iloc[x], con=con,settle_date=pnl_date_1, return_nan_if_emptyQ = True)['close_price'].values[0] for x in range(len(option_frame.index))] option_frame['price'] = [gop.get_options_price_from_db(ticker=option_frame['ticker'].iloc[x], strike=option_frame['strike_price'].iloc[x], option_type=option_frame['option_type'].iloc[x], con=con,settle_date=pnl_date, return_nan_if_emptyQ = True)['close_price'].values[0] for x in range(len(option_frame.index))] trades_frame = pd.concat([option_frame, underlying_frame]) position_frame = trades_frame[trades_frame['trade_date'] < pnl_datetime] intraday_frame = trades_frame[trades_frame['trade_date'] == pnl_datetime] position_pnl_per_ticker = pd.DataFrame(columns=['ticker','pnl_position']) intraday_pnl_per_ticker = pd.DataFrame(columns=['ticker','pnl_intraday']) position_pnl_per_tickerhead = pd.DataFrame(columns=['ticker_head','pnl_position']) intraday_pnl_per_tickerhead = pd.DataFrame(columns=['ticker_head','pnl_intraday']) if len(position_frame) == 0: position_pnl = 0 else: position_frame['pnl'] = position_frame['contract_multiplier']*\ position_frame['trade_quantity']*\ (position_frame['price']-position_frame['price_1']) position_pnl = position_frame['pnl'].sum() position_grouped_per_ticker = position_frame.groupby('ticker') position_grouped_per_tickerhead = position_frame.groupby('ticker_head') position_pnl_per_ticker['pnl_position'] = (position_grouped_per_ticker['pnl'].sum()).values position_pnl_per_ticker['ticker'] = (position_grouped_per_ticker['ticker'].first()).values position_pnl_per_tickerhead['pnl_position'] = (position_grouped_per_tickerhead['pnl'].sum()).values position_pnl_per_tickerhead['ticker_head'] = (position_grouped_per_tickerhead['ticker_head'].first()).values if len(intraday_frame) == 0: intraday_pnl = 0 t_cost = 0 else: intraday_frame['pnl'] = intraday_frame['contract_multiplier']*\ intraday_frame['trade_quantity']*\ (intraday_frame['price']-intraday_frame['trade_price']) intraday_frame['pnl_wtcost'] = intraday_frame['pnl']-abs(intraday_frame['trade_quantity']*intraday_frame['t_cost']) intraday_pnl = intraday_frame['pnl'].sum() t_cost = (abs(intraday_frame['trade_quantity']*intraday_frame['t_cost'])).sum() intraday_grouped_per_ticker = intraday_frame.groupby('ticker') intraday_grouped_per_tickerhead = intraday_frame.groupby('ticker_head') intraday_pnl_per_ticker['pnl_intraday'] = (intraday_grouped_per_ticker['pnl_wtcost'].sum()).values intraday_pnl_per_ticker['ticker'] = (intraday_grouped_per_ticker['ticker'].first()).values intraday_pnl_per_tickerhead['pnl_intraday'] = (intraday_grouped_per_tickerhead['pnl_wtcost'].sum()).values intraday_pnl_per_tickerhead['ticker_head'] = (intraday_grouped_per_tickerhead['ticker_head'].first()).values pnl_per_ticker = pd.merge(position_pnl_per_ticker,intraday_pnl_per_ticker,how='outer',on='ticker') intraday_zero_indx = [x not in intraday_pnl_per_ticker['ticker'].values for x in pnl_per_ticker['ticker']] position_zero_indx = [x not in position_pnl_per_ticker['ticker'].values for x in pnl_per_ticker['ticker']] pnl_per_ticker['pnl_position'][position_zero_indx] = 0 pnl_per_ticker['pnl_intraday'][intraday_zero_indx] = 0 pnl_per_ticker['pnl_total'] = pnl_per_ticker['pnl_position']+pnl_per_ticker['pnl_intraday'] pnl_per_ticker.set_index('ticker', drop=True, inplace=True) pnl_per_tickerhead = pd.merge(position_pnl_per_tickerhead,intraday_pnl_per_tickerhead,how='outer',on='ticker_head') intraday_zero_indx = [x not in intraday_pnl_per_tickerhead['ticker_head'].values for x in pnl_per_tickerhead['ticker_head']] position_zero_indx = [x not in position_pnl_per_tickerhead['ticker_head'].values for x in pnl_per_tickerhead['ticker_head']] pnl_per_tickerhead['pnl_position'][position_zero_indx] = 0 pnl_per_tickerhead['pnl_intraday'][intraday_zero_indx] = 0 pnl_per_tickerhead['pnl_total'] = pnl_per_tickerhead['pnl_position']+pnl_per_tickerhead['pnl_intraday'] pnl_per_tickerhead.set_index('ticker_head', drop=True, inplace=True) if 'con' not in kwargs.keys(): con.close() return {'total_pnl': int(position_pnl+intraday_pnl - t_cost), 'position_pnl': int(position_pnl), 'intraday_pnl': int(intraday_pnl), 't_cost': int(t_cost), 'pnl_per_ticker': pnl_per_ticker, 'pnl_per_tickerhead':pnl_per_tickerhead}
def get_strike_4current_delta(**kwargs): ticker = kwargs['ticker'] settle_date = kwargs['settle_date'] underlying_current_price = kwargs['underlying_current_price'] if m.isnan(underlying_current_price): return np.nan underlying_ticker = get_option_underlying(ticker=ticker) contract_specs_output = cmi.get_contract_specs(underlying_ticker) ticker_head = contract_specs_output['ticker_head'] if 'futures_data_dictionary' in kwargs.keys(): futures_data_dictionary = kwargs['futures_data_dictionary'] else: futures_data_dictionary = { x: gfp.get_futures_price_preloaded(ticker_head=x) for x in [ticker_head] } if 'call_delta_target' in kwargs.keys(): call_delta_target = kwargs['call_delta_target'] else: call_delta_target = 0.5 con = msu.get_my_sql_connection(**kwargs) option_data = gop.get_options_price_from_db(ticker=ticker, settle_date=settle_date, column_names=[ 'id', 'option_type', 'strike', 'cal_dte', 'tr_dte', 'close_price', 'volume', 'open_interest', 'delta' ]) underlying_settle_price = gfp.get_futures_price_preloaded( ticker=underlying_ticker, settle_date=settle_date, futures_data_dictionary=futures_data_dictionary)['close_price'].iloc[0] call_data = option_data[option_data['option_type'] == 'C'] call_data['delta_abs_centered'] = abs(call_data['delta'] - call_delta_target) call_data.sort_values('delta_abs_centered', ascending=True, inplace=True) strike_at_settle = (call_data['strike'].iloc[0] * call_data['delta_abs_centered'].iloc[1] + call_data['strike'].iloc[1]*call_data['delta_abs_centered'].iloc[0])/\ (call_data['delta_abs_centered'].iloc[0] + call_data['delta_abs_centered'].iloc[1]) strike_offset = strike_at_settle - underlying_settle_price strike_current_approximate = underlying_current_price + strike_offset call_data['strike_diff'] = abs(call_data['strike'] - strike_current_approximate) call_data.sort_values('strike_diff', ascending=True, inplace=True) strike_current = call_data['strike'].iloc[0] if 'con' not in kwargs.keys(): con.close() return strike_current
def calc_intraday_structure_pnl_from_prices(**kwargs): if 'as_of_date' in kwargs.keys(): as_of_date = kwargs['as_of_date'] else: as_of_date = exp.doubledate_shift_bus_days() con = msu.get_my_sql_connection(**kwargs) structure_type = kwargs['structure_type'] structure_price = kwargs['structure_price'] ticker_list = kwargs['ticker_list'] strike_list = kwargs['strike_list'] underlying_price_list = kwargs['underlying_price_list'] qty = kwargs['qty'] if structure_type == 'straddle_spread': option_frame = pd.DataFrame.from_dict({ 'ticker': [ticker_list[0], ticker_list[0], ticker_list[1], ticker_list[1]], 'option_type': ['C', 'P', 'C', 'P'], 'strike_price': [strike_list[0], strike_list[0], strike_list[1], strike_list[1]], 'qty': [-1, -1, 1, 1] }) elif structure_type == 'straddle': option_frame = pd.DataFrame.from_dict({ 'ticker': [ticker_list[0], ticker_list[0]], 'option_type': ['C', 'P'], 'strike_price': [strike_list[0], strike_list[0]], 'qty': [1, 1] }) option_price_output = [ gop.get_options_price_from_db( ticker=option_frame['ticker'].iloc[x], strike=option_frame['strike_price'].iloc[x], option_type=option_frame['option_type'].iloc[x], con=con, settle_date=as_of_date, column_names=['close_price', 'delta']) for x in range(len(option_frame.index)) ] option_frame['delta'] = [ option_price_output[x]['delta'][0] for x in range(len(option_frame.index)) ] option_frame['close_price'] = [ option_price_output[x]['close_price'][0] for x in range(len(option_frame.index)) ] option_frame['PQ'] = option_frame['close_price'] * option_frame['qty'] option_frame['signed_delta'] = option_frame['delta'] * option_frame['qty'] delta_list = [ option_frame[option_frame['ticker'] == x]['signed_delta'].sum() for x in ticker_list ] ticker_head = cmi.get_contract_specs(ticker_list[0])['ticker_head'] contract_multiplier = cmi.contract_multiplier[ticker_head] structure_price_yesterday = option_frame['PQ'].sum() structure_pnl = qty * (structure_price - structure_price_yesterday) * contract_multiplier underlying_ticker_list = [ oputil.get_option_underlying(ticker=x) for x in ticker_list ] underlying_price_list_yesterday = [ gfp.get_futures_price_preloaded( ticker=x, settle_date=as_of_date)['close_price'].iloc[0] for x in underlying_ticker_list ] delta_pnl = contract_multiplier * sum([ -delta_list[x] * qty * (underlying_price_list[x] - underlying_price_list_yesterday[x]) for x in range(len(delta_list)) ]) return { 'total_pnl': structure_pnl + delta_pnl, 'structure_pnl': structure_pnl, 'delta_pnl': delta_pnl, 'structure_price_yesterday': structure_price_yesterday }
def calc_intrday_pnl_from_prices(**kwargs): if 'as_of_date' in kwargs.keys(): as_of_date = kwargs['as_of_date'] else: as_of_date = exp.doubledate_shift_bus_days() net_position_frame = tas.get_net_position_4strategy_alias( alias=kwargs['alias'], as_of_date=as_of_date) net_position_frame['ticker_head'] = [ cmi.get_contract_specs(x)['ticker_head'] for x in net_position_frame['ticker'] ] net_position_frame['contract_multiplier'] = [ cmi.contract_multiplier[x] for x in net_position_frame['ticker_head'] ] con = msu.get_my_sql_connection(**kwargs) option_frame = net_position_frame[net_position_frame['instrument'] == 'O'] #option_frame = option_frame[(option_frame['strike_price'] == 54)|(option_frame['strike_price'] == 60)] #option_frame = option_frame[option_frame['strike_price'] == 112] #option_frame['qty'].loc[option_frame['ticker']=='LNV2016'] = -20 option_frame['close_price'] = [ gop.get_options_price_from_db( ticker=option_frame['ticker'].iloc[x], strike=option_frame['strike_price'].iloc[x], option_type=option_frame['option_type'].iloc[x], con=con, settle_date=as_of_date)['close_price'][0] for x in range(len(option_frame.index)) ] structure_quantity = abs(option_frame['qty']).unique()[0] structure_multiplier = option_frame['contract_multiplier'].unique()[0] structure_price = sum( option_frame['close_price'] * option_frame['qty']) / structure_quantity #return option_frame if structure_price < 0: structure_quantity = -structure_quantity structure_price = -structure_price structure_pnl = structure_quantity * structure_multiplier * ( kwargs['structure_price'] - structure_price) futures_frame = net_position_frame[net_position_frame['instrument'] == 'F'] futures_frame['close_price'] = [ gfp.get_futures_price_preloaded( ticker=x, settle_date=as_of_date)['close_price'].iloc[0] for x in futures_frame['ticker'] ] futures_frame['intraday_price'] = [ kwargs[x] for x in futures_frame['ticker'] ] futures_frame['intraday_pnl'] = ( futures_frame['intraday_price'] - futures_frame['close_price'] ) * futures_frame['qty'] * futures_frame['contract_multiplier'] if 'con' not in kwargs.keys(): con.close() return { 'structure_pnl': structure_pnl, 'futures_pnl': futures_frame['intraday_pnl'].sum(), 'structure_settle': structure_price }
def get_hedge_4strategy(**kwargs): con = msu.get_my_sql_connection(**kwargs) current_date = cu.get_doubledate() settle_price_date = exp.doubledate_shift_bus_days(double_date=current_date, shift_in_days=1) position_frame = tas.get_net_position_4strategy_alias( alias=kwargs['alias'], as_of_date=current_date, con=con) if 'intraday_price_frame' in kwargs.keys(): intraday_price_frame = kwargs['intraday_price_frame'] else: intraday_price_frame = gip.get_cme_direct_prices() intraday_price_frame.rename(columns={'ticker': 'underlying_ticker'}, inplace=True) intraday_price_frame['ticker_head'] = [ cmi.get_contract_specs(x)['ticker_head'] for x in intraday_price_frame['underlying_ticker'] ] intraday_price_frame['mid_price'] = ( intraday_price_frame['bid_price'] + intraday_price_frame['ask_price']) / 2 intraday_price_frame['mid_price'] = [ tfl.convert_trade_price_from_cme_direct( ticker_head=intraday_price_frame['ticker_head'].iloc[x], price=intraday_price_frame['mid_price'].iloc[x]) for x in range(len(intraday_price_frame.index)) ] options_frame = position_frame[position_frame['instrument'] == 'O'] futures_frame = position_frame[position_frame['instrument'] == 'F'] if options_frame.empty: futures_frame.rename(columns={ 'ticker': 'underlying_ticker', 'qty': 'underlying_delta' }, inplace=True) futures_frame = futures_frame[[ 'underlying_ticker', 'underlying_delta' ]] net_position = pd.merge(futures_frame, intraday_price_frame, how='left', on='underlying_ticker') net_position['hedge_price'] = net_position['mid_price'] net_position['hedge'] = -net_position['underlying_delta'] return net_position imp_vol_list = [ gop.get_options_price_from_db( ticker=options_frame['ticker'].iloc[x], settle_date=settle_price_date, strike=options_frame['strike_price'].iloc[x], column_names=['imp_vol'], con=con)['imp_vol'] for x in range(len(options_frame.index)) ] options_frame['imp_vol'] = [ imp_vol_list[x][1] if (np.isnan(imp_vol_list[x][0]) and len(imp_vol_list[x]) > 1) else imp_vol_list[x][0] for x in range(len(options_frame.index)) ] options_frame['underlying_ticker'] = [ omu.get_option_underlying(ticker=x) for x in options_frame['ticker'] ] #print(intraday_price_frame) options_frame = pd.merge(options_frame, intraday_price_frame, how='left', on='underlying_ticker') options_frame['ticker_head'] = [ cmi.get_contract_specs(x)['ticker_head'] for x in options_frame['ticker'] ] options_frame['exercise_type'] = [ cmi.get_option_exercise_type(ticker_head=x) for x in options_frame['ticker_head'] ] options_frame['strike_price'] = options_frame['strike_price'].astype( 'float64') options_frame['delta'] = [ omu.option_model_wrapper( ticker=options_frame['ticker'].iloc[x], calculation_date=current_date, interest_rate_date=settle_price_date, underlying=options_frame['mid_price'].iloc[x], strike=options_frame['strike_price'].iloc[x], implied_vol=options_frame['imp_vol'].iloc[x], option_type=options_frame['option_type'].iloc[x], exercise_type=options_frame['exercise_type'].iloc[x], con=con)['delta'] for x in range(len(options_frame.index)) ] options_frame[ 'total_delta'] = options_frame['qty'] * options_frame['delta'] grouped = options_frame.groupby('underlying_ticker') net_position = pd.DataFrame() net_position['underlying_ticker'] = ( grouped['underlying_ticker'].first()).values net_position['hedge_price'] = (grouped['mid_price'].first()).values net_position['option_delta'] = (grouped['total_delta'].sum()).values net_position['option_delta'] = net_position['option_delta'].round(2) if futures_frame.empty: net_position['total_delta'] = net_position['option_delta'] else: futures_frame.rename(columns={ 'ticker': 'underlying_ticker', 'qty': 'underlying_delta' }, inplace=True) futures_frame = futures_frame[[ 'underlying_ticker', 'underlying_delta' ]] isinOptions = futures_frame['underlying_ticker'].isin( net_position['underlying_ticker']) futures_frame_w_options = futures_frame[isinOptions] futures_frame_wo_options = futures_frame[~isinOptions] if futures_frame_w_options.empty: net_position['underlying_delta'] = 0 net_position['total_delta'] = net_position['option_delta'] else: net_position = pd.merge(net_position, futures_frame_w_options, how='outer', on='underlying_ticker') net_position[['underlying_delta' ]] = net_position[['underlying_delta' ]].fillna(value=0, inplace=False) net_position['total_delta'] = net_position[ 'option_delta'] + net_position['underlying_delta'] if not futures_frame_wo_options.empty: net_position_futures = pd.merge(futures_frame_wo_options, intraday_price_frame, how='left', on='underlying_ticker') net_position_futures['hedge_price'] = net_position_futures[ 'mid_price'] net_position_futures['option_delta'] = 0 net_position_futures['total_delta'] = net_position_futures[ 'underlying_delta'] net_position = pd.concat([ net_position, net_position_futures[[ 'underlying_ticker', 'hedge_price', 'option_delta', 'underlying_delta', 'total_delta' ]] ]) net_position['hedge'] = -net_position['total_delta'] if 'con' not in kwargs.keys(): con.close() return net_position