question

Upvotes
Accepted
7 3 3 2

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.

datahistoricalprice-history
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
Accepted
14k 30 5 10

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.

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
14k 30 5 10

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

1658769968544.png

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


1658769968544.png (87.6 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.

thank you raksina!

Upvote
32.2k 40 11 19

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

goog1.gif

And pivot this result:

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

goog2.gif

Hope this helps in addition


goog1.gif (28.3 KiB)
goog2.gif (27.4 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.

Thank you zoya! very helpful!

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.