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 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 move_position_from_strategy_2_strategy(**kwargs):
strategy_from = kwargs['strategy_from']
strategy_to = kwargs['strategy_to']
now_time = dt.datetime.now()
con = msu.get_my_sql_connection(**kwargs)
if 'con' not in kwargs.keys():
kwargs['con'] = con
if 'as_of_date' in kwargs.keys():
as_of_date = kwargs['as_of_date']
else:
as_of_date = exp.doubledate_shift_bus_days()
kwargs['as_of_date'] = as_of_date
if 'pricing_date' in kwargs.keys():
pricing_date = kwargs['pricing_date']
else:
pricing_date = as_of_date
net_position_frame = get_net_position_4strategy_alias(alias=strategy_from,
**kwargs)
target_strategy_id = get_strategy_id_from_alias(alias=strategy_to,
**kwargs)
source_strategy_id = get_strategy_id_from_alias(alias=strategy_from,
**kwargs)
futures_position_frame = net_position_frame[
net_position_frame['instrument'] == 'F']
options_position_frame = net_position_frame[
net_position_frame['instrument'] == 'O']
futures_position_frame['trade_price'] = \
[float(gfp.get_futures_price_4ticker(ticker=x,date_from=pricing_date,date_to=pricing_date,con=con)['close_price'][0]) for x in futures_position_frame['ticker']]
if not options_position_frame.empty:
options_position_frame['trade_price'] = options_position_frame.apply(
lambda row: gop.get_options_price_from_db(
ticker=row['ticker'],
strike=row['strike_price'],
option_type=row['option_type'],
con=con,
return_nan_if_emptyQ=True,
settle_date=pricing_date)['close_price'][0],
axis=1)
net_position_frame = pd.concat(
[futures_position_frame, options_position_frame])
else:
net_position_frame = futures_position_frame
column_names = net_position_frame.columns.tolist()
ticker_indx = column_names.index('ticker')
option_type_indx = column_names.index('option_type')
strike_price_indx = column_names.index('strike_price')
trade_price_indx = column_names.index('trade_price')
trade_quantity_indx = column_names.index('qty')
instrument_indx = column_names.index('instrument')
column_str = "ticker, option_type, strike_price, strategy_id, trade_price, trade_quantity, trade_date, instrument, real_tradeQ, created_date, last_updated_date"
insert_str = ("%s, " * 11)[:-2]
final_str = "INSERT INTO trades (%s) VALUES (%s)" % (column_str,
insert_str)
tuples_target = [
tuple([
x[ticker_indx], x[option_type_indx],
None if np.isnan(x[strike_price_indx]) else x[strike_price_indx],
target_strategy_id, x[trade_price_indx], x[trade_quantity_indx],
as_of_date, x[instrument_indx], True, now_time, now_time
]) for x in net_position_frame.values
]
msu.sql_execute_many_wrapper(final_str=final_str,
tuples=tuples_target,
con=con)
tuples_source = [
tuple([
x[ticker_indx], x[option_type_indx],
None if np.isnan(x[strike_price_indx]) else x[strike_price_indx],
source_strategy_id, x[trade_price_indx], -x[trade_quantity_indx],
as_of_date, x[instrument_indx], True, now_time, now_time
]) for x in net_position_frame.values
]
msu.sql_execute_many_wrapper(final_str=final_str,
tuples=tuples_source,
con=con)
if 'con' not in kwargs.keys():
con.close()
def move_position_from_strategy_2_strategy(**kwargs):
strategy_from = kwargs['strategy_from']
strategy_to = kwargs['strategy_to']
now_time = dt.datetime.now()
con = msu.get_my_sql_connection(**kwargs)
if 'con' not in kwargs.keys():
kwargs['con'] = con
if 'as_of_date' in kwargs.keys():
as_of_date = kwargs['as_of_date']
else:
as_of_date = exp.doubledate_shift_bus_days()
kwargs['as_of_date'] = as_of_date
net_position_frame = get_net_position_4strategy_alias(alias=strategy_from, **kwargs)
target_strategy_id = get_strategy_id_from_alias(alias=strategy_to, **kwargs)
source_strategy_id = get_strategy_id_from_alias(alias=strategy_from, **kwargs)
futures_position_frame = net_position_frame[net_position_frame['instrument'] == 'F']
options_position_frame = net_position_frame[net_position_frame['instrument'] == 'O']
futures_position_frame['trade_price'] = \
[float(gfp.get_futures_price_4ticker(ticker=x,date_from=as_of_date,date_to=as_of_date,con=con)['close_price'][0]) for x in futures_position_frame['ticker']]
if not options_position_frame.empty:
options_position_frame['trade_price'] = options_position_frame.apply(lambda row: gop.get_options_price_from_db(ticker=row['ticker'],
strike=row['strike_price'],
option_type=row['option_type'],con=con,
return_nan_if_emptyQ=True,
settle_date=as_of_date)['close_price'][0], axis=1)
net_position_frame = pd.concat([futures_position_frame,options_position_frame])
else:
net_position_frame = futures_position_frame
column_names = net_position_frame.columns.tolist()
ticker_indx = column_names.index('ticker')
option_type_indx = column_names.index('option_type')
strike_price_indx = column_names.index('strike_price')
trade_price_indx = column_names.index('trade_price')
trade_quantity_indx = column_names.index('qty')
instrument_indx = column_names.index('instrument')
column_str = "ticker, option_type, strike_price, strategy_id, trade_price, trade_quantity, trade_date, instrument, real_tradeQ, created_date, last_updated_date"
insert_str = ("%s, " * 11)[:-2]
final_str = "INSERT INTO trades (%s) VALUES (%s)" % (column_str, insert_str)
tuples_target = [tuple([x[ticker_indx],x[option_type_indx],
None if np.isnan(x[strike_price_indx]) else x[strike_price_indx], target_strategy_id,
x[trade_price_indx], x[trade_quantity_indx],
as_of_date,x[instrument_indx], True,now_time,now_time]) for x in net_position_frame.values]
msu.sql_execute_many_wrapper(final_str=final_str, tuples=tuples_target, con=con)
tuples_source = [tuple([x[ticker_indx],x[option_type_indx],
None if np.isnan(x[strike_price_indx]) else x[strike_price_indx], source_strategy_id,
x[trade_price_indx], -x[trade_quantity_indx],
as_of_date,x[instrument_indx], True,now_time,now_time]) for x in net_position_frame.values]
msu.sql_execute_many_wrapper(final_str=final_str, tuples=tuples_source, con=con)
if 'con' not in kwargs.keys():
con.close()
def update_options_price_database_from_cme_files_4ticker(**kwargs):
ticker = kwargs['ticker']
contract_specs_output = cmi.get_contract_specs(ticker)
ticker_head = contract_specs_output['ticker_head']
ticker_month_num = contract_specs_output['ticker_month_num']
ticker_year = contract_specs_output['ticker_year']
if 'settle_date' in kwargs.keys():
settle_date = kwargs['settle_date']
kwargs['report_date'] = settle_date
else:
settle_date = int(time.strftime('%Y%m%d'))
kwargs['settle_date'] = settle_date
kwargs['report_date'] = settle_date
if not exp.is_business_day(double_date=settle_date,
reference_tickerhead=ticker_head):
return
if 'expiration_date' in kwargs.keys():
expiration_date = kwargs['expiration_date']
else:
expiration_date = exp.get_options_expiration(ticker)
expiration_date = expiration_date.date()
settle_datetime = cu.convert_doubledate_2datetime(settle_date)
if 'cal_dte' in kwargs.keys():
cal_dte = kwargs['cal_dte']
else:
cal_dte = (expiration_date - settle_datetime.date()).days
if 'tr_dte' in kwargs.keys():
tr_dte = kwargs['tr_dte']
else:
bday_us = CustomBusinessDay(
calendar=exp.get_calendar_4ticker_head(ticker_head))
dts = pd.date_range(start=settle_datetime,
end=expiration_date,
freq=bday_us)
tr_dte = len(
[x for x in dts if x.to_pydatetime().date() < expiration_date])
data_vendor_id = 2
now = dt.datetime.now()
con = msu.get_my_sql_connection(**kwargs)
process_output = pco.process_cme_options_4ticker(**kwargs)
if process_output['success']:
settle_frame = process_output['settle_frame']
else:
if 'con' not in kwargs.keys():
con.close()
return
column_names = settle_frame.columns.tolist()
option_type_indx = column_names.index('option_type')
strike_indx = column_names.index('strike')
settle_indx = column_names.index('settle')
volume_indx = column_names.index('volume')
interest_indx = column_names.index('interest')
tuples = [
tuple([
data_vendor_id, ticker_head, ticker_month_num, ticker_year, ticker,
x[option_type_indx], x[strike_indx],
settle_datetime.date(), cal_dte, tr_dte, now, now,
None if np.isnan(x[settle_indx]) else x[settle_indx],
None if np.isnan(x[volume_indx]) else x[volume_indx],
None if np.isnan(x[interest_indx]) else x[interest_indx]
]) for x in settle_frame.values
]
column_str = "data_vendor_id, ticker_head, ticker_month, ticker_year, ticker, " \
" option_type, strike, price_date, cal_dte, tr_dte, " \
" created_date,last_updated_date, close_price, volume, open_interest"
insert_str = ("%s, " * len(column_str.split(',')))[:-2]
final_str = "REPLACE INTO daily_option_price (%s) VALUES (%s)" % (
column_str, insert_str)
msu.sql_execute_many_wrapper(final_str=final_str, tuples=tuples, con=con)
if 'con' not in kwargs.keys():
con.close()
def update_futures_price_database_from_cme_file(**kwargs):
ticker_head_list = cmi.cme_futures_tickerhead_list
import time
con = msu.get_my_sql_connection(**kwargs)
if 'settle_date' in kwargs.keys():
run_date = kwargs['settle_date']
else:
run_date = int(time.strftime('%Y%m%d'))
#run_date = 20160225
data_vendor_id = 2
now = datetime.datetime.now()
run_datetime = cu.convert_doubledate_2datetime(run_date)
for ticker_head in ticker_head_list:
#print(ticker_head)
contract_list = []
bday_us = CustomBusinessDay(calendar=exp.get_calendar_4ticker_head(ticker_head))
if not exp.is_business_day(double_date=run_date, reference_tickerhead=ticker_head):
continue
cme_output = pcf.process_cme_futures_4tickerhead(ticker_head=ticker_head, report_date=run_date)
settle_frame = cme_output['settle_frame']
for ticker_month in cmi.futures_contract_months[ticker_head]:
ticker_month_num = cmi.letter_month_string.find(ticker_month)+1
max_cal_dte = cmi.get_max_cal_dte(ticker_head=ticker_head, ticker_month=ticker_month_num)
contract_list.extend(cl.get_db_contract_list_filtered(expiration_date_from=run_date,
expiration_date_to=cu.doubledate_shift(run_date, -max_cal_dte),
ticker_head=ticker_head, ticker_month=ticker_month_num, con=con,
instrument='futures'))
contract_frame = pd.DataFrame(contract_list, columns=['symbol_id', 'ticker', 'expiration_date'])
merged_frame = pd.merge(contract_frame,settle_frame, how='inner', on='ticker')
merged_frame.sort('expiration_date', ascending=True, inplace=True)
column_names = merged_frame.columns.tolist()
symbol_id_indx = column_names.index('symbol_id')
ticker_month_indx = column_names.index('ticker_month')
open_indx = column_names.index('open')
high_indx = column_names.index('high')
low_indx = column_names.index('low')
settle_indx = column_names.index('settle')
volume_indx = column_names.index('volume')
interest_indx = column_names.index('interest')
expiration_indx = column_names.index('expiration_date')
dts = pd.date_range(start=run_datetime, end=merged_frame['expiration_date'].iloc[-1], freq=bday_us)
tuples = [tuple([data_vendor_id, x[symbol_id_indx],
ticker_head,
x[ticker_month_indx],
run_datetime.date(),
(x[expiration_indx]-run_datetime.date()).days,
len([y for y in dts if y.to_datetime().date() < x[expiration_indx]]),
now, now,
None if np.isnan(x[open_indx]) else x[open_indx],
None if np.isnan(x[high_indx]) else x[high_indx],
None if np.isnan(x[low_indx]) else x[low_indx],
None if np.isnan(x[settle_indx]) else x[settle_indx],
None if np.isnan(x[volume_indx]) else x[volume_indx],
None if np.isnan(x[interest_indx]) else x[interest_indx]]) for x in merged_frame.values]
column_str = "data_vendor_id, symbol_id, ticker_head, ticker_month, price_date,cal_dte, tr_dte, created_date,last_updated_date, open_price, high_price, low_price, close_price, volume, open_interest"
insert_str = ("%s, " * 15)[:-2]
final_str = "REPLACE INTO daily_price (%s) VALUES (%s)" % (column_str, insert_str)
msu.sql_execute_many_wrapper(final_str=final_str, tuples=tuples, con=con)
if 'con' not in kwargs.keys():
con.close()
def load_price_data_4ticker(load_price_data_input):
ticker = load_price_data_input['ticker']
expiration_date = load_price_data_input['expiration_date']
data_vendor_id = load_price_data_input['data_vendor_id']
symbol_id = load_price_data_input['symbol_id']
quandl_input = {'ticker': ticker}
if 'date_to' in load_price_data_input.keys():
quandl_input['date_to'] = load_price_data_input['date_to']
if 'date_from' in load_price_data_input.keys() and load_price_data_input['date_from'] is not None:
quandl_input['date_from'] = load_price_data_input['date_from']
quandl_out = gdq.get_daily_historic_data_quandl(**quandl_input)
if not quandl_out['success']:
return
price_data = quandl_out['data_out']
if price_data.empty:
print('Empty Results For ' + ticker)
return
contract_specs_output = cmi.get_contract_specs(ticker)
if contract_specs_output['ticker_head'] == 'JY':
price_multiplier = 10
else:
price_multiplier = 1
bday_us = CustomBusinessDay(calendar=exp.get_calendar_4ticker_head(contract_specs_output['ticker_head']))
dts = pd.date_range(start=price_data.index[0], end=expiration_date, freq=bday_us)
dts = [x.date() for x in dts]
now = datetime.datetime.now()
price_data['price_date'] = pd.Series(price_data.index, index=price_data.index)
column_names = price_data.columns.tolist()
open_indx = column_names.index('Open')
high_indx = column_names.index('High')
low_indx = column_names.index('Low')
settle_indx = column_names.index('Settle')
volume_indx = column_names.index('Volume')
interest_indx = column_names.index('Open Interest')
date_indx = column_names.index('price_date')
tuples = [tuple([data_vendor_id, symbol_id,
contract_specs_output['ticker_head'],
contract_specs_output['ticker_month_num'],
x[date_indx].to_datetime().date(),
(expiration_date-x[date_indx].to_datetime().date()).days,
len([y for y in dts if y > x[date_indx].to_datetime().date()]),
now, now,
None if np.isnan(x[open_indx]) else price_multiplier*x[open_indx],
None if np.isnan(x[high_indx]) else price_multiplier*x[high_indx],
None if np.isnan(x[low_indx]) else price_multiplier*x[low_indx],
None if np.isnan(x[settle_indx]) else price_multiplier*x[settle_indx],
None if np.isnan(x[volume_indx]) else x[volume_indx],
None if np.isnan(x[interest_indx]) else x[interest_indx]]) for x in price_data.values]
column_str = "data_vendor_id, symbol_id, ticker_head, ticker_month, price_date,cal_dte, tr_dte, created_date,last_updated_date, open_price, high_price, low_price, close_price, volume, open_interest"
insert_str = ("%s, " * 15)[:-2]
final_str = "REPLACE INTO daily_price (%s) VALUES (%s)" % (column_str, insert_str)
con = msu.get_my_sql_connection(**load_price_data_input)
msu.sql_execute_many_wrapper(final_str=final_str, tuples=tuples, con=con)
if 'con' not in load_price_data_input.keys():
con.close()
def load_pnls_4settle_date(**kwargs):
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
con = kwargs['con']
if not exp.is_business_day(double_date=kwargs['settle_date']):
if close_connection_before_exit:
con.close()
return
settle_date_ex = exp.doubledate_shift_bus_days(
double_date=kwargs['settle_date'], shift_in_days=7)
indicator_frame = ops.get_option_ticker_indicators(
settle_date=settle_date_ex,
delta=0.5,
column_names=['id', 'ticker', 'price_date', 'tr_dte', 'ticker_head'])
if indicator_frame.empty:
if close_connection_before_exit:
con.close()
return
indicator_frame.reset_index(drop=True, inplace=True)
delta_vol_output = [
ops.calc_delta_vol_4ticker(ticker=x,
settle_date=settle_date_ex,
delta_target=0.5)
for x in indicator_frame['ticker']
]
delta_vol_output = pd.DataFrame(delta_vol_output)
indicator_frame['option_pnl5'] = delta_vol_output['option_pnl5']
indicator_frame['delta_pnl5'] = delta_vol_output['delta_pnl5']
indicator_frame.dropna(inplace=True)
column_names = indicator_frame.columns.tolist()
#indicator_frame['option_pnl5'] = indicator_frame['option_pnl5'].astype(object)
#indicator_frame['delta_pnl5'] = indicator_frame['delta_pnl5'].astype(object)
id_indx = column_names.index('id')
option_pnl_indx = column_names.index('option_pnl5')
delta_pnl_indx = column_names.index('delta_pnl5')
tuples = [
tuple([x[option_pnl_indx], x[delta_pnl_indx], x[id_indx]])
for x in indicator_frame.values
]
final_str = "UPDATE option_ticker_indicators SET option_pnl5 = %s, delta_pnl5 = %s WHERE id=%s"
msu.sql_execute_many_wrapper(final_str=final_str, tuples=tuples, con=con)
if close_connection_before_exit:
con.close()
def load_ticker_signals_4settle_date(**kwargs):
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
con = kwargs['con']
if not exp.is_business_day(double_date=kwargs['settle_date']):
if close_connection_before_exit:
con.close()
return
options_frame = cl.generate_liquid_options_list_dataframe(**kwargs)
futures_data_dictionary = {
x: gfp.get_futures_price_preloaded(ticker_head=x)
for x in cmi.get_tickerhead_list('cme_futures')
}
real_vol_output = [
ops.calc_realized_vol_4options_ticker(
ticker=x,
futures_data_dictionary=futures_data_dictionary,
settle_date=kwargs['settle_date']) for x in options_frame['ticker']
]
delta_vol_output = [
ops.calc_delta_vol_4ticker(ticker=x,
settle_date=kwargs['settle_date'],
delta_target=0.5)
for x in options_frame['ticker']
]
volume_interest_output = [
ops.calc_volume_interest_4ticker(ticker=x,
settle_date=kwargs['settle_date'])
for x in options_frame['ticker']
]
atm_vol_frame = pd.concat([
options_frame,
pd.DataFrame(delta_vol_output),
pd.DataFrame(volume_interest_output)
],
axis=1)
atm_vol_frame['real_vol'] = real_vol_output
contract_specs_output = [
cmi.get_contract_specs(x) for x in atm_vol_frame['ticker']
]
atm_vol_frame['ticker_head'] = [
x['ticker_head'] for x in contract_specs_output
]
atm_vol_frame['ticker_month'] = [
x['ticker_month_num'] for x in contract_specs_output
]
atm_vol_frame['ticker_year'] = [
x['ticker_year'] for x in contract_specs_output
]
column_names = atm_vol_frame.columns.tolist()
ticker_indx = column_names.index('ticker')
cal_dte_indx = column_names.index('cal_dte')
tr_dte_indx = column_names.index('tr_dte')
ticker_head_indx = column_names.index('ticker_head')
ticker_month_indx = column_names.index('ticker_month')
ticker_year_indx = column_names.index('ticker_year')
imp_vol_indx = column_names.index('delta_vol')
strike_indx = column_names.index('strike')
theta_indx = column_names.index('theta')
open_interest_indx = column_names.index('open_interest')
volume_indx = column_names.index('volume')
real_vol_indx = column_names.index('real_vol')
now = dt.datetime.now()
settle_datetime = cu.convert_doubledate_2datetime(kwargs['settle_date'])
tuples = [
tuple([
x[ticker_indx], x[ticker_head_indx], x[ticker_month_indx],
x[ticker_year_indx],
None if np.isnan(x[cal_dte_indx]) else x[cal_dte_indx],
None if np.isnan(x[tr_dte_indx]) else x[tr_dte_indx],
None if np.isnan(x[imp_vol_indx]) else 100 * x[imp_vol_indx], 0.5,
None if np.isnan(x[theta_indx]) else x[theta_indx],
None if np.isnan(x[strike_indx]) else x[strike_indx],
None if np.isnan(x[real_vol_indx]) else x[real_vol_indx],
None if np.isnan(x[open_interest_indx]) else x[open_interest_indx],
None if np.isnan(x[volume_indx]) else x[volume_indx],
settle_datetime.date(), now, now
]) for x in atm_vol_frame.values
]
column_str = "ticker, ticker_head, ticker_month, ticker_year, " \
" cal_dte, tr_dte, imp_vol, delta, theta, strike, " \
"close2close_vol20, open_interest, volume, price_date, created_date, last_updated_date"
insert_str = ("%s, " * len(column_str.split(',')))[:-2]
final_str = "REPLACE INTO option_ticker_indicators (%s) VALUES (%s)" % (
column_str, insert_str)
msu.sql_execute_many_wrapper(final_str=final_str, tuples=tuples, con=con)
load_pnls_4settle_date(**kwargs)
if close_connection_before_exit:
con.close()
def update_options_greek_database_4ticker(**kwargs):
if 'con' not in kwargs.keys():
con = msu.get_my_sql_connection(**kwargs)
kwargs['con'] = con
close_connection_before_exit = True
else:
close_connection_before_exit = False
con = kwargs['con']
ticker = kwargs['ticker']
print(ticker)
option_greeks = osc.cal_greeks_4option_maturity(**kwargs)
if option_greeks.empty:
return
column_names = option_greeks.columns.tolist()
option_type_indx = column_names.index('option_type')
strike_indx = column_names.index('strike')
settle_indx = column_names.index('close_price')
volume_indx = column_names.index('volume')
interest_indx = column_names.index('open_interest')
delta_indx = column_names.index('delta')
gamma_indx = column_names.index('gamma')
implied_vol_indx = column_names.index('implied_vol')
theta_indx = column_names.index('theta')
vega_indx = column_names.index('vega')
cal_dte = int(option_greeks['cal_dte'].iloc[0])
tr_dte = int(option_greeks['tr_dte'].iloc[0])
data_vendor_id = 2
now = dt.datetime.now()
settle_datetime = cu.convert_doubledate_2datetime(kwargs['settle_date'])
contract_specs_output = cmi.get_contract_specs(ticker)
x = option_greeks.values[0]
option_greeks['close_price'] = option_greeks['close_price'].astype(
'float64')
option_greeks['volume'] = option_greeks['volume'].astype('int_')
option_greeks['open_interest'] = option_greeks['open_interest'].astype(
'int_')
tuples = [
tuple([
data_vendor_id, contract_specs_output['ticker_head'],
contract_specs_output['ticker_month_num'],
contract_specs_output['ticker_year'], ticker, x[option_type_indx],
x[strike_indx],
settle_datetime.date(), cal_dte, tr_dte, now, now,
None if np.isnan(x[settle_indx]) else x[settle_indx],
None if np.isnan(x[implied_vol_indx]) else x[implied_vol_indx],
None if np.isnan(x[delta_indx]) else x[delta_indx],
None if np.isnan(x[gamma_indx]) else x[gamma_indx],
None if np.isnan(x[theta_indx]) else x[theta_indx],
None if np.isnan(x[vega_indx]) else x[vega_indx],
None if np.isnan(x[volume_indx]) else int(x[volume_indx]),
None if np.isnan(x[interest_indx]) else int(x[interest_indx])
]) for x in option_greeks.values
]
column_str = "data_vendor_id, ticker_head, ticker_month, ticker_year, ticker, " \
"option_type, strike, price_date, cal_dte, tr_dte, " \
"created_date,last_updated_date, close_price, imp_vol, delta, gamma, theta, vega, " \
"volume, open_interest"
insert_str = ("%s, " * len(column_str.split(',')))[:-2]
final_str = "REPLACE INTO daily_option_price (%s) VALUES (%s)" % (
column_str, insert_str)
msu.sql_execute_many_wrapper(final_str=final_str, tuples=tuples, con=con)
if close_connection_before_exit:
con.close()
def update_options_price_database_from_cme_files_4ticker(**kwargs):
ticker = kwargs['ticker']
contract_specs_output = cmi.get_contract_specs(ticker)
ticker_head = contract_specs_output['ticker_head']
ticker_month_num = contract_specs_output['ticker_month_num']
ticker_year = contract_specs_output['ticker_year']
if 'settle_date' in kwargs.keys():
settle_date = kwargs['settle_date']
kwargs['report_date'] = settle_date
else:
settle_date = int(time.strftime('%Y%m%d'))
kwargs['settle_date'] = settle_date
kwargs['report_date'] = settle_date
if not exp.is_business_day(double_date=settle_date, reference_tickerhead=ticker_head):
return
if 'expiration_date' in kwargs.keys():
expiration_date = kwargs['expiration_date']
else:
expiration_date = exp.get_options_expiration(ticker)
expiration_date = expiration_date.date()
settle_datetime = cu.convert_doubledate_2datetime(settle_date)
if 'cal_dte' in kwargs.keys():
cal_dte = kwargs['cal_dte']
else:
cal_dte = (expiration_date-settle_datetime.date()).days
if 'tr_dte' in kwargs.keys():
tr_dte = kwargs['tr_dte']
else:
bday_us = CustomBusinessDay(calendar=exp.get_calendar_4ticker_head(ticker_head))
dts = pd.date_range(start=settle_datetime, end=expiration_date, freq=bday_us)
tr_dte = len([x for x in dts if x.to_datetime().date() < expiration_date])
data_vendor_id = 2
now = dt.datetime.now()
con = msu.get_my_sql_connection(**kwargs)
process_output = pco.process_cme_options_4ticker(**kwargs)
if process_output['success']:
settle_frame = process_output['settle_frame']
else:
if 'con' not in kwargs.keys():
con.close()
return
column_names = settle_frame.columns.tolist()
option_type_indx = column_names.index('option_type')
strike_indx = column_names.index('strike')
settle_indx = column_names.index('settle')
volume_indx = column_names.index('volume')
interest_indx = column_names.index('interest')
tuples = [tuple([data_vendor_id, ticker_head, ticker_month_num, ticker_year,
ticker, x[option_type_indx],x[strike_indx],settle_datetime.date(),
cal_dte, tr_dte, now, now,
None if np.isnan(x[settle_indx]) else x[settle_indx],
None if np.isnan(x[volume_indx]) else x[volume_indx],
None if np.isnan(x[interest_indx]) else x[interest_indx]]) for x in settle_frame.values]
column_str = "data_vendor_id, ticker_head, ticker_month, ticker_year, ticker, " \
" option_type, strike, price_date, cal_dte, tr_dte, " \
" created_date,last_updated_date, close_price, volume, open_interest"
insert_str = ("%s, " * len(column_str.split(',')))[:-2]
final_str = "REPLACE INTO daily_option_price (%s) VALUES (%s)" % (column_str, insert_str)
msu.sql_execute_many_wrapper(final_str=final_str, tuples=tuples, con=con)
if 'con' not in kwargs.keys():
con.close()
def update_options_greek_database_4ticker(**kwargs):
if 'con' not in kwargs.keys():
con = msu.get_my_sql_connection(**kwargs)
kwargs['con'] = con
close_connection_before_exit = True
else:
close_connection_before_exit = False
con = kwargs['con']
ticker = kwargs['ticker']
#print(ticker)
option_greeks = osc.cal_greeks_4option_maturity(**kwargs)
if option_greeks.empty:
return
column_names = option_greeks.columns.tolist()
option_type_indx = column_names.index('option_type')
strike_indx = column_names.index('strike')
settle_indx = column_names.index('close_price')
volume_indx = column_names.index('volume')
interest_indx = column_names.index('open_interest')
delta_indx = column_names.index('delta')
gamma_indx = column_names.index('gamma')
implied_vol_indx = column_names.index('implied_vol')
theta_indx = column_names.index('theta')
vega_indx = column_names.index('vega')
cal_dte = int(option_greeks['cal_dte'].iloc[0])
tr_dte = int(option_greeks['tr_dte'].iloc[0])
data_vendor_id = 2
now = dt.datetime.now()
settle_datetime = cu.convert_doubledate_2datetime(kwargs['settle_date'])
contract_specs_output = cmi.get_contract_specs(ticker)
x = option_greeks.values[0]
option_greeks['close_price'] = option_greeks['close_price'].astype('float64')
option_greeks['volume'] = option_greeks['volume'].astype('int_')
option_greeks['open_interest'] = option_greeks['open_interest'].astype('int_')
tuples = [tuple([data_vendor_id,
contract_specs_output['ticker_head'],
contract_specs_output['ticker_month_num'],
contract_specs_output['ticker_year'],
ticker, x[option_type_indx], x[strike_indx],settle_datetime.date(),
cal_dte, tr_dte, now, now,
None if np.isnan(x[settle_indx]) else x[settle_indx],
None if np.isnan(x[implied_vol_indx]) else x[implied_vol_indx],
None if np.isnan(x[delta_indx]) else x[delta_indx],
None if np.isnan(x[gamma_indx]) else x[gamma_indx],
None if np.isnan(x[theta_indx]) else x[theta_indx],
None if np.isnan(x[vega_indx]) else x[vega_indx],
None if np.isnan(x[volume_indx]) else int(x[volume_indx]),
None if np.isnan(x[interest_indx]) else int(x[interest_indx])]) for x in option_greeks.values]
column_str = "data_vendor_id, ticker_head, ticker_month, ticker_year, ticker, " \
"option_type, strike, price_date, cal_dte, tr_dte, " \
"created_date,last_updated_date, close_price, imp_vol, delta, gamma, theta, vega, " \
"volume, open_interest"
insert_str = ("%s, " * len(column_str.split(',')))[:-2]
final_str = "REPLACE INTO daily_option_price (%s) VALUES (%s)" % (column_str, insert_str)
msu.sql_execute_many_wrapper(final_str=final_str, tuples=tuples, con=con)
if close_connection_before_exit:
con.close()
