Eikon Data API

API Family: Eikon APIs

Fundamentals API - Rating transition matrix

Download source code from

Last update January 2019
Interpreter Python 2.x/3.x

 

The goal of this tutorial is to demonstrate the Eikon Data API with the focus on the fundamental data retrieval. So, for that purpose we are going to build a rating transition matrix for the current constituents of the S&P 500 Index.

Such matrices are essential for computation of default probabilities and other credit analysis metrics for your portfolios, and also observations of the market in general. I am presenting a simplified model: displaying the count of the companies that we upgraded, downgraded or remained unchanged.

So, in order to build this, we will request the name of the company, the current and the historical issuer ratings.

Before we start, let's make sure that:

  • Eikon (version 4.0.36 or higher) or the Eikon Data API Proxy is up and running;
  • Eikon API library is installed;
  • You have created an App Key for this script.

If you have not yet done this, have a look at the quick start section for this API.

Let's start with referencing Eikon API library and pandas:

import eikon as tr
import pandas as pd

Paste your App Key in to this line:

tr.set_app_key('your_app_key')

The get_data() function supports both individual instrument codes and code chains, so we are going to use 0#.SPX, the S&P 500 Index constituent chain as the instrument argument. As for the fields, will get:

  • the name of the company TR.CommonName;
  • issuer rating from S&P as of now TR.IssuerRating(IssuerRatingSrc=SPI);
  • historical S&P rating TR.IssuerRating(IssuerRatingSrc=SPI,Sdate=-10Y).

You can find these fields and more in the Data Item Browser in your Eikon or the Scripting proxy. The result is formatted as a pandas dataframe.

ratings_data, err = tr.get_data(instruments=['0#.SPX'], 
                                fields=['TR.CommonName',
                                        'TR.IssuerRating(IssuerRatingSrc=SPI)',
                                        'TR.IssuerRating(IssuerRatingSrc=SPI,Sdate=-10Y)'])
ratings_data.head()
  Instrument Company Common Name Issuer Rating Issuer Rating
0 MMM.N 3M Co AA- AA
1 ABT.N Abbott Laboratories BBB AA
2 ABBV.N AbbVie Inc A-  
3 ACN.N Accenture PLC A+ A+
4 ATVI.OQ Activision Blizzard Inc BBB- BB-

Now, let us build a proper readable output. For this, we need to sort ratings by their rank:

sp_pos_by_rating = {'AAA':1,'AA+':2,'AA':3,'AA-':4,'A+':5,'A':6,'A-':7,'BBB+':8,
            'BBB':9,'BBB-':10,'BB+':11,'BB':12,'BB-':13,'B+':14,'B':15,
            'B-':16,'CCC+':17,'CCC':18,'CCC-':19,'CC':20,'C':21,'RD':22,
            'SD':23,'D':24, 'NR':25}

You have noticed that there are some blanks, for instance, for AbbVie Inc, as it was incorporated in 2013 and did not have a rating in 2007. So, let us clear out those values.

We will create a set of all possible values, filter out None values and sort the companies according to their ranking from the previous step:

ratings = set(ratings_data.iloc[:,2].tolist() + ratings_data.iloc[:,3].tolist())
ratings_filtered = filter(None, ratings)
labels = sorted(ratings_filtered, key=lambda r: sp_pos_by_rating.get(r))

Now we will create a dataframe that will show us the matrix and also a nifty lookup dictionary, where the companies in transition will be stored.

result = pd.DataFrame(0, index=labels, columns=labels)
lookup = {}

for index, code, issuer, current_rating, historic_rating in ratings_data.itertuples():
    if (current_rating!='') & (historic_rating!=''):
        result[current_rating][historic_rating] += 1
        lookup.setdefault((historic_rating, current_rating), []).append(issuer)

result
  AAA AA+ AA AA- A+ A A- BBB+ BBB BBB- BB+ BB BB- B+ B B- D NR
AAA 1 1 3 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0
AA+ 0 0 0 1 0 1 0 0 0 0 0 0 0 0 0 0 0 0
AA 0 0 1 3 2 0 0 3 1 0 0 0 0 0 0 0 0 0
AA- 0 0 1 2 2 2 1 2 0 0 0 0 0 0 0 0 0 0
A+ 0 0 0 6 4 11 6 6 0 0 0 0 0 0 0 0 0 0
A 0 0 0 1 7 14 15 10 4 0 0 0 0 0 0 0 0 0
A- 0 0 0 0 0 11 9 6 8 1 1 0 0 0 0 0 0 0
BBB+ 0 0 0 0 0 5 6 18 14 7 0 0 0 0 0 0 0 0
BBB 0 0 0 0 0 2 11 18 11 9 4 4 0 0 0 0 0 1
BBB- 0 0 0 0 1 0 4 6 9 8 2 0 0 0 0 0 0 1
BB+ 0 0 0 0 0 1 0 1 6 5 7 0 1 0 0 0 0 0
BB 0 0 0 0 0 0 0 3 2 3 1 1 0 0 0 1 0 0
BB- 0 0 0 1 0 0 0 0 2 6 3 3 3 1 0 0 0 1
B+ 0 0 0 0 0 0 0 0 1 0 0 2 1 0 0 1 0 0
B 0 0 0 0 0 0 0 1 2 2 0 0 2 0 0 0 0 0
B- 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0
D 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1
NR 1 1 0 0 0 0 0 4 1 0 2 0 0 0 0 0 0 5

Let us have a close look at the results. Using the lookup dictionary, we are going to see which companies' ratings changed from BB- to AA-?

lookup[('BB-', 'AA-')]
['Amazon.com Inc']

What about A to A-?

lookup[('A', 'A-')]
['Aflac Inc',
 'Baxter International Inc',
 'Brown-Forman Corp',
 'Consolidated Edison Inc',
 'Dover Corp',
 'E I du Pont de Nemours and Co',
 'Ecolab Inc',
 'McCormick & Company Inc',
 'Metlife Inc',
 'NextEra Energy Inc',
 'PNC Financial Services Group Inc',
 'PPG Industries Inc',
 'Rockwell Collins Inc',
 'Southern Co',
 'United Technologies Corp']