Article
Rating Transitions Matrix in Python

Introduction
The goal of this tutorial is to demonstrate the Data Library 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:
- LSEG Workspace application is up and running;
- LSEG Data library is installed.
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 [10]: lookup('A3', 'Baa1', transition_matrix, ratings_data)
Out[10]: Number of transitions from A3 to Baa1: 7 ...
For more info on the Jupyter Notebook, check out Project Jupyter site http://jupyter.org
Getting started
Let's start with importing the LSEG Data library and pandas:
import pandas as pd
import lseg.data as ld
from lseg.data.discovery import Chain
ld.open_session()
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 Moody's Senior Unsecured as of now TR.IssuerRating(IssuerRatingSrc=MSU);
- historical Moody's Senior Unsecured rating (10 years back) TR.IssuerRating(IssuerRatingSrc=SPI,Sdate=MSU).
You can find these fields and more in the Data Item Browser in your LSEG Workspace. The result is formatted as a pandas dataframe..
rics = Chain(name="0#.SPX")
ratings_data = ld.get_data(universe=rics,
fields=['TR.CommonName',
'TR.IssuerRating',
'TR.IssuerRating(Sdate=-10Y)'],
parameters={'IssuerRatingSrc':'MSU'},
header_type=ld.content._header_type.HeaderType.NAME)
ratings_data.head()
Instrument | TR.COMMONNAME | TR.ISSUERRATING | TR.ISSUERRATING(SDATE=-10Y) | |
---|---|---|---|---|
0 | A.N | Agilent Technologies Inc | Baa1 | Baa2 |
1 | AAPL.OQ | Apple Inc | Aaa | Aa1 |
2 | ABBV.N | AbbVie Inc | A3 | Baa1 |
3 | ABNB.OQ | Airbnb Inc | ||
4 | ABT.N | Abbott Laboratories | Aa3 | A2 |
You have noticed that there are some blanks. So, let us clear out those values and rename the columns.
col_rename = {'TR.COMMONNAME': 'Company Common Name', 'TR.ISSUERRATING': 'Issuer Rating - this year',
'TR.ISSUERRATING(SDATE=-10Y)': 'Issuer Rating - 10 years back'}
ratings_data.rename(columns=col_rename,inplace=True)
ratings_data = ratings_data[(ratings_data['Issuer Rating - this year']!='') & (ratings_data['Issuer Rating - 10 years back']!='')]
ratings_data.reset_index(inplace=True)
ratings_data
Working with the results
Now, let us build a proper readable output. For this, we need to sort ratings by their rank:
ratings_scale = ['Aaa', 'Aa1', 'Aa2', 'Aa3', 'A1', 'A2', 'A3', 'Baa1', 'Baa2', 'Baa3', 'Ba1', 'Ba2', 'Ba3', 'B1', 'B2', 'B3', 'Caa1', 'Caa2', 'Caa3', 'Ca', 'C', 'WR']
Now we will create a data frame that will show us a matrix and also a lookup dictionary, where companies in transition will be stored.
def lookup(from_rating, to_rating, matrix, df):
try:
count = matrix.loc[from_rating, to_rating]
print(f"Number of transitions from {from_rating} to {to_rating}: {count}")
filtered = df[(df['Issuer Rating - 10 years back'] == from_rating) & (df['Issuer Rating - this year'] == to_rating)]
return print('\n'.join(filtered['Company Common Name'].tolist()))
except KeyError:
print("Invalid rating(s). Please check the rating names.")
return None
transition_matrix = pd.crosstab(ratings_data['Issuer Rating - 10 years back'], ratings_data['Issuer Rating - this year'], rownames=['From'], colnames=['To'], dropna=False)
transition_matrix = transition_matrix.reindex(index=ratings_scale, columns=ratings_scale, fill_value=0)
transition_matrix
To From |
Aaa | Aa1 | Aa2 | Aa3 | A1 | A2 | A3 | Baa1 | Baa2 | Baa3 | ... | Ba3 | B1 | B2 | B3 | Caa1 | Caa2 | Caa3 | Ca | C | WR |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Aaa | 2 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ... | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
Aa1 | 1 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ... | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
Aa2 | 0 | 0 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ... | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
Aa3 | 0 | 0 | 0 | 3 | 1 | 1 | 2 | 0 | 0 | 0 | ... | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
A1 | 0 | 0 | 0 | 3 | 6 | 3 | 2 | 2 | 1 | 0 | ... | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
A2 | 0 | 0 | 0 | 5 | 3 | 18 | 7 | 5 | 2 | 2 | ... | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
A3 | 0 | 0 | 0 | 0 | 2 | 8 | 12 | 7 | 5 | 0 | ... | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
Baa1 | 0 | 0 | 0 | 0 | 3 | 2 | 15 | 17 | 15 | 3 | ... | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2 |
Baa2 | 0 | 0 | 0 | 0 | 0 | 1 | 7 | 9 | 26 | 4 | ... | 0 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2 |
Baa3 | 0 | 0 | 0 | 0 | 0 | 0 | 3 | 5 | 14 | 7 | ... | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
Ba1 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 2 | 6 | 4 | ... | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 |
Ba2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 3 | 2 | ... | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
Ba3 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2 | 2 | ... | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
B1 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 1 | 1 | ... | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
B2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ... | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 |
B3 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ... | 2 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
Caa1 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | ... | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 |
Caa2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ... | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 |
Caa3 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ... | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
Ca | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ... | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
C | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ... | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
WR | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 2 | 0 | ... | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 11 |
Let us have a close look at the results. Using the lookup dictionary, we are going to see which companies' ratings changed from Baa1 to A3?
lookup('Baa1', 'A3', transition_matrix, ratings_data)
Number of transitions from Baa1 to A3: 15
AbbVie Inc
Amphenol Corp
AvalonBay Communities Inc
Booking Holdings Inc
Citigroup Inc
Church & Dwight Co Inc
Camden Property Trust
CSX Corp
Ecolab Inc
Intuit Inc
Marsh & McLennan Companies Inc
Altria Group Inc
Realty Income Corp
PPG Industries Inc
Prudential Financial Inc
What about A3 to Baa1?
lookup('A3', 'Baa1', transition_matrix, ratings_data)
Number of transitions from A3 to Baa1: 7
Corning Inc
Hubbell Inc
Lowe's Companies Inc
McDonald's Corp
Starbucks Corp
WEC Energy Group Inc
Xcel Energy Inc
Conclusion
I hope I have showed you that LSEG Data Library is quite powerful and easy to work with when you are dealing with the reference data.