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 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()
