For a deeper look into our Eikon Data API, look into:
Overview | Quickstart | Documentation | Downloads | Tutorials | Articles
Historical Highs and Lows using EDAPI
I know I can get snapshots of Highs and Lows with code such as the below, but is it possile to get this historically?
df, err = ek.get_data( instruments = [ 'VOD.L' ], fields = [ 'TR.Price52WeekHighDate', 'TR.Price52WeekHigh', 'TR.Price52WeekLow', 'TR.Price52WeekLowDate', 'YRHIGH', 'YRLOW', 'YRHIGHDAT', 'YRLOWDAT' ] ) display(df)
Best Answer
Please try the following:
def Historical_HL(from_date=(datetime.now() - relativedelta(years=3)).strftime("%Y-%m-%d"),
to_date=datetime.now().strftime("%Y-%m-%d"),
instrument='VOD.L', field='TR.closeprice', field_name='Close Price', weeks=52, years=1):
mxyw = max(years, weeks/52)
if [years, weeks].index(mxyw) == 0:
switch = relativedelta(years=years)
else:
switch = relativedelta(weeks=weeks)
fromd = datetime.strptime(from_date, "%Y-%m-%d")
tod = datetime.strptime(to_date, "%Y-%m-%d")
df0, err = ek.get_data(
instruments = [instrument],
fields = [field + '.date', field],
parameters={
'SDate': (fromd - switch).strftime("%Y-%m-%d"), # more here: https://dateutil.readthedocs.io/en/stable/relativedelta.html
'EDate': to_date,
'Frq':'d'})
df0.Date = [df0.Date.iloc[i][:10] for i in range(len(df0))]
df1 = pd.DataFrame()
df1['date'] = [str(i)[:10] for i in pd.date_range((fromd - switch).strftime("%Y-%m-%d"), to_date, freq='D')]
df2 = df1.merge(df0, left_on=['date'], right_on=['Date'], how='outer')
df3 = df2.replace({float('nan') : np.nan})
df3 = df3.replace({pd._libs.missing.NA : np.nan})
mx_d, mx_ix_l , mx_val_l, mi_d, mi_ix_l, mi_val_l = [], [], [], [], [], []
for j, i in enumerate(range(weeks*7,len(df3))):
number_list = df3[field_name][j:i].to_list()
mi_val_l.append(min(number_list))
mi_ix = len(number_list) - number_list[::-1].index(np.nanmin(number_list)) - 1 + j
mi_ix_l.append(mi_ix)
mi_d.append(df3.iloc[mi_ix])
mx_val_l.append(max(number_list))
mx_ix = len(number_list) - number_list[::-1].index(np.nanmax(number_list)) - 1 + j
mx_ix_l.append(mx_ix)
mx_d.append(df3.iloc[mx_ix])
df4 = df3.copy()
df4[str(weeks) + "WH"] = [np.nan for i in range(weeks*7)] + [mx_d[i][field_name] for i in range(len(mx_d))]
df4[str(weeks) + "WHD"] = [np.nan for i in range(weeks*7)] + [mx_d[i].date for i in range(len(mx_d))]
df4[str(weeks) + "WL"] = [np.nan for i in range(weeks*7)] + [mi_d[i][field_name] for i in range(len(mi_d))]
df4[str(weeks) + "WLD"] = [np.nan for i in range(weeks*7)] + [mi_d[i].date for i in range(len(mi_d))]
ymx, ymi, ymxd, ymid = [], [], [], []
for i in range(366*years, len(df4)):
fm = df4.index[df4.date == (datetime.strptime(df4.iloc[i].date, "%Y-%m-%d") - relativedelta(years=years)).strftime("%Y-%m-%d")].tolist()
ymx.append(df4[field_name].iloc[fm[0]:i].max())
ymxd.append(df4.iloc[df4[field_name].iloc[fm[0]:i].idxmax()].date)
ymi.append(df4[field_name].iloc[fm[0]:i].min())
ymid.append(df4.iloc[df4[field_name].iloc[fm[0]:i].idxmin()].date)
df5 = df4.copy()
df5[str(years) + "YH"] = [np.nan for i in range(366*years)] + ymx
df5[str(years) + "YHD"] = [np.nan for i in range(366*years)] + ymxd
df5[str(years) + "YL"] = [np.nan for i in range(366*years)] + ymi
df5[str(years) + "YLD"] = [np.nan for i in range(366*years)] + ymid
return df5
I tried this for e.g.:
Historical_HL(from_date='2018-01-01', to_date='2020-01-01', instrument='VOD.L', field='TR.closeprice', field_name='Close Price', weeks=52, years=2)
