def generate_symbol_table(**kwargs): instrument = kwargs['instrument'] if instrument == 'futures': ticker_list = cl.get_contract_list_4year_range(**kwargs) elif instrument == 'options': ticker_list = cl.get_option_contract_list_4year_range(**kwargs) con = msu.get_my_sql_connection(**kwargs) column_str = "ticker, ticker_head, ticker_year, ticker_month, expiration_date," \ "instrument, name, ticker_class, currency, created_date, last_updated_date" insert_str = ("%s, " * 11)[:-2] final_str = "INSERT INTO symbol (%s) VALUES (%s)" % (column_str, insert_str) cur = con.cursor() for i in range(0, int(ceil(len(ticker_list) / 100.0))): cur.executemany(final_str, ticker_list[i*100:(i+1)*100]) con.commit() if 'con' not in kwargs.keys(): con.close()
def select_strategies(**kwargs): con = msu.get_my_sql_connection(**kwargs) sql_query = 'SELECT * FROM futures_master.strategy WHERE ' if 'open_date_from' in kwargs.keys(): open_date_from = kwargs['open_date_from'] else: open_date_from = 20180601 sql_query = sql_query + ' open_date>=' + str(open_date_from) if 'open_date_to' in kwargs.keys(): sql_query = sql_query + ' and open_date<=' + str( kwargs['open_date_to']) cur = con.cursor() cur.execute(sql_query) data = cur.fetchall() if 'con' not in kwargs.keys(): con.close() return pd.DataFrame(data, columns=[ 'id', 'alias', 'open_date', 'close_date', 'pnl', 'created_date', 'last_updated_date', 'description_string' ])
def get_futures_prices_4date(**kwargs): date_to = kwargs['date_to'] filter_string = 'WHERE dp.price_date=' + str(date_to) if 'date_from' in kwargs.keys(): filter_string = 'WHERE dp.price_date<=' + str( date_to) + ' and dp.price_date>=' + str(kwargs['date_from']) con = msu.get_my_sql_connection(**kwargs) sql_query = 'SELECT dp.price_date, sym.ticker, dp.ticker_head, dp.ticker_month, ' + \ 'sym.ticker_year, dp.cal_dte, dp.tr_dte, ' \ 'dp.open_price, dp.high_price, dp.low_price, dp.close_price, dp.volume ' + \ 'FROM symbol as sym ' + \ 'INNER JOIN daily_price as dp ON dp.symbol_id = sym.id ' + \ filter_string + \ ' ORDER BY dp.ticker_head, dp.cal_dte ' cur = con.cursor() cur.execute(sql_query) data = cur.fetchall() if 'con' not in kwargs.keys(): con.close() return pd.DataFrame(data, columns=[ 'settle_date', 'ticker', 'ticker_head', 'ticker_month', 'ticker_year', 'cal_dte', 'tr_dte', 'open_price', 'high_price', 'low_price', 'close_price', 'volume' ])
def load_tagged_tt_trades(**kwargs): assign_output = get_tagged_tt_fills(**kwargs) aggregate_trades = assign_output['aggregate_trades'] con = msu.get_my_sql_connection(**kwargs) final_alias_dictionary = {} unique_alias_list = aggregate_trades['alias'].unique() open_strategy_frame = ts.get_open_strategies() for i in range(len(unique_alias_list)): if ~open_strategy_frame['alias'].str.contains(unique_alias_list[i]).any(): print('Need to create ' + unique_alias_list[i]) if '_ocs' in unique_alias_list[i]: gen_output = ts.generate_db_strategy_from_alias_and_class(alias=unique_alias_list[i], strategy_class='ocs') final_alias_dictionary[unique_alias_list[i]] = gen_output['alias'] else: final_alias_dictionary[unique_alias_list[i]] = unique_alias_list[i] aggregate_trades['alias_final'] = [final_alias_dictionary[x] for x in aggregate_trades['alias']] aggregate_trades['alias'] = aggregate_trades['alias_final'] ts.load_trades_2strategy(trade_frame=aggregate_trades,con=con,**kwargs) if 'con' not in kwargs.keys(): con.close()
def get_expiration_report(**kwargs): con = msu.get_my_sql_connection(**kwargs) portfolio_frame = get_portfolio_expirations(**kwargs) strategy_frame = ts.get_open_strategies(con=con, as_of_date=kwargs['report_date']) expiration_list = [ get_strategy_expiration(con=con, alias=strategy_frame['alias'].iloc[x], as_of_date=kwargs['report_date']) for x in range(len(strategy_frame.index)) ] expiration_list.append(portfolio_frame) if 'con' not in kwargs.keys(): con.close() expiration_list = [x for x in expiration_list if not x.empty] expiration_frame = pd.concat(expiration_list, sort=True) return expiration_frame.sort_values('tr_days_2roll', ascending=True, inplace=False)
def get_db_contract_list_filtered(**kwargs): con = msu.get_my_sql_connection(**kwargs) sql_query = 'SELECT id, ticker, expiration_date FROM symbol' if ('ticker_year_from' in kwargs.keys()) and ('ticker_year_to' in kwargs.keys()): filter_string = 'ticker_year>=' + str(kwargs['ticker_year_from']) + ' and ticker_year<=' + str(kwargs['ticker_year_to']) elif ('expiration_date_from' in kwargs.keys()) and ('expiration_date_to' in kwargs.keys()): filter_string = 'expiration_date>=' + str(kwargs['expiration_date_from']) + ' and expiration_date<=' + str(kwargs['expiration_date_to']) if 'ticker_head' in kwargs.keys(): filter_string = filter_string + ' and ticker_head=\'' + kwargs['ticker_head'] + '\'' if 'ticker_month' in kwargs.keys(): filter_string = filter_string + ' and ticker_month=' + str(kwargs['ticker_month']) if 'instrument' in kwargs.keys(): filter_string = filter_string + ' and instrument=\'' + kwargs['instrument'] + '\'' sql_query = sql_query + ' WHERE ' + filter_string + ' ORDER BY id ASC' cur = con.cursor() cur.execute(sql_query) data = cur.fetchall() if 'con' not in kwargs.keys(): con.close() return data
def generate_liquid_options_list_dataframe(**kwargs): settle_date = kwargs["settle_date"] con = msu.get_my_sql_connection(**kwargs) contract_list = [] for ticker_head in cmi.option_tickerhead_list: for ticker_month in cmi.get_option_contract_months(ticker_head=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( get_db_contract_list_filtered( expiration_date_from=settle_date, expiration_date_to=cu.doubledate_shift(settle_date, -max_cal_dte), ticker_head=ticker_head, ticker_month=ticker_month_num, con=con, instrument="options", ) ) if "con" not in kwargs.keys(): con.close() return pd.DataFrame(contract_list, columns=["id", "ticker", "expiration_date"])
def generate_symbol_table(**kwargs): instrument = kwargs['instrument'] if instrument == 'futures': ticker_list = cl.get_contract_list_4year_range(**kwargs) elif instrument == 'options': ticker_list = cl.get_option_contract_list_4year_range(**kwargs) con = msu.get_my_sql_connection(**kwargs) column_str = "ticker, ticker_head, ticker_year, ticker_month, expiration_date," \ "instrument, name, ticker_class, currency, created_date, last_updated_date" insert_str = ("%s, " * 11)[:-2] final_str = "INSERT INTO symbol (%s) VALUES (%s)" % (column_str, insert_str) cur = con.cursor() for i in range(0, int(ceil(len(ticker_list) / 100.0))): cur.executemany(final_str, ticker_list[i * 100:(i + 1) * 100]) con.commit() if 'con' not in kwargs.keys(): con.close()
def get_strategy_class_historical_pnls(**kwargs): con = msu.get_my_sql_connection(**kwargs) if 'as_of_date' in kwargs.keys(): as_of_date = kwargs['as_of_date'] else: as_of_date = exp.doubledate_shift_bus_days() strategy_frame = ts.select_strategies(con=con,open_date_to=as_of_date) strategy_frame['strategy_class'] = [sc.convert_from_string_to_dictionary(string_input=strategy_frame['description_string'][x])['strategy_class'] for x in range(len(strategy_frame.index))] unique_strategy_class_list = strategy_frame['strategy_class'].unique() time_series_list = [None]*len(unique_strategy_class_list) for i in range(len(unique_strategy_class_list)): strategy_frame_selected = strategy_frame[strategy_frame['strategy_class'] == unique_strategy_class_list[i]] pnl_out = [tpnl.get_strategy_pnl(alias=x,as_of_date=as_of_date,con=con)['pnl_frame'][['settle_date','total_pnl']] for x in strategy_frame_selected['alias']] [x.set_index('settle_date',drop=True, inplace=True) for x in pnl_out] time_series_list[i] = pd.concat(pnl_out,axis=1).fillna(0).sum(axis=1) if 'con' not in kwargs.keys(): con.close() merged_pnl = pd.concat(time_series_list,axis=1,keys=unique_strategy_class_list).fillna(0) merged_pnl['total'] = merged_pnl.sum(axis=1) output_dir = dn.get_directory_name(ext='daily') writer = pd.ExcelWriter(output_dir + '/historical_performance_' + str(as_of_date) + '.xlsx', engine='xlsxwriter') merged_pnl.to_excel(writer, sheet_name='timeSeries') writer.save() return merged_pnl
def hedge_strategy_against_delta(**kwargs): con = msu.get_my_sql_connection(**kwargs) print(kwargs['alias']) hedge_results = get_hedge_4strategy(alias=kwargs['alias'], con=con) trade_frame = pd.DataFrame() trade_frame['ticker'] = hedge_results['underlying_ticker'] trade_frame['option_type'] = None trade_frame['strike_price'] = np.NaN trade_frame['trade_price'] = hedge_results['hedge_price'] trade_frame['trade_quantity'] = hedge_results['hedge'] trade_frame['instrument'] = 'F' trade_frame['real_tradeQ'] = True trade_frame['alias'] = kwargs['alias'] tas.load_trades_2strategy(trade_frame=trade_frame,con=con) trade_frame['trade_quantity'] = -trade_frame['trade_quantity'] trade_frame['alias'] = 'delta_oct16' tas.load_trades_2strategy(trade_frame=trade_frame,con=con) if 'con' not in kwargs.keys(): con.close()
def load_entire_history(**kwargs): contract_list = cl.get_db_contract_list_filtered(instrument='futures', ticker_year_from=1980, ticker_year_to=2022, **kwargs) # length of contract_list is 6594 if 'start_indx' in kwargs.keys(): start_indx = kwargs['start_indx'] else: start_indx = 0 if 'end_indx' in kwargs.keys(): end_indx = kwargs['end_indx'] else: end_indx = len(contract_list) con = msu.get_my_sql_connection(**kwargs) load_price_data_input = dict() load_price_data_input['con'] = con if 'date_to' in kwargs.keys(): load_price_data_input['date_to'] = kwargs['date_to'] for i in range(start_indx, end_indx): load_price_data_input['symbol_id'] = contract_list[i][0] load_price_data_input['data_vendor_id'] = 1 load_price_data_input['ticker'] = contract_list[i][1] load_price_data_input['expiration_date'] = contract_list[i][2] load_price_data_4ticker(load_price_data_input) print('No : ' + str(i) + ', ' + contract_list[i][1] + ' loaded') if 'con' not in kwargs.keys(): con.close()
def update_futures_price_database(**kwargs): import time run_date = int(time.strftime('%Y%m%d')) early_start_date = cu.doubledate_shift(run_date, 15) #45 con = msu.get_my_sql_connection(**kwargs) contract_list = [] for ticker_head in cmi.futures_butterfly_strategy_tickerhead_list: 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=early_start_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')) date_from_list = [gfp.get_futures_last_price_date_4ticker(ticker=x[1], con=con) for x in contract_list] load_price_data_input = dict() load_price_data_input['con'] = con for i in range(len(contract_list)): load_price_data_input['symbol_id'] = contract_list[i][0] load_price_data_input['data_vendor_id'] = 1 load_price_data_input['ticker'] = contract_list[i][1] load_price_data_input['expiration_date'] = contract_list[i][2] load_price_data_input['date_from'] = date_from_list[i] load_price_data_4ticker(load_price_data_input) print('No : ' + str(i) + ', ' + contract_list[i][1] + ' loaded') if 'con' not in kwargs.keys(): con.close()
def select_strategies(**kwargs): con = msu.get_my_sql_connection(**kwargs) sql_query = 'SELECT * FROM futures_master.strategy WHERE ' if 'open_date_from' in kwargs.keys(): open_date_from = kwargs['open_date_from'] else: open_date_from = 20160107 sql_query = sql_query + ' open_date>=' + str(open_date_from) if 'open_date_to' in kwargs.keys(): sql_query = sql_query + ' and open_date<=' + str(kwargs['open_date_to']) cur = con.cursor() cur.execute(sql_query) data = cur.fetchall() if 'con' not in kwargs.keys(): con.close() return pd.DataFrame(data,columns=['id','alias','open_date','close_date','pnl','created_date','last_updated_date','description_string'])
def get_trades_4strategy_alias(**kwargs): con = msu.get_my_sql_connection(**kwargs) sql_query = 'SELECT tr.id, tr.ticker, tr.option_type, tr.strike_price, tr.trade_price, tr.trade_quantity, tr.trade_date, tr.instrument, tr.real_tradeQ ' + \ 'FROM strategy as str INNER JOIN trades as tr ON tr.strategy_id=str.id ' + \ 'WHERE str.alias=\'' + kwargs['alias'] + '\'' cur = con.cursor() cur.execute(sql_query) data = cur.fetchall() if 'con' not in kwargs.keys(): con.close() trade_frame = pd.DataFrame(data, columns=[ 'id', 'ticker', 'option_type', 'strike_price', 'trade_price', 'trade_quantity', 'trade_date', 'instrument', 'real_tradeQ' ]) trade_frame['trade_price'] = [ float(x) if x is not None else float('NaN') for x in trade_frame['trade_price'].values ] trade_frame['trade_quantity'] = trade_frame['trade_quantity'].astype( 'float64') trade_frame['strike_price'] = trade_frame['strike_price'].astype('float64') return trade_frame
def get_open_strategies(**kwargs): con = msu.get_my_sql_connection(**kwargs) if 'as_of_date' in kwargs.keys(): as_of_date = kwargs['as_of_date'] else: as_of_date = int(tm.strftime('%Y%m%d')) cur = con.cursor() sql_query = 'SELECT * FROM futures_master.strategy WHERE close_date>=' + str( as_of_date) + ' and open_date<=' + str(as_of_date) cur.execute(sql_query) data = cur.fetchall() if 'con' not in kwargs.keys(): con.close() return pd.DataFrame(data, columns=[ 'id', 'alias', 'open_date', 'close_date', 'pnl', 'created_date', 'last_updated_date', 'description_string' ])
def close_strategy(**kwargs): alias = kwargs['alias'] if 'close_date' in kwargs.keys(): close_date = kwargs['close_date'] else: close_date = exp.doubledate_shift_bus_days() now_time = dt.datetime.now() now_date = now_time.date() con = msu.get_my_sql_connection(**kwargs) net_position = ts.get_net_position_4strategy_alias(alias=alias, con=con, as_of_date=close_date) if net_position.empty: pnl_output = get_strategy_pnl(alias=alias, con=con, as_of_date=close_date) total_pnl = pnl_output['total_pnl'] cur = con.cursor() query_str = 'UPDATE strategy SET pnl=' + str(total_pnl) + \ ', close_date=' + str(close_date) + \ ', last_updated_date=' + now_date.strftime('%Y%m%d') + \ ' WHERE alias=\'' + alias + '\'' cur.execute(query_str) con.commit() else: print(alias + ' is not empty ! ') return if 'con' not in kwargs.keys(): con.close()
def hedge_strategy_against_delta(**kwargs): con = msu.get_my_sql_connection(**kwargs) print(kwargs['alias']) if 'intraday_price_frame' in kwargs.keys(): hedge_results = get_hedge_4strategy( alias=kwargs['alias'], intraday_price_frame=kwargs['intraday_price_frame'], con=con) else: hedge_results = get_hedge_4strategy(alias=kwargs['alias'], con=con) trade_frame = pd.DataFrame() trade_frame['ticker'] = hedge_results['underlying_ticker'] trade_frame['option_type'] = None trade_frame['strike_price'] = np.NaN trade_frame['trade_price'] = hedge_results['hedge_price'] trade_frame['trade_quantity'] = hedge_results['hedge'] trade_frame['instrument'] = 'F' trade_frame['real_tradeQ'] = True trade_frame['alias'] = kwargs['alias'] tas.load_trades_2strategy(trade_frame=trade_frame, con=con) trade_frame['trade_quantity'] = -trade_frame['trade_quantity'] trade_frame['alias'] = kwargs['delta_alias'] tas.load_trades_2strategy(trade_frame=trade_frame, con=con) if 'con' not in kwargs.keys(): con.close()
def option_model_wrapper(**kwargs): # enter underlying, strike, (option_price or implied_vol) # calculation_date, exercise_type, option_type con = msu.get_my_sql_connection(**kwargs) ticker = kwargs['ticker'] calculation_date = kwargs['calculation_date'] if 'interest_rate_date' in kwargs.keys(): interest_rate_date = kwargs['interest_rate_date'] else: interest_rate_date = calculation_date #print(ticker) #print(kwargs['exercise_type']) contract_specs_output = cmi.get_contract_specs(ticker) ticker_head = contract_specs_output['ticker_head'] contract_multiplier = cmi.contract_multiplier[ticker_head] expiration_datetime = exp.get_expiration_from_db(ticker=ticker, instrument='options', con=con) expiration_date = int(expiration_datetime.strftime('%Y%m%d')) if 'con' not in kwargs.keys(): con.close() interest_rate = grfs.get_simple_rate( as_of_date=interest_rate_date, date_to=expiration_date)['rate_output'] if np.isnan(interest_rate): option_greeks = { 'implied_vol': np.NaN, 'delta': np.NaN, 'vega': np.NaN, 'dollar_vega': np.NaN, 'theta': np.NaN, 'dollar_theta': np.NaN, 'gamma': np.NaN, 'dollar_gamma': np.NaN, 'interest_rate': np.NaN, 'cal_dte': np.NaN } else: option_greeks = qom.get_option_greeks(risk_free_rate=interest_rate, expiration_date=expiration_date, **kwargs) option_greeks['implied_vol'] = 100 * option_greeks['implied_vol'] option_greeks[ 'dollar_vega'] = option_greeks['vega'] * contract_multiplier / 100 option_greeks[ 'dollar_theta'] = option_greeks['theta'] * contract_multiplier option_greeks[ 'dollar_gamma'] = option_greeks['gamma'] * contract_multiplier option_greeks['interest_rate'] = interest_rate return option_greeks
def load_manual_entry_trades(**kwargs): trade_frame = assign_trades_2strategies(trade_source='manual_entry') con = msu.get_my_sql_connection(**kwargs) ts.load_trades_2strategy(trade_frame=trade_frame,con=con,**kwargs) if 'con' not in kwargs.keys(): con.close()
def load_tagged_tt_trades(**kwargs): assign_output = get_tagged_tt_fills(**kwargs) con = msu.get_my_sql_connection(**kwargs) ts.load_trades_2strategy(trade_frame=assign_output['aggregate_trades'],con=con,**kwargs) if 'con' not in kwargs.keys(): con.close()
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 generate_spread_carry_followup_report(**kwargs): 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 con = msu.get_my_sql_connection(**kwargs) ta_output_dir = dn.get_dated_directory_extension(folder_date=as_of_date, ext='ta') if 'writer' in kwargs.keys(): writer = kwargs['writer'] else: writer = pd.ExcelWriter(ta_output_dir + '/followup.xlsx', engine='xlsxwriter') strategy_frame = ts.get_open_strategies(**kwargs) strategy_class_list = [ sc.convert_from_string_to_dictionary( string_input=strategy_frame['description_string'][x]) ['strategy_class'] for x in range(len(strategy_frame.index)) ] spread_carry_indx = [x == 'spread_carry' for x in strategy_class_list] spread_carry_frame = strategy_frame[spread_carry_indx] if spread_carry_frame.empty: return writer results = [ sf.get_results_4strategy( alias=spread_carry_frame['alias'].iloc[x], strategy_info_output=spread_carry_frame.iloc[x], con=con) for x in range(len(spread_carry_frame.index)) ] results_frame_list = [ results[x]['results_frame'] for x in range(len(results)) if results[x]['success'] ] spread_carry_followup_frame = pd.concat(results_frame_list) spread_carry_followup_frame.to_excel(writer, sheet_name='sc') worksheet_sc = writer.sheets['sc'] worksheet_sc.freeze_panes(1, 0) worksheet_sc.autofilter(0, 0, len(spread_carry_followup_frame.index), len(spread_carry_followup_frame.columns)) if 'con' not in kwargs.keys(): con.close() return writer
def generate_ics_sheet_4date(**kwargs): date_to = kwargs['date_to'] output_dir = ts.create_strategy_output_dir(strategy_class='ics', report_date=date_to) if os.path.isfile(output_dir + '/summary.pkl'): intraday_spreads = pd.read_pickle(output_dir + '/summary.pkl') return {'intraday_spreads': intraday_spreads, 'success': True} con = msu.get_my_sql_connection(**kwargs) intraday_spreads = get_spreads_4date(**kwargs) num_spreads = len(intraday_spreads.index) signals_output = [ ics.get_ics_signals(ticker=intraday_spreads['ticker'].iloc[x], con=con, date_to=date_to) for x in range(num_spreads) ] intraday_spreads['downside'] = [x['downside'] for x in signals_output] intraday_spreads['upside'] = [x['upside'] for x in signals_output] intraday_spreads['front_tr_dte'] = [ x['front_tr_dte'] for x in signals_output ] intraday_spreads['intraday_mean5'] = [ x['intraday_mean5'] for x in signals_output ] intraday_spreads['intraday_mean2'] = [ x['intraday_mean2'] for x in signals_output ] intraday_spreads['intraday_mean1'] = [ x['intraday_mean1'] for x in signals_output ] intraday_spreads['intraday_std5'] = [ x['intraday_std5'] for x in signals_output ] intraday_spreads['intraday_std2'] = [ x['intraday_std2'] for x in signals_output ] intraday_spreads['intraday_std1'] = [ x['intraday_std1'] for x in signals_output ] intraday_spreads.to_pickle(output_dir + '/summary.pkl') if 'con' not in kwargs.keys(): con.close() return {'intraday_spreads': intraday_spreads, 'success': True}
def main(): app = algo.Algo() con = msu.get_my_sql_connection() date_now = cu.get_doubledate() datetime_now = dt.datetime.now() report_date = exp.doubledate_shift_bus_days() ticker_head_list = ['HO', 'LC', 'FC', 'NQ'] # avoid HO on wednesdays for now!! data_list = [gfp.get_futures_price_preloaded(ticker_head=x, settle_date=report_date) for x in ticker_head_list] ticker_frame = pd.concat(data_list) ticker_frame.sort(['ticker_head','volume'], ascending=[True, False], inplace=True) ticker_frame.drop_duplicates(subset=['ticker_head'], take_last=False, inplace=True) ticker_list = list(ticker_frame['ticker']) theme_name_list = set([x + '_long' for x in ticker_list]).union(set([x + '_short' for x in ticker_list])) alias_portfolio = aup.portfolio(ticker_list=theme_name_list) latest_trade_entry_datetime = dt.datetime(datetime_now.year, datetime_now.month, datetime_now.day, 12, 0, 0) latest_livestock_exit_datetime = dt.datetime(datetime_now.year, datetime_now.month, datetime_now.day, 13, 55, 0) latest_macro_exit_datetime = dt.datetime(datetime_now.year, datetime_now.month, datetime_now.day, 15, 55, 0) daily_sd_dictionary = {} for i in range(len(ticker_list)): daily_data_frame = gfp.get_futures_price_preloaded(ticker=ticker_list[i], settle_date_to=report_date) daily_data_frame['close_diff'] = daily_data_frame['close_price'].diff() daily_sd_dictionary[ticker_list[i]] = np.std(daily_data_frame['close_diff'].iloc[-40:]) db_alias = 'itf_' + datetime_now.strftime('%b_%y') strategy_frame = ts.get_open_strategies(as_of_date=int(datetime_now.strftime('%Y%m%d')), con=con) if db_alias not in strategy_frame.values: db_strategy_output = ts.generate_db_strategy_from_alias(alias=db_alias, description_string='strategy_class=itf',con=con) db_alias = db_strategy_output['alias'] app.ticker_list = ticker_list app.db_alias = db_alias app.tick_size_dictionary = {x:cmi.tick_size[cmi.get_contract_specs(x)['ticker_head']] for x in ticker_list} app.daily_sd_dictionary = daily_sd_dictionary app.contract_multiplier_dictionary = {x:cmi.contract_multiplier[cmi.get_contract_specs(x)['ticker_head']] for x in ticker_list} app.ticker_head_dictionary = {x:cmi.get_contract_specs(x)['ticker_head'] for x in ticker_list} app.latest_trade_entry_datetime = latest_trade_entry_datetime app.latest_trade_exit_datetime_dictionary = {'HO': latest_macro_exit_datetime, 'LC': latest_livestock_exit_datetime, 'FC': latest_livestock_exit_datetime, 'NQ': latest_macro_exit_datetime} app.alias_portfolio = alias_portfolio app.output_dir = ts.create_strategy_output_dir(strategy_class='itf', report_date=report_date) app.log = lg.get_logger(file_identifier='ib_itf',log_level='INFO') app.con = con app.connect(client_id=4) app.run()
def get_option_ticker_indicators(**kwargs): con = msu.get_my_sql_connection(**kwargs) if 'ticker' in kwargs.keys(): filter_string = 'WHERE ticker=\'' + kwargs['ticker'] + '\'' elif 'ticker_head' in kwargs.keys(): filter_string = 'WHERE ticker_head=\'' + kwargs['ticker_head'] + '\'' else: filter_string = '' if 'settle_date' in kwargs.keys(): if filter_string == '': filter_string = filter_string + ' WHERE price_date=' + str(kwargs['settle_date']) else: filter_string = filter_string + ' and price_date=' + str(kwargs['settle_date']) if 'settle_date_to' in kwargs.keys(): if filter_string == '': filter_string = filter_string + ' WHERE price_date<=' + str(kwargs['settle_date_to']) else: filter_string = filter_string + ' and price_date<=' + str(kwargs['settle_date_to']) if 'delta' in kwargs.keys(): if filter_string == '': filter_string = ' WHERE delta=' + str(kwargs['delta']) else: filter_string = filter_string + ' and delta=0.5' if 'num_cal_days_back' in kwargs.keys(): date_from = cu.doubledate_shift(kwargs['settle_date_to'], kwargs['num_cal_days_back']) filter_string = filter_string + ' and price_date>=' + str(date_from) if 'column_names' in kwargs.keys(): column_names = kwargs['column_names'] else: column_names = ['ticker', 'price_date' , 'ticker_head', 'ticker_month', 'ticker_year', 'cal_dte', 'tr_dte', 'imp_vol', 'theta','close2close_vol20', 'volume','open_interest'] sql_query = 'SELECT ' + ",".join(column_names) + ' FROM option_ticker_indicators ' + filter_string cur = con.cursor() cur.execute(sql_query) data = cur.fetchall() if 'con' not in kwargs.keys(): con.close() data_frame_output = pd.DataFrame(data, columns=['settle_date' if x == 'price_date' else x for x in column_names]) for x in ['imp_vol', 'close2close_vol20', 'theta']: if x in column_names: data_frame_output[x] = data_frame_output[x].astype('float64') return data_frame_output.sort(['ticker_head','settle_date', 'tr_dte'], ascending=[True, True,True], inplace=False)
def get_option_ticker_indicators(**kwargs): con = msu.get_my_sql_connection(**kwargs) if 'ticker' in kwargs.keys(): filter_string = 'WHERE ticker=\'' + kwargs['ticker'] + '\'' elif 'ticker_head' in kwargs.keys(): filter_string = 'WHERE ticker_head=\'' + kwargs['ticker_head'] + '\'' else: filter_string = '' if 'settle_date' in kwargs.keys(): if filter_string == '': filter_string = filter_string + ' WHERE price_date=' + str(kwargs['settle_date']) else: filter_string = filter_string + ' and price_date=' + str(kwargs['settle_date']) if 'settle_date_to' in kwargs.keys(): if filter_string == '': filter_string = filter_string + ' WHERE price_date<=' + str(kwargs['settle_date_to']) else: filter_string = filter_string + ' and price_date<=' + str(kwargs['settle_date_to']) if 'delta' in kwargs.keys(): if filter_string == '': filter_string = ' WHERE delta=' + str(kwargs['delta']) else: filter_string = filter_string + ' and delta=0.5' if 'num_cal_days_back' in kwargs.keys(): date_from = cu.doubledate_shift(kwargs['settle_date_to'], kwargs['num_cal_days_back']) filter_string = filter_string + ' and price_date>=' + str(date_from) if 'column_names' in kwargs.keys(): column_names = kwargs['column_names'] else: column_names = ['ticker', 'price_date' , 'ticker_head', 'ticker_month', 'ticker_year', 'cal_dte', 'tr_dte', 'imp_vol', 'theta','close2close_vol20', 'volume','open_interest'] sql_query = 'SELECT ' + ",".join(column_names) + ' FROM option_ticker_indicators ' + filter_string cur = con.cursor() cur.execute(sql_query) data = cur.fetchall() if 'con' not in kwargs.keys(): con.close() data_frame_output = pd.DataFrame(data, columns=['settle_date' if x == 'price_date' else x for x in column_names]) for x in ['imp_vol', 'close2close_vol20', 'theta']: if x in column_names: data_frame_output[x] = data_frame_output[x].astype('float64') return data_frame_output.sort_values(['ticker_head','settle_date', 'tr_dte'], ascending=[True, True,True], inplace=False)
def test_ib_order_follow_up(): app = ib_order_follow_up() date_now = cu.get_doubledate() con = msu.get_my_sql_connection() delta_strategy_frame = ts.get_filtered_open_strategies(as_of_date=date_now, con=con, strategy_class_list=['delta']) app.delta_alias = delta_strategy_frame['alias'].iloc[-1] ta_folder = dn.get_dated_directory_extension(folder_date=date_now, ext='ta') app.trade_file = ta_folder + '/trade_dir.csv' app.con = con app.connect(client_id=7) app.run()
def generate_underlying_proxy_report(**kwargs): con = msu.get_my_sql_connection(**kwargs) report_date = kwargs['report_date'] futures_dataframe = cl.generate_futures_list_dataframe(date_to=report_date) futures_flat_curve = futures_dataframe[ futures_dataframe['ticker_class'].isin( ['FX', 'Metal', 'Treasury', 'Index'])] futures_flat_curve.reset_index(drop=True, inplace=True) futures_flat_curve['tr_days_2roll'] = futures_flat_curve.apply( lambda x: exp.get_days2_roll(ticker=x['ticker'], instrument='Futures', date_to=report_date, con=con)['tr_days_2roll'], axis=1) futures_data_dictionary = { x: gfp.get_futures_price_preloaded(ticker_head=x) for x in futures_flat_curve['ticker_head'].unique() } proxy_output_list = [ get_underlying_proxy_ticker( ticker=futures_flat_curve['ticker'].iloc[x], settle_date=report_date, con=con, futures_data_dictionary=futures_data_dictionary) for x in range(len(futures_flat_curve.index)) ] futures_flat_curve['proxy_ticker'] = [ x['ticker'] for x in proxy_output_list ] futures_flat_curve['add_2_proxy'] = [ x['add_2_proxy'] for x in proxy_output_list ] ta_output_dir = dn.get_dated_directory_extension(folder_date=report_date, ext='ta') writer = pd.ExcelWriter(ta_output_dir + '/proxy_report.xlsx', engine='xlsxwriter') futures_flat_curve = futures_flat_curve[[ 'ticker', 'ticker_head', 'ticker_class', 'volume', 'tr_days_2roll', 'proxy_ticker', 'add_2_proxy' ]] futures_flat_curve.to_excel(writer, sheet_name='proxy_report') writer.save() if 'con' not in kwargs.keys(): con.close()
def test_ib_reconciler(): app = ib_reconciler() con = msu.get_my_sql_connection() app.con = con position_frame = tpm.get_position_4portfolio( trade_date_to=cu.get_doubledate()) position_frame['con_id'] = np.nan position_frame['ib_position'] = np.nan position_frame['ib_symbol'] = '' position_frame.reset_index(drop=True, inplace=True) app.position_frame = position_frame app.connect(client_id=1) app.run()
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 get_options_price_from_db(**kwargs): con = msu.get_my_sql_connection(**kwargs) if 'ticker' in kwargs.keys(): filter_string = 'WHERE ticker=\'' + kwargs['ticker'] + '\'' elif 'ticker_head' in kwargs.keys(): filter_string = 'WHERE ticker_head=\'' + kwargs['ticker_head'] + '\'' else: filter_string = '' if 'settle_date' in kwargs.keys(): filter_string = filter_string + ' and price_date=' + str(kwargs['settle_date']) if 'delta_target' in kwargs.keys(): filter_string = filter_string + ' and abs(' + str(kwargs['delta_target']) + '-delta) = ' +\ '(SELECT min(abs('+ str(kwargs['delta_target']) + '-delta)) FROM daily_option_price ' + filter_string + ')' if 'strike' in kwargs.keys(): filter_string = filter_string + ' and strike=' + str(kwargs['strike']) if 'option_type' in kwargs.keys(): filter_string = filter_string + ' and option_type=\'' + kwargs['option_type'] + '\'' if 'column_names' in kwargs.keys(): column_names = kwargs['column_names'] else: column_names = ['id', 'option_type', 'strike', 'cal_dte', 'tr_dte', 'close_price', 'volume', 'open_interest'] sql_query = 'SELECT ' + ",".join(column_names) + ' FROM daily_option_price ' + filter_string cur = con.cursor() cur.execute(sql_query) data = cur.fetchall() if 'con' not in kwargs.keys(): con.close() if (not data) and ('strike' in kwargs.keys()) and ('option_type' in kwargs.keys()): data_frame_out = pd.DataFrame([(np.nan, kwargs['option_type'], kwargs['strike'], np.nan, np.nan, np.nan, np.nan, np.nan)], columns=column_names) else: data_frame_out = pd.DataFrame(data, columns=column_names) for x in ['close_price', 'delta', 'imp_vol', 'strike', 'theta', 'vega']: if x in column_names: data_frame_out[x] = data_frame_out[x].astype('float64') return data_frame_out
def get_strategy_class_historical_pnls(**kwargs): con = msu.get_my_sql_connection(**kwargs) if 'as_of_date' in kwargs.keys(): as_of_date = kwargs['as_of_date'] else: as_of_date = exp.doubledate_shift_bus_days() strategy_frame = ts.select_strategies(con=con, open_date_to=as_of_date) strategy_frame['strategy_class'] = [ sc.convert_from_string_to_dictionary( string_input=strategy_frame['description_string'][x]) ['strategy_class'] for x in range(len(strategy_frame.index)) ] unique_strategy_class_list = strategy_frame['strategy_class'].unique() time_series_list = [None] * len(unique_strategy_class_list) for i in range(len(unique_strategy_class_list)): strategy_frame_selected = strategy_frame[ strategy_frame['strategy_class'] == unique_strategy_class_list[i]] pnl_out = [ tpnl.get_strategy_pnl( alias=x, as_of_date=as_of_date, con=con)['pnl_frame'][['settle_date', 'total_pnl']] for x in strategy_frame_selected['alias'] ] [x.set_index('settle_date', drop=True, inplace=True) for x in pnl_out] time_series_list[i] = pd.concat(pnl_out, axis=1).fillna(0).sum(axis=1) if 'con' not in kwargs.keys(): con.close() merged_pnl = pd.concat(time_series_list, axis=1, keys=unique_strategy_class_list).fillna(0) merged_pnl['total'] = merged_pnl.sum(axis=1) output_dir = dn.get_directory_name(ext='daily') writer = pd.ExcelWriter(output_dir + '/historical_performance_' + str(as_of_date) + '.xlsx', engine='xlsxwriter') merged_pnl.to_excel(writer, sheet_name='timeSeries') writer.save() return merged_pnl
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 get_expiration_from_db(**kwargs): # instrument=options or futures con = msu.get_my_sql_connection(**kwargs) sql_query = 'SELECT expiration_date FROM futures_master.symbol WHERE instrument=\'' + kwargs['instrument'] + '\' and ticker=\'' + kwargs['ticker'] + '\'' cur = con.cursor() cur.execute(sql_query) data = cur.fetchall() if 'con' not in kwargs.keys(): con.close() return data[0][0]
def calc_volume_interest_4ticker(**kwargs): con = msu.get_my_sql_connection(**kwargs) sql_query = 'SELECT sum(volume),sum(open_interest) FROM futures_master.daily_option_price WHERE ticker=\'' + kwargs['ticker'] + '\'' + \ ' and price_date=' + str(kwargs['settle_date']) cur = con.cursor() cur.execute(sql_query) data = cur.fetchall() if 'con' not in kwargs.keys(): con.close() return {'volume': int(data[0][0]) if data[0][0] is not None else np.NaN, 'open_interest': int(data[0][1]) if data[0][1] is not None else np.NaN}
def get_futures_last_price_date_4ticker(**kwargs): if 'ticker' in kwargs.keys(): sql_query = 'SELECT max(dp.price_date) FROM symbol as sym INNER JOIN daily_price as dp ON dp.symbol_id = sym.id WHERE sym.ticker=\'' + kwargs['ticker'] + '\'' elif 'ticker_head' in kwargs.keys(): sql_query = 'SELECT max(price_date) FROM futures_master.daily_price WHERE ticker_head=\'' + kwargs['ticker_head'] + '\'' con = msu.get_my_sql_connection(**kwargs) cur = con.cursor() cur.execute(sql_query) data = cur.fetchall() if 'con' not in kwargs.keys(): con.close() return int(data[0][0].strftime('%Y%m%d')) if data[0][0] is not None else None
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 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()
def get_strategy_id_from_alias(**kwargs): alias = kwargs['alias'] con = msu.get_my_sql_connection(**kwargs) cur = con.cursor() query_string = 'SELECT id FROM strategy WHERE alias=\'' + alias + '\'' cur.execute(query_string) data = cur.fetchall() if not data: strategy_id = None else: strategy_id = data[0][0] if 'con' not in kwargs.keys(): con.close() return strategy_id
def symbol_id_ticker_conversion(**kwargs): con = msu.get_my_sql_connection(**kwargs) ticker = kwargs['ticker'] con = kwargs['con'] cur = con.cursor() cur.execute('SELECT * FROM futures_master.symbol WHERE ticker=\'' + ticker + '\'') data = cur.fetchall() if data[0][2] == ticker: output = data[0][0] if 'con' not in kwargs.keys(): con.close() return output
def symbol_id_ticker_conversion(**kwargs): con = msu.get_my_sql_connection(**kwargs) ticker = kwargs["ticker"] con = kwargs["con"] cur = con.cursor() cur.execute("SELECT * FROM futures_master.symbol WHERE ticker='" + ticker + "'") data = cur.fetchall() if data[0][2] == ticker: output = data[0][0] if "con" not in kwargs.keys(): con.close() return output
def generate_spread_carry_followup_report(**kwargs): 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 con = msu.get_my_sql_connection(**kwargs) ta_output_dir = dn.get_dated_directory_extension(folder_date=as_of_date, ext='ta') if 'writer' in kwargs.keys(): writer = kwargs['writer'] else: writer = pd.ExcelWriter(ta_output_dir + '/followup.xlsx', engine='xlsxwriter') strategy_frame = ts.get_open_strategies(**kwargs) strategy_class_list = [sc.convert_from_string_to_dictionary(string_input=strategy_frame['description_string'][x])['strategy_class'] for x in range(len(strategy_frame.index))] spread_carry_indx = [x == 'spread_carry' for x in strategy_class_list] spread_carry_frame = strategy_frame[spread_carry_indx] results = [sf.get_results_4strategy(alias=spread_carry_frame['alias'].iloc[x], strategy_info_output=spread_carry_frame.iloc[x], con=con) for x in range(len(spread_carry_frame.index))] results_frame_list = [results[x]['results_frame'] for x in range(len(results)) if results[x]['success']] spread_carry_followup_frame = pd.concat(results_frame_list) spread_carry_followup_frame.to_excel(writer, sheet_name='sc') worksheet_sc = writer.sheets['sc'] worksheet_sc.freeze_panes(1, 0) worksheet_sc.autofilter(0, 0, len(spread_carry_followup_frame.index), len(spread_carry_followup_frame.columns)) if 'con' not in kwargs.keys(): con.close() return writer
def get_futures_last_price_date_4ticker(**kwargs): if 'ticker' in kwargs.keys(): sql_query = 'SELECT max(dp.price_date) FROM symbol as sym INNER JOIN daily_price as dp ON dp.symbol_id = sym.id WHERE sym.ticker=\'' + kwargs[ 'ticker'] + '\'' elif 'ticker_head' in kwargs.keys(): sql_query = 'SELECT max(price_date) FROM futures_master.daily_price WHERE ticker_head=\'' + kwargs[ 'ticker_head'] + '\'' con = msu.get_my_sql_connection(**kwargs) cur = con.cursor() cur.execute(sql_query) data = cur.fetchall() if 'con' not in kwargs.keys(): con.close() return int( data[0][0].strftime('%Y%m%d')) if data[0][0] is not None else None
def get_trades_4strategy_alias(**kwargs): con = msu.get_my_sql_connection(**kwargs) sql_query = 'SELECT tr.id, tr.ticker, tr.option_type, tr.strike_price, tr.trade_price, tr.trade_quantity, tr.trade_date, tr.instrument, tr.real_tradeQ ' + \ 'FROM strategy as str INNER JOIN trades as tr ON tr.strategy_id=str.id ' + \ 'WHERE str.alias=\'' + kwargs['alias'] + '\'' cur = con.cursor() cur.execute(sql_query) data = cur.fetchall() if 'con' not in kwargs.keys(): con.close() trade_frame = pd.DataFrame(data, columns=['id', 'ticker', 'option_type', 'strike_price', 'trade_price', 'trade_quantity', 'trade_date', 'instrument', 'real_tradeQ']) trade_frame['trade_price'] = [float(x) if x is not None else float('NaN') for x in trade_frame['trade_price'].values] trade_frame['trade_quantity'] = trade_frame['trade_quantity'].astype('float64') trade_frame['strike_price'] = trade_frame['strike_price'].astype('float64') return trade_frame
def strategy_hedge_report(**kwargs): current_date = cu.get_doubledate() con = msu.get_my_sql_connection(**kwargs) strategy_frame = tas.get_open_strategies(as_of_date=current_date,con=con) strategy_class_list = [sc.convert_from_string_to_dictionary(string_input=strategy_frame['description_string'][x])['strategy_class'] for x in range(len(strategy_frame.index))] hedge_indx = [x in ['vcs', 'scv','optionInventory'] for x in strategy_class_list] hedge_frame = strategy_frame[hedge_indx] #hedge_frame = hedge_frame[hedge_frame['alias'] != 'LNZ16V16VCS'] [hedge_strategy_against_delta(alias=x, con=con) for x in hedge_frame['alias']] if 'con' not in kwargs.keys(): con.close()
def get_trades_4portfolio(**kwargs): con = msu.get_my_sql_connection(**kwargs) if "trade_date_to" in kwargs.keys(): filter_string = "WHERE trade_date<=" + str(kwargs["trade_date_to"]) else: filter_string = "" sql_query = "SELECT ticker, trade_quantity, instrument, option_type, strike_price from trades " + filter_string cur = con.cursor() cur.execute(sql_query) data = cur.fetchall() if "con" not in kwargs.keys(): con.close() trade_frame = pd.DataFrame(data, columns=["ticker", "trade_quantity", "instrument", "option_type", "strike_price"]) return trade_frame
def generate_liquid_options_list_dataframe(**kwargs): settle_date = kwargs['settle_date'] con = msu.get_my_sql_connection(**kwargs) contract_list = [] for ticker_head in cmi.option_tickerhead_list: for ticker_month in cmi.get_option_contract_months(ticker_head=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(get_db_contract_list_filtered(expiration_date_from=settle_date, expiration_date_to=cu.doubledate_shift(settle_date, -max_cal_dte), ticker_head=ticker_head, ticker_month=ticker_month_num, con=con, instrument='options')) if 'con' not in kwargs.keys(): con.close() return pd.DataFrame(contract_list,columns=['id', 'ticker', 'expiration_date'])
def get_strategy_info_from_alias(**kwargs): alias = kwargs['alias'] con = msu.get_my_sql_connection(**kwargs) cur = con.cursor() query_string = 'SELECT * FROM strategy WHERE alias=\'' + alias + '\'' cur.execute(query_string) data = cur.fetchall() if 'con' not in kwargs.keys(): con.close() return {'id': data[0][0], 'alias': data[0][1], 'open_date': data[0][2], 'close_date': data[0][3], 'pnl': data[0][4], 'created_date': data[0][5], 'last_updated_date': data[0][6], 'description_string': data[0][7]}
def get_expiration_report(**kwargs): con = msu.get_my_sql_connection(**kwargs) portfolio_frame = get_portfolio_expirations(**kwargs) strategy_frame = ts.get_open_strategies(con=con, as_of_date=kwargs["report_date"]) expiration_list = [ get_strategy_expiration(con=con, alias=strategy_frame["alias"].iloc[x], as_of_date=kwargs["report_date"]) for x in range(len(strategy_frame.index)) ] expiration_list.append(portfolio_frame) if "con" not in kwargs.keys(): con.close() expiration_list = [x for x in expiration_list if not x.empty] expiration_frame = pd.concat(expiration_list) return expiration_frame.sort("tr_dte", ascending=True, inplace=False)
def get_open_strategies(**kwargs): con = msu.get_my_sql_connection(**kwargs) if 'as_of_date' in kwargs.keys(): as_of_date = kwargs['as_of_date'] else: as_of_date = int(tm.strftime('%Y%m%d')) cur = con.cursor() sql_query = 'SELECT * FROM futures_master.strategy WHERE close_date>=' + str(as_of_date) + ' and open_date<=' + str(as_of_date) cur.execute(sql_query) data = cur.fetchall() if 'con' not in kwargs.keys(): con.close() return pd.DataFrame(data,columns=['id','alias','open_date','close_date','pnl','created_date','last_updated_date','description_string'])
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()
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 get_futures_price_4ticker(**kwargs): if 'ticker' in kwargs.keys(): filter_string = 'WHERE sym.ticker=\'' + kwargs['ticker'] + '\'' elif 'ticker_head' in kwargs.keys(): filter_string = 'WHERE dp.ticker_head=\'' + kwargs['ticker_head'] + '\'' con = msu.get_my_sql_connection(**kwargs) if 'tr_dte_min' in kwargs.keys(): filter_string = filter_string + ' and dp.tr_dte>=' + str(kwargs['tr_dte_min']) if 'tr_dte_max' in kwargs.keys(): filter_string = filter_string + ' and dp.tr_dte<=' + str(kwargs['tr_dte_max']) if 'ticker_month' in kwargs.keys(): filter_string = filter_string + ' and dp.ticker_month=' + str(kwargs['ticker_month']) if 'date_from' in kwargs.keys(): filter_string = filter_string + ' and dp.price_date>=' + str(kwargs['date_from']) if 'date_to' in kwargs.keys(): filter_string = filter_string + ' and dp.price_date<=' + str(kwargs['date_to']) sql_query = 'SELECT dp.price_date, sym.ticker, dp.ticker_head, dp.ticker_month, ' + \ 'sym.ticker_year, dp.cal_dte, dp.tr_dte, ' \ 'dp.open_price, dp.high_price, dp.low_price, dp.close_price, dp.volume ' + \ 'FROM symbol as sym ' + \ 'INNER JOIN daily_price as dp ON dp.symbol_id = sym.id ' + \ filter_string + \ ' ORDER BY dp.price_date, dp.cal_dte' cur = con.cursor() cur.execute(sql_query) data = cur.fetchall() if 'con' not in kwargs.keys(): con.close() return pd.DataFrame(data,columns=['settle_date', 'ticker', 'ticker_head', 'ticker_month', 'ticker_year', 'cal_dte', 'tr_dte', 'open_price','high_price','low_price','close_price', 'volume'])
def get_portfolio_expirations(**kwargs): con = msu.get_my_sql_connection(**kwargs) position_frame = tpm.get_position_4portfolio(trade_date_to=kwargs["report_date"]) position_frame.reset_index(drop=True, inplace=True) futures_indx = position_frame["instrument"] == "F" position_frame.loc[futures_indx, "instrument"] = "futures" position_frame.loc[~futures_indx, "instrument"] = "options" position_frame["tr_dte"] = position_frame.apply( lambda x: exp.get_days2_expiration( ticker=x["ticker"], instrument=x["instrument"], date_to=kwargs["report_date"], con=con )["tr_dte"], axis=1, ) position_frame["alias"] = "Portfolio" if "con" not in kwargs.keys(): con.close() return position_frame