0

Thanks to the help in this forum, I got my SQL-conncection and inserts working now.

The following TR-formula is used to retrieve the data from Excel Eikon:

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

For 100k RICs the formulas usually need between 30s and 120s to refresh. That would still be acceptable.

The problem is to get the same refresh-speed in a VBA-loop. Application.Run "EikonRefreshWorksheet" is currently used for a synchronous refresh as recommended in this post.

https://community.developers.refinitiv.com/questions/20247/can-you-please-send-me-the-excel-vba-code-which-ex.html

The syntax of the code is correct and working for 100 RICS. But already for 1k the fetching gets very slow and will freeze completely for like 50k. Even with a timeout interval of 5min.

I isolated the refresh-part. There is nothing else slowing it down. So is this maybe just not the right method for fetching larger data sets? Does anyone know a better alternative?

ouflak
  • 2,458
  • 10
  • 44
  • 49
Christian Kühner
  • 3
  • 1

1 Answers1

0

I finally got some good advice from the Refinitiv Developer Forum which I wanted to share here:

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:

https://developers.refinitiv.com/en/api-catalog/eikon/com-apis-for-use-in-microsoft-office/tutorials#tutorial-6-data-engine-dex-2

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") 1641297076596.png

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.

Unfortunately, our company policy does not allow for Python at the moment. But the VBA-solution also worked, even though it took some time to understand the tutorial and it has more limitations.

Christian Kühner
  • 3
  • 1