Using functions that require WRDS data

Introduction

To actually use the functions listed below, you will need to tell R how to access the WRDS data on which they depend. For each function, we have listed the WRDS tables on which it depends.

• get_annc_dates(): crsp.dsi
• get_trading_dates(): crsp.dsi
• get_event_dates(): crsp.dsi (through calls to get_annc_dates() and get_trading_dates())
• get_event_cum_rets(): crsp.dsi, crsp.dsedelist, crsp.dsf, crsp.erdport1
• get_event_rets(): As for get_event_cum_rets()
• get_event_cum_rets_mth(): crsp.msi, crsp.msedelist, crsp.msf, crsp.ermport1

library(farr)
library(dplyr, warn.conflicts = FALSE)
library(DBI)

We recommend that you use environment variables to set up your connection to WRDS. The easiest way to do this within R is to execute a line like the following:

Sys.setenv(PGHOST = "wrds-pgdata.wharton.upenn.edu",
           PGPORT = 9737L,
           PGDATABASE = "wrds",
           PGUSER = "your_WRDS_ID", 
           PGPASSWORD = "your_WRDS_password")

Obviously, you should replace your_WRDS_ID and your_WRDS_password with your actual WRDS ID and WRDS password, respectively. To access WRDS data, this code will need to be run each time you open R. But once you have run this code, you do not need to run it again during the same session (i.e., until you close and reopen R).

If the only PostgreSQL database you access is the WRDS database, you could put the values above in .Renviron, a special file that is opened every time you open R.¹ The contents of this file would look something like this:

PGHOST = "wrds-pgdata.wharton.upenn.edu"
PGPORT = 9737L
PGDATABASE = "wrds"
PGUSER = "your_WRDS_ID"
PGPASSWORD="your_WRDS_password"

I recommend the approaches above as they keeps the user-specific aspects of your code separate from the parts of the code that should work for everyone. By using environment variables in Accounting Research: An Introductory Course, we can ensure that the code in the book works for you if you copy it and paste it in your R console.

The current package includes the data frame apple_events, which is derived from data found on Wikipedia.² This data set is made available in R when we call library(farr). Let’s look at the last few rows of this table:

tail(apple_events)

Let’s get return data from CRSP as if we were conducting an event study. We first need to get Apple’s PERMNO so we can look up returns on CRSP. Knowing Apple’s ticker is AAPL helps.

pg <- dbConnect(RPostgres::Postgres(), bigint = "integer")

stocknames <- tbl(pg, sql("SELECT * FROM crsp.stocknames"))

apple_permno <-
  stocknames %>%
  filter(ticker == "AAPL") %>%
  select(permno) %>%
  distinct() %>%
  pull()

So Apple’s PERMNO is 14593 and we can use this to get data from CRSP. In this case, we will get daily returns for Apple (ret) from crsp.dsf and value-weighted “market” returns (vwretd) from crsp.dsi and calculate market-adjusted returns as ret - vwretd. In this case, we will grab all returns since the start of 2005, which covers all the events on apple_events.

dsf <- tbl(pg, sql("SELECT * FROM crsp.dsf"))
dsi <- tbl(pg, sql("SELECT * FROM crsp.dsi"))

apple_rets <-
  dsf %>%
  inner_join(dsi, by = "date") %>%
  mutate(mkt_ret = ret - vwretd) %>%
  select(permno, date, ret, mkt_ret, vol) %>%
  filter(permno == apple_permno,
         date >= "2005-01-01") %>%
  collect()

Apple’s media events extend over multiple days, so our event windows need to also extend over multiple days. To allow for some leakage in the day before the start of the media events and to allow the market some time to process the value implications of the media event, we will set our event window from one trading day before the start of each media event through to one day after the end of the media event. We will use the get_event_dates function from the farr package to this end; behind the scenes, this function uses the get_trading_dates and get_annc_dates functions.³

apple_event_dates <-
  apple_events %>%
  mutate(permno = apple_permno) %>%
  get_event_dates(pg, 
                  end_event_date = "end_event_date",
                  win_start = -1, win_end = +1) 

tail(apple_event_dates)

Another function in farr, get_event_cum_rets, calculates cumulative raw returns and cumulative abnormal returns using two approaches: market-adjusted returns and size-adjusted returns over event windows. Here we use this function to get cumulative returns over the windows around each Apple event.

rets <-
    apple_events %>%
    mutate(permno = apple_permno) %>% 
    get_event_cum_rets(pg,
                       win_start = -1, win_end = +1,
                       end_event_date = "end_event_date")

rets