Excel-related COM API upgrades:

AdfinXRtLib (rtx.dll)

Content

Prerequisites

COM Prerequisites

AdfinXRtLib (rtx.dll)

AdfinX RealTime AdxRtSourceList

What does 'AdfinX RealTime AdxRtSourceList' do?

VBA

New Method (Python)

Real-Time Fields Available With The COM API

Real-Time Fields Available In Python

AdfinX RealTime AdxRtList

What does AdxRtList do?

RtGet Real-Time

Real-Time ONIMAGE

Real-Time ONIMAGE VBA

Python

Real-Time ONUPDATE

VBA

Python

NEW FUNCTIONALITY: Record ticks

AdfinX RealTime - AdxRtChain

What does AdxRtChain do?

VBA

Python

AdfinX RealTime - AdxRtHistory - Interday Time Series History

What does AdxRtHistory do?

VBA

Python

AdxRtHistory - Intraday Time Series History

VBA

Python

DEX2.dll

Rdata List Realtime

VBA

Python

Create a Streaming Price and register event callbacks using RDP

RData Function

What does RData do?

VBA

Python

Snapshot requests

Snapshot Requests Tip 1

Snapshot Requests Tip 2

Conclusion

Further Resources

Prerequisites

COM Prerequisites

1. Open a new single sheet Excel workbook.

   Save As with an appropriate name (e.g. AdxRtSourceList.xls or AdxRtSourceList.xlsm in Office 2007 or higher).

2. Go to the VBE (Visual Basic Editor), ensure the Project Explorer is visible and select the project for the workbook.

   <ALT><F11> or Tools, Macro, Visual Basic Editor in Excel 2003 or Developer, Visual Basic in Excel 2007 and above, View, Project Explorer If the Developer header is not visible in Excel 2007 and above, go to the Excel Office Button, select Excel Options (lower right), Popular, and check the 'Show Developer tab in the Ribbon' box.

3. In the VBE, click on File, Import File and import PLVbaApis.bas.

   The .bas location is C:\Program Files (x86)\Thomson Reuters\Eikon\Z\Bin (Z may be X or Y, depending on the last Eikon update). The .bas is loaded as a new VB project module, PLVbaApis.

4. In the PLVbaAPis module, comment out the sections which aren't required.

   E.G.: when dealing with AdxRtSourceList, part of the real time library AdfinXRtLib, the AdfinX Real Time section can remain uncommented.

5. In the VBE, go Tools, References and ensure that AdfinX Real Time Library is checked.

   If it is not in the list the library is called rtx.dll and its location for Eikon 4 is ">C:\Program Files (x86)\Thomson Reuters\Eikon\Z\Bin (Z may be X or Y, depending on the last Eikon update).

Documentation on using the COM API in the Microsoft Office suite is available here: COM APIs for Microsoft Office. Users were also able to use the COM APIs outside of Microsoft Office suite for example in a standalone app: COM APIs for use in custom applications. A list of the prerequisites in question can be found in the index of this article.

If you are new to Python, don't hesitate to install it on your machine and try it out yourself as outlined in this 3rd party tutorial. Otherwise, you can simply use Codebook as outlined in this Tutorial Video.

Python works with libraries that one can import to use functionalities that are not natively supported by the base coding package. Some popular distributuions of python include many of the popular packages that one could use for various tasks - Anaconda is the most popular such distribution.

The RD Library allows for code portability across the desktop and enterprise platforms - with only small changes in authentication credentials. These credentials are stored in a config file - but if you are just using the desktop you need not concern yourself with this as a desktop session is the default credential setup.

<refinitiv.data.session.Definition object at 0x7fa34230ac18 {name='workspace'}>

AdfinXRtLib (rtx.dll)

AdfinX RealTime AdxRtSourceList

What does 'AdfinX RealTime AdxRtSourceList' do?

This functionality was used to get the real-time field list for the selected Source Name, such as an instrument (e.g.: VOD.L) (using AdxRtSourceList Class of AdfinXRtLib, the AdfinX Real Time 6.0 Library, rtx.dll). It was best demonstrated in the Tutorial 1 - Real-time Source List, Fields - AdxRtSourceList's Excel Workbook:

VBA

The VBA for AdxRtSourceList is very simple, and consists simply in creating an instance of an AdxRtSourceList object using the PLVbaApis function:

Set myAdxRtSrcLst = CreateAdxRtSourceList()

New Method

This functionality is replaced with a user interface called the Data Item Browswer (DIB) that you can find on workspace, and for which there is a video tutorial. This will give you a list of all real-time and non-real-time fields available for a particular instrument. For real-time fields the RD library can also provide this programatically - see below:

Real-Time Fields Available With The COM API

There are a limited number of fields available on the COM API in 'AdxRtSourceList', you can lookup fields available in the Quote app for any instrument in question:

More are available on the DIB and via the code available below in the 'Real-Time Fields Available In Python' section.

Note that available fields for each instrument type difffers on the type.

Note that the CF_ fields are also available to streaming Desktop sessions (not to platform sessions due to differences in data licenses)

Real-Time Fields Available In Python

You can get a collection of all the Real-Time fields available via:

['Instrument', 'PROD_PERM', 'RDNDISPLAY', ..., 'MIDLO1_MS', 'BID_HR_MS']

Note that available fields for each instrument type difffers on the type, e.g.:

['Instrument', 'PROD_PERM', 'RDNDISPLAY', ..., 'CF_CURR', 'SPS_SP_RIC']

AdfinX RealTime AdxRtList

What does AdxRtList do?

Returns real-time data for multiple (or single) instrument and fields. Data is returned initially as ONIMAGE - which is a snapshot of data for the requested fields, followed by a series of ONUPDATE messages - which are received whenever a data item changes or gets updated. You can also request ONTIME returns which would give an ONIMAGE snapshot at periodic intervals (say every hour for example). All the following functions rely on AdxRtList API calls - RtGet(), RData() and TR() for real-time data. Typically we would need to write callback handlers to deal with the returns from the API.

RtGet Real-Time

When using the old COM API to get RtGet Real Time data, one may be greeted with an Excel sheet that looks like the below, as per Tutorial 2 - Real-time Data Retrieval - AdxRtList's Excel workbook:

The functionalities shown here are easily recreated using the RD Library:

1. Real Time FX rates snapshot - 'Real-Time ONIMAGE'
2. 'Real Time Update', which updates real time, as soon as an update for the instrument and field is received
3. A periodic ONTIME snapshot

Real-Time ONIMAGE

When collecting data ONIMAGE, we are collecting a current snapshot of the data we're after. This could not be simpler in RD in Python!

VBA

In VBA, you'd create an instance of an AdxRtList object using the PLVbaApis function CreateAdxRtList.

Set myRtGet = CreateAdxRtList() ' The code will replicate RtGet(), one instrument, one field.'

Set myAdxRtList = CreateAdxRtList() ' The code will replicate RData(), multiple items & fields.'

Then create your function cmdGetRealTimeONIMAGE_Click:

Private Sub cmdGetRealTimeONIMAGE_Click()
Dim strRICs As String ' Can have one or more items
Dim varFIDs As Variant ' Field can be numeric as well as a string, e.g. BID is field 22

ActiveCell.Select

If Not myRtGet Is Nothing Then Set myRtGet = Nothing
Set myRtGet = CreateAdxRtList()

With myRtGet
.ErrorMode = DialogBox
.Source = [Source].Value
strRICs = [RIC].Value
varFIDs = [FID].Value

.RegisterItems strRICs, varFIDs
' 'Different methods shown below.
' strRICs = "EUR="
' varFIDs = "BID"
' .RegisterItems strRICs, varFIDs
' .RegisterItems "EUR=,GBP=,JPY=", "BID,ASK"

.StartUpdates RT_MODE_IMAGE ' 4
'.StartUpdates RT_MODE_ONUPDATE ' 3
'.StartUpdates RT_MODE_NOT_SET ' 5
'.StartUpdates RT_MODE_ONTIME ' 2
'.StartUpdates RT_MODE_ONTIME_IF_UPDATED ' 1
End With ' For the With myRtGet
End Sub

This would allow, in this example, for the 'Get Real Time ONIMAGE' buttons to work. For updates, the below could be used:

' Returns the initial image for the instrument. NOTE - .StartUpdates RT_MODE_IMAGE
Private Sub myRtGet_OnImage(ByVal DataStatus As AdfinXRtLib.RT_DataStatus)
Dim arrRICs As Variant, arrFields As Variant
Dim lngRICFidVal As Single
Dim a As Integer

If DataStatus = RT_DS_FULL Then
With myRtGet
' Array of the list of instruments - only one in this case.
arrRICs = .ListItems(RT_IRV_ALL, RT_ICV_USERTAG)
' Array of the list of Fields for the ath item in the arrRics (base 0)
a = 0
arrFields = .ListFields(arrRICs(a, 0), RT_FRV_ALL, RT_FCV_VALUE)
End With

' And a specific value for a specific instrument, specific field.
'lngRICFidVal = myRtGet.Value("EUR=", "BID")
lngRICFidVal = myRtGet.Value([RIC].Value, [FID].Value)
[F7].Value = lngRICFidVal
End If
End Sub

Python

In python, things could not be easier as most of the equivalent code about is abstracted to the library and you just use a one-line function! You can go ahead and try it all out in Codebook:

You can easily assign this info to an object too.

The 'Real Time ONUPDATE' buttons in the example pictured above was coded with VBA code for cmdGetRealTimeONUPDATE_Click:

Private Sub cmdGetRealTimeONUPDATE_Click()
Dim strRICs As Variant, varFIDs As Variant

ActiveCell.Select
Set myRtGet2 = CreateAdxRtList
With myRtGet2 .ErrorMode = DialogBox .Source = [Source].Value strRICs = [RIC].Value varFIDs = [FID].Value
.RegisterItems strRICs, varFIDs .StartUpdates RT_MODE_ONUPDATE End With ' For the With myRtGet2 End Sub

' Returns the data for updates - NOTE .StartUpdates RT_MODE_ONUPDATE. Private Sub myRtGet2_OnUpdate(ByVal a_itemName As String, ByVal a_userTag As Variant, ByVal a_itemStatus As AdfinXRtLib.RT_ItemStatus) Dim arrFields As Variant Dim lngRICFidVal As Long
If a_itemStatus = RT_ITEM_OK Then arrFields = myRtGet2.ListFields(a_itemName, RT_FRV_ALL, RT_FCV_VALUE)
' And a specific value for a specific instrument, specific field. 'If a_itemName = "EUR=" Then lngRICFidVal = myRtGet2.Value("EUR=", "BID"): [F12].Value = arrFields(0, 1) If a_itemName = [RIC].Value Then lngRICFidVal = myRtGet2.Value([RIC].Value, [FID].Value): [F12].Value = arrFields(0, 1) End If End Sub

Then, on VBA, you'd have to have a buttons to stop the stream with cmdSwitchRealTimeOFF_Click, which, in python, is stream.close():

Python

For us to start using pricing streams with events, we need to define a callback to receive data events:

Open the stream and register the callback

11:38:32.995630 - Data received for EUR=

Close the stream

We can use a Python loop with sleep to recreate that simply:

The cell below gets an update for instruments 'GBP=', 'EUR=' and 'JPY=' and fields 'BID' and 'ASK' every 5 seconds:

NEW FUNCTIONALITY: Record ticks

With the RD library we now have the ability to record a pricing stream. Here's how:

Create and open a Pricing stream

Start recording

... Wait for a little while (5 seconds) ...

Stop recording and display the recorded history

Resample the tick history to 5 seconds bars

Close the stream

<OpenState.Closed: 'Closed'>

AdfinX RealTime - AdxRtChain

What does AdxRtChain do?

As per Tutorial 3 - Real-time Chain Retrieval - AdxRtChain's Excel Workbook, Adfin X RealTime Chain (AdxRtChain) returns a list of the constituent instrument codes for any chain such as 0#.FTSE (the FTSE 100 instruments). Data is returned as OnUpdate event, the only other event is OnStatusChange:

VBA

In VBA, we went through with the creation of cmdGetChain_Click:

Private Sub cmdGetChain_Click()
ActiveCell.Select

If myAdxRtChain Is Nothing Then Set myAdxRtChain = CreateAdxRtChain()

With myAdxRtChain
.Source = "IDN"
.ItemName = Range("G6").Value
.RequestChain
End With
End Sub

then myAdxRtChain_OnUpdate:

Private Sub myAdxRtChain_OnUpdate(ByVal DataStatus As AdfinXRtLib.RT_DataStatus)
Dim i As Integer

If DataStatus = RT_DS_FULL Then
For i = 1 To UBound(myAdxRtChain.Data)
Range("G8").Offset(i - 1, 0).Value = myAdxRtChain.Data(i)
Next i
End If
End Sub

then we ought to make sure we can close the connection with cmdClearChain_Click, which is done simply in Python with rd.close_session().

We can replicate this easily in Python with the Pricing snapshots and Fundamental & Reference data function get_data() - moreover we can decode the chain and request fields in one operation:

Python

Not all chains resolve directly - for example a commodity chain - in such cases we can use the Chain Object to decode as follows:

AdfinX RealTime - AdxRtHistory - Interday Time Series History

What does AdxRtHistory do?

Adfin RealTime History (AdxRtHistory) is used to retrieve interday (not intraday) time series (historic) data for an instrument or instruments. This was best exemplified in Tutorial 5 - Time Series History - AdxRtHistory's Excel Workbook:

VBA

In VBA, we used AdfinXRtLib:

' Note the use of CreateReutersObject - function in the PLVbaApis module.
If myAdxRtHist Is Nothing Then Set myAdxRtHist = CreateReutersObject("AdfinXRtLib.AdxRtHistory")

On Error GoTo errHndlr
With myAdxRtHist
.FlushData
.ErrorMode = EXCEPTION ' EXCEPTION, DialogBox, NO_EXCEPTION
.Source = "IDN"
.ItemName = [C7].Value
.Mode = [H8].Value
.RequestHistory ("DATE,CLOSE,VOLUME") 'NOTE USE OF OLD FIELD NAMES, NOT ("TRDPRC_1.TIMESTAMP,TRDPRC_1.CLOSE,TRDPRC_1.VOLUME")

'arrFlds = Array("DATE","CLOSE","VOLUME")
'.RequestHistory ()arrFlds
'.RequestHistory ("*") ' "*" requests all fields.
End With

before the Private Sub 'myAdxRtHist_OnUpdate(ByVal DataStatus As AdfinXRtLib.RT_DataStatus)'.

Things are simpler in Python:

Python

As aforementioned, AdxRtHistory is used to retrieve time series (historic) data for an instrument or instruments except for intraday data. This is exactly what the instruments get_history is for!

['STAN.L', 'CRDA.L', 'ANTO.L', ..., 'TSCO.L', 'LGEN.L']

AdxRtHistory - Intraday Time Series History

VBA

Adfin RealTime History (AdxRtHistory) Intraday is similar

Which had few VBA lines needed:

Private Sub cmdGetInterday_Click()
ActiveCell.Select

MsgBox "AdxRtHistory cannot retrieve INTRA day data, use the RHistoryAPI instead"
End Sub

Python

Intraday data is just as easy to get:

As you can see, we get the data sought after. If you are after several fields for any one instrument (e.g.: an FX Pair), it couldn't be any simpler.

DEX2.dll

The DEX2.dll COM API component provides access to a broad range of fundamental and reference data (including all the TR.xxx fields). The RData Excel function provided both Fundamental and Reference as well as streaming realtime prices and news using this component under the hood along with RTX.dll.

Rdata List Realtime

When using the old COM API to get Rdata List data, one may be greeted with an Excel sheet that looks like this:

VBA

In VBA, this was done with a function akin to .StartUpdates RT_MODE_ONUPDATE & myRTList = CreateAdxRtList(), e.g.:

With myRTList
.ErrorMode = EXCEPTION
' N.B.! Source name may need to be changed if not named as below!
.Source = "IDN" '_SELECTFEED"
' Register the items and fields
.RegisterItems ItemArray, FieldArray

' Set the user tag on each item. This helps indexing the results
' table for displaying the data in the callback
For m = LBound(ItemArray) To UBound(ItemArray)
.UserTag(ItemArray(m), "*") = m
For n = LBound(FieldArray) To UBound(FieldArray)
.UserTag(ItemArray(m), FieldArray(n)) = n
Next n
Next m

.Mode = "TIMEOUT:5"
' If timed basis desired, then FRQ setting and RT_MODE_ONTIME or RT_MODE_ONTIME_IF_UPDATED required,
' which will trigger the OnUpdate event, shown below.
'.Mode = "FRQ:2S"
' And, finally, request the data!
Select Case Range("dcUpdateType").Value
Case "RT_MODE_IMAGE"
.StartUpdates RT_MODE_IMAGE
Case "RT_MODE_ONUPDATE"
.StartUpdates RT_MODE_ONUPDATE
End Select

'.StartUpdates RT_MODE_ONUPDATE
'.StartUpdates RT_MODE_IMAGE
'Other modes shown below; different events will be fired.
'.StartUpdates RT_MODE_ONTIME, RT_MODE_ONTIME_IF_UPDATED, RT_MODE_ONTIME,
' RT_MODE_ONUPDATE, RT_MODE_IMAGE , RT_MODE_NOT_SET
End With

To stop this update, you would have to create some VBA code to (e.g.: Sub cmdStop_Click()), but that is simpler in Python with stream.close():

However - many developers also used the RData worksheet function object directly in VBA.

Python

Here we have a data-frame of instruments and fields updating live every x seconds, let's say (for the sake of the use-case example) every 3 seconds. This is simple to recreate in Python:

<OpenState.Opened: 'Opened'>

Close the stream

<OpenState.Closed: 'Closed'>

Create a Streaming Price and register event callbacks using RDP

You can build upon the example above, using the RD Library Example notebook present in Codebook that demonstrates how to use a StreamingPrice with events to update a Pandas DataFrame with real-time streaming data. Using a StreamingPrices object that way allows your application to have at its own in memory representation (a Pandas DataFrame in this example) that is kept updated with the latest streaming values received from Eikon or Refinitiv Workspace. Here we're putting ourselves in the shoes of a Foreign eXchange (FX) trader looking at Emerging Market (EM) currency exchange rates; e.g: the Nigerian Nairas (NGN) and Indonesian Rupiah (IDR).

You can find the code for this on GitHub here.

RData Function

What does RData do?

RData is a flexible excel worksheet function allowing access to realtime and fundamental & reference data content. It can also be used programatcally in VBA and the results then dumped to an excel range for example.

VBA

For VBA related to Fundamental data, please see the 'DEX2 Fundamental and Reference' section below.

Python

We have separated getting current fundamental snapshots - using a rd.get_data function and getting historical fundamental timeseries using either the rd.get_data function or the rd.get_history() function.

Snapshot requests

For snapshot current fundamental requests - things are pretty straight forward - select your universe of instruments and then the list of fields you want. A full list of fields is available using the Data Item Browser App (type DIB into Eikon or Workspace search bar).

If we want to add some fundamental history to this request - we can add a parameters section to the get_data request - as below which will give us the last 4 fiscal years ('FRQ': 'FY') of history for each RIC. Note for static reference fields such sector codes - these will not be published as a timeseries history - however, we can forward fill as shown below.

Snapshot Requests Tip 1

Some fundamental fields will give multiple rows for a given day - for example if we request ratings sources - there could be more than one per date eg if there are 5 ratings agencies providing a rating - this is not usual for a time series history - or perhaps it is very different say than non-expandable single point timeseries. In this example as we have multiple RICS whose ratings dates may not overlap ie be on the same row <NA> artifacts are added to deliver the dataframe

Snapshot Requests Tip 2

Again this same multi-row exanding timeseries history - here in the case of broker recommendations - is another example - with lots of <NA> artifacts added.

Conclusion

In conclusion, we can see that the Office COM API had many great uses, but limitations too. This was without mentioning its reliability on DLLs that can be heavy to run on a personal machine. But the Refinitiv Python Libraries (RD, RDP and EDAPI) can not only replicate these COM functionalities but enhance them in many instances, the simplest example being the Historical News functionality shown above.

Several COM API functionalities relying on a technology called Adfin was not replicated in Python in this article, but we will investigate them in another article - so stay tuned!

Further Resources

COM APIs: Overview | Quickstart Guide | Documentation | Downloads | Tutorials | Q&A Forum

RD Library: Overview | Quickstart Guide | Documentation | Tutorials | Q&A Forum