def get_continuous_bar_data(**kwargs):
date_to = kwargs['date_to']
num_days_back = kwargs['num_days_back']
ticker = kwargs['ticker']
if 'boto_client' in kwargs.keys():
boto_client = kwargs['boto_client']
else:
boto_client = get_boto_client()
date_list = [exp.doubledate_shift_bus_days(double_date=date_to, shift_in_days=x) for x in reversed(range(1, num_days_back))]
date_list.append(date_to)
bar_data_list = [get_book_snapshot(ticker=ticker,utc_doubledate=x,boto_client=boto_client) for x in date_list]
bar_data = pd.concat(bar_data_list)
contract_specs_output = cmi.get_contract_specs(ticker)
ticker_class = contract_specs_output['ticker_class']
if ticker_class == 'Livestock':
data_out = bar_data[(bar_data['hour_minute'] >= 830) & (bar_data['hour_minute'] < 1305)]
elif ticker_class == 'Ag':
data_out = bar_data[((bar_data['hour_minute'] >= 1900) & (bar_data['hour_minute'] <= 2359)) |
(bar_data['hour_minute'] < 745) |
((bar_data['hour_minute'] >= 830) & (bar_data['hour_minute'] < 1320))]
elif ticker_class in ['Energy', 'STIR', 'Index', 'FX', 'Treasury', 'Metal']:
data_out = bar_data[(bar_data['hour_minute'] < 1600) | (bar_data['hour_minute'] >= 1700)]
return data_out
def generate_ifs_formatted_output(**kwargs):
if 'report_date' in kwargs.keys():
report_date = kwargs['report_date']
else:
report_date = exp.doubledate_shift_bus_days()
output_dir = ts.create_strategy_output_dir(strategy_class='ifs',
report_date=report_date)
ifs_output = ifs.generate_ifs_sheet_4date(date_to=report_date)
intraday_spreads = ifs_output['intraday_spreads']
intraday_spreads.rename(columns={
'ticker_head1': 'tickerHead1',
'ticker_head2': 'tickerHead2',
'ticker_head3': 'tickerHead3'
},
inplace=True)
writer = pd.ExcelWriter(output_dir + '/' + futil.xls_file_names['ifs'] +
'.xlsx',
engine='xlsxwriter')
intraday_spreads.to_excel(writer, sheet_name='all')
def send_hrsn_report(**kwargs):
daily_dir = dna.get_directory_name(ext='daily')
if 'report_date' in kwargs.keys():
report_date = kwargs['report_date']
else:
report_date = exp.doubledate_shift_bus_days()
ibo_dir = ts.create_strategy_output_dir(strategy_class='os', report_date=report_date)
cov_data_integrity = ''
try:
with open(ibo_dir + '/' + 'covDataIntegrity.txt','r') as text_file:
cov_data_integrity = text_file.read()
except Exception:
pass
try:
expiration_report = ef.get_expiration_report(report_date=report_date, con=kwargs['con'])
expiration_report = expiration_report[expiration_report['tr_days_2roll'] < 5]
if expiration_report.empty:
expiration_text = 'No near expirations.'
else:
expiration_text = 'Check for approaching expirations!'
except Exception:
expiration_text = 'Check expiration report for errors!'
se.send_email_with_attachment(subject='hrsn_' + str(report_date),
email_text='cov_data_integrity: ' + cov_data_integrity + "\r\n" + expiration_text,
attachment_list = [daily_dir + '/' + 'pnl_final_' + str(report_date) + '.xlsx', daily_dir +
'/' + 'followup_' + str(report_date) + '.xlsx'])
def send_dual_momentum_report(**kwargs):
if 'report_date' in kwargs.keys():
report_date = kwargs['report_date']
else:
report_date = exp.doubledate_shift_bus_days()
output = dm.get_signals_4date(report_date=report_date)
if not output['success']:
email_text = 'Report Failed!'
else:
performance_dictionary = output['performance_dictionary']
ivv = performance_dictionary['IVV']
veu = performance_dictionary['VEU']
bil = performance_dictionary['BIL']
if ivv>=max(veu, bil):
recommended_ticker = 'IVV'
elif (veu>ivv) and (ivv>bil):
recommended_ticker = 'VEU'
else:
recommended_ticker = 'AGG'
email_text = 'US Stocks: ' + '% 3.2f' % ivv + "\r\n" \
+ 'World Stocks: ' + '% 3.2f' % veu + "\r\n" \
+ 'T-Bills: ' + ' % 3.2f' % bil + "\r\n" \
+ "\r\n" \
+ 'Recommended Ticker: ' + recommended_ticker
se.send_email_with_attachment(subject='Dual Momentum Results On ' +
cu.convert_datestring_format({'date_string': str(report_date),
'format_from':'yyyymmdd',
'format_to':'yyyy-mm-dd'}),email_text=email_text)
def send_wh_report(**kwargs):
if 'report_date' in kwargs.keys():
report_date = kwargs['report_date']
else:
report_date = exp.doubledate_shift_bus_days()
ta_output_dir = dn.get_dated_directory_extension(folder_date=report_date, ext='ta')
strategy_frame = tpm.get_daily_pnl_snapshot(as_of_date=report_date, name='final')
total_pnl_row = strategy_frame[strategy_frame.alias == 'TOTAL']
report_date_str = cu.convert_datestring_format({'date_string': str(report_date), 'format_from': 'yyyymmdd', 'format_to': 'dd/mm/yyyy'})
config_output = su.read_config_file(file_name=dna.get_directory_name(ext='daily') + '/riskAndMargin.txt')
email_text = "Expected Maximum Drawdown: " + config_output['emd'] + "K" + \
"\nMargin: " + str(int(config_output['iceMargin']) + int(config_output['cmeMargin'])) + "K" + \
"\nNet Liquidating Value: " + config_output['pnl'] + "K" + \
"\n \nSee attached for individual strategy pnls."
se.send_email_with_attachment(send_from='*****@*****.**',
send_to=['*****@*****.**','*****@*****.**'],
sender_account_alias='wh_trading',
subject='Daily PnL for ' + report_date_str + ' is: ' + '${:,}'.format(total_pnl_row['daily_pnl'].iloc[0]),
email_text=email_text,
attachment_list = [ta_output_dir + '/' + 'pnl_final.xlsx'],
attachment_name_list=['PnLs.xlsx'])
def create_sector_classification_file(**kwargs):
file_name = directory_name + '\sector_classification.pkl'
if os.path.isfile(file_name):
return pd.read_pickle(file_name)
if 'report_date' in kwargs.keys():
report_date = kwargs['report_date']
else:
report_date = exp.doubledate_shift_bus_days()
symbol_list = ssd.get_symbol_list_4date(settle_date=report_date)
for i in range(len(symbol_list)):
eqty = Equity(symbol_list[i])
try:
sector_list.append(eqty.sector)
industry_list.append(eqty.industry)
except:
sector_list.append(None)
industry_list.append(None)
sector_classification = pd.DataFrame.from_dict({'ticker': symbol_list, 'sector': sector_list, 'industry': industry_list})
sector_classification.to_pickle(file_name)
return sector_classification
def generate_scv_formatted_output(**kwargs):
if 'report_date' in kwargs.keys():
report_date = kwargs['report_date']
else:
report_date = exp.doubledate_shift_bus_days()
output_dir = ts.create_strategy_output_dir(strategy_class='scv', report_date=report_date)
vcs_output = scv.generate_scv_sheet_4date(date_to=report_date)
scv_frame = vcs_output['scv_frame']
filter_out = of.get_scv_filters(data_frame_input=scv_frame, filter_list=['long1', 'short1'])
good_scv_frame = filter_out['selected_frame']
writer = pd.ExcelWriter(output_dir + '/' + futil.xls_file_names['scv'] + '.xlsx', engine='xlsxwriter')
scv_frame.to_excel(writer, sheet_name='all')
good_scv_frame.to_excel(writer, sheet_name='good')
worksheet_good = writer.sheets['good']
worksheet_all = writer.sheets['all']
worksheet_good.freeze_panes(1, 0)
worksheet_all.freeze_panes(1, 0)
worksheet_good.autofilter(0, 0, len(good_scv_frame.index),
len(good_scv_frame.columns))
worksheet_all.autofilter(0, 0, len(scv_frame.index),
len(scv_frame.columns))
def generate_scv_formatted_output(**kwargs):
if 'report_date' in kwargs.keys():
report_date = kwargs['report_date']
else:
report_date = exp.doubledate_shift_bus_days()
output_dir = ts.create_strategy_output_dir(strategy_class='scv',
report_date=report_date)
vcs_output = scv.generate_scv_sheet_4date(date_to=report_date)
scv_frame = vcs_output['scv_frame']
filter_out = of.get_scv_filters(data_frame_input=scv_frame,
filter_list=['long1', 'short1'])
good_scv_frame = filter_out['selected_frame']
writer = pd.ExcelWriter(output_dir + '/' + futil.xls_file_names['scv'] +
'.xlsx',
engine='xlsxwriter')
scv_frame.to_excel(writer, sheet_name='all')
good_scv_frame.to_excel(writer, sheet_name='good')
worksheet_good = writer.sheets['good']
worksheet_all = writer.sheets['all']
worksheet_good.freeze_panes(1, 0)
worksheet_all.freeze_panes(1, 0)
worksheet_good.autofilter(0, 0, len(good_scv_frame.index),
len(good_scv_frame.columns))
worksheet_all.autofilter(0, 0, len(scv_frame.index),
len(scv_frame.columns))
def move_from_dated_folder_2daily_folder(**kwargs):
ext = kwargs['ext']
file_name_raw = kwargs['file_name']
file_name_split = file_name_raw.split('.')
if len(file_name_split) == 1:
file_name = file_name_raw
file_ext = '.xlsx'
else:
file_name = file_name_split[0]
file_ext = file_name_split[1]
if 'folder_date' in kwargs.keys():
folder_date = kwargs['folder_date']
else:
folder_date = exp.doubledate_shift_bus_days()
dated_folder = dn.get_dated_directory_extension(folder_date=folder_date,
ext=ext)
sutil.copyfile(
dated_folder + '/' + file_name + file_ext,
daily_dir + '/' + file_name + '_' + str(folder_date) + file_ext)
def close_strategy(**kwargs):
alias = kwargs['alias']
if 'close_date' in kwargs.keys():
close_date = kwargs['close_date']
else:
close_date = exp.doubledate_shift_bus_days()
now_time = dt.datetime.now()
now_date = now_time.date()
con = msu.get_my_sql_connection(**kwargs)
net_position = ts.get_net_position_4strategy_alias(alias=alias, con=con, as_of_date=close_date)
if net_position.empty:
pnl_output = get_strategy_pnl(alias=alias, con=con, as_of_date=close_date)
total_pnl = pnl_output['total_pnl']
cur = con.cursor()
query_str = 'UPDATE strategy SET pnl=' + str(total_pnl) + \
', close_date=' + str(close_date) + \
', last_updated_date=' + now_date.strftime('%Y%m%d') + \
' WHERE alias=\'' + alias + '\''
cur.execute(query_str)
con.commit()
else:
print(alias + ' is not empty ! ')
return
if 'con' not in kwargs.keys():
con.close()
def get_strategy_class_historical_pnls(**kwargs):
con = msu.get_my_sql_connection(**kwargs)
if 'as_of_date' in kwargs.keys():
as_of_date = kwargs['as_of_date']
else:
as_of_date = exp.doubledate_shift_bus_days()
strategy_frame = ts.select_strategies(con=con,open_date_to=as_of_date)
strategy_frame['strategy_class'] = [sc.convert_from_string_to_dictionary(string_input=strategy_frame['description_string'][x])['strategy_class']
for x in range(len(strategy_frame.index))]
unique_strategy_class_list = strategy_frame['strategy_class'].unique()
time_series_list = [None]*len(unique_strategy_class_list)
for i in range(len(unique_strategy_class_list)):
strategy_frame_selected = strategy_frame[strategy_frame['strategy_class'] == unique_strategy_class_list[i]]
pnl_out = [tpnl.get_strategy_pnl(alias=x,as_of_date=as_of_date,con=con)['pnl_frame'][['settle_date','total_pnl']] for x in strategy_frame_selected['alias']]
[x.set_index('settle_date',drop=True, inplace=True) for x in pnl_out]
time_series_list[i] = pd.concat(pnl_out,axis=1).fillna(0).sum(axis=1)
if 'con' not in kwargs.keys():
con.close()
merged_pnl = pd.concat(time_series_list,axis=1,keys=unique_strategy_class_list).fillna(0)
merged_pnl['total'] = merged_pnl.sum(axis=1)
output_dir = dn.get_directory_name(ext='daily')
writer = pd.ExcelWriter(output_dir + '/historical_performance_' + str(as_of_date) + '.xlsx', engine='xlsxwriter')
merged_pnl.to_excel(writer, sheet_name='timeSeries')
writer.save()
return merged_pnl
def get_daily_pnl_snapshot(**kwargs):
if "as_of_date" not in kwargs.keys():
as_of_date = exp.doubledate_shift_bus_days()
kwargs["as_of_date"] = as_of_date
else:
as_of_date = kwargs["as_of_date"]
ta_output_dir = dn.get_dated_directory_extension(folder_date=as_of_date, ext="ta")
if os.path.isfile(ta_output_dir + "/portfolio_pnl.pkl"):
strategy_frame = pd.read_pickle(ta_output_dir + "/portfolio_pnl.pkl")
return strategy_frame
strategy_frame = ts.get_open_strategies(**kwargs)
pnl_output = [tapnl.get_strategy_pnl(alias=x, **kwargs) for x in strategy_frame["alias"]]
strategy_frame["daily_pnl"] = [x["daily_pnl"] for x in pnl_output]
strategy_frame["total_pnl"] = [x["total_pnl"] for x in pnl_output]
strategy_frame = strategy_frame[["alias", "daily_pnl", "total_pnl"]]
strategy_frame.sort("daily_pnl", ascending=False, inplace=True)
strategy_frame.loc[max(strategy_frame.index) + 1] = [
"TOTAL",
strategy_frame["daily_pnl"].sum(),
strategy_frame["total_pnl"].sum(),
]
strategy_frame.to_pickle(ta_output_dir + "/portfolio_pnl.pkl")
return strategy_frame
def get_intraday_outright_covariance(**kwargs):
date_to = kwargs['date_to']
num_days_back_4intraday = 20
liquid_futures_frame = cl.get_liquid_outright_futures_frame(settle_date=date_to)
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=liquid_futures_frame['ticker'].values,date_list=date_list)
intraday_data['time_stamp'] = [x.to_datetime() for x in intraday_data.index]
intraday_data['hour_minute'] = [100*x.hour+x.minute for x in intraday_data['time_stamp']]
intraday_data = intraday_data.resample('30min',how='last')
intraday_data = intraday_data[(intraday_data['hour_minute'] >= 830) &
(intraday_data['hour_minute'] <= 1200)]
intraday_data_shifted = intraday_data.shift(1)
selection_indx = intraday_data['hour_minute']-intraday_data_shifted['hour_minute'] > 0
num_contracts = len(liquid_futures_frame.index)
diff_frame = pd.DataFrame()
for i in range(num_contracts):
mid_p = (intraday_data['c' + str(i+1)]['best_bid_p']+
intraday_data['c' + str(i+1)]['best_ask_p'])/2
mid_p_shifted = (intraday_data_shifted['c' + str(i+1)]['best_bid_p']+
intraday_data_shifted['c' + str(i+1)]['best_ask_p'])/2
diff_frame[liquid_futures_frame['ticker_head'].iloc[i]] = (mid_p-mid_p_shifted)*cmi.contract_multiplier[liquid_futures_frame['ticker_head'].iloc[i]]
diff_frame = diff_frame[selection_indx]
return {'cov_matrix': diff_frame.cov(),
'cov_data_integrity': 100*diff_frame.notnull().sum().sum()/(len(diff_frame.columns)*20*6)}
def get_smrs_signal_frame(**kwargs):
if 'settle_date' in kwargs.keys():
settle_date = kwargs['settle_date']
else:
settle_date = exp.doubledate_shift_bus_days()
output_dir = ts.create_strategy_output_dir(strategy_class='smrs',
report_date=settle_date)
if os.path.isfile(output_dir + '/summary.pkl'):
stocks = pd.read_pickle(output_dir + '/summary.pkl')
return {'stocks': stocks, 'success': True}
symbol_list = get_symbols(settle_date=settle_date)
result_list = [
smr.get_stock_mrs_signals(symbol=symbol_list[i],
settle_date=settle_date)
for i in range(len(symbol_list))
]
stocks = pd.DataFrame(result_list)
stocks.to_pickle(output_dir + '/summary.pkl')
return {'stocks': stocks, 'success': True}
def generate_historic_risk_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
ta_output_dir = dn.get_dated_directory_extension(folder_date=as_of_date, ext='ta')
historic_risk_output = hr.get_historical_risk_4open_strategies(**kwargs)
strategy_risk_frame = historic_risk_output['strategy_risk_frame']
ticker_head_risk_frame = historic_risk_output['ticker_head_risk_frame']
writer = pd.ExcelWriter(ta_output_dir + '/risk.xlsx', engine='xlsxwriter')
strategy_risk_frame.to_excel(writer, sheet_name='strategies')
ticker_head_risk_frame.to_excel(writer, sheet_name='tickerHeads')
worksheet_strategies = writer.sheets['strategies']
worksheet_ticker_heads = writer.sheets['tickerHeads']
worksheet_strategies.freeze_panes(1, 0)
worksheet_ticker_heads.freeze_panes(1, 0)
worksheet_strategies.autofilter(0, 0, len(strategy_risk_frame.index),
len(strategy_risk_frame.columns))
worksheet_ticker_heads.autofilter(0, 0, len(ticker_head_risk_frame.index),
len(ticker_head_risk_frame.columns))
writer.save()
def generate_futures_butterfly_formatted_output(**kwargs):
if 'report_date' in kwargs.keys():
report_date = kwargs['report_date']
else:
report_date = exp.doubledate_shift_bus_days()
output_dir = ts.create_strategy_output_dir(
strategy_class='futures_butterfly', report_date=report_date)
butterfly_output = fb.generate_futures_butterfly_sheet_4date(
date_to=report_date)
butterflies = butterfly_output['butterflies']
filter_out = ff.get_futures_butterfly_filters(
data_frame_input=butterflies, filter_list=['long7', 'short7'])
good_butterflies = filter_out['selected_frame']
good_butterflies = good_butterflies[
(good_butterflies['second_spread_weight_1'] <= 2.5)
& (good_butterflies['second_spread_weight_1'] >= 0.4)]
butterflies_w_selected_columns = butterflies[[
'ticker1', 'ticker2', 'ticker3', 'tickerHead', 'trDte1', 'trDte2',
'trDte3', 'Q', 'QF', 'z1', 'z2', 'z3', 'z4', 'theo_pnl', 'r1', 'r2',
'bf_price', 'RC', 'seasonality', 'second_spread_weight_1', 'upside',
'downside', 'recent_vol_ratio', 'recent_5day_pnl', 'bf_sell_limit',
'bf_buy_limit'
]]
good_butterflies_w_selected_columns = good_butterflies[[
'ticker1', 'ticker2', 'ticker3', 'tickerHead', 'trDte1', 'trDte2',
'trDte3', 'Q', 'QF', 'z1', 'z2', 'z3', 'z4', 'theo_pnl', 'r1', 'r2',
'bf_price', 'RC', 'seasonality', 'second_spread_weight_1', 'upside',
'downside', 'recent_vol_ratio', 'recent_5day_pnl', 'bf_sell_limit',
'bf_buy_limit'
]]
writer = pd.ExcelWriter(
output_dir + '/' + futil.xls_file_names['futures_butterfly'] + '.xlsx',
engine='xlsxwriter')
butterflies_w_selected_columns.to_excel(writer, sheet_name='all')
good_butterflies_w_selected_columns.to_excel(writer, sheet_name='good')
worksheet_good = writer.sheets['good']
worksheet_all = writer.sheets['all']
worksheet_good.freeze_panes(1, 0)
worksheet_all.freeze_panes(1, 0)
worksheet_good.autofilter(0, 0,
len(good_butterflies_w_selected_columns.index),
len(good_butterflies_w_selected_columns.columns))
worksheet_all.autofilter(0, 0, len(butterflies_w_selected_columns.index),
len(butterflies_w_selected_columns.columns))
writer.save()
def get_intraday_outright_covariance(**kwargs):
date_to = kwargs['date_to']
num_days_back_4intraday = 20
liquid_futures_frame = cl.get_liquid_outright_futures_frame(
settle_date=date_to)
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=liquid_futures_frame['ticker'].values,
date_list=date_list)
if len(intraday_data.index) == 0:
return {'cov_matrix': pd.DataFrame(), 'cov_data_integrity': 0}
intraday_data['time_stamp'] = [
x.to_datetime() for x in intraday_data.index
]
intraday_data['hour_minute'] = [
100 * x.hour + x.minute for x in intraday_data['time_stamp']
]
intraday_data = intraday_data.resample('30min', how='last')
intraday_data = intraday_data[(intraday_data['hour_minute'] >= 830)
& (intraday_data['hour_minute'] <= 1200)]
intraday_data_shifted = intraday_data.shift(1)
selection_indx = intraday_data['hour_minute'] - intraday_data_shifted[
'hour_minute'] > 0
num_contracts = len(liquid_futures_frame.index)
diff_frame = pd.DataFrame()
for i in range(num_contracts):
mid_p = (intraday_data['c' + str(i + 1)]['best_bid_p'] +
intraday_data['c' + str(i + 1)]['best_ask_p']) / 2
mid_p_shifted = (
intraday_data_shifted['c' + str(i + 1)]['best_bid_p'] +
intraday_data_shifted['c' + str(i + 1)]['best_ask_p']) / 2
diff_frame[liquid_futures_frame['ticker_head'].iloc[i]] = (
mid_p - mid_p_shifted) * cmi.contract_multiplier[
liquid_futures_frame['ticker_head'].iloc[i]]
diff_frame = diff_frame[selection_indx]
return {
'cov_matrix':
diff_frame.cov(),
'cov_data_integrity':
100 * diff_frame.notnull().sum().sum() /
(len(diff_frame.columns) * 20 * 6)
}
def get_greeks_4strategy_4date(**kwargs):
con = msu.get_my_sql_connection(**kwargs)
alias = kwargs['alias']
if 'as_of_date' in kwargs.keys():
as_of_date = kwargs['as_of_date']
else:
as_of_date = exp.doubledate_shift_bus_days()
position_frame = tas.get_net_position_4strategy_alias(alias=alias,as_of_date=as_of_date,con=con)
options_frame = position_frame[position_frame['instrument'] == 'O']
if options_frame.empty:
if 'con' not in kwargs.keys():
con.close()
return {'ticker_portfolio': pd.DataFrame(columns=['total_oev', 'theta','dollar_vega','ticker']), 'strike_portfolio': pd.DataFrame()}
unique_ticker_list = options_frame['ticker'].unique()
result_list = []
contract_specs_output_list = [cmi.get_contract_specs(x) for x in unique_ticker_list]
contract_multiplier_list = [cmi.contract_multiplier[x['ticker_head']] for x in contract_specs_output_list]
for i in range(len(unique_ticker_list)):
skew_output = gop.get_options_price_from_db(ticker=unique_ticker_list[i],
settle_date=as_of_date,
column_names=['option_type', 'strike', 'theta', 'vega', 'delta'])
skew_output.reset_index(drop=True,inplace=True)
skew_output['delta_diff'] = abs(skew_output['delta']-0.5)
atm_point = skew_output.loc[skew_output['delta_diff'].idxmin()]
skew_output.rename(columns={'strike': 'strike_price'}, inplace=True)
merged_data = pd.merge(options_frame[options_frame['ticker'] == unique_ticker_list[i]], skew_output, how='left', on=['option_type','strike_price'])
merged_data['oev'] = merged_data['vega']/atm_point['vega']
merged_data['total_oev'] = merged_data['oev']*merged_data['qty']
merged_data['dollar_theta'] = merged_data['theta']*merged_data['qty']*contract_multiplier_list[i]
merged_data['dollar_vega'] = merged_data['vega']*merged_data['qty']*contract_multiplier_list[i]/100
result_list.append(merged_data)
strike_portfolio = pd.concat(result_list)
grouped = strike_portfolio.groupby('ticker')
ticker_portfolio = pd.DataFrame()
ticker_portfolio['ticker'] = (grouped['ticker'].first()).values
ticker_portfolio['total_oev'] = (grouped['total_oev'].sum()).values
ticker_portfolio['theta'] = (grouped['dollar_theta'].sum()).values
ticker_portfolio['dollar_vega'] = (grouped['dollar_vega'].sum()).values
if 'con' not in kwargs.keys():
con.close()
return {'ticker_portfolio': ticker_portfolio, 'strike_portfolio': strike_portfolio }
def get_butterfly_panel_plot(**kwargs):
report_date = kwargs['report_date']
id = kwargs['id']
bf_output = fb.generate_futures_butterfly_sheet_4date(date_to=report_date)
butterflies = bf_output['butterflies']
contract_list = [butterflies['ticker1'][id], butterflies['ticker2'][id], butterflies['ticker3'][id]]
tr_dte_list = [butterflies['trDte1'][id], butterflies['trDte2'][id], butterflies['trDte3'][id]]
if 'aggregation_method' in kwargs.keys():
aggregation_method = kwargs['aggregation_method']
else:
aggregation_method = butterflies['agg'][id]
if 'contracts_back' in kwargs.keys():
contracts_back = kwargs['contracts_back']
else:
contracts_back = butterflies['cBack'][id]
post_report_date = exp.doubledate_shift_bus_days(double_date=report_date,shift_in_days=-20)
bf_signals_output = fs.get_futures_butterfly_signals(ticker_list=contract_list,
tr_dte_list=tr_dte_list,
aggregation_method=aggregation_method,
contracts_back=contracts_back,
date_to=post_report_date,
contract_multiplier=butterflies['multiplier'][id],
use_last_as_current=True)
aligned_data = bf_signals_output['aligned_output']['aligned_data']
new_index = list(range(len(aligned_data.index)))
contract_change_indx = (aligned_data['c1']['ticker_year']-aligned_data['c1']['ticker_year'].shift(1)!=0).values
front_contract_year = aligned_data['c1']['ticker_year'] % 10
contract_change_indx[0] = False
report_datetime = cu.convert_doubledate_2datetime(report_date)
x_index = [x for x in new_index if aligned_data['settle_date'][x] == report_datetime][0]
x_tick_locations = [x for x in new_index if contract_change_indx[x]]
x_tick_locations.append(x_index)
x_tick_values = [cmi.letter_month_string[aligned_data['c1']['ticker_month'].values[x]-1]+
str(front_contract_year.values[x]) for x in new_index if contract_change_indx[x]]
x_tick_values.append('X')
plt.figure(figsize=(16, 7))
plt.plot(aligned_data['residuals'])
plt.xticks(x_tick_locations,x_tick_values)
plt.grid()
plt.title('Contracts: ' + str(contract_list) + ', weight2: ' + str(bf_signals_output['second_spread_weight_1'].round(2)))
plt.show()
return bf_signals_output
def get_butterfly_panel_plot(**kwargs):
report_date = kwargs['report_date']
id = kwargs['id']
bf_output = fb.generate_futures_butterfly_sheet_4date(date_to=report_date)
butterflies = bf_output['butterflies']
contract_list = [butterflies['ticker1'][id], butterflies['ticker2'][id], butterflies['ticker3'][id]]
tr_dte_list = [butterflies['trDte1'][id], butterflies['trDte2'][id], butterflies['trDte3'][id]]
if 'aggregation_method' in kwargs.keys():
aggregation_method = kwargs['aggregation_method']
else:
aggregation_method = butterflies['agg'][id]
if 'contracts_back' in kwargs.keys():
contracts_back = kwargs['contracts_back']
else:
contracts_back = butterflies['cBack'][id]
post_report_date = exp.doubledate_shift_bus_days(double_date=report_date,shift_in_days=-20)
bf_signals_output = fs.get_futures_butterfly_signals(ticker_list=contract_list,
tr_dte_list=tr_dte_list,
aggregation_method=aggregation_method,
contracts_back=contracts_back,
date_to=post_report_date,
contract_multiplier=butterflies['multiplier'][id],
use_last_as_current=True)
aligned_data = bf_signals_output['aligned_output']['aligned_data']
new_index = list(range(len(aligned_data.index)))
contract_change_indx = (aligned_data['c1']['ticker_year']-aligned_data['c1']['ticker_year'].shift(1)!=0).values
front_contract_year = aligned_data['c1']['ticker_year'] % 10
contract_change_indx[0] = False
report_datetime = cu.convert_doubledate_2datetime(report_date)
x_index = [x for x in new_index if aligned_data['settle_date'][x] == report_datetime][0]
x_tick_locations = [x for x in new_index if contract_change_indx[x]]
x_tick_locations.append(x_index)
x_tick_values = [cmi.letter_month_string[aligned_data['c1']['ticker_month'].values[x]-1]+
str(front_contract_year.values[x]) for x in new_index if contract_change_indx[x]]
x_tick_values.append('X')
plt.figure(figsize=(16, 7))
plt.plot(aligned_data['residuals'])
plt.xticks(x_tick_locations,x_tick_values)
plt.grid()
plt.title('Contracts: ' + str(contract_list) + ', weight2: ' + str(bf_signals_output['second_spread_weight_1'].round(2)))
plt.show()
return bf_signals_output
def generate_spread_carry_followup_report(**kwargs):
if 'as_of_date' in kwargs.keys():
as_of_date = kwargs['as_of_date']
else:
as_of_date = exp.doubledate_shift_bus_days()
kwargs['as_of_date'] = as_of_date
con = msu.get_my_sql_connection(**kwargs)
ta_output_dir = dn.get_dated_directory_extension(folder_date=as_of_date,
ext='ta')
if 'writer' in kwargs.keys():
writer = kwargs['writer']
else:
writer = pd.ExcelWriter(ta_output_dir + '/followup.xlsx',
engine='xlsxwriter')
strategy_frame = ts.get_open_strategies(**kwargs)
strategy_class_list = [
sc.convert_from_string_to_dictionary(
string_input=strategy_frame['description_string'][x])
['strategy_class'] for x in range(len(strategy_frame.index))
]
spread_carry_indx = [x == 'spread_carry' for x in strategy_class_list]
spread_carry_frame = strategy_frame[spread_carry_indx]
if spread_carry_frame.empty:
return writer
results = [
sf.get_results_4strategy(
alias=spread_carry_frame['alias'].iloc[x],
strategy_info_output=spread_carry_frame.iloc[x],
con=con) for x in range(len(spread_carry_frame.index))
]
results_frame_list = [
results[x]['results_frame'] for x in range(len(results))
if results[x]['success']
]
spread_carry_followup_frame = pd.concat(results_frame_list)
spread_carry_followup_frame.to_excel(writer, sheet_name='sc')
worksheet_sc = writer.sheets['sc']
worksheet_sc.freeze_panes(1, 0)
worksheet_sc.autofilter(0, 0, len(spread_carry_followup_frame.index),
len(spread_carry_followup_frame.columns))
if 'con' not in kwargs.keys():
con.close()
return writer
def get_clean_intraday_data(**kwargs):
ticker = kwargs['ticker']
date_to = kwargs['date_to']
#print(ticker)
if 'num_days_back' in kwargs.keys():
num_days_back = kwargs['num_days_back']
else:
num_days_back = 10
if 'freq_str' in kwargs.keys():
freq_str = kwargs['freq_str']
else:
freq_str = 'T'
ticker_list = ticker.split('-')
ticker_head = cmi.get_contract_specs(ticker_list[0])['ticker_head']
ticker_class = cmi.ticker_class[ticker_head]
date_list = [exp.doubledate_shift_bus_days(double_date=date_to,shift_in_days=x) for x in reversed(range(1,num_days_back))]
date_list.append(date_to)
intraday_data = get_aligned_futures_data_intraday(contract_list=[ticker],
date_list=date_list,freq_str=freq_str)
if intraday_data.empty:
return pd.DataFrame()
intraday_data['time_stamp'] = [x.to_datetime() for x in intraday_data.index]
intraday_data['hour_minute'] = [100*x.hour+x.minute for x in intraday_data['time_stamp']]
intraday_data['settle_date'] = [x.date() for x in intraday_data['time_stamp']]
end_datetime = cmi.last_trade_hour_minute[ticker_head]
start_datetime = cmi.first_trade_hour_minute[ticker_head]
end_hour_minute = 100*end_datetime.hour + end_datetime.minute
start_hour_minute = 100*start_datetime.hour + start_datetime.minute
if ticker_class in ['Ag']:
start_hour_minute1 = 45
end_hour_minute1 = 745
selected_data = intraday_data[((intraday_data['hour_minute']< end_hour_minute1)&(intraday_data['hour_minute'] >= start_hour_minute1))|
((intraday_data['hour_minute'] < end_hour_minute)&(intraday_data['hour_minute'] >= start_hour_minute))]
else:
selected_data = intraday_data[(intraday_data['hour_minute'] < end_hour_minute)&(intraday_data['hour_minute'] >= start_hour_minute)]
selected_data['mid_p'] = (selected_data['c1']['best_bid_p']+selected_data['c1']['best_ask_p'])/2
selected_data['total_traded_q'] = selected_data['c1']['total_traded_q']
return selected_data
def main():
app = algo.Algo()
con = msu.get_my_sql_connection()
date_now = cu.get_doubledate()
datetime_now = dt.datetime.now()
report_date = exp.doubledate_shift_bus_days()
ticker_head_list = ['HO', 'LC', 'FC', 'NQ'] # avoid HO on wednesdays for now!!
data_list = [gfp.get_futures_price_preloaded(ticker_head=x, settle_date=report_date) for x in ticker_head_list]
ticker_frame = pd.concat(data_list)
ticker_frame.sort(['ticker_head','volume'], ascending=[True, False], inplace=True)
ticker_frame.drop_duplicates(subset=['ticker_head'], take_last=False, inplace=True)
ticker_list = list(ticker_frame['ticker'])
theme_name_list = set([x + '_long' for x in ticker_list]).union(set([x + '_short' for x in ticker_list]))
alias_portfolio = aup.portfolio(ticker_list=theme_name_list)
latest_trade_entry_datetime = dt.datetime(datetime_now.year, datetime_now.month, datetime_now.day, 12, 0, 0)
latest_livestock_exit_datetime = dt.datetime(datetime_now.year, datetime_now.month, datetime_now.day, 13, 55, 0)
latest_macro_exit_datetime = dt.datetime(datetime_now.year, datetime_now.month, datetime_now.day, 15, 55, 0)
daily_sd_dictionary = {}
for i in range(len(ticker_list)):
daily_data_frame = gfp.get_futures_price_preloaded(ticker=ticker_list[i], settle_date_to=report_date)
daily_data_frame['close_diff'] = daily_data_frame['close_price'].diff()
daily_sd_dictionary[ticker_list[i]] = np.std(daily_data_frame['close_diff'].iloc[-40:])
db_alias = 'itf_' + datetime_now.strftime('%b_%y')
strategy_frame = ts.get_open_strategies(as_of_date=int(datetime_now.strftime('%Y%m%d')), con=con)
if db_alias not in strategy_frame.values:
db_strategy_output = ts.generate_db_strategy_from_alias(alias=db_alias, description_string='strategy_class=itf',con=con)
db_alias = db_strategy_output['alias']
app.ticker_list = ticker_list
app.db_alias = db_alias
app.tick_size_dictionary = {x:cmi.tick_size[cmi.get_contract_specs(x)['ticker_head']] for x in ticker_list}
app.daily_sd_dictionary = daily_sd_dictionary
app.contract_multiplier_dictionary = {x:cmi.contract_multiplier[cmi.get_contract_specs(x)['ticker_head']] for x in ticker_list}
app.ticker_head_dictionary = {x:cmi.get_contract_specs(x)['ticker_head'] for x in ticker_list}
app.latest_trade_entry_datetime = latest_trade_entry_datetime
app.latest_trade_exit_datetime_dictionary = {'HO': latest_macro_exit_datetime, 'LC': latest_livestock_exit_datetime, 'FC': latest_livestock_exit_datetime, 'NQ': latest_macro_exit_datetime}
app.alias_portfolio = alias_portfolio
app.output_dir = ts.create_strategy_output_dir(strategy_class='itf', report_date=report_date)
app.log = lg.get_logger(file_identifier='ib_itf',log_level='INFO')
app.con = con
app.connect(client_id=4)
app.run()
def get_symbol_frame(**kwargs):
frame_type = kwargs['frame_type']
settle_date = kwargs['settle_date']
if frame_type == 'nasdaq':
symbol_address = nasdaq_symbol_address
elif frame_type == 'other':
symbol_address = other_symbol_address
output_dir = dn.get_directory_name(ext='stock_data')
file_name = output_dir + '/' + frame_type + '_' + str(settle_date) + '.pkl'
if os.path.isfile(file_name):
return pd.read_pickle(file_name)
else:
datetime_now = dt.datetime.now()
if datetime_now.weekday() in [5, 6]:
last_settle_date = exp.doubledate_shift_bus_days()
elif 100 * datetime_now.hour + datetime_now.minute > 930:
last_settle_date = cu.get_doubledate()
else:
last_settle_date = exp.doubledate_shift_bus_days()
if settle_date == last_settle_date:
data_list = sd.download_txt_from_web(web_address=symbol_address)
column_names = data_list[0].decode('iso-8859-1').split("|")
parset_data_list = [
data_list[x].decode('iso-8859-1').split("|")
for x in range(1,
len(data_list) - 1)
]
symbol_frame = pd.DataFrame(parset_data_list, columns=column_names)
symbol_frame.to_pickle(file_name)
else:
symbol_frame = pd.DataFrame()
return symbol_frame
def generate_curve_pca_formatted_output(**kwargs):
if "report_date" in kwargs.keys():
report_date = kwargs["report_date"]
else:
report_date = exp.doubledate_shift_bus_days()
output_dir = ts.create_strategy_output_dir(strategy_class="curve_pca", report_date=report_date)
ticker_head_list = ["CL", "B"]
selected_column_list = [
"ticker1",
"ticker2",
"monthSpread",
"tr_dte_front",
"residuals",
"price",
"yield",
"z",
"z2",
"factor_load1",
"factor_load2",
]
writer = pd.ExcelWriter(output_dir + "/" + futil.xls_file_names["curve_pca"] + ".xlsx", engine="xlsxwriter")
for ticker_head in ticker_head_list:
curve_pca_output = cpc.get_curve_pca_report(ticker_head=ticker_head, date_to=report_date)
if curve_pca_output["success"]:
all_spreads = curve_pca_output["pca_results"]
filter_out = ff.get_curve_pca_filters(data_frame_input=all_spreads, filter_list=["long1", "short1"])
good_spreads = filter_out["selected_frame"]
all_spreads = all_spreads[selected_column_list]
good_spreads = good_spreads[selected_column_list]
all_spreads.to_excel(writer, sheet_name=ticker_head + "-all")
good_spreads.to_excel(writer, sheet_name=ticker_head + "-good")
worksheet_good = writer.sheets[ticker_head + "-good"]
worksheet_all = writer.sheets[ticker_head + "-all"]
worksheet_good.freeze_panes(1, 0)
worksheet_all.freeze_panes(1, 0)
worksheet_good.autofilter(0, 0, len(good_spreads.index), len(selected_column_list))
worksheet_all.autofilter(0, 0, len(all_spreads.index), len(selected_column_list))
def generate_vcs_formatted_output(**kwargs):
if 'report_date' in kwargs.keys():
report_date = kwargs['report_date']
else:
report_date = exp.doubledate_shift_bus_days()
output_dir = ts.create_strategy_output_dir(strategy_class='vcs',
report_date=report_date)
vcs_output = vcs.generate_vcs_sheet_4date(date_to=report_date)
vcs_pairs = vcs_output['vcs_pairs']
filter_out = of.get_vcs_filters(data_frame_input=vcs_pairs,
filter_list=['long2', 'short2'])
good_vcs_pairs = filter_out['selected_frame']
vcs_pairs_w_selected_columns = vcs_pairs[[
'ticker1', 'ticker2', 'tickerHead', 'tickerClass', 'trDte1', 'trDte2',
'Q', 'Q1', 'fwdVolQ', 'downside', 'upside', 'atmVolRatio', 'fwdVol',
'realVolRatio', 'atmRealVolRatio', 'theta'
]]
good_vcs_pairs_w_selected_columns = good_vcs_pairs[[
'ticker1', 'ticker2', 'tickerHead', 'tickerClass', 'trDte1', 'trDte2',
'Q', 'Q1', 'fwdVolQ', 'downside', 'upside', 'atmVolRatio', 'fwdVol',
'realVolRatio', 'atmRealVolRatio', 'theta'
]]
writer = pd.ExcelWriter(output_dir + '/' + futil.xls_file_names['vcs'] +
'.xlsx',
engine='xlsxwriter')
vcs_pairs_w_selected_columns.to_excel(writer, sheet_name='all')
good_vcs_pairs_w_selected_columns.to_excel(writer, sheet_name='good')
worksheet_good = writer.sheets['good']
worksheet_all = writer.sheets['all']
worksheet_good.freeze_panes(1, 0)
worksheet_all.freeze_panes(1, 0)
worksheet_good.autofilter(0, 0,
len(good_vcs_pairs_w_selected_columns.index),
len(good_vcs_pairs_w_selected_columns.columns))
worksheet_all.autofilter(0, 0, len(vcs_pairs_w_selected_columns.index),
len(vcs_pairs_w_selected_columns.columns))
writer.save()
def get_intraday_vcs(**kwargs):
if 'report_date' in kwargs.keys():
report_date = kwargs['report_date']
else:
report_date = exp.doubledate_shift_bus_days()
atm_vol_ratio = kwargs['atm_vol_ratio']
vcs_output = vcs.generate_vcs_sheet_4date(date_to=report_date)
vcs_pairs = vcs_output['vcs_pairs']
if 'id' in kwargs.keys():
id = kwargs['id']
ticker1 = vcs_pairs['ticker1'].iloc[id]
ticker2 = vcs_pairs['ticker2'].iloc[id]
else:
ticker1 = kwargs['ticker1']
ticker2 = kwargs['ticker2']
ticker_head = cmi.get_contract_specs(ticker1)['ticker_head']
ticker_class = cmi.ticker_class[ticker_head]
vcs_output = ops.get_vcs_signals(ticker_list=[ticker1, ticker2],
settle_date=report_date,
atm_vol_ratio=atm_vol_ratio)
q = vcs_output['q']
q1 = vcs_output['q1']
filter_out = of.get_vcs_filters(data_frame_input=pd.DataFrame.from_dict({
'tickerHead': [ticker_head],
'tickerClass': [ticker_class],
'Q': [q],
'Q1': [q1]
}),
filter_list=['long2', 'short2'])
if filter_out['selected_frame'].empty:
validQ = False
else:
validQ = True
return {
'ticker1': ticker1,
'ticker2': ticker2,
'Q': q,
'Q1': q1,
'validQ': validQ
}
def generate_curve_pca_formatted_output(**kwargs):
if 'report_date' in kwargs.keys():
report_date = kwargs['report_date']
else:
report_date = exp.doubledate_shift_bus_days()
output_dir = ts.create_strategy_output_dir(strategy_class='curve_pca',
report_date=report_date)
ticker_head_list = ['CL', 'B']
selected_column_list = [
'ticker1', 'ticker2', 'monthSpread', 'tr_dte_front', 'residuals',
'price', 'yield', 'z', 'z2', 'factor_load1', 'factor_load2'
]
writer = pd.ExcelWriter(output_dir + '/' +
futil.xls_file_names['curve_pca'] + '.xlsx',
engine='xlsxwriter')
for ticker_head in ticker_head_list:
curve_pca_output = cpc.get_curve_pca_report(ticker_head=ticker_head,
date_to=report_date)
if curve_pca_output['success']:
all_spreads = curve_pca_output['pca_results']
filter_out = ff.get_curve_pca_filters(
data_frame_input=all_spreads, filter_list=['long1', 'short1'])
good_spreads = filter_out['selected_frame']
all_spreads = all_spreads[selected_column_list]
good_spreads = good_spreads[selected_column_list]
all_spreads.to_excel(writer, sheet_name=ticker_head + '-all')
good_spreads.to_excel(writer, sheet_name=ticker_head + '-good')
worksheet_good = writer.sheets[ticker_head + '-good']
worksheet_all = writer.sheets[ticker_head + '-all']
worksheet_good.freeze_panes(1, 0)
worksheet_all.freeze_panes(1, 0)
worksheet_good.autofilter(0, 0, len(good_spreads.index),
len(selected_column_list))
worksheet_all.autofilter(0, 0, len(all_spreads.index),
len(selected_column_list))
writer.save()
def send_followup_report(**kwargs):
if 'report_date' in kwargs.keys():
report_date = kwargs['report_date']
else:
report_date = exp.doubledate_shift_bus_days()
ta_output_dir = dn.get_dated_directory_extension(folder_date=report_date,ext='ta')
se.send_email_with_attachment(send_from='*****@*****.**',
send_to='*****@*****.**',
sender_account_alias='wh_trading',
subject='followup_' + str(report_date),
attachment_list = [ta_output_dir + '/' + 'followup.xlsx'],
attachment_name_list=['Followup.xlsx'])
def prepare_strategy_daily(**kwargs):
strategy_class = kwargs["strategy_class"]
if "report_date" in kwargs.keys():
report_date = kwargs["report_date"]
else:
report_date = exp.doubledate_shift_bus_days()
output_dir = ts.create_strategy_output_dir(strategy_class=strategy_class, report_date=report_date)
sutil.copyfile(
output_dir + "/" + futil.xls_file_names[strategy_class] + ".xlsx",
daily_dir + "/" + futil.xls_file_names[strategy_class] + "_" + str(report_date) + ".xlsx",
)
def get_strategy_class_historical_pnls(**kwargs):
con = msu.get_my_sql_connection(**kwargs)
if 'as_of_date' in kwargs.keys():
as_of_date = kwargs['as_of_date']
else:
as_of_date = exp.doubledate_shift_bus_days()
strategy_frame = ts.select_strategies(con=con, open_date_to=as_of_date)
strategy_frame['strategy_class'] = [
sc.convert_from_string_to_dictionary(
string_input=strategy_frame['description_string'][x])
['strategy_class'] for x in range(len(strategy_frame.index))
]
unique_strategy_class_list = strategy_frame['strategy_class'].unique()
time_series_list = [None] * len(unique_strategy_class_list)
for i in range(len(unique_strategy_class_list)):
strategy_frame_selected = strategy_frame[
strategy_frame['strategy_class'] == unique_strategy_class_list[i]]
pnl_out = [
tpnl.get_strategy_pnl(
alias=x, as_of_date=as_of_date,
con=con)['pnl_frame'][['settle_date', 'total_pnl']]
for x in strategy_frame_selected['alias']
]
[x.set_index('settle_date', drop=True, inplace=True) for x in pnl_out]
time_series_list[i] = pd.concat(pnl_out, axis=1).fillna(0).sum(axis=1)
if 'con' not in kwargs.keys():
con.close()
merged_pnl = pd.concat(time_series_list,
axis=1,
keys=unique_strategy_class_list).fillna(0)
merged_pnl['total'] = merged_pnl.sum(axis=1)
output_dir = dn.get_directory_name(ext='daily')
writer = pd.ExcelWriter(output_dir + '/historical_performance_' +
str(as_of_date) + '.xlsx',
engine='xlsxwriter')
merged_pnl.to_excel(writer, sheet_name='timeSeries')
writer.save()
return merged_pnl
def calc_intrday_pnl_from_prices(**kwargs):
if 'as_of_date' in kwargs.keys():
as_of_date = kwargs['as_of_date']
else:
as_of_date = exp.doubledate_shift_bus_days()
net_position_frame = tas.get_net_position_4strategy_alias(alias=kwargs['alias'], as_of_date=as_of_date)
net_position_frame['ticker_head'] = [cmi.get_contract_specs(x)['ticker_head'] for x in net_position_frame['ticker']]
net_position_frame['contract_multiplier'] = [cmi.contract_multiplier[x] for x in net_position_frame['ticker_head']]
con = msu.get_my_sql_connection(**kwargs)
option_frame = net_position_frame[net_position_frame['instrument'] == 'O']
#option_frame = option_frame[(option_frame['strike_price'] == 54)|(option_frame['strike_price'] == 60)]
#option_frame = option_frame[option_frame['strike_price'] == 112]
#option_frame['qty'].loc[option_frame['ticker']=='LNV2016'] = -20
option_frame['close_price'] = [gop.get_options_price_from_db(ticker=option_frame['ticker'].iloc[x],
strike=option_frame['strike_price'].iloc[x],
option_type=option_frame['option_type'].iloc[x],
con=con,settle_date=as_of_date)['close_price'][0] for x in range(len(option_frame.index))]
structure_quantity = abs(option_frame['qty']).unique()[0]
structure_multiplier = option_frame['contract_multiplier'].unique()[0]
structure_price = sum(option_frame['close_price']*option_frame['qty'])/structure_quantity
if structure_price<0:
structure_quantity = -structure_quantity
structure_price = -structure_price
structure_pnl = structure_quantity*structure_multiplier*(kwargs['structure_price']-structure_price)
futures_frame = net_position_frame[net_position_frame['instrument'] == 'F']
futures_frame['close_price'] = [gfp.get_futures_price_preloaded(ticker=x, settle_date=as_of_date)['close_price'].iloc[0] for x in futures_frame['ticker']]
futures_frame['intraday_price'] = [kwargs[x] for x in futures_frame['ticker']]
futures_frame['intraday_pnl'] = (futures_frame['intraday_price']-futures_frame['close_price'])*futures_frame['qty']*futures_frame['contract_multiplier']
if 'con' not in kwargs.keys():
con.close()
return {'structure_pnl': structure_pnl, 'futures_pnl': futures_frame['intraday_pnl'].sum(),'structure_settle': structure_price}
def prepare_strategy_daily(**kwargs):
strategy_class = kwargs['strategy_class']
if 'report_date' in kwargs.keys():
report_date = kwargs['report_date']
else:
report_date = exp.doubledate_shift_bus_days()
output_dir = ts.create_strategy_output_dir(strategy_class=strategy_class,
report_date=report_date)
sutil.copyfile(
output_dir + '/' + futil.xls_file_names[strategy_class] + '.xlsx',
daily_dir + '/' + futil.xls_file_names[strategy_class] + '_' +
str(report_date) + '.xlsx')
def calc_intraday_structure_pnl_from_prices(**kwargs):
if 'as_of_date' in kwargs.keys():
as_of_date = kwargs['as_of_date']
else:
as_of_date = exp.doubledate_shift_bus_days()
con = msu.get_my_sql_connection(**kwargs)
structure_type = kwargs['structure_type']
structure_price = kwargs['structure_price']
ticker_list = kwargs['ticker_list']
strike_list = kwargs['strike_list']
underlying_price_list = kwargs['underlying_price_list']
qty = kwargs['qty']
if structure_type == 'straddle_spread':
option_frame = pd.DataFrame.from_items([('ticker', [ticker_list[0], ticker_list[0], ticker_list[1], ticker_list[1]]),
('option_type', ['C', 'P', 'C', 'P']),
('strike_price', [strike_list[0], strike_list[0], strike_list[1],strike_list[1]]),
('qty', [-1, -1, 1, 1])])
option_price_output = [gop.get_options_price_from_db(ticker=option_frame['ticker'].iloc[x],
strike=option_frame['strike_price'].iloc[x],
option_type=option_frame['option_type'].iloc[x],
con=con,settle_date=as_of_date,column_names=['close_price','delta']) for x in range(len(option_frame.index))]
option_frame['delta'] = [option_price_output[x]['delta'][0] for x in range(len(option_frame.index))]
option_frame['close_price'] = [option_price_output[x]['close_price'][0] for x in range(len(option_frame.index))]
option_frame['PQ'] = option_frame['close_price']*option_frame['qty']
option_frame['signed_delta'] = option_frame['delta']*option_frame['qty']
delta_list = [option_frame[option_frame['ticker'] == x]['signed_delta'].sum() for x in ticker_list]
ticker_head = cmi.get_contract_specs(ticker_list[0])['ticker_head']
contract_multiplier = cmi.contract_multiplier[ticker_head]
structure_price_yesterday = option_frame['PQ'].sum()
structure_pnl = qty*(structure_price-structure_price_yesterday)*contract_multiplier
underlying_ticker_list = [oputil.get_option_underlying(ticker=x) for x in ticker_list]
underlying_price_list_yesterday = [gfp.get_futures_price_preloaded(ticker=x, settle_date=as_of_date)['close_price'].iloc[0] for x in underlying_ticker_list]
delta_pnl = contract_multiplier*sum([-delta_list[x]*qty*(underlying_price_list[x]-underlying_price_list_yesterday[x]) for x in range(len(delta_list))])
return {'total_pnl': structure_pnl+delta_pnl, 'structure_pnl': structure_pnl,
'delta_pnl': delta_pnl, 'structure_price_yesterday': structure_price_yesterday }
def get_stock_price_preloaded(**kwargs):
ticker = kwargs['ticker']
if 'data_source' in kwargs.keys():
data_source = kwargs['data_source']
else:
data_source = 'iex'
if 'stock_data_dictionary' in kwargs.keys():
data_out = kwargs['stock_data_dictionary'][ticker]
else:
if data_source == 'iex':
file_dir = dn.get_directory_name(ext='iex_stock_data')
else:
file_dir = dn.get_directory_name(ext='stock_data')
if not os.path.isfile(file_dir + '/' + ticker + '.pkl'):
ssd.save_stock_data(symbol_list=[ticker], data_source=data_source)
data_out = pd.read_pickle(file_dir + '/' + ticker + '.pkl')
report_date = exp.doubledate_shift_bus_days()
if cu.convert_doubledate_2datetime(report_date) > data_out[
'settle_datetime'].iloc[-1].to_pydatetime():
ssd.save_stock_data(symbol_list=[ticker], data_source=data_source)
data_out = pd.read_pickle(file_dir + '/' + ticker + '.pkl')
if 'settle_date' in kwargs.keys():
settle_date = kwargs['settle_date']
if isinstance(settle_date, int):
data_out = data_out[data_out['settle_datetime'] ==
cu.convert_doubledate_2datetime(settle_date)]
elif isinstance(settle_date, dt.datetime):
data_out = data_out[data_out['settle_datetime'] == settle_date]
if 'settle_date_from' in kwargs.keys():
data_out = data_out[
data_out['settle_datetime'] >= cu.convert_doubledate_2datetime(
kwargs['settle_date_from'])]
if 'settle_date_to' in kwargs.keys():
data_out = data_out[
data_out['settle_datetime'] <= cu.convert_doubledate_2datetime(
kwargs['settle_date_to'])]
return data_out
def get_ib_exchange_name(ticker):
last_settle_date = exp.doubledate_shift_bus_days()
symbol_frame = ssd.get_symbol_frame(frame_type='other', settle_date=last_settle_date)
selected_frame = symbol_frame[symbol_frame['ACT Symbol'] == ticker]
if len(selected_frame.index) == 0:
exchange_name = 'SMART'
else:
exchange_code = selected_frame['Exchange'].iloc[0]
exchange_name = ''
if exchange_code=='A':
exchange_name = 'AMEX'
elif exchange_code=='N':
exchange_name = 'NYSE'
return exchange_name
def generate_spread_carry_formatted_output(**kwargs):
if 'report_date' in kwargs.keys():
report_date = kwargs['report_date']
else:
report_date = exp.doubledate_shift_bus_days()
output_dir = ts.create_strategy_output_dir(strategy_class='spread_carry',
report_date=report_date)
spread_carry_output = sc.generate_spread_carry_sheet_4date(
report_date=report_date)
spread_report = spread_carry_output['spread_report']
writer = pd.ExcelWriter(output_dir + '/' +
futil.get_xls_file_name('spread_carry') + '.xlsx',
engine='xlsxwriter')
spread_report.to_excel(writer, sheet_name='summary')
def generate_spread_carry_followup_report(**kwargs):
if 'as_of_date' in kwargs.keys():
as_of_date = kwargs['as_of_date']
else:
as_of_date = exp.doubledate_shift_bus_days()
kwargs['as_of_date'] = as_of_date
con = msu.get_my_sql_connection(**kwargs)
ta_output_dir = dn.get_dated_directory_extension(folder_date=as_of_date, ext='ta')
if 'writer' in kwargs.keys():
writer = kwargs['writer']
else:
writer = pd.ExcelWriter(ta_output_dir + '/followup.xlsx', engine='xlsxwriter')
strategy_frame = ts.get_open_strategies(**kwargs)
strategy_class_list = [sc.convert_from_string_to_dictionary(string_input=strategy_frame['description_string'][x])['strategy_class']
for x in range(len(strategy_frame.index))]
spread_carry_indx = [x == 'spread_carry' for x in strategy_class_list]
spread_carry_frame = strategy_frame[spread_carry_indx]
results = [sf.get_results_4strategy(alias=spread_carry_frame['alias'].iloc[x],
strategy_info_output=spread_carry_frame.iloc[x],
con=con)
for x in range(len(spread_carry_frame.index))]
results_frame_list = [results[x]['results_frame'] for x in range(len(results)) if results[x]['success']]
spread_carry_followup_frame = pd.concat(results_frame_list)
spread_carry_followup_frame.to_excel(writer, sheet_name='sc')
worksheet_sc = writer.sheets['sc']
worksheet_sc.freeze_panes(1, 0)
worksheet_sc.autofilter(0, 0, len(spread_carry_followup_frame.index),
len(spread_carry_followup_frame.columns))
if 'con' not in kwargs.keys():
con.close()
return writer
def get_dated_directory_extension(**kwargs):
if 'folder_date' in kwargs.keys():
folder_date = kwargs['folder_date']
else:
folder_date = exp.doubledate_shift_bus_days()
if 'ext' not in kwargs.keys():
print('Need to provide a valid ext !')
return
directory_name = get_directory_name(**kwargs)
dated_directory_name = directory_name + '/' + cu.get_directory_extension(folder_date)
if not os.path.exists(dated_directory_name):
os.makedirs(dated_directory_name)
return dated_directory_name
def generate_ifs_formatted_output(**kwargs):
if "report_date" in kwargs.keys():
report_date = kwargs["report_date"]
else:
report_date = exp.doubledate_shift_bus_days()
output_dir = ts.create_strategy_output_dir(strategy_class="ifs", report_date=report_date)
ifs_output = ifs.generate_ifs_sheet_4date(date_to=report_date)
intraday_spreads = ifs_output["intraday_spreads"]
intraday_spreads.rename(
columns={"ticker_head1": "tickerHead1", "ticker_head2": "tickerHead2", "ticker_head3": "tickerHead3"},
inplace=True,
)
writer = pd.ExcelWriter(output_dir + "/" + futil.xls_file_names["ifs"] + ".xlsx", engine="xlsxwriter")
intraday_spreads.to_excel(writer, sheet_name="all")
def get_dated_directory_extension(**kwargs):
if 'folder_date' in kwargs.keys():
folder_date = kwargs['folder_date']
else:
folder_date = exp.doubledate_shift_bus_days()
if 'ext' not in kwargs.keys():
print('Need to provide a valid ext !')
return
directory_name = get_directory_name(**kwargs)
dated_directory_name = directory_name + '/' + cu.get_directory_extension(
folder_date)
if not os.path.exists(dated_directory_name):
os.makedirs(dated_directory_name)
return dated_directory_name
def generate_portfolio_pnl_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
ta_output_dir = dn.get_dated_directory_extension(folder_date=as_of_date, ext='ta')
daily_pnl_frame = tpm.get_daily_pnl_snapshot(**kwargs)
writer = pd.ExcelWriter(ta_output_dir + '/pnl.xlsx', engine='xlsxwriter')
daily_pnl_frame.to_excel(writer, sheet_name='strategies')
worksheet_strategies = writer.sheets['strategies']
worksheet_strategies.set_column('B:B', 30)
worksheet_strategies.freeze_panes(1, 0)
worksheet_strategies.autofilter(0, 0, len(daily_pnl_frame.index),
len(daily_pnl_frame.columns))
def close_strategy(**kwargs):
alias = kwargs['alias']
broker = kwargs['broker']
if 'close_date' in kwargs.keys():
close_date = kwargs['close_date']
else:
close_date = exp.doubledate_shift_bus_days()
now_time = dt.datetime.now()
now_date = now_time.date()
con = msu.get_my_sql_connection(**kwargs)
net_position = ts.get_net_position_4strategy_alias(alias=alias,
con=con,
as_of_date=close_date)
if net_position.empty:
pnl_output = get_strategy_pnl(alias=alias,
broker=broker,
con=con,
as_of_date=close_date)
total_pnl = pnl_output['total_pnl']
cur = con.cursor()
query_str = 'UPDATE strategy SET pnl=' + str(total_pnl) + \
', close_date=' + str(close_date) + \
', last_updated_date=' + now_date.strftime('%Y%m%d') + \
' WHERE alias=\'' + alias + '\''
cur.execute(query_str)
con.commit()
else:
print(alias + ' is not empty ! ')
return
if 'con' not in kwargs.keys():
con.close()
def load_csv_file_4ticker(**kwargs):
ticker = kwargs['ticker']
if 'folder_date' in kwargs.keys():
folder_date = kwargs['folder_date']
else:
folder_date = exp.doubledate_shift_bus_days()
data_dir = dn.get_dated_directory_extension(ext='intraday_ttapi_data', folder_date=folder_date)
file_name = get_ttapi_filename(ticker=ticker)
if os.path.isfile(data_dir + '/' + file_name):
data_frame_out = pd.read_csv(data_dir + '/' + file_name,names=['time','field','value'],dtype={2: 'str'})
data_frame_out['time'] = pd.to_datetime(data_frame_out['time'])
else:
data_frame_out = pd.DataFrame(columns=['time','field','value'])
return data_frame_out
def generate_ocs_formatted_output(**kwargs):
if 'report_date' in kwargs.keys():
report_date = kwargs['report_date']
else:
report_date = exp.doubledate_shift_bus_days()
output_dir = ts.create_strategy_output_dir(strategy_class='ocs',
report_date=report_date)
ocs_output = ocs.generate_overnight_spreads_sheet_4date(
date_to=report_date)
overnight_calendars = ocs_output['overnight_calendars']
overnight_calendars = overnight_calendars[
overnight_calendars['butterflyQ'].notnull()]
writer = pd.ExcelWriter(output_dir + '/' + futil.xls_file_names['ocs'] +
'.xlsx',
engine='xlsxwriter')
overnight_calendars.to_excel(writer, sheet_name='all')
def generate_vcs_formatted_output(**kwargs):
if 'report_date' in kwargs.keys():
report_date = kwargs['report_date']
else:
report_date = exp.doubledate_shift_bus_days()
output_dir = ts.create_strategy_output_dir(strategy_class='vcs', report_date=report_date)
vcs_output = vcs.generate_vcs_sheet_4date(date_to=report_date)
vcs_pairs = vcs_output['vcs_pairs']
filter_out = of.get_vcs_filters(data_frame_input=vcs_pairs, filter_list=['long2', 'short2'])
good_vcs_pairs = filter_out['selected_frame']
vcs_pairs_w_selected_columns = vcs_pairs[['ticker1', 'ticker2', 'tickerHead', 'tickerClass', 'trDte1', 'trDte2', 'Q',
'Q1', 'fwdVolQ', 'downside', 'upside', 'atmVolRatio', 'fwdVol','realVolRatio',
'atmRealVolRatio','theta']]
good_vcs_pairs_w_selected_columns = good_vcs_pairs[['ticker1', 'ticker2', 'tickerHead', 'tickerClass', 'trDte1', 'trDte2', 'Q',
'Q1', 'fwdVolQ', 'downside', 'upside', 'atmVolRatio', 'fwdVol','realVolRatio',
'atmRealVolRatio','theta']]
writer = pd.ExcelWriter(output_dir + '/' + futil.xls_file_names['vcs'] + '.xlsx', engine='xlsxwriter')
vcs_pairs_w_selected_columns.to_excel(writer, sheet_name='all')
good_vcs_pairs_w_selected_columns.to_excel(writer, sheet_name='good')
worksheet_good = writer.sheets['good']
worksheet_all = writer.sheets['all']
worksheet_good.freeze_panes(1, 0)
worksheet_all.freeze_panes(1, 0)
worksheet_good.autofilter(0, 0, len(good_vcs_pairs_w_selected_columns.index),
len(good_vcs_pairs_w_selected_columns.columns))
worksheet_all.autofilter(0, 0, len(vcs_pairs_w_selected_columns.index),
len(vcs_pairs_w_selected_columns.columns))
def generate_ibo_formatted_output(**kwargs):
if 'report_date' in kwargs.keys():
report_date = kwargs['report_date']
else:
report_date = exp.doubledate_shift_bus_days()
output_dir = ts.create_strategy_output_dir(strategy_class='ibo', report_date=report_date)
out_dictionary = ibo.generate_ibo_sheet_4date(date_to=report_date)
cov_matrix = out_dictionary['cov_output']['cov_matrix']
cov_matrix.reset_index(drop=False,inplace=True)
writer = pd.ExcelWriter(output_dir + '/' + 'cov_matrix.xlsx', engine='xlsxwriter')
cov_matrix.to_excel(writer, sheet_name='cov_matrix')
writer.save()
cov_data_integrity = round(out_dictionary['cov_output']['cov_data_integrity'], 2)
with open(output_dir + '/' + 'covDataIntegrity.txt','w') as text_file:
text_file.write(str(cov_data_integrity))
def get_intraday_vcs(**kwargs):
if 'report_date' in kwargs.keys():
report_date = kwargs['report_date']
else:
report_date = exp.doubledate_shift_bus_days()
id = kwargs['id']
atm_vol_ratio = kwargs['atm_vol_ratio']
vcs_output = vcs.generate_vcs_sheet_4date(date_to=report_date)
vcs_pairs = vcs_output['vcs_pairs']
ticker1 = vcs_pairs['ticker1'].iloc[id]
ticker2 = vcs_pairs['ticker2'].iloc[id]
ticker_head = cmi.get_contract_specs(ticker1)['ticker_head']
ticker_class = cmi.ticker_class[ticker_head]
vcs_output = ops.get_vcs_signals(ticker_list=[ticker1, ticker2],settle_date=report_date,atm_vol_ratio=atm_vol_ratio)
q = vcs_output['q']
q1 = vcs_output['q1']
filter_out = of.get_vcs_filters(data_frame_input=pd.DataFrame.from_items([('tickerHead', [ticker_head]),
('tickerClass', [ticker_class]),
('Q', [q]), ('Q1', [q1])]), filter_list=['long2', 'short2'])
if filter_out['selected_frame'].empty:
validQ = False
else:
validQ = True
print(ticker1)
print(ticker2)
print('Q: ' + str(q))
print('Q1: ' + str(q1))
print('Valid?: ' + str(validQ))
def send_hrsn_report(**kwargs):
daily_dir = dn.get_directory_name(ext="daily")
if "report_date" in kwargs.keys():
report_date = kwargs["report_date"]
else:
report_date = exp.doubledate_shift_bus_days()
ibo_dir = ts.create_strategy_output_dir(strategy_class="ibo", report_date=report_date)
cov_data_integrity = ""
try:
with open(ibo_dir + "/" + "covDataIntegrity.txt", "r") as text_file:
cov_data_integrity = text_file.read()
except Exception:
pass
try:
expiration_report = ef.get_expiration_report(report_date=report_date)
expiration_report = expiration_report[expiration_report["tr_dte"] < 10]
if expiration_report.empty:
expiration_text = "No near expirations."
else:
expiration_text = "Check for approaching expirations!"
except Exception:
expiration_text = "Check expiration report for errors!"
se.send_email_with_attachment(
subject="hrsn_" + str(report_date),
email_text="cov_data_integrity: " + cov_data_integrity + "\r\n" + expiration_text,
attachment_list=[
daily_dir + "/" + "pnl_" + str(report_date) + ".xlsx",
daily_dir + "/" + "followup_" + str(report_date) + ".xlsx",
],
)
def get_pnl_4_date_range(**kwargs):
ticker_list = kwargs['ticker_list']
date_to = kwargs['date_to']
num_bus_days_back = kwargs['num_bus_days_back']
directory_name = dn.get_directory_name(ext='backtest_results')
file_name = '_'.join(ticker_list)
if os.path.isfile(directory_name + '/ifs_pnls/' + file_name + '.pkl'):
pnl_frame = pd.read_pickle(directory_name + '/ifs_pnls/' + file_name + '.pkl')
else:
pnl_frame = pd.DataFrame(columns=['pnl_date', 'long_pnl', 'short_pnl','total_pnl'])
date_from = exp.doubledate_shift_bus_days(double_date=date_to,shift_in_days=num_bus_days_back)
date_list = exp.get_bus_day_list(date_from=date_from,date_to=date_to)
dates2calculate = list(set(date_list)-set(pnl_frame['pnl_date']))
if not dates2calculate:
return pnl_frame[(pnl_frame['pnl_date']>=date_list[0])&(pnl_frame['pnl_date']<=date_list[-1])]
pnl_list = []
for i in dates2calculate:
#print(i)
pnl_list.append(calc_pnl4date(ticker_list=ticker_list,pnl_date=i))
pnl_frame = pd.concat([pnl_frame,pd.DataFrame(pnl_list)])
pnl_frame = pnl_frame[['pnl_date', 'long_pnl', 'short_pnl','total_pnl']]
pnl_frame.sort('pnl_date',ascending=True,inplace=True)
pnl_frame.to_pickle(directory_name + '/ifs_pnls/' + file_name + '.pkl')
return pnl_frame[(pnl_frame['pnl_date']>=date_list[0])&(pnl_frame['pnl_date']<=date_list[-1])]
def get_net_position_4strategy_alias(**kwargs):
alias = kwargs['alias']
if 'as_of_date' in kwargs.keys():
as_of_date = kwargs['as_of_date']
else:
as_of_date = exp.doubledate_shift_bus_days()
con = msu.get_my_sql_connection(**kwargs)
trades_frame = get_trades_4strategy_alias(alias=alias,con=con)
as_of_datetime = cu.convert_doubledate_2datetime(as_of_date)
trades_frame = trades_frame[trades_frame['trade_date'] <= as_of_datetime]
trades_frame['full_ticker'] = [trades_frame['ticker'].iloc[x] if trades_frame['instrument'].iloc[x] == 'F' else
trades_frame['ticker'].iloc[x] + '_' + trades_frame['option_type'].iloc[x] + str(trades_frame['strike_price'].iloc[x])
for x in range(len(trades_frame.index))]
grouped = trades_frame.groupby('full_ticker')
net_position = pd.DataFrame()
net_position['ticker'] = (grouped['ticker'].first()).values
net_position['option_type'] = (grouped['option_type'].first()).values
net_position['strike_price'] = (grouped['strike_price'].first()).values
net_position['instrument'] = (grouped['instrument'].first()).values
net_position['qty'] = (grouped['trade_quantity'].sum()).values
net_position['qty'] = net_position['qty'].round(2)
if 'con' not in kwargs.keys():
con.close()
return net_position[net_position['qty'] != 0]
def move_from_dated_folder_2daily_folder(**kwargs):
ext = kwargs["ext"]
file_name_raw = kwargs["file_name"]
file_name_split = file_name_raw.split(".")
if len(file_name_split) == 1:
file_name = file_name_raw
file_ext = ".xlsx"
else:
file_name = file_name_split[0]
file_ext = file_name_split[1]
if "folder_date" in kwargs.keys():
folder_date = kwargs["folder_date"]
else:
folder_date = exp.doubledate_shift_bus_days()
dated_folder = dn.get_dated_directory_extension(folder_date=folder_date, ext=ext)
sutil.copyfile(
dated_folder + "/" + file_name + file_ext, daily_dir + "/" + file_name + "_" + str(folder_date) + file_ext
)
def get_strategy_pnl_4day(**kwargs):
alias = kwargs['alias']
pnl_date = kwargs['pnl_date']
#print(pnl_date)
pnl_datetime = cu.convert_doubledate_2datetime(pnl_date)
con = msu.get_my_sql_connection(**kwargs)
if 'trades_frame' in kwargs.keys():
trades_frame = kwargs['trades_frame']
ticker_head_list = [cmi.get_contract_specs(x)['ticker_head'] for x in trades_frame['ticker']]
else:
trades_frame = ts.get_trades_4strategy_alias(alias=alias,con=con)
ticker_head_list = [cmi.get_contract_specs(x)['ticker_head'] for x in trades_frame['ticker']]
trades_frame['contract_multiplier'] = [cmi.contract_multiplier[x] for x in ticker_head_list]
trades_frame['t_cost'] = [cmi.t_cost[x] for x in ticker_head_list]
trades_frame['ticker_head'] = ticker_head_list
if 'futures_data_dictionary' in kwargs.keys():
futures_data_dictionary = kwargs['futures_data_dictionary']
else:
unique_ticker_head_list = list(set(ticker_head_list))
futures_data_dictionary = {x: gfp.get_futures_price_preloaded(ticker_head=x) for x in unique_ticker_head_list}
pnl_date_1 = exp.doubledate_shift_bus_days(double_date=pnl_date)
underlying_frame = trades_frame[trades_frame['instrument'] == 'F']
option_frame = trades_frame[trades_frame['instrument'] == 'O']
futures_price_out_1 = [gfp.get_futures_price_preloaded(ticker=x,
futures_data_dictionary=futures_data_dictionary,
settle_date=pnl_date_1) for x in underlying_frame['ticker']]
futures_price_out = [gfp.get_futures_price_preloaded(ticker=x,
futures_data_dictionary=futures_data_dictionary,
settle_date=pnl_date) for x in underlying_frame['ticker']]
underlying_frame['price_1'] = [np.NaN if x.empty else x['close_price'].values[0] for x in futures_price_out_1]
underlying_frame['price'] = [np.NaN if x.empty else x['close_price'].values[0] for x in futures_price_out]
#underlying_frame['price_1'] = [gfp.get_futures_price_preloaded(ticker=x,
# futures_data_dictionary=futures_data_dictionary,
# settle_date=pnl_date_1)['close_price'].values[0] for x in underlying_frame['ticker']]
#underlying_frame['price'] = [gfp.get_futures_price_preloaded(ticker=x,
# futures_data_dictionary=futures_data_dictionary,
# settle_date=pnl_date)['close_price'].values[0] for x in underlying_frame['ticker']]
option_frame['price_1'] = [gop.get_options_price_from_db(ticker=option_frame['ticker'].iloc[x],
strike=option_frame['strike_price'].iloc[x],
option_type=option_frame['option_type'].iloc[x],
con=con,settle_date=pnl_date_1,
return_nan_if_emptyQ = True)['close_price'].values[0] for x in range(len(option_frame.index))]
option_frame['price'] = [gop.get_options_price_from_db(ticker=option_frame['ticker'].iloc[x],
strike=option_frame['strike_price'].iloc[x],
option_type=option_frame['option_type'].iloc[x],
con=con,settle_date=pnl_date,
return_nan_if_emptyQ = True)['close_price'].values[0] for x in range(len(option_frame.index))]
trades_frame = pd.concat([option_frame, underlying_frame])
position_frame = trades_frame[trades_frame['trade_date'] < pnl_datetime]
intraday_frame = trades_frame[trades_frame['trade_date'] == pnl_datetime]
position_pnl_per_ticker = pd.DataFrame(columns=['ticker','pnl_position'])
intraday_pnl_per_ticker = pd.DataFrame(columns=['ticker','pnl_intraday'])
position_pnl_per_tickerhead = pd.DataFrame(columns=['ticker_head','pnl_position'])
intraday_pnl_per_tickerhead = pd.DataFrame(columns=['ticker_head','pnl_intraday'])
if len(position_frame) == 0:
position_pnl = 0
else:
position_frame['pnl'] = position_frame['contract_multiplier']*\
position_frame['trade_quantity']*\
(position_frame['price']-position_frame['price_1'])
position_pnl = position_frame['pnl'].sum()
position_grouped_per_ticker = position_frame.groupby('ticker')
position_grouped_per_tickerhead = position_frame.groupby('ticker_head')
position_pnl_per_ticker['pnl_position'] = (position_grouped_per_ticker['pnl'].sum()).values
position_pnl_per_ticker['ticker'] = (position_grouped_per_ticker['ticker'].first()).values
position_pnl_per_tickerhead['pnl_position'] = (position_grouped_per_tickerhead['pnl'].sum()).values
position_pnl_per_tickerhead['ticker_head'] = (position_grouped_per_tickerhead['ticker_head'].first()).values
if len(intraday_frame) == 0:
intraday_pnl = 0
t_cost = 0
else:
intraday_frame['pnl'] = intraday_frame['contract_multiplier']*\
intraday_frame['trade_quantity']*\
(intraday_frame['price']-intraday_frame['trade_price'])
intraday_frame['pnl_wtcost'] = intraday_frame['pnl']-abs(intraday_frame['trade_quantity']*intraday_frame['t_cost'])
intraday_pnl = intraday_frame['pnl'].sum()
t_cost = (abs(intraday_frame['trade_quantity']*intraday_frame['t_cost'])).sum()
intraday_grouped_per_ticker = intraday_frame.groupby('ticker')
intraday_grouped_per_tickerhead = intraday_frame.groupby('ticker_head')
intraday_pnl_per_ticker['pnl_intraday'] = (intraday_grouped_per_ticker['pnl_wtcost'].sum()).values
intraday_pnl_per_ticker['ticker'] = (intraday_grouped_per_ticker['ticker'].first()).values
intraday_pnl_per_tickerhead['pnl_intraday'] = (intraday_grouped_per_tickerhead['pnl_wtcost'].sum()).values
intraday_pnl_per_tickerhead['ticker_head'] = (intraday_grouped_per_tickerhead['ticker_head'].first()).values
pnl_per_ticker = pd.merge(position_pnl_per_ticker,intraday_pnl_per_ticker,how='outer',on='ticker')
intraday_zero_indx = [x not in intraday_pnl_per_ticker['ticker'].values for x in pnl_per_ticker['ticker']]
position_zero_indx = [x not in position_pnl_per_ticker['ticker'].values for x in pnl_per_ticker['ticker']]
pnl_per_ticker['pnl_position'][position_zero_indx] = 0
pnl_per_ticker['pnl_intraday'][intraday_zero_indx] = 0
pnl_per_ticker['pnl_total'] = pnl_per_ticker['pnl_position']+pnl_per_ticker['pnl_intraday']
pnl_per_ticker.set_index('ticker', drop=True, inplace=True)
pnl_per_tickerhead = pd.merge(position_pnl_per_tickerhead,intraday_pnl_per_tickerhead,how='outer',on='ticker_head')
intraday_zero_indx = [x not in intraday_pnl_per_tickerhead['ticker_head'].values for x in pnl_per_tickerhead['ticker_head']]
position_zero_indx = [x not in position_pnl_per_tickerhead['ticker_head'].values for x in pnl_per_tickerhead['ticker_head']]
pnl_per_tickerhead['pnl_position'][position_zero_indx] = 0
pnl_per_tickerhead['pnl_intraday'][intraday_zero_indx] = 0
pnl_per_tickerhead['pnl_total'] = pnl_per_tickerhead['pnl_position']+pnl_per_tickerhead['pnl_intraday']
pnl_per_tickerhead.set_index('ticker_head', drop=True, inplace=True)
if 'con' not in kwargs.keys():
con.close()
return {'total_pnl': int(position_pnl+intraday_pnl - t_cost),
'position_pnl': int(position_pnl),
'intraday_pnl': int(intraday_pnl),
't_cost': int(t_cost),
'pnl_per_ticker': pnl_per_ticker,
'pnl_per_tickerhead':pnl_per_tickerhead}
def get_hedge_4strategy(**kwargs):
con = msu.get_my_sql_connection(**kwargs)
current_date = cu.get_doubledate()
settle_price_date = exp.doubledate_shift_bus_days(double_date=current_date, shift_in_days=1)
position_frame = tas.get_net_position_4strategy_alias(alias=kwargs['alias'],as_of_date=current_date,con=con)
intraday_price_frame = gip.get_cme_direct_prices()
intraday_price_frame.rename(columns={'ticker': 'underlying_ticker'},inplace=True)
intraday_price_frame['ticker_head'] = [cmi.get_contract_specs(x)['ticker_head'] for x in intraday_price_frame['underlying_ticker']]
intraday_price_frame['mid_price'] = (intraday_price_frame['bid_price'] + intraday_price_frame['ask_price'])/2
intraday_price_frame['mid_price'] = [tfl.convert_trade_price_from_cme_direct(ticker_head=intraday_price_frame['ticker_head'].iloc[x],
price=intraday_price_frame['mid_price'].iloc[x]) for x in range(len(intraday_price_frame.index))]
options_frame = position_frame[position_frame['instrument'] == 'O']
futures_frame = position_frame[position_frame['instrument'] == 'F']
if options_frame.empty:
futures_frame.rename(columns={'ticker': 'underlying_ticker', 'qty': 'underlying_delta'},inplace=True)
futures_frame = futures_frame[['underlying_ticker', 'underlying_delta']]
net_position = pd.merge(futures_frame, intraday_price_frame, how='left', on='underlying_ticker')
net_position['hedge_price'] = net_position['mid_price']
net_position['hedge'] = -net_position['underlying_delta']
return net_position
imp_vol_list = [gop.get_options_price_from_db(ticker=options_frame['ticker'].iloc[x],
settle_date=settle_price_date,
strike=options_frame['strike_price'].iloc[x],
column_names=['imp_vol'],
con=con)['imp_vol'] for x in range(len(options_frame.index))]
options_frame['imp_vol'] = [imp_vol_list[x][1] if (np.isnan(imp_vol_list[x][0]) and len(imp_vol_list[x]) > 1) else imp_vol_list[x][0]
for x in range(len(options_frame.index))]
options_frame['underlying_ticker'] = [omu.get_option_underlying(ticker=x) for x in options_frame['ticker']]
options_frame = pd.merge(options_frame, intraday_price_frame, how='left', on='underlying_ticker')
options_frame['ticker_head'] = [cmi.get_contract_specs(x)['ticker_head'] for x in options_frame['ticker']]
options_frame['exercise_type'] = [cmi.get_option_exercise_type(ticker_head=x) for x in options_frame['ticker_head']]
options_frame['strike_price'] = options_frame['strike_price'].astype('float64')
options_frame['delta'] = [omu.option_model_wrapper(ticker=options_frame['ticker'].iloc[x],
calculation_date=current_date,
interest_rate_date=settle_price_date,
underlying=options_frame['mid_price'].iloc[x],
strike=options_frame['strike_price'].iloc[x],
implied_vol=options_frame['imp_vol'].iloc[x],
option_type=options_frame['option_type'].iloc[x],
exercise_type=options_frame['exercise_type'].iloc[x],
con=con)['delta'] for x in range(len(options_frame.index))]
options_frame['total_delta'] = options_frame['qty']*options_frame['delta']
grouped = options_frame.groupby('underlying_ticker')
net_position = pd.DataFrame()
net_position['underlying_ticker'] = (grouped['underlying_ticker'].first()).values
net_position['hedge_price'] = (grouped['mid_price'].first()).values
net_position['option_delta'] = (grouped['total_delta'].sum()).values
net_position['option_delta'] = net_position['option_delta'].round(2)
if futures_frame.empty:
net_position['total_delta'] = net_position['option_delta']
else:
futures_frame.rename(columns={'ticker': 'underlying_ticker', 'qty': 'underlying_delta'},inplace=True)
futures_frame = futures_frame[['underlying_ticker', 'underlying_delta']]
isinOptions = futures_frame['underlying_ticker'].isin(net_position['underlying_ticker'])
futures_frame_w_options = futures_frame[isinOptions]
futures_frame_wo_options = futures_frame[~isinOptions]
if futures_frame_w_options.empty:
net_position['underlying_delta'] = 0
net_position['total_delta'] = net_position['option_delta']
else:
net_position = pd.merge(net_position, futures_frame_w_options, how='outer', on='underlying_ticker')
net_position['total_delta'] = net_position['option_delta']+net_position['underlying_delta']
if not futures_frame_wo_options.empty:
net_position_futures = pd.merge(futures_frame_wo_options, intraday_price_frame, how='left', on='underlying_ticker')
net_position_futures['hedge_price'] = net_position_futures['mid_price']
net_position_futures['option_delta'] = 0
net_position_futures['total_delta'] = net_position_futures['underlying_delta']
net_position = pd.concat([net_position,net_position_futures[['underlying_ticker','hedge_price','option_delta','underlying_delta','total_delta']]])
net_position['hedge'] = -net_position['total_delta']
if 'con' not in kwargs.keys():
con.close()
return net_position
def get_book_snapshot_4ticker(**kwargs):
if 'folder_date' in kwargs.keys():
folder_date = kwargs['folder_date']
else:
folder_date = exp.doubledate_shift_bus_days()
if 'freq_str' in kwargs.keys():
freq_str = kwargs['freq_str']
else:
freq_str = 'T'
ticker = kwargs['ticker']
data_dir = dn.get_dated_directory_extension(ext='intraday_ttapi_data_fixed_interval', folder_date=folder_date)
file_name = data_dir + '/' + ticker + '_' + freq_str + '.pkl'
if os.path.isfile(file_name):
book_snapshot = pd.read_pickle(file_name)
return book_snapshot
data_frame_out = load_csv_file_4ticker(**kwargs)
if data_frame_out.empty:
return pd.DataFrame(columns=['best_bid_p','best_bid_q','best_ask_p','best_ask_q'])
start_datetime = dt.datetime.utcfromtimestamp(data_frame_out['time'].values[0].tolist()/1e9).replace(microsecond=0, second=0)
end_datetime = dt.datetime.utcfromtimestamp(data_frame_out['time'].values[-1].tolist()/1e9).replace(microsecond=0, second=0)
merged_index = pd.date_range(start=start_datetime,end=end_datetime,freq=freq_str)
data_frame_out.set_index('time', inplace=True, drop=True)
best_bid_p = data_frame_out[data_frame_out['field'] == 'BestBidPrice']
best_bid_p = best_bid_p.groupby(best_bid_p.index).last()
best_bid_p = best_bid_p.reindex(merged_index,method='pad')
best_bid_q = data_frame_out[data_frame_out['field'] == 'BestBidQuantity']
best_bid_q = best_bid_q.groupby(best_bid_q.index).last()
best_bid_q = best_bid_q.reindex(merged_index,method='pad')
best_ask_p = data_frame_out[data_frame_out['field'] == 'BestAskPrice']
best_ask_p = best_ask_p.groupby(best_ask_p.index).last()
best_ask_p = best_ask_p.reindex(merged_index,method='pad')
best_ask_q = data_frame_out[data_frame_out['field'] == 'BestAskQuantity']
best_ask_q = best_ask_q.groupby(best_ask_q.index).last()
best_ask_q = best_ask_q.reindex(merged_index,method='pad')
book_snapshot = pd.DataFrame(index=merged_index)
book_snapshot['best_bid_p'] = best_bid_p['value'].astype('float64')
book_snapshot['best_bid_q'] = best_bid_q['value']
book_snapshot['best_ask_p'] = best_ask_p['value'].astype('float64')
book_snapshot['best_ask_q'] = best_ask_q['value']
ticker_head = cmi.get_contract_specs(kwargs['ticker'])['ticker_head']
book_snapshot['best_bid_p'] = [tfl.convert_trade_price_from_tt(price=x,ticker_head=ticker_head) for x in book_snapshot['best_bid_p']]
book_snapshot['best_ask_p'] = [tfl.convert_trade_price_from_tt(price=x,ticker_head=ticker_head) for x in book_snapshot['best_ask_p']]
book_snapshot.to_pickle(file_name)
return book_snapshot
def generate_vcs_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
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))]
vcs_indx = [x == 'vcs' for x in strategy_class_list]
vcs_frame = strategy_frame[vcs_indx]
results = [sf.get_results_4strategy(alias=vcs_frame['alias'].iloc[x],
strategy_info_output=vcs_frame.iloc[x])
for x in range(len(vcs_frame.index))]
vcs_followup_frame = pd.DataFrame(results)
vcs_followup_frame['alias'] = vcs_frame['alias'].values
pnl_frame = pm.get_daily_pnl_snapshot(**kwargs)
merged_frame1 = pd.merge(vcs_followup_frame,pnl_frame, how='left', on='alias')
vcs_followup_frame = merged_frame1[['alias', 'last_adjustment_days_ago','min_tr_dte', 'long_short_ratio',
'net_oev', 'net_theta', 'long_oev', 'short_oev', 'favQMove', 'total_pnl','recommendation']]
vcs_followup_frame['long_short_ratio'] = vcs_followup_frame['long_short_ratio'].round()
vcs_followup_frame['net_oev'] = vcs_followup_frame['net_oev'].round(1)
vcs_followup_frame['long_oev'] = vcs_followup_frame['long_oev'].round(1)
vcs_followup_frame['short_oev'] = vcs_followup_frame['short_oev'].round(1)
vcs_followup_frame['net_theta'] = vcs_followup_frame['net_theta'].round(1)
vcs_followup_frame.sort('total_pnl', ascending=False, inplace=True)
vcs_followup_frame.reset_index(drop=True,inplace=True)
vcs_followup_frame.loc[len(vcs_followup_frame.index)] = ['TOTAL', None, None, None, None, vcs_followup_frame['net_theta'].sum(),
None, None, None, vcs_followup_frame['total_pnl'].sum(), None]
vcs_followup_frame.to_excel(writer, sheet_name='vcs')
worksheet_vcs = writer.sheets['vcs']
worksheet_vcs.set_column('B:B', 18)
worksheet_vcs.freeze_panes(1, 0)
worksheet_vcs.autofilter(0, 0, len(vcs_followup_frame.index),
len(vcs_followup_frame.columns))
if 'con' not in kwargs.keys():
con.close()
writer.save()
def generate_futures_butterfly_followup_report(**kwargs):
con = msu.get_my_sql_connection(**kwargs)
if 'as_of_date' in kwargs.keys():
as_of_date = kwargs['as_of_date']
else:
as_of_date = exp.doubledate_shift_bus_days()
kwargs['as_of_date'] = as_of_date
if 'writer' in kwargs.keys():
writer = kwargs['writer']
else:
ta_output_dir = dn.get_dated_directory_extension(folder_date=as_of_date, ext='ta')
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))]
futures_butterfly_indx = [x == 'futures_butterfly' for x in strategy_class_list]
futures_butterfly_frame = strategy_frame[futures_butterfly_indx]
results = [sf.get_results_4strategy(alias=futures_butterfly_frame['alias'].iloc[x],
strategy_info_output=futures_butterfly_frame.iloc[x])
for x in range(len(futures_butterfly_frame.index))]
butterfly_followup_frame = pd.DataFrame(results)
butterfly_followup_frame['alias'] = futures_butterfly_frame['alias'].values
pnl_frame = pm.get_daily_pnl_snapshot(as_of_date=as_of_date, con=con)
risk_output = hr.get_historical_risk_4open_strategies(as_of_date=as_of_date, con=con)
merged_frame1 = pd.merge(butterfly_followup_frame,pnl_frame, how='left', on='alias')
merged_frame2 = pd.merge(merged_frame1, risk_output['strategy_risk_frame'], how='left', on='alias')
butterfly_followup_frame = merged_frame2[['alias', 'ticker_head', 'holding_tr_dte', 'short_tr_dte',
'z1_initial', 'z1', 'QF_initial', 'QF',
'total_pnl', 'downside','recommendation']]
butterfly_followup_frame.rename(columns={'alias': 'Alias', 'ticker_head': 'TickerHead',
'holding_tr_dte': 'HoldingTrDte', 'short_tr_dte': 'ShortTrDte',
'z1_initial': 'Z1Initial', 'z1': 'Z1',
'QF_initial': 'QFInitial','total_pnl': 'TotalPnl',
'downside': 'Downside','recommendation':'Recommendation'}, inplace=True)
butterfly_followup_frame.sort('QF', ascending=False,inplace=True)
butterfly_followup_frame['Z1'] = butterfly_followup_frame['Z1'].round(2)
butterfly_followup_frame.to_excel(writer, sheet_name='butterflies')
worksheet_butterflies = writer.sheets['butterflies']
worksheet_butterflies.set_column('B:B', 26)
worksheet_butterflies.freeze_panes(1, 0)
worksheet_butterflies.autofilter(0, 0, len(butterfly_followup_frame.index),
len(butterfly_followup_frame.columns))
if 'con' not in kwargs.keys():
con.close()
return writer
def move_position_from_strategy_2_strategy(**kwargs):
strategy_from = kwargs['strategy_from']
strategy_to = kwargs['strategy_to']
now_time = dt.datetime.now()
con = msu.get_my_sql_connection(**kwargs)
if 'con' not in kwargs.keys():
kwargs['con'] = con
if 'as_of_date' in kwargs.keys():
as_of_date = kwargs['as_of_date']
else:
as_of_date = exp.doubledate_shift_bus_days()
kwargs['as_of_date'] = as_of_date
net_position_frame = get_net_position_4strategy_alias(alias=strategy_from, **kwargs)
target_strategy_id = get_strategy_id_from_alias(alias=strategy_to, **kwargs)
source_strategy_id = get_strategy_id_from_alias(alias=strategy_from, **kwargs)
futures_position_frame = net_position_frame[net_position_frame['instrument'] == 'F']
options_position_frame = net_position_frame[net_position_frame['instrument'] == 'O']
futures_position_frame['trade_price'] = \
[float(gfp.get_futures_price_4ticker(ticker=x,date_from=as_of_date,date_to=as_of_date,con=con)['close_price'][0]) for x in futures_position_frame['ticker']]
if not options_position_frame.empty:
options_position_frame['trade_price'] = options_position_frame.apply(lambda row: gop.get_options_price_from_db(ticker=row['ticker'],
strike=row['strike_price'],
option_type=row['option_type'],con=con,
return_nan_if_emptyQ=True,
settle_date=as_of_date)['close_price'][0], axis=1)
net_position_frame = pd.concat([futures_position_frame,options_position_frame])
else:
net_position_frame = futures_position_frame
column_names = net_position_frame.columns.tolist()
ticker_indx = column_names.index('ticker')
option_type_indx = column_names.index('option_type')
strike_price_indx = column_names.index('strike_price')
trade_price_indx = column_names.index('trade_price')
trade_quantity_indx = column_names.index('qty')
instrument_indx = column_names.index('instrument')
column_str = "ticker, option_type, strike_price, strategy_id, trade_price, trade_quantity, trade_date, instrument, real_tradeQ, created_date, last_updated_date"
insert_str = ("%s, " * 11)[:-2]
final_str = "INSERT INTO trades (%s) VALUES (%s)" % (column_str, insert_str)
tuples_target = [tuple([x[ticker_indx],x[option_type_indx],
None if np.isnan(x[strike_price_indx]) else x[strike_price_indx], target_strategy_id,
x[trade_price_indx], x[trade_quantity_indx],
as_of_date,x[instrument_indx], True,now_time,now_time]) for x in net_position_frame.values]
msu.sql_execute_many_wrapper(final_str=final_str, tuples=tuples_target, con=con)
tuples_source = [tuple([x[ticker_indx],x[option_type_indx],
None if np.isnan(x[strike_price_indx]) else x[strike_price_indx], source_strategy_id,
x[trade_price_indx], -x[trade_quantity_indx],
as_of_date,x[instrument_indx], True,now_time,now_time]) for x in net_position_frame.values]
msu.sql_execute_many_wrapper(final_str=final_str, tuples=tuples_source, con=con)
if 'con' not in kwargs.keys():
con.close()
def get_intraday_trend_signals(**kwargs):
ticker = kwargs['ticker']
date_to = kwargs['date_to']
datetime_to = cu.convert_doubledate_2datetime(date_to)
breakout_method = 2
#print(ticker)
ticker_head = cmi.get_contract_specs(ticker)['ticker_head']
contract_multiplier = cmi.contract_multiplier[ticker_head]
ticker_class = cmi.ticker_class[ticker_head]
daily_settles = gfp.get_futures_price_preloaded(ticker=ticker)
daily_settles = daily_settles[daily_settles['settle_date'] <= datetime_to]
daily_settles['ewma10'] = pd.ewma(daily_settles['close_price'], span=10)
daily_settles['ewma50'] = pd.ewma(daily_settles['close_price'], span=50)
if daily_settles['ewma10'].iloc[-1] > daily_settles['ewma50'].iloc[-1]:
long_term_trend = 1
else:
long_term_trend = -1
date_list = [exp.doubledate_shift_bus_days(double_date=date_to,shift_in_days=1)]
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['hour_minute'] = [100*x.hour+x.minute for x in intraday_data['time_stamp']]
end_hour = cmi.last_trade_hour_minute[ticker_head]
start_hour = cmi.first_trade_hour_minute[ticker_head]
if ticker_class in ['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)]
selected_data = intraday_data.iloc[selection_indx]
selected_data['mid_p'] = (selected_data['c1']['best_bid_p']+selected_data['c1']['best_ask_p'])/2
selected_data['ewma100'] = pd.ewma(selected_data['mid_p'], span=100)
selected_data['ewma25'] = pd.ewma(selected_data['mid_p'], span=25)
selected_data.reset_index(inplace=True,drop=True)
datetime_to = cu.convert_doubledate_2datetime(date_to)
range_start = dt.datetime.combine(datetime_to,dt.time(8,30,0,0))
range_end = dt.datetime.combine(datetime_to,dt.time(9,0,0,0))
first_30_minutes = selected_data[(selected_data['time_stamp'] >= range_start)&
(selected_data['time_stamp'] <= range_end)]
trading_data = selected_data[selected_data['time_stamp'] > range_end]
trading_data_shifted = trading_data.shift(5)
range_min = first_30_minutes['mid_p'].min()
range_max = first_30_minutes['mid_p'].max()
initial_range = range_max-range_min
if breakout_method == 1:
bullish_breakout = trading_data[(trading_data['mid_p'] > range_max)&
(trading_data['mid_p'] < range_max+0.5*initial_range)&
(trading_data_shifted['mid_p']<range_max)&
(trading_data['ewma25'] > range_max)&
(trading_data['mid_p'] > trading_data['ewma100'])]
bearish_breakout = trading_data[(trading_data['mid_p'] < range_min)&
(trading_data['mid_p'] > range_min-0.5*initial_range)&
(trading_data_shifted['mid_p']>range_min)&
(trading_data['ewma25'] < range_min)&
(trading_data['mid_p'] < trading_data['ewma100'])]
elif breakout_method == 2:
bullish_breakout = pd.DataFrame()
bearish_breakout = pd.DataFrame()
if long_term_trend > 0:
bullish_breakout = trading_data[(trading_data['mid_p'] > range_max)&
(trading_data_shifted['mid_p']<range_max)&
(long_term_trend == 1)&
(trading_data['mid_p'] > trading_data['ewma100'])]
elif long_term_trend < 0:
bearish_breakout = trading_data[(trading_data['mid_p'] < range_min)&
(trading_data_shifted['mid_p']>range_min)&
(long_term_trend == -1)&
(trading_data['mid_p'] < trading_data['ewma100'])]
bullish_cross = trading_data[trading_data['mid_p'] > trading_data['ewma100']]
bearish_cross = trading_data[trading_data['mid_p'] < trading_data['ewma100']]
end_of_day_price = trading_data['mid_p'].iloc[-1]
end_of_day_time_stamp = trading_data['time_stamp'].iloc[-1]
valid_bearish_breakoutQ = False
valid_bullish_breakoutQ = False
bearish_breakout_price_entry = np.NaN
bullish_breakout_price_entry = np.NaN
if not bearish_breakout.empty:
if bearish_breakout.index[0]+1 < trading_data.index[-1]:
if trading_data['mid_p'].loc[bearish_breakout.index[0]+1]>range_min-0.5*initial_range:
valid_bearish_breakoutQ = True
bearish_breakout_price_entry = trading_data['mid_p'].loc[bearish_breakout.index[0]+1]
bearish_breakout_time_stamp = trading_data['time_stamp'].loc[bearish_breakout.index[0]+1]
if not bullish_breakout.empty:
if bullish_breakout.index[0]+1<trading_data.index[-1]:
if trading_data['mid_p'].loc[bullish_breakout.index[0]+1]<range_max+0.5*initial_range:
valid_bullish_breakoutQ = True
bullish_breakout_price_entry = trading_data['mid_p'].loc[bullish_breakout.index[0]+1]
bullish_breakout_time_stamp = trading_data['time_stamp'].loc[bullish_breakout.index[0]+1]
stop_loss = (range_max-range_min)*contract_multiplier
pnl_list = []
direction_list = []
entry_time_list = []
exit_time_list = []
entry_price_list = []
exit_price_list = []
daily_trade_no_list = []
exit_type_list = []
ticker_list = []
if valid_bearish_breakoutQ:
direction_list.append(-1)
entry_time_list.append(bearish_breakout_time_stamp)
entry_price_list.append(bearish_breakout_price_entry)
daily_pnl = bearish_breakout_price_entry-end_of_day_price
exit_price = end_of_day_price
exit_type = 'eod'
exit_time = end_of_day_time_stamp
daily_trade_no = 1
#bullish_cross_stop_frame = bullish_cross[(bullish_cross['time_stamp'] > bearish_breakout_time_stamp)]
#if (not bullish_cross_stop_frame.empty) and (bullish_cross_stop_frame.index[0]+1<trading_data.index[-1]):
# daily_pnl = bearish_breakout_price_entry-trading_data['mid_p'].loc[bullish_cross_stop_frame.index[0]+1]
# exit_price = trading_data['mid_p'].loc[bullish_cross_stop_frame.index[0]+1]
# exit_type = 'oso'
# exit_time = trading_data['time_stamp'].loc[bullish_cross_stop_frame.index[0]+1]
#if valid_bullish_breakoutQ:
# if bullish_breakout_time_stamp>bearish_breakout_time_stamp:
# if bullish_breakout_time_stamp<exit_time:
# daily_pnl = bearish_breakout_price_entry-bullish_breakout_price_entry
# exit_price = bullish_breakout_price_entry
# exit_type = 'fso'
# exit_time = bullish_breakout_time_stamp
# else:
# daily_trade_no = 2
exit_time_list.append(exit_time)
exit_type_list.append(exit_type)
daily_trade_no_list.append(daily_trade_no)
pnl_list.append(daily_pnl)
exit_price_list.append(exit_price)
ticker_list.append(ticker)
if valid_bullish_breakoutQ:
direction_list.append(1)
entry_time_list.append(bullish_breakout_time_stamp)
entry_price_list.append(bullish_breakout_price_entry)
daily_pnl = end_of_day_price-bullish_breakout_price_entry
exit_price = end_of_day_price
exit_type = 'eod'
exit_time = end_of_day_time_stamp
daily_trade_no = 1
bearish_cross_stop_frame = bearish_cross[(bearish_cross['time_stamp'] > bullish_breakout_time_stamp)]
#if (not bearish_cross_stop_frame.empty) and (bearish_cross_stop_frame.index[0]+1 < trading_data.index[-1]):
# daily_pnl = trading_data['mid_p'].loc[bearish_cross_stop_frame.index[0]+1]-bullish_breakout_price_entry
# exit_price = trading_data['mid_p'].loc[bearish_cross_stop_frame.index[0]+1]
# exit_type = 'oso'
# exit_time = trading_data['time_stamp'].loc[bearish_cross_stop_frame.index[0]+1]
#if valid_bearish_breakoutQ:
# if bearish_breakout_time_stamp>bullish_breakout_time_stamp:
# if bearish_breakout_time_stamp<exit_time:
# daily_pnl = bearish_breakout_price_entry-bullish_breakout_price_entry
# exit_price = bearish_breakout_price_entry
# exit_type = 'fso'
# exit_time = bearish_breakout_time_stamp
# else:
# daily_trade_no = 2
exit_time_list.append(exit_time)
exit_type_list.append(exit_type)
daily_trade_no_list.append(daily_trade_no)
pnl_list.append(daily_pnl)
exit_price_list.append(exit_price)
ticker_list.append(ticker)
pnl_frame = pd.DataFrame.from_items([('ticker', ticker_list),
('ticker_head',ticker_head),
('direction', direction_list),
('entry_price', entry_price_list),
('exit_price', exit_price_list),
('pnl', pnl_list),
('entry_time', entry_time_list),
('exit_time', exit_time_list),
('exit_type', exit_type_list),
('daily_trade_no', daily_trade_no_list)])
pnl_frame['pnl'] = pnl_frame['pnl']*contract_multiplier
return {'intraday_data': selected_data, 'range_min': range_min, 'range_max':range_max,'pnl_frame':pnl_frame,'stop_loss':stop_loss}
def get_strategy_pnl(**kwargs):
alias = kwargs['alias']
con = msu.get_my_sql_connection(**kwargs)
#print(alias)
strategy_info = ts.get_strategy_info_from_alias(alias=alias, con=con)
if 'as_of_date' in kwargs.keys():
as_of_date = kwargs['as_of_date']
else:
as_of_date = exp.doubledate_shift_bus_days()
open_date = int(strategy_info['open_date'].strftime('%Y%m%d'))
close_date = int(strategy_info['close_date'].strftime('%Y%m%d'))
if close_date>as_of_date:
close_date = as_of_date
bus_day_list = exp.get_bus_day_list(date_from=open_date,date_to=close_date)
trades_frame = ts.get_trades_4strategy_alias(alias=alias,con=con)
ticker_head_list = [cmi.get_contract_specs(x)['ticker_head'] for x in trades_frame['ticker']]
unique_ticker_head_list = list(set(ticker_head_list))
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 unique_ticker_head_list}
trades_frame['contract_multiplier'] = [cmi.contract_multiplier[x] for x in ticker_head_list]
trades_frame['t_cost'] = [cmi.t_cost[x] for x in ticker_head_list]
pnl_path = [get_strategy_pnl_4day(alias=alias,pnl_date=x,con=con,
trades_frame=trades_frame,
futures_data_dictionary=futures_data_dictionary) for x in bus_day_list]
pnl_per_tickerhead_list = [x['pnl_per_tickerhead'] for x in pnl_path]
pnl_per_tickerhead = pd.concat(pnl_per_tickerhead_list, axis=1)
pnl_per_tickerhead = pnl_per_tickerhead[['pnl_total']]
pnl_per_tickerhead = pnl_per_tickerhead.transpose()
if len(unique_ticker_head_list)>1:
zero_indx = [[x not in y.index for y in pnl_per_tickerhead_list] for x in pnl_per_tickerhead.columns]
for i in range(len(pnl_per_tickerhead.columns)):
pnl_per_tickerhead.iloc[:, i][zero_indx[i]] = 0
pnl_per_tickerhead['settle_date'] = bus_day_list
pnl_per_tickerhead.reset_index(inplace=True,drop=True)
pnl_frame = pd.DataFrame(pnl_path)
pnl_frame['settle_date'] = bus_day_list
if 'con' not in kwargs.keys():
con.close()
return {'pnl_frame': pnl_frame[['settle_date','position_pnl','intraday_pnl','t_cost','total_pnl']],
'pnl_per_tickerhead': pnl_per_tickerhead,
'daily_pnl': pnl_frame['total_pnl'].values[-1],
'total_pnl': pnl_frame['total_pnl'].sum()}
