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.