Significant change in daily mean changes of multiple variables

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基础。它只是通过这个公式计算出来的

CDS-(Bond-Swap zero rate)

CDS-(债券互换零利率)

the dataset contains 45078 entries of 25 different banks over the time period 02.01.2007-30.12.2016.

在02.01.2007-30.12.2016期间,数据集包含25个不同银行的45078个条目。

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 ->

之后我想总结变量的平均每日变化:CDS,债券,掉期零利率和CDS债券基础在以下时间间隔 - >

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.

例如,[ - 30,-1]代表降级前30天和1天的时间间隔,[1,10]代表降级后1天和10天之间的间隔。因此,两个数据集中的银行必须相同 - > 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.

另一个困难是我的数据集仅包含工作日,因此每周5天,因为周末而缺少2天。

Thank you for your help in advance, Ramon

拉蒙先谢谢你的帮助

2 个解决方案

#1

Here you are. It prints 3 data frame (one for each of the three separate intervals you wanted).

这个给你。它打印3个数据框(一个用于您想要的三个独立间隔中的每一个)。

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:

对于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

#2

I added the rating scales of DRBS and Standard and Poors as you said. Furthermore I changed some ratings in the list in order to adjust it to my data as follows:

如你所说,我添加了DRBS和Standard和Poors的评级等级。此外,我更改了列表中的一些评级,以便将其调整为我的数据,如下所示:

fitch <- c("AA+ *-","AA *-", "AA- *-",  "AA", "AA-", "A+", "A+ *-", "A", "A 
*-", "A-", "A- *-", "BBB+", "BBB+ *-", "BBB", "BBB *-", "BBB-", "BBB- *-", 
"BB+", "BB+ *-", "BB", "BB *-", "BB-", "B+ *-", "B-", "B- *-", "CCC", "CC", 
"C")
moodys <- c("Aaa*-", "Aa1 *-", "Aa2", "Aa2 *-",  "Aa3", "Aa3 *-",  "A1", "A1 
*-", "A2", "A2 *-", "A3", "A3 *-", "Baa1", "Baa1 *-", "Baa2", "Baa2 *-", 
"Baa3", "Baa3 *-", "Ba1", "Ba1 *-", "Ba2", "Ba2 *-", "Ba3", "Ba3 *-", "B1", 
"B2",  "Caa2", "Caa2 *-", "Caa3", "Caa3 *-", "Ca", "C", "C-","C *-","C- *-
","C+","C+ *-")
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 *-")

Afterwards I also added it in the section whether to check if downgrade occured:

之后我还在部分中添加了是否检查是否发生了降级:

  if (isTRUE(match(RatingDowngradesFinal_$New.rating[i], fitch) > 
  match(RatingDowngradesFinal_$Previous.rating[i], fitch)) | 
  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)) |
  isTRUE(match(RatingDowngradesFinal_$New.rating[i], moodys) > 
  match(RatingDowngradesFinal_$Previous.rating[i], moodys))) {`

So I ran the whole code in R and got the following error messages in each time interval:

所以我在R中运行了整个代码,并在每个时间间隔中收到以下错误消息:

PreviousMonth1 <- c(mean(listMeanCDSbbFirst[[1]]), 
mean(listMeanBondFirst[[1]]), mean(listMeanSwapZRFirst[[1]]), 
mean(listMeanCDSbbFirst[[1]]))
Warning messages:
1: In mean.default(listMeanCDSbbFirst[[1]]) :
Argument ist weder numerisch noch boolesch: gebe NA zurück
2: In mean.default(listMeanBondFirst[[1]]) :
Argument ist weder numerisch noch boolesch: gebe NA zurück
3: In mean.default(listMeanSwapZRFirst[[1]]) :
Argument ist weder numerisch noch boolesch: gebe NA zurück
4: In mean.default(listMeanCDSbbFirst[[1]]) :
Argument ist weder numerisch noch boolesch: gebe NA zurück`

This resulted in this outcome:

这导致了这个结果:

print(period1) [-30,-1] [1,30] [31,60] [61,90] [-1,1] [1,10] CDS NA NA NA NA NA NA Bond NA NA NA NA NA NA Swap zero Rate NA NA NA NA NA NA CDS-bond-basis NA NA NA NA NA NA print(period2) [-30,-1] [1,30] [31,60] [61,90] [-1,1] [1,10] CDS NA NA NA NA NA NA Bond NA NA NA NA NA NA Swap zero Rate NA NA NA NA NA NA CDS-bond-basis NA NA NA NA NA NA print(period3)
[-30,-1] [1,30] [31,60] [61,90] [-1,1] [1,10] CDS NA NA NA NA NA NA Bond NA NA NA NA NA NA Swap zero Rate NA NA NA NA NA NA CDS-bond-basis NA NA NA NA NA NA ` What seems to be the problem?

print(period1)[-30,-1] [1,30] [31,60] [61,90] [-1,1] [1,10] CDS NA NA NA NA NA NA Bond NA NA NA NA NA NA交换零速率NA NA NA NA NA NA CDS键基NA NA NA NA NA NA打印(句号2)[-30,-1] [1,30] [31,60] [61,90] [-1 ,1] [1,10] CDS NA NA NA NA NA NA键NA NA NA NA NA NA交换零速率NA NA NA NA NA NA CDS键基NA NA NA NA NA NA打印(句号3)[ - 30, -1] [1,30] [31,60] [61,90] [-1,1] [1,10] CDS NA NA NA NA NA NA键NA NA NA NA NA NA交换零速率NA NA NA NA NA NA CDS-bond-basis NA NA NA NA NA NA`似乎有什么问题?