Historical Highs and Lows using EDAPI

danieluphromes

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)

jonathan.legrand

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)