Credit Ratings

Hello

I am trying to retrieve yearly (end-of-year) credit ratings for a list of companies. I provide a sample code with a list of 2 companies here. I am running the following:

df2, err = ek.get_data(['US0378331005','US345370BR09'], ['TR.BIDPRICE.date', 'TR.BIDPRICE', 'TR.ASKPRICE','TR.GR.Rating(BondRatingSrc=MDY)',
                  'TR.GR.Rating(BondRatingSrc=FTC)','TR.GR.Rating(BondRatingSrc=SPI)'],
                  {'SDate':'-12Y','EDate':'0', 'FRQ':'Y'})
df2.columns = df2.columns[:-3].tolist() + ['Moody\'s Rating','Fitch Rating','SP Rating']
df2

Can somebody confirm that I am doing the right thing above?

Two problems:

1. S&P Ratings are not showing at all. They are all blank

2. Even within Moody's, there are blanks in some years. E.g. for the second company in the list, I get a rating in 2013, then blank for 2014, 2015, and a new rating in 2016. How do I fill these gaps?

Find more posts tagged with

python

eikon

bonds

workspace

eikon-data-api

refinitiv-dataplatform-eikon

workspace-data-api

Accepted answers

jonathan.legrand

Hi @staff2,

1. S&P changed its policy for use of ratings data, you may require a direct license with S&P. Please speak with your Refinitiv representative, and they can inform you of the workflow to incorporate such S&P data in your Refinitiv package.

2.1 The 'blanks' are there to signify that there was no change. You may choose to forward fill these cells as such (for e.g.):

import pandas as pd
import numpy as np

df3, err = ek.get_data(
    instruments = ['US345370BR09','US0378331005'],
    fields = ['TR.BIDPRICE.date', 'TR.BIDPRICE', 'TR.ASKPRICE',
              'TR.GR.Rating(BondRatingSrc=MDY).date', 'TR.GR.Rating(BondRatingSrc=MDY)',
              'TR.GR.Rating(BondRatingSrc=FTC).date', 'TR.GR.Rating(BondRatingSrc=FTC)'],
    parameters = {'SDate':'-25Y','EDate':'0', 'FRQ':'Y'})
columns_of_interest = ["Moody's Rating", "Fitch Rating"]
df3.columns = df3.columns[:4].tolist() +  ["MDY Date", "Moody's Rating", "FTC Date", "Fitch Rating"]

ratings_per_inst = [
    df3[df3["Instrument"] == i][columns_of_interest # We wre only interested in the ratings.
         ].replace('', np.nan, regex=True # We want all empty cells to have the same ' <NA> ' vales to then replace them.
                  ).fillna(method='ffill') # Finally, fill-forward all <NA> cells.
    for i in df3["Instrument"].unique()] # ' .unique() ' conserves the order, ' groupby('Instrument') ' doesn't nessesarily.

df4 = ratings_per_inst[0] # Initialise a new data-frame ' df4 ' to subsequently append.
for i in ratings_per_inst[1:]:
    df4 = df4.append(i, ignore_index = True)
df3[columns_of_interest] = df4 # change relevent columns in original data-frame.

display(df3)

Note that I explicitly requested ratings dates too, and that was because:

2.2 TR.GR.RatingDate and price data (e.g.: TR.ASKPRICE ) are pulled from Eikon's Data API (EDAPI) at different frequencies. The reason for that is that it is possible for a rating's agency to change its rating more than once a year, so only the most granular data-points can be matched in this instance (i.e.: daily). To circumvent this issue, I would suggest only pulling TR.GR.Rating data; if you would like both data-sets together, you may then concatenate them. Please let me know if you would like an example of such concatenation.

All comments

jason.ramchandani01

@staff2 Please look at the solution on this thread. I hope this can help.

staff2

I took my code from that thread. So, the problems that I mention above in my original post still remain. That is where I need most help.

staff2

Can somebody help me with the above?

jonathan.legrand

2.1 The 'blanks' are there to signify that there was no change. You may choose to forward fill these cells as such (for e.g.):

import pandas as pd
import numpy as np

df3, err = ek.get_data(
    instruments = ['US345370BR09','US0378331005'],
    fields = ['TR.BIDPRICE.date', 'TR.BIDPRICE', 'TR.ASKPRICE',
              'TR.GR.Rating(BondRatingSrc=MDY).date', 'TR.GR.Rating(BondRatingSrc=MDY)',
              'TR.GR.Rating(BondRatingSrc=FTC).date', 'TR.GR.Rating(BondRatingSrc=FTC)'],
    parameters = {'SDate':'-25Y','EDate':'0', 'FRQ':'Y'})
columns_of_interest = ["Moody's Rating", "Fitch Rating"]
df3.columns = df3.columns[:4].tolist() +  ["MDY Date", "Moody's Rating", "FTC Date", "Fitch Rating"]

ratings_per_inst = [
    df3[df3["Instrument"] == i][columns_of_interest # We wre only interested in the ratings.
         ].replace('', np.nan, regex=True # We want all empty cells to have the same ' <NA> ' vales to then replace them.
                  ).fillna(method='ffill') # Finally, fill-forward all <NA> cells.
    for i in df3["Instrument"].unique()] # ' .unique() ' conserves the order, ' groupby('Instrument') ' doesn't nessesarily.

df4 = ratings_per_inst[0] # Initialise a new data-frame ' df4 ' to subsequently append.
for i in ratings_per_inst[1:]:
    df4 = df4.append(i, ignore_index = True)
df3[columns_of_interest] = df4 # change relevent columns in original data-frame.

display(df3)

Note that I explicitly requested ratings dates too, and that was because:

staff2

Thank you @jonathan.legrand. Please give me an example of concatenation taking into account the forward fill as well I would presume

jonathan.legrand

2: My favorite:

df6, err = ek.get_data(
    instruments = ['US345370BR09'],
    fields = ['TR.GR.RatingDate', 'TR.GR.Rating', 'TR.GR.Rating.RatingSource'],
    parameters = {'SDate':'-25Y','EDate':'0', 'FRQ':'Y'})
df6.columns = ['InstrumentRated', 'Date', 'Rating', 'Rating Source']
df6['Date'] = pd.to_datetime(df6["Date"], format = '%Y-%m-%dT%H:%M:%S')
df6 = df6.dropna()

ratings_dates = [i.strftime("%Y-%m-%d") for i in df6["Date"].to_list()]
price_point = pd.DataFrame(
    data = [ek.get_data(
        instruments = ['US345370BR09'],
        fields = ['TR.BIDPRICE.date',
                  'TR.BIDPRICE',
                  'TR.ASKPRICE'],
        parameters = {'SDate': i})[0].values.tolist()[0] for i in ratings_dates],
    columns = ['Instrument', 'Date', 'Bid Price', 'Ask Price']).dropna()
price_point['Date'] = pd.to_datetime(price_point["Date"], format = '%Y-%m-%dT%H:%M:%S')
df8 = df6.merge(price_point, how = 'outer', on = "Date").drop_duplicates().reset_index(drop=True)
df8

Note that there isn't necessarily price data for each of these dates. This could be because of several reasons (e.g.: it falls on a non-trading day such as a weekend, price data were not gathered at that time on the listed exchange, ...)

jonathan.legrand

I also just noticed that I was working with Pandas version 1.0.3. On later versions, you can concatenate without converting data-frames to lists the way I did it. The above ought to work anyway.

staff2

Thanks @jonathan.legrand. the df3 method worked best for me actually because the merging for df6 did not always produce desired results. For example, companies matched on wrong ISINs. Credit ratings would be for one company and on the same line bid and ask prices would be for another company. It is not a big issue for me, as I can do both separately and then merge.

How would I integrate S&P license into Eikon? Ideally I would like to use Python to retrieve Moody's, S&P and Fitch. Is there a specific code within Jupyter Notebook that would allow me to retrieve the S&P data using my S&P username and password?

EXPLORE OUR SITES