Purpose

This script is created to merch the clean merger data with the credit rating data. The credit rating is the S&P rating translated to a 1-13 scale. The rating is as of the last quarter end prior to the merger announcement. \(\Delta\) Credit Rating (X months) is the change in the credit rating from the last quarter end prior to the merger announcement to the first quarter-end at least X months after the merger completion.

Loading the data

# Load the SDC mergers dataset

sdc_clean <- read_csv("data/processed/sdc_clean.csv")

n_sdc <- as.character(nrow(sdc_clean))

# Load the Credit rating data

credit_rating <- read_csv("data/raw/credit_rating.csv")

Cleaning

We first convert the cusip from the raw credit rating data to a 6-digit cusip.

credit_rating <- credit_rating %>%
  mutate(cusip = str_sub(cusip, 1, 6))

We now show the table of bond ratings and then convert them to a 1-13 scale. Based on the following scale:

Rating Symbol Rating Value
AA, AAA 1
A, A+ 2
A- 3
BBB+ 4
BBB 5
BBB- 6
BB+ 7
BB 8
BB- 9
B+ 10
B 11
B- 12
C and lower 13 
credit_rating %>% group_by(ratingsymbol) %>%
  summarise(n = n()) %>%
  kable() %>%
  kable_styling(full_width = F, position = "left")
ratingsymbol n
A 5089
A+ 4321
A+/A-1 168
A+/A-1+ 3
A- 6250
A-/A-1 20
A-/A-2 37
A-/NR 1
A-1 467
A-1(HIGH) 3
A-1+ 407
A-2 310
A-3 102
A/A-1 122
A/NR 4
AA 5327
AA+ 2651
AA+/A-1 13
AA+/A-1+ 250
AA+/A-2 3
AA+/NR 4
AA- 4907
AA-/A-1 11
AA-/A-1+ 240
AA-/A-2 14
AA-/NR 4
AA/A-1 44
AA/A-1+ 355
AA/NR 4
AAA 9837
AAA/A-1 159
AAA/A-1+ 605
AAA/A-2 20
AAA/NR 29
B 1576
B+ 1402
B+/C 5
B- 1079
B-/NR 3
B-1 2
B-2 4
B-3 2
B/C 1
BB 1712
BB+ 1535
BB+/B 4
BB+/NR 4
BB- 1594
BB/B 1
BBB 4341
BBB+ 4471
BBB+/A-2 6
BBB- 2605
BBB-/A-3 1
BBB/A-2 2
BBB/NR 1
C 79
CC 286
CCC 360
CCC+ 631
CCC- 327
D 352
NR 16783
NR/NR 319
SD 4
SP-1 1
SP-1+ 57
SP-1+/A-1+ 4
mxA-1+ 2
mxAA+ 9
mxAAA 29
raAA 3
raBBB- 6
raCCC 1
twA+ 1
twAA 1
twAAA 2 
# create a new column call numerical_rating

credit_rating <- credit_rating %>%
  mutate(numerical_rating = case_when(
    ratingsymbol %in% c("AA", "AAA") ~ 1,
    ratingsymbol %in% c("A", "A+") ~ 2,
    ratingsymbol == "A-" ~ 3,
    ratingsymbol == "BBB+" ~ 4,
    ratingsymbol == "BBB" ~ 5,
    ratingsymbol == "BBB-" ~ 6,
    ratingsymbol == "BB+" ~ 7,
    ratingsymbol == "BB" ~ 8,
    ratingsymbol == "BB-" ~ 9,
    ratingsymbol == "B+" ~ 10,
    ratingsymbol == "B" ~ 11,
    ratingsymbol == "B-" ~ 12,
    TRUE ~ 13
  ))

credit_rating %>% group_by((numerical_rating)) %>%
  summarise(n = n()) %>%
  kable() %>%
  kable_styling(full_width = F, position = "left")
(numerical_rating) n
1 15164
2 9410
3 6250
4 4471
5 4341
6 2605
7 1535
8 1712
9 1594
10 1402
11 1576
12 1079
13 30250 
# For each day and each CUSIP, if there are multiple rating, only keep the median

credit_rating <- credit_rating %>%
  group_by(cusip, ratingDate) %>%
  summarise(median_numerical_rating = median(numerical_rating, na.rm = TRUE)) %>%
  ungroup() %>%
  mutate(numerical_rating = median_numerical_rating) %>%
  select(-median_numerical_rating)
# Merge with cusip, drop non matches, and provide count of unique cusips

sdc_clean_rating <- sdc_clean %>%
  left_join(credit_rating, by = c("acq_cusip" = "cusip")) %>%
  filter(!is.na(numerical_rating))

# Count of unique cusips in the merged dataset

sdc_clean_rating %>%
  summarise(unique_cusips = n_distinct(acq_cusip)) %>%
  kable() %>%
  kable_styling(full_width = F, position = "left")
unique_cusips
20 
# In the SDC data set, create four columns. The first column is the quarter of the merger announcement. The second column is closest quarter before the merger. The third column is the quarter of merger completion. The fourth column is the first quarter-end at least 6 months after the merger completion. The fifth column is the first quarter-end at least 12 months after the merger completion.

sdc_clean <- sdc_clean %>%
  mutate(announcement_quarter = quarter(da),
         year = year(da),
         closest_quarter_before_merger = floor_date(da, "quarter"),
         completion_quarter = quarter(de),
         first_quarter_6_months_after_completion = ceiling_date(de + months(6), "quarter"),
         first_quarter_12_months_after_completion = ceiling_date(de + months(12), "quarter"))
# Cleaning the credit rating data

credit_rating_clean <- credit_rating %>%
  mutate(quarter = quarter(ratingDate),
         year = year(ratingDate)) %>%
  select(cusip, numerical_rating, quarter, year) %>%
  arrange(cusip, year, quarter)

# Merge the SDC data with the credit rating data

sdc_clean <- sdc_clean %>%
  left_join(credit_rating_clean, by = c("acq_cusip" = "cusip", "closest_quarter_before_merger" = "quarter", "year" = "year"))