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 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_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_pca_seasonality_adjustments(**kwargs): ticker_head = kwargs['ticker_head'] if 'file_date_to' in kwargs.keys(): file_date_to = kwargs['file_date_to'] else: file_date_to = 20160219 if 'years_back' in kwargs.keys(): years_back = kwargs['years_back'] else: years_back = 10 if 'date_to' in kwargs.keys(): date_to = kwargs['date_to'] else: date_to = file_date_to date5_years_ago = cu.doubledate_shift(date_to, 5 * 365) backtest_output_dir = dn.get_directory_name(ext='backtest_results') file_name = ticker_head + '_' + str(file_date_to) + '_' + str( years_back) + '_z' if os.path.isfile(backtest_output_dir + '/curve_pca/' + file_name + '.pkl'): backtest_results = pd.read_pickle(backtest_output_dir + '/curve_pca/' + file_name + '.pkl') else: return pd.DataFrame.from_items([('monthSpread', [1] * 12 + [6] * 2), ('ticker_month_front', list(range(1, 13)) + [6] + [12]), ('z_seasonal_mean', [0] * 14)]) entire_report = pd.concat(backtest_results['report_results_list']) selected_report = entire_report[ (entire_report['report_date'] <= date_to) & (entire_report['report_date'] >= date5_years_ago)] selected_report = selected_report[(selected_report['tr_dte_front'] > 80) & (selected_report['monthSpread'] < 12)] grouped = selected_report.groupby(['monthSpread', 'ticker_month_front']) seasonality_adjustment = pd.DataFrame() seasonality_adjustment['monthSpread'] = ( grouped['monthSpread'].first()).values seasonality_adjustment['ticker_month_front'] = ( grouped['ticker_month_front'].first()).values seasonality_adjustment['z_seasonal_mean'] = (grouped['z'].mean()).values return seasonality_adjustment
def get_arma_signals(**kwargs): ticker_head = kwargs['ticker_head'] date_to = kwargs['date_to'] date2_years_ago = cu.doubledate_shift(date_to, 2*365) panel_data = gfp.get_futures_price_preloaded(ticker_head=ticker_head,settle_date_from=date2_years_ago,settle_date_to=date_to) panel_data = panel_data[panel_data['tr_dte']>=40] panel_data.sort(['settle_date','tr_dte'],ascending=[True,True],inplace=True) rolling_data = panel_data.drop_duplicates(subset=['settle_date'], take_last=False) rolling_data['percent_change'] = 100*rolling_data['change_1']/rolling_data['close_price'] rolling_data = rolling_data[rolling_data['percent_change'].notnull()] data_input = np.array(rolling_data['percent_change']) daily_noise = np.std(data_input) param1_list = [] param2_list = [] akaike_list = [] forecast_list = [] for i in range(0, 3): for j in range(0, 3): try: model_output = sm.tsa.ARMA(data_input, (i,j)).fit() except: continue param1_list.append(i) param2_list.append(j) akaike_list.append(model_output.aic) forecast_list.append(model_output.predict(len(data_input),len(data_input))[0]) result_frame = pd.DataFrame.from_items([('param1', param1_list), ('param2', param2_list), ('akaike', akaike_list), ('forecast', forecast_list)]) result_frame.sort('akaike',ascending=True,inplace=True) param1 = result_frame['param1'].iloc[0] param2 = result_frame['param2'].iloc[0] if (param1 == 0)&(param2 == 0): forecast = np.nan else: forecast = result_frame['forecast'].iloc[0] return {'success': True, 'forecast':forecast,'normalized_forecast': forecast/daily_noise, 'param1':param1,'param2':param2, 'normalized_target': 100*(rolling_data['change1_instant'].iloc[-1]/rolling_data['close_price'].iloc[-1])/daily_noise}
def get_pca_seasonality_adjustments(**kwargs): ticker_head = kwargs['ticker_head'] if 'file_date_to' in kwargs.keys(): file_date_to = kwargs['file_date_to'] else: file_date_to = 20160219 if 'years_back' in kwargs.keys(): years_back = kwargs['years_back'] else: years_back = 10 if 'date_to' in kwargs.keys(): date_to = kwargs['date_to'] else: date_to = file_date_to date5_years_ago = cu.doubledate_shift(date_to, 5*365) backtest_output_dir = dn.get_directory_name(ext='backtest_results') file_name = ticker_head + '_' + str(file_date_to) + '_' + str(years_back) + '_z' if os.path.isfile(backtest_output_dir + '/curve_pca/' + file_name + '.pkl'): backtest_results = pd.read_pickle(backtest_output_dir + '/curve_pca/' + file_name + '.pkl') else: return pd.DataFrame.from_items([('monthSpread',[1]*12+[6]*2), ('ticker_month_front',list(range(1,13))+[6]+[12]), ('z_seasonal_mean',[0]*14)]) entire_report = pd.concat(backtest_results['report_results_list']) selected_report = entire_report[(entire_report['report_date'] <= date_to) & (entire_report['report_date'] >= date5_years_ago)] selected_report = selected_report[(selected_report['tr_dte_front'] > 80)&(selected_report['monthSpread'] < 12)] grouped = selected_report.groupby(['monthSpread','ticker_month_front']) seasonality_adjustment = pd.DataFrame() seasonality_adjustment['monthSpread'] = (grouped['monthSpread'].first()).values seasonality_adjustment['ticker_month_front'] = (grouped['ticker_month_front'].first()).values seasonality_adjustment['z_seasonal_mean'] = (grouped['z'].mean()).values return seasonality_adjustment
def get_spread_carry_4tickerhead(**kwargs): ticker_head = kwargs['ticker_head'] report_date = kwargs['report_date'] if 'min_tr_dte' in kwargs.keys(): min_tr_dte = kwargs['min_tr_dte'] else: min_tr_dte = 15 if 'futures_data_dictionary' in kwargs.keys(): futures_data_dictionary = kwargs['futures_data_dictionary'] else: futures_data_dictionary = { ticker_head: gfp.get_futures_price_preloaded(ticker_head=ticker_head) } if 'datetime5_years_ago' in kwargs.keys(): datetime5_years_ago = kwargs['datetime5_years_ago'] else: date5_years_ago = cu.doubledate_shift(report_date, 5 * 365) datetime5_years_ago = cu.convert_doubledate_2datetime(date5_years_ago) daily_data = gfp.get_futures_price_preloaded( ticker_head=ticker_head, settle_date=report_date, futures_data_dictionary=futures_data_dictionary) daily_data = daily_data[(daily_data['tr_dte'] >= min_tr_dte) & ( daily_data['tr_dte'] <= max_tr_dte_limits[ticker_head])] if len(daily_data.index) > 1: carry_signals = fs.get_futures_spread_carry_signals( ticker_list=daily_data['ticker'].values, tr_dte_list=daily_data['tr_dte'].values, futures_data_dictionary=futures_data_dictionary, datetime5_years_ago=datetime5_years_ago, date_to=report_date) return {'success': True, 'carry_signals': carry_signals} else: return {'success': False, 'carry_signals': pd.DataFrame()}
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_backtesting_dates(**kwargs): date_to = kwargs['date_to'] years_back = kwargs['years_back'] if 'day_of_week' in kwargs.keys(): day_of_week = kwargs['day_of_week'] else: day_of_week = 2 date_from = cu.doubledate_shift(date_to, years_back*365) trading_calendar = exp.get_calendar_4ticker_head('CL') bday_us = CustomBusinessDay(calendar=trading_calendar) dts = pd.date_range(start=cu.convert_doubledate_2datetime(date_from), end=cu.convert_doubledate_2datetime(date_to), freq=bday_us) dts = dts[dts.dayofweek==day_of_week] return {'date_time_dates': dts, 'double_dates': [int(x.strftime('%Y%m%d')) for x in dts]}
def get_spread_carry_4tickerhead(**kwargs): ticker_head = kwargs['ticker_head'] report_date = kwargs['report_date'] if 'min_tr_dte' in kwargs.keys(): min_tr_dte = kwargs['min_tr_dte'] else: min_tr_dte = 10 if 'futures_data_dictionary' in kwargs.keys(): futures_data_dictionary = kwargs['futures_data_dictionary'] else: futures_data_dictionary = {ticker_head: gfp.get_futures_price_preloaded(ticker_head=ticker_head)} if 'datetime5_years_ago' in kwargs.keys(): datetime5_years_ago = kwargs['datetime5_years_ago'] else: date5_years_ago = cu.doubledate_shift(report_date,5*365) datetime5_years_ago = cu.convert_doubledate_2datetime(date5_years_ago) daily_data = gfp.get_futures_price_preloaded(ticker_head=ticker_head, settle_date=report_date, futures_data_dictionary=futures_data_dictionary) daily_data = daily_data[(daily_data['tr_dte'] >= min_tr_dte) & (daily_data['tr_dte'] <= max_tr_dte_limits[ticker_head])] if len(daily_data.index) > 1: carry_signals = fs.get_futures_spread_carry_signals(ticker_list=daily_data['ticker'].values, tr_dte_list=daily_data['tr_dte'].values, futures_data_dictionary=futures_data_dictionary, datetime5_years_ago=datetime5_years_ago, date_to=report_date) return {'success': True, 'carry_signals': carry_signals} else: return {'success': False, 'carry_signals': pd.DataFrame()}
def get_backtesting_dates(**kwargs): date_to = kwargs['date_to'] years_back = kwargs['years_back'] if 'day_of_week' in kwargs.keys(): day_of_week = kwargs['day_of_week'] else: day_of_week = 2 date_from = cu.doubledate_shift(date_to, years_back * 365) trading_calendar = exp.get_calendar_4ticker_head('CL') bday_us = CustomBusinessDay(calendar=trading_calendar) dts = pd.date_range(start=cu.convert_doubledate_2datetime(date_from), end=cu.convert_doubledate_2datetime(date_to), freq=bday_us) dts = dts[dts.dayofweek == day_of_week] return { 'date_time_dates': dts, 'double_dates': [int(x.strftime('%Y%m%d')) for x in dts] }
def get_futures_spread_carry_signals(**kwargs): ticker_list = kwargs['ticker_list'] date_to = kwargs['date_to'] if 'tr_dte_list' in kwargs.keys(): tr_dte_list = kwargs['tr_dte_list'] else: tr_dte_list = [ exp.get_futures_days2_expiration({ 'ticker': x, 'date_to': date_to }) for x in ticker_list ] if 'aggregation_method' in kwargs.keys( ) and 'contracts_back' in kwargs.keys(): aggregation_method = kwargs['aggregation_method'] contracts_back = kwargs['contracts_back'] else: amcb_output = opUtil.get_aggregation_method_contracts_back( cmi.get_contract_specs(ticker_list[0])) aggregation_method = amcb_output['aggregation_method'] contracts_back = amcb_output['contracts_back'] if 'use_last_as_current' in kwargs.keys(): use_last_as_current = kwargs['use_last_as_current'] else: use_last_as_current = False 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 [cmi.get_contract_specs(ticker_list[0])['ticker_head']] } if 'contract_multiplier' in kwargs.keys(): contract_multiplier = kwargs['contract_multiplier'] else: contract_multiplier = cmi.contract_multiplier[cmi.get_contract_specs( ticker_list[0])['ticker_head']] if 'datetime5_years_ago' in kwargs.keys(): datetime5_years_ago = kwargs['datetime5_years_ago'] else: date5_years_ago = cu.doubledate_shift(date_to, 5 * 365) datetime5_years_ago = cu.convert_doubledate_2datetime(date5_years_ago) aligned_output = opUtil.get_aligned_futures_data( contract_list=ticker_list, tr_dte_list=tr_dte_list, aggregation_method=aggregation_method, contracts_back=contracts_back, date_to=date_to, futures_data_dictionary=futures_data_dictionary, use_last_as_current=use_last_as_current) aligned_data = aligned_output['aligned_data'] current_data = aligned_output['current_data'] last5_years_indx = aligned_data['settle_date'] >= datetime5_years_ago data_last5_years = aligned_data[last5_years_indx] ticker1_list = [ current_data['c' + str(x + 1)]['ticker'] for x in range(len(ticker_list) - 1) ] ticker2_list = [ current_data['c' + str(x + 2)]['ticker'] for x in range(len(ticker_list) - 1) ] yield_current_list = [ 100 * (current_data['c' + str(x + 1)]['close_price'] - current_data['c' + str(x + 2)]['close_price']) / current_data['c' + str(x + 2)]['close_price'] for x in range(len(ticker_list) - 1) ] butterfly_current_list = [ 100 * (current_data['c' + str(x + 1)]['close_price'] - 2 * current_data['c' + str(x + 2)]['close_price'] + current_data['c' + str(x + 3)]['close_price']) / current_data['c' + str(x + 2)]['close_price'] for x in range(len(ticker_list) - 2) ] price_current_list = [ current_data['c' + str(x + 1)]['close_price'] - current_data['c' + str(x + 2)]['close_price'] for x in range(len(ticker_list) - 1) ] yield_history = [ 100 * (aligned_data['c' + str(x + 1)]['close_price'] - aligned_data['c' + str(x + 2)]['close_price']) / aligned_data['c' + str(x + 2)]['close_price'] for x in range(len(ticker_list) - 1) ] butterfly_history = [ 100 * (aligned_data['c' + str(x + 1)]['close_price'] - 2 * aligned_data['c' + str(x + 2)]['close_price'] + aligned_data['c' + str(x + 3)]['close_price']) / aligned_data['c' + str(x + 2)]['close_price'] for x in range(len(ticker_list) - 2) ] change_5_history = [ data_last5_years['c' + str(x + 1)]['change_5'] - data_last5_years['c' + str(x + 2)]['change_5'] for x in range(len(ticker_list) - 1) ] change5 = [ contract_multiplier * (current_data['c' + str(x + 1)]['change5'] - current_data['c' + str(x + 2)]['change5']) for x in range(len(ticker_list) - 1) ] change10 = [ contract_multiplier * (current_data['c' + str(x + 1)]['change10'] - current_data['c' + str(x + 2)]['change10']) for x in range(len(ticker_list) - 1) ] change20 = [ contract_multiplier * (current_data['c' + str(x + 1)]['change20'] - current_data['c' + str(x + 2)]['change20']) for x in range(len(ticker_list) - 1) ] front_tr_dte = [ current_data['c' + str(x + 1)]['tr_dte'] for x in range(len(ticker_list) - 1) ] q_list = [ stats.get_quantile_from_number({ 'x': yield_current_list[x], 'y': yield_history[x].values, 'clean_num_obs': max(100, round(3 * len(yield_history[x].values) / 4)) }) for x in range(len(ticker_list) - 1) ] butterfly_q_list = [ stats.get_quantile_from_number({ 'x': butterfly_current_list[x], 'y': butterfly_history[x].values[-40:], 'clean_num_obs': round(3 * len(butterfly_history[x].values[-40:]) / 4) }) for x in range(len(ticker_list) - 2) ] extreme_quantiles_list = [ stats.get_number_from_quantile( y=x.values[:-40], quantile_list=[10, 25, 35, 50, 65, 75, 90]) for x in butterfly_history ] butterfly_q10 = [x[0] for x in extreme_quantiles_list] butterfly_q25 = [x[1] for x in extreme_quantiles_list] butterfly_q35 = [x[2] for x in extreme_quantiles_list] butterfly_q50 = [x[3] for x in extreme_quantiles_list] butterfly_q65 = [x[4] for x in extreme_quantiles_list] butterfly_q75 = [x[5] for x in extreme_quantiles_list] butterfly_q90 = [x[6] for x in extreme_quantiles_list] butterfly_noise_list = [ stats.get_stdev(x=butterfly_history[i].values[-20:]) for i in range(len(ticker_list) - 2) ] butterfly_mean_list = [ stats.get_mean(x=butterfly_history[i].values[-10:]) for i in range(len(ticker_list) - 2) ] butterfly_z_list = [(butterfly_current_list[i] - butterfly_mean_list[i]) / butterfly_noise_list[i] for i in range(len(ticker_list) - 2)] percentile_vector = [ stats.get_number_from_quantile( y=change_5_history[x].values, quantile_list=[1, 15, 85, 99], clean_num_obs=max(100, round(3 * len(change_5_history[x].values) / 4))) for x in range(len(ticker_list) - 1) ] q1 = [x[0] for x in percentile_vector] q15 = [x[1] for x in percentile_vector] q85 = [x[2] for x in percentile_vector] q99 = [x[3] for x in percentile_vector] downside = [ contract_multiplier * (q1[x] + q15[x]) / 2 for x in range(len(q1)) ] upside = [ contract_multiplier * (q85[x] + q99[x]) / 2 for x in range(len(q1)) ] carry = [ contract_multiplier * (price_current_list[x] - price_current_list[x + 1]) for x in range(len(q_list) - 1) ] q_carry = [q_list[x] - q_list[x + 1] for x in range(len(q_list) - 1)] q_average = np.cumsum(q_list) / range(1, len(q_list) + 1) q_series = pd.Series(q_list) q_min = q_series.cummin().values q_max = q_series.cummax().values q_carry_average = [ q_average[x] - q_list[x + 1] for x in range(len(q_list) - 1) ] q_carry_max = [q_max[x] - q_list[x + 1] for x in range(len(q_list) - 1)] q_carry_min = [q_min[x] - q_list[x + 1] for x in range(len(q_list) - 1)] reward_risk = [ 5 * carry[x] / ((front_tr_dte[x + 1] - front_tr_dte[x]) * abs(downside[x + 1])) if carry[x] > 0 else 5 * carry[x] / ((front_tr_dte[x + 1] - front_tr_dte[x]) * upside[x + 1]) for x in range(len(carry)) ] return pd.DataFrame.from_dict({ 'ticker1': ticker1_list, 'ticker2': ticker2_list, 'ticker1L': [''] + ticker1_list[:-1], 'ticker2L': [''] + ticker2_list[:-1], 'ticker_head': cmi.get_contract_specs(ticker_list[0])['ticker_head'], 'front_tr_dte': front_tr_dte, 'front_tr_dteL': [np.NAN] + front_tr_dte[:-1], 'carry': [np.NAN] + carry, 'q_carry': [np.NAN] + q_carry, 'q_carry_average': [np.NAN] + q_carry_average, 'q_carry_max': [np.NAN] + q_carry_max, 'q_carry_min': [np.NAN] + q_carry_min, 'butterfly_q': [np.NAN] + butterfly_q_list, 'butterfly_z': [np.NAN] + butterfly_z_list, 'reward_risk': [np.NAN] + reward_risk, 'price': price_current_list, 'priceL': [np.NAN] + price_current_list[:-1], 'butterfly_q10': [np.NAN] + butterfly_q10, 'butterfly_q25': [np.NAN] + butterfly_q25, 'butterfly_q35': [np.NAN] + butterfly_q35, 'butterfly_q50': [np.NAN] + butterfly_q50, 'butterfly_q65': [np.NAN] + butterfly_q65, 'butterfly_q75': [np.NAN] + butterfly_q75, 'butterfly_q90': [np.NAN] + butterfly_q90, 'butterfly_mean': [np.NAN] + butterfly_mean_list, 'butterfly_noise': [np.NAN] + butterfly_noise_list, 'q': q_list, 'upside': upside, 'downside': downside, 'upsideL': [np.NAN] + upside[:-1], 'downsideL': [np.NAN] + downside[:-1], 'change5': change5, 'change10': change10, 'change20': change20 })
def get_curve_pca_report(**kwargs): ticker_head = kwargs['ticker_head'] date_to = kwargs['date_to'] if 'use_existing_filesQ' in kwargs.keys(): use_existing_filesQ = kwargs['use_existing_filesQ'] else: use_existing_filesQ = True output_dir = ts.create_strategy_output_dir(strategy_class='curve_pca', report_date=date_to) if os.path.isfile(output_dir + '/' + ticker_head + '.pkl') and use_existing_filesQ: pca_results = pd.read_pickle(output_dir + '/' + ticker_head + '.pkl') return {'pca_results': pca_results, 'success': True} date10_years_ago = cu.doubledate_shift(date_to, 10 * 365) datetime_to = cu.convert_doubledate_2datetime(date_to) if ticker_head == 'ED': num_contracts = 12 rolling_data = cd.get_rolling_curve_data(ticker_head=ticker_head, num_contracts=num_contracts, front_tr_dte_limit=10, date_from=date10_years_ago, date_to=date_to) if datetime_to != rolling_data[0].index[-1].to_datetime(): return {'pca_results': pd.DataFrame(), 'success': False} merged_data = pd.concat(rolling_data, axis=1, join='inner') total_range = list(range(len(rolling_data))) index_exclude = [len(total_range) - 1] month_spread = [3] * (len(rolling_data) - 1) elif ticker_head in ['CL', 'B']: num_monthly_contracts = 18 if ticker_head == 'CL': num_semiannual_contracts = 6 elif ticker_head == 'B': num_semiannual_contracts = 7 rolling_data_monthly = cd.get_rolling_curve_data( ticker_head=ticker_head, num_contracts=num_monthly_contracts, front_tr_dte_limit=10, date_from=date10_years_ago, date_to=date_to) rolling_data_semiannual = cd.get_rolling_curve_data( ticker_head=ticker_head, num_contracts=num_semiannual_contracts, front_tr_dte_limit=10, date_from=date10_years_ago, month_separation=6, date_to=date_to) if datetime_to != rolling_data_monthly[0].index[-1].to_pydatetime( ) or datetime_to != rolling_data_semiannual[0].index[-1].to_pydatetime( ): return {'pca_results': pd.DataFrame(), 'success': False} rolling_data_merged = pd.concat(rolling_data_semiannual, axis=1) annual_select = rolling_data_merged['ticker_month'].iloc[-1] % 12 == 0 rolling_data_annual = [ rolling_data_semiannual[x] for x in range(len(rolling_data_semiannual)) if annual_select.values[x] ] merged_data = pd.concat(rolling_data_monthly + rolling_data_semiannual + rolling_data_annual, axis=1, join='inner') total_range = list( range( len(rolling_data_monthly) + len(rolling_data_semiannual) + len(rolling_data_annual))) index_exclude = [ len(rolling_data_monthly) - 1, len(rolling_data_monthly) + len(rolling_data_semiannual) - 1, len(total_range) - 1 ] month_spread = [1] * (len(rolling_data_monthly) - 1) + [6] * ( len(rolling_data_semiannual) - 1) + [12] * (len(rolling_data_annual) - 1) yield_raw = [(merged_data['close_price'].ix[:, x] - merged_data['close_price'].ix[:, x + 1]) / merged_data['close_price'].ix[:, x + 1] for x in total_range if x not in index_exclude] yield_merged = pd.concat(yield_raw, axis=1) yield_data = 100 * yield_merged.values change5_raw = [ (merged_data['change5'].ix[:, x] - merged_data['change5'].ix[:, x + 1]) for x in total_range if x not in index_exclude ] change5_merged = pd.concat(change5_raw, axis=1) change5_data = change5_merged.values change10_raw = [(merged_data['change10'].ix[:, x] - merged_data['change10'].ix[:, x + 1]) for x in total_range if x not in index_exclude] change10_merged = pd.concat(change10_raw, axis=1) change10_data = change10_merged.values change20_raw = [(merged_data['change20'].ix[:, x] - merged_data['change20'].ix[:, x + 1]) for x in total_range if x not in index_exclude] change20_merged = pd.concat(change20_raw, axis=1) change20_data = change20_merged.values tr_dte_raw = [ merged_data['tr_dte'].ix[:, x] for x in total_range if x not in index_exclude ] tr_dte_merged = pd.concat(tr_dte_raw, axis=1) tr_dte_data = tr_dte_merged.values ticker_month_raw = [ merged_data['ticker_month'].ix[:, x] for x in total_range if x not in index_exclude ] ticker_month_merged = pd.concat(ticker_month_raw, axis=1) ticker_month_data = ticker_month_merged.values ticker1_list = [ merged_data['ticker'].ix[-1, x] for x in total_range if x not in index_exclude ] ticker2_list = [ merged_data['ticker'].ix[-1, x + 1] for x in total_range if x not in index_exclude ] price_list = [(merged_data['close_price'].ix[-1, x] - merged_data['close_price'].ix[-1, x + 1]) for x in total_range if x not in index_exclude] pca_out = stats.get_pca(data_input=yield_data, n_components=2) residuals = yield_data - pca_out['model_fit'] pca_results = pd.DataFrame.from_dict({ 'ticker1': ticker1_list, 'ticker2': ticker2_list, 'monthSpread': month_spread, 'tr_dte_front': tr_dte_data[-1], 'ticker_month_front': ticker_month_data[-1], 'residuals': residuals[-1], 'price': price_list, 'yield': yield_data[-1], 'z': (residuals[-1] - residuals.mean(axis=0)) / residuals.std(axis=0), 'factor_load1': pca_out['loadings'][0], 'factor_load2': pca_out['loadings'][1], 'change5': change5_data[-1], 'change10': change10_data[-1], 'change20': change20_data[-1] }) # notice that this date_to needs to me removed once we are done with backtesting seasonality_adjustment = fs.get_pca_seasonality_adjustments( ticker_head=ticker_head, date_to=date_to) pca_results = pd.merge(pca_results, seasonality_adjustment, how='left', on=['monthSpread', 'ticker_month_front']) pca_results['z2'] = pca_results['z'] - pca_results['z_seasonal_mean'] pca_results['residuals'] = pca_results['residuals'].round(3) pca_results['yield'] = pca_results['yield'].round(2) pca_results['z'] = pca_results['z'].round(2) pca_results['z2'] = pca_results['z2'].round(2) pca_results['z_seasonal_mean'] = pca_results['z_seasonal_mean'].round(2) pca_results['factor_load1'] = pca_results['factor_load1'].round(3) pca_results['factor_load2'] = pca_results['factor_load2'].round(3) pca_results.to_pickle(output_dir + '/' + ticker_head + '.pkl') return {'pca_results': pca_results, 'success': True}
def get_underlying_proxy_ticker(**kwargs): ticker = kwargs['ticker'] settle_date = kwargs['settle_date'] contract_specs_output = cmi.get_contract_specs(ticker) ticker_head = contract_specs_output['ticker_head'] ticker_class = contract_specs_output['ticker_class'] con = msu.get_my_sql_connection(**kwargs) if ticker_class in ['Livestock', 'Ag', 'Soft', 'Energy', 'STIR']: return {'ticker': ticker, 'add_2_proxy': 0} 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] } settle_date_from = cu.doubledate_shift(settle_date, 15) settle_datetime = cu.convert_doubledate_2datetime(settle_date) panel_data = gfp.get_futures_price_preloaded( ticker_head=ticker_head, settle_date_from=settle_date_from, settle_date_to=settle_date, futures_data_dictionary=futures_data_dictionary) last_day_data = panel_data[panel_data['settle_date'] == settle_datetime] last_day_data['tr_days_2roll'] = last_day_data.apply( lambda x: exp.get_days2_roll(ticker=x['ticker'], instrument='Futures', date_to=settle_date, con=con)['tr_days_2roll'], axis=1) last_day_data = last_day_data[last_day_data['tr_days_2roll'] >= 10] last_day_data.reset_index(drop=True, inplace=True) last_day_data.sort_values('volume', ascending=False, inplace=True) proxy_ticker = last_day_data['ticker'].iloc[0] ticker_data = panel_data.loc[panel_data['ticker'] == ticker, ['settle_date', 'close_price']] proxy_data = panel_data.loc[panel_data['ticker'] == proxy_ticker, ['settle_date', 'close_price']] merged_data = pd.merge(ticker_data, proxy_data, on=['settle_date'], how='inner') merged_data['add_to_proxy'] = merged_data['close_price_x'] - merged_data[ 'close_price_y'] add_2_proxy = merged_data['add_to_proxy'].mean() if 'con' not in kwargs.keys(): con.close() return {'ticker': proxy_ticker, 'add_2_proxy': add_2_proxy}
def get_futures_butterfly_signals(**kwargs): ticker_list = kwargs['ticker_list'] date_to = kwargs['date_to'] if 'tr_dte_list' in kwargs.keys(): tr_dte_list = kwargs['tr_dte_list'] else: tr_dte_list = [ exp.get_futures_days2_expiration({ 'ticker': x, 'date_to': date_to }) for x in ticker_list ] if 'aggregation_method' in kwargs.keys( ) and 'contracts_back' in kwargs.keys(): aggregation_method = kwargs['aggregation_method'] contracts_back = kwargs['contracts_back'] else: amcb_output = opUtil.get_aggregation_method_contracts_back( cmi.get_contract_specs(ticker_list[0])) aggregation_method = amcb_output['aggregation_method'] contracts_back = amcb_output['contracts_back'] if 'use_last_as_current' in kwargs.keys(): use_last_as_current = kwargs['use_last_as_current'] else: use_last_as_current = False 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 [cmi.get_contract_specs(ticker_list[0])['ticker_head']] } if 'contract_multiplier' in kwargs.keys(): contract_multiplier = kwargs['contract_multiplier'] else: contract_multiplier = cmi.contract_multiplier[cmi.get_contract_specs( ticker_list[0])['ticker_head']] if 'datetime5_years_ago' in kwargs.keys(): datetime5_years_ago = kwargs['datetime5_years_ago'] else: date5_years_ago = cu.doubledate_shift(date_to, 5 * 365) datetime5_years_ago = cu.convert_doubledate_2datetime(date5_years_ago) if 'datetime2_months_ago' in kwargs.keys(): datetime2_months_ago = kwargs['datetime2_months_ago'] else: date2_months_ago = cu.doubledate_shift(date_to, 60) datetime2_months_ago = cu.convert_doubledate_2datetime( date2_months_ago) aligned_output = opUtil.get_aligned_futures_data( contract_list=ticker_list, tr_dte_list=tr_dte_list, aggregation_method=aggregation_method, contracts_back=contracts_back, date_to=date_to, futures_data_dictionary=futures_data_dictionary, use_last_as_current=use_last_as_current) if not aligned_output['success']: return {'success': False} current_data = aligned_output['current_data'] aligned_data = aligned_output['aligned_data'] month_diff_1 = 12 * (current_data['c1']['ticker_year'] - current_data['c2']['ticker_year']) + ( current_data['c1']['ticker_month'] - current_data['c2']['ticker_month']) month_diff_2 = 12 * (current_data['c2']['ticker_year'] - current_data['c3']['ticker_year']) + ( current_data['c2']['ticker_month'] - current_data['c3']['ticker_month']) weight_11 = 2 * month_diff_2 / (month_diff_1 + month_diff_1) weight_12 = -2 weight_13 = 2 * month_diff_1 / (month_diff_1 + month_diff_1) price_1 = current_data['c1']['close_price'] price_2 = current_data['c2']['close_price'] price_3 = current_data['c3']['close_price'] linear_interp_price2 = (weight_11 * aligned_data['c1']['close_price'] + weight_13 * aligned_data['c3']['close_price']) / 2 butterfly_price = aligned_data['c1']['close_price'] - 2 * aligned_data[ 'c2']['close_price'] + aligned_data['c3']['close_price'] price_ratio = linear_interp_price2 / aligned_data['c2']['close_price'] linear_interp_price2_current = (weight_11 * price_1 + weight_13 * price_3) / 2 price_ratio_current = linear_interp_price2_current / price_2 q = stats.get_quantile_from_number({ 'x': price_ratio_current, 'y': price_ratio.values, 'clean_num_obs': max(100, round(3 * len(price_ratio.values) / 4)) }) qf = stats.get_quantile_from_number({ 'x': price_ratio_current, 'y': price_ratio.values[-40:], 'clean_num_obs': 30 }) recent_quantile_list = [ stats.get_quantile_from_number({ 'x': x, 'y': price_ratio.values[-40:], 'clean_num_obs': 30 }) for x in price_ratio.values[-40:] ] weight1 = weight_11 weight2 = weight_12 weight3 = weight_13 last5_years_indx = aligned_data['settle_date'] >= datetime5_years_ago last2_months_indx = aligned_data['settle_date'] >= datetime2_months_ago data_last5_years = aligned_data[last5_years_indx] yield1 = 100 * ( aligned_data['c1']['close_price'] - aligned_data['c2']['close_price']) / aligned_data['c2']['close_price'] yield2 = 100 * ( aligned_data['c2']['close_price'] - aligned_data['c3']['close_price']) / aligned_data['c3']['close_price'] yield1_last5_years = yield1[last5_years_indx] yield2_last5_years = yield2[last5_years_indx] yield1_current = 100 * ( current_data['c1']['close_price'] - current_data['c2']['close_price']) / current_data['c2']['close_price'] yield2_current = 100 * ( current_data['c2']['close_price'] - current_data['c3']['close_price']) / current_data['c3']['close_price'] butterfly_price_current = current_data['c1']['close_price']\ -2*current_data['c2']['close_price']\ +current_data['c3']['close_price'] #return {'yield1': yield1, 'yield2': yield2, 'yield1_current':yield1_current, 'yield2_current': yield2_current} yield_regress_output = stats.get_regression_results({ 'x': yield2, 'y': yield1, 'x_current': yield2_current, 'y_current': yield1_current, 'clean_num_obs': max(100, round(3 * len(yield1.values) / 4)) }) yield_regress_output_last5_years = stats.get_regression_results({ 'x': yield2_last5_years, 'y': yield1_last5_years, 'x_current': yield2_current, 'y_current': yield1_current, 'clean_num_obs': max(100, round(3 * len(yield1_last5_years.values) / 4)) }) bf_qz_frame_short = pd.DataFrame() bf_qz_frame_long = pd.DataFrame() if (len(yield1) >= 40) & (len(yield2) >= 40): recent_zscore_list = [ (yield1[-40 + i] - yield_regress_output['alpha'] - yield_regress_output['beta'] * yield2[-40 + i]) / yield_regress_output['residualstd'] for i in range(40) ] bf_qz_frame = pd.DataFrame.from_dict({ 'bf_price': butterfly_price.values[-40:], 'q': recent_quantile_list, 'zscore': recent_zscore_list }) bf_qz_frame = np.round(bf_qz_frame, 8) bf_qz_frame.drop_duplicates(['bf_price'], keep='last', inplace=True) # return bf_qz_frame bf_qz_frame_short = bf_qz_frame[(bf_qz_frame['zscore'] >= 0.6) & (bf_qz_frame['q'] >= 85)] bf_qz_frame_long = bf_qz_frame[(bf_qz_frame['zscore'] <= -0.6) & (bf_qz_frame['q'] <= 12)] if bf_qz_frame_short.empty: short_price_limit = np.NAN else: short_price_limit = bf_qz_frame_short['bf_price'].min() if bf_qz_frame_long.empty: long_price_limit = np.NAN else: long_price_limit = bf_qz_frame_long['bf_price'].max() zscore1 = yield_regress_output['zscore'] rsquared1 = yield_regress_output['rsquared'] zscore2 = yield_regress_output_last5_years['zscore'] rsquared2 = yield_regress_output_last5_years['rsquared'] second_spread_weight_1 = yield_regress_output['beta'] second_spread_weight_2 = yield_regress_output_last5_years['beta'] butterfly_5_change = data_last5_years['c1']['change_5']\ - (1+second_spread_weight_1)*data_last5_years['c2']['change_5']\ + second_spread_weight_1*data_last5_years['c3']['change_5'] butterfly_5_change_current = current_data['c1']['change_5']\ - (1+second_spread_weight_1)*current_data['c2']['change_5']\ + second_spread_weight_1*current_data['c3']['change_5'] butterfly_1_change = data_last5_years['c1']['change_1']\ - (1+second_spread_weight_1)*data_last5_years['c2']['change_1']\ + second_spread_weight_1*data_last5_years['c3']['change_1'] percentile_vector = stats.get_number_from_quantile( y=butterfly_5_change.values, quantile_list=[1, 15, 85, 99], clean_num_obs=max(100, round(3 * len(butterfly_5_change.values) / 4))) downside = contract_multiplier * (percentile_vector[0] + percentile_vector[1]) / 2 upside = contract_multiplier * (percentile_vector[2] + percentile_vector[3]) / 2 recent_5day_pnl = contract_multiplier * butterfly_5_change_current residuals = yield1 - yield_regress_output[ 'alpha'] - yield_regress_output['beta'] * yield2 regime_change_ind = (residuals[last5_years_indx].mean() - residuals.mean()) / residuals.std() seasonal_residuals = residuals[aligned_data['c1']['ticker_month'] == current_data['c1']['ticker_month']] seasonal_clean_residuals = seasonal_residuals[np.isfinite( seasonal_residuals)] clean_residuals = residuals[np.isfinite(residuals)] contract_seasonality_ind = ( seasonal_clean_residuals.mean() - clean_residuals.mean()) / clean_residuals.std() yield1_quantile_list = stats.get_number_from_quantile( y=yield1, quantile_list=[10, 90]) yield2_quantile_list = stats.get_number_from_quantile( y=yield2, quantile_list=[10, 90]) noise_ratio = (yield1_quantile_list[1] - yield1_quantile_list[0]) / ( yield2_quantile_list[1] - yield2_quantile_list[0]) daily_noise_recent = stats.get_stdev(x=butterfly_1_change.values[-20:], clean_num_obs=15) daily_noise_past = stats.get_stdev( x=butterfly_1_change.values, clean_num_obs=max(100, round(3 * len(butterfly_1_change.values) / 4))) recent_vol_ratio = daily_noise_recent / daily_noise_past alpha1 = yield_regress_output['alpha'] residuals_last5_years = residuals[last5_years_indx] residuals_last2_months = residuals[last2_months_indx] residual_current = yield1_current - alpha1 - second_spread_weight_1 * yield2_current z3 = (residual_current - residuals_last5_years.mean()) / residuals.std() z4 = (residual_current - residuals_last2_months.mean()) / residuals.std() yield_change = (alpha1 + second_spread_weight_1 * yield2_current - yield1_current) / (1 + second_spread_weight_1) new_yield1 = yield1_current + yield_change new_yield2 = yield2_current - yield_change price_change1 = 100 * ( (price_2 * (new_yield1 + 100) / 100) - price_1) / (200 + new_yield1) price_change2 = 100 * ( (price_3 * (new_yield2 + 100) / 100) - price_2) / (200 + new_yield2) theo_pnl = contract_multiplier * ( 2 * price_change1 - 2 * second_spread_weight_1 * price_change2) aligned_data['residuals'] = residuals aligned_output['aligned_data'] = aligned_data grouped = aligned_data.groupby(aligned_data['c1']['cont_indx']) aligned_data['shifted_residuals'] = grouped['residuals'].shift(-5) aligned_data['residual_change'] = aligned_data[ 'shifted_residuals'] - aligned_data['residuals'] mean_reversion = stats.get_regression_results({ 'x': aligned_data['residuals'].values, 'y': aligned_data['residual_change'].values, 'clean_num_obs': max(100, round(3 * len(yield1.values) / 4)) }) theo_spread_move_output = su.calc_theo_spread_move_from_ratio_normalization( ratio_time_series=price_ratio.values[-40:], starting_quantile=qf, num_price=linear_interp_price2_current, den_price=current_data['c2']['close_price'], favorable_quantile_move_list=[5, 10, 15, 20, 25]) theo_pnl_list = [ x * contract_multiplier * 2 for x in theo_spread_move_output['theo_spread_move_list'] ] return { 'success': True, 'aligned_output': aligned_output, 'q': q, 'qf': qf, 'theo_pnl_list': theo_pnl_list, 'ratio_target_list': theo_spread_move_output['ratio_target_list'], 'weight1': weight1, 'weight2': weight2, 'weight3': weight3, 'zscore1': zscore1, 'rsquared1': rsquared1, 'zscore2': zscore2, 'rsquared2': rsquared2, 'zscore3': z3, 'zscore4': z4, 'zscore5': zscore1 - regime_change_ind, 'zscore6': zscore1 - contract_seasonality_ind, 'zscore7': zscore1 - regime_change_ind - contract_seasonality_ind, 'theo_pnl': theo_pnl, 'regime_change_ind': regime_change_ind, 'contract_seasonality_ind': contract_seasonality_ind, 'second_spread_weight_1': second_spread_weight_1, 'second_spread_weight_2': second_spread_weight_2, 'downside': downside, 'upside': upside, 'yield1': yield1, 'yield2': yield2, 'yield1_current': yield1_current, 'yield2_current': yield2_current, 'bf_price': butterfly_price_current, 'short_price_limit': short_price_limit, 'long_price_limit': long_price_limit, 'noise_ratio': noise_ratio, 'alpha1': alpha1, 'alpha2': yield_regress_output_last5_years['alpha'], 'residual_std1': yield_regress_output['residualstd'], 'residual_std2': yield_regress_output_last5_years['residualstd'], 'recent_vol_ratio': recent_vol_ratio, 'recent_5day_pnl': recent_5day_pnl, 'price_1': price_1, 'price_2': price_2, 'price_3': price_3, 'last5_years_indx': last5_years_indx, 'price_ratio': price_ratio, 'mean_reversion_rsquared': mean_reversion['rsquared'], 'mean_reversion_signif': (mean_reversion['conf_int'][1, :] < 0).all() }
def get_aligned_futures_data(**kwargs): contract_list = kwargs['contract_list'] aggregation_method = kwargs['aggregation_method'] contracts_back = kwargs['contracts_back'] date_to = kwargs['date_to'] if 'use_last_as_current' in kwargs.keys(): use_last_as_current = kwargs['use_last_as_current'] else: use_last_as_current = False if 'tr_dte_list' in kwargs.keys(): tr_dte_list = kwargs['tr_dte_list'] else: tr_dte_list = [exp.get_futures_days2_expiration({'ticker': x,'date_to': date_to}) for x in contract_list] if 'futures_data_dictionary' in kwargs.keys(): futures_data_dictionary = kwargs['futures_data_dictionary'] else: ticker_head_list = [cmi.get_contract_specs(x)['ticker_head'] for x in contract_list] futures_data_dictionary = {x: gfp.get_futures_price_preloaded(ticker_head=x) for x in ticker_head_list} date_from = cu.doubledate_shift(date_to, 2*3650) date_to_datetime = cu.convert_doubledate_2datetime(date_to) date_from_datetime = cu.convert_doubledate_2datetime(date_from) tr_days_half_band_width_selected = tr_days_half_band_with[aggregation_method] num_contracts = len(contract_list) contract_specs_list = [cmi.get_contract_specs(x) for x in contract_list] data_frame_list = [] for i in range(num_contracts): futures_data_frame = futures_data_dictionary[contract_specs_list[i]['ticker_head']] selection_indx = (futures_data_frame['tr_dte'] >= tr_dte_list[i]-tr_days_half_band_width_selected)& \ (futures_data_frame['tr_dte'] <= tr_dte_list[i]+tr_days_half_band_width_selected)& \ (futures_data_frame['cal_dte'] >= 0)& \ (futures_data_frame['settle_date'] >= date_from_datetime) & \ (futures_data_frame['settle_date'] <= date_to_datetime) futures_data_frame = futures_data_frame[selection_indx] data_frame_list.append(futures_data_frame) cont_indx_list_rolls = [get_cont_indx_list_history({'current_year': x['ticker_year'], 'current_month': x['ticker_month_num'], 'aggregation_method': aggregation_method, 'contracts_back': contracts_back}) for x in contract_specs_list] merged_dataframe_list = [None]*contracts_back for i in range(contracts_back): if sum([(data_frame_list[j]['cont_indx'] == cont_indx_list_rolls[j][i]).any() for j in range(len(contract_list))])<len(contract_list): continue contract_data_list = [None]*num_contracts for k in range(num_contracts): contract_data_list[k] = data_frame_list[k][data_frame_list[k]['cont_indx']==cont_indx_list_rolls[k][i]] contract_data_list[k].set_index('settle_date',inplace=True) merged_dataframe_list[i] = pd.concat(contract_data_list, axis=1, join='inner',keys=['c'+ str(x+1) for x in range(num_contracts)]) aligned_dataframe = pd.concat(merged_dataframe_list) aligned_dataframe.sort_index(inplace=True) aligned_dataframe['tr_dte_match'] = abs(aligned_dataframe['c1']['tr_dte']-tr_dte_list[0]) aligned_dataframe['settle_date'] = aligned_dataframe.index aligned_dataframe.sort(['settle_date','tr_dte_match'],ascending=[True,True],inplace=True) aligned_dataframe.drop_duplicates('settle_date',inplace=True) if use_last_as_current: current_data = aligned_dataframe.iloc[-1] else: current_data = aligned_dataframe.loc[cu.convert_doubledate_2datetime(date_to)] return {'aligned_data': aligned_dataframe, 'current_data': current_data}
def get_ics_signals(**kwargs): ticker = kwargs['ticker'] #print(ticker) date_to = kwargs['date_to'] con = msu.get_my_sql_connection(**kwargs) ticker_list = ticker.split('-') #print(ticker_list) ticker_head_list = [ cmi.get_contract_specs(x)['ticker_head'] for x in ticker_list ] ticker_class = cmi.ticker_class[ticker_head_list[0]] 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 list(set(ticker_head_list)) } if 'datetime5_years_ago' in kwargs.keys(): datetime5_years_ago = kwargs['datetime5_years_ago'] else: date5_years_ago = cu.doubledate_shift(date_to, 5 * 365) datetime5_years_ago = cu.convert_doubledate_2datetime(date5_years_ago) if 'num_days_back_4intraday' in kwargs.keys(): num_days_back_4intraday = kwargs['num_days_back_4intraday'] else: num_days_back_4intraday = 5 tr_dte_list = [ exp.get_days2_expiration(ticker=x, date_to=date_to, instrument='futures', con=con)['tr_dte'] for x in ticker_list ] amcb_output = [ opUtil.get_aggregation_method_contracts_back(cmi.get_contract_specs(x)) for x in ticker_list ] aggregation_method = max([x['aggregation_method'] for x in amcb_output]) contracts_back = min([x['contracts_back'] for x in amcb_output]) contract_multiplier = cmi.contract_multiplier[ticker_head_list[0]] aligned_output = opUtil.get_aligned_futures_data( contract_list=ticker_list, tr_dte_list=tr_dte_list, aggregation_method=aggregation_method, contracts_back=contracts_back, date_to=date_to, futures_data_dictionary=futures_data_dictionary, use_last_as_current=True) aligned_data = aligned_output['aligned_data'] last5_years_indx = aligned_data['settle_date'] >= datetime5_years_ago data_last5_years = aligned_data[last5_years_indx] data_last5_years['spread_pnl_1'] = aligned_data['c1'][ 'change_1'] - aligned_data['c2']['change_1'] percentile_vector = stats.get_number_from_quantile( y=data_last5_years['spread_pnl_1'].values, quantile_list=[1, 15, 85, 99], clean_num_obs=max(100, round(3 * len(data_last5_years.index) / 4))) downside = contract_multiplier * (percentile_vector[0] + percentile_vector[1]) / 2 upside = contract_multiplier * (percentile_vector[2] + percentile_vector[3]) / 2 date_list = [ exp.doubledate_shift_bus_days(double_date=date_to, shift_in_days=x) for x in reversed(range(1, num_days_back_4intraday)) ] date_list.append(date_to) intraday_data = opUtil.get_aligned_futures_data_intraday( contract_list=[ticker], date_list=date_list) intraday_data['time_stamp'] = [ x.to_datetime() for x in intraday_data.index ] intraday_data['settle_date'] = intraday_data['time_stamp'].apply( lambda x: x.date()) end_hour = cmi.last_trade_hour_minute[ticker_head_list[0]] start_hour = cmi.first_trade_hour_minute[ticker_head_list[0]] if ticker_class == 'Ag': start_hour1 = dt.time(0, 45, 0, 0) end_hour1 = dt.time(7, 45, 0, 0) selection_indx = [ x for x in range(len(intraday_data.index)) if ((intraday_data['time_stamp'].iloc[x].time() < end_hour1) and (intraday_data['time_stamp'].iloc[x].time() >= start_hour1)) or ((intraday_data['time_stamp'].iloc[x].time() < end_hour) and (intraday_data['time_stamp'].iloc[x].time() >= start_hour)) ] else: selection_indx = [ x for x in range(len(intraday_data.index)) if (intraday_data.index[x].to_datetime().time() < end_hour) and ( intraday_data.index[x].to_datetime().time() >= start_hour) ] intraday_data = intraday_data.iloc[selection_indx] intraday_mean5 = np.nan intraday_std5 = np.nan intraday_mean2 = np.nan intraday_std2 = np.nan intraday_mean1 = np.nan intraday_std1 = np.nan if len(intraday_data.index) > 0: intraday_data['mid_p'] = (intraday_data['c1']['best_bid_p'] + intraday_data['c1']['best_ask_p']) / 2 intraday_mean5 = intraday_data['mid_p'].mean() intraday_std5 = intraday_data['mid_p'].std() intraday_data_last2days = intraday_data[ intraday_data['settle_date'] >= cu.convert_doubledate_2datetime( date_list[-2]).date()] intraday_data_yesterday = intraday_data[ intraday_data['settle_date'] == cu.convert_doubledate_2datetime( date_list[-1]).date()] intraday_mean2 = intraday_data_last2days['mid_p'].mean() intraday_std2 = intraday_data_last2days['mid_p'].std() intraday_mean1 = intraday_data_yesterday['mid_p'].mean() intraday_std1 = intraday_data_yesterday['mid_p'].std() if 'con' not in kwargs.keys(): con.close() return { 'downside': downside, 'upside': upside, 'front_tr_dte': tr_dte_list[0], 'intraday_mean5': intraday_mean5, 'intraday_std5': intraday_std5, 'intraday_mean2': intraday_mean2, 'intraday_std2': intraday_std2, 'intraday_mean1': intraday_mean1, 'intraday_std1': intraday_std1 }
def get_results_4tickerhead(**kwargs): ticker_head = kwargs['ticker_head'] date_to = kwargs['date_to'] ticker_class = cmi.ticker_class[ticker_head] indicator_list = [ 'change_1_high_volume', 'change_5_high_volume', 'change_10_high_volume', 'change_20_high_volume', 'change_1_low_volume', 'change_5_low_volume', 'change_10_low_volume', 'change_20_low_volume', 'change_1Normalized', 'change_5Normalized', 'change_10Normalized', 'change_20Normalized', 'comm_net_change_1_normalized', 'comm_net_change_2_normalized', 'comm_net_change_4_normalized', 'spec_net_change_1_normalized', 'spec_net_change_2_normalized', 'spec_net_change_4_normalized', 'comm_z', 'spec_z' ] date_from = cu.doubledate_shift(date_to, 5 * 365) datetime_to = cu.convert_doubledate_2datetime(date_to) panel_data = gfp.get_futures_price_preloaded(ticker_head=ticker_head, settle_date_from=date_from, settle_date_to=date_to) panel_data = panel_data[panel_data['tr_dte'] >= 40] panel_data.sort(['settle_date', 'tr_dte'], ascending=[True, True], inplace=True) rolling_data = panel_data.drop_duplicates(subset=['settle_date'], take_last=False) daily_noise = np.std(rolling_data['change_1']) average_volume = rolling_data['volume'].mean() rolling_data['change_40'] = pd.rolling_sum(rolling_data['change_1'], 40, min_periods=30) rolling_data['change_20'] = pd.rolling_sum(rolling_data['change_1'], 20, min_periods=15) rolling_data['change_10'] = pd.rolling_sum(rolling_data['change_1'], 10, min_periods=7) rolling_data['change_1Normalized'] = rolling_data['change_1'] / daily_noise rolling_data['change_5Normalized'] = rolling_data['change_5'] / daily_noise rolling_data[ 'change_10Normalized'] = rolling_data['change_10'] / daily_noise rolling_data[ 'change_20Normalized'] = rolling_data['change_20'] / daily_noise rolling_data[ 'change_40Normalized'] = rolling_data['change_40'] / daily_noise rolling_data['change1Normalized'] = rolling_data['change1'] / daily_noise rolling_data['change1_InstantNormalized'] = rolling_data[ 'change1_instant'] / daily_noise rolling_data['change1_InstantNormalized'] = rolling_data[ 'change1_instant'] / daily_noise rolling_data['high1_InstantNormalized'] = ( rolling_data['high1_instant'] - rolling_data['close_price']) / daily_noise rolling_data['low1_InstantNormalized'] = ( rolling_data['low1_instant'] - rolling_data['close_price']) / daily_noise rolling_data['change5Normalized'] = rolling_data['change5'] / daily_noise rolling_data['change10Normalized'] = rolling_data['change10'] / daily_noise rolling_data['change20Normalized'] = rolling_data['change20'] / daily_noise rolling_data['volume_mean20'] = pd.rolling_mean(rolling_data['volume'], 20, min_periods=15) rolling_data['volume_mean10'] = pd.rolling_mean(rolling_data['volume'], 10, min_periods=7) rolling_data['volume_mean5'] = pd.rolling_mean(rolling_data['volume'], 5, min_periods=4) rolling_data['volume_mean20Normalized'] = rolling_data[ 'volume_mean20'] / average_volume rolling_data['volume_mean10Normalized'] = rolling_data[ 'volume_mean10'] / average_volume rolling_data['volume_mean5Normalized'] = rolling_data[ 'volume_mean5'] / average_volume rolling_data['volume_Normalized'] = rolling_data['volume'] / average_volume rolling_data['change_1_high_volume'] = ( rolling_data['volume'] > average_volume) * rolling_data['change_1Normalized'] rolling_data['change_5_high_volume'] = ( rolling_data['volume_mean5'] > average_volume) * rolling_data['change_5Normalized'] rolling_data['change_10_high_volume'] = ( rolling_data['volume_mean10'] > average_volume) * rolling_data['change_10Normalized'] rolling_data['change_20_high_volume'] = ( rolling_data['volume_mean20'] > average_volume) * rolling_data['change_20Normalized'] rolling_data['change_1_low_volume'] = ( rolling_data['volume'] <= average_volume) * rolling_data['change_1Normalized'] rolling_data['change_5_low_volume'] = ( rolling_data['volume_mean5'] <= average_volume) * rolling_data['change_5Normalized'] rolling_data['change_10_low_volume'] = ( rolling_data['volume_mean10'] <= average_volume) * rolling_data['change_10Normalized'] rolling_data['change_20_low_volume'] = ( rolling_data['volume_mean20'] <= average_volume) * rolling_data['change_20Normalized'] cot_output = cot.get_cot_data(ticker_head=ticker_head, date_from=date_from, date_to=date_to) dictionary_out = { 'vote1': np.nan, 'vote1_instant': np.nan, 'vote12_instant': np.nan, 'vote13_instant': np.nan, 'vote5': np.nan, 'vote10': np.nan, 'vote20': np.nan, 'regress_forecast1': np.nan, 'regress_forecast2': np.nan, 'regress_forecast3': np.nan, 'svr_forecast1': np.nan, 'svr_forecast2': np.nan, 'norm_pnl1': np.nan, 'norm_pnl1Instant': np.nan, 'long_tight_stop_pnl1Instant': np.nan, 'long_loose_stop_pnl1Instant': np.nan, 'short_tight_stop_pnl1Instant': np.nan, 'short_loose_stop_pnl1Instant': np.nan, 'norm_pnl5': np.nan, 'norm_pnl10': np.nan, 'norm_pnl20': np.nan, 'rolling_data': pd.DataFrame() } for ind in indicator_list: dictionary_out[ind] = np.nan if len(cot_output.index) < 20: return dictionary_out cot_net = pd.DataFrame() if ticker_class in ['FX', 'STIR', 'Index', 'Treasury']: cot_net['comm_net'] = cot_output['Dealer Longs'] - cot_output[ 'Dealer Shorts'] cot_net['spec_net'] = cot_output['Asset Manager Longs'] - cot_output[ 'Asset Manager Shorts'] + cot_output[ 'Leveraged Funds Longs'] - cot_output['Leveraged Funds Shorts'] else: cot_net['comm_net'] = cot_output[ 'Producer/Merchant/Processor/User Longs'] - cot_output[ 'Producer/Merchant/Processor/User Shorts'] cot_net['spec_net'] = cot_output['Money Manager Longs'] - cot_output[ 'Money Manager Shorts'] cot_net['comm_net_change_1'] = cot_net['comm_net'] - cot_net[ 'comm_net'].shift(1) cot_net['comm_net_change_2'] = cot_net['comm_net'] - cot_net[ 'comm_net'].shift(2) cot_net['comm_net_change_4'] = cot_net['comm_net'] - cot_net[ 'comm_net'].shift(4) cot_net['spec_net_change_1'] = cot_net['spec_net'] - cot_net[ 'spec_net'].shift(1) cot_net['spec_net_change_2'] = cot_net['spec_net'] - cot_net[ 'spec_net'].shift(2) cot_net['spec_net_change_4'] = cot_net['spec_net'] - cot_net[ 'spec_net'].shift(4) comm_net_change_1_avg = np.std(cot_net['comm_net_change_1']) comm_net_change_2_avg = np.std(cot_net['comm_net_change_2']) comm_net_change_4_avg = np.std(cot_net['comm_net_change_4']) spec_net_change_1_avg = np.std(cot_net['spec_net_change_1']) spec_net_change_2_avg = np.std(cot_net['spec_net_change_2']) spec_net_change_4_avg = np.std(cot_net['spec_net_change_4']) cot_net['comm_net_change_1_normalized'] = cot_net[ 'comm_net_change_1'] / comm_net_change_1_avg cot_net['comm_net_change_2_normalized'] = cot_net[ 'comm_net_change_2'] / comm_net_change_2_avg cot_net['comm_net_change_4_normalized'] = cot_net[ 'comm_net_change_4'] / comm_net_change_4_avg cot_net['spec_net_change_1_normalized'] = cot_net[ 'spec_net_change_1'] / spec_net_change_1_avg cot_net['spec_net_change_2_normalized'] = cot_net[ 'spec_net_change_2'] / spec_net_change_2_avg cot_net['spec_net_change_4_normalized'] = cot_net[ 'spec_net_change_4'] / spec_net_change_4_avg cot_net['comm_z'] = (cot_net['comm_net'] - np.mean( cot_net['comm_net'])) / np.std(cot_net['comm_net']) cot_net['spec_z'] = (cot_net['spec_net'] - np.mean( cot_net['spec_net'])) / np.std(cot_net['spec_net']) cot_net['settle_date'] = cot_net.index cot_net['settle_date'] = [ x + dt.timedelta(days=3) for x in cot_net['settle_date'] ] combined_data = pd.merge(rolling_data, cot_net, how='left', on='settle_date') combined_data['comm_net_change_1_normalized'] = combined_data[ 'comm_net_change_1_normalized'].fillna(method='pad') combined_data['comm_net_change_2_normalized'] = combined_data[ 'comm_net_change_2_normalized'].fillna(method='pad') combined_data['comm_net_change_4_normalized'] = combined_data[ 'comm_net_change_4_normalized'].fillna(method='pad') combined_data['spec_net_change_1_normalized'] = combined_data[ 'spec_net_change_1_normalized'].fillna(method='pad') combined_data['spec_net_change_2_normalized'] = combined_data[ 'spec_net_change_2_normalized'].fillna(method='pad') combined_data['spec_net_change_4_normalized'] = combined_data[ 'spec_net_change_4_normalized'].fillna(method='pad') combined_data['comm_z'] = combined_data['comm_z'].fillna(method='pad') combined_data['spec_z'] = combined_data['spec_z'].fillna(method='pad') test_data = combined_data[combined_data['settle_date'] == datetime_to] training_data = combined_data[combined_data['settle_date'] < datetime_to + dt.timedelta(days=-30)] if test_data.empty or training_data.empty: return dictionary_out sharp1_list = [] sharp1_instant_list = [] sharp5_list = [] sharp10_list = [] sharp20_list = [] higher_level_list = [] lower_level_list = [] for i in range(len(indicator_list)): selected_data = training_data[training_data[ indicator_list[i]].notnull()] indicator_levels = stats.get_number_from_quantile( y=selected_data[indicator_list[i]].values, quantile_list=[10, 90]) lower_level_list.append(indicator_levels[0]) higher_level_list.append(indicator_levels[1]) low_data = selected_data[ selected_data[indicator_list[i]] < indicator_levels[0]] high_data = selected_data[ selected_data[indicator_list[i]] > indicator_levels[1]] high_data['pnl1'] = high_data['change1Normalized'] low_data['pnl1'] = -low_data['change1Normalized'] high_data['pnl1_instant'] = high_data['change1_InstantNormalized'] low_data['pnl1_instant'] = -low_data['change1_InstantNormalized'] high_data['pnl5'] = high_data['change5Normalized'] low_data['pnl5'] = -low_data['change5Normalized'] high_data['pnl10'] = high_data['change10Normalized'] low_data['pnl10'] = -low_data['change10Normalized'] high_data['pnl20'] = high_data['change20Normalized'] low_data['pnl20'] = -low_data['change20Normalized'] merged_data = pd.concat([high_data, low_data]) sharp1_list.append(16 * merged_data['pnl1'].mean() / merged_data['pnl1'].std()) sharp1_instant_list.append(16 * merged_data['pnl1_instant'].mean() / merged_data['pnl1_instant'].std()) sharp5_list.append(7.2 * merged_data['pnl5'].mean() / merged_data['pnl5'].std()) sharp10_list.append(5.1 * merged_data['pnl10'].mean() / merged_data['pnl10'].std()) sharp20_list.append(3.5 * merged_data['pnl20'].mean() / merged_data['pnl20'].std()) sharp_frame = pd.DataFrame.from_items([('indicator', indicator_list), ('lower_level', lower_level_list), ('higher_level', higher_level_list), ('sharp1', sharp1_list), ('sharp1_instant', sharp1_instant_list), ('sharp5', sharp5_list), ('sharp10', sharp10_list), ('sharp20', sharp20_list)]) vote1 = 0 for i in range(len(indicator_list)): indicator_value = test_data[indicator_list[i]].iloc[0] selected_sharp_row = sharp_frame[sharp_frame['indicator'] == indicator_list[i]] if (selected_sharp_row['sharp1'].iloc[0] > 0.75) & ( indicator_value > selected_sharp_row['higher_level'].iloc[0]): vote1 += 1 elif (selected_sharp_row['sharp1'].iloc[0] > 0.75) & ( indicator_value < selected_sharp_row['lower_level'].iloc[0]): vote1 -= 1 elif (selected_sharp_row['sharp1'].iloc[0] < -0.75) & ( indicator_value > selected_sharp_row['higher_level'].iloc[0]): vote1 -= 1 elif (selected_sharp_row['sharp1'].iloc[0] < -0.75) & ( indicator_value < selected_sharp_row['lower_level'].iloc[0]): vote1 += 1 vote1_instant = 0 for i in range(len(indicator_list)): indicator_value = test_data[indicator_list[i]].iloc[0] selected_sharp_row = sharp_frame[sharp_frame['indicator'] == indicator_list[i]] if (selected_sharp_row['sharp1_instant'].iloc[0] > 0.75) & ( indicator_value > selected_sharp_row['higher_level'].iloc[0]): vote1_instant += 1 elif (selected_sharp_row['sharp1_instant'].iloc[0] > 0.75) & ( indicator_value < selected_sharp_row['lower_level'].iloc[0]): vote1_instant -= 1 elif (selected_sharp_row['sharp1_instant'].iloc[0] < -0.75) & ( indicator_value > selected_sharp_row['higher_level'].iloc[0]): vote1_instant -= 1 elif (selected_sharp_row['sharp1_instant'].iloc[0] < -0.75) & ( indicator_value < selected_sharp_row['lower_level'].iloc[0]): vote1_instant += 1 vote12_instant = 0 indicator_t_list = [ 'change_1_high_volume', 'change_5_high_volume', 'change_10_high_volume', 'change_20_high_volume', 'change_1_low_volume', 'change_5_low_volume', 'change_10_low_volume', 'change_20_low_volume' ] for i in range(len(indicator_t_list)): indicator_value = test_data[indicator_t_list[i]].iloc[0] selected_sharp_row = sharp_frame[sharp_frame['indicator'] == indicator_t_list[i]] if (selected_sharp_row['sharp1_instant'].iloc[0] > 0.75) & ( indicator_value > selected_sharp_row['higher_level'].iloc[0]): vote12_instant += 1 elif (selected_sharp_row['sharp1_instant'].iloc[0] > 0.75) & ( indicator_value < selected_sharp_row['lower_level'].iloc[0]): vote12_instant -= 1 elif (selected_sharp_row['sharp1_instant'].iloc[0] < -0.75) & ( indicator_value > selected_sharp_row['higher_level'].iloc[0]): vote12_instant -= 1 elif (selected_sharp_row['sharp1_instant'].iloc[0] < -0.75) & ( indicator_value < selected_sharp_row['lower_level'].iloc[0]): vote12_instant += 1 vote13_instant = 0 for i in range(len(indicator_t_list)): indicator_value = test_data[indicator_t_list[i]].iloc[0] selected_sharp_row = sharp_frame[sharp_frame['indicator'] == indicator_t_list[i]] if (selected_sharp_row['sharp1_instant'].iloc[0] > 1) & ( indicator_value > selected_sharp_row['higher_level'].iloc[0]): vote13_instant += 1 elif (selected_sharp_row['sharp1_instant'].iloc[0] > 1) & ( indicator_value < selected_sharp_row['lower_level'].iloc[0]): vote13_instant -= 1 elif (selected_sharp_row['sharp1_instant'].iloc[0] < -1) & ( indicator_value > selected_sharp_row['higher_level'].iloc[0]): vote13_instant -= 1 elif (selected_sharp_row['sharp1_instant'].iloc[0] < -1) & ( indicator_value < selected_sharp_row['lower_level'].iloc[0]): vote13_instant += 1 vote5 = 0 for i in range(len(indicator_list)): indicator_value = test_data[indicator_list[i]].iloc[0] selected_sharp_row = sharp_frame[sharp_frame['indicator'] == indicator_list[i]] if (selected_sharp_row['sharp5'].iloc[0] > 0.75) & ( indicator_value > selected_sharp_row['higher_level'].iloc[0]): vote5 += 1 elif (selected_sharp_row['sharp5'].iloc[0] > 0.75) & ( indicator_value < selected_sharp_row['lower_level'].iloc[0]): vote5 -= 1 elif (selected_sharp_row['sharp5'].iloc[0] < -0.75) & ( indicator_value > selected_sharp_row['higher_level'].iloc[0]): vote5 -= 1 elif (selected_sharp_row['sharp5'].iloc[0] < -0.75) & ( indicator_value < selected_sharp_row['lower_level'].iloc[0]): vote5 += 1 vote10 = 0 for i in range(len(indicator_list)): indicator_value = test_data[indicator_list[i]].iloc[0] selected_sharp_row = sharp_frame[sharp_frame['indicator'] == indicator_list[i]] if (selected_sharp_row['sharp10'].iloc[0] > 0.75) & ( indicator_value > selected_sharp_row['higher_level'].iloc[0]): vote10 += 1 elif (selected_sharp_row['sharp10'].iloc[0] > 0.75) & ( indicator_value < selected_sharp_row['lower_level'].iloc[0]): vote10 -= 1 elif (selected_sharp_row['sharp10'].iloc[0] < -0.75) & ( indicator_value > selected_sharp_row['higher_level'].iloc[0]): vote10 -= 1 elif (selected_sharp_row['sharp10'].iloc[0] < -0.75) & ( indicator_value < selected_sharp_row['lower_level'].iloc[0]): vote10 += 1 vote20 = 0 for i in range(len(indicator_list)): indicator_value = test_data[indicator_list[i]].iloc[0] selected_sharp_row = sharp_frame[sharp_frame['indicator'] == indicator_list[i]] if (selected_sharp_row['sharp20'].iloc[0] > 0.75) & ( indicator_value > selected_sharp_row['higher_level'].iloc[0]): vote20 += 1 elif (selected_sharp_row['sharp20'].iloc[0] > 0.75) & ( indicator_value < selected_sharp_row['lower_level'].iloc[0]): vote20 -= 1 elif (selected_sharp_row['sharp20'].iloc[0] < -0.75) & ( indicator_value > selected_sharp_row['higher_level'].iloc[0]): vote20 -= 1 elif (selected_sharp_row['sharp20'].iloc[0] < -0.75) & ( indicator_value < selected_sharp_row['lower_level'].iloc[0]): vote20 += 1 #svr_rbf = SVR(kernel='rbf', C=1e3, gamma=0.1) #svr_forecast = svr_rbf.fit(x,y).predict(test_data[indicator_list].values)[0] #svc_rbf1 = SVC(kernel='rbf', C=1, gamma=0.1) #svc_forecast1 = svc_rbf1.fit(x,y).predict(test_data[indicator_list].values)[0] regress_forecast1 = np.nan regress_forecast2 = np.nan regress_forecast3 = np.nan svr_forecast1 = np.nan svr_forecast2 = np.nan try: regress_input = training_data[[ 'change_1Normalized', 'change_10Normalized', 'change1_InstantNormalized' ]].dropna() y = regress_input['change1_InstantNormalized'] X = regress_input[['change_1Normalized', 'change_10Normalized']] X = sm.add_constant(X) params1 = sm.OLS(y, X).fit().params regress_forecast1 = params1['const']+\ params1['change_1Normalized']*test_data['change_1Normalized'].iloc[0]+\ params1['change_10Normalized']*test_data['change_10Normalized'].iloc[0] except: pass try: regress_input = training_data[[ 'change_1Normalized', 'change_10Normalized', 'change1_InstantNormalized', 'comm_net_change_1_normalized' ]].dropna() y = regress_input['change1_InstantNormalized'] X = regress_input[[ 'change_1Normalized', 'change_10Normalized', 'comm_net_change_1_normalized' ]] X = sm.add_constant(X) params2 = sm.OLS(y, X).fit().params regress_forecast2 = params2['const']+\ params2['change_1Normalized']*test_data['change_1Normalized'].iloc[0]+\ params2['change_10Normalized']*test_data['change_10Normalized'].iloc[0]+\ params2['comm_net_change_1_normalized']*test_data['comm_net_change_1_normalized'].iloc[0] except: pass try: regress_input = training_data[[ 'change_1Normalized', 'change_10Normalized', 'change1_InstantNormalized', 'spec_net_change_1_normalized' ]].dropna() y = regress_input['change1_InstantNormalized'] X = regress_input[[ 'change_1Normalized', 'change_10Normalized', 'spec_net_change_1_normalized' ]] X = sm.add_constant(X) params3 = sm.OLS(y, X).fit().params regress_forecast3 = params3['const']+\ params3['change_1Normalized']*test_data['change_1Normalized'].iloc[0]+\ params3['change_10Normalized']*test_data['change_10Normalized'].iloc[0]+\ params3['spec_net_change_1_normalized']*test_data['spec_net_change_1_normalized'].iloc[0] except: pass regress_input = training_data[[ 'change_1Normalized', 'change_10Normalized', 'change1_InstantNormalized', 'comm_net_change_1_normalized' ]].dropna() try: svr_rbf1 = SVR(kernel='rbf', C=1, gamma=0.04) y = regress_input['change1_InstantNormalized'] X = regress_input[[ 'change_1Normalized', 'change_10Normalized', 'comm_net_change_1_normalized' ]] svr_forecast1 = svr_rbf1.fit(X, y).predict(test_data[[ 'change_1Normalized', 'change_10Normalized', 'comm_net_change_1_normalized' ]].values)[0] except: pass try: svr_rbf2 = SVR(kernel='rbf', C=50, gamma=0.04) y = regress_input['change1_InstantNormalized'] X = regress_input[[ 'change_1Normalized', 'change_10Normalized', 'comm_net_change_1_normalized' ]] svr_forecast2 = svr_rbf2.fit(X, y).predict(test_data[[ 'change_1Normalized', 'change_10Normalized', 'comm_net_change_1_normalized' ]].values)[0] except: pass if test_data['low1_InstantNormalized'].iloc[0] < -0.25: long_tight_stop_pnl1Instant = -0.25 else: long_tight_stop_pnl1Instant = test_data[ 'change1_InstantNormalized'].iloc[0] if test_data['low1_InstantNormalized'].iloc[0] < -0.5: long_loose_stop_pnl1Instant = -0.5 else: long_loose_stop_pnl1Instant = test_data[ 'change1_InstantNormalized'].iloc[0] if test_data['high1_InstantNormalized'].iloc[0] > 0.25: short_tight_stop_pnl1Instant = -0.25 else: short_tight_stop_pnl1Instant = -test_data[ 'change1_InstantNormalized'].iloc[0] if test_data['high1_InstantNormalized'].iloc[0] > 0.5: short_loose_stop_pnl1Instant = -0.5 else: short_loose_stop_pnl1Instant = -test_data[ 'change1_InstantNormalized'].iloc[0] dictionary_out['vote1'] = vote1 dictionary_out['vote1_instant'] = vote1_instant dictionary_out['vote12_instant'] = vote12_instant dictionary_out['vote13_instant'] = vote13_instant dictionary_out['vote5'] = vote5 dictionary_out['vote10'] = vote10 dictionary_out['vote20'] = vote20 dictionary_out['regress_forecast1'] = regress_forecast1 dictionary_out['regress_forecast2'] = regress_forecast2 dictionary_out['regress_forecast3'] = regress_forecast3 dictionary_out['svr_forecast1'] = svr_forecast1 dictionary_out['svr_forecast2'] = svr_forecast2 dictionary_out['norm_pnl1'] = test_data['change1Normalized'].iloc[0] dictionary_out['norm_pnl1Instant'] = test_data[ 'change1_InstantNormalized'].iloc[0] dictionary_out['long_tight_stop_pnl1Instant'] = long_tight_stop_pnl1Instant dictionary_out['long_loose_stop_pnl1Instant'] = long_loose_stop_pnl1Instant dictionary_out[ 'short_tight_stop_pnl1Instant'] = short_tight_stop_pnl1Instant dictionary_out[ 'short_loose_stop_pnl1Instant'] = short_loose_stop_pnl1Instant dictionary_out['norm_pnl5'] = test_data['change5Normalized'].iloc[0] dictionary_out['norm_pnl10'] = test_data['change10Normalized'].iloc[0] dictionary_out['norm_pnl20'] = test_data['change20Normalized'].iloc[0] dictionary_out['rolling_data'] = rolling_data for ind in indicator_list: dictionary_out[ind] = test_data[ind].iloc[0] return dictionary_out
def get_curve_pca_report(**kwargs): ticker_head = kwargs['ticker_head'] date_to = kwargs['date_to'] if 'use_existing_filesQ' in kwargs.keys(): use_existing_filesQ = kwargs['use_existing_filesQ'] else: use_existing_filesQ = True output_dir = ts.create_strategy_output_dir(strategy_class='curve_pca', report_date=date_to) if os.path.isfile(output_dir + '/' + ticker_head + '.pkl') and use_existing_filesQ: pca_results = pd.read_pickle(output_dir + '/' + ticker_head + '.pkl') return {'pca_results': pca_results, 'success': True} date10_years_ago = cu.doubledate_shift(date_to, 10*365) datetime_to = cu.convert_doubledate_2datetime(date_to) if ticker_head == 'ED': num_contracts = 12 rolling_data = cd.get_rolling_curve_data(ticker_head=ticker_head, num_contracts=num_contracts, front_tr_dte_limit=10, date_from=date10_years_ago, date_to=date_to) if datetime_to != rolling_data[0].index[-1].to_datetime(): return {'pca_results': pd.DataFrame(), 'success': False} merged_data = pd.concat(rolling_data, axis=1, join='inner') total_range = list(range(len(rolling_data))) index_exclude = [len(total_range)-1] month_spread = [3]*(len(rolling_data)-1) elif ticker_head in ['CL', 'B']: num_monthly_contracts = 18 if ticker_head == 'CL': num_semiannual_contracts = 6 elif ticker_head == 'B': num_semiannual_contracts = 7 rolling_data_monthly = cd.get_rolling_curve_data(ticker_head=ticker_head, num_contracts=num_monthly_contracts, front_tr_dte_limit=10, date_from=date10_years_ago, date_to=date_to) rolling_data_semiannual = cd.get_rolling_curve_data(ticker_head=ticker_head, num_contracts=num_semiannual_contracts, front_tr_dte_limit=10, date_from=date10_years_ago, month_separation=6, date_to=date_to) if datetime_to != rolling_data_monthly[0].index[-1].to_datetime() or datetime_to != rolling_data_semiannual[0].index[-1].to_datetime(): return {'pca_results': pd.DataFrame(), 'success': False} rolling_data_merged = pd.concat(rolling_data_semiannual, axis=1) annual_select = rolling_data_merged['ticker_month'].iloc[-1] % 12 == 0 rolling_data_annual = [rolling_data_semiannual[x] for x in range(len(rolling_data_semiannual)) if annual_select.values[x]] merged_data = pd.concat(rolling_data_monthly+rolling_data_semiannual+rolling_data_annual, axis=1, join='inner') total_range = list(range(len(rolling_data_monthly)+len(rolling_data_semiannual)+len(rolling_data_annual))) index_exclude = [len(rolling_data_monthly)-1,len(rolling_data_monthly)+len(rolling_data_semiannual)-1, len(total_range)-1] month_spread = [1]*(len(rolling_data_monthly)-1)+[6]*(len(rolling_data_semiannual)-1)+[12]*(len(rolling_data_annual)-1) yield_raw = [(merged_data['close_price'].ix[:, x]-merged_data['close_price'].ix[:, x+1]) / merged_data['close_price'].ix[:, x+1] for x in total_range if x not in index_exclude] yield_merged = pd.concat(yield_raw, axis=1) yield_data = 100*yield_merged.values change5_raw = [(merged_data['change5'].ix[:, x]-merged_data['change5'].ix[:, x+1]) for x in total_range if x not in index_exclude] change5_merged = pd.concat(change5_raw, axis=1) change5_data = change5_merged.values change10_raw = [(merged_data['change10'].ix[:, x]-merged_data['change10'].ix[:, x+1]) for x in total_range if x not in index_exclude] change10_merged = pd.concat(change10_raw, axis=1) change10_data = change10_merged.values change20_raw = [(merged_data['change20'].ix[:, x]-merged_data['change20'].ix[:, x+1]) for x in total_range if x not in index_exclude] change20_merged = pd.concat(change20_raw, axis=1) change20_data = change20_merged.values tr_dte_raw = [merged_data['tr_dte'].ix[:, x] for x in total_range if x not in index_exclude] tr_dte_merged = pd.concat(tr_dte_raw, axis=1) tr_dte_data = tr_dte_merged.values ticker_month_raw = [merged_data['ticker_month'].ix[:, x] for x in total_range if x not in index_exclude] ticker_month_merged = pd.concat(ticker_month_raw, axis=1) ticker_month_data = ticker_month_merged.values ticker1_list = [merged_data['ticker'].ix[-1, x] for x in total_range if x not in index_exclude] ticker2_list = [merged_data['ticker'].ix[-1, x+1] for x in total_range if x not in index_exclude] price_list = [(merged_data['close_price'].ix[-1, x]-merged_data['close_price'].ix[-1, x+1]) for x in total_range if x not in index_exclude] pca_out = stats.get_pca(data_input=yield_data, n_components=2) residuals = yield_data-pca_out['model_fit'] pca_results = pd.DataFrame.from_items([('ticker1',ticker1_list), ('ticker2',ticker2_list), ('monthSpread',month_spread), ('tr_dte_front', tr_dte_data[-1]), ('ticker_month_front', ticker_month_data[-1]), ('residuals',residuals[-1]), ('price',price_list), ('yield',yield_data[-1]), ('z', (residuals[-1]-residuals.mean(axis=0))/residuals.std(axis=0)), ('factor_load1',pca_out['loadings'][0]), ('factor_load2',pca_out['loadings'][1]), ('change5', change5_data[-1]), ('change10', change10_data[-1]), ('change20', change20_data[-1])]) # notice that this date_to needs to me removed once we are done with backtesting seasonality_adjustment = fs.get_pca_seasonality_adjustments(ticker_head=ticker_head,date_to=date_to) pca_results = pd.merge(pca_results, seasonality_adjustment,how='left',on=['monthSpread','ticker_month_front']) pca_results['z2'] = pca_results['z']-pca_results['z_seasonal_mean'] pca_results['residuals'] = pca_results['residuals'].round(3) pca_results['yield'] = pca_results['yield'].round(2) pca_results['z'] = pca_results['z'].round(2) pca_results['z2'] = pca_results['z2'].round(2) pca_results['z_seasonal_mean'] = pca_results['z_seasonal_mean'].round(2) pca_results['factor_load1'] = pca_results['factor_load1'].round(3) pca_results['factor_load2'] = pca_results['factor_load2'].round(3) pca_results.to_pickle(output_dir + '/' + ticker_head + '.pkl') return {'pca_results': pca_results, 'success': True}
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', 'option_pnl5', 'delta_pnl5', '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 get_fm_signals(**kwargs): ticker_head = kwargs['ticker_head'] date_to = kwargs['date_to'] #print(ticker_head) ticker_class = cmi.ticker_class[ticker_head] datetime_to = cu.convert_doubledate_2datetime(date_to) date5_years_ago = cu.doubledate_shift(date_to,5*365) datetime5_years_ago = cu.convert_doubledate_2datetime(date5_years_ago) data_out = gfp.get_futures_price_preloaded(ticker_head=ticker_head,settle_date_to=datetime_to) data4day = data_out[data_out['settle_date']==datetime_to] data4day = data4day[data4day['tr_dte']>=20] if len(data4day.index)<2: return {'ticker': '', 'comm_net': np.nan, 'spec_net': np.nan, 'comm_cot_index_slow': np.nan, 'comm_cot_index_fast': np.nan, 'trend_direction': np.nan,'curve_slope': np.nan, 'rsi_3': np.nan, 'rsi_7': np.nan, 'rsi_14': np.nan, 'change1': np.nan, 'change1_instant': np.nan, 'change5': np.nan, 'change10': np.nan, 'change20': np.nan, 'change1_dollar': np.nan, 'change1_instant_dollar': np.nan, 'change5_dollar': np.nan, 'change10_dollar': np.nan, 'change20_dollar': np.nan} data4day.sort_values('volume', ascending=False, inplace=True) data4day = data4day.iloc[:2] data4day.sort_values('tr_dte',ascending=True,inplace=True) ticker1 = data4day['ticker'].iloc[0] ticker2 = data4day['ticker'].iloc[1] tr_dte_list = [data4day['tr_dte'].iloc[0], data4day['tr_dte'].iloc[1]] amcb_output = opUtil.get_aggregation_method_contracts_back({'ticker_head': ticker_head, 'ticker_class': cmi.ticker_class[ticker_head]}) aggregation_method = amcb_output['aggregation_method'] contracts_back = amcb_output['contracts_back'] futures_data_dictionary = {ticker_head: data_out} aligned_output = opUtil.get_aligned_futures_data(contract_list=[ticker1,ticker2], tr_dte_list=tr_dte_list, aggregation_method=aggregation_method, contracts_back=contracts_back, date_to=date_to, futures_data_dictionary=futures_data_dictionary, use_last_as_current=False) aligned_data = aligned_output['aligned_data'] current_data = aligned_output['current_data'] yield1_current = 100*(current_data['c1']['close_price']-current_data['c2']['close_price'])/current_data['c2']['close_price'] yield1 = 100*(aligned_data['c1']['close_price']-aligned_data['c2']['close_price'])/aligned_data['c2']['close_price'] last5_years_indx = aligned_data['settle_date']>=datetime5_years_ago yield1_last5_years = yield1[last5_years_indx] curve_slope = stats.get_quantile_from_number({'x':yield1_current,'y': yield1_last5_years}) ticker_head_data = gfp.get_futures_price_preloaded(ticker_head=ticker_head) ticker_head_data = ticker_head_data[ticker_head_data['settle_date'] <= datetime_to] if ticker_class in ['Index', 'FX', 'Metal', 'Treasury', 'STIR']: merged_data = ticker_head_data[ticker_head_data['tr_dte'] >= 10] merged_data.sort_values(['settle_date', 'tr_dte'],ascending=True,inplace=True) merged_data.drop_duplicates(subset=['settle_date'], keep='first', inplace=True) merged_data['ma200'] = merged_data['close_price'].rolling(200).mean() merged_data['ma200_10'] = merged_data['ma200']-merged_data['ma200'].shift(10) else: data_out_front = ticker_head_data[ticker_head_data['tr_dte'] <= 60] data_out_front.drop_duplicates(subset=['settle_date'], keep='last', inplace=True) data_out_back = ticker_head_data[ticker_head_data['tr_dte'] > 60] data_out_back.drop_duplicates(subset=['settle_date'], keep='last', inplace=True) merged_data = pd.merge(data_out_front[['settle_date','tr_dte','close_price']],data_out_back[['tr_dte','close_price','settle_date','ticker','change_1']],how='inner',on='settle_date') merged_data['const_mat']=((merged_data['tr_dte_y']-60)*merged_data['close_price_x']+ (60-merged_data['tr_dte_x'])*merged_data['close_price_y'])/\ (merged_data['tr_dte_y']-merged_data['tr_dte_x']) merged_data['ma200'] = merged_data['const_mat'].rolling(200).mean() merged_data['ma200_10'] = merged_data['ma200']-merged_data['ma200'].shift(10) merged_data = merged_data[merged_data['settle_date']==datetime_to] if len(merged_data.index) == 0: trend_direction = np.nan elif merged_data['ma200_10'].iloc[0]>=0: trend_direction = 1 else: trend_direction = -1 ticker_data = gfp.get_futures_price_preloaded(ticker=ticker2,settle_date_to=datetime_to) ticker_data = ti.rsi(data_frame_input=ticker_data, change_field='change_1', period=3) ticker_data = ti.rsi(data_frame_input=ticker_data, change_field='change_1', period=7) ticker_data = ti.rsi(data_frame_input=ticker_data, change_field='change_1', period=14) cot_output = cot.get_cot_data(ticker_head=ticker_head, date_to=date_to) daily_noise = np.std(ticker_data['change_1'].iloc[-60:]) if len(cot_output.index)>0: if ticker_class in ['FX','STIR','Index','Treasury']: cot_output['comm_long'] = cot_output['Asset Manager Longs']+cot_output['Dealer Longs'] cot_output['comm_short'] = cot_output['Asset Manager Shorts']+cot_output['Dealer Shorts'] cot_output['comm_net'] = cot_output['comm_long']-cot_output['comm_short'] cot_output['spec_long'] = cot_output['Leveraged Funds Longs'] cot_output['spec_short'] = cot_output['Leveraged Funds Shorts'] cot_output['spec_net'] = cot_output['spec_long']-cot_output['spec_short'] else: cot_output['comm_long'] = cot_output['Producer/Merchant/Processor/User Longs']+cot_output['Swap Dealer Longs'] cot_output['comm_short'] = cot_output['Producer/Merchant/Processor/User Shorts']+cot_output['Swap Dealer Shorts'] cot_output['comm_net'] = cot_output['comm_long']-cot_output['comm_short'] cot_output['spec_long'] = cot_output['Money Manager Longs']+cot_output['Other Reportable Longs'] cot_output['spec_short'] = cot_output['Money Manager Shorts']+cot_output['Other Reportable Shorts'] cot_output['spec_net'] = cot_output['spec_long']-cot_output['spec_short'] if (datetime_to-cot_output['settle_date'].iloc[-1]).days>=10: comm_net = np.nan spec_net = np.nan else: comm_net = cot_output['comm_net'].iloc[-1] spec_net = cot_output['spec_net'].iloc[-1] comm_net_min_slow = cot_output['comm_net'].iloc[-156:].min() comm_net_max_slow = cot_output['comm_net'].iloc[-156:].max() comm_cot_index_slow = 100*(comm_net-comm_net_min_slow)/(comm_net_max_slow-comm_net_min_slow) comm_net_min_fast = cot_output['comm_net'].iloc[-52:].min() comm_net_max_fast = cot_output['comm_net'].iloc[-52:].max() comm_cot_index_fast = 100*(comm_net-comm_net_min_fast)/(comm_net_max_fast-comm_net_min_fast) else: comm_net = np.nan spec_net = np.nan comm_cot_index_slow = np.nan comm_cot_index_fast = np.nan contract_multiplier = cmi.contract_multiplier[ticker_head] return {'ticker': ticker2, 'comm_net': comm_net, 'spec_net': spec_net, 'comm_cot_index_slow': comm_cot_index_slow, 'comm_cot_index_fast': comm_cot_index_fast, 'trend_direction': trend_direction,'curve_slope': curve_slope, 'rsi_3': ticker_data['rsi_3'].iloc[-1], 'rsi_7': ticker_data['rsi_7'].iloc[-1], 'rsi_14': ticker_data['rsi_14'].iloc[-1], 'change1': ticker_data['change1'].iloc[-1]/daily_noise, 'change1_instant': ticker_data['change1_instant'].iloc[-1]/daily_noise, 'change5': ticker_data['change5'].iloc[-1]/daily_noise, 'change10': ticker_data['change10'].iloc[-1]/daily_noise, 'change20': ticker_data['change20'].iloc[-1]/daily_noise, 'change1_dollar': ticker_data['change1'].iloc[-1]*contract_multiplier, 'change1_instant_dollar': ticker_data['change1_instant'].iloc[-1]*contract_multiplier, 'change5_dollar': ticker_data['change5'].iloc[-1]*contract_multiplier, 'change10_dollar': ticker_data['change10'].iloc[-1]*contract_multiplier, 'change20_dollar': ticker_data['change20'].iloc[-1]*contract_multiplier}
def get_intraday_spread_signals(**kwargs): ticker_list = kwargs['ticker_list'] date_to = kwargs['date_to'] ticker_list = [x for x in ticker_list if x is not None] ticker_head_list = [cmi.get_contract_specs(x)['ticker_head'] for x in ticker_list] ticker_class_list = [cmi.ticker_class[x] for x in ticker_head_list] print('-'.join(ticker_list)) if 'tr_dte_list' in kwargs.keys(): tr_dte_list = kwargs['tr_dte_list'] else: tr_dte_list = [exp.get_days2_expiration(ticker=x,date_to=date_to, instrument='futures')['tr_dte'] for x in ticker_list] weights_output = sutil.get_spread_weights_4contract_list(ticker_head_list=ticker_head_list) if 'aggregation_method' in kwargs.keys() and 'contracts_back' in kwargs.keys(): aggregation_method = kwargs['aggregation_method'] contracts_back = kwargs['contracts_back'] else: amcb_output = [opUtil.get_aggregation_method_contracts_back(cmi.get_contract_specs(x)) for x in ticker_list] aggregation_method = max([x['aggregation_method'] for x in amcb_output]) contracts_back = min([x['contracts_back'] for x in amcb_output]) 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 list(set(ticker_head_list))} if 'use_last_as_current' in kwargs.keys(): use_last_as_current = kwargs['use_last_as_current'] else: use_last_as_current = True if 'datetime5_years_ago' in kwargs.keys(): datetime5_years_ago = kwargs['datetime5_years_ago'] else: date5_years_ago = cu.doubledate_shift(date_to,5*365) datetime5_years_ago = cu.convert_doubledate_2datetime(date5_years_ago) if 'num_days_back_4intraday' in kwargs.keys(): num_days_back_4intraday = kwargs['num_days_back_4intraday'] else: num_days_back_4intraday = 5 contract_multiplier_list = [cmi.contract_multiplier[x] for x in ticker_head_list] aligned_output = opUtil.get_aligned_futures_data(contract_list=ticker_list, tr_dte_list=tr_dte_list, aggregation_method=aggregation_method, contracts_back=contracts_back, date_to=date_to, futures_data_dictionary=futures_data_dictionary, use_last_as_current=use_last_as_current) aligned_data = aligned_output['aligned_data'] current_data = aligned_output['current_data'] spread_weights = weights_output['spread_weights'] portfolio_weights = weights_output['portfolio_weights'] aligned_data['spread'] = 0 aligned_data['spread_pnl_1'] = 0 aligned_data['spread_pnl1'] = 0 spread_settle = 0 last5_years_indx = aligned_data['settle_date']>=datetime5_years_ago num_contracts = len(ticker_list) for i in range(num_contracts): aligned_data['spread'] = aligned_data['spread']+aligned_data['c' + str(i+1)]['close_price']*spread_weights[i] spread_settle = spread_settle + current_data['c' + str(i+1)]['close_price']*spread_weights[i] aligned_data['spread_pnl_1'] = aligned_data['spread_pnl_1']+aligned_data['c' + str(i+1)]['change_1']*portfolio_weights[i]*contract_multiplier_list[i] aligned_data['spread_pnl1'] = aligned_data['spread_pnl1']+aligned_data['c' + str(i+1)]['change1_instant']*portfolio_weights[i]*contract_multiplier_list[i] aligned_data['spread_normalized'] = aligned_data['spread']/aligned_data['c1']['close_price'] data_last5_years = aligned_data[last5_years_indx] percentile_vector = stats.get_number_from_quantile(y=data_last5_years['spread_pnl_1'].values, quantile_list=[1, 15, 85, 99], clean_num_obs=max(100, round(3*len(data_last5_years.index)/4))) downside = (percentile_vector[0]+percentile_vector[1])/2 upside = (percentile_vector[2]+percentile_vector[3])/2 date_list = [exp.doubledate_shift_bus_days(double_date=date_to,shift_in_days=x) for x in reversed(range(1,num_days_back_4intraday))] date_list.append(date_to) intraday_data = opUtil.get_aligned_futures_data_intraday(contract_list=ticker_list, date_list=date_list) intraday_data['time_stamp'] = [x.to_datetime() for x in intraday_data.index] intraday_data['settle_date'] = intraday_data['time_stamp'].apply(lambda x: x.date()) end_hour = min([cmi.last_trade_hour_minute[x] for x in ticker_head_list]) start_hour = max([cmi.first_trade_hour_minute[x] for x in ticker_head_list]) trade_start_hour = dt.time(9, 30, 0, 0) if 'Ag' in ticker_class_list: start_hour1 = dt.time(0, 45, 0, 0) end_hour1 = dt.time(7, 45, 0, 0) selection_indx = [x for x in range(len(intraday_data.index)) if ((intraday_data['time_stamp'].iloc[x].time() < end_hour1) and(intraday_data['time_stamp'].iloc[x].time() >= start_hour1)) or ((intraday_data['time_stamp'].iloc[x].time() < end_hour) and(intraday_data['time_stamp'].iloc[x].time() >= start_hour))] else: selection_indx = [x for x in range(len(intraday_data.index)) if (intraday_data.index[x].to_datetime().time() < end_hour) and(intraday_data.index[x].to_datetime().time() >= start_hour)] intraday_data = intraday_data.iloc[selection_indx] intraday_data['spread'] = 0 for i in range(num_contracts): intraday_data['c' + str(i+1), 'mid_p'] = (intraday_data['c' + str(i+1)]['best_bid_p'] + intraday_data['c' + str(i+1)]['best_ask_p'])/2 intraday_data['spread'] = intraday_data['spread']+intraday_data['c' + str(i+1)]['mid_p']*spread_weights[i] unique_settle_dates = intraday_data['settle_date'].unique() intraday_data['spread1'] = np.nan for i in range(len(unique_settle_dates)-1): if (intraday_data['settle_date'] == unique_settle_dates[i]).sum() == \ (intraday_data['settle_date'] == unique_settle_dates[i+1]).sum(): intraday_data.loc[intraday_data['settle_date'] == unique_settle_dates[i],'spread1'] = \ intraday_data['spread'][intraday_data['settle_date'] == unique_settle_dates[i+1]].values intraday_data = intraday_data[intraday_data['settle_date'].notnull()] intraday_mean = intraday_data['spread'].mean() intraday_std = intraday_data['spread'].std() intraday_data_last2days = intraday_data[intraday_data['settle_date'] >= cu.convert_doubledate_2datetime(date_list[-2]).date()] intraday_data_yesterday = intraday_data[intraday_data['settle_date'] == cu.convert_doubledate_2datetime(date_list[-1]).date()] intraday_mean2 = intraday_data_last2days['spread'].mean() intraday_std2 = intraday_data_last2days['spread'].std() intraday_mean1 = intraday_data_yesterday['spread'].mean() intraday_std1 = intraday_data_yesterday['spread'].std() intraday_z = (spread_settle-intraday_mean)/intraday_std num_obs_intraday = len(intraday_data.index) num_obs_intraday_half = round(num_obs_intraday/2) intraday_tail = intraday_data.tail(num_obs_intraday_half) num_positives = sum(intraday_tail['spread'] > intraday_data['spread'].mean()) num_negatives = sum(intraday_tail['spread'] < intraday_data['spread'].mean()) recent_trend = 100*(num_positives-num_negatives)/(num_positives+num_negatives) pnl_frame = ifs.get_pnl_4_date_range(date_to=date_to, num_bus_days_back=20, ticker_list=ticker_list) if (len(pnl_frame.index)>15)&(pnl_frame['total_pnl'].std() != 0): historical_sharp = (250**(0.5))*pnl_frame['total_pnl'].mean()/pnl_frame['total_pnl'].std() else: historical_sharp = np.nan return {'downside': downside, 'upside': upside,'intraday_data': intraday_data, 'z': intraday_z,'recent_trend': recent_trend, 'intraday_mean': intraday_mean, 'intraday_std': intraday_std, 'intraday_mean2': intraday_mean2, 'intraday_std2': intraday_std2, 'intraday_mean1': intraday_mean1, 'intraday_std1': intraday_std1, 'aligned_output': aligned_output, 'spread_settle': spread_settle, 'data_last5_years': data_last5_years,'historical_sharp':historical_sharp}
def generate_futures_butterfly_sheet_4date(**kwargs): date_to = kwargs['date_to'] output_dir = ts.create_strategy_output_dir(strategy_class='futures_butterfly', report_date=date_to) if os.path.isfile(output_dir + '/summary.pkl'): butterflies = pd.read_pickle(output_dir + '/summary.pkl') return {'butterflies': butterflies,'success': True} if 'volume_filter' not in kwargs.keys(): kwargs['volume_filter'] = 100 butterflies = get_futures_butterflies_4date(**kwargs) butterflies = butterflies[butterflies['trDte1'] >= 35] butterflies.reset_index(drop=True,inplace=True) num_butterflies = len(butterflies) q_list = [None]*num_butterflies qf_list = [None]*num_butterflies zscore1_list = [None]*num_butterflies zscore2_list = [None]*num_butterflies zscore3_list = [None]*num_butterflies zscore4_list = [None]*num_butterflies zscore5_list = [None]*num_butterflies zscore6_list = [None]*num_butterflies zscore7_list = [None]*num_butterflies rsquared1_list = [None]*num_butterflies rsquared2_list = [None]*num_butterflies regime_change_list = [None]*num_butterflies contract_seasonality_list = [None]*num_butterflies yield1_list = [None]*num_butterflies yield2_list = [None]*num_butterflies bf_price_list = [None]*num_butterflies bf_price_sell_limit_list = [None]*num_butterflies bf_price_buy_limit_list = [None]*num_butterflies noise_ratio_list = [None]*num_butterflies alpha1_list = [None]*num_butterflies alpha2_list = [None]*num_butterflies residual_std1_list = [None]*num_butterflies residual_std2_list = [None]*num_butterflies second_spread_weight_1_list = [None]*num_butterflies second_spread_weight_2_list = [None]*num_butterflies weight1_list = [None]*num_butterflies weight2_list = [None]*num_butterflies weight3_list = [None]*num_butterflies downside_list = [None]*num_butterflies upside_list = [None]*num_butterflies recent_5day_pnl_list = [None]*num_butterflies recent_vol_ratio_list = [None]*num_butterflies theo_pnl_list = [None]*num_butterflies theo_pnl5_list = [None]*num_butterflies theo_pnl10_list = [None]*num_butterflies theo_pnl15_list = [None]*num_butterflies theo_pnl20_list = [None]*num_butterflies theo_pnl25_list = [None]*num_butterflies ratio_target5_list = [None]*num_butterflies ratio_target10_list = [None]*num_butterflies ratio_target15_list = [None]*num_butterflies ratio_target20_list = [None]*num_butterflies ratio_target25_list = [None]*num_butterflies price_1_list = [None]*num_butterflies price_2_list = [None]*num_butterflies price_3_list = [None]*num_butterflies mean_reversion_rsquared_list = [None]*num_butterflies mean_reversion_signif_list = [None]*num_butterflies futures_data_dictionary = {x: gfp.get_futures_price_preloaded(ticker_head=x) for x in cmi.futures_butterfly_strategy_tickerhead_list} date5_years_ago = cu.doubledate_shift(date_to,5*365) datetime5_years_ago = cu.convert_doubledate_2datetime(date5_years_ago) for i in range(num_butterflies): bf_signals_output = fs.get_futures_butterfly_signals(ticker_list=[butterflies['ticker1'][i], butterflies['ticker2'][i], butterflies['ticker3'][i]], tr_dte_list=[butterflies['trDte1'][i], butterflies['trDte2'][i], butterflies['trDte3'][i]], aggregation_method=butterflies['agg'][i], contracts_back=butterflies['cBack'][i], date_to=date_to, futures_data_dictionary=futures_data_dictionary, contract_multiplier=butterflies['multiplier'][i], datetime5_years_ago=datetime5_years_ago) q_list[i] = bf_signals_output['q'] qf_list[i] = bf_signals_output['qf'] zscore1_list[i] = bf_signals_output['zscore1'] zscore2_list[i] = bf_signals_output['zscore2'] zscore3_list[i] = bf_signals_output['zscore3'] zscore4_list[i] = bf_signals_output['zscore4'] zscore5_list[i] = bf_signals_output['zscore5'] zscore6_list[i] = bf_signals_output['zscore6'] zscore7_list[i] = bf_signals_output['zscore7'] rsquared1_list[i] = bf_signals_output['rsquared1'] rsquared2_list[i] = bf_signals_output['rsquared2'] regime_change_list[i] = bf_signals_output['regime_change_ind'] contract_seasonality_list[i] = bf_signals_output['contract_seasonality_ind'] yield1_list[i] = bf_signals_output['yield1_current'] yield2_list[i] = bf_signals_output['yield2_current'] bf_price_list[i] = bf_signals_output['bf_price'] bf_price_sell_limit_list[i] = bf_signals_output['short_price_limit'] bf_price_buy_limit_list[i] = bf_signals_output['long_price_limit'] noise_ratio_list[i] = bf_signals_output['noise_ratio'] alpha1_list[i] = bf_signals_output['alpha1'] alpha2_list[i] = bf_signals_output['alpha2'] residual_std1_list = bf_signals_output['residual_std1'] residual_std2_list = bf_signals_output['residual_std2'] second_spread_weight_1_list[i] = bf_signals_output['second_spread_weight_1'] second_spread_weight_2_list[i] = bf_signals_output['second_spread_weight_2'] weight1_list[i] = bf_signals_output['weight1'] weight2_list[i] = bf_signals_output['weight2'] weight3_list[i] = bf_signals_output['weight3'] downside_list[i] = bf_signals_output['downside'] upside_list[i] = bf_signals_output['upside'] recent_5day_pnl_list[i] = bf_signals_output['recent_5day_pnl'] recent_vol_ratio_list[i] = bf_signals_output['recent_vol_ratio'] theo_pnl_list[i] = bf_signals_output['theo_pnl'] theo_pnl5_list[i] = bf_signals_output['theo_pnl_list'][0] theo_pnl10_list[i] = bf_signals_output['theo_pnl_list'][1] theo_pnl15_list[i] = bf_signals_output['theo_pnl_list'][2] theo_pnl20_list[i] = bf_signals_output['theo_pnl_list'][3] theo_pnl25_list[i] = bf_signals_output['theo_pnl_list'][4] ratio_target5_list[i] = bf_signals_output['ratio_target_list'][0] ratio_target10_list[i] = bf_signals_output['ratio_target_list'][1] ratio_target15_list[i] = bf_signals_output['ratio_target_list'][2] ratio_target20_list[i] = bf_signals_output['ratio_target_list'][3] ratio_target25_list[i] = bf_signals_output['ratio_target_list'][4] price_1_list[i] = bf_signals_output['price_1'] price_2_list[i] = bf_signals_output['price_2'] price_3_list[i] = bf_signals_output['price_3'] mean_reversion_rsquared_list[i] = bf_signals_output['mean_reversion_rsquared'] mean_reversion_signif_list[i] = bf_signals_output['mean_reversion_signif'] butterflies['Q'] = q_list butterflies['QF'] = qf_list butterflies['z1'] = zscore1_list butterflies['z2'] = zscore2_list butterflies['z3'] = zscore3_list butterflies['z4'] = zscore4_list butterflies['z5'] = zscore5_list butterflies['z6'] = zscore6_list butterflies['z7'] = zscore7_list butterflies['r1'] = rsquared1_list butterflies['r2'] = rsquared2_list butterflies['RC'] = regime_change_list butterflies['seasonality'] = contract_seasonality_list butterflies['yield1'] = yield1_list butterflies['yield2'] = yield2_list butterflies['bf_price'] = bf_price_list butterflies['bf_sell_limit'] = bf_price_sell_limit_list butterflies['bf_buy_limit'] = bf_price_buy_limit_list butterflies['noise_ratio'] = noise_ratio_list butterflies['alpha1'] = alpha1_list butterflies['alpha2'] = alpha2_list butterflies['residual_std1'] = residual_std1_list butterflies['residual_std2'] = residual_std2_list butterflies['second_spread_weight_1'] = second_spread_weight_1_list butterflies['second_spread_weight_2'] = second_spread_weight_2_list butterflies['weight1'] = weight1_list butterflies['weight2'] = weight2_list butterflies['weight3'] = weight3_list butterflies['downside'] = downside_list butterflies['upside'] = upside_list butterflies['recent_5day_pnl'] = recent_5day_pnl_list butterflies['recent_vol_ratio'] = recent_vol_ratio_list butterflies['theo_pnl'] = theo_pnl_list butterflies['theo_pnl5'] = theo_pnl5_list butterflies['theo_pnl10'] = theo_pnl10_list butterflies['theo_pnl15'] = theo_pnl15_list butterflies['theo_pnl20'] = theo_pnl20_list butterflies['theo_pnl25'] = theo_pnl25_list butterflies['ratio_target5'] = ratio_target5_list butterflies['ratio_target10'] = ratio_target10_list butterflies['ratio_target15'] = ratio_target15_list butterflies['ratio_target20'] = ratio_target20_list butterflies['ratio_target25'] = ratio_target25_list butterflies['price1'] = price_1_list butterflies['price2'] = price_2_list butterflies['price3'] = price_3_list butterflies['mean_reversion_rsquared'] = mean_reversion_rsquared_list butterflies['mean_reversion_signif'] = mean_reversion_signif_list butterflies['z1'] = butterflies['z1'].round(2) butterflies['z2'] = butterflies['z2'].round(2) butterflies['z3'] = butterflies['z3'].round(2) butterflies['z4'] = butterflies['z4'].round(2) butterflies['z5'] = butterflies['z5'].round(2) butterflies['z6'] = butterflies['z6'].round(2) butterflies['z7'] = butterflies['z7'].round(2) butterflies['r1'] = butterflies['r1'].round(2) butterflies['r2'] = butterflies['r2'].round(2) butterflies['RC'] = butterflies['RC'].round(2) butterflies['seasonality'] = butterflies['seasonality'].round(2) butterflies['second_spread_weight_1'] = butterflies['second_spread_weight_1'].round(2) butterflies['second_spread_weight_2'] = butterflies['second_spread_weight_1'].round(2) butterflies['yield1'] = butterflies['yield1'].round(3) butterflies['yield2'] = butterflies['yield2'].round(3) butterflies['noise_ratio'] = butterflies['noise_ratio'].round(3) butterflies['alpha1'] = butterflies['alpha1'].round(3) butterflies['alpha2'] = butterflies['alpha2'].round(3) butterflies['residual_std1'] = butterflies['residual_std1'].round(3) butterflies['residual_std2'] = butterflies['residual_std2'].round(3) butterflies['downside'] = butterflies['downside'].round(3) butterflies['upside'] = butterflies['upside'].round(3) butterflies['recent_5day_pnl'] = butterflies['recent_5day_pnl'].round(3) butterflies['recent_vol_ratio'] = butterflies['recent_vol_ratio'].round(2) butterflies['theo_pnl'] = butterflies['theo_pnl'].round(3) butterflies['price1'] = butterflies['price1'].round(4) butterflies['price2'] = butterflies['price2'].round(4) butterflies['price3'] = butterflies['price3'].round(4) butterflies['mean_reversion_rsquared'] = butterflies['mean_reversion_rsquared'].round(2) butterflies.to_pickle(output_dir + '/summary.pkl') return {'butterflies': butterflies,'success': True}
def get_historical_risk_4strategy(**kwargs): con = msu.get_my_sql_connection(**kwargs) alias = kwargs['alias'] #print(alias) if 'as_of_date' in kwargs.keys(): as_of_date = kwargs['as_of_date'] else: as_of_date = exp.doubledate_shift_bus_days() if 'datetime5_years_ago' in kwargs.keys(): datetime5_years_ago = kwargs['datetime5_years_ago'] else: date5_years_ago = cu.doubledate_shift(as_of_date,5*365) datetime5_years_ago = cu.convert_doubledate_2datetime(date5_years_ago) net_position = ts.get_net_position_4strategy_alias(alias=alias,con=con) net_position = net_position[net_position['instrument'] != 'O'] if 'con' not in kwargs.keys(): con.close() if net_position.empty: return {'downside': 0, 'pnl_5_change': []} amcb_output = [opUtil.get_aggregation_method_contracts_back(cmi.get_contract_specs(x)) for x in net_position['ticker']] aggregation_method = pd.DataFrame(amcb_output)['aggregation_method'].max() if aggregation_method == 12: contracts_back = const.annualContractsBack elif aggregation_method == 3: contracts_back = const.quarterlyContractsBack elif aggregation_method == 1: contracts_back = const.monthlyContractsBack aligned_output = opUtil.get_aligned_futures_data(contract_list=net_position['ticker'].values, aggregation_method=aggregation_method, contracts_back=contracts_back,date_to=as_of_date,**kwargs) aligned_data = aligned_output['aligned_data'] last5_years_indx = aligned_data['settle_date'] >= datetime5_years_ago data_last5_years = aligned_data[last5_years_indx] ticker_head_list = [cmi.get_contract_specs(x)['ticker_head'] for x in net_position['ticker']] contract_multiplier_list = [cmi.contract_multiplier[x] for x in ticker_head_list] pnl_5_change_list = [contract_multiplier_list[x]* net_position['qty'].iloc[x]* data_last5_years['c' + str(x+1)]['change_5'] for x in range(len(net_position.index))] pnl_5_change = sum(pnl_5_change_list) percentile_vector = stats.get_number_from_quantile(y=pnl_5_change.values, quantile_list=[1, 15], clean_num_obs=max(100, round(3*len(pnl_5_change.values)/4))) downside = (percentile_vector[0]+percentile_vector[1])/2 unique_ticker_head_list = list(set(ticker_head_list)) ticker_head_based_pnl_5_change = {x: sum([pnl_5_change_list[y] for y in range(len(ticker_head_list)) if ticker_head_list[y] == x]) for x in unique_ticker_head_list} return {'downside': downside, 'pnl_5_change': pnl_5_change,'ticker_head_based_pnl_5_change':ticker_head_based_pnl_5_change}
def get_overnight_spreads_4date(**kwargs): date_to = kwargs['date_to'] datetime_to = cu.convert_doubledate_2datetime(date_to) kwargs['date_from'] = cu.doubledate_shift(date_to, 6) futures_dataframe = cl.generate_futures_list_dataframe(**kwargs) futures_dataframe.sort_values(['ticker', 'settle_date'], ascending=[True, True], inplace=True) grouped = futures_dataframe.groupby(['ticker']) mean_frame = pd.DataFrame() mean_frame['averege_volume'] = grouped['volume'].mean() mean_frame['ticker'] = grouped['ticker'].last() mean_frame.index.names = [ 'ticker_index' if x is 'ticker' else x for x in mean_frame.index.names ] mean_frame['averege_volume'] = mean_frame['averege_volume'].round() futures_dataframe = futures_dataframe[futures_dataframe['settle_date'] == datetime_to] futures_dataframe = pd.merge(futures_dataframe, mean_frame, on='ticker', how='left') if 'volume_filter' in kwargs.keys(): volume_filter = kwargs['volume_filter'] futures_dataframe = futures_dataframe[ futures_dataframe['averege_volume'] > volume_filter] futures_dataframe.reset_index(drop=True, inplace=True) unique_ticker_heads = cmi.futures_butterfly_strategy_tickerhead_list tuples = [] for ticker_head_i in unique_ticker_heads: ticker_head_data = futures_dataframe[futures_dataframe['ticker_head'] == ticker_head_i] ticker_head_data.sort_values(['ticker_year', 'ticker_month'], ascending=[True, True], inplace=True) if len(ticker_head_data.index) >= 2: tuples = tuples + [ (ticker_head_data.index[i], ticker_head_data.index[i + 1]) for i in range(len(ticker_head_data.index) - 1) ] return pd.DataFrame([(futures_dataframe['ticker'][indx[0]], futures_dataframe['ticker'][indx[1]], futures_dataframe['ticker_head'][indx[0]], futures_dataframe['ticker_class'][indx[0]], futures_dataframe['tr_dte'][indx[0]], futures_dataframe['tr_dte'][indx[1]], min([ futures_dataframe['averege_volume'][indx[0]], futures_dataframe['averege_volume'][indx[1]] ]), futures_dataframe['multiplier'][indx[0]], futures_dataframe['aggregation_method'][indx[0]], futures_dataframe['contracts_back'][indx[0]]) for indx in tuples], columns=[ 'ticker1', 'ticker2', 'tickerHead', 'tickerClass', 'trDte1', 'trDte2', 'min_avg_volume', 'multiplier', 'agg', 'cBack' ])
def generate_ocs_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 broker = kwargs['broker'] ta_output_dir = dn.get_dated_directory_extension(folder_date=as_of_date, ext='ta') con = msu.get_my_sql_connection(**kwargs) 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)) ] ocs_indx = [x == 'ocs' for x in strategy_class_list] ocs_frame = strategy_frame[ocs_indx] if ocs_frame.empty: writer.save() return results = [ sf.get_results_4strategy(alias=ocs_frame['alias'].iloc[x], strategy_info_output=ocs_frame.iloc[x], con=con, broker=broker, date_to=as_of_date) for x in range(len(ocs_frame.index)) ] ocs_followup_frame = pd.DataFrame(results) ocs_followup_frame['alias'] = ocs_frame['alias'].values kwargs['name'] = 'final' pnl_frame = pm.get_daily_pnl_snapshot(**kwargs) merged_frame1 = pd.merge(ocs_followup_frame, pnl_frame, how='left', on='alias') ocs_followup_frame = merged_frame1[[ 'alias', 'dollar_noise', 'time_held', 'daily_pnl', 'total_pnl', 'notes' ]] ocs_followup_frame.reset_index(drop=True, inplace=True) ocs_followup_frame.loc[max(ocs_followup_frame.index) + 1] = [ 'TOTAL', np.nan, np.nan, ocs_followup_frame['daily_pnl'].sum(), ocs_followup_frame['total_pnl'].sum(), '' ] date_from30 = cu.doubledate_shift(as_of_date, 30) history_frame = ts.select_strategies(close_date_from=date_from30, close_date_to=as_of_date, con=con) strategy_class_list = [ sc.convert_from_string_to_dictionary( string_input=history_frame['description_string'][x]) ['strategy_class'] for x in range(len(history_frame.index)) ] ocs_indx = [x == 'ocs' for x in strategy_class_list] ocs_history_frame = history_frame[ocs_indx] pnl_past_month = ocs_history_frame['pnl'].sum() as_of_datetime = cu.convert_doubledate_2datetime(as_of_date) date_from7 = as_of_datetime + dt.timedelta(days=-7) ocs_short_history_frame = ocs_history_frame[ ocs_history_frame['close_date'] >= date_from7] pnl_past_week = ocs_short_history_frame['pnl'].sum() ocs_followup_frame.loc[max(ocs_followup_frame.index) + 1] = [ 'WEEKLY PERFORMANCE', np.nan, np.nan, np.nan, pnl_past_week, '' ] ocs_followup_frame.loc[max(ocs_followup_frame.index) + 1] = [ 'MONTHLY PERFORMANCE', np.nan, np.nan, np.nan, pnl_past_month, '' ] ocs_followup_frame['total_pnl'] = ocs_followup_frame['total_pnl'].astype( int) ocs_followup_frame.to_excel(writer, sheet_name='ocs') worksheet_ocs = writer.sheets['ocs'] worksheet_ocs.freeze_panes(1, 0) worksheet_ocs.set_column('B:B', 26) worksheet_ocs.autofilter(0, 0, len(ocs_followup_frame.index), len(ocs_followup_frame.columns)) if 'con' not in kwargs.keys(): con.close() writer.save()
def generate_and_update_futures_data_file_4tickerhead(**kwargs): ticker_head = kwargs['ticker_head'] con = msu.get_my_sql_connection(**kwargs) if os.path.isfile(presaved_futures_data_folder + '/' + ticker_head + '.pkl'): old_data = pd.read_pickle(presaved_futures_data_folder + '/' + ticker_head + '.pkl') last_available_date = int(old_data['settle_date'].max().to_datetime().strftime('%Y%m%d')) date_from = cu.doubledate_shift(last_available_date, 60) data4_tickerhead = gfp.get_futures_price_4ticker(ticker_head=ticker_head, date_from=date_from, con=con) else: data4_tickerhead = gfp.get_futures_price_4ticker(ticker_head=ticker_head, con=con) data4_tickerhead = pd.merge(data4_tickerhead, dirty_data_points, on=['settle_date', 'ticker'],how='left') data4_tickerhead = data4_tickerhead[data4_tickerhead['discard'] != True] data4_tickerhead = data4_tickerhead.drop('discard', 1) data4_tickerhead['close_price'] = [float(x) if x is not None else float('NaN') for x in data4_tickerhead['close_price'].values] data4_tickerhead['open_price'] = [float(x) if x is not None else float('NaN') for x in data4_tickerhead['open_price'].values] data4_tickerhead['high_price'] = [float(x) if x is not None else float('NaN') for x in data4_tickerhead['high_price'].values] data4_tickerhead['low_price'] = [float(x) if x is not None else float('NaN') for x in data4_tickerhead['low_price'].values] data4_tickerhead['cont_indx'] = 100*data4_tickerhead['ticker_year']+data4_tickerhead['ticker_month'] unique_cont_indx_list = data4_tickerhead['cont_indx'].unique() num_contracts = len(unique_cont_indx_list) unique_cont_indx_list = np.sort(unique_cont_indx_list) merged_dataframe_list = [None]*num_contracts bday_us = CustomBusinessDay(calendar=exp.get_calendar_4ticker_head('CL')) full_dates = pd.date_range(start=data4_tickerhead['settle_date'].min(),end=data4_tickerhead['settle_date'].max(), freq=bday_us) for i in range(num_contracts): contract_data = data4_tickerhead[data4_tickerhead['cont_indx']==unique_cont_indx_list[i]] contract_full_dates = full_dates[(full_dates >= contract_data['settle_date'].min()) & (full_dates<=contract_data['settle_date'].max())] full_date_frame = pd.DataFrame(contract_full_dates, columns=['settle_date']) merged_dataframe_list[i] = pd.merge(full_date_frame,contract_data,on='settle_date',how='left') merged_dataframe_list[i]['ticker'] = contract_data['ticker'][contract_data.index[0]] merged_dataframe_list[i]['ticker_head'] = contract_data['ticker_head'][contract_data.index[0]] merged_dataframe_list[i]['ticker_month'] = contract_data['ticker_month'][contract_data.index[0]] merged_dataframe_list[i]['ticker_year'] = contract_data['ticker_year'][contract_data.index[0]] merged_dataframe_list[i]['cont_indx'] = contract_data['cont_indx'][contract_data.index[0]] merged_dataframe_list[i]['change1'] = merged_dataframe_list[i]['close_price'].shift(-2)-merged_dataframe_list[i]['close_price'].shift(-1) merged_dataframe_list[i]['change1_instant'] = merged_dataframe_list[i]['close_price'].shift(-1)-merged_dataframe_list[i]['close_price'] merged_dataframe_list[i]['change2'] = merged_dataframe_list[i]['close_price'].shift(-3)-merged_dataframe_list[i]['close_price'].shift(-1) merged_dataframe_list[i]['change5'] = merged_dataframe_list[i]['close_price'].shift(-6)-merged_dataframe_list[i]['close_price'].shift(-1) merged_dataframe_list[i]['change10'] = merged_dataframe_list[i]['close_price'].shift(-11)-merged_dataframe_list[i]['close_price'].shift(-1) merged_dataframe_list[i]['change20'] = merged_dataframe_list[i]['close_price'].shift(-21)-merged_dataframe_list[i]['close_price'].shift(-1) merged_dataframe_list[i]['change_5'] = merged_dataframe_list[i]['close_price']-merged_dataframe_list[i]['close_price'].shift(5) merged_dataframe_list[i]['change_1'] = merged_dataframe_list[i]['close_price']-merged_dataframe_list[i]['close_price'].shift(1) data4_tickerhead = pd.concat(merged_dataframe_list) if os.path.isfile(presaved_futures_data_folder + '/' + ticker_head + '.pkl'): clean_data = data4_tickerhead[np.isfinite(data4_tickerhead['change_5'])] clean_data['frame_indx'] = 1 old_data['frame_indx'] = 0 merged_data = pd.concat([old_data,clean_data],ignore_index=True) merged_data.sort(['cont_indx', 'settle_date', 'frame_indx'], ascending=[True, True, False], inplace=True) merged_data.drop_duplicates(subset=['settle_date', 'cont_indx'], take_last=False, inplace=True) data4_tickerhead = merged_data.drop('frame_indx', 1, inplace=False) data4_tickerhead.to_pickle(presaved_futures_data_folder + '/' + ticker_head + '.pkl') if 'con' not in kwargs.keys(): con.close()
def generate_and_update_futures_data_file_4tickerhead(**kwargs): ticker_head = kwargs['ticker_head'] con = msu.get_my_sql_connection(**kwargs) if os.path.isfile(presaved_futures_data_folder + '/' + ticker_head + '.pkl'): old_data = pd.read_pickle(presaved_futures_data_folder + '/' + ticker_head + '.pkl') last_available_date = int( old_data['settle_date'].max().to_pydatetime().strftime('%Y%m%d')) date_from = cu.doubledate_shift(last_available_date, 60) data4_tickerhead = gfp.get_futures_price_4ticker( ticker_head=ticker_head, date_from=date_from, con=con) else: data4_tickerhead = gfp.get_futures_price_4ticker( ticker_head=ticker_head, con=con) data4_tickerhead = pd.merge(data4_tickerhead, dirty_data_points, on=['settle_date', 'ticker'], how='left') data4_tickerhead = data4_tickerhead[data4_tickerhead['discard'] != True] data4_tickerhead = data4_tickerhead.drop('discard', 1) data4_tickerhead['close_price'] = [ float(x) if x is not None else float('NaN') for x in data4_tickerhead['close_price'].values ] data4_tickerhead['open_price'] = [ float(x) if x is not None else float('NaN') for x in data4_tickerhead['open_price'].values ] data4_tickerhead['high_price'] = [ float(x) if x is not None else float('NaN') for x in data4_tickerhead['high_price'].values ] data4_tickerhead['low_price'] = [ float(x) if x is not None else float('NaN') for x in data4_tickerhead['low_price'].values ] data4_tickerhead['cont_indx'] = 100 * data4_tickerhead[ 'ticker_year'] + data4_tickerhead['ticker_month'] unique_cont_indx_list = data4_tickerhead['cont_indx'].unique() num_contracts = len(unique_cont_indx_list) unique_cont_indx_list = np.sort(unique_cont_indx_list) merged_dataframe_list = [None] * num_contracts bday_us = CustomBusinessDay(calendar=exp.get_calendar_4ticker_head('CL')) full_dates = pd.date_range(start=data4_tickerhead['settle_date'].min(), end=data4_tickerhead['settle_date'].max(), freq=bday_us) for i in range(num_contracts): contract_data = data4_tickerhead[data4_tickerhead['cont_indx'] == unique_cont_indx_list[i]] contract_full_dates = full_dates[ (full_dates >= contract_data['settle_date'].min()) & (full_dates <= contract_data['settle_date'].max())] full_date_frame = pd.DataFrame(contract_full_dates, columns=['settle_date']) merged_dataframe_list[i] = pd.merge(full_date_frame, contract_data, on='settle_date', how='left') merged_dataframe_list[i]['ticker'] = contract_data['ticker'][ contract_data.index[0]] merged_dataframe_list[i]['ticker_head'] = contract_data['ticker_head'][ contract_data.index[0]] merged_dataframe_list[i]['ticker_month'] = contract_data[ 'ticker_month'][contract_data.index[0]] merged_dataframe_list[i]['ticker_year'] = contract_data['ticker_year'][ contract_data.index[0]] merged_dataframe_list[i]['cont_indx'] = contract_data['cont_indx'][ contract_data.index[0]] merged_dataframe_list[i][ 'change1'] = merged_dataframe_list[i]['close_price'].shift( -2) - merged_dataframe_list[i]['close_price'].shift(-1) merged_dataframe_list[i]['change1_instant'] = merged_dataframe_list[i][ 'close_price'].shift(-1) - merged_dataframe_list[i]['close_price'] merged_dataframe_list[i]['high1_instant'] = merged_dataframe_list[i][ 'high_price'].shift(-1) merged_dataframe_list[i]['low1_instant'] = merged_dataframe_list[i][ 'low_price'].shift(-1) merged_dataframe_list[i][ 'change2'] = merged_dataframe_list[i]['close_price'].shift( -3) - merged_dataframe_list[i]['close_price'].shift(-1) merged_dataframe_list[i][ 'change5'] = merged_dataframe_list[i]['close_price'].shift( -6) - merged_dataframe_list[i]['close_price'].shift(-1) merged_dataframe_list[i][ 'change10'] = merged_dataframe_list[i]['close_price'].shift( -11) - merged_dataframe_list[i]['close_price'].shift(-1) merged_dataframe_list[i][ 'change20'] = merged_dataframe_list[i]['close_price'].shift( -21) - merged_dataframe_list[i]['close_price'].shift(-1) merged_dataframe_list[i]['change_5'] = merged_dataframe_list[i][ 'close_price'] - merged_dataframe_list[i]['close_price'].shift(5) merged_dataframe_list[i]['change_1'] = merged_dataframe_list[i][ 'close_price'] - merged_dataframe_list[i]['close_price'].shift(1) data4_tickerhead = pd.concat(merged_dataframe_list) if os.path.isfile(presaved_futures_data_folder + '/' + ticker_head + '.pkl'): data4_tickerhead['past_indx'] = [ 1 if np.isfinite(x) else 0 for x in data4_tickerhead['change_5'].values ] clean_data = data4_tickerhead clean_data['frame_indx'] = 1 data_columns = old_data.columns old_data['frame_indx'] = 0 old_data['past_indx'] = [ 1 if np.isfinite(x) else 0 for x in old_data['change_5'].values ] merged_data = pd.concat([old_data, clean_data], ignore_index=True, sort=True) merged_data.sort_values( ['cont_indx', 'settle_date', 'past_indx', 'frame_indx'], ascending=[True, True, False, False], inplace=True) merged_data.drop_duplicates(subset=['settle_date', 'cont_indx'], keep='first', inplace=True) data4_tickerhead = merged_data.drop(['frame_indx', 'past_indx'], 1, inplace=False) data4_tickerhead = data4_tickerhead[data_columns] data4_tickerhead.to_pickle(presaved_futures_data_folder + '/' + ticker_head + '.pkl') if 'con' not in kwargs.keys(): con.close()
def generate_futures_butterfly_sheet_4date(**kwargs): date_to = kwargs['date_to'] output_dir = ts.create_strategy_output_dir( strategy_class='futures_butterfly', report_date=date_to) if os.path.isfile(output_dir + '/summary.pkl'): butterflies = pd.read_pickle(output_dir + '/summary.pkl') return {'butterflies': butterflies, 'success': True} if 'volume_filter' not in kwargs.keys(): kwargs['volume_filter'] = 100 butterflies = get_futures_butterflies_4date(**kwargs) butterflies = butterflies[butterflies['trDte1'] >= 35] butterflies.reset_index(drop=True, inplace=True) num_butterflies = len(butterflies) q_list = [None] * num_butterflies qf_list = [None] * num_butterflies zscore1_list = [None] * num_butterflies zscore2_list = [None] * num_butterflies zscore3_list = [None] * num_butterflies zscore4_list = [None] * num_butterflies zscore5_list = [None] * num_butterflies zscore6_list = [None] * num_butterflies zscore7_list = [None] * num_butterflies rsquared1_list = [None] * num_butterflies rsquared2_list = [None] * num_butterflies regime_change_list = [None] * num_butterflies contract_seasonality_list = [None] * num_butterflies yield1_list = [None] * num_butterflies yield2_list = [None] * num_butterflies bf_price_list = [None] * num_butterflies bf_price_sell_limit_list = [None] * num_butterflies bf_price_buy_limit_list = [None] * num_butterflies noise_ratio_list = [None] * num_butterflies alpha1_list = [None] * num_butterflies alpha2_list = [None] * num_butterflies residual_std1_list = [None] * num_butterflies residual_std2_list = [None] * num_butterflies second_spread_weight_1_list = [None] * num_butterflies second_spread_weight_2_list = [None] * num_butterflies weight1_list = [None] * num_butterflies weight2_list = [None] * num_butterflies weight3_list = [None] * num_butterflies downside_list = [None] * num_butterflies upside_list = [None] * num_butterflies recent_5day_pnl_list = [None] * num_butterflies recent_vol_ratio_list = [None] * num_butterflies theo_pnl_list = [None] * num_butterflies theo_pnl5_list = [None] * num_butterflies theo_pnl10_list = [None] * num_butterflies theo_pnl15_list = [None] * num_butterflies theo_pnl20_list = [None] * num_butterflies theo_pnl25_list = [None] * num_butterflies ratio_target5_list = [None] * num_butterflies ratio_target10_list = [None] * num_butterflies ratio_target15_list = [None] * num_butterflies ratio_target20_list = [None] * num_butterflies ratio_target25_list = [None] * num_butterflies price_1_list = [None] * num_butterflies price_2_list = [None] * num_butterflies price_3_list = [None] * num_butterflies mean_reversion_rsquared_list = [None] * num_butterflies mean_reversion_signif_list = [None] * num_butterflies futures_data_dictionary = { x: gfp.get_futures_price_preloaded(ticker_head=x) for x in cmi.futures_butterfly_strategy_tickerhead_list } date5_years_ago = cu.doubledate_shift(date_to, 5 * 365) datetime5_years_ago = cu.convert_doubledate_2datetime(date5_years_ago) for i in range(num_butterflies): bf_signals_output = fs.get_futures_butterfly_signals( ticker_list=[ butterflies['ticker1'][i], butterflies['ticker2'][i], butterflies['ticker3'][i] ], tr_dte_list=[ butterflies['trDte1'][i], butterflies['trDte2'][i], butterflies['trDte3'][i] ], aggregation_method=butterflies['agg'][i], contracts_back=butterflies['cBack'][i], date_to=date_to, futures_data_dictionary=futures_data_dictionary, contract_multiplier=butterflies['multiplier'][i], datetime5_years_ago=datetime5_years_ago) if not bf_signals_output['success']: continue q_list[i] = bf_signals_output['q'] qf_list[i] = bf_signals_output['qf'] zscore1_list[i] = bf_signals_output['zscore1'] zscore2_list[i] = bf_signals_output['zscore2'] zscore3_list[i] = bf_signals_output['zscore3'] zscore4_list[i] = bf_signals_output['zscore4'] zscore5_list[i] = bf_signals_output['zscore5'] zscore6_list[i] = bf_signals_output['zscore6'] zscore7_list[i] = bf_signals_output['zscore7'] rsquared1_list[i] = bf_signals_output['rsquared1'] rsquared2_list[i] = bf_signals_output['rsquared2'] regime_change_list[i] = bf_signals_output['regime_change_ind'] contract_seasonality_list[i] = bf_signals_output[ 'contract_seasonality_ind'] yield1_list[i] = bf_signals_output['yield1_current'] yield2_list[i] = bf_signals_output['yield2_current'] bf_price_list[i] = bf_signals_output['bf_price'] bf_price_sell_limit_list[i] = bf_signals_output['short_price_limit'] bf_price_buy_limit_list[i] = bf_signals_output['long_price_limit'] noise_ratio_list[i] = bf_signals_output['noise_ratio'] alpha1_list[i] = bf_signals_output['alpha1'] alpha2_list[i] = bf_signals_output['alpha2'] residual_std1_list = bf_signals_output['residual_std1'] residual_std2_list = bf_signals_output['residual_std2'] second_spread_weight_1_list[i] = bf_signals_output[ 'second_spread_weight_1'] second_spread_weight_2_list[i] = bf_signals_output[ 'second_spread_weight_2'] weight1_list[i] = bf_signals_output['weight1'] weight2_list[i] = bf_signals_output['weight2'] weight3_list[i] = bf_signals_output['weight3'] downside_list[i] = bf_signals_output['downside'] upside_list[i] = bf_signals_output['upside'] recent_5day_pnl_list[i] = bf_signals_output['recent_5day_pnl'] recent_vol_ratio_list[i] = bf_signals_output['recent_vol_ratio'] theo_pnl_list[i] = bf_signals_output['theo_pnl'] theo_pnl5_list[i] = bf_signals_output['theo_pnl_list'][0] theo_pnl10_list[i] = bf_signals_output['theo_pnl_list'][1] theo_pnl15_list[i] = bf_signals_output['theo_pnl_list'][2] theo_pnl20_list[i] = bf_signals_output['theo_pnl_list'][3] theo_pnl25_list[i] = bf_signals_output['theo_pnl_list'][4] ratio_target5_list[i] = bf_signals_output['ratio_target_list'][0] ratio_target10_list[i] = bf_signals_output['ratio_target_list'][1] ratio_target15_list[i] = bf_signals_output['ratio_target_list'][2] ratio_target20_list[i] = bf_signals_output['ratio_target_list'][3] ratio_target25_list[i] = bf_signals_output['ratio_target_list'][4] price_1_list[i] = bf_signals_output['price_1'] price_2_list[i] = bf_signals_output['price_2'] price_3_list[i] = bf_signals_output['price_3'] mean_reversion_rsquared_list[i] = bf_signals_output[ 'mean_reversion_rsquared'] mean_reversion_signif_list[i] = bf_signals_output[ 'mean_reversion_signif'] butterflies['Q'] = q_list butterflies['QF'] = qf_list butterflies['z1'] = zscore1_list butterflies['z2'] = zscore2_list butterflies['z3'] = zscore3_list butterflies['z4'] = zscore4_list butterflies['z5'] = zscore5_list butterflies['z6'] = zscore6_list butterflies['z7'] = zscore7_list butterflies['r1'] = rsquared1_list butterflies['r2'] = rsquared2_list butterflies['RC'] = regime_change_list butterflies['seasonality'] = contract_seasonality_list butterflies['yield1'] = yield1_list butterflies['yield2'] = yield2_list butterflies['bf_price'] = bf_price_list butterflies['bf_sell_limit'] = bf_price_sell_limit_list butterflies['bf_buy_limit'] = bf_price_buy_limit_list butterflies['noise_ratio'] = noise_ratio_list butterflies['alpha1'] = alpha1_list butterflies['alpha2'] = alpha2_list butterflies['residual_std1'] = residual_std1_list butterflies['residual_std2'] = residual_std2_list butterflies['second_spread_weight_1'] = second_spread_weight_1_list butterflies['second_spread_weight_2'] = second_spread_weight_2_list butterflies['weight1'] = weight1_list butterflies['weight2'] = weight2_list butterflies['weight3'] = weight3_list butterflies['downside'] = downside_list butterflies['upside'] = upside_list butterflies['recent_5day_pnl'] = recent_5day_pnl_list butterflies['recent_vol_ratio'] = recent_vol_ratio_list butterflies['theo_pnl'] = theo_pnl_list butterflies['theo_pnl5'] = theo_pnl5_list butterflies['theo_pnl10'] = theo_pnl10_list butterflies['theo_pnl15'] = theo_pnl15_list butterflies['theo_pnl20'] = theo_pnl20_list butterflies['theo_pnl25'] = theo_pnl25_list butterflies['ratio_target5'] = ratio_target5_list butterflies['ratio_target10'] = ratio_target10_list butterflies['ratio_target15'] = ratio_target15_list butterflies['ratio_target20'] = ratio_target20_list butterflies['ratio_target25'] = ratio_target25_list butterflies['price1'] = price_1_list butterflies['price2'] = price_2_list butterflies['price3'] = price_3_list butterflies['mean_reversion_rsquared'] = mean_reversion_rsquared_list butterflies['mean_reversion_signif'] = mean_reversion_signif_list butterflies['z1'] = butterflies['z1'].round(2) butterflies['z2'] = butterflies['z2'].round(2) butterflies['z3'] = butterflies['z3'].round(2) butterflies['z4'] = butterflies['z4'].round(2) butterflies['z5'] = butterflies['z5'].round(2) butterflies['z6'] = butterflies['z6'].round(2) butterflies['z7'] = butterflies['z7'].round(2) butterflies['r1'] = butterflies['r1'].round(2) butterflies['r2'] = butterflies['r2'].round(2) butterflies['RC'] = butterflies['RC'].round(2) butterflies['seasonality'] = butterflies['seasonality'].round(2) butterflies['second_spread_weight_1'] = butterflies[ 'second_spread_weight_1'].round(2) butterflies['second_spread_weight_2'] = butterflies[ 'second_spread_weight_1'].round(2) butterflies['yield1'] = butterflies['yield1'].round(3) butterflies['yield2'] = butterflies['yield2'].round(3) butterflies['noise_ratio'] = butterflies['noise_ratio'].round(3) butterflies['alpha1'] = butterflies['alpha1'].round(3) butterflies['alpha2'] = butterflies['alpha2'].round(3) butterflies['residual_std1'] = butterflies['residual_std1'].round(3) butterflies['residual_std2'] = butterflies['residual_std2'].round(3) butterflies['downside'] = butterflies['downside'].round(3) butterflies['upside'] = butterflies['upside'].round(3) butterflies['recent_5day_pnl'] = butterflies['recent_5day_pnl'].round(3) butterflies['recent_vol_ratio'] = butterflies['recent_vol_ratio'].round(2) butterflies['theo_pnl'] = butterflies['theo_pnl'].round(3) butterflies['price1'] = butterflies['price1'].round(4) butterflies['price2'] = butterflies['price2'].round(4) butterflies['price3'] = butterflies['price3'].round(4) butterflies['mean_reversion_rsquared'] = butterflies[ 'mean_reversion_rsquared'].round(2) butterflies.to_pickle(output_dir + '/summary.pkl') return {'butterflies': butterflies, 'success': True}
def get_futures_spread_carry_signals(**kwargs): ticker_list = kwargs['ticker_list'] date_to = kwargs['date_to'] if 'tr_dte_list' in kwargs.keys(): tr_dte_list = kwargs['tr_dte_list'] else: tr_dte_list = [exp.get_futures_days2_expiration({'ticker': x,'date_to': date_to}) for x in ticker_list] if 'aggregation_method' in kwargs.keys() and 'contracts_back' in kwargs.keys(): aggregation_method = kwargs['aggregation_method'] contracts_back = kwargs['contracts_back'] else: amcb_output = opUtil.get_aggregation_method_contracts_back(cmi.get_contract_specs(ticker_list[0])) aggregation_method = amcb_output['aggregation_method'] contracts_back = amcb_output['contracts_back'] if 'use_last_as_current' in kwargs.keys(): use_last_as_current = kwargs['use_last_as_current'] else: use_last_as_current = False 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 [cmi.get_contract_specs(ticker_list[0])['ticker_head']]} if 'contract_multiplier' in kwargs.keys(): contract_multiplier = kwargs['contract_multiplier'] else: contract_multiplier = cmi.contract_multiplier[cmi.get_contract_specs(ticker_list[0])['ticker_head']] if 'datetime5_years_ago' in kwargs.keys(): datetime5_years_ago = kwargs['datetime5_years_ago'] else: date5_years_ago = cu.doubledate_shift(date_to,5*365) datetime5_years_ago = cu.convert_doubledate_2datetime(date5_years_ago) if 'datetime2_months_ago' in kwargs.keys(): datetime2_months_ago = kwargs['datetime2_months_ago'] else: date2_months_ago = cu.doubledate_shift(date_to,60) datetime2_months_ago = cu.convert_doubledate_2datetime(date2_months_ago) aligned_output = opUtil.get_aligned_futures_data(contract_list=ticker_list, tr_dte_list=tr_dte_list, aggregation_method=aggregation_method, contracts_back=contracts_back, date_to=date_to, futures_data_dictionary=futures_data_dictionary, use_last_as_current=use_last_as_current) aligned_data = aligned_output['aligned_data'] current_data = aligned_output['current_data'] last5_years_indx = aligned_data['settle_date']>=datetime5_years_ago data_last5_years = aligned_data[last5_years_indx] ticker1_list = [current_data['c' + str(x+1)]['ticker'] for x in range(len(ticker_list)-1)] ticker2_list = [current_data['c' + str(x+2)]['ticker'] for x in range(len(ticker_list)-1)] yield_current_list = [100*(current_data['c' + str(x+1)]['close_price']- current_data['c' + str(x+2)]['close_price'])/ current_data['c' + str(x+2)]['close_price'] for x in range(len(ticker_list)-1)] price_current_list = [current_data['c' + str(x+1)]['close_price']-current_data['c' + str(x+2)]['close_price'] for x in range(len(ticker_list)-1)] yield_history = [100*(aligned_data['c' + str(x+1)]['close_price']- aligned_data['c' + str(x+2)]['close_price'])/ aligned_data['c' + str(x+2)]['close_price'] for x in range(len(ticker_list)-1)] change_5_history = [data_last5_years['c' + str(x+1)]['change_5']- data_last5_years['c' + str(x+2)]['change_5'] for x in range(len(ticker_list)-1)] change5 = [contract_multiplier*(current_data['c' + str(x+1)]['change5']- current_data['c' + str(x+2)]['change5']) for x in range(len(ticker_list)-1)] change10 = [contract_multiplier*(current_data['c' + str(x+1)]['change10']- current_data['c' + str(x+2)]['change10']) for x in range(len(ticker_list)-1)] change20 = [contract_multiplier*(current_data['c' + str(x+1)]['change20']- current_data['c' + str(x+2)]['change20']) for x in range(len(ticker_list)-1)] front_tr_dte = [current_data['c' + str(x+1)]['tr_dte'] for x in range(len(ticker_list)-1)] q_list = [stats.get_quantile_from_number({'x': yield_current_list[x], 'y': yield_history[x].values, 'clean_num_obs': max(100, round(3*len(yield_history[x].values)/4))}) for x in range(len(ticker_list)-1)] percentile_vector = [stats.get_number_from_quantile(y=change_5_history[x].values, quantile_list=[1, 15, 85, 99], clean_num_obs=max(100, round(3*len(change_5_history[x].values)/4))) for x in range(len(ticker_list)-1)] q1 = [x[0] for x in percentile_vector] q15 = [x[1] for x in percentile_vector] q85 = [x[2] for x in percentile_vector] q99 = [x[3] for x in percentile_vector] downside = [contract_multiplier*(q1[x]+q15[x])/2 for x in range(len(q1))] upside = [contract_multiplier*(q85[x]+q99[x])/2 for x in range(len(q1))] carry = [contract_multiplier*(price_current_list[x]-price_current_list[x+1]) for x in range(len(q_list)-1)] q_carry = [q_list[x]-q_list[x+1] for x in range(len(q_list)-1)] reward_risk = [5*carry[x]/((front_tr_dte[x+1]-front_tr_dte[x])*abs(downside[x+1])) if carry[x]>0 else 5*carry[x]/((front_tr_dte[x+1]-front_tr_dte[x])*upside[x+1]) for x in range(len(carry))] return pd.DataFrame.from_items([('ticker1',ticker1_list), ('ticker2',ticker2_list), ('ticker_head',cmi.get_contract_specs(ticker_list[0])['ticker_head']), ('front_tr_dte',front_tr_dte), ('carry',[np.NAN]+carry), ('q_carry',[np.NAN]+q_carry), ('reward_risk',[np.NAN]+reward_risk), ('price',price_current_list), ('q',q_list), ('upside',upside), ('downside',downside), ('change5',change5), ('change10',change10), ('change20',change20)])
def update_futures_price_database_from_cme_file(**kwargs): ticker_head_list = cmi.cme_futures_tickerhead_list import time con = msu.get_my_sql_connection(**kwargs) if 'settle_date' in kwargs.keys(): run_date = kwargs['settle_date'] else: run_date = int(time.strftime('%Y%m%d')) #run_date = 20160225 data_vendor_id = 2 now = datetime.datetime.now() run_datetime = cu.convert_doubledate_2datetime(run_date) for ticker_head in ticker_head_list: #print(ticker_head) contract_list = [] bday_us = CustomBusinessDay(calendar=exp.get_calendar_4ticker_head(ticker_head)) if not exp.is_business_day(double_date=run_date, reference_tickerhead=ticker_head): continue cme_output = pcf.process_cme_futures_4tickerhead(ticker_head=ticker_head, report_date=run_date) settle_frame = cme_output['settle_frame'] for ticker_month in cmi.futures_contract_months[ticker_head]: ticker_month_num = cmi.letter_month_string.find(ticker_month)+1 max_cal_dte = cmi.get_max_cal_dte(ticker_head=ticker_head, ticker_month=ticker_month_num) contract_list.extend(cl.get_db_contract_list_filtered(expiration_date_from=run_date, expiration_date_to=cu.doubledate_shift(run_date, -max_cal_dte), ticker_head=ticker_head, ticker_month=ticker_month_num, con=con, instrument='futures')) contract_frame = pd.DataFrame(contract_list, columns=['symbol_id', 'ticker', 'expiration_date']) merged_frame = pd.merge(contract_frame,settle_frame, how='inner', on='ticker') merged_frame.sort('expiration_date', ascending=True, inplace=True) column_names = merged_frame.columns.tolist() symbol_id_indx = column_names.index('symbol_id') ticker_month_indx = column_names.index('ticker_month') open_indx = column_names.index('open') high_indx = column_names.index('high') low_indx = column_names.index('low') settle_indx = column_names.index('settle') volume_indx = column_names.index('volume') interest_indx = column_names.index('interest') expiration_indx = column_names.index('expiration_date') dts = pd.date_range(start=run_datetime, end=merged_frame['expiration_date'].iloc[-1], freq=bday_us) tuples = [tuple([data_vendor_id, x[symbol_id_indx], ticker_head, x[ticker_month_indx], run_datetime.date(), (x[expiration_indx]-run_datetime.date()).days, len([y for y in dts if y.to_datetime().date() < x[expiration_indx]]), now, now, None if np.isnan(x[open_indx]) else x[open_indx], None if np.isnan(x[high_indx]) else x[high_indx], None if np.isnan(x[low_indx]) else x[low_indx], None if np.isnan(x[settle_indx]) else x[settle_indx], None if np.isnan(x[volume_indx]) else x[volume_indx], None if np.isnan(x[interest_indx]) else x[interest_indx]]) for x in merged_frame.values] column_str = "data_vendor_id, symbol_id, ticker_head, ticker_month, price_date,cal_dte, tr_dte, created_date,last_updated_date, open_price, high_price, low_price, close_price, volume, open_interest" insert_str = ("%s, " * 15)[:-2] final_str = "REPLACE INTO daily_price (%s) VALUES (%s)" % (column_str, insert_str) msu.sql_execute_many_wrapper(final_str=final_str, tuples=tuples, con=con) if 'con' not in kwargs.keys(): con.close()
def get_futures_butterfly_signals(**kwargs): ticker_list = kwargs['ticker_list'] date_to = kwargs['date_to'] if 'tr_dte_list' in kwargs.keys(): tr_dte_list = kwargs['tr_dte_list'] else: tr_dte_list = [exp.get_futures_days2_expiration({'ticker': x,'date_to': date_to}) for x in ticker_list] if 'aggregation_method' in kwargs.keys() and 'contracts_back' in kwargs.keys(): aggregation_method = kwargs['aggregation_method'] contracts_back = kwargs['contracts_back'] else: amcb_output = opUtil.get_aggregation_method_contracts_back(cmi.get_contract_specs(ticker_list[0])) aggregation_method = amcb_output['aggregation_method'] contracts_back = amcb_output['contracts_back'] if 'use_last_as_current' in kwargs.keys(): use_last_as_current = kwargs['use_last_as_current'] else: use_last_as_current = False 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 [cmi.get_contract_specs(ticker_list[0])['ticker_head']]} if 'contract_multiplier' in kwargs.keys(): contract_multiplier = kwargs['contract_multiplier'] else: contract_multiplier = cmi.contract_multiplier[cmi.get_contract_specs(ticker_list[0])['ticker_head']] if 'datetime5_years_ago' in kwargs.keys(): datetime5_years_ago = kwargs['datetime5_years_ago'] else: date5_years_ago = cu.doubledate_shift(date_to,5*365) datetime5_years_ago = cu.convert_doubledate_2datetime(date5_years_ago) if 'datetime2_months_ago' in kwargs.keys(): datetime2_months_ago = kwargs['datetime2_months_ago'] else: date2_months_ago = cu.doubledate_shift(date_to,60) datetime2_months_ago = cu.convert_doubledate_2datetime(date2_months_ago) aligned_output = opUtil.get_aligned_futures_data(contract_list=ticker_list, tr_dte_list=tr_dte_list, aggregation_method=aggregation_method, contracts_back=contracts_back, date_to=date_to, futures_data_dictionary=futures_data_dictionary, use_last_as_current=use_last_as_current) current_data = aligned_output['current_data'] aligned_data = aligned_output['aligned_data'] month_diff_1 = 12*(current_data['c1']['ticker_year']-current_data['c2']['ticker_year'])+(current_data['c1']['ticker_month']-current_data['c2']['ticker_month']) month_diff_2 = 12*(current_data['c2']['ticker_year']-current_data['c3']['ticker_year'])+(current_data['c2']['ticker_month']-current_data['c3']['ticker_month']) weight_11 = 2*month_diff_2/(month_diff_1+month_diff_1) weight_12 = -2 weight_13 = 2*month_diff_1/(month_diff_1+month_diff_1) price_1 = current_data['c1']['close_price'] price_2 = current_data['c2']['close_price'] price_3 = current_data['c3']['close_price'] linear_interp_price2 = (weight_11*aligned_data['c1']['close_price']+weight_13*aligned_data['c3']['close_price'])/2 butterfly_price = aligned_data['c1']['close_price']-2*aligned_data['c2']['close_price']+aligned_data['c3']['close_price'] price_ratio = linear_interp_price2/aligned_data['c2']['close_price'] linear_interp_price2_current = (weight_11*price_1+weight_13*price_3)/2 price_ratio_current = linear_interp_price2_current/price_2 q = stats.get_quantile_from_number({'x': price_ratio_current, 'y': price_ratio.values, 'clean_num_obs': max(100, round(3*len(price_ratio.values)/4))}) qf = stats.get_quantile_from_number({'x': price_ratio_current, 'y': price_ratio.values[-40:], 'clean_num_obs': 30}) recent_quantile_list = [stats.get_quantile_from_number({'x': x, 'y': price_ratio.values[-40:], 'clean_num_obs': 30}) for x in price_ratio.values[-40:]] weight1 = weight_11 weight2 = weight_12 weight3 = weight_13 last5_years_indx = aligned_data['settle_date']>=datetime5_years_ago last2_months_indx = aligned_data['settle_date']>=datetime2_months_ago data_last5_years = aligned_data[last5_years_indx] yield1 = 100*(aligned_data['c1']['close_price']-aligned_data['c2']['close_price'])/aligned_data['c2']['close_price'] yield2 = 100*(aligned_data['c2']['close_price']-aligned_data['c3']['close_price'])/aligned_data['c3']['close_price'] yield1_last5_years = yield1[last5_years_indx] yield2_last5_years = yield2[last5_years_indx] yield1_current = 100*(current_data['c1']['close_price']-current_data['c2']['close_price'])/current_data['c2']['close_price'] yield2_current = 100*(current_data['c2']['close_price']-current_data['c3']['close_price'])/current_data['c3']['close_price'] butterfly_price_current = current_data['c1']['close_price']\ -2*current_data['c2']['close_price']\ +current_data['c3']['close_price'] yield_regress_output = stats.get_regression_results({'x':yield2, 'y':yield1,'x_current': yield2_current, 'y_current': yield1_current, 'clean_num_obs': max(100, round(3*len(yield1.values)/4))}) yield_regress_output_last5_years = stats.get_regression_results({'x':yield2_last5_years, 'y':yield1_last5_years, 'x_current': yield2_current, 'y_current': yield1_current, 'clean_num_obs': max(100, round(3*len(yield1_last5_years.values)/4))}) bf_qz_frame_short = pd.DataFrame() bf_qz_frame_long = pd.DataFrame() if (len(yield1) >= 40)&(len(yield2) >= 40): recent_zscore_list = [(yield1[-40+i]-yield_regress_output['alpha']-yield_regress_output['beta']*yield2[-40+i])/yield_regress_output['residualstd'] for i in range(40)] bf_qz_frame = pd.DataFrame.from_items([('bf_price', butterfly_price.values[-40:]), ('q',recent_quantile_list), ('zscore', recent_zscore_list)]) bf_qz_frame = np.round(bf_qz_frame, 8) bf_qz_frame.drop_duplicates(['bf_price'], take_last=True, inplace=True) # return bf_qz_frame bf_qz_frame_short = bf_qz_frame[(bf_qz_frame['zscore'] >= 0.6) & (bf_qz_frame['q'] >= 85)] bf_qz_frame_long = bf_qz_frame[(bf_qz_frame['zscore'] <= -0.6) & (bf_qz_frame['q'] <= 12)] if bf_qz_frame_short.empty: short_price_limit = np.NAN else: short_price_limit = bf_qz_frame_short['bf_price'].min() if bf_qz_frame_long.empty: long_price_limit = np.NAN else: long_price_limit = bf_qz_frame_long['bf_price'].max() zscore1= yield_regress_output['zscore'] rsquared1= yield_regress_output['rsquared'] zscore2= yield_regress_output_last5_years['zscore'] rsquared2= yield_regress_output_last5_years['rsquared'] second_spread_weight_1 = yield_regress_output['beta'] second_spread_weight_2 = yield_regress_output_last5_years['beta'] butterfly_5_change = data_last5_years['c1']['change_5']\ - (1+second_spread_weight_1)*data_last5_years['c2']['change_5']\ + second_spread_weight_1*data_last5_years['c3']['change_5'] butterfly_5_change_current = current_data['c1']['change_5']\ - (1+second_spread_weight_1)*current_data['c2']['change_5']\ + second_spread_weight_1*current_data['c3']['change_5'] butterfly_1_change = data_last5_years['c1']['change_1']\ - (1+second_spread_weight_1)*data_last5_years['c2']['change_1']\ + second_spread_weight_1*data_last5_years['c3']['change_1'] percentile_vector = stats.get_number_from_quantile(y=butterfly_5_change.values, quantile_list=[1, 15, 85, 99], clean_num_obs=max(100, round(3*len(butterfly_5_change.values)/4))) downside = contract_multiplier*(percentile_vector[0]+percentile_vector[1])/2 upside = contract_multiplier*(percentile_vector[2]+percentile_vector[3])/2 recent_5day_pnl = contract_multiplier*butterfly_5_change_current residuals = yield1-yield_regress_output['alpha']-yield_regress_output['beta']*yield2 regime_change_ind = (residuals[last5_years_indx].mean()-residuals.mean())/residuals.std() contract_seasonality_ind = (residuals[aligned_data['c1']['ticker_month'] == current_data['c1']['ticker_month']].mean()-residuals.mean())/residuals.std() yield1_quantile_list = stats.get_number_from_quantile(y=yield1, quantile_list=[10, 90]) yield2_quantile_list = stats.get_number_from_quantile(y=yield2, quantile_list=[10, 90]) noise_ratio = (yield1_quantile_list[1]-yield1_quantile_list[0])/(yield2_quantile_list[1]-yield2_quantile_list[0]) daily_noise_recent = stats.get_stdev(x=butterfly_1_change.values[-20:], clean_num_obs=15) daily_noise_past = stats.get_stdev(x=butterfly_1_change.values, clean_num_obs=max(100, round(3*len(butterfly_1_change.values)/4))) recent_vol_ratio = daily_noise_recent/daily_noise_past alpha1 = yield_regress_output['alpha'] residuals_last5_years = residuals[last5_years_indx] residuals_last2_months = residuals[last2_months_indx] residual_current = yield1_current-alpha1-second_spread_weight_1*yield2_current z3 = (residual_current-residuals_last5_years.mean())/residuals.std() z4 = (residual_current-residuals_last2_months.mean())/residuals.std() yield_change = (alpha1+second_spread_weight_1*yield2_current-yield1_current)/(1+second_spread_weight_1) new_yield1 = yield1_current + yield_change new_yield2 = yield2_current - yield_change price_change1 = 100*((price_2*(new_yield1+100)/100)-price_1)/(200+new_yield1) price_change2 = 100*((price_3*(new_yield2+100)/100)-price_2)/(200+new_yield2) theo_pnl = contract_multiplier*(2*price_change1-2*second_spread_weight_1*price_change2) aligned_data['residuals'] = residuals aligned_output['aligned_data'] = aligned_data grouped = aligned_data.groupby(aligned_data['c1']['cont_indx']) aligned_data['shifted_residuals'] = grouped['residuals'].shift(-5) aligned_data['residual_change'] = aligned_data['shifted_residuals']-aligned_data['residuals'] mean_reversion = stats.get_regression_results({'x':aligned_data['residuals'].values, 'y':aligned_data['residual_change'].values, 'clean_num_obs': max(100, round(3*len(yield1.values)/4))}) theo_spread_move_output = su.calc_theo_spread_move_from_ratio_normalization(ratio_time_series=price_ratio.values[-40:], starting_quantile=qf, num_price=linear_interp_price2_current, den_price=current_data['c2']['close_price'], favorable_quantile_move_list=[5, 10, 15, 20, 25]) theo_pnl_list = [x*contract_multiplier*2 for x in theo_spread_move_output['theo_spread_move_list']] return {'aligned_output': aligned_output, 'q': q, 'qf': qf, 'theo_pnl_list': theo_pnl_list, 'ratio_target_list': theo_spread_move_output['ratio_target_list'], 'weight1': weight1, 'weight2': weight2, 'weight3': weight3, 'zscore1': zscore1, 'rsquared1': rsquared1, 'zscore2': zscore2, 'rsquared2': rsquared2, 'zscore3': z3, 'zscore4': z4, 'zscore5': zscore1-regime_change_ind, 'zscore6': zscore1-contract_seasonality_ind, 'zscore7': zscore1-regime_change_ind-contract_seasonality_ind, 'theo_pnl': theo_pnl, 'regime_change_ind' : regime_change_ind,'contract_seasonality_ind': contract_seasonality_ind, 'second_spread_weight_1': second_spread_weight_1, 'second_spread_weight_2': second_spread_weight_2, 'downside': downside, 'upside': upside, 'yield1': yield1, 'yield2': yield2, 'yield1_current': yield1_current, 'yield2_current': yield2_current, 'bf_price': butterfly_price_current, 'short_price_limit': short_price_limit,'long_price_limit':long_price_limit, 'noise_ratio': noise_ratio, 'alpha1': alpha1, 'alpha2': yield_regress_output_last5_years['alpha'], 'residual_std1': yield_regress_output['residualstd'], 'residual_std2': yield_regress_output_last5_years['residualstd'], 'recent_vol_ratio': recent_vol_ratio, 'recent_5day_pnl': recent_5day_pnl, 'price_1': price_1, 'price_2': price_2, 'price_3': price_3, 'last5_years_indx': last5_years_indx, 'price_ratio': price_ratio, 'mean_reversion_rsquared': mean_reversion['rsquared'], 'mean_reversion_signif' : (mean_reversion['conf_int'][1, :] < 0).all()}
def get_tms(**kwargs): date_to = kwargs['date_to'] feature_list = ['obv', 'rsi_6', 'rsi_12', 'atr_14', 'mfi_14', 'adx_14', 'adx_20', 'rocr_1', 'rocr_3', 'rocr_12', 'cci_12', 'cci_20', 'macd_12_26', 'macd_signal_12_26_9', 'macd_hist_12_26_9', 'williams_r_10', 'tsf_10_3', 'tsf_20_3', 'trix_15'] if 'ticker_head' in kwargs.keys(): ticker_head = kwargs['ticker_head'] panel_data = gfp.get_futures_price_preloaded(ticker_head=ticker_head, settle_date_to=date_to, settle_date_from=cu.doubledate_shift(date_to, 11 * 365)) panel_data.rename(columns={'open_price': 'open', 'high_price': 'high', 'low_price': 'low', 'close_price': 'close'},inplace=True) panel_data.sort_values(['cont_indx', 'settle_date'], ascending=[True, True], inplace=True) unique_cont_indx_list = panel_data['cont_indx'].unique() contract_data_list = [] for i in range(len(unique_cont_indx_list)): contract_data = panel_data[panel_data['cont_indx'] == unique_cont_indx_list[i]] if len(contract_data.index) < 30: continue contract_data = calc_tms(contract_data=contract_data) contract_data_list.append(contract_data) merged_frame = pd.concat(contract_data_list) merged_frame.dropna(subset=feature_list, inplace=True) merged_frame = merged_frame[merged_frame['cal_dte'] > 30] merged_frame.sort_values(['settle_date', 'cont_indx'], ascending=[True, True], inplace=True) data_out = merged_frame.drop_duplicates('settle_date', inplace=False, keep='first') elif 'stock_ticker' in kwargs.keys(): contract_data = gsp.get_stock_price_preloaded(ticker='AAPL', settle_date_to=date_to, settle_date_from=cu.doubledate_shift(date_to, 11 * 365)) contract_data = contract_data[['close', 'settle_datetime', 'volume', 'high', 'low', 'split_coefficient']] contract_data.reset_index(drop=True,inplace=True) split_envents = contract_data[contract_data['split_coefficient'] != 1] split_index_list = split_envents.index for i in range(len(split_index_list)): contract_data['close'].iloc[:split_index_list[i]] = \ contract_data['close'].iloc[:split_index_list[i]] / contract_data['split_coefficient'].iloc[split_index_list[i]] contract_data['high'].iloc[:split_index_list[i]] = \ contract_data['high'].iloc[:split_index_list[i]] / contract_data['split_coefficient'].iloc[ split_index_list[i]] contract_data['low'].iloc[:split_index_list[i]] = \ contract_data['low'].iloc[:split_index_list[i]] / contract_data['split_coefficient'].iloc[ split_index_list[i]] contract_data.rename(columns={'settle_datetime': 'settle_date'}, inplace=True) contract_data['change_1'] = contract_data.close - contract_data.close.shift(1) data_out = calc_tms(contract_data=contract_data) data_out.dropna(subset=feature_list, inplace=True) return data_out
def get_intraday_spread_signals(**kwargs): ticker_list = kwargs['ticker_list'] date_to = kwargs['date_to'] #print(ticker_list) ticker_list = [x for x in ticker_list if x is not None] ticker_head_list = [ cmi.get_contract_specs(x)['ticker_head'] for x in ticker_list ] ticker_class_list = [cmi.ticker_class[x] for x in ticker_head_list] #print('-'.join(ticker_list)) if 'tr_dte_list' in kwargs.keys(): tr_dte_list = kwargs['tr_dte_list'] else: tr_dte_list = [ exp.get_days2_expiration(ticker=x, date_to=date_to, instrument='futures')['tr_dte'] for x in ticker_list ] if 'aggregation_method' in kwargs.keys( ) and 'contracts_back' in kwargs.keys(): aggregation_method = kwargs['aggregation_method'] contracts_back = kwargs['contracts_back'] else: amcb_output = [ opUtil.get_aggregation_method_contracts_back( cmi.get_contract_specs(x)) for x in ticker_list ] aggregation_method = max( [x['aggregation_method'] for x in amcb_output]) contracts_back = min([x['contracts_back'] for x in amcb_output]) 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 list(set(ticker_head_list)) } if 'use_last_as_current' in kwargs.keys(): use_last_as_current = kwargs['use_last_as_current'] else: use_last_as_current = True if 'datetime5_years_ago' in kwargs.keys(): datetime5_years_ago = kwargs['datetime5_years_ago'] else: date5_years_ago = cu.doubledate_shift(date_to, 5 * 365) datetime5_years_ago = cu.convert_doubledate_2datetime(date5_years_ago) if 'num_days_back_4intraday' in kwargs.keys(): num_days_back_4intraday = kwargs['num_days_back_4intraday'] else: num_days_back_4intraday = 10 contract_multiplier_list = [ cmi.contract_multiplier[x] for x in ticker_head_list ] aligned_output = opUtil.get_aligned_futures_data( contract_list=ticker_list, tr_dte_list=tr_dte_list, aggregation_method=aggregation_method, contracts_back=contracts_back, date_to=date_to, futures_data_dictionary=futures_data_dictionary, use_last_as_current=use_last_as_current) aligned_data = aligned_output['aligned_data'] current_data = aligned_output['current_data'] if ticker_head_list in fixed_weight_future_spread_list: weights_output = sutil.get_spread_weights_4contract_list( ticker_head_list=ticker_head_list) spread_weights = weights_output['spread_weights'] portfolio_weights = weights_output['portfolio_weights'] else: regress_output = stats.get_regression_results({ 'x': aligned_data['c2']['change_1'][-60:], 'y': aligned_data['c1']['change_1'][-60:] }) spread_weights = [1, -regress_output['beta']] portfolio_weights = [ 1, -regress_output['beta'] * contract_multiplier_list[0] / contract_multiplier_list[1] ] aligned_data['spread'] = 0 aligned_data['spread_pnl_1'] = 0 aligned_data['spread_pnl1'] = 0 spread_settle = 0 last5_years_indx = aligned_data['settle_date'] >= datetime5_years_ago num_contracts = len(ticker_list) for i in range(num_contracts): aligned_data['spread'] = aligned_data['spread'] + aligned_data[ 'c' + str(i + 1)]['close_price'] * spread_weights[i] spread_settle = spread_settle + current_data[ 'c' + str(i + 1)]['close_price'] * spread_weights[i] aligned_data[ 'spread_pnl_1'] = aligned_data['spread_pnl_1'] + aligned_data[ 'c' + str(i + 1)]['change_1'] * portfolio_weights[ i] * contract_multiplier_list[i] aligned_data[ 'spread_pnl1'] = aligned_data['spread_pnl1'] + aligned_data[ 'c' + str(i + 1)]['change1_instant'] * portfolio_weights[ i] * contract_multiplier_list[i] aligned_data['spread_normalized'] = aligned_data['spread'] / aligned_data[ 'c1']['close_price'] data_last5_years = aligned_data[last5_years_indx] percentile_vector = stats.get_number_from_quantile( y=data_last5_years['spread_pnl_1'].values, quantile_list=[1, 15, 85, 99], clean_num_obs=max(100, round(3 * len(data_last5_years.index) / 4))) downside = (percentile_vector[0] + percentile_vector[1]) / 2 upside = (percentile_vector[2] + percentile_vector[3]) / 2 date_list = [ exp.doubledate_shift_bus_days(double_date=date_to, shift_in_days=x) for x in reversed(range(1, num_days_back_4intraday)) ] date_list.append(date_to) intraday_data = opUtil.get_aligned_futures_data_intraday( contract_list=ticker_list, date_list=date_list) if len(intraday_data.index) == 0: return { 'downside': downside, 'upside': upside, 'intraday_data': intraday_data, 'trading_data': intraday_data, 'spread_weight': spread_weights[1], 'portfolio_weight': portfolio_weights[1], 'z': np.nan, 'recent_trend': np.nan, 'intraday_mean10': np.nan, 'intraday_std10': np.nan, 'intraday_mean5': np.nan, 'intraday_std5': np.nan, 'intraday_mean2': np.nan, 'intraday_std2': np.nan, 'intraday_mean1': np.nan, 'intraday_std1': np.nan, 'aligned_output': aligned_output, 'spread_settle': spread_settle, 'data_last5_years': data_last5_years, 'ma_spread_lowL': np.nan, 'ma_spread_highL': np.nan, 'ma_spread_low': np.nan, 'ma_spread_high': np.nan, 'intraday_sharp': np.nan } intraday_data['time_stamp'] = [ x.to_datetime() for x in intraday_data.index ] intraday_data['settle_date'] = intraday_data['time_stamp'].apply( lambda x: x.date()) end_hour = min([cmi.last_trade_hour_minute[x] for x in ticker_head_list]) start_hour = max( [cmi.first_trade_hour_minute[x] for x in ticker_head_list]) trade_start_hour = dt.time(9, 30, 0, 0) if 'Ag' in ticker_class_list: start_hour1 = dt.time(0, 45, 0, 0) end_hour1 = dt.time(7, 45, 0, 0) selection_indx = [ x for x in range(len(intraday_data.index)) if ((intraday_data['time_stamp'].iloc[x].time() < end_hour1) and (intraday_data['time_stamp'].iloc[x].time() >= start_hour1)) or ((intraday_data['time_stamp'].iloc[x].time() < end_hour) and (intraday_data['time_stamp'].iloc[x].time() >= start_hour)) ] else: selection_indx = [ x for x in range(len(intraday_data.index)) if (intraday_data.index[x].to_datetime().time() < end_hour) and ( intraday_data.index[x].to_datetime().time() >= start_hour) ] intraday_data = intraday_data.iloc[selection_indx] intraday_data['spread'] = 0 for i in range(num_contracts): intraday_data[ 'c' + str(i + 1), 'mid_p'] = (intraday_data['c' + str(i + 1)]['best_bid_p'] + intraday_data['c' + str(i + 1)]['best_ask_p']) / 2 intraday_data['spread'] = intraday_data['spread'] + intraday_data[ 'c' + str(i + 1)]['mid_p'] * spread_weights[i] unique_settle_dates = intraday_data['settle_date'].unique() intraday_data['spread1'] = np.nan for i in range(len(unique_settle_dates) - 1): if (intraday_data['settle_date'] == unique_settle_dates[i]).sum() == \ (intraday_data['settle_date'] == unique_settle_dates[i+1]).sum(): intraday_data.loc[intraday_data['settle_date'] == unique_settle_dates[i],'spread1'] = \ intraday_data['spread'][intraday_data['settle_date'] == unique_settle_dates[i+1]].values intraday_data = intraday_data[intraday_data['settle_date'].notnull()] intraday_mean10 = intraday_data['spread'].mean() intraday_std10 = intraday_data['spread'].std() intraday_data_last5days = intraday_data[ intraday_data['settle_date'] >= cu.convert_doubledate_2datetime( date_list[-5]).date()] intraday_data_last2days = intraday_data[ intraday_data['settle_date'] >= cu.convert_doubledate_2datetime( date_list[-2]).date()] intraday_data_yesterday = intraday_data[intraday_data['settle_date'] == cu.convert_doubledate_2datetime( date_list[-1]).date()] intraday_mean5 = intraday_data_last5days['spread'].mean() intraday_std5 = intraday_data_last5days['spread'].std() intraday_mean2 = intraday_data_last2days['spread'].mean() intraday_std2 = intraday_data_last2days['spread'].std() intraday_mean1 = intraday_data_yesterday['spread'].mean() intraday_std1 = intraday_data_yesterday['spread'].std() intraday_z = (spread_settle - intraday_mean5) / intraday_std5 num_obs_intraday = len(intraday_data.index) num_obs_intraday_half = round(num_obs_intraday / 2) intraday_tail = intraday_data.tail(num_obs_intraday_half) num_positives = sum( intraday_tail['spread'] > intraday_data['spread'].mean()) num_negatives = sum( intraday_tail['spread'] < intraday_data['spread'].mean()) if num_positives + num_negatives != 0: recent_trend = 100 * (num_positives - num_negatives) / (num_positives + num_negatives) else: recent_trend = np.nan intraday_data_shifted = intraday_data.groupby('settle_date').shift(-60) intraday_data['spread_shifted'] = intraday_data_shifted['spread'] intraday_data[ 'delta60'] = intraday_data['spread_shifted'] - intraday_data['spread'] intraday_data['ewma10'] = pd.ewma(intraday_data['spread'], span=10) intraday_data['ewma50'] = pd.ewma(intraday_data['spread'], span=50) intraday_data['ewma200'] = pd.ewma(intraday_data['spread'], span=200) intraday_data['ma40'] = pd.rolling_mean(intraday_data['spread'], 40) intraday_data[ 'ewma50_spread'] = intraday_data['spread'] - intraday_data['ewma50'] intraday_data[ 'ma40_spread'] = intraday_data['spread'] - intraday_data['ma40'] selection_indx = [ x for x in range(len(intraday_data.index)) if (intraday_data['time_stamp'].iloc[x].time() > trade_start_hour) ] selected_data = intraday_data.iloc[selection_indx] selected_data['delta60Net'] = (contract_multiplier_list[0] * selected_data['delta60'] / spread_weights[0]) selected_data.reset_index(drop=True, inplace=True) selected_data['proxy_pnl'] = 0 t_cost = cmi.t_cost[ticker_head_list[0]] ma_spread_low = np.nan ma_spread_high = np.nan ma_spread_lowL = np.nan ma_spread_highL = np.nan intraday_sharp = np.nan if sum(selected_data['ma40_spread'].notnull()) > 30: quantile_list = selected_data['ma40_spread'].quantile([0.1, 0.9]) down_indx = selected_data['ma40_spread'] < quantile_list[0.1] up_indx = selected_data['ma40_spread'] > quantile_list[0.9] up_data = selected_data[up_indx] down_data = selected_data[down_indx] ma_spread_lowL = quantile_list[0.1] ma_spread_highL = quantile_list[0.9] #return {'selected_data':selected_data,'up_data':up_data,'up_indx':up_indx} selected_data.loc[up_indx, 'proxy_pnl'] = (-up_data['delta60Net'] - 2 * num_contracts * t_cost).values selected_data.loc[down_indx, 'proxy_pnl'] = (down_data['delta60Net'] - 2 * num_contracts * t_cost).values short_term_data = selected_data[ selected_data['settle_date'] >= cu.convert_doubledate_2datetime( date_list[-5]).date()] if sum(short_term_data['ma40_spread'].notnull()) > 30: quantile_list = short_term_data['ma40_spread'].quantile([0.1, 0.9]) ma_spread_low = quantile_list[0.1] ma_spread_high = quantile_list[0.9] if selected_data['proxy_pnl'].std() != 0: intraday_sharp = selected_data['proxy_pnl'].mean( ) / selected_data['proxy_pnl'].std() return { 'downside': downside, 'upside': upside, 'intraday_data': intraday_data, 'trading_data': selected_data, 'spread_weight': spread_weights[1], 'portfolio_weight': portfolio_weights[1], 'z': intraday_z, 'recent_trend': recent_trend, 'intraday_mean10': intraday_mean10, 'intraday_std10': intraday_std10, 'intraday_mean5': intraday_mean5, 'intraday_std5': intraday_std5, 'intraday_mean2': intraday_mean2, 'intraday_std2': intraday_std2, 'intraday_mean1': intraday_mean1, 'intraday_std1': intraday_std1, 'aligned_output': aligned_output, 'spread_settle': spread_settle, 'data_last5_years': data_last5_years, 'ma_spread_lowL': ma_spread_lowL, 'ma_spread_highL': ma_spread_highL, 'ma_spread_low': ma_spread_low, 'ma_spread_high': ma_spread_high, 'intraday_sharp': intraday_sharp }
def get_aligned_futures_data(**kwargs): contract_list = kwargs['contract_list'] aggregation_method = kwargs['aggregation_method'] contracts_back = kwargs['contracts_back'] date_to = kwargs['date_to'] if 'use_last_as_current' in kwargs.keys(): use_last_as_current = kwargs['use_last_as_current'] else: use_last_as_current = False if 'tr_dte_list' in kwargs.keys(): tr_dte_list = kwargs['tr_dte_list'] else: tr_dte_list = [exp.get_futures_days2_expiration({'ticker': x,'date_to': date_to}) for x in contract_list] if 'futures_data_dictionary' in kwargs.keys(): futures_data_dictionary = kwargs['futures_data_dictionary'] else: ticker_head_list = [cmi.get_contract_specs(x)['ticker_head'] for x in contract_list] futures_data_dictionary = {x: gfp.get_futures_price_preloaded(ticker_head=x) for x in ticker_head_list} if 'tr_days_half_band_width_selected' in kwargs.keys(): tr_days_half_band_width_selected = kwargs['tr_days_half_band_width_selected'] else: tr_days_half_band_width_selected = tr_days_half_band_with[aggregation_method] if 'get_uniqe_data' in kwargs.keys(): get_uniqe_data = kwargs['get_uniqe_data'] else: get_uniqe_data = True date_from = cu.doubledate_shift(date_to, 2*3650) date_to_datetime = cu.convert_doubledate_2datetime(date_to) date_from_datetime = cu.convert_doubledate_2datetime(date_from) num_contracts = len(contract_list) contract_specs_list = [cmi.get_contract_specs(x) for x in contract_list] data_frame_list = [] for i in range(num_contracts): futures_data_frame = futures_data_dictionary[contract_specs_list[i]['ticker_head']] selection_indx = (futures_data_frame['tr_dte'] >= tr_dte_list[i]-tr_days_half_band_width_selected)& \ (futures_data_frame['tr_dte'] <= tr_dte_list[i]+tr_days_half_band_width_selected)& \ (futures_data_frame['cal_dte'] >= 0)& \ (futures_data_frame['settle_date'] >= date_from_datetime) & \ (futures_data_frame['settle_date'] <= date_to_datetime) futures_data_frame = futures_data_frame[selection_indx] data_frame_list.append(futures_data_frame) cont_indx_list_rolls = [get_cont_indx_list_history({'current_year': x['ticker_year'], 'current_month': x['ticker_month_num'], 'aggregation_method': aggregation_method, 'contracts_back': contracts_back}) for x in contract_specs_list] merged_dataframe_list = [None]*contracts_back for i in range(contracts_back): if sum([(data_frame_list[j]['cont_indx'] == cont_indx_list_rolls[j][i]).any() for j in range(len(contract_list))])<len(contract_list): continue contract_data_list = [None]*num_contracts for k in range(num_contracts): contract_data_list[k] = data_frame_list[k][data_frame_list[k]['cont_indx']==cont_indx_list_rolls[k][i]] contract_data_list[k].set_index('settle_date',inplace=True) merged_dataframe_list[i] = pd.concat(contract_data_list, axis=1, join='inner',keys=['c'+ str(x+1) for x in range(num_contracts)]) aligned_dataframe = pd.concat(merged_dataframe_list) aligned_dataframe.sort_index(inplace=True) aligned_dataframe['settle_date'] = aligned_dataframe.index if get_uniqe_data: aligned_dataframe['tr_dte_match'] = abs(aligned_dataframe['c1']['tr_dte']-tr_dte_list[0]) aligned_dataframe.index.names = ['settle_date_index' if x is 'settle_date' else x for x in aligned_dataframe.index.names] aligned_dataframe.sort_values(['settle_date','tr_dte_match'],ascending=[True,True],inplace=True) unique_index = np.unique(aligned_dataframe['settle_date'], return_index=True)[1] aligned_dataframe = aligned_dataframe.iloc[unique_index] success = True if use_last_as_current: current_data = aligned_dataframe.iloc[-1] elif date_to_datetime in aligned_dataframe.index: current_data = aligned_dataframe.loc[cu.convert_doubledate_2datetime(date_to)] else: current_data = [] success = False return {'aligned_data': aligned_dataframe, 'current_data': current_data,'success': success}