How to estimate rolling correlations and then take the mean of it?

Question

Based on the following data-frame, I would like to compute the rolling correlations (with a window size of 12):

library(rugarch)
library(rmgarch)
data(dji30retw)
Dat = dji30retw[, 1:8, drop = FALSE]

> dput(head(Dat))

structure(list(AA = c(-0.00595239852729524, 0.00595239852729524, 
-0.0149479614358734, 0.0470675108579858, 0.0170944333593002, 
0.0251059211310762), AXP = c(-0.00794285351393668, -0.0258495814613253, 
-0.0265355536259657, -0.0359320092260634, -0.0555200763856309, 
0.0254559199933486), BA = c(-0.00886642920564158, -0.0102302682508148, 
-0.0142397228111357, -0.0237478178500363, -0.046456440823212, 
-0.0590524317817008), BAC = c(-0.0311983708558615, 0, -0.0358461317731357, 
-0.0258794479878207, -0.0304205967007118, -0.0116506172199752
), C = c(-0.0258635105899192, -0.0176216013498196, -0.0134230203321406, 
-0.0944096844710748, -0.0352681388374579, 0.0203052661607457), 
    CAT = c(0.0158733491562901, 0.0411369055604894, -0.046400075604764, 
    -0.00794706169253204, -0.0106952891167477, 0.0369435151916841
    ), CVX = c(-0.0220481372217624, 0.0632438936600297, -0.0165791288029112, 
    -0.0340063679851951, -0.0287101058824313, 0.0112631922787107
    ), DD = c(0.00638979809877117, 0.0354573118367292, -0.0354573118367292, 
    0.00529381860971498, -0.031101702565588, -0.0198026272961791
    )), .Names = c("AA", "AXP", "BA", "BAC", "C", "CAT", "CVX", 
"DD"), row.names = c("1987-03-27", "1987-04-03", "1987-04-10", 
"1987-04-17", "1987-04-24", "1987-05-01"), class = "data.frame")

And then after computing the rolling correlations, I would like to create a data-frame consisting of one column with the average correlation coefficient per time period T (in this case: per week).

Is there anyone out there that could help me out? I would really appreciate that!

Thanks in advance!

Asking people to install 2 packages just for sample data is a bit excessive. You could use `data(sample_matrix, package='xts')` instead. — Joshua Ulrich, Apr 01 '18 at 17:43
See https://stackoverflow.com/questions/49587940/series-of-correlation-coefficient-calculation/49588070#49588070 — G. Grothendieck, Apr 01 '18 at 18:05

Jerome Dineen · Answer 1 · 2018-04-01T18:52:38.573

There are methods in R that are more tailored to time series analysis than the one I'm about to show. Here's a link.

This is a very inelegant solution. I've created my own data for the example:

library(dplyr)

#set seed
set.seed(123)

#initialize matrix
roll_corr <- data.frame(matrix(nrow = 365,ncol = 5))
names(roll_corr) <- c("date","week","sales1","sales2","corr")

#generate sequence of dates
roll_corr$date <- seq(as.Date("2000/01/01"), as.Date("2000/12/30"), by="day")

# calculate week number
roll_corr$week <- as.numeric(roll_corr$date - roll_corr$date[1]) %/% 7

#generate random variates for sales

roll_corr$sales1 <- rnorm(365,500,1000)
roll_corr$sales2 <- runif(365,1000,80000)

#calculate rolling correlation using for loop

for(i in 1:365) {

  roll_corr$corr[i] <- cor(roll_corr$sales1[1:i],roll_corr$sales2[1:i])

}

#use dplyr to group data by week and calculate average correlation

weekly_roll_corr <- roll_corr %>%
  group_by(week) %>%
  summarize(average = mean(corr,na.rm = TRUE))

head(weekly_roll_corr)

  week    average
1    0  0.1480184
2    1 -0.1008872
3    2  0.1265146
4    3  0.2481083
5    4  0.2518001
6    5  0.1892407

How to estimate rolling correlations and then take the mean of it?

1 Answers1