I have daily return data over a period of four years for a panel data set. Now I would like to calculate the annual return with a rolling window in the following way:
At the beginning, use 30 days as time window to calculate the rolling return by taking the average of the return and multiply by 250. I know I will lose the first 30 observations for that.
When I have more than 30 days for the rolling window (in my data it would be 30.09.2018) then use 31 days. On 01.10.2018 use 32 days etc. until you reach a time window of 250 days. Then keep 250 days as rolling window. Always compute the annual return by taking the average of the return in the time window and multiply by 250.
My data looks like this: I haven't included all the dates as it would be to large to include here so there are only some dates in the data below.
structure(list(Date = c("31.08.2018", "01.09.2018", "02.09.2018",
"03.09.2018", "04.09.2018", "05.09.2018", "06.09.2018", "07.09.2018",
"08.09.2018", "09.09.2018", "10.09.2018", "11.09.2018", "12.09.2018",
"13.09.2018", "14.09.2018", "15.09.2018", "16.09.2018", "17.09.2018",
"18.09.2018", "19.09.2018", "20.09.2018", "21.09.2018", "22.09.2018",
"23.09.2018", "24.09.2018", "25.09.2018", "26.09.2018", "27.09.2018",
"28.09.2018", "29.09.2018", "30.09.2018", "01.10.2018", "02.10.2018",
"03.10.2018", "04.10.2018", "05.10.2018", "06.10.2018", "07.10.2018",
"31.08.2018", "01.09.2018", "02.09.2018", "03.09.2018", "04.09.2018",
"05.09.2018", "06.09.2018", "07.09.2018", "08.09.2018", "09.09.2018",
"10.09.2018", "11.09.2018", "12.09.2018", "13.09.2018", "14.09.2018",
"15.09.2018", "16.09.2018", "17.09.2018", "18.09.2018", "19.09.2018",
"20.09.2018", "21.09.2018", "22.09.2018", "23.09.2018", "24.09.2018",
"25.09.2018", "26.09.2018", "27.09.2018", "28.09.2018", "29.09.2018",
"30.09.2018", "01.10.2018", "02.10.2018", "03.10.2018", "04.10.2018",
"05.10.2018", "06.10.2018", "07.10.2018"), Name = c("A", "A",
"A", "A", "A", "A", "A", "A", "A", "A", "A", "A", "A", "A", "A",
"A", "A", "A", "A", "A", "A", "A", "A", "A", "A", "A", "A", "A",
"A", "A", "A", "A", "A", "A", "A", "A", "A", "A", "B", "B", "B",
"B", "B", "B", "B", "B", "B", "B", "B", "B", "B", "B", "B", "B",
"B", "B", "B", "B", "B", "B", "B", "B", "B", "B", "B", "B", "B",
"B", "B", "B", "B", "B", "B", "B", "B", "B"), Return = c(-0.021071566,
0.005862582, -0.005960414, 0.012376918, 0.000768618, -0.005279988,
-0.023441498, -0.006774054, -0.002641439, -0.003987006, -0.001579702,
0.00533432, 0.01881727, 0.009697647, -0.008075897, 0.007608133,
0.008286782, 0.003794405, -0.001018981, -0.007252921, -0.008454767,
-0.002505217, -0.009758029, 0.000479896, -0.002947385, -0.009147111,
0.008870721, -0.002740876, 0.006288296, 0.001098006, 0.001815974,
0.005788253, 0.005000533, 0.005697275, 0.01245532, 0.002927324,
-0.000641278, 0.005642696, -0.002631572, -0.004581003, -0.020361952,
0.000433813, -0.00706271, 0.003822377, -0.004783566, -0.012040944,
-0.016589789, 0.002386652, -0.003800319, -0.018014731, -0.001018981,
-0.007252921, -0.008454767, -0.002505217, -0.009758029, 0.000479896,
-0.002947385, -0.009147111, 0.008870721, -0.002740876, 0.006288296,
0.001098006, 0.001815974, 0.005788253, 0.005000533, 0.005697275,
0.01245532, 0.002927324, 0.006288296, 0.001098006, 0.001815974,
0.005788253, 0.005000533, 0.006288296, 0.001098006, 0.001815974
)), class = "data.frame", row.names = c(NA, -76L))
I have started with the code below:
Data <- Data %>%
group_by(Name) %>%
mutate(Annual_Return=(mean(Return)*250)
However, I have difficulties to include the rolling window here. Could someone help me?
I think slider::slide_dbl what you were looking for.
For example, slide_dbl(1:10, mean, .before = 2, .after = 0) returns
[1] 1.0 1.5 2.0 3.0 4.0 5.0 6.0 7.0 8.0 9.0
that until be able to take a mean of three values, it takes mean till then.(1->1, 1 2 -> 1.5)
So, for your data,
library(slider)
library(dplyr)
Data %>%
group_by(Name) %>% # not sure if you want this
mutate(ma = slider::slide_dbl(Return, mean, .before = 249, .after = 0) * 250) %>%
dplyr::slice(-1:-29) # to remove first 29 rows per group that you want to start from 30 days
Date Name Return ma
<chr> <chr> <dbl> <dbl>
1 29.09.2018 A 0.00110 -0.278
2 30.09.2018 A 0.00182 -0.254
3 01.10.2018 A 0.00579 -0.201
4 02.10.2018 A 0.00500 -0.157
5 03.10.2018 A 0.00570 -0.111
6 04.10.2018 A 0.0125 -0.0185
7 05.10.2018 A 0.00293 0.00230
8 06.10.2018 A -0.000641 -0.00209
9 07.10.2018 A 0.00564 0.0351
10 29.09.2018 B 0.00293 -0.639
11 30.09.2018 B 0.00629 -0.567
12 01.10.2018 B 0.00110 -0.541
13 02.10.2018 B 0.00182 -0.511
14 03.10.2018 B 0.00579 -0.453
15 04.10.2018 B 0.00500 -0.405
16 05.10.2018 B 0.00629 -0.350
17 06.10.2018 B 0.00110 -0.333
18 07.10.2018 B 0.00182 -0.312