Article

Rating Transitions Matrix in Python
Evgeny Kovalyov
Product Manager, Framework Services Product Manager, Framework Services

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.