Is there a more efficient method to identify the necessary RIC Code to get Dividend Forecast

Marc_Belen24

The initial Query is; Salesforce Case: 14975215

The following securities have Projected Dividends listed on the front end of the Workspace, however, they are not present in the Refinitiv API when we request them

Instruments

ADSGnDIVCF.F

TURUDIVCF.L

BASFnDIVCF.F

BAYGnDIVCF.F

BRKbDIVCF.U

DTGGeDIVCF.F

DTEGnDIVCF.F

FREGDIVCF.F

HAVASDIVCF.AS

LINDIVCF.D

MANTADIVCF.HE

MBGnDIVCF.F

SAPGDIVCF.F

VOWG_pDIVCF.F

Fields

['ROW80_1',

'ROW80_2',

'ROW80_3',

'ROW80_4',

'ROW80_7',

'ROW80_8',

'ROW80_9',

'ROW80_10',

'ROW80_11',

'ROW80_12',

'ROW80_13',

'ROW80_14',

'ROW80_15',

'ROW80_16',

'ROW80_17',

'ROW80_18',

'ROW80_19',

'ROW80_20',

'ROW80_21',

'ROW80_22',

'ROW80_23',

'ROW80_24',

'ROW80_25',

'ROW8_26',

'ROW80_27',

'ROW80_28',

'ROW80_29',

]

df, err = ek.get_data(instruments = list(instruments, fields = Fields)

The row is empty in the API return for the instruments requested, but in the Workspace front-end we can see there are forecasted dividends.

+++++++++++++++++++++++++

We have found that the reason why they could not capture the correct dividend forecast page from Workspace of a particular company - the RIC instrument code they were

using is incorrect and does not correspond to the correct page code in Workspace Quote

it seems the one we have on the list has invalid suffixes on which is the reason why it is not being captured correctly in the API.

Hence, we crossed checked all of the instruments and the company behind it. Then, we changed the suffix of some of them. For example;

BAYGnDIVCF.F changed to > BAYGnDIVCF.DE

Please see this code below for your reference:

import refinitiv.data as rd

rd.open_session()

df = rd.get_data(

universe = ['BAYGnDIVCF.DE','ADSGnDIVCF.DE','DTGGeDIVCF.DE','TURUDIVCF.L','BASFnDIVCF.DE','BRKbDIVCF.N','DTEGnDIVCF.DE','FREGDIVCF.DE','HAVASDIVCF.AS','LINDIVCF.U','MANTADIVCF.HE','MBGnDIVCF.DE','SAPGDIVCF.DE','VOWG_pDIVCF.DE'],

fields = [

'ROW80_7',

'ROW80_8',

'ROW80_9',

'ROW80_10'

]

)

display(df)

++++++++

The LOGIC on how the client is extracting the Dividend Forecast is:

Using a sample SEDOL

> 5069211

Extract the RIC code using TR.RIC > which Returns BAYGn.F

BAYGn.F gets transformed into BAYGnDIVCF.F (as we’ve always done) to get the Dividend Forecast.

++++++++++++++++++++++++++

As a workaround, we advised to the client in order for him to correctly identify the instrument code is by using TR.PrimaryRIC to add in the field alongside the TR.RIC

++++++++++++++++++++++++++

Questions:

The client was able to do a workaround which he shared thru a notebook copy of the python code they are using

This includes how the DIVCF code is derived (block 5) that is used to retrieve the forecasted dividends; it is spliced together based on input we received from Eikon support many years ago.

I’ve added a new condition (commented out) which uses .DE for German securities – something we’ve not had to do before this week. Without it, the German securities are not producing the data we need anymore. I’ve included a US and Germany security in the script for comparison.

Is there a more efficient method to identify the necessary instrument code required to get the Dividend Forecast fields?

Also, why are they called “ROW80_?”

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

> Here is the Workaround the client was able to come-up:

import os
import pandas as pd

import eikon as ek
ek.set_app_key(os.getenv('EIKON_API_KEY'))

sedol=['4942904', '2838555']

Fields, err = ek.get_data(instruments = sedol, fields = ["TR.RIC", "TR.ExchangeCountry", "TR.PrimaryRIC"])
Fields["DividendQuote"]="DivQuote"
display(Fields)

for i in range(0,len(Fields)):

if type(Fields.iloc[i]["Country of Exchange"])==pd._libs.missing.NAType or Fields.iloc[i]["RIC"]=="":

    Fields.loc[i,'DividendQuote']="Missing"


elif Fields.loc[i,'Country of Exchange']=="United States of America":

    Fields.loc[i,'DividendQuote']=Fields.loc[i,'RIC'].split('.')[0]+'DIVCF'+'.U'     


# New condition to use DE for German securities

#elif Fields.loc[i,'Country of Exchange']=="Germany":

#    Fields.loc[i,'DividendQuote']=Fields.loc[i,'RIC'].split('.')[0]+'DIVCF'+'.DE'     


else:

    if '.' in Fields.loc[i,'RIC']:

        Fields.loc[i,'DividendQuote']=Fields.loc[i,'RIC'].split('.')[0]+'DIVCF'+'.'+Fields.loc[i,'RIC'].split('.')[1]  

    else:

        Fields.loc[i,'DividendQuote']=Fields.loc[i,'RIC']

Fields.index=Fields['Instrument']
display(Fields)

divfields = ['ROW80_1'
'ROW80_2'
'ROW80_3'
'ROW80_4'
'ROW80_7'
'ROW80_8'
'ROW80_9'
'ROW80_10'
'ROW80_11'
'ROW80_12'
'ROW80_13'
'ROW80_14'
'ROW80_15'
'ROW80_16'
'ROW80_17'
'ROW80_18'
'ROW80_19'
'ROW80_20'
'ROW80_21'
'ROW80_22'
'ROW80_23'
'ROW80_24'
'ROW80_25'
'ROW80_26'
'ROW80_27'
'ROW80_28'
'ROW80_29'
]

df, err = ek.get_data(instruments = list(Fields["DividendQuote"]), fields = divfields)
display(df)

display(df)

Jirapongse

@Marc_Belen24

Thank you for reaching out to us.

It looks like to be a content question. You need to check with the content support how this RIC (BAYGnDIVCF.DE) was created.

Otherwise, you need to check if there are other TR.XXX fields that can be used to get BAYGnDIVCF.DE.

Jirapongse

I can not confirm which method is more effective.

The current logic has two conditions. One condition is for "United States of America" and another one is for "Germany", I think that it will work for this use case.

The LSEG Data Library for Python provides the Lookup API. I tested it and found that It can be used to get Dividend Forecast RICs, as shown below.

sedol=['4942904', '2838555']
response = search.lookup.Definition(
    view=search.Views.SEARCH_ALL,                              
    scope="SEDOL",                                                  
    terms=",".join(sedol),
    select="RicRoot",               
).get_data()

display(response.data.df)

response.data.df['DividendQuoteRicRoot'] = response.data.df['RicRoot']+'DIVCF'
display(response.data.df)

response1 = search.lookup.Definition(
    view=search.Views.SEARCH_ALL,                              
    scope="RicRoot",                                                  
    terms=",".join(response.data.df['DividendQuoteRicRoot'].tolist()),
    select="RIC",               
).get_data()

display(response1.data.df)

The method is:

1. Using a lookup API to get RICRoots for those SEDOLs
2. Appending the RICRoots with 'DIVCF'
3. Using a lookup API to get RICs from the appended RICRoots

The lookup example is available on GitHub. However, please contact the content team to verify if this method is valid.