For a deeper look into our Eikon Data API, look into:

Overview |  Quickstart |  Documentation |  Downloads |  Tutorials |  Articles

question

Upvotes
Accepted
john.lupton avatar image
3 ●0 ●1 ●1

Last Total Return value returned ad infinitum

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


eikoneikon-data-apiworkspaceworkspace-data-apirefinitiv-dataplatform-eikonpythondata
icon clock
10 |1500

Up to 2 attachments (including images) can be used with a maximum of 5.0 MiB each and 10.0 MiB total.

7 Answers

Upvotes
Accepted
jason.ramchandani01 avatar image
6.7k ●8 ●6 ●7

@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.


1621260809095.png (94.7 KiB)
icon clock
10 |1500

Up to 2 attachments (including images) can be used with a maximum of 5.0 MiB each and 10.0 MiB total.

Upvotes
jason.ramchandani01 avatar image
6.7k ●8 ●6 ●7

@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()
icon clock
10 |1500

Up to 2 attachments (including images) can be used with a maximum of 5.0 MiB each and 10.0 MiB total.

john.lupton avatar image 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 avatar image 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

Upvotes
jason.ramchandani01 avatar image
6.7k ●8 ●6 ●7

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

icon clock
10 |1500

Up to 2 attachments (including images) can be used with a maximum of 5.0 MiB each and 10.0 MiB total.

john.lupton avatar image john.lupton

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

Upvotes
jason.ramchandani01 avatar image
6.7k ●8 ●6 ●7

@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.


1618249811108.png (5.8 KiB)
1618249931036.png (140.3 KiB)
icon clock
10 |1500

Up to 2 attachments (including images) can be used with a maximum of 5.0 MiB each and 10.0 MiB total.

Upvotes
jason.ramchandani01 avatar image
6.7k ●8 ●6 ●7

@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.

icon clock
10 |1500

Up to 2 attachments (including images) can be used with a maximum of 5.0 MiB each and 10.0 MiB total.

john.lupton avatar image 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

Upvotes
jason.ramchandani01 avatar image
6.7k ●8 ●6 ●7

@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.

icon clock
10 |1500

Up to 2 attachments (including images) can be used with a maximum of 5.0 MiB each and 10.0 MiB total.

Upvotes
jason.ramchandani01 avatar image
6.7k ●8 ●6 ●7

@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.

icon clock
10 |1500

Up to 2 attachments (including images) can be used with a maximum of 5.0 MiB each and 10.0 MiB total.

Click below to post an Idea Post Idea