For a deeper look into our Eikon Data API, look into:

Overview |  Quickstart |  Documentation |  Downloads |  Tutorials |  Articles

question

Upvotes
Accepted
5 0 0 3

Get First Result From Time-Series Request Without Using a Target Cell Range Literal

I am trying to retrieve the dates of upcoming earnings releases/earnings calls using the Excel Eikon add-in. Crucially, I only want to retrieve a single date - that is the date of the NEXT event. The following formula achieves this:

=TR("B3MSM28","TR.EventStartDate","Sdate=1D Edate=6M EventType=ECALL:RES", B2:B2)

(this retrieves earnings call/earnings release dates between tomorrow & 6M from now for 'Amadeus IT Group', returning the results in cell range B2:B2 - as of today 3 events fall within the 6 month window, and only 1 is returned by restricting the target cell range to size 1).

Result:

My Problem:

I am actually building excel reports using python, dumping Eikon add-in formulae into the excel as text strings. When I create the text string that will be inserted I do not know into which cell it will eventually written. Accordingly, I cannot specify a target cell range literal (highlighted bold) as above.

Attempted Solutions:

i. Simply omitting the target cell range doesn't work - I want it to return one value only:

=TR("B3MSM28","TR.EventStartDate","Sdate=1D Edate=6M EventType=ECALL:RES")

ii. I have tried defining a relative cell range via the excel name manager. My definition is:

THIS_CELL_RANGE : Refers to: =!A1:A1

I understand that this defines a relative cell range of size 1 that points at the cell itself. For example, the following excel built-in formula returns the width of whatever cell into which it is pasted: =CELL("width",THIS_CELL_RANGE)

But when I try this in my Eikon/Excel formula it doesn't work - it still returns three values:

=TR("B3MSM28","TR.EventStartDate","Sdate=1D Edate=6M EventType=ECALL:RES", THIS_CELL_RANGE)

Conclusion:

I hope my problem is clear. I've spoken with my in-house Refinitiv rep. and he has ruled out any solution that involves passing an additional parameter to the TR function (i.e. some special parameter that says 'return first result only'). I think I'm probably looking for some clever excel solution like my attempt ii. above, but I'm no excel whizz.

eikoneikon-data-apiworkspaceworkspace-data-apirefinitiv-dataplatform-eikonpythonexcel
1.png (2.4 KiB)
2.png (3.1 KiB)
icon clock
10 |1500

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

Upvotes
Accepted
3.8k 4 4 6

Hi @pseudo-lycaena

What if you try wrapping a parameter in a MIN() function?

=TR("B3MSM28","MIN(TR.EventStartDate)","Sdate=1D Edate=6M EventType=ECALL:RES",)
icon clock
10 |1500

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

Upvotes
5 0 0 3

Wow @marcin.bunkowski - you nailed it! That's exactly what I'm looking for.

Are the 'helper functions' like MIN & others documented somewhere so that I can see what others are available?

icon clock
10 |1500

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

Hi @pseudo-lycaena,

I just tried to modify the formula manually. In the Eikon Excel wizard you will not get any hint for that field as only a few functions are applicable.

However, if you are asking about 'helper functions' you may try e.g. with TR.PriceClose field and check the section "Quick Functions" where you would discover plenty of options how you can manipulate that field on the fly (see screenshot below).

For more guidance on Excel formulas please reach out to your local Refinitiv Helpdesk team.

ahs.jpg (266.8 KiB)
Click below to post an Idea Post Idea