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 ●0 ●0 ●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

excelindexconstituentstimeseriesformula-builder
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.

2 Answers

Upvotes
Accepted
raksina.samasiri avatar image
8.6k ●18 ●5 ●8

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
8.3k ●13 ●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.