CODEWITHSUNDEEP
rdplyr
pphong
pphong

Reputation: 57

Rolling window with dplyr to find value of factor

I have a matrix like this

 head(a)
# A tibble: 6 x 4
    date          ROE          ROFE        ROTFE
   <date>        <dbl>         <dbl>        <dbl>
1 2000-01-31  0.033968932  0.0324214815  0.010205926
2 2000-02-29  0.006891111 -0.0003352941 -0.005230147
3 2000-03-31  0.006158519  0.0213992647  0.040399265
4 2000-04-28  0.060022222  0.0151191176  0.047586029
5 2000-05-31 -0.016960000 -0.0287617647 -0.036209559
6 2000-06-30  0.034133577  0.0144456522  0.030756522

I want to pick the value of a factor which has highest cumulative return last 2 months over time. I have done something like this and it works. However, my friend told me that it can be done in one or two lines of dplyr and I'm wondering if you could please show me how to do that.

index = as.Date(unique(a$date))
nmonth = 2;
mean.ROE = numeric()
for (i in 1:(length(index) - nmonth)) { # i = 2
    index1 = index[i]
    index2 = index[nmonth + i]
    index3 = index[nmonth + i+1]
    # Take a 2-month window of ROE returns:
    b = a[a$date >= index1 & a$date < index2,] %>% mutate(cum.ROE = cumprod(1 + ROE)) %>% mutate(cum.ROFE = cumprod(1 + ROFE)) %>% mutate(cum.ROTFE = cumprod(1 + ROTFE))

    # Use the cumulative return over the 2-month window to determine which factor is best.
    mean.ROE1 = ifelse(b$cum.ROE[nmonth] > b$cum.ROFE[nmonth] & b$cum.ROE[nmonth] > b$cum.ROTFE[nmonth], a[a$date == index3,]$ROE, ifelse(b$cum.ROFE[nmonth] > b$cum.ROE[nmonth] & b$cum.ROFE[nmonth] > b$cum.ROTFE[nmonth], a[a$date == index3,]$ROFE, a[a$date == index3,]$ROTFE))

    # Bind the answer to the answer vector
    mean.ROE = rbind(mean.ROE, mean.ROE1)
}

Upvotes: 2

Views: 218

Answers (1)

G. Grothendieck
G. Grothendieck

Reputation: 270020

Create a function maxret which takes 2 + nmonth rows, x, and calculates the cumulative returns, r, for each column of the first two rows. For the largest of those return the value in the last row of x.

Now use rollapplyr to apply it to a rolling window of width 2 + month: 

library(zoo)

maxret <- function(x) {
    r <- apply(1 + x[1:2, ], 2, prod)
    x[2 + nmonth, which.max(r)]
}

z <- read.zoo(as.data.frame(a))
res <- rollapplyr(z, 2 + nmonth, maxret, by.column = FALSE)

giving the zoo series:

 > res
 2000-04-28  2000-05-31  2000-06-30 
 0.06002222 -0.03620956  0.03075652

If you want a data frame use fortify.zoo(res) .

Note: 1 The input was not provided in reproducible form in the question so I have assumed this data.frame:

Lines <- 
"date          ROE          ROFE        ROTFE
1 2000-01-31  0.033968932  0.0324214815  0.010205926
2 2000-02-29  0.006891111 -0.0003352941 -0.005230147
3 2000-03-31  0.006158519  0.0213992647  0.040399265
4 2000-04-28  0.060022222  0.0151191176  0.047586029
5 2000-05-31 -0.016960000 -0.0287617647 -0.036209559
6 2000-06-30  0.034133577  0.0144456522  0.030756522"
a <- read.table(text = Lines, header = TRUE)

Note 2: With the input in Note 1 or with zoo 1.8.1 (the development version of zoo) this line:

z <- read.zoo(as.data.frame(a))

could be simplified to just:

z <- read.zoo(a)

but we have added the as.data.frame part in the main code so it works with tibbles as well as straight data frames even with the current version of zoo on CRAN.

Upvotes: 2

Related Questions