Discover Refinitiv
MyRefinitiv Refinitiv Perspectives Careers
Created with Sketch.
All APIs Questions & Answers  Register |  Login
Ask a question
  • Questions
  • Tags
  • Badges
  • Unanswered
Search:
  • Home /
  • Eikon COM /
avatar image
Question by Jotun · Dec 31, 2021 at 11:01 AM · eikonricsvbasqlsynchronous

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.


People who like this

0 Show 1
Comment
10 |1500 characters needed characters left characters exceeded
▼
  • Viewable by all users
  • Viewable by moderators
  • Viewable by moderators and the original poster
  • Advanced visibility
Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

avatar image
Jotun · Dec 31, 2021 at 11:44 AM 0
Share

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

1 Reply

  • Sort: 
avatar image
REFINITIV
Best Answer
Answer by jason.ramchandani01 · Jan 04 at 11:57 AM

@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")

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.

I hope this can help.


1641297076596.png (79.5 KiB)
Comment
n.barisic
Jotun

People who like this

2 Show 0 · Share
10 |1500 characters needed characters left characters exceeded
▼
  • Viewable by all users
  • Viewable by moderators
  • Viewable by moderators and the original poster
  • Advanced visibility
Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Watch this question

Add to watch list
Add to your watch list to receive emailed updates for this question. Too many emails? Change your settings >
13 People are following this question.

Related Questions

Synchronous Refresh Methods for VBA in Excel

Time Series w/ AdxRtHistory (Multiple RICs)

Cannot retrive data in VBA from AdxRtHistory

Can I change the Pause and Play mode of Eikon Excel using VBA?

Can we download un-adjusted stock price under DEX2 COM API

  • Copyright
  • Cookie Policy
  • Privacy Statement
  • Terms of Use
  • Anonymous
  • Sign in
  • Create
  • Ask a question
  • Spaces
  • Alpha
  • App Studio
  • Block Chain
  • Bot Platform
  • Connected Risk APIs
  • DSS
  • Data Fusion
  • Data Model Discovery
  • Datastream
  • Eikon COM
  • Eikon Data APIs
  • Electronic Trading
    • Generic FIX
    • Local Bank Node API
    • Trading API
  • Elektron
    • EMA
    • ETA
    • WebSocket API
  • Intelligent Tagging
  • Legal One
  • Messenger Bot
  • Messenger Side by Side
  • ONESOURCE
    • Indirect Tax
  • Open Calais
  • Open PermID
    • Entity Search
  • Org ID
  • PAM
    • PAM - Logging
  • ProView
  • ProView Internal
  • Product Insight
  • Project Tracking
  • RDMS
  • Refinitiv Data Platform
    • Refinitiv Data Platform Libraries
  • Rose's Space
  • Screening
    • Qual-ID API
    • Screening Deployed
    • Screening Online
    • World-Check One
    • World-Check One Zero Footprint
  • Side by Side Integration API
  • TR Knowledge Graph
  • TREP APIs
    • CAT
    • DACS Station
    • Open DACS
    • RFA
    • UPA
  • TREP Infrastructure
  • TRKD
  • TRTH
  • Thomson One Smart
  • Transactions
    • REDI API
  • Velocity Analytics
  • Wealth Management Web Services
  • Workspace SDK
    • Element Framework
    • Grid
  • World-Check Data File
  • 中文论坛
  • Explore
  • Tags
  • Questions
  • Badges