Which Python API can be used to retrieve ALL underwriters of a bond

r.fernandez

I am looking for a method to retrieve all underwriters of a specific bond using a python API.

Within the Refinitiv Workspace, you can open a bond and retrieve a set of multiple underwriters, as exampel

Following the article Debt Structure Analysis on an Organizational Level, I constructed the following Python code:

import refinitiv.data as rd
from refinitiv.data.content import search

# opens session with refinitiv-data.config.json
rd.open_session()

organization = "4295885383"  # YARA International ASA

# Fields
fields = [
    # basic bond data
    "PermID",
    "ISIN",
    "IssueDate",
    "Currency",
    "FaceIssuedTotal",

    # underwriter fields
    "UnderwriterCode",
    "UnderwriterID",
    "UnderwriterName",
    "UnderwriterSetupDate",
    "UnderwriterAmount",
]

df = search.Definition(
    view=search.Views.GOV_CORP_INSTRUMENTS,
    filter=f"ParentOAPermID eq '{organization}' and IsActive eq true",
    select=",".join(fields),
).get_data()

data = df.data.df
print(data)

The result is per obligation the underwriter, as such:

However, you can see that for obligation permID NO0010727993, there is only 1 underwriter, but in the Workspace you see 3 underwriters.

Another way to search for this field is using the data Item Browser. Searching for the permID you see the fields can be called: "TR.Underwriter", "TR.UnderwriterCode", "TR.UnderwriterRankDescription", "TR.UnderwriterAmount", "TR.FIUseOfProceedsAmount".

However, these fields are not known in the search engine. I retrieve the error: "Error code 221 | The access to field(s) denied".

Alternative, I can use the followings calls: "fundamental_and_reference.Definition", "rd.get_history" and "rd.get_data". They all give errors and no data.

As such, My question is:

What Python API should I use, and how do I set this up, to retrieve ALL underwriters of a obligation.

jonathan.legrand

Hi @r.fernandez

I used the `SearchPropertyExplorer` module in the LDL, and found that the "UnderwriterCode" property is unfortunately not navigable:

# pip install lseg.data
import lseg.data as ld
ld.open_session()
props = ld.discovery.SearchPropertyExplorer.get_properties_for(
    view = ld.discovery.Views.GOV_CORP_INSTRUMENTS,
    query="NO0010727993")
props.get_by_name("under").df

This means that the Search API will not suffice in returning all values for this property. An example of how you may do so for fields that are navigatable would be:

props0 = ld.discovery.SearchPropertyExplorer.get_properties_for(
    view = ld.discovery.Views.GOV_CORP_INSTRUMENTS,
    query = "LSEG bonds",
    # navigators = "RCSIssuerCountry, RCSIssuerCountryLeaf",
    # filter = "IsPerpetualSecurity ne true and IsActive eq true and not(AssetStatus in ('MAT' 'DC'))"
    )
props0.get_by_name("IssuerCountry").df

You can even use the `get_possible_values` function, e.g. with

props2 = ld.discovery.SearchPropertyExplorer.get_properties_for(
    view = ld.discovery.Views.GOV_CORP_INSTRUMENTS,
    query = "LSEG bonds",
    navigators = "RCSIssuerCountryLeaf")
rcs_issuer_country_leaf = props2.properties["RCSIssuerCountryLeaf"].get_possible_values()
rcs_issuer_country_leaf.df

In your case, you will need to go through a 2nd step, calling for the data you're after:

ld.get_data(
    universe="NO0010727993",
    fields = [
        "TR.Underwriter"])

| Instrument | Underwriter Name |

|------------------------|----------------------------|

| N000101727993 | DANSKE BANK A/S |

| N000101727993 | NORDEA BANK AB |

| N000101727993 | DNB ASA |