Do a rolling regression only on specific dates

Question

I am trying to do the following rolling regression: stock_return = α + β market_return.

My dataset "data" looks as follows:

Company Date        stock_return    market_return       Alpha   Beta
AAPL    01.01.2014  3%              4%
…
AAPL    31.12.2019  5%              1%
MSFT    01.01.2014  2%              4%
…
MSFT    31.12.2019  6%              1%

Moreover, I have a dataframe with events at which the regression should be performed:

Company Date        
AAPL    05.02.2015
…   
MSFT    04.08.2018

I am using the runner package for the rolling regression:

running_regression <- function(z) {
  coef(lm(stock_return ~ market_return , data = as.data.frame(z)))
}

output <- runner(seq_along(data$market_return), 
                         k = 180, 
                         lag = 5,
                         at = ?
                         f = running_regression)

However, I struggle with my function and what to put into "at" to just do a regression for the events in my second dataframe.

Please see below a minimal example:

    Date <- seq(from = as.Date("2014-01-01"), to = as.Date("2019-12-31"), by = 'day')
    Date <- format(Date, format="%d.%m.%Y")

    Company.name <- c(replicate(2191, ""))
    AAPL <- data.frame(Company.name = "AAPL",Date)
    market_return <- c(rnorm(2191))
    AAPL <- cbind(AAPL, market_return)
    MSFT <- c(replicate(2191, "MSFT"))
    MSFT <- data.frame(Company.name = "MSFT",Date)
    MSFT <- cbind(MSFT, market_return)
    df <- rbind(AAPL, MSFT)
    stock_return <- c(rnorm(4382))
    df <- cbind(df,stock_return)
    df[,"Alpha"] <- NA
    df[,"Beta"] <- NA

    Company.name2 <- c(replicate(450, "AAPL"),replicate(450, "MSFT"))
    Event_date <- sample(seq(as.Date('2015/01/01'), as.Date('2019/12/31'), by="day"), 900)
    Event_date <- format(Event_date, format="%d.%m.%Y")
    df2 <- data.frame(Company.name2, Event_date)

    #____

    running_regression <- function(z) {
      coef(lmList(stock_return ~ market_return | Company.name, data=z))
    }


runner(df$market_return, 
       k = 180, 
       lag = 5,
       f = running_regression)

user_3495076 · Answer

Since runner version 0.3.5 you can specify x = df and do running regression on windows from data.frame. Because you mutate df2 you have to subset relevant part of df by x = df[df$Company.name == Company.name2,] - you have to do the same with idx.

running_regression_intercept <- function(x) {
  coef(lm(stock_return ~ market_return, data = x))[1]
}

running_regression_slope <- function(x) {
  coef(lm(stock_return ~ market_return, data = x))[2]
}

library(dplyr)
library(runner)
df2 %>%
  group_by(Company.name2) %>%
  mutate(
    intercept = runner(
      x = df[df$Company.name ==  Company.name2[1], ],
      k = "180 days",
      lag = "5 days",
      idx = df$Date[df$Company.name == Company.name2[1]],
      at = Event_date,
      f = running_regression_intercept,
    ),
    slope = runner(
      x = df[df$Company.name == Company.name2[1], ],
      k = "180 days",
      lag = "5 days",
      idx = df$Date[df$Company.name == Company.name2[1]],
      at = Event_date,
      f = running_regression_slope
    )
  )

# Company.name2 Event_date    alpha     beta
#                          
#   1 AAPL          2017-01-12  0.0114   0.00488
#   2 AAPL          2017-07-31 -0.0654   0.00574
#   3 AAPL          2019-02-27 -0.0861   0.0310 
#   4 AAPL          2018-09-06  0.0405  -0.0630 
#   5 AAPL          2015-09-03 -0.121   -0.0246 
#   6 AAPL          2018-11-20 -0.0283  -0.0254 
#   7 AAPL          2015-07-03 -0.116   -0.0186 
#   8 AAPL          2015-02-03  0.102    0.0409 
#   9 AAPL          2017-03-16 -0.0157   0.0124 
#   10 AAPL          2019-06-08 -0.00302  0.0532

I needed to modify your data a bit because format changed Event.Date from Date to character.

Date <- seq(from = as.Date("2014-01-01"), 
            to = as.Date("2019-12-31"), 
            by = 'day')
market_return <- c(rnorm(2191))

AAPL <- data.frame(
  Company.name = "AAPL", 
  Date =  Date,
  market_return = market_return
)

MSFT <- data.frame(
  Company.name = "MSFT", 
  Date = Date,
  market_return = market_return
)

df <- rbind(AAPL, MSFT)
df$stock_return <- c(rnorm(4382))
df <- df[order(df$Date),]

df2 <- data.frame(
  Company.name2 = c(replicate(450, "AAPL"), replicate(450, "MSFT")), 
  Event_date = sample(
    seq(as.Date('2015/01/01'), 
        as.Date('2019/12/31'), 
        by="day"),
    size =  900)
  )

user_12258459 · Answer

Hope I got you correct,

If you want to only run the regression on the specific dates in df2, a quick solution is merge them using the relevant names:

head(merge(df2,df,by.x=c("Company.name2","Event_date"),by.y=c("Company.name","Date")))
  Company.name2 Event_date market_return stock_return Alpha Beta
1          AAPL 01.01.2016    -0.4814525   -0.9864268    NA   NA
2          AAPL 01.01.2019    -1.3093155    0.8357707    NA   NA
3          AAPL 01.02.2017     1.5059626   -1.3181592    NA   NA
4          AAPL 01.03.2015    -1.3226590    1.4786993    NA   NA
5          AAPL 01.03.2016     2.1394338   -0.8900286    NA   NA
6          AAPL 01.03.2019     0.6035526    0.5453212    NA   NA

You can see the dates are messed up, so it makes sense to sort your df2 by company and date first:

df2 = df2[order(df2$Company.name2,
as.Date(as.character(df2$Event_date),format="%d.%m.%Y")),]

df_merged = merge(df2,df,
by.x=c("Company.name2","Event_date"),
by.y=c("Company.name","Date"),sort=FALSE)

To do the rolling regression you can use lmList here but it will get a bit more complicated. Below I use roll_lm from roll to calculate the coefficient.. Don't quite understand your lag, maybe you can elaborate on that, but there's no way you can do a regression on runner:

library(roll)
result = by(df_merged,df_merged$Company.name2,function(z){
roll_lm(z$market_return,z$stock_return,180)})

tail(result$AAPL$coefficients)
       (Intercept)         x1
[445,] -0.07817682 0.10662762
[446,] -0.06440454 0.09257577
[447,] -0.07007445 0.09461642
[448,] -0.05917523 0.09582312
[449,] -0.05292590 0.10025369
[450,] -0.04930798 0.09911921

Do a rolling regression only on specific dates

2 Answers