On this page, we download the stock price data so that we can later calculate the abnormal return after the earnings announcements.
library(tidyverse)
library(here)
library(RPostgres)
library(dbplyr)
i_am("freaky_friday/download_stocks.qmd")here() starts at /Users/stijnmasschelein/Library/CloudStorage/Dropbox/Teaching/lecturenotes/method_packageearn_ann <- readRDS(here("data", "freaky_friday", "earn_ann.RDS")) wrds <- dbConnect(Postgres(),
host='wrds-pgdata.wharton.upenn.edu',
port=9737,
dbname='wrds',
user='stimas',
sslmode='require')This section sets the parameters that we will need to limit the download. Following Dellavigna and Pollet (2009), the beginning date is 300 days before the first earnings announcement and the end date is 75 days after the last earnings announcement. I could have done this for every earnings announcement specificially but Finally, I keep the permno identifiers because these are the only stocks we want the data from.
crsp_input <- earn_ann %>%
summarise(begin = min(anndat) - 300, end = max(anndat) + 75, .by = permno) %>%
glimpse()Rows: 8,754
Columns: 3
$ permno <dbl> 10560, 10656, 88784, 10659, 87832, 84606, 80585, 88836, 87771, …
$ begin <date> 2003-12-26, 2002-07-12, 2001-01-17, 2001-11-09, 2003-05-06, 20…
$ end <date> 2006-07-10, 2006-07-19, 2006-07-18, 2006-05-23, 2005-01-16, 20…permnos <- crsp_input$permno
begin_date <- min(crsp_input$begin)
end_date <- max(crsp_input$end)I use the same syntax as before to call the WRDS databases as before with sql interspersed with the R parameters created in the previous code block. We get the daily volume, return, price, shares outstanding, cumulative factor to adjust price, and cumulative factor to adjust shares. The latter two are adjustment factors for stock splits and dividends which we probably will not need but if we do we have them.
This is by far the largest download from WRDS and this is why it has it’s own page. We do not want to rerun this more than strictly necessary.
crsp_query <- tbl(wrds, in_schema("crsp_a_stock", "dsf")) %>%
filter(permno %in% permnos, date >= begin_date, date <= end_date) %>%
select(permno, date, vol, ret, prc, shrout, cfacpr, cfacshr)
all_stocks <- collect(crsp_query)
saveRDS(all_stocks, here("data", "freaky_friday", "all_stocks.RDS"))
print(all_stocks)# A tibble: 14,967,132 × 8
permno date vol ret prc shrout cfacpr cfacshr
<int> <date> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
1 10002 1994-03-10 700 -0.00952 13 2999 1.5 1.5
2 10002 1994-03-11 200 0.0577 13.8 2999 1.5 1.5
3 10002 1994-03-14 0 -0.0455 -13.1 2999 1.5 1.5
4 10002 1994-03-15 0 0 -13.1 2999 1.5 1.5
5 10002 1994-03-16 1700 0.00952 13.2 2999 1.5 1.5
6 10002 1994-03-17 0 -0.00943 -13.1 2999 1.5 1.5
7 10002 1994-03-18 0 0 -13.1 2999 1.5 1.5
8 10002 1994-03-21 0 0.00457 -13.1 2999 1.5 1.5
9 10002 1994-03-22 2000 0.0190 13.4 2999 1.5 1.5
10 10002 1994-03-23 0 -0.00935 -13.2 2999 1.5 1.5
# ℹ 14,967,122 more rowsOne important footnote is that the price is negative on days where there were no trades. This might be important going forward.