TR function

Hi,

I want to download historical stock prices from 1 Jan 1990 until 30 Jun 2022 for a certain number of companies. I use the TR function and especially "=@TR(B1;"TR.Closeprice";"Sdate=1990-12-31 EDate=2021-12-31 Frq=D CH=Fd";B3)", where on B1, I place the stock RIC and on B3, the data are placed. Although the data are retrieved, the starting date (and the first observation) changes according to the data availability or the initiation of the stock public trading. How I can create fixed columns where in case of data unavailability in the first days or any other period a "null" to be appeared? So every column (stock) includes the same number of observations. I have a large number of stocks and to adjust every column by hand is an overkill.

Thank you in advance.

Find more posts tagged with

data

historical

price-history

Accepted answers

raksina.samasiri

hi @evan.salachas ,

Your question is about the Eikon Excel issue and the available functionality, rather than about Eikon Data API integration, and the moderators on this forum have expertise in APIs.

Hence, the best approach is to reach out to Eikon support directly, by submitting Eikon Excel questions via MyRefinitiv -> Product and Content -> I need help using the product -> Refinitiv Eikon rather than posting on dev forums after that, please expect an Eikon support expert to reach out to you shortly, via the email address that you have registered on MyRefinitiv with.

All comments

raksina.samasiri

hi @evan.salachas ,

Your question is about the Eikon Excel issue and the available functionality, rather than about Eikon Data API integration, and the moderators on this forum have expertise in APIs.

raksina.samasiri

hi @evan.salachas ,

In case you'd like to retrieve the data using the Eikon Data API, the parameter 'CalcDate' can be added to show all the dates of the calculation, for example, this instrument's first trade date is on 2018-04-25 and the data returned after the 'CalcDate' was added are all <NA> before the first trade date

rics = ['BNDX.N']
fields = ['TR.PriceClose.Date','TR.PriceClose.CalcDate','TR.PriceClose']
parameters = {'SDate':'2018-04-01', 'EDate': '2019-08-31', 'Frq': 'D'}

df, err = ek.get_data(rics, fields, parameters)
df

More information regarding the Eikon Data API can be found in the Developer portal and here's its quickstart guide

zoya faberov

Hello @evan.salachas ,

Based on the answer from @raksina.samasiri , and applying FirstTradeDate to multiple instruments, you may:

ek.get_data(['GOOG.O','TRI.N'],['TR.FirstTradeDate'])

Resulting in dates:

(  Instrument First Trade Date
 0     GOOG.O       2014-03-27
 1      TRI.N       2001-10-29,
 None)

Find minimum of them all:

df["First Trade Date"].min()

Request starting just before:

df, err = ek.get_data(['GOOG.O','TRI.N'],
            ['TR.CLOSEPRICE','TR.CLOSEPRICE.date'],
            {'SDate':'2001-10-28','EDate':'2022-08-30','Frq':'D'})
df

And pivot this result:

pt = df.pivot_table(values='Close Price', index=['Date', 'Instrument']).unstack('Instrument')
pt

Hope this helps in addition

evan.salachas

thank you raksina!

evan.salachas

Thank you zoya! very helpful!

EXPLORE OUR SITES