Compute portfolio average short-term rating¶

The following case-study demonstrates how to compute the average short-term rating of your portfolio. Before we start looking at a concrete example, let's recap the steps that are necessary to compute the average short-term portfolio rating.

Translating short-term ratings into numerical rating scores and vice versa isn't as straightforward as with long-term ratings (see Short-term ratings).

The below chart shows the steps to compute the average portfolio rating for a two-security portfolio.

• Step 1:
  Translate all short-term ratings from available rating agencies into an average equivalent long-term rating score or into a so-called average equivalent value (AEV).
• Step 2:
  Consolidate all ratings on a security basis into one equivalent long-term rating score.
  For example, you could decide to choose the worst rating (read: highest numerical score) among all rating agencies.
  As a result, you'll end up with the worst (i.e. highest) AEV per security.
• Step 3:
  Compute the weighted AEV for the portfolio. That is, multiply an individual security's weight with its worst AEV. You'll end up with this security's contribution to the portfolio AEV.
  Finally, sum up the individual securities' contributions.
  Essentially, this is simply computing the sum-product of the weights and the worst AEVs.
• Step 4:
  Translate the portfolio AEV back into a human-readable short-term rating, while using the agency's rating scale of your choice.

Preliminary tasks¶

As a first step, we are going to import a portfolio into a pd.DataFrame. We'll call it port_df. This dataframe comprises a number of securities with respective weights and short-term ratings.

In [1]:

import pandas as pd

import pyratings as rtg

port_df = pd.read_excel(
    "portfolio.xlsx",
    sheet_name="short_term_ratings",
)

port_df

import pandas as pd import pyratings as rtg port_df = pd.read_excel( "portfolio.xlsx", sheet_name="short_term_ratings", ) port_df

Out[1]:

	ISIN	weight	Moody	SP	Fitch	DBRS
0	ISIN00000001	5.0	P-1	A-1+	F1+	R-1M
1	ISIN00000002	8.5	P-1	A-1	NaN	NaN
2	ISIN00000003	2.0	P-1	NaN	F2	R-1L
3	ISIN00000004	3.5	P-1	NaN	F1+	NaN
4	ISIN00000005	12.0	P-1	NaN	NaN	R-2M
5	ISIN00000006	3.0	(P)P-1	A-1+	NaN	R-2H
6	ISIN00000007	5.4	P-2	A-1	F3	R-1M
7	ISIN00000008	7.6	P-1	A-1+	F1	R-1H
8	ISIN00000009	2.0	P-1	A-1	F1+	NaN
9	ISIN00000010	4.0	P-3	A-1+	F1+	R-1M
10	ISIN00000011	12.0	P-1	A-2	F-1	R-3
11	ISIN00000012	3.0	P-2	A-3	F1	NaN
12	ISIN00000013	7.0	P-1	A-1	F2	R-2L
13	ISIN00000014	8.0	(P)P-1	NaN	NaN	NaN
14	ISIN00000015	4.0	P-2	A-1	F1+	R-1M
15	ISIN00000016	13.0	P-1	A-1+	F1	R-1H

Clean ratings¶

If you take a closer look at row 5 and 13, you see that there are two ratings from Moody's which are based on public information (indicated by "(P)"). We need to get rid of this prefix, otherwise the rating can't be correctly identified by pyratings.

In [2]:

port_df_clean = rtg.get_pure_ratings(
    ratings=port_df.loc[:, ["Moody", "SP", "Fitch", "DBRS"]]
)
port_df_clean

port_df_clean = rtg.get_pure_ratings( ratings=port_df.loc[:, ["Moody", "SP", "Fitch", "DBRS"]] ) port_df_clean

Out[2]:

	Moody_clean	SP_clean	Fitch_clean	DBRS_clean
0	P-1	A-1+	F1+	R-1M
1	P-1	A-1	NaN	NaN
2	P-1	NaN	F2	R-1L
3	P-1	NaN	F1+	NaN
4	P-1	NaN	NaN	R-2M
5	P-1	A-1+	NaN	R-2H
6	P-2	A-1	F3	R-1M
7	P-1	A-1+	F1	R-1H
8	P-1	A-1	F1+	NaN
9	P-3	A-1+	F1+	R-1M
10	P-1	A-2	F-1	R-3
11	P-2	A-3	F1	NaN
12	P-1	A-1	F2	R-2L
13	P-1	NaN	NaN	NaN
14	P-2	A-1	F1+	R-1M
15	P-1	A-1+	F1	R-1H

Consolidate ratings (step 1 and 2)¶

Next, we are consolidating the clean ratings. We take the conservative approach and identify the worst numerical rating score assigned to every individual security.

We can use the get_worst_scores function. pyratings will automatically convert the short-term ratings into AEVs (step 1) and identify the hightest number (read: worst rating score) (step 2).

In [3]:

port_worst_scores_df = rtg.get_worst_scores(
    ratings=port_df_clean,
    rating_provider_input=["Moody", "SP", "Fitch", "DBRS"],
    tenor="short-term",
)

port_worst_scores_df = pd.concat(
    [
        port_df.loc[:, ["ISIN", "weight"]],
        port_worst_scores_df,
    ],
    axis=1
)

port_worst_scores_df

port_worst_scores_df = rtg.get_worst_scores( ratings=port_df_clean, rating_provider_input=["Moody", "SP", "Fitch", "DBRS"], tenor="short-term", ) port_worst_scores_df = pd.concat( [ port_df.loc[:, ["ISIN", "weight"]], port_worst_scores_df, ], axis=1 ) port_worst_scores_df

Out[3]:

	ISIN	weight	worst_scores
0	ISIN00000001	5.0	3.5
1	ISIN00000002	8.5	5.5
2	ISIN00000003	2.0	8.0
3	ISIN00000004	3.5	3.5
4	ISIN00000005	12.0	9.0
5	ISIN00000006	3.0	8.0
6	ISIN00000007	5.4	9.5
7	ISIN00000008	7.6	6.5
8	ISIN00000009	2.0	5.5
9	ISIN00000010	4.0	9.5
10	ISIN00000011	12.0	10.0
11	ISIN00000012	3.0	10.0
12	ISIN00000013	7.0	8.0
13	ISIN00000014	8.0	3.5
14	ISIN00000015	4.0	7.5
15	ISIN00000016	13.0	6.5

Compute the weighted AEV (step 3)¶

Now, we need to compute a weighted average rating score. We use the get_weighted_average function.

In [4]:

avg_rtg_score = rtg.get_weighted_average(
    data=port_worst_scores_df["worst_scores"],
    weights=port_worst_scores_df["weight"] / 100,
)

print(f"Average rating score: {avg_rtg_score:.2f}")
print(f"Average rating score (rounded): {round(avg_rtg_score):.2f}")

avg_rtg_score = rtg.get_weighted_average( data=port_worst_scores_df["worst_scores"], weights=port_worst_scores_df["weight"] / 100, ) print(f"Average rating score: {avg_rtg_score:.2f}") print(f"Average rating score (rounded): {round(avg_rtg_score):.2f}")

Average rating score: 7.23
Average rating score (rounded): 7.00

Going back to human-readable ratings (step 4)¶

Translating rating scores into short-term ratings is somewhat different compared to long-term ratings (see Short-term ratings). We need to decide which strategy we will use: best, base, or worst. Choosing the strategy influences the outcome. The rounded rating score is 7.

We are going to use the get_ratings_from_scores function. The input parameter short_term_strategy defines the strategy, which will be used to translate AEVs back into short-term ratings.

The following table describes how this rating score will be translated into a short-term rating depending on the strategy.

In [5]:

avg_rtg_best = rtg.get_ratings_from_scores(
    rating_scores=avg_rtg_score,
    rating_provider="S&P",
    tenor="short-term",
    short_term_strategy="best",
)

avg_rtg_base = rtg.get_ratings_from_scores(
    rating_scores=avg_rtg_score,
    rating_provider="S&P",
    tenor="short-term",
    short_term_strategy="base",
)

avg_rtg_worst = rtg.get_ratings_from_scores(
    rating_scores=avg_rtg_score,
    rating_provider="S&P",
    tenor="short-term",
    short_term_strategy="worst",
)

print(f"Average portfolio rating (strategy: 'best'): {avg_rtg_best}")
print(f"Average portfolio rating (strategy: 'base'): {avg_rtg_base}")
print(f"Average portfolio rating (strategy: 'worst'): {avg_rtg_worst}")

avg_rtg_best = rtg.get_ratings_from_scores( rating_scores=avg_rtg_score, rating_provider="S&P", tenor="short-term", short_term_strategy="best", ) avg_rtg_base = rtg.get_ratings_from_scores( rating_scores=avg_rtg_score, rating_provider="S&P", tenor="short-term", short_term_strategy="base", ) avg_rtg_worst = rtg.get_ratings_from_scores( rating_scores=avg_rtg_score, rating_provider="S&P", tenor="short-term", short_term_strategy="worst", ) print(f"Average portfolio rating (strategy: 'best'): {avg_rtg_best}") print(f"Average portfolio rating (strategy: 'base'): {avg_rtg_base}") print(f"Average portfolio rating (strategy: 'worst'): {avg_rtg_worst}")

Average portfolio rating (strategy: 'best'): A-1
Average portfolio rating (strategy: 'base'): A-2
Average portfolio rating (strategy: 'worst'): A-2

There you go. The average rating of the portfolio is either A-1 (best) or A-2 (base and worst).