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.
=@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
@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.