Starting period and ending period-CodePudding

I have created this data table in R and now I would like to compute the cumulative return of a stock (stock is shown by ID and a stock can be seen on different time periods). If I have month t, the cumulative return should be computed from month t-11 to month t - 1. Columns new_11 and new_1 are helping columns which show me for eg on first row, for Dec 1980 cumulative return should be computed from Jan 1980 to Nov 1980, grouped by Id. I know the cumulative return formula in R is cumprod(1 RETURN), but I do not know how to write the code according to starting and ending month.

Can anyone please help me? Press to see the table -> 1

CodePudding user response：

Try this using slider to evaluate the prior sliding 11 months:

library(tidyverse)
library(lubridate)
library(tsibble)
library(slider)

tribble(
  ~id, ~yr_mon, ~return,
  1, "Nov 1979", 0.10,
  1, "Dec 1979", 0.11,
  1, "Jan 1980", 0.12,
  1, "Feb 1980", 0.13,
  1, "Mar 1980", 0.14,
  1, "Apr 1980", 0.15,
  1, "May 1980", 0.16,
  1, "Jun 1980", 0.17,
  1, "Jul 1980", 0.18,
  1, "Aug 1980", 0.19,
  1, "Sep 1980", 0.20,
  1, "Oct 1980", 0.21,
  1, "Nov 1980", 0.22,
  1, "Dec 1980", 0.23
) |> 
  mutate(yr_mon = parse_date(yr_mon, format = c("%b %Y")) |> yearmonth()) |> 
  arrange(id, yr_mon) |> 
  group_by(id) |> 
  mutate(cum_ret = slide_dbl(lag(return), ~ last(cumprod(1   .x)), .before = 10, .complete = TRUE))

#> # A tibble: 14 × 4
#> # Groups:   id [1]
#>       id   yr_mon return cum_ret
#>    <dbl>    <mth>  <dbl>   <dbl>
#>  1     1 1979 Nov   0.1    NA   
#>  2     1 1979 Dec   0.11   NA   
#>  3     1 1980 Jan   0.12   NA   
#>  4     1 1980 Feb   0.13   NA   
#>  5     1 1980 Mar   0.14   NA   
#>  6     1 1980 Apr   0.15   NA   
#>  7     1 1980 May   0.16   NA   
#>  8     1 1980 Jun   0.17   NA   
#>  9     1 1980 Jul   0.18   NA   
#> 10     1 1980 Aug   0.19   NA   
#> 11     1 1980 Sep   0.2    NA   
#> 12     1 1980 Oct   0.21    4.63
#> 13     1 1980 Nov   0.22    5.10
#> 14     1 1980 Dec   0.23    5.60

# Check for Oct 1980 (Nov to Sep)
cumprod(1   seq(0.1, 0.2, 0.01)) |> last()
#> [1] 4.63307

^{Created on 2022-06-08 by the reprex package (v2.0.1)}