Created
May 10, 2023 22:14
-
-
Save iangow/caeb321c123be85585ad60d051893c0c to your computer and use it in GitHub Desktop.
Code to calculate betas using DuckDB
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
library(tidyverse) | |
library(DBI) | |
library(dbplyr) | |
ddb <- dbConnect( | |
duckdb::duckdb(), | |
"data/tidy_finance.duckdb", | |
read_only = FALSE | |
) | |
crsp_monthly <- tbl(ddb, "crsp_monthly") | |
crsp_daily <- tbl(ddb, "crsp_daily") | |
factors_ff_monthly <- tbl(ddb, "factors_ff_monthly") | |
factors_ff_daily <- tbl(ddb, "factors_ff_daily") | |
window <- "OVER (PARTITION BY permno ORDER BY month | |
RANGE BETWEEN INTERVAL 59 MONTHS PRECEDING | |
AND INTERVAL 0 MONTHS FOLLOWING)" | |
beta_sql <- sql(paste("regr_slope(ret_excess, mkt_excess)", window)) | |
n_sql <- sql(paste("count(*)", window)) | |
max_sql <- sql(paste("max(month)", window)) | |
min_sql <- sql(paste("min(month)", window)) | |
beta_monthly <- | |
crsp_monthly |> | |
left_join(factors_ff_monthly, by = "month") |> | |
mutate(beta_monthly = beta_sql, | |
n = n_sql, | |
max_month = max_sql, | |
min_month = min_sql) |> | |
ungroup() |> | |
# Check that months are consecutive | |
mutate(n_months = date_diff('month', min_month, max_month) + 1) |> | |
filter(n >= 48, n_months == n) |> | |
select(permno, month, beta_monthly) |> | |
filter(!is.na(beta_monthly)) %>% | |
compute() | |
window <- "OVER (PARTITION BY permno ORDER BY month | |
RANGE BETWEEN INTERVAL 2 MONTHS PRECEDING | |
AND INTERVAL 0 MONTHS FOLLOWING)" | |
beta_sql <- sql(paste("regr_slope(ret_excess, mkt_excess)", window)) | |
n_sql <- sql(paste("count(*)", window)) | |
beta_daily <- | |
crsp_daily |> | |
left_join(factors_ff_daily, by = "date") |> | |
select(permno, month, ret_excess, mkt_excess) |> | |
filter(!is.na(ret_excess)) |> | |
mutate(beta_daily = beta_sql, n = n_sql) %>% | |
filter(n >= 50) %>% | |
select(permno, month, beta_daily) %>% | |
distinct() | |
dbExecute(ddb, "DROP TABLE IF EXISTS beta_alt") | |
beta_alt <- | |
beta_monthly |> | |
full_join(beta_daily, by = c("permno", "month")) |> | |
arrange(permno, month) %>% | |
compute(name = "beta_alt", temporary = FALSE) | |
dbDisconnect(ddb, shutdown = TRUE) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment