I have the following two datasets. The first one is a list which looks as follows:
head(CDS_bond_basis)
Dates CDS Bond Swap zero rate CDS-bond basis Bank
1 2015-01-22 124.50 194.7738 31.10 -39.17377 AIB Group UK PLC
2 2015-01-23 124.41 185.0195 27.20 -33.40953 AIB Group UK PLC
3 2015-01-26 124.41 184.3250 31.50 -28.41500 AIB Group UK PLC
4 2015-01-27 124.41 184.2980 30.90 -28.98801 AIB Group UK PLC
5 2015-01-28 124.41 184.7475 27.45 -32.88754 AIB Group UK PLC
6 2015-01-29 124.41 186.9114 32.05 -30.45136 AIB Group UK PLC
The important part is the column CDS-bond basis. It is simply calculated by this formula
CDS-(Bond-Swap zero rate)
the dataset contains 45078 entries of 25 different banks over the time period 02.01.2007-30.12.2016.
The second dataset looks as follows:
head(RatingDowngradesFinal_)
Bank Dates Rating agency New rating Previous rating State
1 ABN AMRO Bank NV 2016-02-17 Moody's WR Ba1 NL
2 ABN AMRO Bank NV 2015-09-29 DBRS A AH *- NL
3 ABN AMRO Bank NV 2015-05-20 DBRS AH *- AH NL
4 ABN AMRO Bank NV 2015-05-20 DBRS AL *- AL NL
5 ABN AMRO Bank NV 2015-05-19 Fitch A A+ NL
6 ABN AMRO Bank NV 2015-05-19 Fitch A A+ NL
This dataset contains information about rating downgrades over the time period.
First of all I would like to split the whole time period into three separate intervals:
1. 02.01.2007-31.12.2009
2. 01.01.2010-31.12.2012
3. 01.01.2013-30.12.2016
Afterwards I would like to summarize the mean daily changes of the variables: CDS, Bond, Swap zero rate and CDS-bond basis over the following time intervals ->
1. [-30,-1]
2. [1,30]
3. [31,60]
4. [61,90]
5. [-1,1]
6. [1,10]
,where for example [-30,-1] stands for the time interval bewteen 30 days and 1 day before the downgrade and [1,10] stands for the interval between 1 day and 10 days after the downgrade. Therefore the banks have to be the same in both datasets -> AIB Group UK PLC = AIB Group UK PLC.
Another difficulty is that my datasets consist only of business days, therefore every 5 days, 2 days are missing because of the weekend.
Thank you for your help in advance, Ramon
Here you are. It prints 3 data frame (one for each of the three separate intervals you wanted).
There is probably a more elegant way to handle all the various lists and vectors, feel free to work on it.
library(readxl)
CDS_bond_basis <- read_excel("CDS-bond basis.xlsx")
RatingDowngradesFinal_ <- read_excel("RatingDowngradesFinal.xlsx")
CDS_bond_basis$Dates <- as.Date(CDS_bond_basis$Dates)
RatingDowngradesFinal_$Dates <- as.Date(RatingDowngradesFinal_$Dates)
# Ordered Fitch and Moody's rating scale
fitch <- c("AAA", "AA+ ", "AA", "AA–", "A+", "A ", "A– ", "BBB+", "BBB", "BBB–", "BB+", "BB", "BB–", "B+", "B", "B–", "CCC", "CC", "C", "RD/D")
moodys <- c("Aaa", "Aa1 *-", "Aa2", "Aa3", "A1", "A2", "A3", "Baa1", "Baa2", "Baa3", "Ba1", "Ba2", "Ba3", "B1", "B2", "B3", "Caa1", "Caa2", "Caa3", "Ca", "C", "WR")
standardandpoors <- c("AA *-", "AA- *-", "AA", "AA-", "A+", "A+ *-", "A", "A *-", "A-", "A- *-", "BBB+", "BBB+ *-", "BBB", "BBB *-", "BBB-", "BB+ *-", "BB *-", "B")
dbrs <- c("AAA *-", "AAH *-", "AAH", "AAL *-", "AAL", "AA", "AA *-", "AH *-", "AH", "A", "A *-", "AL", "AL *-", "BBBH", "BBBH *-", "BBB", "BBB *-", "BBBL *-")
# A way to split your dataframe
firstPeriod <- split(CDS_bond_basis,as.Date("2007-01-02") <= CDS_bond_basis$Dates &
CDS_bond_basis$Dates <= as.Date("2009-12-31"))[2]
secondPeriod <- split(CDS_bond_basis,as.Date("2010-01-01") <= CDS_bond_basis$Dates &
CDS_bond_basis$Dates <= as.Date("2012-12-31"))[2]
thirdPeriod <- split(CDS_bond_basis,as.Date("2013-01-01") <= CDS_bond_basis$Dates &
CDS_bond_basis$Dates <= as.Date("2016-12-30"))[2]
listIntervals <- list(c(-30, -1), c(1, 30), c(31, 60), c(61, 90), c(-1, 1), c(1, 10))
# Create list of vectors that will contain the mean data for each of your 6 intervals, First/Second/Third is used
# for your "First of all I would like to split the whole time period into three separate intervals" request
listMeanCDSFirst <- list(c(), c(), c(), c(), c(), c())
listMeanBondFirst <- list(c(), c(), c(), c(), c(), c())
listMeanSwapZRFirst <- list(c(), c(), c(), c(), c(), c())
listMeanCDSbbFirst <- list(c(), c(), c(), c(), c(), c())
listMeanCDSSecond <- list(c(), c(), c(), c(), c(), c())
listMeanBondSecond <- list(c(), c(), c(), c(), c(), c())
listMeanSwapZRSecond <- list(c(), c(), c(), c(), c(), c())
listMeanCDSbbSecond <- list(c(), c(), c(), c(), c(), c())
listMeanCDSThird <- list(c(), c(), c(), c(), c(), c())
listMeanBondThird <- list(c(), c(), c(), c(), c(), c())
listMeanSwapZRThird <- list(c(), c(), c(), c(), c(), c())
listMeanCDSbbThird <- list(c(), c(), c(), c(), c(), c())
for (i in seq(nrow(RatingDowngradesFinal_))) {
# Check whether a downgrade occured
if (isTRUE(match(RatingDowngradesFinal_$`New rating`[i], fitch) >
match(RatingDowngradesFinal_$`Previous rating`[i], fitch)) |
isTRUE(match(RatingDowngradesFinal_$`New rating`[i], moodys) >
match(RatingDowngradesFinal_$`Previous rating`[i], moodys)) |
isTRUE(match(RatingDowngradesFinal_$`New rating`[i], standardandpoors) >
match(RatingDowngradesFinal_$`Previous rating`[i], standardandpoors)) |
isTRUE(match(RatingDowngradesFinal_$`New rating`[i], dbrs) >
match(RatingDowngradesFinal_$`Previous rating`[i], dbrs))) {
# Set the interval
for (j in seq(length(listIntervals))) {
interval <- c(RatingDowngradesFinal_$Dates[i] + listIntervals[[j]][1], RatingDowngradesFinal_$Dates[i] + listIntervals[[j]][2])
# Filter the dataframe by "interval"
beforeDownGrade <- split(CDS_bond_basis, interval[1] <= CDS_bond_basis$Dates &
CDS_bond_basis$Dates <= interval[2] &
CDS_bond_basis$Bank == as.character(RatingDowngradesFinal_$Bank[i]))
if (is.null(beforeDownGrade$'TRUE') == FALSE) {
if (nrow(beforeDownGrade$'TRUE') > 1) {
if (as.Date("2007-01-02") <= RatingDowngradesFinal_$Dates[i] & RatingDowngradesFinal_$Dates[i] <= as.Date("2009-12-31")) {
listMeanCDSFirst[[j]] <- c(listMeanCDSFirst[[j]], mean(diff(beforeDownGrade$'TRUE'$CDS)))
listMeanBondFirst[[j]] <- c(listMeanBondFirst[[j]], mean(diff(beforeDownGrade$'TRUE'$Bond)))
listMeanSwapZRFirst[[j]] <- c(listMeanSwapZRFirst[[j]], mean(diff(beforeDownGrade$'TRUE'$`Swap zero rate`)))
listMeanCDSbbFirst[[j]] <- c(listMeanCDSbbFirst[[j]], mean(diff(beforeDownGrade$'TRUE'$`CDS-bond basis`)))
} else if (as.Date("2010-01-01") <= RatingDowngradesFinal_$Dates[i] & RatingDowngradesFinal_$Dates[i] <= as.Date("2012-12-31")) {
listMeanCDSSecond[[j]] <- c(listMeanCDSSecond[[j]], mean(diff(beforeDownGrade$'TRUE'$CDS)))
listMeanBondSecond[[j]] <- c(listMeanBondSecond[[j]], mean(diff(beforeDownGrade$'TRUE'$Bond)))
listMeanSwapZRSecond[[j]] <- c(listMeanSwapZRSecond[[j]], mean(diff(beforeDownGrade$'TRUE'$`Swap zero rate`)))
listMeanCDSbbSecond[[j]] <- c(listMeanCDSbbSecond[[j]], mean(diff(beforeDownGrade$'TRUE'$`CDS-bond basis`)))
} else if (as.Date("2013-01-01") <= RatingDowngradesFinal_$Dates[i] & RatingDowngradesFinal_$Dates[i] <= as.Date("2016-12-30")) {
listMeanCDSThird[[j]] <- c(listMeanCDSThird[[j]], mean(diff(beforeDownGrade$'TRUE'$CDS)))
listMeanBondThird[[j]] <- c(listMeanBondThird[[j]], mean(diff(beforeDownGrade$'TRUE'$Bond)))
listMeanSwapZRThird[[j]] <- c(listMeanSwapZRThird[[j]], mean(diff(beforeDownGrade$'TRUE'$`Swap zero rate`)))
listMeanCDSbbThird[[j]] <- c(listMeanCDSbbThird[[j]], mean(diff(beforeDownGrade$'TRUE'$`CDS-bond basis`)))
}
}
}
}
}
}
PreviousMonth1 <- c(mean(listMeanCDSFirst[[1]]), mean(listMeanBondFirst[[1]]), mean(listMeanSwapZRFirst[[1]]), mean(listMeanCDSbbFirst[[1]]))
NextMonth1 <- c(mean(listMeanCDSFirst[[2]]), mean(listMeanBondFirst[[2]]), mean(listMeanSwapZRFirst[[2]]), mean(listMeanCDSbbFirst[[2]]))
NextSecondMonth1 <- c(mean(listMeanCDSFirst[[3]]), mean(listMeanBondFirst[[3]]), mean(listMeanSwapZRFirst[[3]]), mean(listMeanCDSbbFirst[[3]]))
NextThirdMonth1 <- c(mean(listMeanCDSFirst[[4]]), mean(listMeanBondFirst[[4]]), mean(listMeanSwapZRFirst[[4]]), mean(listMeanCDSbbFirst[[4]]))
PreviousAndNextDay1 <- c(mean(listMeanCDSFirst[[5]]), mean(listMeanBondFirst[[5]]), mean(listMeanSwapZRFirst[[5]]), mean(listMeanCDSbbFirst[[5]]))
NextTenDays1 <- c(mean(listMeanCDSFirst[[6]]), mean(listMeanBondFirst[[6]]), mean(listMeanSwapZRFirst[[6]]), mean(listMeanCDSbbFirst[[6]]))
PreviousMonth2 <- c(mean(listMeanCDSSecond[[1]]), mean(listMeanBondSecond[[1]]), mean(listMeanSwapZRSecond[[1]]), mean(listMeanCDSbbSecond[[1]]))
NextMonth2 <- c(mean(listMeanCDSSecond[[2]]), mean(listMeanBondSecond[[2]]), mean(listMeanSwapZRSecond[[2]]), mean(listMeanCDSbbSecond[[2]]))
NextSecondMonth2 <- c(mean(listMeanCDSSecond[[3]]), mean(listMeanBondSecond[[3]]), mean(listMeanSwapZRSecond[[3]]), mean(listMeanCDSbbSecond[[3]]))
NextThirdMonth2 <- c(mean(listMeanCDSSecond[[4]]), mean(listMeanBondSecond[[4]]), mean(listMeanSwapZRSecond[[4]]), mean(listMeanCDSbbSecond[[4]]))
PreviousAndNextDay2 <- c(mean(listMeanCDSSecond[[5]]), mean(listMeanBondSecond[[5]]), mean(listMeanSwapZRSecond[[5]]), mean(listMeanCDSbbSecond[[5]]))
NextTenDays2 <- c(mean(listMeanCDSSecond[[6]]), mean(listMeanBondSecond[[6]]), mean(listMeanSwapZRSecond[[6]]), mean(listMeanCDSbbSecond[[6]]))
PreviousMonth3 <- c(mean(listMeanCDSThird[[1]]), mean(listMeanBondThird[[1]]), mean(listMeanSwapZRThird[[1]]), mean(listMeanCDSbbThird[[1]]))
NextMonth3 <- c(mean(listMeanCDSThird[[2]]), mean(listMeanBondThird[[2]]), mean(listMeanSwapZRThird[[2]]), mean(listMeanCDSbbThird[[2]]))
NextSecondMonth3 <- c(mean(listMeanCDSThird[[3]]), mean(listMeanBondThird[[3]]), mean(listMeanSwapZRThird[[3]]), mean(listMeanCDSbbThird[[3]]))
NextThirdMonth3 <- c(mean(listMeanCDSThird[[4]]), mean(listMeanBondThird[[4]]), mean(listMeanSwapZRThird[[4]]), mean(listMeanCDSbbThird[[4]]))
PreviousAndNextDay3 <- c(mean(listMeanCDSThird[[5]]), mean(listMeanBondThird[[5]]), mean(listMeanSwapZRThird[[5]]), mean(listMeanCDSbbThird[[5]]))
NextTenDays3 <- c(mean(listMeanCDSThird[[6]]), mean(listMeanBondThird[[6]]), mean(listMeanSwapZRThird[[6]]), mean(listMeanCDSbbThird[[6]]))
period1 <- data.frame(PreviousMonth1, NextMonth1, NextSecondMonth1, NextThirdMonth1, PreviousAndNextDay1, NextTenDays1)
rownames(period1) <- c("CDS", "Bond", "Swap zero Rate", "CDS-bond-basis")
colnames(period1) <- c("[-30,-1]", "[1,30]", "[31,60]", "[61,90]", "[-1,1]", "[1,10]")
period2 <- data.frame(PreviousMonth2, NextMonth2, NextSecondMonth2, NextThirdMonth2, PreviousAndNextDay2, NextTenDays2)
rownames(period2) <- c("CDS", "Bond", "Swap zero Rate", "CDS-bond-basis")
colnames(period2) <- c("[-30,-1]", "[1,30]", "[31,60]", "[61,90]", "[-1,1]", "[1,10]")
period3 <- data.frame(PreviousMonth3, NextMonth3, NextSecondMonth3, NextThirdMonth3, PreviousAndNextDay3, NextTenDays3)
rownames(period3) <- c("CDS", "Bond", "Swap zero Rate", "CDS-bond-basis")
colnames(period3) <- c("[-30,-1]", "[1,30]", "[31,60]", "[61,90]", "[-1,1]", "[1,10]")
print(period1)
print(period2)
print(period3)
Which gives, for period1:
> print(period1)
[-30,-1] [1,30] [31,60] [61,90] [-1,1] [1,10]
CDS -0.1934029 0.5002909 0.09593413 -0.38126535 1.4342439 0.50836275
Bond 0.1001838 0.5286359 0.78631190 -0.88260529 1.3531346 -0.06724158
Swap zero Rate -0.5743715 -0.4472814 -0.13148844 -0.09563088 0.7412500 -0.30337037
CDS-bond-basis -0.8679582 -0.4756264 -0.82186622 0.40570906 0.8223592 0.27223396