Upgrade from Eikon -> Workspace. Learn about programming differences.

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 ●1 ●3 ●4

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-apipythonrefinitiv-dataplatform-eikonworkspaceworkspace-data-apidata
icon clock
10 |1500

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

wasin.w avatar image   wasin.w

<AHS>

The case 09793728 is still in "Working in progress" status, extend triage.

zoya faberov avatar image   zoya faberov

<AHS>

Hi @jason.ramchandani,

The case is closed, however the solution involves going back to the client. Would you mind looking it up, to make sure the solution and suggestion are conveyed correctly?

Thanks

zoya faberov avatar image   zoya faberov

<AHS>

Extending triage

Alex Putkov.1 avatar image   Alex Putkov.1

Emailed Jason to provide a response to the user following the support case being closed by the Helpdesk.

chavalit-jintamalit avatar image   chavalit-jintamalit

Thank you for your participation in the forum.
Is the reply below satisfactory in answering your question?
If yes please click the 'Accept' text next to the reply.
This will guide all community members who have a similar question.
Otherwise please post again offering further insight into your question.
Thanks,
AHS

7 Answers

· Write an Answer
Upvotes
Accepted
jason.ramchandani01 avatar image
10.2k ●18 ●6 ●9

@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 512.0 KiB each and 1.0 MiB total.

Jirapongse avatar image   Jirapongse

@john.lupton

Hi,

Please be informed that a reply has been verified as correct in answering the question, and marked as such.

Thanks,

AHS

Upvotes
jason.ramchandani01 avatar image
10.2k ●18 ●6 ●9

@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 512.0 KiB each and 1.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
10.2k ●18 ●6 ●9

@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 512.0 KiB each and 1.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
10.2k ●18 ●6 ●9

@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 512.0 KiB each and 1.0 MiB total.

Upvotes
jason.ramchandani01 avatar image
10.2k ●18 ●6 ●9

@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 512.0 KiB each and 1.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
10.2k ●18 ●6 ●9

@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 512.0 KiB each and 1.0 MiB total.

Upvotes
jason.ramchandani01 avatar image
10.2k ●18 ●6 ●9

@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 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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