Joining CRSP Monthly Stock and Event Data

# how to merge these two crsp data sets


# how to merge these two crsp data sets












I'm not totally confident on how to merge these two monthly CRSP data sets. As I write this, it comes from two databases: `crsp.mse` and `crsp.msf` (the documentation on their site does not agree with the deprecation errors I get when I run my code). "mse" stands for "monthly stock events" I assume, and "msf" stands for "monthly stock file."

Here's what they look like from an R console:

```
> head(df_crsp_mse)
  shrcd dlret dlstcd dlpdt exchcd permco permno       date                          comnam    cusip
1    11    NA     NA  <NA>      1   7999  10051 2020-02-14                      HANGER INC 41043F20
2    11    NA     NA  <NA>      2   7978  10028 2020-02-24                     ENVELA CORP 29402E10
3    11    NA     NA  <NA>      3   7992  10044 2021-02-23 ROCKY MOUNTAIN CHOC FAC INC NEW 77467X10
4    11    NA     NA  <NA>      3   7976  10026 2021-04-27          J & J SNACK FOODS CORP 46603210
5    11    NA     NA  <NA>      1  22168  10145 2021-04-06     HONEYWELL INTERNATIONAL INC 43851610
6    11    NA     NA  <NA>      3   6331  10066 2021-03-29          FRANKLIN WIRELESS CORP 35518410
> head(df_crsp_msf)
           ret         retx    prc shrout permno permco       date
1 -0.100016318 -0.100016318 165.84  18919  10026   7976 2020-01-31
2  0.607407451  0.607407451   2.17  26924  10028   7978 2020-01-31
3 -0.075643353 -0.075643353  71.12  29222  10032   7980 2020-01-31
4 -0.098591536 -0.098591536   8.32   6000  10044   7992 2020-01-31
5 -0.115175672 -0.115175672  24.43  37338  10051   7999 2020-01-31
6  0.005706988  0.005706988  15.86 105413  10065  20023 2020-01-31
```

This video from WRDS that shows some SAS code seems to suggest doing a one-sided join (retain all msf entries but not necessarily mse), and to truncate the date to only look at months and years. That makes sense to me because you can't really merge on date if you don't do this.

However, I don't even understand the point of the `mse` data set. There is no row that doesn't have an `NA`. All exchange ids correspond with typical exchanges (amex, nyse, nasdaq). What's the point of these super specific dates? Maybe the point is to get some extra column information (such as exchange ids corresponding with a company code), but this doesn't have to do with an "event" in my opinion.

For posterity, here's what I have so far. I'd love some feedback on why I would even need to do this, though.

```
########################
# set up db connection #
########################
# Sys.setenv(WRDS_PW = "type_your_password_here_but_dont_let_others_see_it")

startDate <- '2020-01-01'

library(RPostgres)
library(dplyr)

wrds <- dbConnect(Postgres(),
                  host = 'wrds-pgdata.wharton.upenn.edu',
                  port = 9737,
                  dbname = 'wrds',
                  sslmode = 'require',
                  user = 'yourusernbame',
                  password=Sys.getenv("WRDS_PW", unset = NA))

#########################
# get crsp monthly data #
#########################

# get "monthly stock events" data
sqlQuery <- paste("select shrcd, dlret,dlstcd,dlpdt, exchcd, permco, permno, date, comnam, cusip",
                  "from crsp.mse",
                  "where date >= '", startDate, 
                  "'and shrcd in (10,11)") # only keep common shares
res <- dbSendQuery(wrds, sqlQuery) 
df_crsp_mse <- dbFetch(res, n = -1)
dbClearResult(res)

# get price "monthly stock file" information
sqlQuery <- paste("select ret, retx, prc, shrout, permno, permco, date",
                  "from crsp.msf", 
                  "where date >= '", startDate,"'")
res <- dbSendQuery(wrds, sqlQuery)
df_crsp_msf <- dbFetch(res, n = -1)
dbClearResult(res)

# remove duplicates
df_crsp_mse <- df_crsp_mse[!duplicated(df_crsp_mse),]
df_crsp_msf <- df_crsp_msf[!duplicated(df_crsp_msf),]

# remove nas in price
# drop special flags in price
df_crsp_msf <- df_crsp_msf[!is.na(df_crsp_msf$prc),]
weirdPrices <- c(-44, -55, -66, -77, -88, -99)
df_crsp_msf <- df_crsp_msf[!(df_crsp_msf$prc %in% weirdPrices),]

# only retain major exchanges (amex, nyse, nasdaq)
df_crsp_mse <- df_crsp_mse[df_crsp_mse$exchcd %in% c(1,2,3),]

# add month and year columns to facilitate merge
df_crsp_mse$month_year <- format(df_crsp_mse$date, "%Y-%m")
df_crsp_msf$month_year <- format(df_crsp_msf$date, "%Y-%m")

if( sum(complete.cases(df_crsp_mse)) == 0){
  cat("mse looks useless\n")  
}

# append event data wherever applicable
crsp_m <- merge(df_crsp_mse, df_crsp_msf, 
                by.x = c("permno", "permno", "month_year"),
                by.y = c("permno", "permno", "month_year"), 
                all.x = FALSE, all.y = TRUE)
```