For a deeper look into our Eikon Data API, look into:

Overview |  Quickstart |  Documentation |  Downloads |  Tutorials |  Articles

question

Upvotes
Accepted
16 1 1 3

From ISIN to "parent RIC" to get lasts 3 years return on assets - python EIKON API

Hi,

How do I go from a bond ISIN to the RIC of the company that has issued that particular bond so I get get financial data?

eikoneikon-data-apiworkspaceworkspace-data-apirefinitiv-dataplatform-eikonpython
icon clock
10 |1500

Up to 2 attachments (including images) can be used with a maximum of 5.0 MiB each and 10.0 MiB total.

Upvotes
Accepted
39.2k 75 11 27

This is not a trivial task, since the relationship between debt issuer and public parent is not always straightforward.
In the simplest case where the bond issuer is a public company, which you can check using TR.IsPublic field, you can retrieve the issuer organization ID and use it to retrieve company financials.

tmp_df, err = ek.get_data('US345370BV11',['TR.IsPublic','TR.OrganizationID'])
if tmp_df.iloc[0,1]=='True':
    df, err = ek.get_data(str(tmp_df.iloc[0,2]),['TR.TotalAssets'])
    print(df) 

Or if you prefer you can get the RIC for the primary stock quote of a public company using its organization PermID returned by TR.OrganizationID field, e.g.

ek.get_data(str(tmp_df.iloc[0,2]),['TR.PrimaryQuote'])

If bond issuer is not a public company, you may check if the ultimate parent is public.

tmp_df1, err = ek.get_data('US89235MGA36',['TR.UltimateParentID'])
tmp_df2, err = ek.get_data(str(tmp_df1.iloc[0,1]),
                           ['TR.IsPublic','TR.OrganizationID'])
if tmp_df2.iloc[0,1]=='True':
    df, err = ek.get_data(str(tmp_df2.iloc[0,2]),
                          ['TR.PrimaryQuote','TR.TotalAssets'])
    print(df)


There are cases where public company is not the ultimate parent. The ultimate parent may be a private company that has controlling interest in a public company. In this case you may need to get the organization ID of an immediate parent of the bond issuer using TR.ImmediateParentID field and traverse the ownership chain until you hit a public company.

isin = 'US747133CB01'
tmp_df, err = ek.get_data(isin, ['TR.IsPublic',
                                'TR.OrganizationID','TR.ImmediateParentID'])
while (tmp_df.iloc[0,1] != 'True' and tmp_df.iloc[0,2] != tmp_df.iloc[0,3]):
    print(tmp_df.iloc[0,0])
    tmp_df, err = ek.get_data(str(tmp_df.iloc[0,3]), ['TR.IsPublic',
                                                      'TR.OrganizationID',
                                                      'TR.ImmediateParentID'])
if tmp_df.iloc[0,1]=='True':
    df, err = ek.get_data(str(tmp_df.iloc[0,2]),
                          ['TR.PrimaryQuote','TR.TotalAssets'])
    print(df)
else:
    print('There is no public parent company for the issuer of ' + isin)

And then there's a case where public borrower may not have parent/child relationship with private debt issuer. This is typical for debt issued through special purpose vehicles. In this case you can query TR.BorrowerOrgID field and see if the borrower is a public company.

tmp_df1, err = ek.get_data('XS0191754729','TR.BorrowerOrgID')
if pd.isnull(tmp_df1.iloc[0,1]):
    print('The borrower is the same as the issuer or unknown')
else:
    tmp_df2, err = ek.get_data(str(tmp_df1.iloc[0,1]),'TR.IsPublic')
    if tmp_df2.iloc[0,1]=='True':
        df = ek.get_data(str(tmp_df1.iloc[0,1]),
                         ['TR.PrimaryQuote','TR.TotalAssets'])
        print(df)
icon clock
10 |1500

Up to 2 attachments (including images) can be used with a maximum of 5.0 MiB each and 10.0 MiB total.

Upvotes
16 1 1 3

Thank you very much, that was really helpful.

Two new questions:

How do I go the other way: If have have a list of RICs how do I extract which bonds these companies have issued?

Is it correct that the SCREENER (python EIKON API) only works for equities and not fixed income?

thx

Michael

icon clock
10 |1500

Up to 2 attachments (including images) can be used with a maximum of 5.0 MiB each and 10.0 MiB total.

1. You can do it using RSearch library of Eikon COM APIs. But I cannot think of a good way of doing this using Eikon Data APIs currently. You could try the following, but I don't think it reliably returns all the results.

ek.get_data('F',['TR.BondISIN'])
You may also want to contact Refinitiv Helpdesk and see if they can offer an alternative workflow for retrieving all bonds issued by a company using =TR function in Excel.

2. Correct. The Screener does not cover fixed income.

Click below to post an Idea Post Idea