def trading_day(self, date):
"""
Return trading day idx from trading_day_idx
:param symbols: expiry date in YYYY-MM-DD format
:return: trading day idx from trading_day_idx
"""
trading_day_list = list(self.trading_day_idx.keys())
trading_day_list.sort()
last_trading_day = trading_day_list[len(trading_day_list) - 1]
if date > last_trading_day: # If passed expiry date is beyond last available bar
weekdays_till_date = dates.dates(
last_trading_day, date,
['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday'])
return self.trading_day_idx[last_trading_day] + len(
weekdays_till_date) - 1
else:
if date in self.trading_day_idx: # If passed expiry date is a trading day
return self.trading_day_idx[date]
else: # If passed expiry date is not a trading day, use previous trading day
found = False
save_date = date
while not found:
date = dates.relativedate(date, days=-1)
if date in self.trading_day_idx:
return self.trading_day_idx[date]
found = True
if dates.datediff(save_date, date) > 10:
return None
def continuous_contracts_all(delta=None):
"""
Create continuous contracts file for near and far series
:param delta: List of Contract switch day differences from expiry day
:return: None, Create continuous contracts file
"""
if delta is None:
delta = [0]
if not os.path.isfile(EXPIRIES):
write_expiries()
expiry_dates = read_expiries(EXPIRIES)
print(expiry_dates)
utils.mkdir(CONTINUOUS)
csv_files = [f for f in os.listdir(os.curdir) if f.endswith('.csv')]
print('Initiating continuous contract creation for {} days'.format(
len(csv_files)))
#near_exp, far_exp = {}, {} # '1900-01-01', '1900-01-01' # Initialize
exp = [{}]
success, error = 0, 0
for file in csv_files:
try:
date = file[0:10]
df = pd.read_csv(file)
date_pd = pd.DataFrame()
for symbol in df['Symbol'].unique():
if symbol not in exp[0]:
for d in delta:
if d > 0:
exp.append({})
exp[d][symbol] = '1900-01-01' # Initialize
series = []
for d in delta:
if exp[d][symbol] <= dates.relativedate(date, days=d):
exp[d][symbol] = select_expiry(expiry_dates, date,
symbol, d, 0)
series.append(df.loc[(df['Symbol'] == symbol)
& (df['Expiry'] == exp[d][symbol])])
series[d]['Symbol'] = series[d]['Symbol'] + '-' + 'I' * d
date_pd = pd.concat([date_pd, series[d]], axis=0)
print('###')
date_pd.to_csv('{}{}'.format(CONTINUOUS, file),
sep=',',
index=False)
print(date, ',Continuous contract created', file)
success += 1
except:
print(date, ',Error creating Continuous contract', file)
error += 1
print('Contract created for {} days, {} errors'.format(success, error))
def continuous_contracts(delta=0):
"""
Create continuous contracts file for near and far series
:param delta: Contract switch day difference from expiry day
:return: None, Create continuous contracts file
"""
if not os.path.isfile(EXPIRIES):
write_expiries()
expiry_dates = read_expiries(EXPIRIES)
print(expiry_dates)
utils.mkdir(CONTINUOUS)
csv_files = [f for f in os.listdir(os.curdir) if f.endswith('.csv')]
print('Initiating continuous contract creation for {} days'.format(
len(csv_files)))
near_exp, far_exp = {}, {} # '1900-01-01', '1900-01-01' # Initialize
success, error = 0, 0
for file in csv_files:
try:
date = file[0:10]
df = pd.read_csv(file)
date_pd = pd.DataFrame()
for symbol in df['Symbol'].unique():
if symbol not in near_exp:
near_exp[symbol], far_exp[
symbol] = '1900-01-01', '1900-01-01' # Initialize
if near_exp[symbol] <= dates.relativedate(date, days=delta):
near_exp[symbol] = select_expiry(expiry_dates, date,
symbol, delta, 0)
far_exp[symbol] = select_expiry(expiry_dates, date, symbol,
delta, 1)
series1 = df.loc[(df['Symbol'] == symbol)
& (df['Expiry'] == near_exp[symbol])]
series2 = df.loc[(df['Symbol'] == symbol)
& (df['Expiry'] == far_exp[symbol])]
series1['Symbol'], series2['Symbol'] = series1[
'Symbol'] + '-I', series2['Symbol'] + '-II'
if date_pd.empty:
date_pd = pd.concat([series1, series2], axis=0)
else:
date_pd = pd.concat([date_pd, series1, series2], axis=0)
date_pd.to_csv('{}{}'.format(CONTINUOUS, file),
sep=',',
index=False)
print(date, ',Continuous contract created', file)
success += 1
except:
print(date, ',Error creating Continuous contract', file)
error += 1
print('Contract created for {} days, {} errors'.format(success, error))
def select_expiry(expiry_dates, date, symbol, delta, series=0):
expiry_index = 0
for expiry in expiry_dates[symbol]:
if expiry > dates.relativedate(date, days=delta):
print('select_expiry', symbol, date, delta, series,
expiry_dates[symbol][expiry_index + series])
return expiry_dates[symbol][expiry_index + series]
expiry_index += 1
def select_far_expiry(expiry_dates, date, symbol, delta):
for expiry in expiry_dates[symbol]:
if int(date[8:10]) < delta:
month_delta = 1
else:
month_delta = 2
if expiry > dates.relativedate(date, months=month_delta):
#print('select_far_expiry', symbol, date, delta, expiry)
return expiry
def select_near_expiry(expiry_dates, date, symbol, delta):
for expiry in expiry_dates[symbol]:
if expiry > dates.relativedate(date, days=delta):
# print('select_near_expiry', symbol, date, delta, expiry)
return expiry
def manage_missed_records(self, symbols=[], delta=0):
'''
Identify records missed while creating continuous contracts and insert them
:param symbols: [list of symbols], no need to pass anything if for all symbols
:return:
'''
print('start manage missed records')
if len(symbols) == 0: # no symbol passed, default to all symbols
symbols = self.unique_symbols()
# Identify symbol-date combinations which were available in tblDump but not included in tblFutures
qry = '''SELECT tblDump.Symbol, tblDump.Date, tblDump.ExpiryDate, tblDump.VolumeLots
FROM tblDump LEFT OUTER JOIN tblFutures
ON tblDump.Symbol = tblFutures.Symbol
AND tblDump.Date = tblFutures.Date
WHERE tblFutures.Date is NULL
AND tblDump.InstrumentName = "{}"
ORDER BY tblDump.Symbol ASC, tblDump.ExpiryDate ASC, tblDump.Date ASC'''.format(
self.INSTRUMENT_NAME)
missed_records = pd.read_sql_query(qry, self.conn)
select_missed_records = missed_records if len(symbols) == 0 \
else missed_records[missed_records.Symbol.isin(symbols)]
try:
os.remove(self.SELECTED_RECORDS_FILE)
os.remove(self.ELIGIBLE_RECORDS_FILE)
except OSError:
pass
symbols_latest_date, symbols_latest_exp = dict(), dict()
c = self.conn.cursor()
selected_records, eligible_records = pd.DataFrame(), pd.DataFrame()
# Loop through symbols
for symbol in select_missed_records['Symbol'].unique():
symbol_expiry_qry = '''SELECT ExpiryDate FROM tblExpiries WHERE Symbol = "{}"
ORDER BY ExpiryDate ASC'''.format(symbol)
symbol_expiries = pd.read_sql_query(symbol_expiry_qry, self.conn)
symbols_latest_date[symbol] = '1900-01-01'
symbols_latest_exp[symbol] = symbol_expiries['ExpiryDate'].tolist(
)[0]
symbol_missed_records = select_missed_records[
select_missed_records.Symbol == symbol]
# Loop through expiry_dates for the symbol
for expiry_date in symbol_missed_records['ExpiryDate'].unique():
symbol_missed_records_for_expiry = \
symbol_missed_records[symbol_missed_records.ExpiryDate == expiry_date]
if len(symbol_missed_records_for_expiry.index) == 0:
print('{}: skipping expiry date {}, symbol latest date {}'.
format(symbol, expiry_date,
symbols_latest_date[symbol]))
continue
# Sort all dates selected
all_dates = symbol_missed_records_for_expiry['Date'].unique()
all_dates.sort()
prev_next_dates = self.prev_and_next_dates(
c, symbol, expiry_date, symbols_latest_date[symbol],
symbols_latest_exp[symbol], all_dates[0],
all_dates[len(all_dates) - 1])
prev_date, prev_exp, next_date, next_exp = prev_next_dates['prev_date'], prev_next_dates['prev_exp'],\
prev_next_dates['next_date'], prev_next_dates['next_exp']
if next_exp < expiry_date or prev_exp > expiry_date:
print(
'{}: skipping expiry date {}, prev exp {} next exp {}'.
format(symbol, expiry_date, prev_exp, next_exp))
continue
else:
print(
'{}: checking expiry date {}, prev exp {} next exp {}'.
format(symbol, expiry_date, prev_exp, next_exp))
prev_date_plus_1, next_date_minus_1 = dates.relativedate(prev_date, days=1), \
dates.relativedate(next_date, days=-1)
# Select records for compare and deletion if needed
selected_records_qry = '''SELECT * FROM tblFutures
WHERE Symbol = "{}"
AND Date BETWEEN "{}" AND "{}"'''.format(
symbol, prev_date_plus_1, next_date_minus_1)
selected_records_temp = pd.read_sql_query(
selected_records_qry, self.conn)
# Select eligible records
eligible_records_qry = '''SELECT Symbol, Date, Open, High, Low, Close, VolumeLots, OpenInterestLots, ExpiryDate
FROM tblDump
WHERE Symbol = "{}"
AND Date BETWEEN "{}" AND "{}"
AND ExpiryDate = "{}"'''.format(
symbol, prev_date_plus_1, next_date_minus_1, expiry_date)
eligible_records_temp = pd.read_sql_query(
eligible_records_qry, self.conn)
# Loop through missing dates
for date in all_dates:
prev_next_dates2 = self.prev_and_next_dates(
c, symbol, expiry_date, symbols_latest_date[symbol],
symbols_latest_exp[symbol], date, date)
next_exp = prev_next_dates2['next_exp']
next_symbol_expiries = [
d for d in symbol_expiries['ExpiryDate'].tolist()
if d >= next_exp
]
# Find expiry after the next to make sure records from too fare away are not identified as eligible
if len(next_symbol_expiries) >= 2:
next_next_expiry = next_symbol_expiries[1]
else:
if len(next_symbol_expiries) == 0:
next_next_expiry = expiry_date
else:
next_next_expiry = next_symbol_expiries[
len(next_symbol_expiries) - 1]
eligible_records_temp2 = eligible_records_temp[
(eligible_records_temp.Date >= date) &
(eligible_records_temp.ExpiryDate <= next_next_expiry)
& (eligible_records_temp.Date >
symbols_latest_date[symbol])]
if eligible_records_temp2.empty:
print(date, 'eligible_records empty, skipping')
continue
start, end = eligible_records_temp2.iloc[0]['ExpiryDate'], \
eligible_records_temp2.iloc[len(eligible_records_temp2.index) - 1]['ExpiryDate']
prev_next_dates3 = self.prev_and_next_dates(
c, symbol, expiry_date, symbols_latest_date[symbol],
symbols_latest_exp[symbol], start, end)
prev_exp, next_exp = prev_next_dates3[
'prev_exp'], prev_next_dates3['next_exp']
if next_exp < expiry_date or prev_exp > expiry_date:
print(date, 'next expiry is out of place, skipping',
expiry_date, prev_exp, next_exp)
continue
selected_records_temp2 = selected_records_temp[
(selected_records_temp.Date >= date)
& (selected_records_temp.Date >
symbols_latest_date[symbol])]
if len(eligible_records_temp2.index) > len(
selected_records_temp2.index):
selected_records = pd.concat(
[selected_records, selected_records_temp2], axis=0)
eligible_records = pd.concat(
[eligible_records, eligible_records_temp2], axis=0)
symbols_latest_date[symbol] = \
eligible_records_temp2.iloc[len(eligible_records_temp2.index) - 1]['Date']
symbols_latest_exp[symbol] = \
eligible_records_temp2.iloc[len(eligible_records_temp2.index) - 1]['ExpiryDate']
print(
'eligible selected records range',
eligible_records_temp2.iloc[0]['Date'],
eligible_records_temp2.iloc[
len(eligible_records_temp2.index) - 1]['Date'],
len(eligible_records_temp2.index))
if len(selected_records_temp2.index) > 0:
print(
'replacing records range',
selected_records_temp2.iloc[0]['Date'],
selected_records_temp2.iloc[
len(selected_records_temp2.index) -
1]['Date'],
len(selected_records_temp2.index))
else:
print('replacing records range', 'NA',
len(selected_records_temp2.index))
break
selected_records.to_csv(self.SELECTED_RECORDS_FILE,
sep=',',
index=False)
eligible_records.to_csv(self.ELIGIBLE_RECORDS_FILE,
sep=',',
index=False)
c.close()
