Skip to main content

Accounting Research: An Introductory Course

9 Linking databases

Almost any research project in accounting will involve merging data from multiple sources. For example, to evaluate the market reaction to earnings announcements, we might start with data on comp.fundq from Compustat (gvkey and datadate to identify firm-quarters plus associated announcement dates in rdq) and then look to merge with daily stock return data from the Center for Research in Security Prices (CRSP, pronounced as “crisp”) on crsp.dsf, which uses permno and date to identify firms and their daily returns, ret. But this raises the question as to which permno (if any) matches a given gvkey. Alternatively, given security price information from CRSP, we might ask what it the most recent financial statement information for that secruity. This raises the question as to which gvkey (if any) matches the given permno.

9.1 Firm identifiers

The idea behind firm identifiers is that they uniquely identify a firm for a particular purpose.67 While Compustat uses GVKEYs, CRSP uses PERMNOs, the SEC uses CIKs, stock exchanges use tickers, and there are also CUSIPs. Of course, identifiers apply not only to firms, but also people.68 Nonetheless, most of this chapter will focus on firm identifiers, in part because of the importance of firms as units of observation in finance and accounting research, but also because identifying firms is much harder than identifying people. While not specific to the platform we describe in this book, the issue of identifiers is one that seems less perplexing and better-handled when a relational database provides the backbone of your data store as it does here.

Data provider Firm identifiers Notes
Compustat (comp) gvkey
CRSP (crsp) permno, permco permno is a security identifier
IBES (ibes) ticker ticker is not necessarily the ticker assigned to the firm by the exchange on which it trades
SEC EDGAR CIK
Audit Analytics (audit) company_fkey company_fkey is the same as CIK
Various CUSIP CUSIP is a security identifier

9.1.1 Firm identifiers: A quiz

Our sense is that firm identifiers is one of those topics that seem easy, but is actually fairly tricky. Here is a quick quiz to test your knowledge of firm identifiers.69

  • 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?
  • Can a CUSIP map to more than one PERMNO? To more than one GVKEY?
  • Can a PERMNO map to more than one CUSIP?
  • Can a GVKEY map to more than one PERMCO?
  • Can a PERMCO map to different CIKs?
  • If you have two data sets, \(X\) and \(Y\) and CUSIP is a “firm” identifier on each, can you simply merge using CUSIPs?
  • When would a “firm” change CUSIPs?
  • When would a “firm” change CIKs?
  • If the firm identifier on IBES is ticker, should I merge with CRSP using ticker from crsp.stocknames?

Maybe you know the answers to some questions, but not all. If so, read on; this chapter aims to provide answers to these questions and more.

9.2 Linking CRSP and Compustat

The CRSP/Compustat Merged (CCM) database provides the standard link between CRSP data and Compustat’s fundamental data. The CCM provides three tables that you will see in common use:

  • crsp.ccmxpf_lnkhist
  • crsp.ccmxpf_lnkused
  • crsp.ccmxpf_linktable

The reality is that the only table we need to worry about is crsp.ccmxpf_lnkhist, as the other two tables can be (and likely are) constructed from it (see here for details).70

library(dbplyr)
library(dplyr, warn.conflicts = FALSE)
library(DBI)
library(tidyr)
pg <- dbConnect(RPostgres::Postgres(), 
                bigint = "integer", 
                check_interrupts = TRUE)
ccmxpf_lnkhist <- tbl(pg, sql("SELECT * FROM crsp.ccmxpf_lnkhist")) 
ccmxpf_lnkhist
## # Source:   SQL [?? x 8]
## # Database: postgres  [iangow@/tmp:5432/iangow]
##    gvkey  linkprim liid  linktype lpermno lpermco linkdt     linkenddt 
##    <chr>  <chr>    <chr> <chr>      <int>   <int> <date>     <date>    
##  1 001000 C        00X   NU            NA      NA 1961-01-01 1970-09-29
##  2 001000 P        01    NU            NA      NA 1970-09-30 1970-11-12
##  3 001000 P        01    LU         25881   23369 1970-11-13 1978-06-30
##  4 001001 C        00X   NU            NA      NA 1978-01-01 1983-09-19
##  5 001001 P        01    LU         10015    6398 1983-09-20 1986-07-31
##  6 001002 C        00X   NR            NA      NA 1960-01-01 1970-09-29
##  7 001002 C        01    NR            NA      NA 1970-09-30 1972-12-13
##  8 001002 C        01    NR            NA      NA 1973-06-06 1973-08-31
##  9 001002 C        01    LC         10023   22159 1972-12-14 1973-06-05
## 10 001003 C        00X   NU            NA      NA 1980-01-01 1983-12-06
## # … with more rows

The basic idea of the table above is that given a gvkey and a date, one can match that gvkey-date combination to a PERMNO (here called lpermno) by merging on gvkey where the date is between linkdt and linkenddt. One thing you will see is that there are cases where lpermno is NA, so “matching” these rows will result in non-matches, which is of no real value. The only value might be in determining whether the non-match has linktype of NR, which means that lack of a link has been “confirmed by research” (presumably by CRSP), or of NU, which means the link is “not yet confirmed” by research.

ccmxpf_lnkhist %>%
  filter(is.na(lpermno)) %>%
  count(linktype)
## # Source:   SQL [3 x 2]
## # Database: postgres  [iangow@/tmp:5432/iangow]
##   linktype     n
##   <chr>    <int>
## 1 NU       34110
## 2 NR       40533
## 3 NP           4

In practice, we would likely ignore all matches with linktype %in% c("NU", "NR") or (equivalently) is.na(lpermno). Let’s look at the remaining linktype values.

ccm_link <-
  ccmxpf_lnkhist %>%
  filter(!is.na(lpermno)) 

ccm_link %>%
  count(linktype) %>%
  arrange(desc(n))
## # Source:     SQL [6 x 2]
## # Database:   postgres  [iangow@/tmp:5432/iangow]
## # Ordered by: desc(n)
##   linktype     n
##   <chr>    <int>
## 1 LC       16696
## 2 LU       16017
## 3 LS        4601
## 4 LX        1141
## 5 LN         186
## 6 LD         118

The cases where linktype is LD represent cases where two GVKEYs map to a single PERMNO at the same time and, according to WRDS, “this link should not be used.” Here is one example:

ccm_link %>% filter(lpermno == 23536)
## # Source:   SQL [3 x 8]
## # Database: postgres  [iangow@/tmp:5432/iangow]
##   gvkey  linkprim liid  linktype lpermno lpermco linkdt     linkenddt 
##   <chr>  <chr>    <chr> <chr>      <int>   <int> <date>     <date>    
## 1 011550 P        01    LC         23536   21931 1962-01-31 NA        
## 2 013353 P        01    LD         23536   21931 1962-01-31 1986-12-31
## 3 013353 C        99X   LD         23536   21931 1987-01-01 2020-12-31

Here we’ll take the WRDS’s advice and omit these.

The cases where linktype is LX represent cases where the security referred to on Compustat is one that trades on a foreign exchange and CRSP is merely “helpfully” linking to a different security that is found on CRSP. Here is one example:

ccm_link %>% filter(gvkey == "001186")
## # Source:   SQL [2 x 8]
## # Database: postgres  [iangow@/tmp:5432/iangow]
##   gvkey  linkprim liid  linktype lpermno lpermco linkdt     linkenddt
##   <chr>  <chr>    <chr> <chr>      <int>   <int> <date>     <date>   
## 1 001186 P        01    LC         78223   26174 1982-11-01 NA       
## 2 001186 N        01C   LX         78223   26174 1982-11-01 NA

These matches are duplicates and we don’t want them.

The remaining category for discussion is where linktype is LN. These are cases where a link exists, but Compustat does not have price data to allow CRSP to check the quality of the link. This is a case where researcher discretion may be used to include these, but most researchers appear to exclude these cases and we will do likewise. Given the above, we are only including cases where linktype is in LC (valid, researched link), LU (unresearched link), or LS (link valid for this lpermno only).

ccm_link <-
  ccmxpf_lnkhist %>%
  filter(linktype %in% c("LC", "LU", "LS")) 

ccm_link %>%
  count(linkprim) %>%
  arrange(desc(n))
## # Source:     SQL [4 x 2]
## # Database:   postgres  [iangow@/tmp:5432/iangow]
## # Ordered by: desc(n)
##   linkprim     n
##   <chr>    <int>
## 1 P        28918
## 2 C         7870
## 3 J          428
## 4 N           98

Now, let’s consider, linkprim. WRDS explains as follows:

linkprim clarifies the link’s relationship to Compustat’s marked primary security within the related range. “P” indicates a primary link marker, as identified by Compustat in monthly security data. “C” indicates a primary link marker, as identified by CRSP to resolve ranges of overlapping or missing primary markers from Compustat in order to produce one primary security throughout the company history. “J” indicates a joiner secondary issue of a company, identified by Compustat in monthly security data.

And cases where linkprim equals N are duplicated links due to the existence of Canadian securities for a US-traded firm; so we ignore these.

A natural question is whether, for any given GVKEY-date, is there only one PERMNO that is matched with linkprim IN ('P', 'C')?

ccm_link <-
  ccmxpf_lnkhist %>%
  filter(linktype %in% c("LC", "LU", "LS"),
         linkprim %in% c("C", "P")) 

# Look for overlapping date ranges    
ccm_link %>%
  group_by(gvkey) %>%
  window_order(linkdt) %>%
  mutate(lead_linkdt = lead(linkdt),
         lag_linkenddt = lag(linkenddt)) %>%
  filter(linkenddt >= lead_linkdt | lag_linkenddt >= linkdt) %>%
  ungroup() %>%
  count()
## # Source:     SQL [1 x 1]
## # Database:   postgres  [iangow@/tmp:5432/iangow]
## # Ordered by: linkdt
##       n
##   <int>
## 1     0

So there are no cases of overlapping dates, which means that only one lpermno is linked to for a given date. Our sense is that the last iteration of ccm_link above is more or less the standard approach used by researchers in practice. You may occasionally see code that filters on usedflag==1, which is a variable found on crsp.ccmxpf_lnkused. But, as can be seen here, using this table and filter yields exactly the same result as ccm_link above.

Note that the vast majority of GVKEYs map to just one PERMNO even without regard to time.

ccm_link %>%    
  select(gvkey, lpermno) %>%
  distinct() %>%
  group_by(gvkey) %>%
  mutate(num_permnos = n()) %>%
  ungroup() %>%
  count(num_permnos) %>%
  arrange(num_permnos)
## # Source:     SQL [5 x 2]
## # Database:   postgres  [iangow@/tmp:5432/iangow]
## # Ordered by: num_permnos
##   num_permnos     n
##         <int> <int>
## 1           1 31468
## 2           2  2018
## 3           3   219
## 4           4    12
## 5           5     5

The case with 5 PERMNOs is, if we recall correctly, a total mess with tracking stock, spin-offs, etc. But one observation doesn’t matter much.71

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)))

9.3 All about CUSIPs

According to CUSIP Global Services, “CUSIP identifiers are the universally accepted standard for classifying financial instruments across institutions and exchanges worldwide.”

“Derived from the Committee on Uniform Security Identification Procedures, CUSIPs are 9-character identifiers that capture an issue’s important differentiating characteristics for issuers and their financial instruments in the U.S. and Canada.”

CUSIP Global Services uses the CUSIP of Amazon.com’s common stock, 023135106, as an example of the components of a 9-character CUSIP. The first six characters, 023135 represent the issuer, which is Amazon.com, a company, in this case, but could be a municipality or a government agency. The next two characters (10) indicate the type of instrument (e.g., debt or equity), but also uniquely identifies the issue among the issuer’s securities. The final character (6) is a check digit created by a mathematical formula. This last character will indicate any corruption of the preceding 8 characters. Note that the characters need not be digits. For example, the Class C Common Stock of Dell Technologies Inc. has a CUSIP of 24703L202, which contains the letter L.73

While a full CUSIP always comprises nine characters, many data services abbreviate the CUSIP by omitting the check digit (to create an “eight-digit” CUSIP) or both the check digit and the issue identifier (to create a “six-digit” CUSIP). For example, the CRSP table crsp.stocknames uses eight-digit CUSIPs.

9.4 Exercises

  1. 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?
stocknames <- tbl(pg, sql("SELECT * FROM crsp.stocknames"))

  1. 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? Can a CUSIP be associated with more than one PERMNO over time?

  2. Looking at entries on crsp.stocknames where ticker is DELL, we see two different permno values. What explains this?

stocknames %>%
  filter(ticker == "DELL") %>%
  select(permno, namedt, nameenddt)
## # Source:   SQL [4 x 3]
## # Database: postgres  [iangow@/tmp:5432/iangow]
##   permno namedt     nameenddt 
##    <int> <date>     <date>    
## 1  11081 1988-06-22 2003-07-21
## 2  11081 2003-07-22 2013-10-29
## 3  18267 2018-12-28 2020-03-22
## 4  18267 2020-03-23 2022-12-30
  1. Looking at permno of 11081 (Dell), we see two different CUSIP values. What change appear to caused the change in CUSIP for what CRSP regards as the same security?

9.5 Linking Compustat with Audit Analytics

The SEC EDGAR databases uses CIKs to identify firms. Other databases, often derived from SEC EDGAR data, also use CIKs as firm identifiers. One such database is Audit Analytics (audit). We use Audit Analytics to illustrate linking of other databases using CIK links and focus on the table containing data on restatements (audit.auditnonreli) to do so. Because accounting restatements most closely relate to financial statements, we focus here on linking Compustat to Audit Analytics.

For the purposes of this chapter, we are not concerned with the detailed contents of audit.auditnonreli, merely with linking Compustat to that table. The three fields from audit.auditnonreli that we will use here are res_notif_key, company_fkey, and file_date_num. The first field is a primary key for the audit.auditnonreli table, the second field is what Audit Analytics calls CIKs, and the last field is date on which the SEC filing related to the restatement was filed. This will be our “test date” for linking GVKEYs to CIKs.

auditnonreli <- tbl(pg, sql("SELECT * FROM audit.auditnonreli"))

aa_lookup <-
  auditnonreli %>% 
  select(res_notif_key, company_fkey, file_date_num) %>%
  rename(cik = company_fkey) %>%
  collect()

As our source linking GVKEYs to CIKs, we will use the table crsp.comphist, which is created by CRSP from historical editions of the Compustat database.

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

gvkey_cik_link <-
  comphist %>% 
  filter(!is.na(hcik)) %>%
  group_by(gvkey) %>%
  window_order(hchgdt) %>%
  # Some fancy code to deal with tricky cases (see exercises)
  mutate(new_cik = as.integer(row_number()==1L | hcik != lag(hcik)),
         cik_id = cumsum(new_cik)) %>%
  group_by(gvkey, hcik, cik_id) %>%
  # SQL kills missing hchgenddt values, so this enables me 
  # to restore these
  summarize(missing_hchgenddt = any(is.na(hchgenddt), na.rm = TRUE),
            hchgdt = min(hchgdt, na.rm = TRUE),
            hchgenddt = max(hchgenddt, na.rm = TRUE),
            .groups = "drop") %>%
  mutate(hchgenddt = if_else(missing_hchgenddt, NA, hchgenddt)) %>%
  select(-missing_hchgenddt, -cik_id) %>%
  rename(cik = hcik) %>%
  arrange(gvkey, hchgdt) %>%
  collect(0)
gvkey_cik_link %>%
  select(gvkey, cik) %>%
  distinct() %>%
  group_by(gvkey) %>%
  summarize(num_ciks = n()) %>%
  ungroup() %>%
  count(num_ciks)
## # A tibble: 3 × 2
##   num_ciks     n
##      <int> <int>
## 1        1 35803
## 2        2  1280
## 3        3    33
gvkey_aa_link <-
  aa_lookup %>%
  inner_join(gvkey_cik_link, by = "cik") %>%
  filter(file_date_num >= hchgdt | hchgdt == min(hchgdt),
         file_date_num <= hchgenddt | is.na(hchgenddt)) %>%
  select(res_notif_key, gvkey) %>%
  right_join(aa_lookup, by = "res_notif_key")
## Warning in inner_join(., gvkey_cik_link, by = "cik"): Detected an unexpected many-to-many relationship between `x` and `y`.
## ℹ Row 72 of `x` matches multiple rows in `y`.
## ℹ Row 22747 of `y` matches multiple rows in `x`.
## ℹ If a many-to-many relationship is expected, set `relationship = "many-to-many"` to silence this warning.
gvkey_aa_link %>% 
  count(has_gvkey = !is.na(gvkey), 
        year = lubridate::year(file_date_num)) %>%
  pivot_wider(names_from = has_gvkey, values_from = n,
              names_glue = "has_gvkey_{has_gvkey}") %>%
  mutate(total = has_gvkey_TRUE + has_gvkey_FALSE,
         prop_w_gvkey = has_gvkey_TRUE/total) %>%
  rename(num_w_gvkey = has_gvkey_TRUE) %>%
  select(-has_gvkey_FALSE)
## # A tibble: 29 × 4
##     year num_w_gvkey total prop_w_gvkey
##    <dbl>       <int> <int>        <dbl>
##  1  1996          21    24        0.875
##  2  1997          62    64        0.969
##  3  1998          79    88        0.898
##  4  1999         127   137        0.927
##  5  2000         444   551        0.806
##  6  2001         540   642        0.841
##  7  2002         574   739        0.777
##  8  2003         719   876        0.821
##  9  2004         857   995        0.861
## 10  2005        1413  1683        0.840
## # … with 19 more rows
auditnonreli %>%
  group_by(res_notif_key) %>%
  summarize(num_rows = n()) %>%
  ungroup() %>%
  filter(num_rows != 1) %>%
  collect() %>%
  nrow() == 0
## [1] TRUE
auditnonreli %>%
  filter(is.na(res_notif_key)) %>%
  collect() %>%
  nrow() == 0
## [1] TRUE