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 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_liquid_spread_frame(**kwargs):
ticker_head_list = cmi.futures_butterfly_strategy_tickerhead_list
data_dir = dn.get_dated_directory_extension(ext='intraday_ttapi_data', folder_date=kwargs['settle_date'])
file_name = 'ContractList.csv'
data_frame_out = pd.read_csv(data_dir + '/' + file_name)
data_frame_out_filtered = data_frame_out[(data_frame_out['ProductType'] == 'SPREAD')&
((data_frame_out['InstrumentName'].str.contains('Calendar')&
(data_frame_out['MarketKey'] == 'CME')))|
((data_frame_out['InstrumentName'].str.contains('Spread')&
(data_frame_out['MarketKey'] == 'ICE_IPE')))]
num_contracts = len(data_frame_out_filtered.index)
reformat_out_list = [tfl.get_ticker_from_tt_instrument_name_and_product_name(instrument_name=data_frame_out_filtered['InstrumentName'].iloc[x],
product_name =data_frame_out_filtered['ProductName'].iloc[x] ) for x in range(num_contracts)]
data_frame_out_filtered['ticker'] = [reformat_out_list[x]['ticker'] for x in range(num_contracts)]
data_frame_out_filtered['ticker_head'] = [reformat_out_list[x]['ticker_head'] for x in range(num_contracts)]
selection_indx = [data_frame_out_filtered['ticker_head'].iloc[x] in ticker_head_list for x in range(num_contracts)]
data_frame_out_filtered2 = data_frame_out_filtered[selection_indx]
data_frame_out_filtered2.sort(['ticker_head','Volume'],ascending=[True, False],inplace=True)
return data_frame_out_filtered2
def load_options_backtesting_data(**kwargs):
ticker = kwargs['ticker']
settle_date = kwargs['settle_date']
delta = kwargs['delta']
if 'model' in kwargs.keys():
model = kwargs['model']
else:
model = 'BS'
option_data_dir = dn.get_dated_directory_extension(folder_date=settle_date, ext='options_backtesting_data')
file_dir = ticker + '_' + model + '_' + str(delta) + '.mat'
output_dictionary = {'final_pnl5': np.NaN, 'final_pnl10': np.NaN, 'final_pnl20': np.NaN,
'accumulated_theta_5': np.NaN, 'accumulated_theta_10': np.NaN, 'accumulated_theta_20': np.NaN}
if os.path.isfile(option_data_dir+'/'+file_dir):
try:
mat_output = scipy.io.loadmat(option_data_dir+'/'+file_dir)
paper_trader_output = mat_output['paperTraderOutput']
output_dictionary = {'final_pnl5': paper_trader_output['finalPnl5'][0][0][0][0],
'final_pnl10': paper_trader_output['finalPnl10'][0][0][0][0],
'final_pnl20': paper_trader_output['finalPnl20'][0][0][0][0],
'accumulated_theta_5': paper_trader_output['accumulatedTheta5'][0][0][0][0],
'accumulated_theta_10': paper_trader_output['accumulatedTheta10'][0][0][0][0],
'accumulated_theta_20': paper_trader_output['accumulatedTheta20'][0][0][0][0]}
except Exception:
print('Cannot load ' + file_dir + ' !')
return output_dictionary
def get_logger(**kwargs):
now_datetime = dt.datetime.now()
folder_string = dn.get_dated_directory_extension(
ext='log', folder_date=int(now_datetime.strftime('%Y%m%d')))
file_identifier = kwargs['file_identifier']
if 'log_level' in kwargs.keys():
log_level = kwargs['log_level']
else:
log_level = 'WARNING'
if log_level.upper() == 'CRITICAL':
log_level = lgg.CRITICAL
elif log_level.upper() == 'ERROR':
log_level = lgg.ERROR
elif log_level.upper() == 'WARNING':
log_level = lgg.WARNING
elif log_level.upper() == 'INFO':
log_level = lgg.INFO
elif log_level.upper() == 'DEBUG':
log_level = lgg.DEBUG
logger = lgg.getLogger(__name__)
logger.setLevel(log_level)
handler = lgg.FileHandler(folder_string + '/' +
now_datetime.strftime('%Y%m%d_%H%M%S') + '_' +
file_identifier + '.log')
handler.setLevel(log_level)
logger.addHandler(handler)
return logger
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 load_options_backtesting_data(**kwargs):
ticker = kwargs['ticker']
settle_date = kwargs['settle_date']
delta = kwargs['delta']
if 'model' in kwargs.keys():
model = kwargs['model']
else:
model = 'BS'
option_data_dir = dn.get_dated_directory_extension(folder_date=settle_date, ext='options_backtesting_data')
file_dir = ticker + '_' + model + '_' + str(delta) + '.mat'
output_dictionary = {'final_pnl5': np.NaN, 'final_pnl10': np.NaN, 'final_pnl20': np.NaN,
'accumulated_theta_5': np.NaN, 'accumulated_theta_10': np.NaN, 'accumulated_theta_20': np.NaN}
if os.path.isfile(option_data_dir+'/'+file_dir):
try:
mat_output = scipy.io.loadmat(option_data_dir+'/'+file_dir)
paper_trader_output = mat_output['paperTraderOutput']
output_dictionary = {'final_pnl5': paper_trader_output['finalPnl5'][0][0][0][0],
'final_pnl10': paper_trader_output['finalPnl10'][0][0][0][0],
'final_pnl20': paper_trader_output['finalPnl20'][0][0][0][0],
'accumulated_theta_5': paper_trader_output['accumulatedTheta5'][0][0][0][0],
'accumulated_theta_10': paper_trader_output['accumulatedTheta10'][0][0][0][0],
'accumulated_theta_20': paper_trader_output['accumulatedTheta20'][0][0][0][0]}
except Exception:
print('Cannot load ' + file_dir + ' !')
return output_dictionary
def get_liquid_outright_futures_frame(**kwargs):
ticker_head_list = list(set(cmi.futures_butterfly_strategy_tickerhead_list) | set(cmi.cme_futures_tickerhead_list))
data_dir = dn.get_dated_directory_extension(ext="intraday_ttapi_data", folder_date=kwargs["settle_date"])
file_name = "ContractList.csv"
data_frame_out = pd.read_csv(data_dir + "/" + file_name)
data_frame_out_filtered = data_frame_out[data_frame_out["ProductType"] == "FUTURE"]
num_contracts = len(data_frame_out_filtered.index)
reformat_out_list = [
tfl.get_ticker_from_tt_instrument_name_and_product_name(
instrument_name=data_frame_out_filtered["InstrumentName"].iloc[x],
product_name=data_frame_out_filtered["ProductName"].iloc[x],
)
for x in range(num_contracts)
]
data_frame_out_filtered["ticker"] = [reformat_out_list[x]["ticker"] for x in range(num_contracts)]
data_frame_out_filtered["ticker_head"] = [reformat_out_list[x]["ticker_head"] for x in range(num_contracts)]
selection_indx = [data_frame_out_filtered["ticker_head"].iloc[x] in ticker_head_list for x in range(num_contracts)]
data_frame_out_filtered2 = data_frame_out_filtered[selection_indx]
data_frame_out_filtered2.sort(["ticker_head", "Volume"], ascending=[True, False], inplace=True)
return data_frame_out_filtered2.drop_duplicates("ticker_head")
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 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_tagged_tt_fills(**kwargs):
fill_frame = load_latest_tt_fills(**kwargs)
str_indx = fill_frame['Contract'].values[0].find('-')
if str_indx == 2:
date_format = '%y-%b'
elif str_indx == -1:
date_format = '%b%y'
datetime_conversion = [dt.datetime.strptime(x,date_format) for x in fill_frame['Contract']]
fill_frame['ticker_year'] = [x.year for x in datetime_conversion]
fill_frame['ticker_month'] = [x.month for x in datetime_conversion]
fill_frame['ticker_head'] = [conversion_from_tt_ticker_head[x] for x in fill_frame['Product']]
fill_frame['ticker'] = [fill_frame.loc[x,'ticker_head'] +
cmi.full_letter_month_list[fill_frame.loc[x,'ticker_month']-1] +
str(fill_frame.loc[x,'ticker_year']) for x in fill_frame.index]
fill_frame['trade_price'] = [convert_trade_price_from_tt(price=fill_frame.loc[x,'Price'],ticker_head=fill_frame.loc[x,'ticker_head'])
for x in fill_frame.index]
fill_frame['PQ'] = fill_frame['trade_price']*fill_frame['Qty']
grouped = fill_frame.groupby(['ticker','B/S', 'Order Tag'])
aggregate_trades = pd.DataFrame()
aggregate_trades['trade_price'] = grouped['PQ'].sum()/grouped['Qty'].sum()
aggregate_trades['trade_quantity'] = grouped['Qty'].sum()
aggregate_trades.loc[(slice(None),'S'),'trade_quantity']=-aggregate_trades.loc[(slice(None),'S'),'trade_quantity']
aggregate_trades['ticker'] = grouped['ticker'].first()
aggregate_trades['ticker_head'] = grouped['ticker_head'].first()
aggregate_trades['order_tag'] = grouped['Order Tag'].first()
aggregate_trades['instrument'] = [product_type_instrument_conversion[x] for x in grouped['Product Type'].first()]
aggregate_trades['option_type'] = None
aggregate_trades['strike_price'] = None
aggregate_trades['real_tradeQ'] = True
ta_directory = dn.get_dated_directory_extension(ext='ta', folder_date=cu.get_doubledate())
trade_alias_frame = pd.read_csv(ta_directory + '/tradeAlias.csv')
combined_list = [None]*len(trade_alias_frame.index)
for i in range(len(trade_alias_frame.index)):
selected_trades = aggregate_trades[aggregate_trades['order_tag'] == trade_alias_frame['tag'].iloc[i]]
combined_list[i] = selected_trades[['ticker','option_type','strike_price','trade_price','trade_quantity','instrument','real_tradeQ']]
combined_list[i]['alias'] = trade_alias_frame['alias'].iloc[i]
aggregate_trades = pd.concat(combined_list).reset_index(drop=True)
return {'raw_trades': fill_frame, 'aggregate_trades': aggregate_trades}
def test_ib_order_follow_up():
app = ib_order_follow_up()
date_now = cu.get_doubledate()
con = msu.get_my_sql_connection()
delta_strategy_frame = ts.get_filtered_open_strategies(as_of_date=date_now, con=con, strategy_class_list=['delta'])
app.delta_alias = delta_strategy_frame['alias'].iloc[-1]
ta_folder = dn.get_dated_directory_extension(folder_date=date_now, ext='ta')
app.trade_file = ta_folder + '/trade_dir.csv'
app.con = con
app.connect(client_id=7)
app.run()
def generate_underlying_proxy_report(**kwargs):
con = msu.get_my_sql_connection(**kwargs)
report_date = kwargs['report_date']
futures_dataframe = cl.generate_futures_list_dataframe(date_to=report_date)
futures_flat_curve = futures_dataframe[
futures_dataframe['ticker_class'].isin(
['FX', 'Metal', 'Treasury', 'Index'])]
futures_flat_curve.reset_index(drop=True, inplace=True)
futures_flat_curve['tr_days_2roll'] = futures_flat_curve.apply(
lambda x: exp.get_days2_roll(ticker=x['ticker'],
instrument='Futures',
date_to=report_date,
con=con)['tr_days_2roll'],
axis=1)
futures_data_dictionary = {
x: gfp.get_futures_price_preloaded(ticker_head=x)
for x in futures_flat_curve['ticker_head'].unique()
}
proxy_output_list = [
get_underlying_proxy_ticker(
ticker=futures_flat_curve['ticker'].iloc[x],
settle_date=report_date,
con=con,
futures_data_dictionary=futures_data_dictionary)
for x in range(len(futures_flat_curve.index))
]
futures_flat_curve['proxy_ticker'] = [
x['ticker'] for x in proxy_output_list
]
futures_flat_curve['add_2_proxy'] = [
x['add_2_proxy'] for x in proxy_output_list
]
ta_output_dir = dn.get_dated_directory_extension(folder_date=report_date,
ext='ta')
writer = pd.ExcelWriter(ta_output_dir + '/proxy_report.xlsx',
engine='xlsxwriter')
futures_flat_curve = futures_flat_curve[[
'ticker', 'ticker_head', 'ticker_class', 'volume', 'tr_days_2roll',
'proxy_ticker', 'add_2_proxy'
]]
futures_flat_curve.to_excel(writer, sheet_name='proxy_report')
writer.save()
if 'con' not in kwargs.keys():
con.close()
def read_cme_option_settle_csv_files(**kwargs):
file_name = kwargs['file_name']
report_date = kwargs['report_date']
options_data_dir = dn.get_dated_directory_extension(folder_date=report_date, ext='raw_options_data')
with open(options_data_dir + '/' + file_name + '.pkl', 'rb') as handle:
raw_data = pickle.load(handle)
raw_data['ticker_head'] = [convert_from_cme_product_symbol_2tickerhead(x) for x in raw_data['PRODUCT SYMBOL']]
return raw_data
def get_ticker_frame(**kwargs):
date = kwargs['date']
folder_name = dn.get_dated_directory_extension(folder_date=date,
ext='binance')
try:
frame_out = pd.read_pickle(folder_name + '/ticker_frame.pkl')
except:
frame_out = pd.DataFrame(columns=['symbol'])
return frame_out
def read_cme_option_settle_csv_files(**kwargs):
file_name = kwargs['file_name']
report_date = kwargs['report_date']
options_data_dir = dn.get_dated_directory_extension(folder_date=report_date, ext='raw_options_data')
with open(options_data_dir + '/' + file_name + '.pkl', 'rb') as handle:
raw_data = pickle.load(handle)
raw_data['ticker_head'] = [convert_from_cme_product_symbol_2tickerhead(x) for x in raw_data['PRODUCT SYMBOL']]
return raw_data
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 save_ticker_frame(**kwargs):
if 'client' in kwargs.keys():
client = kwargs['client']
else:
client = btu.get_binance_client()
folder_date = cu.get_doubledate()
folder_name = dn.get_dated_directory_extension(folder_date=folder_date,
ext='binance')
tickers = client.get_ticker()
ticker_frame = pd.DataFrame(tickers)
ticker_frame.to_pickle(folder_name + '/ticker_frame.pkl')
def get_binance_price_preloaded(**kwargs):
interval = kwargs['interval']
ticker = kwargs['ticker']
date_from = kwargs['date_from']
date_to = kwargs['date_to']
file_name = ticker + '_1h.pkl'
datetime_from = cu.convert_doubledate_2datetime(date_from)
datetime_to = cu.convert_doubledate_2datetime(date_to)
x = datetime_from
price_frame_list = []
while x <= datetime_to:
folder_name = dn.get_dated_directory_extension(folder_date=int(
x.strftime('%Y%m%d')),
ext='binance')
dated_file_name = folder_name + '/' + file_name
if os.path.isfile(dated_file_name):
price_frame_list.append(pd.read_pickle(dated_file_name))
x = x + dt.timedelta(days=1)
if len(price_frame_list) == 0:
return pd.DataFrame()
merged_data = pd.concat(price_frame_list)
if len(merged_data.index) == 0:
return pd.DataFrame()
merged_data.set_index('openDatetime', drop=True, inplace=True)
if interval.upper() != '1H':
data_out = pd.DataFrame()
data_out['open'] = merged_data['open'].resample('4H').first()
data_out['close'] = merged_data['close'].resample('4H').last()
data_out['high'] = merged_data['high'].resample('4H').max()
data_out['low'] = merged_data['low'].resample('4H').min()
data_out['volume'] = merged_data['volume'].resample('4H').sum()
else:
data_out = merged_data
return data_out
def download_coinbase_data_4date(**kwargs):
utc_date = kwargs['utc_date']
print(utc_date)
candlestick_minutes = kwargs['candlestick_minutes']
ticker = kwargs['ticker']
coinbase_data_dir = dn.get_dated_directory_extension(folder_date=utc_date,
ext='coinbase_data')
file_name = coinbase_data_dir + '/' + ticker + '_' + str(
candlestick_minutes) + '.pkl'
if os.path.isfile(file_name):
return pd.read_pickle(file_name)
if 'coin_client' in kwargs.keys():
coin_client = kwargs['coin_client']
else:
coin_client = coin_util.get_coinbase_client()
start_date = cu.convert_doubledate_2datetime(utc_date)
start_date = start_date.replace(hour=0, minute=0)
end_date = start_date + dt.timedelta(days=1)
end_date = end_date.replace(hour=0, minute=5)
date_raw = coin_client.get_product_historic_rates(
ticker,
granularity=candlestick_minutes * 60,
start=start_date,
end=end_date)
time.sleep(0.5)
frame_out = pd.DataFrame(
date_raw, columns=['time', 'low', 'high', 'open', 'close', 'volume'])
frame_out['time'] = [
dt.datetime.utcfromtimestamp(x).replace(tzinfo=pytz.utc)
for x in frame_out['time']
]
frame_out.sort_values(by='time', ascending=True, inplace=True)
frame_out.reset_index(drop=True, inplace=True)
frame_out.to_pickle(file_name)
return frame_out
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 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 save_price_data(**kwargs):
if 'client' in kwargs.keys():
client = kwargs['client']
else:
client = btu.get_binance_client()
interval = kwargs['interval']
ticker = kwargs['ticker']
date_from = kwargs['date_from']
date_to = kwargs['date_to']
file_name = ticker + '_' + interval + '.pkl'
datetime_from = cu.convert_doubledate_2datetime(date_from)
datetime_to = cu.convert_doubledate_2datetime(date_to)
x = datetime_from
while x <= datetime_to:
xplus = x + dt.timedelta(days=1)
folder_name = dn.get_dated_directory_extension(folder_date=int(
x.strftime('%Y%m%d')),
ext='binance')
dated_file_name = folder_name + '/' + file_name
if os.path.isfile(dated_file_name):
price_frame = pd.read_pickle(dated_file_name)
print(len(price_frame.index))
else:
price_frame = gbp.get_klines(ticker=ticker,
interval=interval,
start_str=x.strftime('%m/%d/%y'),
end_str=xplus.strftime('%m/%d/%y'),
client=client)
price_frame = price_frame[price_frame['openDate'] == x.date()]
price_frame.to_pickle(dated_file_name)
tm.sleep(0.5)
x = xplus
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 main():
app = dh_algo.Algo()
con = msu.get_my_sql_connection()
date_now = cu.get_doubledate()
contract_frame = tsh.get_intraday_data_contract_frame(con=con)
contract_frame = dh_ut.calculate_contract_risk(
contract_frame=contract_frame, current_date=date_now)
contract_frame['bid_p'] = np.nan
contract_frame['ask_p'] = np.nan
contract_frame['mid_price'] = np.nan
contract_frame['spread_cost'] = np.nan
contract_frame['bid_q'] = np.nan
contract_frame['ask_q'] = np.nan
contract_frame_outright = contract_frame[contract_frame['is_spread_q'] ==
False]
outright_ticker_list = list(contract_frame_outright['ticker'].values)
contract_frame_spread = contract_frame[contract_frame['is_spread_q']]
ta_folder = dn.get_dated_directory_extension(folder_date=date_now,
ext='ta')
app.ticker_list = outright_ticker_list
app.price_request_dictionary['spread'] = list(
contract_frame_spread['ticker'].values)
app.price_request_dictionary['outright'] = outright_ticker_list
app.contract_frame = contract_frame
app.log = lg.get_logger(file_identifier='ib_delta_hedge', log_level='INFO')
app.trade_file = ta_folder + '/trade_dir.csv'
app.delta_alias = tsh.get_delta_strategy_alias(con=con)
app.con = con
app.current_date = date_now
app.connect(client_id=6)
app.run()
def get_liquid_outright_futures_frame(**kwargs):
ticker_head_list = list(set(cmi.futures_butterfly_strategy_tickerhead_list) | set(cmi.cme_futures_tickerhead_list))
data_dir = dn.get_dated_directory_extension(ext='intraday_ttapi_data', folder_date=kwargs['settle_date'])
file_name = 'ContractList.csv'
data_frame_out = pd.read_csv(data_dir + '/' + file_name)
data_frame_out_filtered = data_frame_out[data_frame_out['ProductType'] == 'FUTURE']
num_contracts = len(data_frame_out_filtered.index)
reformat_out_list = [tfl.get_ticker_from_tt_instrument_name_and_product_name(instrument_name=data_frame_out_filtered['InstrumentName'].iloc[x],
product_name =data_frame_out_filtered['ProductName'].iloc[x] ) for x in range(num_contracts)]
data_frame_out_filtered['ticker'] = [reformat_out_list[x]['ticker'] for x in range(num_contracts)]
data_frame_out_filtered['ticker_head'] = [reformat_out_list[x]['ticker_head'] for x in range(num_contracts)]
selection_indx = [data_frame_out_filtered['ticker_head'].iloc[x] in ticker_head_list for x in range(num_contracts)]
data_frame_out_filtered2 = data_frame_out_filtered[selection_indx]
data_frame_out_filtered2.sort_values(['ticker_head','Volume'],ascending=[True, False],inplace=True)
return data_frame_out_filtered2.drop_duplicates('ticker_head')
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')
file_name = '/portfolio_pnl_' + kwargs['name'] + '.pkl'
if os.path.isfile(ta_output_dir + file_name):
strategy_frame = pd.read_pickle(ta_output_dir + file_name)
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_values('daily_pnl', ascending=False, inplace=True)
if len(strategy_frame.index) > 0:
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 + file_name)
return strategy_frame
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 = tfl.convert_ticker_from_db2tt(ticker) + '.csv'
#print(file_name)
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 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_presaved_coinbase_data(**kwargs):
date_to = kwargs["date_to"]
num_days_back = kwargs["num_days_back"]
datetime_to = cu.convert_doubledate_2datetime(date_to)
date_list = [
int((datetime_to - dt.timedelta(days=x)).strftime('%Y%m%d'))
for x in range(0, num_days_back)
]
data_list = []
for i in range(len(date_list)):
coinbase_data_dir = dn.get_dated_directory_extension(
folder_date=date_list[i], ext='coinbase_data')
data_list.append(
pd.read_pickle(coinbase_data_dir + '/' + kwargs["ticker"] + '_' +
str(kwargs["candlestick_minutes"]) + '.pkl'))
frame_out = pd.concat(data_list)
frame_out.drop_duplicates(subset=['time'], keep='first', inplace=True)
frame_out.sort_values(by='time', ascending=True, inplace=True)
return frame_out.reset_index(drop=True, inplace=False)
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 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 main():
app = algo.Algo()
report_date = exp.doubledate_shift_bus_days()
todays_date = cu.get_doubledate()
con = msu.get_my_sql_connection()
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'])
vcs_pairs = filter_out['selected_frame']
vcs_pairs = vcs_pairs[vcs_pairs['downside'].notnull()
& vcs_pairs['upside'].notnull()]
# &(vcs_pairs.tickerClass!='Energy')
vcs_pairs = vcs_pairs[(vcs_pairs['trDte1'] >= 50)
& (vcs_pairs.tickerClass != 'Metal') &
(vcs_pairs.tickerClass != 'FX') &
(vcs_pairs.tickerClass != 'Energy')]
vcs_pairs = vcs_pairs[((vcs_pairs['Q'] <= 30) &
(vcs_pairs['fwdVolQ'] >= 30)) |
((vcs_pairs['Q'] >= 70) &
(vcs_pairs['fwdVolQ'] <= 70))]
vcs_pairs.reset_index(drop=True, inplace=True)
vcs_pairs['underlying_ticker1'] = [
omu.get_option_underlying(ticker=x) for x in vcs_pairs['ticker1']
]
vcs_pairs['underlying_ticker2'] = [
omu.get_option_underlying(ticker=x) for x in vcs_pairs['ticker2']
]
vcs_pairs['underlying_tickerhead'] = [
cmi.get_contract_specs(x)['ticker_head']
for x in vcs_pairs['underlying_ticker1']
]
futures_data_dictionary = {
x: gfp.get_futures_price_preloaded(ticker_head=x)
for x in vcs_pairs['underlying_tickerhead'].unique()
}
proxy_output_list1 = [
up.get_underlying_proxy_ticker(
ticker=x,
settle_date=report_date,
futures_data_dictionary=futures_data_dictionary)
for x in vcs_pairs['underlying_ticker1']
]
vcs_pairs['proxy_ticker1'] = [x['ticker'] for x in proxy_output_list1]
vcs_pairs['add_2_proxy1'] = [x['add_2_proxy'] for x in proxy_output_list1]
proxy_output_list2 = [
up.get_underlying_proxy_ticker(
ticker=x,
settle_date=report_date,
futures_data_dictionary=futures_data_dictionary)
for x in vcs_pairs['underlying_ticker2']
]
vcs_pairs['proxy_ticker2'] = [x['ticker'] for x in proxy_output_list2]
vcs_pairs['add_2_proxy2'] = [x['add_2_proxy'] for x in proxy_output_list2]
vcs_pairs['expiration_date1'] = [
int(
exp.get_expiration_from_db(instrument='options', ticker=x,
con=con).strftime('%Y%m%d'))
for x in vcs_pairs['ticker1']
]
vcs_pairs['expiration_date2'] = [
int(
exp.get_expiration_from_db(instrument='options', ticker=x,
con=con).strftime('%Y%m%d'))
for x in vcs_pairs['ticker2']
]
vcs_pairs['interest_date1'] = [
grfs.get_simple_rate(as_of_date=report_date, date_to=x)['rate_output']
for x in vcs_pairs['expiration_date1']
]
vcs_pairs['interest_date2'] = [
grfs.get_simple_rate(as_of_date=report_date, date_to=x)['rate_output']
for x in vcs_pairs['expiration_date2']
]
vcs_pairs['exercise_type'] = [
cmi.get_option_exercise_type(ticker_head=x)
for x in vcs_pairs['tickerHead']
]
admin_dir = dna.get_directory_name(ext='admin')
risk_file_out = su.read_text_file(file_name=admin_dir +
'/RiskParameter.txt')
vcs_risk_parameter = 5 * 2 * float(risk_file_out[0])
vcs_pairs['long_quantity'] = vcs_risk_parameter / abs(
vcs_pairs['downside'])
vcs_pairs['short_quantity'] = vcs_risk_parameter / vcs_pairs['upside']
vcs_pairs['long_quantity'] = vcs_pairs['long_quantity'].round()
vcs_pairs['short_quantity'] = vcs_pairs['short_quantity'].round()
vcs_pairs['alias'] = [
generate_vcs_alias(vcs_row=vcs_pairs.iloc[x])
for x in range(len(vcs_pairs.index))
]
vcs_pairs['call_mid_price1'] = np.nan
vcs_pairs['put_mid_price1'] = np.nan
vcs_pairs['call_mid_price2'] = np.nan
vcs_pairs['put_mid_price2'] = np.nan
vcs_pairs['call_iv1'] = np.nan
vcs_pairs['put_iv1'] = np.nan
vcs_pairs['call_iv2'] = np.nan
vcs_pairs['put_iv2'] = np.nan
vcs_pairs['underlying_mid_price1'] = np.nan
vcs_pairs['underlying_mid_price2'] = np.nan
vcs_pairs['proxy_mid_price1'] = np.nan
vcs_pairs['proxy_mid_price2'] = np.nan
vcs_pairs['current_strike1'] = np.nan
vcs_pairs['current_strike2'] = np.nan
ta_folder = dn.get_dated_directory_extension(folder_date=todays_date,
ext='ta')
app.vcs_pairs = vcs_pairs
app.con = con
app.futures_data_dictionary = futures_data_dictionary
app.report_date = report_date
app.todays_date = todays_date
app.log = lg.get_logger(file_identifier='vcs', log_level='INFO')
app.trade_file = ta_folder + '/trade_dir.csv'
app.vcs_risk_parameter = vcs_risk_parameter
app.connect(client_id=3)
app.run()
def get_book_snapshot(**kwargs):
ticker = kwargs['ticker']
utc_doubledate = kwargs['utc_doubledate']
if 'freq_str' in kwargs.keys():
freq_str = kwargs['freq_str']
else:
freq_str = '5T'
contract_specs_output = cmi.get_contract_specs(ticker)
ticker_head = contract_specs_output['ticker_head']
output_dir = dn.get_dated_directory_extension(ext='book_snapshot_data', folder_date=utc_doubledate)
file_name = output_dir + '/' + ticker + '_' + freq_str + '.pkl'
if os.path.isfile(file_name):
return pd.read_pickle(file_name)
data_out = get_tick_data(**kwargs)
if len(data_out.index)==0:
return pd.DataFrame(columns=['mid_p','buy_volume','sell_volume','volume', 'hour_minute'])
utc_year = m.floor(utc_doubledate / 10000)
if utc_year in [2017, 2018]:
p_multiplier = price_multiplier.get(ticker_head, 1)
else:
p_multiplier = price_multiplier_old.get(ticker_head, 1)
if 'LocalTime' in data_out.columns:
data_out['hour'] = data_out['LocalTime'] / 1e7
data_out['hour'] = data_out['hour'].apply(np.floor)
data_out['minute'] = data_out['LocalTime'] / 1e5
data_out['minute'] = data_out['minute'].apply(np.floor)
data_out['minute'] = data_out['minute'] - 1e2 * data_out['hour']
data_out['second'] = data_out['LocalTime'] / 1e3
data_out['second'] = data_out['second'].apply(np.floor)
data_out['second'] = data_out['second'] - 1e4 * data_out['hour']-1e2*data_out['minute']
data_out['year'] = data_out['LocalDate'] / 1e4
data_out['year'] = data_out['year'].apply(np.floor)
data_out['month'] = data_out['LocalDate'] / 1e2
data_out['month'] = data_out['month'].apply(np.floor)
data_out['month'] = data_out['month'] - 1e2 * data_out['year']
data_out['day'] = data_out['LocalDate'] - 1e4 * data_out['year'] - 1e2 * data_out['month']
data_out['date_time'] = [dt.datetime(int(data_out['year'].iloc[x]), int(data_out['month'].iloc[x]), int(data_out['day'].iloc[x]),
int(data_out['hour'].iloc[x]), int(data_out['minute'].iloc[x]), int(data_out['second'].iloc[x])) for x in range(len(data_out.index))]
else:
data_out['hour_utc'] = data_out['Timestamp'] / 1e7
data_out['hour_utc'] = data_out['hour_utc'].apply(np.floor)
data_out['minute'] = data_out['Timestamp'] / 1e5
data_out['minute'] = data_out['minute'].apply(np.floor)
data_out['minute'] = data_out['minute'] - 1e2 * data_out['hour_utc']
data_out['second'] = data_out['Timestamp'] / 1e3
data_out['second'] = data_out['second'].apply(np.floor)
data_out['second'] = data_out['second'] - 1e4 * data_out['hour_utc'] - 1e2 * data_out['minute']
data_out['year'] = data_out['Date'] / 1e4
data_out['year'] = data_out['year'].apply(np.floor)
data_out['month'] = data_out['Date'] / 1e2
data_out['month'] = data_out['month'].apply(np.floor)
data_out['month'] = data_out['month'] - 1e2 * data_out['year']
data_out['day'] = data_out['Date'] - 1e4 * data_out['year'] - 1e2 * data_out['month']
data_out['date_time'] = [
dt.datetime(int(data_out['year'].iloc[x]), int(data_out['month'].iloc[x]), int(data_out['day'].iloc[x]),
int(data_out['hour_utc'].iloc[x]), int(data_out['minute'].iloc[x]), int(data_out['second'].iloc[x]),
tzinfo=pytz.utc).astimezone(pytz.timezone('US/Central')).replace(tzinfo=None)
for x in range(len(data_out.index))]
merged_index = pd.date_range(start=data_out['date_time'].iloc[0].replace(second=0), end=data_out['date_time'].iloc[-1].replace(second=0), freq='S')
data_out.set_index('date_time', inplace=True, drop=True)
if 'Type' in data_out.columns:
type_column_name = 'Type'
elif 'EventType' in data_out.columns:
type_column_name = 'EventType'
bid_data = data_out[data_out[type_column_name] == 'QUOTE BID']
bid_data = bid_data.groupby(bid_data.index).last()
bid_data = bid_data.reindex(merged_index, method='pad')
ask_data = data_out[data_out[type_column_name] == 'QUOTE SELL']
ask_data = ask_data.groupby(ask_data.index).last()
ask_data = ask_data.reindex(merged_index, method='pad')
buy_trade_data = data_out[data_out[type_column_name] == 'TRADE AGRESSOR ON BUY']
buy_trade_data['CumQuantity'] = buy_trade_data['Quantity'].cumsum()
buy_trade_data = buy_trade_data.groupby(buy_trade_data.index).last()
buy_trade_data = buy_trade_data.reindex(merged_index, method='pad')
buy_trade_data['Quantity'] = buy_trade_data['CumQuantity'].diff()
sell_trade_data = data_out[data_out[type_column_name] == 'TRADE AGRESSOR ON SELL']
sell_trade_data['CumQuantity'] = sell_trade_data['Quantity'].cumsum()
sell_trade_data = sell_trade_data.groupby(sell_trade_data.index).last()
sell_trade_data = sell_trade_data.reindex(merged_index, method='pad')
sell_trade_data['Quantity'] = sell_trade_data['CumQuantity'].diff()
trade_data = data_out[data_out[type_column_name] == 'TRADE']
trade_data['CumQuantity'] = trade_data['Quantity'].cumsum()
trade_data = trade_data.groupby(trade_data.index).last()
trade_data = trade_data.reindex(merged_index, method='pad')
trade_data['Quantity'] = trade_data['CumQuantity'].diff()
book_snapshot = pd.DataFrame(index=merged_index)
book_snapshot['bid_p'] = bid_data['Price']
book_snapshot['bid_p'] = book_snapshot['bid_p']*p_multiplier
book_snapshot['bid_q'] = bid_data['Quantity']
book_snapshot['ask_p'] = ask_data['Price']
book_snapshot['ask_p'] = book_snapshot['ask_p'] * p_multiplier
book_snapshot['ask_q'] = ask_data['Quantity']
book_snapshot['buy_volume'] = buy_trade_data['Quantity']
book_snapshot['sell_volume'] = sell_trade_data['Quantity']
book_snapshot['volume'] = trade_data['Quantity']
book_snapshot['mid_p'] = (book_snapshot['bid_p'] * book_snapshot['bid_q'] + book_snapshot['ask_p'] * book_snapshot['ask_q']) / (
book_snapshot['bid_q'] + book_snapshot['ask_q'])
bar_data = book_snapshot['mid_p'].resample(freq_str).ohlc()
bar_data['buy_volume'] = book_snapshot['buy_volume'].resample(freq_str).sum()
bar_data['sell_volume'] = book_snapshot['sell_volume'].resample(freq_str).sum()
bar_data['volume'] = book_snapshot['volume'].resample(freq_str).sum()
bar_data['hour_minute'] = 100 * bar_data.index.hour + bar_data.index.minute
bar_data.to_pickle(file_name)
return bar_data
def generate_ocs_followup_report(**kwargs):
if 'as_of_date' in kwargs.keys():
as_of_date = kwargs['as_of_date']
else:
as_of_date = exp.doubledate_shift_bus_days()
kwargs['as_of_date'] = as_of_date
broker = kwargs['broker']
ta_output_dir = dn.get_dated_directory_extension(folder_date=as_of_date,
ext='ta')
con = msu.get_my_sql_connection(**kwargs)
if 'writer' in kwargs.keys():
writer = kwargs['writer']
else:
writer = pd.ExcelWriter(ta_output_dir + '/followup.xlsx',
engine='xlsxwriter')
strategy_frame = ts.get_open_strategies(**kwargs)
strategy_class_list = [
sc.convert_from_string_to_dictionary(
string_input=strategy_frame['description_string'][x])
['strategy_class'] for x in range(len(strategy_frame.index))
]
ocs_indx = [x == 'ocs' for x in strategy_class_list]
ocs_frame = strategy_frame[ocs_indx]
if ocs_frame.empty:
writer.save()
return
results = [
sf.get_results_4strategy(alias=ocs_frame['alias'].iloc[x],
strategy_info_output=ocs_frame.iloc[x],
con=con,
broker=broker,
date_to=as_of_date)
for x in range(len(ocs_frame.index))
]
ocs_followup_frame = pd.DataFrame(results)
ocs_followup_frame['alias'] = ocs_frame['alias'].values
kwargs['name'] = 'final'
pnl_frame = pm.get_daily_pnl_snapshot(**kwargs)
merged_frame1 = pd.merge(ocs_followup_frame,
pnl_frame,
how='left',
on='alias')
ocs_followup_frame = merged_frame1[[
'alias', 'dollar_noise', 'time_held', 'daily_pnl', 'total_pnl', 'notes'
]]
ocs_followup_frame.reset_index(drop=True, inplace=True)
ocs_followup_frame.loc[max(ocs_followup_frame.index) + 1] = [
'TOTAL', np.nan, np.nan, ocs_followup_frame['daily_pnl'].sum(),
ocs_followup_frame['total_pnl'].sum(), ''
]
date_from30 = cu.doubledate_shift(as_of_date, 30)
history_frame = ts.select_strategies(close_date_from=date_from30,
close_date_to=as_of_date,
con=con)
strategy_class_list = [
sc.convert_from_string_to_dictionary(
string_input=history_frame['description_string'][x])
['strategy_class'] for x in range(len(history_frame.index))
]
ocs_indx = [x == 'ocs' for x in strategy_class_list]
ocs_history_frame = history_frame[ocs_indx]
pnl_past_month = ocs_history_frame['pnl'].sum()
as_of_datetime = cu.convert_doubledate_2datetime(as_of_date)
date_from7 = as_of_datetime + dt.timedelta(days=-7)
ocs_short_history_frame = ocs_history_frame[
ocs_history_frame['close_date'] >= date_from7]
pnl_past_week = ocs_short_history_frame['pnl'].sum()
ocs_followup_frame.loc[max(ocs_followup_frame.index) + 1] = [
'WEEKLY PERFORMANCE', np.nan, np.nan, np.nan, pnl_past_week, ''
]
ocs_followup_frame.loc[max(ocs_followup_frame.index) + 1] = [
'MONTHLY PERFORMANCE', np.nan, np.nan, np.nan, pnl_past_month, ''
]
ocs_followup_frame['total_pnl'] = ocs_followup_frame['total_pnl'].astype(
int)
ocs_followup_frame.to_excel(writer, sheet_name='ocs')
worksheet_ocs = writer.sheets['ocs']
worksheet_ocs.freeze_panes(1, 0)
worksheet_ocs.set_column('B:B', 26)
worksheet_ocs.autofilter(0, 0, len(ocs_followup_frame.index),
len(ocs_followup_frame.columns))
if 'con' not in kwargs.keys():
con.close()
writer.save()
def get_simple_rate(**kwargs):
as_of_date = kwargs["as_of_date"]
date_to = kwargs["date_to"]
if "date_from" in kwargs.keys():
date_from = kwargs["date_from"]
else:
date_from = as_of_date
if "ticker_head" in kwargs.keys():
ticker_head = kwargs["ticker_head"]
else:
ticker_head = "ED"
ta_output_dir = dn.get_dated_directory_extension(folder_date=as_of_date, ext="ta")
file_name = ta_output_dir + "/" + ticker_head + "_interest_curve.pkl"
# print('as_of_date: ' + str(as_of_date) + ', date_to: ' + str(date_to))
if os.path.isfile(file_name):
price_frame = pd.read_pickle(file_name)
if (not os.path.isfile(file_name)) or price_frame.empty:
price_frame = gfp.get_futures_price_preloaded(ticker_head=ticker_head, settle_date=as_of_date)
price_frame = price_frame[price_frame["close_price"].notnull()]
price_frame.sort("tr_dte", ascending=True, inplace=True)
price_frame["exp_date"] = [exp.get_futures_expiration(x) for x in price_frame["ticker"]]
price_frame["implied_rate"] = 100 - price_frame["close_price"]
price_frame.to_pickle(file_name)
if price_frame.empty:
return {
"rate_output": np.NaN,
"price_frame": pd.DataFrame(columns=["ticker", "cal_dte", "exp_date", "implied_rate"]),
}
datetime_to = cu.convert_doubledate_2datetime(date_to)
datetime_from = cu.convert_doubledate_2datetime(date_from)
price_frame_first = price_frame[price_frame["exp_date"] <= datetime_from]
price_frame_middle = price_frame[
(price_frame["exp_date"] > datetime_from) & (price_frame["exp_date"] < datetime_to)
]
if price_frame_middle.empty:
if not price_frame_first.empty:
rate_output = price_frame_first["implied_rate"].iloc[-1] / 100
else:
rate_output = price_frame["implied_rate"].iloc[0] / 100
return {
"rate_output": rate_output,
"price_frame": price_frame[["ticker", "cal_dte", "exp_date", "implied_rate"]],
}
if price_frame_first.empty:
first_rate = price_frame_middle["implied_rate"].iloc[0]
first_period = (price_frame_middle["exp_date"].iloc[0].to_datetime() - datetime_from).days
else:
first_rate = price_frame_first["implied_rate"].iloc[-1]
first_period = (price_frame_middle["exp_date"].iloc[0].to_datetime() - datetime_from).days
last_rate = price_frame_middle["implied_rate"].iloc[-1]
last_period = (datetime_to - price_frame_middle["exp_date"].iloc[-1].to_datetime()).days
middle_discount = [
1
+ (
price_frame_middle["implied_rate"].iloc[x]
* (price_frame_middle["cal_dte"].iloc[x + 1] - price_frame_middle["cal_dte"].iloc[x])
/ 36500
)
for x in range(len(price_frame_middle.index) - 1)
]
total_discount = (
np.prod(np.array(middle_discount))
* (1 + (first_rate * first_period / 36500))
* (1 + (last_rate * last_period / 36500))
)
total_period = (
(price_frame_middle["cal_dte"].iloc[-1] - price_frame_middle["cal_dte"].iloc[0]) + first_period + last_period
)
rate_output = (total_discount - 1) * 365 / total_period
return {"rate_output": rate_output, "price_frame": price_frame[["ticker", "cal_dte", "exp_date", "implied_rate"]]}
def get_historical_risk_4open_strategies(**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')
if os.path.isfile(ta_output_dir + '/portfolio_risk.pkl'):
with open(ta_output_dir + '/portfolio_risk.pkl','rb') as handle:
portfolio_risk_output = pickle.load(handle)
return portfolio_risk_output
con = msu.get_my_sql_connection(**kwargs)
strategy_frame = ts.get_open_strategies(**kwargs)
futures_data_dictionary = {x: gfp.get_futures_price_preloaded(ticker_head=x) for x in cmi.ticker_class.keys()}
strategy_risk_frame = pd.DataFrame()
historical_risk_output = [get_historical_risk_4strategy(alias=x,
as_of_date=as_of_date,
con=con,
futures_data_dictionary=futures_data_dictionary)
for x in strategy_frame['alias']]
if 'con' not in kwargs.keys():
con.close()
strategy_risk_frame['alias'] = strategy_frame['alias']
strategy_risk_frame['downside'] = [x['downside'] for x in historical_risk_output]
strategy_risk_frame.sort('downside', ascending=True, inplace=True)
ticker_head_list = su.flatten_list([list(x['ticker_head_based_pnl_5_change'].keys()) for x in historical_risk_output if x['downside'] != 0])
unique_ticker_head_list = list(set(ticker_head_list))
ticker_head_aggregated_pnl_5_change = {ticker_head: sum([x['ticker_head_based_pnl_5_change'][ticker_head] for x in historical_risk_output
if x['downside'] != 0 and ticker_head in x['ticker_head_based_pnl_5_change'].keys()]) for ticker_head in unique_ticker_head_list}
percentile_vector = [stats.get_number_from_quantile(y=ticker_head_aggregated_pnl_5_change[ticker_head],
quantile_list=[1, 15],
clean_num_obs=max(100, round(3*len(ticker_head_aggregated_pnl_5_change[ticker_head].values)/4)))
for ticker_head in unique_ticker_head_list]
ticker_head_risk_frame = pd.DataFrame()
ticker_head_risk_frame['tickerHead'] = unique_ticker_head_list
ticker_head_risk_frame['downside'] = [(x[0]+x[1])/2 for x in percentile_vector]
ticker_head_risk_frame.sort('downside', ascending=True, inplace=True)
strategy_risk_frame['downside'] = strategy_risk_frame['downside'].round()
ticker_head_risk_frame['downside'] = ticker_head_risk_frame['downside'].round()
portfolio_risk_output = {'strategy_risk_frame': strategy_risk_frame,
'ticker_head_aggregated_pnl_5_change': ticker_head_aggregated_pnl_5_change,
'ticker_head_risk_frame':ticker_head_risk_frame}
with open(ta_output_dir + '/portfolio_risk.pkl', 'wb') as handle:
pickle.dump(portfolio_risk_output, handle)
return portfolio_risk_output
def read_cme_settle_txt_files(**kwargs):
file_name = kwargs['file_name']
report_date = kwargs['report_date']
options_data_dir = dn.get_dated_directory_extension(folder_date=report_date, ext='raw_options_data')
if os.path.isfile(options_data_dir + '/' + file_name + '_formatted.pkl'):
output_dictionary = pd.read_pickle(options_data_dir + '/' + file_name + '_formatted.pkl')
return output_dictionary
with open(options_data_dir + '/' + file_name + '.pkl', 'rb') as handle:
raw_data = pickle.load(handle)
title_list = []
data_start_list = []
data_end_list = []
decoded_data = [x.decode('UTF-8') for x in raw_data]
volume_start_indx = decoded_data[2].find('EST.VOL')-1
interest_indx = decoded_data[2].find('INT')-5
month_strike_indx = decoded_data[2].find('STRIKE')
settle_indx = decoded_data[2].find('SETT')-4
open_indx = decoded_data[2].find('OPEN')-4
high_indx = decoded_data[2].find('HIGH')-4
low_indx = decoded_data[2].find('LOW')-5
for indx in range(len(decoded_data)):
if any(x in decoded_data[indx] for x in ['OPTIONS', 'OPTION', 'Options', 'Option', 'FUTURE', 'Future',
'CSO', 'AIRASIA', 'SYNTH', 'BURSA', 'SPRD', 'Spread',
'CROSSRATE', 'WEEKLY', 'CALL', 'PUT','UTILITIES','CONSUMER','SP 500']):
title_list.append(decoded_data[indx])
if len(data_start_list) == 0:
data_start_list.append(indx+1)
else:
data_start_list.append(indx+1)
data_end_list.append(indx)
if 'END OF REPORT' in decoded_data[indx]:
data_end_list.append(indx)
data_list = [decoded_data[data_start_list[x]:data_end_list[x]] for x in range(len(data_start_list))]
volume_list = []
volume_filtered_list = []
interest_list = []
interest_filtered_list = []
settle_list = []
open_list = []
high_list = []
low_list = []
month_strike_list = []
filter_1_list = []
filter_2_list = []
total_volume_list = []
for i in range(len(data_list)):
volume_column = [x[volume_start_indx:(volume_start_indx+10)] for x in data_list[i]]
interest_column = [x[interest_indx:(interest_indx+10)] for x in data_list[i]]
settle_column = [x[settle_indx:(settle_indx+9)] for x in data_list[i]]
open_column = [x[open_indx:(open_indx+9)] for x in data_list[i]]
high_column = [x[high_indx:(high_indx+9)] for x in data_list[i]]
low_column = [x[low_indx:(low_indx+9)] for x in data_list[i]]
month_strike_column = [x[month_strike_indx:(month_strike_indx+5)] for x in data_list[i]]
month_strike_column_filtered = [x.strip() for x in month_strike_column if x != 'TOTAL']
settle_column_filtered = [settle_column[x].strip() for x in range(len(settle_column)) if month_strike_column[x] != 'TOTAL']
volume_column_filtered = [volume_column[x].strip() for x in range(len(volume_column)) if month_strike_column[x] != 'TOTAL']
interest_column_filtered = [interest_column[x].strip() for x in range(len(interest_column)) if month_strike_column[x] != 'TOTAL']
open_column_filtered = [open_column[x].strip() for x in range(len(open_column)) if month_strike_column[x] != 'TOTAL']
high_column_filtered = [high_column[x].strip() for x in range(len(high_column)) if month_strike_column[x] != 'TOTAL']
low_column_filtered = [low_column[x].strip() for x in range(len(low_column)) if month_strike_column[x] != 'TOTAL']
filter_1 = ['TOTAL' not in x for x in month_strike_column]
filter_1_list.append(filter_1)
filter_2 = [bool((volume_column[x]).strip()) if filter_1[x] else False for x in range(len(volume_column))]
filter_2_list.append(filter_2)
volume_list.append(volume_column)
volume_filtered_list.append(volume_column_filtered)
interest_list.append(interest_column)
interest_filtered_list.append(interest_column_filtered)
settle_list.append(settle_column_filtered)
open_list.append(open_column_filtered)
high_list.append(high_column_filtered)
low_list.append(low_column_filtered)
month_strike_list.append(month_strike_column_filtered)
total_volume_list.append((sum([int(volume_column[x]) if filter_2[x] else 0 for x in range(len(volume_column))])))
title_frame = pd.DataFrame([process_title(x) for x in title_list])
output_dictionary = {'decoded_data': decoded_data,
'data_start_list': data_start_list,
'data_end_list': data_end_list,
'title_list': title_list,
'data_list': data_list,
'volume_list': volume_list,
'volume_filtered_list': volume_filtered_list,
'interest_list': interest_list,
'interest_filtered_list': interest_filtered_list,
'settle_list': settle_list,
'open_list': open_list,
'high_list': high_list,
'low_list': low_list,
'month_strike_list': month_strike_list,
'filter_1_list': filter_1_list,
'filter_2_list': filter_2_list,
'total_volume_list': total_volume_list,
'title_frame': title_frame}
with open(options_data_dir + '/' + file_name + '_formatted.pkl', 'wb') as handle:
pickle.dump(output_dictionary, handle)
return output_dictionary
import get_price.save_stock_data as ssd
import my_sql_routines.options_pnl_loader as opnl
commodity_address = 'ftp://ftp.cmegroup.com/pub/settle/stlags'
equity_address = 'ftp://ftp.cmegroup.com/pub/settle/stleqt'
fx_address = 'ftp://ftp.cmegroup.com/pub/settle/stlcur'
interest_rate_address = 'ftp://ftp.cmegroup.com/pub/settle/stlint'
comex_futures_csv_address = 'ftp://ftp.cmegroup.com/pub/settle/comex_future.csv'
comex_options_csv_address = 'ftp://ftp.cmegroup.com/pub/settle/comex_option.csv'
nymex_futures_csv_address = 'ftp://ftp.cmegroup.com/pub/settle/nymex_future.csv'
nymex_options_csv_address = 'ftp://ftp.cmegroup.com/pub/settle/nymex_option.csv'
#folder_date = cu.get_doubledate()
folder_date = exp.doubledate_shift_bus_days()
options_data_dir = dn.get_dated_directory_extension(folder_date=folder_date,
ext='raw_options_data')
commodity_output = sd.download_txt_from_web(web_address=commodity_address)
equity_output = sd.download_txt_from_web(web_address=equity_address)
fx_output = sd.download_txt_from_web(web_address=fx_address)
interest_rate_output = sd.download_txt_from_web(
web_address=interest_rate_address)
comex_futures_output = sd.download_csv_from_web(
web_address=comex_futures_csv_address)
comex_options_output = sd.download_csv_from_web(
web_address=comex_options_csv_address)
nymex_futures_output = sd.download_csv_from_web(
web_address=nymex_futures_csv_address)
nymex_options_output = sd.download_csv_from_web(
import formats.options_strategy_formats as osf
import formats.intraday_futures_strategy_formats as ifsf
import ta.prepare_daily as prep
commodity_address = 'ftp://ftp.cmegroup.com/pub/settle/stlags'
equity_address = 'ftp://ftp.cmegroup.com/pub/settle/stleqt'
fx_address = 'ftp://ftp.cmegroup.com/pub/settle/stlcur'
interest_rate_address = 'ftp://ftp.cmegroup.com/pub/settle/stlint'
comex_futures_csv_address = 'ftp://ftp.cmegroup.com/pub/settle/comex_future.csv'
comex_options_csv_address = 'ftp://ftp.cmegroup.com/pub/settle/comex_option.csv'
nymex_futures_csv_address = 'ftp://ftp.cmegroup.com/pub/settle/nymex_future.csv'
nymex_options_csv_address = 'ftp://ftp.cmegroup.com/pub/settle/nymex_option.csv'
folder_date = cu.get_doubledate()
options_data_dir = dn.get_dated_directory_extension(folder_date=folder_date, ext='raw_options_data')
commodity_output = sd.download_txt_from_web(web_address=commodity_address)
equity_output = sd.download_txt_from_web(web_address=equity_address)
fx_output = sd.download_txt_from_web(web_address=fx_address)
interest_rate_output = sd.download_txt_from_web(web_address=interest_rate_address)
comex_futures_output = sd.download_csv_from_web(web_address=comex_futures_csv_address)
comex_options_output = sd.download_csv_from_web(web_address=comex_options_csv_address)
nymex_futures_output = sd.download_csv_from_web(web_address=nymex_futures_csv_address)
nymex_options_output = sd.download_csv_from_web(web_address=nymex_options_csv_address)
with open(options_data_dir + '/commodity.pkl', 'wb') as handle:
pickle.dump(commodity_output, handle)
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,
name='final')
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_values('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 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 read_cme_settle_txt_files(**kwargs):
file_name = kwargs['file_name']
report_date = kwargs['report_date']
options_data_dir = dn.get_dated_directory_extension(folder_date=report_date, ext='raw_options_data')
if os.path.isfile(options_data_dir + '/' + file_name + '_formatted.pkl'):
output_dictionary = pd.read_pickle(options_data_dir + '/' + file_name + '_formatted.pkl')
return output_dictionary
with open(options_data_dir + '/' + file_name + '.pkl', 'rb') as handle:
raw_data = pickle.load(handle)
title_list = []
data_start_list = []
data_end_list = []
decoded_data = [x.decode('UTF-8') for x in raw_data]
volume_start_indx = decoded_data[2].find('EST.VOL')-1
interest_indx = decoded_data[2].find('INT')-5
month_strike_indx = decoded_data[2].find('STRIKE')
settle_indx = decoded_data[2].find('SETT')-4
open_indx = decoded_data[2].find('OPEN')-4
high_indx = decoded_data[2].find('HIGH')-4
low_indx = decoded_data[2].find('LOW')-5
for indx in range(len(decoded_data)):
if any(x in decoded_data[indx] for x in ['OPTIONS', 'OPTION', 'Options', 'Option', 'FUTURE', 'Future',
'CSO', 'AIRASIA', 'SYNTH', 'BURSA', 'SPRD', 'Spread',
'CROSSRATE', 'WEEKLY', 'CALL', 'PUT','UTILITIES','CONSUMER','SP 500','sector','SP500',
'NQ', 'S&P','Black','Sea','WHEAT','Wheat','Financially','Settled']):
title_list.append(decoded_data[indx])
if len(data_start_list) == 0:
data_start_list.append(indx+1)
else:
data_start_list.append(indx+1)
data_end_list.append(indx)
if 'END OF REPORT' in decoded_data[indx]:
data_end_list.append(indx)
data_list = [decoded_data[data_start_list[x]:data_end_list[x]] for x in range(len(data_start_list))]
volume_list = []
volume_filtered_list = []
interest_list = []
interest_filtered_list = []
settle_list = []
open_list = []
high_list = []
low_list = []
month_strike_list = []
filter_1_list = []
filter_2_list = []
total_volume_list = []
for i in range(len(data_list)):
volume_column = [x[volume_start_indx:(volume_start_indx+10)] for x in data_list[i]]
interest_column = [x[interest_indx:(interest_indx+10)] for x in data_list[i]]
settle_column = [x[settle_indx:(settle_indx+9)] for x in data_list[i]]
open_column = [x[open_indx:(open_indx+9)] for x in data_list[i]]
high_column = [x[high_indx:(high_indx+9)] for x in data_list[i]]
low_column = [x[low_indx:(low_indx+9)] for x in data_list[i]]
month_strike_column = [x[month_strike_indx:(month_strike_indx+5)] for x in data_list[i]]
month_strike_column_filtered = [x.strip() for x in month_strike_column if x != 'TOTAL']
settle_column_filtered = [settle_column[x].strip() for x in range(len(settle_column)) if month_strike_column[x] != 'TOTAL']
volume_column_filtered = [volume_column[x].strip() for x in range(len(volume_column)) if month_strike_column[x] != 'TOTAL']
interest_column_filtered = [interest_column[x].strip() for x in range(len(interest_column)) if month_strike_column[x] != 'TOTAL']
open_column_filtered = [open_column[x].strip() for x in range(len(open_column)) if month_strike_column[x] != 'TOTAL']
high_column_filtered = [high_column[x].strip() for x in range(len(high_column)) if month_strike_column[x] != 'TOTAL']
low_column_filtered = [low_column[x].strip() for x in range(len(low_column)) if month_strike_column[x] != 'TOTAL']
filter_1 = ['TOTAL' not in x for x in month_strike_column]
filter_1_list.append(filter_1)
filter_2 = [bool((volume_column[x]).strip()) if filter_1[x] else False for x in range(len(volume_column))]
filter_2_list.append(filter_2)
volume_list.append(volume_column)
volume_filtered_list.append(volume_column_filtered)
interest_list.append(interest_column)
interest_filtered_list.append(interest_column_filtered)
settle_list.append(settle_column_filtered)
open_list.append(open_column_filtered)
high_list.append(high_column_filtered)
low_list.append(low_column_filtered)
month_strike_list.append(month_strike_column_filtered)
total_volume_list.append((sum([int(volume_column[x]) if filter_2[x] else 0 for x in range(len(volume_column))])))
#return title_list
title_frame = pd.DataFrame([process_title(x) for x in title_list])
output_dictionary = {'decoded_data': decoded_data,
'data_start_list': data_start_list,
'data_end_list': data_end_list,
'title_list': title_list,
'data_list': data_list,
'volume_list': volume_list,
'volume_filtered_list': volume_filtered_list,
'interest_list': interest_list,
'interest_filtered_list': interest_filtered_list,
'settle_list': settle_list,
'open_list': open_list,
'high_list': high_list,
'low_list': low_list,
'month_strike_list': month_strike_list,
'filter_1_list': filter_1_list,
'filter_2_list': filter_2_list,
'total_volume_list': total_volume_list,
'title_frame': title_frame}
with open(options_data_dir + '/' + file_name + '_formatted.pkl', 'wb') as handle:
pickle.dump(output_dictionary, handle)
return output_dictionary
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]
if len(vcs_frame.index) == 0:
return writer
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
kwargs['name'] = 'final'
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_values('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()
return writer
