def _get_curr_to_usd_data(x, close_only=True):
if x in _curr_to_usd_ticks.keys():
t1 = cr_sfx_hs.retrieve(_curr_to_usd_ticks[x])
elif x in _curr_to_usd_ticks_inv.keys():
t1 = cr_sfx_hs.retrieve(_curr_to_usd_ticks_inv[x])
for i in ['Open', 'High', 'Low', 'Close']:
t1[i] = 1/t1[i]
else:
raise NotImplementedError('Currency file information not added to curr_to_usd_ticks')
if close_only:
t1 = t1[['Date', 'Close']]
# for certain currencies extend the history
if x in ['EUR', 'GBP', 'JPY', 'CHF', 'HKD', 'AUD', 'NZD', 'SEK', 'SGD', 'ZAR', 'CAD']:
t1h = cr_sfx_morehs_df.retrieve('USD'+x)
if close_only:
t1h = t1h[['Date', 'Close']]
t1m = DataFrame.merge(t1, t1h, on='Date')
t1m['Date'] = t1m['Date'].astype('int64')
t1m.sort('Date')
t1m['Close'] = np.nan
tidx = np.where(~np.isnan(t1m['Close_x'].values))[0]
t1m[tidx, 'Close'] = t1m[tidx, 'Close_x']
tidx = np.where(np.isnan(t1m['Close_x'].values))[0]
t1m[tidx, 'Close'] = t1m[tidx, 'Close_y']
t1 = t1m[['Date', 'Close']]
else:
t1m = DataFrame.merge(t1, t1h, on='Date')
t1m['Date'] = t1m['Date'].astype('int64')
t1m.sort('Date')
for i in ['Open', 'High', 'Low', 'Close']:
t1m[i] = np.nan
tidx = np.where(~np.isnan(t1m['Close_x'].values))[0]
for i in ['Open', 'High', 'Low', 'Close']:
t1m[tidx, i] = t1m[tidx, i+'_x']
tidx = np.where(np.isnan(t1m['Close_x'].values))[0]
for i in ['Open', 'High', 'Low', 'Close']:
t1m[tidx, i] = t1m[tidx, i+'_y']
t1 = t1m[['Date', 'Open', 'High', 'Low', 'Close']]
# fix_errors 20160712 for SEK
if x == 'SEK':
_bad_pos = np.where(t1['Date'].values == 20160712)[0]
if close_only:
t1[_bad_pos, 'Close'] = t1[_bad_pos, 'Close']/10
else:
for i in ['Open', 'High', 'Low', 'Close']:
t1[_bad_pos, i] = t1[_bad_pos, i]/10
return t1
def get_vol_combo():
_eqidx = get_all_equity_index_names()
for k in range(0, len(vol_names)):
# k = 0
for j in vol_lookbacks:
# j = vol_lookbacks[0]
for i in _eqidx:
# i = 'SP500'
_data_ = mkt_retrieve(i, 'Stats', 'Volatility')[['Date', vol_names[k]+str(j)]]
_data_.set_columns(['Date', i])
if i == _eqidx[0]:
_data = _data_
else:
_data = DataFrame.merge(_data, _data_, on='Date')
_data['Date'] = _data['Date'].values.astype('int64')
_data.sort(['Date'])
tcl = _data.tick_cols()
for i in tcl:
_data[i] = filt.fill(_data[i].values)
cr_cret.store(univ_ib_eqidx_ext + vol_names[k]+str(j), _data)
return None
'D07S14_005_QRG', 'D03S20_349_LRB', 'D10S08_236_QRG', 'D02S08_009_QRG', 'D02S26_027_QRB',
'D06S29_005_QRG', 'D04S26_005_QRG', 'D08S17_021_QRG', 'D07S23_015_QRG', 'D09S29_129_QRG',
'D08S26_236_LRB', 'D10S11_005_QRG', 'D09S29_521_QRG']
for i in _eq_idx:
# lag the volatility
# i = _eq_idx[0]
testv = filt.lag(mkt_retrieve(i, 'Stats', 'Volatility')['vol_gk240'].values)
# lag the signal
test2 = mkt_retrieve(i, 'MovReg', 'Signals')[['Date']+_sig_set_95_lvl]
test2.set_columns(['Date']+[k + '_LVL' for k in _sig_set_95_lvl])
test2_ = mkt_retrieve(i, 'MovReg', 'Changes1')[['Date']+_sig_set_95_ch1]
test2_.set_columns(['Date']+[k + '_CH1' for k in _sig_set_95_ch1])
test2 = DataFrame.merge(test2, test2_, on='Date')
test2_ = mkt_retrieve(i, 'MovReg', 'Changes3')[['Date']+_sig_set_95_ch3]
test2_.set_columns(['Date']+[k + '_CH3' for k in _sig_set_95_ch3])
test2 = DataFrame.merge(test2, test2_, on='Date')
test2_ = mkt_retrieve(i, 'MovReg', 'Changes5')[['Date']+_sig_set_95_ch5]
test2_.set_columns(['Date']+[k + '_CH5' for k in _sig_set_95_ch5])
test2 = DataFrame.merge(test2, test2_, on='Date')
test2_ = mkt_retrieve(i, 'MovReg', 'Changes7')[['Date']+_sig_set_95_ch7]
test2_.set_columns(['Date']+[k + '_CH7' for k in _sig_set_95_ch7])
test2 = DataFrame.merge(test2, test2_, on='Date')
test2_ = mkt_retrieve(i, 'MovReg', 'Changes9')[['Date']+_sig_set_95_ch9]
test2_.set_columns(['Date']+[k + '_CH9' for k in _sig_set_95_ch9])
def get_equity_index_combo_returns():
# split up o/h/l/c, trd val into separate files
# also store the multiplier
tick_sym_all, tick_name_all = [], []
for i in EquityIndexFuturesSet:
tick_sym_all.append(i.cstick)
tick_name_all.append(i.crname)
for i in tick_name_all:
# i = tick_name_all[0]
_data = mkt_retrieve(i, 'Stats', 'Returns')
# _data = cr_ret.retrieve(i) # change this
print(i)
if i == tick_name_all[0]:
comb_data_op = _data[['Date', 'Open']]
comb_data_hi = _data[['Date', 'High']]
comb_data_lo = _data[['Date', 'Low']]
comb_data_cl = _data[['Date', 'Close']]
comb_data_tv = _data[['Date', 'AggVolume', 'Close']]
comb_data_tv['TradedValue'] = comb_data_tv['AggVolume']*comb_data_tv['Close']
comb_data_tv = comb_data_tv[['Date', 'TradedValue']]
comb_data_ex = _data[['Date']]
comb_data_ex['ExchOpen'] = 1
comb_data_op.rename({'Open': i})
comb_data_hi.rename({'High': i})
comb_data_lo.rename({'Low': i})
comb_data_cl.rename({'Close': i})
comb_data_tv.rename({'TradedValue': i})
comb_data_ex.rename({'ExchOpen': i})
else:
comb_data_op_new = _data[['Date', 'Open']]
comb_data_hi_new = _data[['Date', 'High']]
comb_data_lo_new = _data[['Date', 'Low']]
comb_data_cl_new = _data[['Date', 'Close']]
comb_data_tv_new = _data[['Date', 'AggVolume', 'Close']]
comb_data_tv_new['TradedValue'] = comb_data_tv_new['AggVolume']*comb_data_tv_new['Close']
comb_data_tv_new = comb_data_tv_new[['Date', 'TradedValue']]
comb_data_ex_new = _data[['Date']]
comb_data_ex_new['ExchOpen'] = 1
comb_data_op_new.rename({'Open': i})
comb_data_hi_new.rename({'High': i})
comb_data_lo_new.rename({'Low': i})
comb_data_cl_new.rename({'Close': i})
comb_data_tv_new.rename({'TradedValue': i})
comb_data_ex_new.rename({'ExchOpen': i})
# merge them
comb_data_op = DataFrame.merge(comb_data_op, comb_data_op_new, on='Date')
comb_data_hi = DataFrame.merge(comb_data_hi, comb_data_hi_new, on='Date')
comb_data_lo = DataFrame.merge(comb_data_lo, comb_data_lo_new, on='Date')
comb_data_cl = DataFrame.merge(comb_data_cl, comb_data_cl_new, on='Date')
comb_data_tv = DataFrame.merge(comb_data_tv, comb_data_tv_new, on='Date')
comb_data_ex = DataFrame.merge(comb_data_ex, comb_data_ex_new, on='Date')
comb_data_op.sort('Date')
comb_data_hi.sort('Date')
comb_data_lo.sort('Date')
comb_data_cl.sort('Date')
comb_data_tv.sort('Date')
comb_data_ex.sort('Date')
tick_cols = comb_data_cl.tick_cols()
for i in tick_cols:
comb_data_cl[i] = cuff.fill(comb_data_cl[i].values)
comb_data_op[i] = cuff.fill2(comb_data_op[i].values, comb_data_cl[i].values)
comb_data_hi[i] = cuff.fill2(comb_data_hi[i].values, comb_data_cl[i].values)
comb_data_lo[i] = cuff.fill2(comb_data_lo[i].values, comb_data_lo[i].values)
comb_data_tv[i] = cuff.fill1(comb_data_tv[i].values, 0)
comb_data_ex[i] = cuff.fill1(comb_data_ex[i].values, 0)
# save the data
comb_data_op['Date'] = comb_data_op['Date'].astype('int64')
comb_data_hi['Date'] = comb_data_hi['Date'].astype('int64')
comb_data_lo['Date'] = comb_data_lo['Date'].astype('int64')
comb_data_cl['Date'] = comb_data_cl['Date'].astype('int64')
comb_data_tv['Date'] = comb_data_tv['Date'].astype('int64')
comb_data_ex['Date'] = comb_data_ex['Date'].astype('int64')
cr_cret.store('EquityIndex_IBUniv_Open', comb_data_op)
cr_cret.store('EquityIndex_IBUniv_High', comb_data_hi)
cr_cret.store('EquityIndex_IBUniv_Low', comb_data_lo)
cr_cret.store('EquityIndex_IBUniv_Close', comb_data_cl)
cr_cret.store('EquityIndex_IBUniv_TrdVal', comb_data_tv)
cr_cret.store('EquityIndex_IBUniv_ExchOpen', comb_data_ex)
return None
def get_history(self, cur_adj=True, trans_deriv=True):
if (self.stdate == 0) or (self.stdate != 0 and self.cstickold != ''):
x1 = get_cont_series_roll_basic(self.cstick, self.ndays, matur=self.fmatur)
else:
x1 = get_cont_series_roll_basic(self.cstick, self.ndays, matur=self.fmatur, hist_st_date=self.stdate)
if self.cstickold != '':
if self.stdate == 0:
x2 = get_cont_series_roll_basic(self.cstickold, self.ndays, matur=self.fmatur)
else:
x2 = get_cont_series_roll_basic(self.cstickold, self.ndays, matur=self.fmatur, hist_st_date=self.stdate)
# merge x1 and x2 based on traded volume
x1['TradedValue'] = x1['Close'] * x1['Volume']
x2['TradedValue'] = x2['Close'] * x2['Volume'] * self.multold
# find the first maturity when the x1 contract is larger than the x2 contract
x1t = x1[['Date', 'TradedValue', 'ContractAtClose']]
x2t = x2[['Date', 'TradedValue', 'ContractAtClose']]
xt = DataFrame.merge(x2t, x1t, on='Date')
switch_idx = [i for i, j in enumerate(zip(xt['TradedValue_x', list], xt['TradedValue_y', list]))
if j[0] < j[1]][0]
# switch_idx = switch_idx[0]
xt = xt[switch_idx:, :]
# now find the first rollover date of x1
xtf = [i for i, j in enumerate(xt['ContractAtClose_y', list]) if j > 0][0]
xtd_switch = xt[xtf, 'Date'] # switch over at the end of this date
x1 = x1[x1['Date'] >= xtd_switch, :]
x2 = x2[x2['Date'] <= xtd_switch, :]
# transform
xr = x1[0, 'Close']/x2[x2.shape[0]-1, 'Close']
for i in ['Open', 'High', 'Low', 'Close']:
x2[i] = x2[i]*xr
for i in ['Volume', 'OpenInterest', 'AggVolume', 'AggOpenInterest']:
x2[i] = x2[i]/xr
# relabel the rollover date
xrc = int(x1[0, 'ContractAtClose'])
xro = [int(i) for i in x2['ContractAtClose', list] if i!=0]
if xrc in xro:
# remove xrc from xro
xroi = [i for i, j in enumerate(x2['ContractAtClose', list]) if j==xrc][0]
x2[xroi, 'ContractAtClose'] = int(0)
# now merge x2 with x1 from the second row of 1
x2.row_bind(x1[1:, :])
del x1['TradedValue']
x1 = x2.copy()
# convert to USD if not converted
if cur_adj and self.curr != 'USD':
xc = _get_curr_to_usd_data(self.curr)
xc.set_columns(['Date', 'Curr'])
x2 = DataFrame.merge(x1, xc, on='Date')
x2 = x2[~np.isnan(x2['Contract'].values), :]
for i in ['Contract', 'Date', 'Front', 'LastTrdDate', 'ContractAtClose']:
x2[i] = x2[i].astype('int64')
# invert the currency
x2['Curr'] = 1/x2['Curr']
# replace currency by previous value
x2['Curr'] = x2['Curr'].fillna(method='ffill')
if trans_deriv: # may be some bug here (?)
# assumes daily cash settlement
cl_val = x2['Close'].values
fx_val = x2['Curr'].values
n = cl_val.shape[0]
fx_mult = np.empty(n)*np.nan
fx_mult[n-1] = 1
fx_mult[0:(n-1)] = fx_val[1:n]/fx_val[0:(n-1)]
# fx_mult[0:(n-1)] = 1/(fx_val[0:(n-1)]+(1-fx_val[0:(n-1)])*cl_val[0:(n-1)]/cl_val[1:n]) --- bug
fx_mult[0:(n - 1)] = 1 / (fx_mult[0:(n - 1)] + (1 - fx_mult[0:(n - 1)]) * cl_val[0:(n - 1)] / cl_val[1:n])
fx_mult = np.cumprod(fx_mult[::-1])[::-1]
# get new values
for i in ['Open', 'High', 'Low', 'Close']:
x2[i+'_'] = x2[i].values*fx_mult
for i in ['Volume', 'OpenInterest', 'AggVolume', 'AggOpenInterest']:
x2[i+'_'] = ((x2['Close'].values*x2[i].values)*fx_val)/x2['Close_'].values
x2 = x2[['Contract', 'Date', 'Open_', 'High_', 'Low_', 'Close_', 'Volume_',
'OpenInterest_', 'AggVolume_', 'AggOpenInterest_', 'Front', 'LastTrdDate', 'ContractAtClose']]
x2.set_columns(['Contract', 'Date', 'Open', 'High', 'Low', 'Close', 'Volume', 'OpenInterest',
'AggVolume', 'AggOpenInterest', 'Front', 'LastTrdDate', 'ContractAtClose'])
else: # trans_spot
# cl_val = x2['Close'].values
fx_val = x2['Curr'].values
for i in ['Open', 'High', 'Low', 'Close']:
x2[i + '_'] = x2[i].values * fx_val
for i in ['Volume', 'OpenInterest', 'AggVolume', 'AggOpenInterest']:
x2[i + '_'] = ((x2['Close'].values * x2[i].values) * fx_val) / x2['Close_'].values
x2 = x2[['Contract', 'Date', 'Open_', 'High_', 'Low_', 'Close_', 'Volume_',
'OpenInterest_', 'AggVolume_', 'AggOpenInterest_', 'Front', 'LastTrdDate', 'ContractAtClose']]
x2.set_columns(['Contract', 'Date', 'Open', 'High', 'Low', 'Close', 'Volume', 'OpenInterest',
'AggVolume', 'AggOpenInterest', 'Front', 'LastTrdDate', 'ContractAtClose'])
x1 = x2.copy()
if 'TradedValue' in x1.columns:
del x1['TradedValue']
return x1
def get_cont_series_roll_basic(tick, n_days, n_months = 0, matur=1, eom=None, hist_st_date=None):
# eom = uses n_days after doing a n_months month end, else it is n_months + n_days
x = cr_ft_hs.retrieve(tick)
# drop data when the AggregateVolume was less than 50 contracts
if hist_st_date is not None:
x = x[x['Date']>=hist_st_date, :]
x['Contract'] = x['Contract'].astype('int64')
x['Date'] = x['Date'].astype('int64')
x1 = cr_ft_ldt.retrieve(tick)
# for each date, retain only the two nearest contracts
x.sort(['Date', 'Contract'])
y = x.apply(['Contract'], _rank, group_col=['Date'])
x['TempCol'] = y.values
# print(matur)
x = x[x['TempCol']<=(matur+1), :]
# merge with te last trading day
x2 = DataFrame.merge(x, x1, on='Contract')
x2 = x2[~np.isnan(x2['Close'].values), :]
x2 = x2[[i for i, j in enumerate(x2['TempCol', list]) if ~np.isnan(j)], :]
x2['Contract'] = x2['Contract'].astype('int64')
x2['Date'] = x2['Date'].astype('int64')
x2['LastTrdDate'] = x2['LastTrdDate'].values.astype('int64')
# get the roll dates
y = str_to_pd_dt([str(int(i)) for i in x2['LastTrdDate', list]])
if n_months == 0:
y = pd_dt_to_str([j-dt.timedelta(days=n_days) for j in y])
else:
if eom is None:
y = pd_dt_to_str([j-dt.timedelta(days=n_days)-dt2.relativedelta(months=n_months) for j in y])
else:
y = pd_dt_to_str([last_day_of_month(j-dt2.relativedelta(months=n_months))-dt.timedelta(days=n_days)
for j in y])
y = [int(k) for k in y]
# fix actual roll date to be a date that is among the list of dates
# or, use calendar to get roll dates in the future
yun = list(sorted(set(y)))
yact = list(sorted(set(x2['Date', list])))
yact_max = max(yact)
yun_alt = []
for i in yun:
if (i <= yact_max) and (i not in yact):
yun_alt.append(max([j for j in yact if j<i]))
else:
if i>yact_max:
idt = dt.datetime(i // 10000, (i % 10000) // 100, i % 100)
if idt.isoweekday() in range(1,6):
yun_alt.append(i)
else:
if idt.isoweekday() == 6:
idt = idt - dt.timedelta(days=1)
else: # idt.isoweekday() == 7:
idt = idt - dt.timedelta(days=2)
yun_alt.append(idt.year*10000+idt.month*100+idt.day)
else:
yun_alt.append(i)
# DataFrame(data={'yun': yun, 'yun_alt': yun_alt})
for i, j in enumerate(yun):
if j != yun_alt[i]:
# print(j, yun_alt[i])
y = [(k if k!= j else yun_alt[i]) for k in y]
x2['RollDate'] = y
# carry out the rollover
x2.sort(['Date', 'Contract'])
# for each date, retain only the earlier rollover date
y2 = x2.apply(['RollDate'], min, group_col=['Date'])
x2['RollDate2'] = y2.values
if matur > 1:
x2 = x2[x2['TempCol'] >= matur, :]
# make a list of all roll dates
y3 = list(sorted(set(x2['RollDate2', list])))
# check if there are atleast two rows on each roll dates
# otherwise reduce the data set
y3n = []
for i in range(0, len(y3)):
xt = x2[x2['Date'] == y3[i], :]
y3n.append(xt.shape[0])
if y3n[0] == 2:
y3nt = 0
else:
y3nt = [i for i, j in enumerate(y3n) if j==2]
y3nt = y3nt[0]-1
y3u = y3[y3nt:len(y3)]
y3uc = x2['RollDate2', list]
y3uc = [i for i, j in enumerate(y3uc) if j in y3u]
x2 = x2[y3uc, :]
# now on the roll dates, retain two contracts
# and between the roll dates, retain one contract
cont_f = []
cont_s = []
for i in y3u:
# i = y3u[76]
x2t = x2[x2['Date'] == i, :]
if x2t.shape[0] == 0:
cont_f.append(0)
cont_s.append(0)
elif (x2t.shape[0] == 1) and (i != y3u[len(y3u)-1]): # newly added condition
x2 = x2[x2['Date'] >= i, :]
cont_f.append(0)
cont_s.append(x2t['Contract', list][0])
elif (x2t.shape[0] == 1) and (i == y3u[len(y3u)-1]): # newly added statement (to handle dead futures)
cont_f.append(x2t['Contract', list][0])
cont_s.append(0)
else:
x2ti = x2t['Contract', list]
cont_f.append(x2ti[0])
cont_s.append(x2ti[1])
cont_def = DataFrame(data={'ARollover': y3u, 'First': cont_f, 'Second': cont_s})
# drop if both 'First' and 'Second' are zero
while (cont_def[0, 'First'] == 0) and (cont_def[0, 'Second'] == 0):
cont_def = cont_def[1:,:]
if cont_def[0, 'Second'] != cont_def[1, 'First']:
cont_def[0, 'Second'] = cont_def[1, 'First'] # this happens if a new contract started midway at the beg
if cont_def[cont_def.shape[0]-1,'First'] == 0:
cont_def = cont_def[0:(cont_def.shape[0]-2), :] # this happens if today is not a rollover date
if cont_def[0, 'First'] == 0:
cont_def = cont_def[1:(cont_def.shape[0]-1), :]
# now built out the concatenated time-series
# on the rollover date, sell the first, and buy the second
x3 = x2[(x2['Contract'] == cont_def[0,'First']).values & (x2['Date'] <= cont_def[0, 'ARollover']), :]
x3['Contract2'] = 0
x3['Contract2'] = x3['Contract2'].astype('int64')
for i in range(0, cont_def.shape[0]-1):
# i = cont_def.shape[0]-2
x3t = x2[(x2['Contract'] == cont_def[i, 'Second']).values & (x2['Date'] >= cont_def[i, 'ARollover']).values
& (x2['Date'] <= cont_def[i+1, 'ARollover']).values, :]
# merge x3 and x3t
x3r = x3t.head(1)['Close', list][0]/x3.tail(1)['Close', list][0]
for j in ['Open', 'High', 'Low', 'Close']:
x3[j] = x3[j]*x3r
for j in ['Volume', 'OpenInterest', 'AggVolume', 'AggOpenInterest']:
x3[j] = x3[j]/x3r
x3t['Contract2'] = 0
x3t['Contract2'] = x3t['Contract2'].astype('int64')
x3[x3.shape[0]-1, 'Contract2'] = x3t[0, 'Contract']
# drop first row of x3t
x3t = x3t[1:, :]
# append the two
x3.row_bind(x3t)
# add the last contract
if cont_def[cont_def.shape[0]-1, 'Second'] != 0: # live contract
nc = cont_def.shape[0]-1
x3t = x2[(x2['Contract'] == cont_def[nc, 'Second']).values & (x2['Date'] >= cont_def[nc, 'ARollover']).values, :]
x3r = x3t.head(1)['Close', list][0] / x3.tail(1)['Close', list][0]
for j in ['Open', 'High', 'Low', 'Close']:
x3[j] = x3[j] * x3r
for j in ['Volume', 'OpenInterest', 'AggVolume', 'AggOpenInterest']:
x3[j] = x3[j] / x3r
x3t['Contract2'] = 0
x3t['Contract2'] = x3t['Contract2'].astype('int64')
x3[x3.shape[0] - 1, 'Contract2'] = x3t[0, 'Contract']
# drop first row of x3t
x3t = x3t[1:, :]
# append the two
x3.row_bind(x3t)
x3['Contract2'] = x3['Contract2'].astype('int64')
# x3[[i for i, j in enumerate(zip(x3['RollDate', list], x3['RollDate2', list])) if j[0]!=j[1]], :]
del x3['RollDate']
del x3['RollDate2']
names = [(i if i != 'TempCol' else 'Front') for i in list(x3.columns)]
x3.set_columns(names)
names = [(i if i != 'Contract2' else 'ContractAtClose') for i in list(x3.columns)]
x3.set_columns(names)
return x3
