--- title: "Exercise template for 'Linking databases'" author: Your name format: html bibliography: book.bib --- ```{r} #| include: false library(dplyr) library(DBI) library(farr) library(ggplot2) library(dbplyr) # window_order() library(lubridate) # floor_date() library(kableExtra) if (knitr::is_latex_output()) { knitr::knit_meta_add(list(rmarkdown::latex_dependency("float"))) } ``` ## The CRSP database ```{r} #| include: false db <- dbConnect(RPostgres::Postgres(), bigint = "integer") dsf <- tbl(db, Id(table = "dsf", schema = "crsp")) dsi <- tbl(db, Id(table = "dsi", schema = "crsp")) msf <- tbl(db, Id(table = "msf", schema = "crsp")) msi <- tbl(db, Id(table = "msi", schema = "crsp")) ccmxpf_lnkhist <- tbl(db, Id(table = "ccmxpf_lnkhist", schema = "crsp")) stocknames <- tbl(db, Id(table = "stocknames", schema = "crsp")) company <- tbl(db, Id(table = "company", schema = "comp")) funda <- tbl(db, Id(table = "funda", schema = "comp")) ``` ```{r} #| include: false ccm_link <- ccmxpf_lnkhist |> filter(linktype %in% c("LC", "LU", "LS"), linkprim %in% c("C", "P")) |> mutate(linkenddt = coalesce(linkenddt, max(linkenddt, na.rm = TRUE))) ``` ### Exercises 1. Looking at `crsp.dsf` and `crsp.msf`, we see that `prc` can be negative. Do negative stock prices make sense economically? What do negative stock prices on CRSP mean?^[CRSP documentation can be found at .] What would be an alternative approach to encode this information? (Write code to recast the data using this approach.) Why do you think that CRSP chose the approach it uses? 2. How do `ret` and `retx` differ? Which variable are you more likely to use in research? 3. Looking at the `date` variable on `crsp.msf`, is it always the last day of the month? If not, why not? 4. Suggest the "natural" primary key for `crsp.dsf` and `crsp.msf`. Check that this is a valid primary key for `crsp.msf`. 5. In the code below, we are using `collect()` followed by `mutate(month = floor_date(date, "month"))` to calculate `month`. What changes occur in terms of where the processing happens if we replace these two lines with `mutate(month = as.Date(floor_date(date, "month"))) |> collect()`? Do we get different results? What effect does `as.Date()` have? ```{r} plot_data <- dsf |> select(date) |> filter(between(date, "2017-12-31", "2022-12-31")) |> collect() |> mutate(month = floor_date(date, "month")) ``` 6. What is being depicted in Figures [-@fig-mys-plot1] and [-@fig-mys-plot2]? What are the sources of variation across months in @fig-mys-plot1? Can you guess what is the main driver of variation in @fig-mys-plot2? Create an additional plot to visualize the source of variation in @fig-mys-plot1 not depicted in @fig-mys-plot2. ```{r} #| label: fig-mys-plot1 #| fig-pos: H #| fig-height: 3 #| fig-cap: Number of observations by month (#1) #| echo: false plot_data |> count(month) |> ggplot(aes(x = month, y = n)) + geom_bar(stat = "identity") + scale_x_date(date_breaks = "2 months", expand = expansion()) + theme(axis.text.x = element_text(angle = 90)) ``` ```{r} #| label: fig-mys-plot2 #| fig-pos: H #| fig-height: 3 #| fig-cap: Number of observations by month (#2) #| echo: false plot_data |> distinct() |> count(month) |> ggplot(aes(x = month, y = n)) + geom_bar(stat = "identity") + scale_x_date(date_breaks = "2 months", expand = expansion()) + theme(axis.text.x = element_text(angle = 90)) ``` 7. What is the primary key for `crsp.dsi` and `crsp.msi`? Verify that it is a valid key for both tables. 8. Using the `dplyr` verb `anti_join()`, determine if there are any dates on `crsp.dsf` that do not appear on `crsp.dsi` or vice versa. Do the same for `crsp.msi` and `crsp.msf`. ## All about CUSIPs ### Exercises 1. Is there any evidence of "reuse" of CUSIPs on `crsp.stocknames`? In other words, are there any `ncusip` or `cusip` values associated with more than one `permno`? 2. The CRSP table `crsp.stocknames` includes two CUSIP-related fields, `cusip` and `ncusip`. What are the differences between the two fields? What does it mean when `ncusip` is missing, but `cusip` is present? 3. Like CUSIPs, PERMNOs are security-level identifiers. Can a PERMNO be associated with more than one CUSIP at a given point in time? Can a PERMNO be associated with more than one CUSIP over time? 4. Looking at entries on `crsp.stocknames` where `ticker` is `DELL`, we see two different `permno` values. What explains this? ```{r} stocknames |> filter(ticker == "DELL") |> select(permno, cusip, ncusip, comnam, siccd, namedt, nameenddt) |> collect() ``` 5. Looking at `permno` of `11081` (Dell), we see two different CUSIP values. What change appears to have caused the change in CUSIP for what CRSP regards as the same security? ```{r} #| include: false funda_mod <- funda |> filter(indfmt == "INDL", datafmt == "STD", consol == "C", popsrc == "D") |> mutate(mkt_cap = prcc_f * csho) |> select(gvkey, datadate, cusip, at, mkt_cap) stocknames_plus <- stocknames |> mutate(ncusip = coalesce(ncusip, cusip)) funda_cusip_link <- funda_mod |> mutate(ncusip = str_sub(cusip, 1L, 8L)) |> inner_join(stocknames_plus, join_by(ncusip, between(datadate, namedt, nameenddt))) |> select(gvkey, datadate, permno, permco) funda_ccm_link <- funda_mod |> select(gvkey, datadate) |> inner_join(ccm_link, join_by(gvkey, between(datadate, linkdt, linkenddt))) |> select(gvkey, datadate, lpermno, lpermco) |> rename(permno = lpermno, permco = lpermco) funda_link_combined <- funda_mod |> select(-cusip) |> left_join(funda_ccm_link, by = join_by(gvkey, datadate)) |> left_join(funda_cusip_link, by = join_by(gvkey, datadate), suffix = c("_ccm", "_cusip")) |> mutate(same_permno = permno_ccm == permno_cusip, same_permco = permco_ccm == permco_cusip, has_permno_ccm = !is.na(permno_ccm), has_permno_cusip = !is.na(permno_cusip)) |> filter(has_permno_ccm | has_permno_cusip) |> collect() ``` 6. Choose a row from `funda_link_combined` where `same_permco` is `FALSE`. Can you discern from the underlying tables what issue is causing the difference and which match (if any) is valid? (*Hint:* Do rows where `gvkey %in% c("065228", "136265")` meet this condition? What appears to be the issue for these GVKEYs?) Can you conclude that the CCM-based match is the preferred one in each case? 7. Choose a row from `funda_link_combined` where `has_permno_cusip` is `TRUE` and `has_permno_ccm` is `FALSE`. Can you discern from the underlying tables whether the CUSIP-based match is valid? (*Hint:* Do rows where `gvkey %in% c("033728", "346027")` meet this condition? What appears to be the issue for these GVKEYs?) 8. Given the results shown in Tables [-@tbl-ccm-cusip-comp] and [-@tbl-ccm-cusip-cov] and your answer to the previous two questions, can you conclude that the CCM-based match is preferred to the CUSIP-based match in each case? ```{r} #| label: tbl-ccm-cusip-comp #| tbl-pos: H #| tbl-cap: Comparison of CCM- and CUSIP-based links #| echo: false funda_link_combined |> count(same_permno, same_permco) |> kbl(booktabs = TRUE) ``` ```{r} #| label: tbl-ccm-cusip-cov #| tbl-pos: H #| tbl-cap: Differences in coverage of CCM- and CUSIP-based link tables #| echo: false funda_link_combined |> count(has_permno_ccm, has_permno_cusip) |> kbl(booktabs = TRUE) ``` 9. General Motors Corporation declared bankruptcy in June 2009. Does the successor firm General Motors *Company* have the same GVKEY as General Motors Corporation? The same PERMNO? Do the answers make sense given the underlying economic and accounting realities? (*Hint:* You can find the relevant PERMNOs on `stocknames` using the ticker "GM", and their GVKEYs using `ccm_link`.) ```{r} #| include: false dbDisconnect(db) ```