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:

DEX2.dll

 

Prerequisites

 

COM Prerequisites

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.

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

 

    	
            

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'}>

 

 

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
2 BARC.L 11/02/2022 21896182240 Financials 55 Banking & Investment Services 5510 Banking Services 551010 Banks 55101010
3 BARC.L 28/01/2021 21603248110 Financials 55 Banking & Investment Services 5510 Banking Services 551010 Banks 55101010
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 Industrials 52 Industrial & Commercial Services 5220 Professional & Commercial Services 522030 Professional Information Services 52203070
6 TRI.N 07/02/2022 6311529500 Industrials 52 Industrial & Commercial Services 5220 Professional & Commercial Services 522030 Professional Information Services 52203070
7 TRI.N 22/02/2021 5980789530 Industrials 52 Industrial & Commercial Services 5220 Professional & Commercial Services 522030 Professional Information Services 52203070
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 Consumer Cyclicals 53 Automobiles & Auto Parts 5310 Automobiles & Auto Parts 531010 Auto & Truck Manufacturers 53101010
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

DEX2

What does this do?

The DEX2 interface provided a broad range of content and functionality such Fundamental and Reference as well as timeseries histories of such fields. The DEX2 interface also provided interday timeseries histories for both realtime and non-realtime data. In addition you could use it for symbology conversion.

DEX2 ID to RIC (Symbology Conversion)

As shown in Tutorial 6 - Data Engine - Dex2's Excel Workbook, we can convert symbols using the COM API.

VBA

After setting MyDex2Mgr = CreateReutersObject("Dex2.Dex2Mgr"), for ISINs, we can then go ahead with

' Must call Initialise() at once
m_cookie = MyDex2Mgr.Initialize()
' We can then create an RData object
Set myRData1 = MyDex2Mgr.CreateRData(m_cookie)


With MyDex2Mgr .SetErrorHandling m_cookie, DE_EH_STRING ' Could also use DE_EH_ERRORCODES End With
' Set the properties & methods for the DEX2Lib.RData object With myRData1 .InstrumentIDList = Range("B7").Value ' Could use "TRI.N", "TRI.N; GOOG.O; MSFT.O" or array, e.g. arrInstrList. .FieldList = Range("D7").Value & "; " & Range("D8").Value '"RI.ID.RIC; RI.ID.ISIN" ' Could use "RI.ID.RIC", "RI.ID.RIC; RI.ID.WERT" or array, e.g. arrFldList. .DisplayParam = "Transpose:Y" 'N.B. Could use .SetParameter "TRI.N; MSFT.O","RF.G.COMPNAME; RF.G.NUMEMPLOY; RF.G.CNTEMAIL", "", "RH:In CH:Fd Transpose:Y" 'Hence .SetParameter [InstrumentIDList].Value, [FieldList].Value, [RequestParam].Value, [DisplayParam].Value
'Ignore cache; get data directly from the Snapshot Server '.Subscribe False 'Or use cache by default .Subscribe End With Exit Sub

For SEDOL, we can use:

' Must call Initialise() at once
m_cookie = MyDex2Mgr.Initialize()
' We can then create an RData object
Set myRData2 = MyDex2Mgr.CreateRData(m_cookie)


With MyDex2Mgr .SetErrorHandling m_cookie, DE_EH_STRING ' Could also use DE_EH_ERRORCODES End With
' Set the properties & methods for the DEX2Lib.RData object With myRData2 .InstrumentIDList = [B10].Value ' Could use "TRI.N", "TRI.N; GOOG.O; MSFT.O" or array, e.g. arrInstrList. .FieldList = Range("D10").Value & "; " & Range("D11").Value '"RI.ID.RIC; RI.ID.SEDOL" ' Could use "RI.ID.RIC", "RI.ID.RIC; RI.ID.WERT" or array, e.g. arrFldList. .DisplayParam = "Transpose:Y" 'N.B. Could use .SetParameter "TRI.N; MSFT.O","RF.G.COMPNAME; RF.G.NUMEMPLOY; RF.G.CNTEMAIL", "", "RH:In CH:Fd Transpose:Y" 'Hence .SetParameter [InstrumentIDList].Value, [FieldList].Value, [RequestParam].Value, [DisplayParam].Value
'Ignore cache; get data directly from the Snapshot Server '.Subscribe False 'Or use cache by default .Subscribe End With Exit Sub

Python

Symbology conversions in python are a lot more powerful than in Excel using the COM API. The extent of the use-cases for symbology conversions are best explained in our GitHub Repository, but for the sake of completeness, you can see an example use in the 2 cells directly below:

    	
            

# # Get generic table of many symbols for 4 companies of choice:

from refinitiv.data.content import symbol_conversion

response = symbol_conversion.Definition(symbols=["MSFT.O", "AAPL.O", "GOOG.O", "IBM.N"]).get_data()

response.data.df

  DocumentTitle RIC IssueISIN CUSIP SEDOL TickerSymbol IssuerOAPermID
MSFT.O Microsoft Corp, Ordinary Share, NASDAQ Global ... MSFT.O US5949181045 594918104 <NA> MSFT 4295907168
AAPL.O Apple Inc, Ordinary Share, NASDAQ Global Selec... AAPL.O US0378331005 37833100 <NA> AAPL 4295905573
GOOG.O Alphabet Inc, Ordinary Share, Class C, NASDAQ ... GOOG.O US02079K1079 02079K107 <NA> GOOG 5030853586
IBM.N International Business Machines Corp, Ordinary... IBM.N US4592001014 459200101 2005973 IBM 4295904307
    	
            

# # Convert specific symbols:

response = symbol_conversion.Definition(

    symbols=["US5949181045", "US02079K1079"],

    from_symbol_type= symbol_conversion.SymbolTypes.ISIN,  # Symbol types: RIC => RIC; ISIN => IssueISIN; CUSIP => CUSIP; SEDOL => SEDOL; TICKER_SYMBOL => TickerSymbol; OA_PERM_ID => IssuerOAPermID; LIPPER_ID => FundClassLipperID

    to_symbol_types=[symbol_conversion.SymbolTypes.RIC,

                     symbol_conversion.SymbolTypes.OA_PERM_ID],

).get_data()

 

response.data.df

DocumentTitle RIC IssuerOAPermID  
US5949181045 Microsoft Corp, Ordinary Share, NASDAQ Global ... MSFT.O 4295907168
US02079K1079 Alphabet Inc, Ordinary Share, Class C, NASDAQ ... GOOG.O 5030853586

DEX2 RData Equity Data

As best exemplified in Tutorial 6 - Data Engine - Dex2's Excel Workbook, the DEX2 RData Equity Data allows us to access historical Market Capital:

VBA

DEX2 replicates RData() and TR() functions for real time data retrieval in Eikon for Excel / Eikon - Microsoft Office. The DEX2 methods differ slightly in the initialisation and use of the Dex2Mgr object depending upon whether RData() or the TR() function is being replicated. The examples below explain this. The TR() function can be used to retrieve data from ADC (the Analytics Data Cloud) and this is contained in Tutorial 11 - Dex2 TR function Analytics Data Cloud ADC data here.

NOTE - Eikon for Excel or Eikon - Microsoft Office should be logged in otherwise the sample VBA code will generate an error "ERROR #360c - AdxRtx : No connection to the platform".

  1. Create an instance of an DEX2Mgr object using the PLVbaApis function CreateDex2Mgr().

    Set myDEX2Mgr = CreateDex2Mgr()

  2. To replicate RData(), create the Dex2Mgr object (myDex2Mgr) and initialise it. Use the return value from the initialisation to create an instance of the Dex2Lib.RData Class (myDex2RData) using the CreateRData method.

  3. Now go ahead with cmdMktCapHist_Click creation:

Private Sub cmdMktCapHist_Click() On Error GoTo errHandler
ActiveCell.Select
Range("F20:G100").ClearContents If MyDex2Mgr Is Nothing Then Set MyDex2Mgr = CreateReutersObject("Dex2.Dex2Mgr")
' Must call Initialise() at once m_cookie = MyDex2Mgr.Initialize() ' We can then create an RData object Set myRData2 = MyDex2Mgr.CreateRData(m_cookie)
With MyDex2Mgr .SetErrorHandling m_cookie, DE_EH_STRING ' Could also use DE_EH_ERROR_CODES End With
' Set the properties & methods for the DEX2Lib.RData object. With myRData2 .InstrumentIDList = [C15].Value .FieldList = [C16].Value '"RII.A.INDEX_MKT_CAP_RTRS" '"RII.A.INDEX_MKT_CAP_RTRS; RII.A.INDEX_MKT_CAP_USDRTRS" ' Could use single "RI.ID.RIC", multiple "RI.ID.RIC; RI.ID.WERT" or array, e.g. arrFldList. .RequestParam = [C17].Value .DisplayParam = "RH:D CH:Fd SORT:DESC" 'N.B. Could use .SetParameter "TRI.N; MSFT.O","RF.G.COMPNAME; RF.G.NUMEMPLOY; RF.G.CNTEMAIL", "", "RH:In CH:Fd Transpose:Y" 'Hence .SetParameter [InstrumentIDList].Value, [FieldList].Value, [RequestParam].Value, [DisplayParam].Value
'Ignore cache; get data directly from the Snapshot Server '.Subscribe False 'Or use cache by default .Subscribe End With Exit Sub
errHandler: MsgBox MyDex2Mgr.GetErrorString(Err.Number) End Sub
' OnUpdate event callback for myRData1 Private Sub myRData2_OnUpdate(ByVal DataStatus As Dex2Lib.DEX2_DataStatus, ByVal Error As Variant) Dim C As Integer, r As Integer Dim res2 As Variant
If Error <> 0 Then [F21].Value = Error: Exit Sub
res2 = myRData2.Data
If IsEmpty(res2) Then [F21].Value = "No data": Exit Sub
' r for the rows For r = LBound(res2, 1) To UBound(res2, 1) ' c for the columns For C = LBound(res2, 2) To UBound(res2, 2) [F21].Offset(r, C).Value = res2(r, C) Next C Next r End Sub

Python

Couldn't be easier in Python:

    	
            

MarketCapDf = rd.get_history(

    universe=['VOD.L','BARC.L'],

    fields=['TR.CompanyMarketCapitalization'],

    interval="1D", # 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-08-10",

    end="2022-09-13")

MarketCapDf

Company Market Capitalization
VOD.L BARC.L
Date    
10/08/2022 33802223010 27530345491
11/08/2022 33794859479 27383302388
12/09/2022 30687668213 27864039079
13/09/2022 30275827985 27526300175

VBA

You'd have to Initialize Dex2 session under the sub cmdInitialize_Click():

' Initialize Dex2 session when the data request is first started. Private Sub cmdInitialize_Click() On Error GoTo ErrorHandle
ActiveCell.Select
' Clear the output of logger lbLog.Clear
ClearAllOutput
' Clear the Cookie and old query If Not MyDex2Cookie = 0 Then MyDex2Mgr.Finalize (MyDex2Cookie) MyDex2Cookie = 0 Set MyDex2Mgr = Nothing Set MyDex2RData = Nothing End If
' Instantiate the Dex2 manager Set MyDex2Mgr = CreateDex2Mgr() Set MyDex2MgrADC = MyDex2Mgr ' Instantiate the RSearch logger Set MyDex2Logger = New CLogger
' Initialize Dex2 session MyDex2Cookie = MyDex2MgrADC.Initialize(DE_MC_ADC_POWERLINK, MyDex2Logger)
' We can choose to display error code MyDex2Mgr.SetErrorHandling MyDex2Cookie, DE_EH_ERROR_CODES ' Or display error description ' MyDex2Mgr.SetErrorHandling MyDex2Cookie, DE_EH_STRING
' Create a Dex2 query using the session cookie Set MyDex2RData = MyDex2Mgr.CreateRData(MyDex2Cookie)
' Create a Dex2 query manager using the session cookie Set MyDex2RDataMgr = MyDex2Mgr.CreateRDataMgr(MyDex2Cookie)
Exit Sub

Python

In Python, things are a little simpler:

    	
            

DEX2Df = rd.get_history(

    universe=['VOD.L', 'BARC.L', 'TRI.N'],

    fields=['TR.TotalReturn', 'TR.PRICECLOSE'],

    interval="1D",

    start="2021-10-01",

    end="2021-10-11")

 

DEX2Df

  VOD.L BARC.L TRI.N
  Total Return Price Close Total Return Price Close Total Return Price Close
Date            
01/10/2021 -0.970874 112.2 -0.907173 187.88 -0.289645 110.16
04/10/2021 1.4082 113.78 -0.319353 187.28 -0.67175 109.42
05/10/2021 0.773422 114.66 3.908586 194.6 0.923049 110.43
06/10/2021 -2.651317 111.62 -1.387461 191.9 1.213438 111.77
07/10/2021 0.734635 112.44 0.958833 193.74 1.673079 113.64
08/10/2021 -0.302383 112.1 0.732941 195.16 0.228793 113.9
11/10/2021 -0.303301 111.76 1.291248 197.68 0.412643 114.37

VBA

Private Sub cmdClearAll_Click()
ActiveCell.Select

Range("G10").Value = ""
Range("G21:J100").Value = ""

If Not myRData1 Is Nothing Then Set myRData1 = Nothing
If Not myRData2 Is Nothing Then Set myRData2 = Nothing
If Not MyDex2Mgr Is Nothing Then Set MyDex2Mgr = Nothing
End Sub


Private Sub cmdGetRating_Click() On Error GoTo errHandler
ActiveCell.Select
[G7].Value = "" ' Note the use of CreateReutersObject - function in the PLVbaApis module. If MyDex2Mgr Is Nothing Then Set MyDex2Mgr = CreateReutersObject("Dex2.Dex2Mgr")
' Must call Initialise() at once m_cookie = MyDex2Mgr.Initialize() ' We can then create an RData object Set myRData1 = MyDex2Mgr.CreateRData(m_cookie)
With MyDex2Mgr .SetErrorHandling m_cookie, DE_EH_STRING ' Could also use DE_EH_ERRORCODES End With
' Set the properties & methods for the DEX2Lib.RData object With myRData1 .InstrumentIdList = [C6].Value ' Could use "TRI.N", "TRI.N; GOOG.O; MSFT.O" or array, e.g. arrInstrList. .FieldList = [C7].Value '"EJV.GR.Rating" ' Could use "RI.ID.RIC", "RI.ID.RIC; RI.ID.WERT" or array, e.g. arrFldList. .RequestParam = [C8].Value '"RTSRC:S&P" 'N.B. Could use .SetParameter "TRI.N; MSFT.O","RF.G.COMPNAME; RF.G.NUMEMPLOY; RF.G.CNTEMAIL", "", "RH:In CH:Fd Transpose:Y" 'Hence .SetParameter [InstrumentIDList].Value, [FieldList].Value, [RequestParam].Value, [DisplayParam].Value
'Ignore cache; get data directly from the Snapshot Server '.Subscribe False 'Or use cache by default .Subscribe End With Exit Sub
errHandler: MsgBox MyDex2Mgr.GetErrorString(Err.Number) End Sub
' OnUpdate event callback for myRData1 Private Sub myRData1_OnUpdate(ByVal DataStatus As Dex2Lib.DEX2_DataStatus, ByVal Error As Variant) Dim res As Variant
'Debug.Print DataStatus
If Error <> 0 Then [G10].Value = Error: Exit Sub
' get the data retrieved from the database res = myRData1.Data
' Display the result. [G10].Value = res End Sub
Private Sub cmdGetHistoryOfRating_Click() On Error GoTo errHandler
ActiveCell.Select
Range("G21:J100").ClearContents If MyDex2Mgr Is Nothing Then Set MyDex2Mgr = CreateReutersObject("Dex2.Dex2Mgr")
' Must call Initialise() at once m_cookie = MyDex2Mgr.Initialize() ' We can then create an RData object Set myRData2 = MyDex2Mgr.CreateRData(m_cookie)
With MyDex2Mgr .SetErrorHandling m_cookie, DE_EH_STRING ' Could also use DE_EH_ERRORCODES End With
' Set the properties & methods for the DEX2Lib.RData object With myRData2 .InstrumentIdList = [C15].Value .FieldList = [C16].Value .RequestParam = [C17].Value .DisplayParam = [C18].Value
'N.B. Could use .SetParameter "TRI.N; MSFT.O","RF.G.COMPNAME; RF.G.NUMEMPLOY; RF.G.CNTEMAIL", "", "RH:In CH:Fd Transpose:Y" 'Hence .SetParameter [InstrumentIDList].Value, [FieldList].Value, [RequestParam].Value, [DisplayParam].Value
'Ignore cache; get data directly from the Snapshot Server '.Subscribe False 'Or use cache by default .Subscribe End With Exit Sub
errHandler: MsgBox MyDex2Mgr.GetErrorString(Err.Number) End Sub

Python

Things are much simpler in Python:

    	
            

rating = rd.get_history(

    universe=['GB137283921='],

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

            'TR.IR.Rating'],  

    interval="1Y",

    start="2015-01-25",

    end="2023-02-01")

rating

 

GB137283921=

Rating Source Description Rating Source Type Issuer Rating
Date      
17/12/2015 Egan-Jones Commercial Paper EJC A1
17/12/2015 Egan-Jones Senior Unsecured EJU BBB+
06/07/2016 Egan-Jones Senior Unsecured EJU BBB
04/10/2018 Fitch Subordinated FBD BBB-
26/11/2019 Egan-Jones Senior Unsecured EJU BBB-
29/07/2021 Fitch Commercial Paper FCP F2
25/11/2022 Egan-Jones Commercial Paper EJC A1

DEX2 Fundamental and Reference

Time Series data as the name suggests changes with time. Reference data, on the other hand, does not primarily change with time; when it does, it usually accounts for a structural change in the instrument at play (e.g.: a company changes from banking to tech operation industries or is acquired and changes name etc). Fundamental data is released temporally say quarterly or half-yearly or annually and is usually looked at across time eg Revenue or Sales. DEX2 provides access to all of our TR fields - which allow you to specify begin dates and end dates amongst other parameters. Access to these is unchanged in our new APIs - only its a lot simpler.

VBA

In VBA, after creating 'Private MyDex2Mgr As Dex2Lib.Dex2Mgr':

Private MyDex2Mgr As Dex2Lib.Dex2Mgr
Private MyDex2MgrADC As Dex2Lib.IDex2Mgr2
Private MyDex2Cookie As Long
' Private variable that holds the Dex2 RData, able to obtain TR fields.
Private WithEvents MyDex2RData As Dex2Lib.RData

to complete the Tutorial 6 example, one could go through the following to collect DEX2 Fundamental and Reference data:

' Now create the Dex2 and IDex2 objects.
Set MyDex2Mgr = CreateDex2Mgr()
Set MyDex2MgrADC = MyDex2Mgr

' Initialise using the DE_MC_ADC_POWERLINK enumeration
MyDex2Cookie = MyDex2MgrADC.Initialize(DE_MC_ADC_POWERLINK)
' And create an RData object.
Set MyDex2RData = MyDex2Mgr.CreateRData(MyDex2Cookie)

With MyDex2RData
.InstrumentIDList = Range("G6").Value ' Or for multiple isntruments "TRI.N;MSFT.O;GOOG.O"
.FieldList = strInstrList ' Or single field "TR.CLOSE"
.RequestParam = "" ' Or of the form "edate:-20d sdate:-9d"
.DisplayParam = "CH:Fd" ' Or of the form "RH:In CH:Fd"

'' OR can use .SetParameter
'.SetParameter Range("G6").Value, strInstrList, "", "CH:Fd"

' Send the query without using cache
.Subscribe False ' Or use cache by default - myDex2RData.Subscribe

' When the data is returned, the myDex2RData_OnUpdate event and Sub are 'fired'.

Python

Fundamental data was covered above under 'RData Fundamentals'; such data can be found in Python with RD (and rd.get_data, rd.get_history or rd.content.fundamental_and_reference.Definition) or EDAPI.

Fundamental and Reference Timeseries

VBA

DEX2 Fundamental and Reference Timeseries data could be fetched similarly to simple time series data:

Start with

' Private Variable that holds the instance of the Dex2 manager singleton
Private MyDex2Mgr As Dex2Lib.Dex2Mgr
Private MyDex2MgrADC As Dex2Lib.IDex2Mgr2


' Private variable that holds the cookie that identifies the Dex2 session Private MyDex2Cookie As Long ' Private variable that holds the Dex2 RData Private WithEvents MyDex2RData As Dex2Lib.RData ' Private variable that holds the Dex2 RDataMgr Private MyDex2RDataMgr As Dex2Lib.RDataMgr
' Private variable that holds the Dex2 logger Private MyDex2Logger As CLogger

Then initialise clicks:

' Instantiate the Dex2 manager
Set MyDex2Mgr = CreateDex2Mgr()
Set MyDex2MgrADC = MyDex2Mgr
' Instantiate the RSearch logger
Set MyDex2Logger = New CLogger

' Initialize Dex2 session
MyDex2Cookie = MyDex2MgrADC.Initialize(DE_MC_ADC_POWERLINK, MyDex2Logger)

' We can choose to display error code
MyDex2Mgr.SetErrorHandling MyDex2Cookie, DE_EH_ERROR_CODES
' Or display error description
' MyDex2Mgr.SetErrorHandling MyDex2Cookie, DE_EH_STRING

' Create a Dex2 query using the session cookie
Set MyDex2RData = MyDex2Mgr.CreateRData(MyDex2Cookie)

' Create a Dex2 query manager using the session cookie
Set MyDex2RDataMgr = MyDex2Mgr.CreateRDataMgr(MyDex2Cookie)

Then, after some error & event handling:

' Set input values
MyDex2RData.SetParameter _
Range("Dex2Item").Value, _
Range("Dex2Fields").Value, _
Range("Dex2RequestParameters").Value, _
Range("Dex2DisplayParameters").Value
' Or using the following individual properties
' MyDex2RData.InstrumentIDList = Range("Dex2Item").Value
' MyDex2RData.FieldList = Range("Dex2Fields").Value
' MyDex2RData.requestParam = Range("Dex2RequestParameters").Value
' MyDex2RData.displayParam = Range("Dex2DisplayParameters").Value


' Send the query without using cache MyDex2RData.Subscribe (False) ' Or use cache by default ' MyDex2RData.Subscribe

Before creating 'OnUpdate' code.

This is all quite heavy in VBA, while it could hardly be simpler in Python:

Python

This was covered above, in the section 'RData Fundamentals' using RD's rd.get_history function. We can also do this with the rd.get_data function:

    	
            

DEX2TrDf = rd.get_data(

    ['VOD.L', 'MSFT.N', 'TRI.N'],

    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'})

 

DEX2TrDf

  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 VOD.L 14/02/2023 45864975420 Technology 57 Telecommunications Services 5740 Telecommunications Services 574010 Wireless Telecommunications Services 57401020
8 TRI.N 22/02/2023 6937020650 Industrials 52 Industrial & Commercial Services 5220 Professional & Commercial Services 522030 Professional Information Services 52203070
9 TRI.N 01/02/2023 6626869820                
10 TRI.N 07/02/2022 6311529500                
11 TRI.N 22/02/2021 5980789530                

 

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