R create monthly returns from daily returns (without xts)-CodePudding

Similar existing topics mostly use stock prices instead of returns, this is why I created a new topic for my question.

I am trying to create geometric monthly portfolio returns out of daily portfolio returns, in this way:

I tried to do this first by using the quantmod package, however, the function monthlyReturn returns improper values for my dataset, which is similar to the sample dataset listed below (also with my code not returning my desired output):

library(dplyr)
library(lubridate)

size = 1e4

df <- data.frame(
Date = sample(seq(as.Date('2020/01/01'), as.Date('2022/01/01'), by="day"), size, replace = TRUE),
Return.Portfolio1 = sample(-2000:2000, size, replace = TRUE)/100000,
Return.Portfolio2 = sample(-3000:3000, size, replace = TRUE)/100000,
Return.Portfolio3 = sample(-4000:4000, size, replace = TRUE)/100000)

df$Month <- lubridate::month(df$Date)
df$Year <- lubridate::year(df$Date)

monthlyreturns <- df %>%
  arrange(Date) %>%
  group_by(Year, Month) %>%
  dplyr::summarise(CumPrdct = (cumprod(buys.minus.sells 1)-1))

The desired output is:

Year    Month   Return
2020    01  0.0123
2020    02  -0.0231

CodePudding user response：

Your solution comes close, you just needed to make it prod instead of cumprod:

library(dplyr)
library(lubridate)

size = 1e4

set.seed(100)
df <- tibble(
  Date = sample(seq(as.Date('2020/01/01'), as.Date('2022/01/01'), by="day"), size, replace = TRUE),
  Return.Portfolio1 = sample(-2000:2000, size, replace = TRUE)/100000,
  Return.Portfolio2 = sample(-3000:3000, size, replace = TRUE)/100000,
  Return.Portfolio3 = sample(-4000:4000, size, replace = TRUE)/100000)

df %>% 
  group_by(Year = year(Date), Month = month(Date)) %>% 
  summarise(
    Return_P1 = prod(Return.Portfolio1   1)-1,
    Return_P2 = prod(Return.Portfolio2   1)-1,
    Return_P3 = prod(Return.Portfolio3   1)-1,
  )
#> `summarise()` has grouped output by 'Year'. You can override using the
#> `.groups` argument.
#> # A tibble: 25 × 5
#> # Groups:   Year [3]
#>     Year Month Return_P1 Return_P2 Return_P3
#>    <dbl> <dbl>     <dbl>     <dbl>     <dbl>
#>  1  2020     1    0.258    0.103     -0.0327
#>  2  2020     2   -0.0911  -0.175     -0.399 
#>  3  2020     3   -0.0140   0.259      0.545 
#>  4  2020     4   -0.116   -0.406      1.09  
#>  5  2020     5    0.555    0.594      0.0872
#>  6  2020     6    0.171   -0.319      0.0246
#>  7  2020     7    0.106   -0.0822    -0.398 
#>  8  2020     8   -0.271    0.239      0.608 
#>  9  2020     9   -0.126    0.131     -0.483 
#> 10  2020    10    0.406   -0.00475   -0.169 
#> # … with 15 more rows