Upgrading to Workspace

We will be discontinuing the Eikon Desktop soon in favour of our next generation data and analytics workflow solution, LSEG Workspace. This page is designed to help you assess any changes you may need to make to programmatic (API) workflows. We also provide resources here to help you make those changes as well.

Custom Application COM API Upgrade:

AdfinXRtLib (rtx.dll)

 

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.

    	
            

import refinitiv.data as rd # pip install httpx==0.21.3 # !pip install refinitiv.data --upgrade

from refinitiv.data.discovery import Chain

from refinitiv.data.content import search

import pandas as pd

pd.set_option('display.max_columns', None)

import numpy as np

import os

import time

import datetime # `datetime` allows us to manipulate time as we would data-points.

from IPython.display import display, clear_output # `IPython` here will allow us to plot grahs and the likes.

rd.open_session("desktop.workspace")

<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:

    	
            RTCurrDf = rd.get_data(universe=['GBP='])
print(list(RTCurrDf.columns))

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

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

    	
            ATMIVDf = rd.get_data(universe=['AAPLATMIV.U'])
print(list(ATMIVDf.columns))

['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:

    	
            

rd.get_data(

    universe=['GBP=', 'EUR=', 'JPY='],

    fields=['BID', 'ASK'])

  Instrument BID ASK
0 GBP= 1.2003 1.2006
1 EUR= 1.0663 1.0667
2 JPY= 135.96 135.97

You can easily assign this info to an object too.

    	
            

realTimeImage = rd.get_data(

    universe=['GBP=', 'EUR=', 'JPY='],

    fields=['BID', 'ASK'])

realTimeImage

  Instrument BID ASK
0 GBP= 1.2002 1.2006
1 EUR= 1.0664 1.0667
2 JPY= 135.93 135.96

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:

    	
            

def display_data(data, instrument, stream):

    clear_output(wait=True)

    current_time = datetime.datetime.now().time()

    print(current_time, "- Data received for", instrument)

    display(data)

Open the stream and register the callback

    	
            

stream = rd.open_pricing_stream(

    universe=['GBP=', 'EUR=', 'JPY='],

    fields=['BID', 'ASK'],

    on_data=display_data

)

stream.open()

11:38:32.995630 - Data received for EUR=

Close the stream

    	
            stream.close()
        
        
    
  BID ASK
EUR= 1.0663 1.0667

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:

    	
            

# This cell's code is usually commented out so that the kernel doesn't get stuck in the while loop.

now = time.perf_counter()

while time.perf_counter() < now + 30:

    time.sleep(5)

    clear_output(wait=True)

    df = stream.get_snapshot(

        universe=['GBP=', 'EUR=', 'JPY='],

        fields=['BID', 'ASK'])

    display(df)

  Instrument  BID ASK
0 GBP= 1.2003 1.2006
1 EUR= 1.0663 1.0667
2 JPY= 135.96 135.97

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

    	
            

stream = rd.open_pricing_stream(

    universe=['GBP=', 'EUR=', 'JPY='],

    fields=['BID']

)

Start recording

    	
            stream.recorder.record(frequency='tick')
        
        
    

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

    	
            time.sleep(5)
        
        
    

Stop recording and display the recorded history

    	
            

stream.recorder.stop()

tick_history = stream.recorder.get_history()

display(tick_history)

  GBP= JPY= EUR=
  BID BID BID
Timestamp      
40:43.2 <NA> 135.97 <NA>
40:43.3 <NA> <NA> 1.0661
40:43.5 <NA> 135.96 <NA>
40:43.5 1.1995 <NA> <NA>
40:50.2 <NA> 135.95 <NA>
40:50.8 1.1994 <NA> <NA>
40:50.8 <NA> 135.95 <NA>

Resample the tick history to 5 seconds bars

    	
            tick_history.ohlc("5s")
        
        
    
  GBP= JPY= EUR=
  BID BID BID
  open high low close open high low close open high low close
Timestamp                        
07/03/2023 11:40 1.1995 1.1995 1.1995 1.1995 135.97 135.97 135.95 135.95 1.0661 1.0661 1.0661 1.0661
07/03/2023 11:40 1.1995 1.1995 1.1994 1.1994 135.95 135.97 135.95 135.95 1.0661 1.0661 1.0661 1.0661
07/03/2023 11:40 1.1994 1.1994 1.1994 1.1994 135.95 135.95 135.95 135.95 1.0661 1.0661 1.0661 1.0661

Close the stream

    	
            stream.close()
        
        
    

<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

    	
            

FTSEConstituentDf1 = rd.get_data(

    universe=['0#.FTSE'],

    fields=['TR.TURNOVER.timestamp', 'TR.TURNOVER', 'TR.EVToSales'])

FTSEConstituentDf1

 

 

 

Instrument Timestamp Turnover Enterprise Value To Sales (Daily Time Series Ratio)
0 STAN.L 2023-03-06T00:00:00Z 5254690524 3.641311
1 CRDA.L 2023-03-06T00:00:00Z 1274351304 4.528984
... ... ... ... ...
98 TSCO.L 2023-03-06T00:00:00Z 3041956489 0.468989
99 LGEN.L 2023-03-06T00:00:00Z 2561324328 <NA>

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

    	
            

LCOConstituentDf = rd.get_data(

    universe=Chain('0#LCO:'),

    fields=["CF_NAME", "CF_CLOSE", "OPINT_1"])

 

LCOConstituentDf

 

 

 

Instrument CF_NAME CF_CLOSE OPINT_1
0 LCOTOT BRENT CRUDE VOLS <NA> 2459512
1 LCOK3 BRENT CRUDE MAY3 86.18 467381
... ... ... ... ...
82 LCOG0 BRENT CRUDE FEB0 65.02 <NA>
83 LCOH0 BRENT CRUDE MAR0 65 <NA>

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!

    	
            

FTSEConstituents = list(FTSEConstituentDf1['Instrument'])

print(FTSEConstituents)

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

    	
            

TimeSeriesDf = rd.get_history(

    universe=FTSEConstituents[1:6],

    fields=['TR.PriceClose', 'TR.Volume'],  # 'TR' fields are usually historic ones.

    interval="1D",

    start="2022-01-25",

    end="2022-02-01")

 

TimeSeriesDf

  CRDA.L ANTO.L BNZL.L SGE.L SVT.L
  Price Close Volume Price Close Volume Price Close Volume Price Close Volume Price Close Volume
Date                    
25/01/2022 7666 371438 1379 1429044 2721 678948 762.4 3467017 2858 642275
26/01/2022 7830 755599 1411.5 889172 2754 628530 713 4323758 2871 318280
27/01/2022 7880 754142 1391.5 1460311 2776 671478 701 4752740 2914 522647
28/01/2022 7774 510683 1337.5 2839242 2786 1203491 711.2 3683775 2893 1017099
31/01/2022 7972 442252 1332.5 1333271 2767 545449 720.8 2966860 2873 751851
01/02/2022 8008 690445 1361.5 1605104 2775 433783 718.6 4462156 2903 439666

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:

    	
            

IntradayTimeSeriesDf = rd.get_history(

    universe=FTSEConstituents,

    fields=['TRDPRC_1'],

    interval="1min",  # The consolidation interval. Supported intervals are: tick, tas, taq, minute, 1min, 5min, 10min, 30min, 60min, hourly, 1h, daily, 1d, 1D, 7D, 7d, weekly, 1W, monthly, 1M, quarterly, 3M, 6M, yearly, 1Y.

    start="2022-06-01T13:00:00",

    end="2022-06-01T15:30:00")

 

IntradayTimeSeriesDf

TRDPRC_1
STAN.L CRDA.L ANTO.L BNZL.L SGE.L SVT.L BLND.L ICAG.L REL.L SMIN.L AZN.L HSBA.L CTEC.L WPP.L FRES.L AAF.L SGRO.L SJP.L TW.L AHT.L HLMA.L III.L CNA.L MNG.L BKGH.L SMDS.L NG.L RKT.L SKG.L WEIR.L MRON.L HSX.L CPG.L AUTOA.L AV.L ENT.L DGE.L INF.L UU.L PSHP.L HLN.L WTB.L PRU.L IMB.L EXPN.L BRBY.L RS1R.L ABDN.L GSK.L LAND.L BEZG.L BP.L JD.L ABF.L AAL.L ADML.L RTO.L RMV.L SBRY.L PHNX.L FLTRF.L IHG.L BT.L MNDI.L BATS.L PSON.L CRH.L SPX.L PSN.L RIO.L JMAT.L CCH.L RR.L SN.L BMEB.L SSE.L SMT.L FRAS.L HRGV.L KGF.L LLOY.L NWG.L SDR.L NXT.L ITRK.L BDEV.L SHEL.L GLEN.L VOD.L BARC.L FCIT.L UTG.L BAES.L DCC.L ULVR.L EDV.L OCDO.L LSEG.L TSCO.L LGEN.L
Timestamp                                                                                                                                                                                                        
01/06/2022 13:00 636.8 <NA> <NA> 2785 652.2 2855 526.506 129.2256 <NA> 1570 10492 535.4 <NA> 937.2 <NA> <NA> 1096 1256.5 130.95 4115 2198 <NA> 80.0871 <NA> 4253 306.7 1127.47 <NA> 3221 1620.5 134.6 924.8 1811.5 587.6 429.8 <NA> 3662.36 545 <NA> <NA> <NA> 2721 1022.467 1811 2602 1734.5 969.5 <NA> 1745.186 <NA> 488 433.308 123.9 1730 3869 <NA> 500.4 588.2 <NA> <NA> 9450 <NA> 189.9 1546.5 3568.5 759.4 <NA> 10520 <NA> 5781 2139 1729.5 89.1448 <NA> 376.1799 1786 812.8979 699.5 846.6 264.5 45.625 <NA> <NA> 6504 4612 <NA> 2370 515.3862 127.26 171.36 <NA> 1135 777.16 5606 3759.7 1794 <NA> 7252.004 260.87 258.3
01/06/2022 13:01 636.8 6884 <NA> 2784 <NA> 2854 526.6 128.74 2242 <NA> 10492 535.5 217.8 937.4 <NA> <NA> <NA> <NA> 130.9 4114 <NA> 1251.573 80.14 <NA> 4256 306.768 1127.35 <NA> 3223 1619.5 134.55 <NA> 1811.5 587.8 430.1 <NA> 3663.5 545.2 1049.61 <NA> <NA> <NA> 1022 1810.5 2605.347 1733.5 <NA> <NA> 1746.398 770.4 <NA> 433.5681 123.9 <NA> 3868 <NA> 501 589.2 230 <NA> 9442 <NA> 189.716 1547 3566 <NA> 3272.5 10525 <NA> 5781 2140 <NA> 89.18 <NA> 376.3712 1786.5 <NA> 700.5 846.6 <NA> 45.6034 231.9109 <NA> <NA> 4613 <NA> 2371 515.0922 <NA> 171.42 <NA> 1136 777.5818 5608 3760.406 1793 920 7253.768 260.8942 258.4
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
01/06/2022 15:28 632.4 6780 1499 2788 652.8 2836 521.2 126.64 2207 1558.5 10428 528.7 213.8 923.8 <NA> 154.2 1084.5 1242.5 129.65 4029 2190 1241 79.92 216.3931 4238 302.8 1119 6168 3184 1612 132.95 912.8 1770 586.4 426.95 1459 3628 534.2 1044.75 2560 <NA> 2642 1015.5 1794.5 2580 1705.5 <NA> 193.55 1722.763 766.6 478.8 431.5 122.45 1706 3850.5 2152.507 495.2 588.2 227.9 632.1798 9260.797 4827 188.71 1533.5 3541.5 749.2 3231 10455 2178 5754 2109 1706 88.53 1271 365.4757 1764.8 793.6 700.5 837 262 44.953 227.5902 491.64 6448 4557.56 500.2 2360.5 514.2102 126.383 167.64 832 1114 778 5606 3700.5 1804 909.4 7146 259 255
01/06/2022 15:29 632.4 6782 1500 2793 652.4 2836 520.6 126.54 2206 1558.5 10424 528.4 214 923.8 768.4 154 1084 1242 129.65 4027 2188 1241 80.02 216.2 4238 302.3 1118 6172 3186 1611.5 132.8 912.8 1772 586.4 426.7 1459.5 3625 533.8 1044 2560 <NA> 2639 1015 1794.5 2577 1703 961 193.4 1722.763 766.8 478.8 431.6 122.45 1704.5 3846 2152.507 495 587.6 228.1 632 9254 4825 188.7 1533.5 3540.5 748.4 3234.5 10465 2175 5756 2111 1707.5 88.51 1271.5 367.4828 1764 796.2 701 835.4 262 44.9 227.5902 491.98 6456 4553 499.8 2361.5 513.9162 126.28 167.52 832 1116 777.6 5600 3695.5 1805 910.4 7138 259 254.6

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:

    	
            

#define stream

stream = rd.open_pricing_stream(

    universe=['GBP=', 'EUR=', 'JPY=', '.GDAXI', '.FTSE', '.NDX', 'TRI.TO', 'EURGBP=R'],

    fields=['CF_TIME', 'CF_LAST', 'BID', 'ASK', 'TRDTIM_1'])

 

#open stream

stream.open()

<OpenState.Opened: 'Opened'>

    	
            

#add temporal update functionality using stream.get_snapshot 

now = time.perf_counter()

while time.perf_counter() < now + 30:

    time.sleep(3)

    clear_output(wait=True)

    df = stream.get_snapshot(

        universe=['GBP=', 'EUR=', 'JPY=', '.GDAXI', '.FTSE', '.NDX', 'TRI.TO', 'EURGBP=R'], 

        fields=['CF_TIME', 'CF_LAST', 'BID', 'ASK', 'TRDTIM_1'])

    display(df)

  Instrument CF_TIME CF_LAST BID ASK TRDTIM_1
0 GBP= 11:44:36 1.1983 1.1983 1.1987 <NA>
1 EUR= 11:44:36 1.0657 1.0657 1.0661 <NA>
2 JPY= 11:44:37 136.02 136.02 136.03 <NA>
3 .GDAXI 11:44:00 15677.13 <NA> <NA> 11:44:00
4 .FTSE 11:44:00 7948.93 <NA> <NA> 11:44:00
5 .NDX 22:15:59 12302.48 <NA> <NA> <NA>
6 TRI.TO 21:00:00 165.81 162.61 167 <NA>
7 EURGBP=R 11:44:37 0.889 0.889 0.8897 <NA>

Close the stream

    	
            stream.close()
        
        
    

<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).

    	
            

df1 = rd.get_data(

    universe=['BARC.L', 'TRI.N', 'TSLA.O'],

    fields=['TR.RevenueMean.date', 'TR.RevenueMean', 'TR.TRBCEconomicSector',

            'TR.TRBCEconSectorCode', 'TR.TRBCBusinessSector',

            'TR.TRBCBusinessSectorCode', 'TR.TRBCIndustryGroup',

            'TR.TRBCIndustryGroupCode', 'TR.TRBCIndustry', 'TR.TRBCIndustryCode'])

df1

  Instrument Date Revenue - Mean TRBC Economic Sector Name TRBC Economic Sector Code TRBC Business Sector Name TRBC Business Sector Code TRBC Industry Group Name TRBC Industry Group Code TRBC Industry Name TRBC Industry Code
0 BARC.L 06/03/2023 26185926370 Financials 55 Banking & Investment Services 5510 Banking Services 551010 Banks 55101010
1 TRI.N 22/02/2023 6937020650 Industrials 52 Industrial & Commercial Services 5220 Professional & Commercial Services 522030 Professional Information Services 52203070
2 TSLA.O 05/03/2023 1.03134E+11 Consumer Cyclicals 53 Automobiles & Auto Parts 5310 Automobiles & Auto Parts 531010 Auto & Truck Manufacturers 53101010

 

 

 

 

 

 

 

 

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.

    	
            

df1 = rd.get_data(

    universe=['BARC.L', 'TRI.N', 'TSLA.O', Peers('HD'), Customers],

    fields=[

        'TR.RevenueMean.date', 'TR.RevenueMean',

        'TR.TRBCEconomicSector', 'TR.TRBCEconSectorCode', 'TR.TRBCBusinessSector',

        'TR.TRBCBusinessSectorCode', 'TR.TRBCIndustryGroup', 'TR.TRBCIndustryGroupCode',

        'TR.TRBCIndustry', 'TR.TRBCIndustryCode'],

    parameters={'SDate': 0, 'EDate': -3, 'FRQ': 'FY'}

)

df1

  Instrument Date Revenue - Mean TRBC Economic Sector Name TRBC Economic Sector Code TRBC Business Sector Name TRBC Business Sector Code TRBC Industry Group Name TRBC Industry Group Code TRBC Industry Name TRBC Industry Code
0 BARC.L 06/03/2023 26185926370 Financials 55 Banking & Investment Services 5510 Banking Services 551010 Banks 55101010
1 BARC.L 13/02/2023 25107439220                
2 BARC.L 11/02/2022 21896182240                
3 BARC.L 28/01/2021 21603248110                
4 TRI.N 22/02/2023 6937020650 Industrials 52 Industrial & Commercial Services 5220 Professional & Commercial Services 522030 Professional Information Services 52203070
5 TRI.N 01/02/2023 6626869820                
6 TRI.N 07/02/2022 6311529500                
7 TRI.N 22/02/2021 5980789530                
8 TSLA.O 05/03/2023 1.03134E+11 Consumer Cyclicals 53 Automobiles & Auto Parts 5310 Automobiles & Auto Parts 531010 Auto & Truck Manufacturers 53101010
9 TSLA.O 25/01/2023 81715341140                
10 TSLA.O 25/01/2022 52595085190                
11 TSLA.O 27/01/2021 31012329500                
    	
            

# The below in this cell is needed to forward fill our dataframe correctly:

df1.replace({'': np.nan}, inplace=True)

df1.where(pd.notnull(df1), np.nan, inplace=True)

 

for i in df1.groupby(by=["Instrument"]):

    if i[0] == df1["Instrument"][0]: _df1 = i[1].ffill()

    else: _df1 = _df1.append(i[1].ffill())

_df1

  Instrument Date Revenue - Mean TRBC Economic Sector Name TRBC Economic Sector Code TRBC Business Sector Name TRBC Business Sector Code TRBC Industry Group Name TRBC Industry Group Code TRBC Industry Name TRBC Industry Code
0 BARC.L 06/03/2023 26185926370 Financials 55 Banking & Investment Services 5510 Banking Services 551010 Banks 55101010
1 BARC.L 13/02/2023 25107439220 Financials 55 Banking & Investment Services 5510 Banking Services 551010 Banks 55101010
... ... ... ... ... ... ... ... ... ... ... ...
10 TSLA.O 25/01/2022 52595085190 Consumer Cyclicals 53 Automobiles & Auto Parts 5310 Automobiles & Auto Parts 531010 Auto & Truck Manufacturers 53101010
11 TSLA.O 27/01/2021 31012329500 Consumer Cyclicals 53 Automobiles & Auto Parts 5310 Automobiles & Auto Parts 531010 Auto & Truck Manufacturers 53101010
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

    	
            

df2 = rd.get_history(

    universe=['BARC.L', 'TRI.N','TSLA.O'],

    fields=['TR.IR.RatingSourceDescription', 'TR.IR.RatingSourceType',

            'TR.IR.Rating','TR.IR.Rating.date'],

    interval="1Y",

    start="2015-01-25",

    end="2022-02-01")

 

df2

  BARC.L TSLA.O
  Rating Source Description Rating Source Type Issuer Rating Date Rating Source Description Rating Source Type Issuer Rating Date
Date                
16/07/2015 <NA> <NA> <NA> NaT <NA> <NA> <NA> NaT
19/11/2015 Fitch Senior Unsecured FSU A 19/11/2015 <NA> <NA> <NA> NaT
19/11/2015 Fitch Short-term Debt Rating FDT F1 19/11/2015 <NA> <NA> <NA> NaT
16/08/2016 <NA> <NA> <NA> NaT <NA> <NA> <NA> NaT
12/12/2016 Moody's Long-term Issuer Rating MIS Baa2 12/12/2016 <NA> <NA> <NA> NaT
12/12/2016 Moody's Long-term Senior Unsecured MTN Rating MMU (P)Baa2 12/12/2016 <NA> <NA> <NA> NaT
22/10/2021 <NA> <NA> <NA> NaT S&P Senior Unsecured SSU BB+ 22/10/2021
26/11/2021 R&I Long-term Issuer Rating RII A 26/11/2021 <NA> <NA> <NA> NaT
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.

    	
            

df2 = rd.get_history(

    universe=['BARC.L', 'TRI.N', 'TSLA.O'],

    fields=['TR.RecEstValue', 'TR.TPEstValue', 'TR.EPSEstValue'],

    interval="1M",

    start="2020-01-25",

    end="2022-02-01")

df2

  BARC.L TRI.N TSLA.O
  Standard Rec (1-5) - Broker Estimate Target Price - Broker Estimate Earnings Per Share - Broker Estimate Standard Rec (1-5) - Broker Estimate Target Price - Broker Estimate Earnings Per Share - Broker Estimate Standard Rec (1-5) - Broker Estimate Target Price - Broker Estimate Earnings Per Share - Broker Estimate
Date                  
02/10/2013 00:00 2 <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA>
02/10/2013 00:00 2 <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA>
... ... ... ... ... ... ... ... ... ...
31/01/2022 21:05 <NA> <NA> <NA> <NA> <NA> <NA> <NA> 326.66633 4.28333
31/01/2022 23:00 <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA> 4.84999

 

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 (RDRDP 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