Batch request bondholders and ultimate parents for multiple bond RICs - python RDP API

l.marsden

Hi there, I am trying to pull the bondholders for several RICs, before finding the ultimate parents of each of these bondholders as well as their identifiers. At the moment, I am doing this RIC by RIC/name by name in a loop. This is resulting in too many API calls to the Refinitiv server, but at least for bondholding data you can only request one RIC at a time. Second, the API is timing out when I then search for ultimate parents for each unique bondholder, and also struggling to handle errors (see below - the universe does exist for this bond so I'm assuming it is a timeout):

## Example errors
RDError: Error code -1 | Backend error. 400 Bad Request Requested universes: ['US252610979=']. Requested fields: ['TR.H.HOLDINGCOMPANYNAME.HOLDINGCOMPANYNAME', 'TR.H.PARHELD', 'TR.H.REPORTDATE'] 

## another example

RDError                                   Traceback (most recent call last)
Cell In[65], line 3
      1 bond_holders = pd.DataFrame()
      2 for ric in bond_rics: # bond_rics defined above
----> 3     df_hold = rd.get_data(ric,['TR.H.HoldingCompanyName.HoldingCompanyName','TR.H.PARHELD','TR.H.REPORTDATE'])
      4     if len(data):
      5         bond_holders = pd.concat([bond_holders,df_hold],axis=0,ignore_index=True)

File ~/anaconda3/envs/eikon/lib/python3.11/site-packages/refinitiv/data/_access_layer/get_data_func.py:126, in get_data(universe, fields, parameters, use_field_names_in_headers)
    124 if exceptions and all(exceptions):
    125     except_msg = "\n\n".join(exceptions)
--> 126     raise RDError(-1, except_msg)
    128 hp_and_cust_inst_data = HPAndCustInstDataContainer(stream_columns, stream_data, stream_df)
    129 adc_data = ADCDataContainer(adc_raw, adc_df, fields)

RDError: Error code -1 | Backend error. 400 Bad Request Requested universes: ['46590XAR7=']. Requested fields: ['TR.H.HOLDINGCOMPANYNAME.HOLDINGCOMPANYNAME', 'TR.H.PARHELD', 'TR.H.REPORTDATE']

I'm quite new to the Python API, so would appreciate help with the following requests:

- Timing/more efficiently managing API calls to pull all the bondholders for the 49 RICs (this is just for one company, so I'd also like to know how to manage these API calls for say 50 companies, cc 2000 RICs?).

- Batch requesting for the second stage, using rdp search for the organisation and its parents, which I think can be done for more general search.

- Error handling when a search returns no result, so that a record of the query is maintained.

Less of an API question, but does anyone have any experience with why records of investors in Bond Search do not always match any organisations within the main part of Refinitiv. And why it is not possible to directly retrieve an investors' PermID or other identifier, only their name?

Thanks in advance and let me know if more information is needed in the code below!

import eikon as ek
import refinitiv.data as rd
from refinitiv.dataplatform import RDPError
import pandas as 
from refinitiv.data.content import search

## Pull all active bonds for one issuer
org = 4295860302 # issuer OAPermID

fi_fields = ['BondRatingLatest', 'IssuerOAPermid','IssuerOrgid','IssuerID','IssuerCommonName','ParentIssuerName', 'ParentOAPermID','IssueRating','IssueRatingSourceCode','BondRatingLatestSourceCode','AssetTypeDescription','DebtTypeDescription','ISIN','MainSuperRIC','DBSTicker','IsGreenBond','IssueDate', 'Currency', 'RCSCurrencyLeaf','FaceIssuedTotal', 'EOMAmountOutstanding', 'NextCallDate','CouponRate','IsPerpetualSecurity','MaturityDate','CdsSeniorityEquivalentDescription','Price', 'RIC']

query = "ParentOAPermID eq '" + str(org) + "' and IsActive eq true" 

bonds_outstanding = rd.discovery.search(view = rd.discovery.Views.GOV_CORP_INSTRUMENTS, 
                filter = query,
                top = 10000,
                select = ','.join(fi_fields))

bonds_outstanding # 49 instruments total

## Pull bondholders for all bonds

bond_holders = pd.DataFrame()
for ric in bond_rics: # bond_rics defined above
    df_hold = rd.get_data(ric,['TR.H.HoldingCompanyName.HoldingCompanyName','TR.H.PARHELD','TR.H.REPORTDATE'])
    if len(data):
        bond_holders = pd.concat([bond_holders,df_hold],axis=0,ignore_index=True)
    else:
        bond_holders = df_hold     

bond_holders # get a 400 error (timeout?)

## Pull permIDs, ultimate parents, and their permIDs for bondholders

df = pd.DataFrame()
for managing_firm in bond_holders['Managing Firm'].unique():
    match = rd.discovery.search(
        view=rd.discovery.Views.ORGANISATIONS,
        query=managing_firm,
        top=1,  # choose best match
        select="LongName, OAPermID, UltimateParentOrganisationName, UltimateParentCompanyOAPermID"
        )
    if len(df):
        df = pd.concat([df,match],axis=0,ignore_index=True)
    else:   
        df = match

## Join tables to have full list of bondholders and their holdings mapped to ultimate parents??

Jirapongse

@l.marsden

Thank you for reaching out to us.

According to the Eikon Data API Usage and Limits Guideline, when a request fails because the platform is overwhelmed, an HTTP response with status code 400 and the message "Backend error. 400 Bad Request" is returned. Then the Python library raises an EikonError exception with the following message.

Therefore, you may try to catch this exception and add the retry logic. For example:

from refinitiv.data.errors import RDError
import time


retry_max = 5
retry_count = 1
bond_rics = bonds_outstanding["RIC"].tolist()


bond_holders = pd.DataFrame()
for ric in bond_rics: # bond_rics defined above
    retry = True
    retry_count = 1
    while retry==True:
        try:
            retry=False
            time.sleep(3)
            df_hold = rd.get_data(ric,['TR.H.HoldingCompanyName.HoldingCompanyName','TR.H.PARHELD','TR.H.REPORTDATE'])
            if len(df_hold):
                bond_holders = pd.concat([bond_holders,df_hold],axis=0,ignore_index=True)
            else:
                bond_holders = df_hold    
        except RDError as err:
            if "Backend error. 400 Bad Request" in err.message:
                retry_count = retry_count + 1
                if retry_count<=retry_max:
                    print("Retry "+ric)
                    retry=True
                else:
                    print("Retry reach max and unable to get the data for "+ric)
            else:
                print(err.code+" "+err.message)


bond_holders # get a 400 error (timeout?)

The code waits for 3 seconds between each request and retries 5 times (retry_max) for each request.

l.marsden

Hi there, thank you for this - this answers my initial question. Are you able to provide guidance on how to batch request in RDP search for the second part of my question? Looking at other answers, it suggests it's not efficient to do a one-by-one search where it is not forced by the nature of the search (i.e., for bonds, only allowed to search RIC by RIC but not sure this is the case for other searches).