Optimizing Speed of Application.Run "EikonRefreshWorkbook"

I am trying to fetch some historical prices in large blocks of at least 50000 and inserting them directly into SQL with a VBA-Loop. Thanks to this forum I was already able to figure out that a synchronous refresh of the TR-formulas can be done with Application.Run "EikonRefreshWorkbook".

Everything works fine for a small number of 100 RICs. But for larger amounts everything just freezes. Even if I use a timeout interval of like 300000, it still isn't done after 5 minutes.

So I was wondering if there is a more efficient way to do this. Or what are reasonable block sizes and timeout intervals?

TR-formula includes TR Undadj Cloese Price, TR Company, TR Volume and TR.Turnover for one specific date.

Find more posts tagged with

eikon

sql

vba

synchronous

rics

Accepted answers

jason.ramchandani01

@Jotun So I think you should be using the APIs directly as opposed to opening a spreadsheet and taking the data from that - but all our APIs have limits in place. There are limits for the worksheet functions as well (which use the same backend services as our APIs) - as I think you have been finding out.

You are able to use our older Eikon COM APIs directly in VBA. In this instance you would want to use the DEX2 API to open a session and download the data. You can find more details and a DEX2 tutorial sample here. From there you could ingest into SQL or other.

However, I would recommend using our Eikon Data API in the Python environment as it is much more modern and will give you a much better experience than the COM APIs. If you have a list of 50K instruments say - you could make 10 API calls of say 5K instruments using some chunking and it would all be much easier for you to manage - without even resorting to Excel - and then you can use any Python SQL tool to ingest into any database you wish - all from one python script.

import refinitiv.dataplatform.eikon as ek
ek.set_app_key('YOUR APPKEY HERE')

riclist = ['VOD.L','IBM.N','TSLA.O']

df,err = ek.get_data(riclist,["TR.CLOSEPRICE(adjusted=0).date","TR.CLOSEPRICE(adjusted=0)",'TR.CompanySharesOutstanding','TR.Volume','TR.TURNOVER'])

df
#df.to_sql - see note below
#df.to_csv("test1.csv")

This will return you a pandas dataframe that you can easily directly write into any SQLAlchemy Database for example (see example here) or CSV / JSON for example.

I hope this can help.

All comments

Jotun

=@TR($C3,"TR.CLOSEPRICE (adjusted=0);TR.CompanySharesOutstanding;TR.Volume;TR.TURNOVER"," NULL=Null CODE=MULTI Frq=D SDate="&$A3&" EDate="&$A3)

VBA-Code, basically:

Dim i As Long

For i = 0 To 2

ThisWorkbook.Sheets("Data").Activate

Range(Cells(2 + i * 50000, 2), Cells(50001 + i * 50000, 4)).Select

Selection.copy

ThisWorkbook.Sheets("TR_Prices").Activate

Range(Cells(3 + i * 50000, 1), Cells(50002 + i * 50000, 3)).Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks_ :=False, Transpose:=False

'DoEvents

Application.Run "EikonRefreshWorkbook", True, 30000

'DoEvents

jason.ramchandani01

import refinitiv.dataplatform.eikon as ek
ek.set_app_key('YOUR APPKEY HERE')

riclist = ['VOD.L','IBM.N','TSLA.O']

df,err = ek.get_data(riclist,["TR.CLOSEPRICE(adjusted=0).date","TR.CLOSEPRICE(adjusted=0)",'TR.CompanySharesOutstanding','TR.Volume','TR.TURNOVER'])

df
#df.to_sql - see note below
#df.to_csv("test1.csv")

This will return you a pandas dataframe that you can easily directly write into any SQLAlchemy Database for example (see example here) or CSV / JSON for example.

I hope this can help.

EXPLORE OUR SITES