How to compute rolling skewness for trailing 12months sliding window using Daily Returns in R?

Question

I have Daily Returns for Stocks with a column containing Date in the format dd-mm-yy. I want to compute skewness for a month based on its trailing 12months Daily Returns using R. The Data looks like this

For Instance: If there are Daily Returns of two companies from 01/01/2001 to 31/12/2003 then I want to find the skewness from Jan 2002 for each of the companies.

• Skewness for Jan 2002: Based on Daily Returns in the past 12months i.e. 01Jan2001 to 31Dec2001.
• Skewness for Feb 2002: Based on Daily Returns in the past 12months i.e. 01Feb2001 to 31Jan2001.

And so on monthly on a rolling basis where the sliding window width will be 12months.

Note: There's a catch as the number of days in last 12months would not be 365. Since the stock returns are only for trading days(excluding Saturday, Sunday and any Holiday). Every month the trading days would be dynamic in nature. I want something to extract the month from the date and compute for the trailing 12months. Maybe something like grouping based on months and aggregating for trailing 12groups.

set.seed(1)
df<-data.frame(Date=seq(as.Date("2001-01-01"), as.Date("2003-12-31"), by="days"), Company1=rnorm(1095,0,1), Company2=rnorm(1095,0,1))

Input Data:

        Date     Company1    Company2
1   2001-01-01  0.046787710  0.21631639
2   2001-01-02  1.007350200  0.88959702
3   2001-01-03 -0.585340438 -1.10898367
4   2001-01-04  2.359564501 -0.62665947
5   2001-01-05 -0.258663440  1.80257433
6   2001-01-06  0.289608127 -3.08371338
7   2001-01-07  0.269705937  0.13092761
8   2001-01-08 -2.076263400  0.36424857
9   2001-01-09  0.752956413 -0.01024824
10  2001-01-10 -0.297581215  0.62589751
11  2001-01-11  0.439587229 -0.48158102
12  2001-01-12 -0.700782594  0.13597666
13  2001-01-13 -0.083560736  0.03184570
14  2001-01-14  0.883048949  0.17284243
15  2001-01-15  0.201498921 -0.64059292
16  2001-01-16  0.591389036 -1.19668946
17  2001-01-17  0.774895061 -0.66963705
18  2001-01-18  1.663075216  0.32016246
19  2001-01-19 -0.713455482 -1.42976017
20  2001-01-20  1.809244713  1.85308653
21  2001-01-21  0.358761796 -0.87284478
22  2001-01-22 -0.192799009 -0.14865949
23  2001-01-23 -0.126879244 -1.44882295
24  2001-01-24 -0.888239162  1.17851064
25  2001-01-25  1.139707845  0.22734274
26  2001-01-26  0.236909406 -1.12476606
27  2001-01-27  0.281275148  0.14908310
28  2001-01-28 -0.404590422 -0.78850844
29  2001-01-29  0.573109940  1.32003315
30  2001-01-30 -2.014078486 -0.36894095
31  2001-01-31 -1.438956369  1.06879518
32  2001-02-01  2.067691040 -0.74283474
33  2001-02-02  0.195995947  1.39753672
34  2001-02-03 -0.582291845  0.21987888
35  2001-02-04 -0.462393447 -1.14957969
36  2001-02-05  0.145901137  0.57741057
37  2001-02-06 -0.358606042 -1.06126753
38  2001-02-07 -1.184867338 -0.85388016
39  2001-02-08 -1.331819366 -0.06583488
40  2001-02-09 -0.284243432  1.24550387
41  2001-02-10  1.625322326 -0.34987800
42  2001-02-11 -1.115882265  0.54337237
43  2001-02-12  0.379784066  0.57215836
44  2001-02-13 -0.643792275 -0.59830689
45  2001-02-14  0.271188752  1.29537846
46  2001-02-15 -0.171287972  0.55311033
47  2001-02-16 -0.847849267 -1.35727918
48  2001-02-17  1.935119202  0.68036412
49  2001-02-18  0.171950923  1.02874683
50  2001-02-19 -1.458405950  0.32483905
51  2001-02-20  1.042342330 -1.61234419
52  2001-02-21  0.206411454 -0.08980562
53  2001-02-22  0.116044124 -0.75188707
54  2001-02-23 -0.080576867 -0.27822619
55  2001-02-24 -0.217406783 -0.48112626
56  2001-02-25 -0.042067201 -0.50870525
57  2001-02-26 -0.034464590  0.46473191
58  2001-02-27  0.277544111 -0.98551626
59  2001-02-28 -0.535228414  1.78895267

The function for skewness:

skewness<-function(x)
{
  m3<-mean((x-mean(x))^3)
  skewness<-m3/(sd(x)^3)
  skewness
}

Output something like:

Month   Company1                  Company2
Jan2002 Skewness(Jan2001:Dec2001) Skewness(Jan2001:Dec2001)
Feb2002 Skewness(Feb2001:Jan2002) Skewness(Feb2001:Jan2002)
Mar2002 Skewness(Mar2001:Feb2002) Skewness(Mar2001:Feb2002)

Answer 1

First define Skewness to be like skewness but for a subset of rows given by ix of a data frame or matrix, data. Then compute ym, the year and month as a yearmon vector having one element per row of df and also compute the unique elements except for the first 12 as ymu. Note that yearmon elements are measured in years and fractions of a year so if y is a yearmon object then y-1 is a year ago.

Next compute a list, rowList, such that the ith component is a vector of the row numbers for those rows that should be used in calculating the skewness of the window that starts at ymu[i]. It depends on df but in the case of df shown in the question it equals list(1:365, 32:396, ..., 700:1064). Finally create a data frame containing the year/month after the one year period for which the skewness is calculated, the number of rows of df used in the calculation of skewness and the skewness values.

library(zoo)

Skewness <- function(data, ix) apply(data[ix, ], 2, skewness)

ym <- as.yearmon(df$Date)
ymu <- tail(unique(ym), -12)
rowList <- lapply(ymu, function(x) which(ym >= x-1 & ym < x))
out <- data.frame(YearMonth = ymu, 
                  n = lengths(rowList),
                  t(sapply(rowList, Skewness, data = df[-1])))

head(out)
##   YearMonth   n     Company1    Company2
## 1  Jan 2002 365 -0.054235132  0.09753488
## 2  Feb 2002 365 -0.021812097  0.11415098
## 3  Mar 2002 365 -0.008200845  0.11443663
## 4  Apr 2002 365 -0.087797237  0.09002947
## 5  May 2002 365 -0.090151360  0.07812791
## 6  Jun 2002 365  0.024598986 -0.01895502

Check

To double check we note that the first row of the output gives the skewness of the first 365 rows for each column of df[-1] for the df in the question so we have the following which does match with the output for the Jan 2002 row above.

Skewness(df[-1], 1:365)
##    Company1    Company2 
## -0.05423513  0.09753488 

Answer 2

You can use any function as an argument to the slide_* functions from the slider package. The following code calculates a 365 days rolling skewness for the sample dataset you provided.

library(dplyr) # for group_by and pipe
library(tidyr) # for pivot_*
library(slider)  # for rolling window functions
library(moments) # for skewness function
df<-data.frame(Date=seq(as.Date("2001-01-01"), as.Date("2003-12-31"), by="days"), Company1=rnorm(1095,0,1), Company2=rnorm(1095,0,1))
df %>%
  pivot_longer(-Date, names_to="Company", values_to="X") %>%
  group_by(Company) %>%
  arrange(Date) %>%
  mutate(
    rollskew = slide_dbl(X, moments::skewness, .before=365, .complete=TRUE)
  ) %>%
  ungroup() %>%
  pivot_wider(names_from = Company, values_from = c(X, rollskew)) %>%
  tail(12)

will output

# A tibble: 12 x 5
   Date       X_Company1 X_Company2 rollskew_Company1 rollskew_Company2
   <date>          <dbl>      <dbl>             <dbl>             <dbl>
 1 2003-12-20      0.599      0.663             0.211            -0.153
 2 2003-12-21      1.13       0.457             0.204            -0.163
 3 2003-12-22     -0.802     -1.14              0.210            -0.159
 4 2003-12-23      1.78       1.45              0.205            -0.165
 5 2003-12-24      0.916     -0.879             0.205            -0.141
 6 2003-12-25     -1.60      -0.259             0.208            -0.136
 7 2003-12-26     -1.12       0.914             0.208            -0.144
 8 2003-12-27     -0.404     -0.319             0.212            -0.147
 9 2003-12-28      0.461     -1.51              0.208            -0.147
10 2003-12-29     -0.436      1.68              0.207            -0.140
11 2003-12-30     -1.74       0.657             0.203            -0.148
12 2003-12-31     -0.119      0.228             0.209            -0.156