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
lena.demaison avatar image
1 ●1 ●1 ●1

Excel formula builder: only 1 data point retrieved

Hi,

I am attempting to retrieve pricing data between 2016-2021 for all constituents of the Euro STOXX 600 index using Formula Builder in Excel. The formula appears to work but only returns one result.

I have also tried retrieving pricing data for the same time period using one individual stock with the same issue occurring.

This is my first time using Refinitiv in Excel so I'm not sure whether it's a simple error.

Thanks in advance :)

screenshot-2022-02-16-at-173247.png

excelindexconstituentsformula-buildertimeseries
screenshot-2022-02-16-at-173247.png (244.8 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.

Shadab Hussain avatar image   Shadab Hussain

Hi @lena.demaison,

Thank you for your participation in the forum. Is the reply below satisfactory in resolving your query?

If so please can you click the 'Accept' text next to the appropriate reply? This will guide all community members who have a similar question.


Thanks,

AHS

wasin.w avatar image   wasin.w

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

Thanks,


AHS

2 Answers

· Write an Answer
Upvotes
Accepted
raksina.samasiri avatar image
14.2k ●30 ●5 ●10

Hi @lena.demaison ,

To get the constituent list, you can use the formula below

=@RDP.Data(".STOXX","TR.IndexConstituentName;TR.IndexConstituentRIC","CH=Fd RH=IN",B2)

1645099697670.png

Then from the RIC list retrieved, you can get their TR.PriceClose

=@RDP.Data($D$3:$D$602,"TR.PriceClose;TR.PriceClose.date","Curn=EUR SDate=2016-06-01 EDate=2017-06-01 Frq=W CH=Fd RH=IN",G2)

1645099800240.png

However,

  • the length of the time period provided is too long (rows exceed the limit), so I put the date until the year 2017 instead, you may put the shorter time range and call it multiple times instead
  • this is to retrieve historical TR.PriceClose of the current Constituents of '.STOXX' only

Lastly, This forum is dedicated to software developers using Refinitiv APIs.

The moderators on this forum do not have deep expertise in every bit of content available through Refinitiv products, which is required to answer content questions such as this one.

The best resource for content questions is the Refinitiv Helpdesk, which can be reached by either calling the Helpdesk number in your country or submitting a new ticket to the support team via MyRefinitiv.

The Helpdesk will either have the required content expertise ready available or can reach out to relevant content experts to get the answer for you.

Hope this could help


1645099697670.png (36.2 KiB)
1645099800240.png (107.2 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

@lena.demaison Please remove the two TR.IndexJL fields and add TR.CommonName field try the call again. 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.