For a deeper look into our Eikon Data API, look into:

Overview |  Quickstart |  Documentation |  Downloads |  Tutorials |  Articles

question

Upvotes
Accepted
3 2 2 1

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?

eikoneikon-data-apipythonworkspaceworkspace-data-apirefinitiv-dataplatform-eikonbonds
icon clock
10 |1500

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Upvotes
Accepted
1.9k 12 2 2

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.


icon clock
10 |1500

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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

Hi @staff2,


There are two ways I can see to concatenate as such:


1:

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

df6, err = ek.get_data(
    instruments = ['US345370BR09'],
    fields = ['TR.BIDPRICE.date', 'TR.BIDPRICE', 'TR.ASKPRICE'],
    parameters = {'SDate':'-15Y','EDate':'0', 'FRQ':'Y'})
df6['Date'] = pd.to_datetime(df6["Date"], format = '%Y-%m-%dT%H:%M:%S')

df7 = df6.merge(df5, how = 'outer', on = 'Date').sort_values(by='Date').tail(40).reset_index()

for i in range(len(df7.index)):
    if type(df7["Instrument"][i]) == pd._libs.missing.NAType:
        df7["Instrument"][i] = df7["InstrumentRated"][i]
df7 = df7[["Instrument", "Date", "Bid Price", "Ask Price", "Rating", "Rating Source"]]

display(df7)




1618828189403.png (68.1 KiB)
Upvotes
7.4k 10 6 8

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

icon clock
10 |1500

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.

Upvotes
3 2 2 1

Can somebody help me with the above?

icon clock
10 |1500

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Upvotes
1.9k 12 2 2

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, ...)


1618828282127.png (61.3 KiB)
icon clock
10 |1500

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.

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?

Hi @staff2, S&P changed its policy for use of ratings data, you may require a direct license with S&P. I took the liberty to speak to your Refinitiv Manager/Representative and asked them to contact you on the matter. Please speak with your Refinitiv representative, and they can inform you of the workflow to incorporate such S&P data in your Refinitiv package.