def get_option_contract_list_4year_range(**kwargs): now = datetime.datetime.utcnow() start_year = kwargs["start_year"] end_year = kwargs["end_year"] tickerhead_list = cmi.cme_option_tickerhead_list contract_name_dict = cmi.contract_name ticker_class_dict = cmi.ticker_class year_list = range(start_year, end_year) ticker_list = [] for i in tickerhead_list: contract_months = cmi.get_option_contract_months(ticker_head=i) for j in contract_months: for k in year_list: ticker = i + j + str(k) print(ticker) ticker_list.append( ( ticker, i, k, cmi.letter_month_string.find(j) + 1, exp.get_options_expiration(ticker), "options", contract_name_dict[i], ticker_class_dict[i], "USD", now, now, ) ) return ticker_list
def get_option_contract_list_4year_range(**kwargs): now = datetime.datetime.utcnow() start_year = kwargs['start_year'] end_year = kwargs['end_year'] tickerhead_list = cmi.cme_option_tickerhead_list contract_name_dict = cmi.contract_name ticker_class_dict = cmi.ticker_class year_list = range(start_year,end_year) ticker_list = [] for i in tickerhead_list: contract_months = cmi.get_option_contract_months(ticker_head=i) for j in contract_months: for k in year_list: ticker = i+j+str(k) print(ticker) ticker_list.append((ticker,i,k,cmi.letter_month_string.find(j)+1,exp.get_options_expiration(ticker), 'options',contract_name_dict[i],ticker_class_dict[i],'USD',now,now)) return ticker_list
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_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()