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

mbp

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?

Alex Putkov.1

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)