Code to generate fake return data and estimate rolling betas.

regr_slope.R

library(dplyr, warn.conflicts = FALSE)
library(dbplyr, warn.conflicts = FALSE)
library(DBI)
set.seed(2023)
n <- 100
betas <- tibble(id = 1:n, beta = runif(n, min = 0.7, max = 1.3))

start_date <- as.Date("1980-01-01")
end_date <- as.Date("2023-01-01")

rets <-
  tibble(date = seq.Date(start_date, end_date, by = "month"),
         r_m = rnorm(n = length(date), sd = 0.05)) |>
  cross_join(betas) |>
  mutate(e_i = rnorm(n = length(date), sd = 0.1)) |>
  mutate(r_i = beta * r_m + e_i) |>
  select(id, date, r_i, r_m) |>
  arrange(id, date)

rets

db <- dbConnect(RPostgres::Postgres())

rets_db <- copy_to(db, rets, overwrite = TRUE)
rets_db |>
  group_by(id) |>
  window_order(date) |>
  window_frame(-60, 0) |>
  mutate(beta = regr_slope(r_i, r_m))

betas_db <-
  rets_db |>
  group_by(id) |>
  window_order(date) |>
  window_frame(-60, 0) |>
  mutate(beta = regr_slope(r_i, r_m))

betas_db |> show_query()
tryCatch(betas_db)

db <- dbConnect(duckdb::duckdb())

rets_db <- copy_to(db, rets, overwrite = TRUE)
rets_db |>
  group_by(id) |>
  window_order(date) |>
  window_frame(-60, 0) |>
  mutate(beta = regr_slope(r_i, r_m))

betas_all <-
  rets_db |>
  group_by(id) |>
  summarize(beta = regr_slope(r_i, r_m))

betas_all |> show_query()
betas_all

betas_db <-
  rets_db |>
  group_by(id) |>
  window_order(date) |>
  window_frame(-60, 0) |>
  mutate(beta = regr_slope(r_i, r_m))

betas_db |> show_query()
try(betas_db)

w <- paste0(" OVER (PARTITION BY id ",
                "ORDER BY date ", 
                "ROWS BETWEEN 59 PRECEDING AND CURRENT ROW)")

betas_db <-
  rets_db |>
  mutate(beta = sql(paste0("regr_slope(r_i, r_m)", w)),
         n_obs = sql(paste0("regr_count(r_i, r_m)", w)))

betas_db |> show_query()
betas_db

betas_db |>
  mutate(beta = if_else(n_obs > 48, beta, NA)) |>
  arrange(id, desc(date))