We are using a macro-enabled Excel file that retrieves historical financial data (including stock prices, company fundamentals, and returns) via the Refinitiv Eikon Excel add-in. The macro loops through a list of tickers and for each ticker:
Writes the ticker into a designated input cell (calculationTicker) on a “Calculator” sheet.
Relies on Refinitiv formulas (e.g., =TR(...), =RHistory(...)) that reference calculationTicker to retrieve historical data.
Refreshes the formulas via EikonRefreshSelection.
Copies the output to a “Data” sheet for further processing (beta regressions, etc.).
Recently, we observed the following issues:
Identical data for multiple tickers
After running the macro, all columns in the Data sheet contain the same values, even though the macro is iterating over different tickers.
This results in beta regressions with negative or unrealistic betas and R² = 100%, which is clearly incorrect.
Symptoms suggest ticker input not propagating
In the Calculator sheet, the input cell calculationTicker only shows the last ticker after the macro finishes.
Formulas that are supposed to dynamically reference calculationTicker may not update during the macro loop.
Formulas in the sheet
Some formulas use constructs like =INDEX(...MATCH(calculationTicker,...)) or reference a cell linked to calculationTicker.
During testing, manually changing calculationTicker sometimes does not update the dependent formulas immediately, suggesting potential issues with formula recalc timing or refresh behavior in combination with the Eikon add-in.
Steps already taken internally:
Verified that calculationTicker updates correctly during the macro loop.
Added forced worksheet calculation (Worksheet.Calculate) and small delays (Application.Wait) to give formulas time to recalc.
Confirmed that Excel is in Automatic Calculation mode.
Still, some tickers fail to refresh correctly, resulting in identical data columns for multiple tickers.
Impact:
Historical data for multiple tickers cannot be reliably retrieved.
Beta and other derived calculations are incorrect, affecting valuation models.
The macro relies heavily on dynamically referencing calculationTicker, so this issue prevents automated retrieval across a list of tickers.
Request to Refinitiv Support:
Can you confirm whether the Eikon Excel formulas (=TR(...), =RHistory(...)) fully support dynamic references to a cell that is updated by VBA macros?
Are there known issues with timing or formula recalculation when updating a ticker input cell and immediately calling EikonRefreshSelection via VBA?
Are there recommended practices for looping over multiple tickers in VBA to retrieve historical data reliably?
Is there a way to force Eikon formulas to recalc immediately after updating the ticker, other than manual refresh?
Formula examples: =@TR (calculationTicker,"TR.CompanyMarketCap(Scale=6);TR.TotalEquity(Scale=6)","SDate=#1 EDate=#2 Curn=EUR Frq=#3",,$D$2,$D$3,$D$4)
=@TR (calculationTicker,"TR.PriceClose","SDate=#1 EDate=#2 Curn=EUR Frq=#3",,$D$2,$D$3,$D$4)
