Article
Rating Transitions Matrix in Python
Introduction
The goal of this article is to demonstrate the new Refinitiv Eikon Data API with the focus on the fundamental data retrieval in a Jupyter Notebook environment. 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:
- Refinitiv Eikon or Refinitiv Workspace is up and running;
- Eikon Data API library is installed;
- You have created an application ID for this script.
If you have not yet done this, have a look at the quick start section for this API.
A general note on the Jupyter Notebook usage: in order to execute the code in the cell, press Shift+Enter. While notebook is busy running your code, the cell will look like this: In [*]. When its finished, you will see it change to the sequence number of the task and the output, if any. For example,
In [7]: lookup[('BB-', 'AA-')]
Out[7]: ['Amazon.com Inc']
For more info on the Jupyter Notebook, check out Project Jupyter site http://jupyter.org or 'How to set up a Python development environment for Refinitiv Eikon' tutorial on this portal.
Getting started
Let us start with referencing Eikon API library and pandas:
import eikon as ek
import pandas as pd
Paste your application ID into this line:
ek.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, while the result is formatted as a pandas data frame.
ratings_data, err = ek.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 | AOS.N | A. O. Smith Corp | ||
| 2 | ABT.N | Abbott Laboratories | BBB | AA |
| 3 | ABBV.N | AbbVie Inc | A- | |
| 4 | ACN.N | Accenture PLC | A+ | A+ |
Working with the resuls
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 2008. 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 data frame that will show us a matrix and also a nifty lookup dictionary, where 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- | NR | |
| AAA | 1 | 1 | 3 | 0 | 1 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| AA+ | 0 | 0 | 0 | 0 | 1 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| AA | 0 | 0 | 1 | 3 | 2 | 0 | 1 | 2 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| AA- | 0 | 0 | 1 | 2 | 2 | 1 | 1 | 3 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| A+ | 0 | 0 | 0 | 5 | 6 | 9 | 7 | 3 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| A | 0 | 0 | 0 | 1 | 6 | 14 | 10 | 12 | 5 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| A- | 0 | 0 | 0 | 0 | 1 | 10 | 10 | 9 | 9 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| BBB+ | 0 | 0 | 0 | 0 | 0 | 3 | 10 | 18 | 12 | 6 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| BBB | 0 | 0 | 0 | 0 | 1 | 1 | 8 | 14 | 18 | 8 | 1 | 1 | 0 | 0 | 0 | 0 | 1 |
| BBB- | 0 | 0 | 0 | 0 | 1 | 0 | 5 | 9 | 7 | 5 | 4 | 1 | 1 | 0 | 0 | 0 | 2 |
| BB+ | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 2 | 7 | 6 | 0 | 1 | 0 | 0 | 0 | 0 |
| BB | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 2 | 3 | 2 | 4 | 0 | 2 | 0 | 0 | 1 | 1 |
| BB- | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2 | 1 | 5 | 3 | 5 | 2 | 1 | 0 | 0 | 0 |
| B+ | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 1 | 1 | 0 | 1 | 2 | 0 | 0 | 0 | 0 |
| B | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 4 | 2 | 1 | 0 | 2 | 0 | 0 | 1 | 0 |
| B- | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| NR | 1 | 1 | 0 | 0 | 0 | 0 | 2 | 3 | 2 | 1 | 2 | 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 BBB+?
lookup[('BB-', 'BBB+')]
['Lam Research Corp', 'NVIDIA Corp']
What about A to A-?
lookup[('A', 'A-')]
['Aflac Inc',
'Brown-Forman Corp',
'ConocoPhillips',
'Consolidated Edison Inc',
'Ecolab Inc',
'Harley-Davidson Inc',
'MetLife Inc',
'NextEra Energy Inc',
'Southern Co',
'United Technologies Corp']
Conclusion
I hope I have showed you that Python API for Refinitiv Eikon is quite powerful and easy to work with when you are dealing with the reference data.