R: Convert daily returns to monthly returns

2
votes

I have an xts of daily returns and I'd like to convert it to monthly returns.

I can find tonnes of threads to convert daily prices to period returns, but I need to convert daily returns.

Having followed the advice in this thread, which works well, I noticed that the returns are not geometric, they're arithmetic.

Therefore, I need something like cumprod(x+1)^(365/12)-1.

However, replacing sum(cx) with that doesn't work.

Here's my code as it stands:

 # Generate data like the type I'm working with    
    testdata <- cbind(rnorm(100,0.0001,0.01),rnorm(100,0.0001,0.01))
    testdata <- as.xts(testdata, order.by = seq(Sys.Date()-99,Sys.Date(),1))


   myFun <- function(x) {
    # need coredata, so c.xts will not be dispatched
     cx <- coredata(x)
     Return = sum(cx)
     }

   MonthlyReturns <- NULL
   for (i in 1:ncol(testdata)){
     MonthlyReturns <- cbind(MonthlyReturns,period.apply(testdata[,i], endpoints(testdata[,i], "months"), 
     myFun))
   }

Any help appreciated!

EDIT - The output should be the same format as the input - a table of monthly returns instead of daily. Either xts or dataframe / matrix.

EDIT - For those interested in the origin of the returns matrix, I'm using the Return.annualized function from the Performance Analytics package as shown here. (Actually, I've modified it by using Return.cumulative, which is much faster). So yes, although I do have a price matrix and can easily calculate monthly returns from that, I have additional columns in my daily returns matrix from other calculations, hence I need to convert the daily returns, not the daily prices.

2
rzooquantmodperformanceanalytics
Can you share what you want the output to look like? – Gautam
Updated original post. Basically the output should be the same as input, except by month instead of by day. – Jimbo Mahoney
To clarify returns, this is already calculated against the basis? I guess a dput() would be useful here. – Chris

2 Answers

4
votes

As an alternative to the accepted solution a much faster way ( > 5 times faster ) to get monthly returns is to combine the aggregate function with cumprod. 

 system.time(aggregate(testdata,as.yearmon,function(x) tail(cumprod(1 + x) -1,1)))
   user  system elapsed 
  0.021   0.002   0.023 
 system.time(apply.monthly(testdata, Return.cumulative))
   user  system elapsed 
  0.116   0.002   0.118

data:

testdata <- as.xts(cbind(rnorm(10000,0.0001,0.01),rnorm(100,0.0001,0.01)), order.by = seq(Sys.Date()-9999,Sys.Date(),1))
2
votes

Since you want monthly cumulative daily returns, we can apply the Return.cumulative function from PerformanceAnalytics on a monthly basis using the apply.monthly function from xts. This gives you what you want. Nice simple solution, no need to write your own function.

library(PerformanceAnalytics) # for Return.cumulative function
library(quantmod) # loads xts which has apply.monthly function 

MonthlyReturns <- apply.monthly(testdata, Return.cumulative)
MonthlyReturns
                  [,1]         [,2]
2020-01-31 -0.09507546 -0.090607862
2020-02-29  0.04056104  0.001859122
2020-03-31  0.01451002  0.117231568
2020-04-12  0.01502248  0.026660881