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