Last Total Return value returned ad infinitum

john.lupton

Hi

I am getting daily total return data and I have a problem when no price data exists, e.g. the stock has been delisted:

If you look at the below data returned

      Instrument TR.TOTALRETURN   TR.TOTALRETURN.DATE TR.TOTALRETURN.CalcDate
0     0FUN.L^D15    0.127319929  2014-08-18T00:00:00Z              2021-03-26
1     0FUN.L^D15    0.127319929  2014-08-18T00:00:00Z              2021-03-25
2     0FUN.L^D15    0.127319929  2014-08-18T00:00:00Z              2021-03-24
3     0FUN.L^D15    0.127319929  2014-08-18T00:00:00Z              2021-03-23
4     0FUN.L^D15    0.127319929  2014-08-18T00:00:00Z              2021-03-22
...          ...            ...                   ...                     ...
3325  0FUN.L^D15            NaN                                    2008-01-31
3326  0FUN.L^D15            NaN                                    2008-01-30
3327  0FUN.L^D15            NaN                                    2008-01-29
3328  0FUN.L^D15            NaN                                    2008-01-28
3329  0FUN.L^D15            NaN                                    2008-01-25

Total return should be zero on a daily basis after 2014-08-18 but the API returns the last daily value ad infinitium which makes no sense, so how can I get zero returned for dates after 2014-08-18?

I am trying to create a total return price index so I need to calculate the product of these values. Alternatively is there a field for since issue total return (price + dividend return)

call:

params = {'SDate': '2008-01-25', 'EDate': '2021-03-26', 'Frq': 'D'}

df, err = ek.get_data(
            '0FUN.L^D15', ['TR.TotalReturn', 'TR.TotalReturn.Date', 'TR.TotalReturn.CalcDate'], field_name=True,
            parameters=params)

thanks

jason.ramchandani01

@john.lupton so I do have a more elegant workaround if you change Frq parameter to NA it will appear correctly as expected and will not repeat:

params = {'SDate': '2008-01-25', 'EDate': '2014-08-18', 'Frq': 'NA'}
df, err = ek.get_data('0FUN.L^D15', ['TR.TotalReturn', 'TR.TotalReturn.Date', 'TR.TotalReturn.CalcDate'], field_name=True,
            parameters=params)
df

I hope this can help.

jason.ramchandani01

@john.lupton please can you post the API call code in python here thanks. The instrument you are looking at is an expired contract. For expired contracts its best to know the start and end date. If you post your code we can look at it and make a suggestion.

You can also try the following which should return the index of the last non-nan value:

df['TR.TOTALRETURN'].last_valid_index()

john.lupton

@jason.ramchandani Hi i added the call in the OP. Its not futures contract its an equity. thanks for taking the time to look

john.lupton

That I know but what you suggest it does not address the problem. The problem is what eikon returns not manipulating a pandas dataframe

jason.ramchandani01

@john.lupton yup - i already changed it - its a delisted equity - did you try the last_valid_index() code I sent.

john.lupton

see my response to your first mentioning the suggestion above - your suggestion does not relate to the question posed

jason.ramchandani01

@john.lupton ok apols got it - you mean you want it to stop at 2014-08-18T00:00:00Z or the last total return date. We can check for the date of the last closing price using:

df,err = ek.get_data('0FUN.L^D15',['TR.PriceCloseDate'])
end_date = pd.to_datetime(df['Date']).dt.date.astype(str)
end_date[0]

We can then use that in the main api call:

params = {'SDate': '2008-01-25', 'EDate': end_date[0], 'Frq': 'D'}
df, err = ek.get_data('0FUN.L^D15', ['TR.TotalReturn', 'TR.TotalReturn.Date', 'TR.TotalReturn.CalcDate'], field_name=True,
            parameters=params)
df

Is this the kind of thing you mean? I do notice the totalreturn.date is repeating.

jason.ramchandani01

@john.lupton I have opened case 09793728 on your behalf as this is more of a content issue and have asked the content team to explain why multiple repeat values are being returned. I hope this is ok.

john.lupton

Hi @jason.ramchandani thanks for taking the time. I agree, this should not be a programming thing, I can code my way around it: I feel it is a bug in the API that if you ask for a total return for a period that ends and/or starts after a security has been delisted you get a non-zero value - that makes no sense in reality and it should be zero. Thanks p.s. how can I follow this ticket 09793728 ? thanks

jason.ramchandani01

@john.lupton Apols the case has been assigned to me - the first response I received from them was incorrect - so I have followed up and its now being investigated and they will come back to me. Apols I will report as soon as they get back to me - but its probably safe to say this won't be resolved quickly so I would for the moment implement a workaround in code. I hope this can help.

jason.ramchandani01

@john.lupton So the Analytics team have got back to me and said 'The current behaviour is expected per ADC design. Is it possible ask the client to filter out records where date not equal to calc date?' - which is all I can offer at present as a workaround.

I have asked the team to change the behaviour of this field as it is clearly not correct. For days where an instrument does not trade total return should be zero not the total return from the last traded day.

I hope this can help.