Merge datasets  Merge Compustat and CRSP Data in Stata


Merging Compustat and CRSP data is a common task in accounting and finance research. Compustat provides fundamental financial data on companies, while CRSP contains rich market and returns data. Combining these datasets enables powerful analyses of the link between financial performance and stock returns.

This comprehensive guide will walk you through the key steps involved in a merging Compustat and CRSP. We’ll cover the essentials of each database, merging mechanics and considerations, and pros and cons of using pre-merged data.

Introduction to Compustat and CRSP

Let’s start with an overview of Compustat and CRSP.

Compustat  Compustat


Compustat, maintained by S&P Global, contains fundamental data on public companies worldwide. It includes full income statements, balance sheets, statements of cash flows, ratios, pricing, earnings estimates and more. Here are some of the key variables found in the Compustat database:

  • GVKEY – Global company identifier unique to each company
  • DATADATE – Date for the fundamental data item, usually end of fiscal period
  • FYEAR – Fiscal year
  • FQTR – Fiscal quarter
  • SALE – Total revenue
  • COGS – Cost of goods sold
  • XRD – R&D expense
  • XINT – Interest expense
  • NI – Net income
  • EPSPX – Earnings per share
  • TXDB – Deferred taxes
  • PPENT – Property, plant and equipment (net)
  • AT – Assets total
  • LT – Liabilities total
  • CEQQ – Common/ordinary equity
  • CSHPRQ – Common shares outstanding
  • PRCC_F – Share price at fiscal year end
  • CUSIP – Committee on Uniform Security Identification Procedures number
  • NAICS – North American Industry Classification System code
  • SIC – Standard Industrial Classification code

Compustat data is organized by company with one row per company-date observation. The GVKEY code uniquely identifies each company across time.

Compustat variables needed for merge Compustat variables needed for merging


For merging Compustat data with CRSP, we need:

  • GVKEY: Unique company identifier assigned by Compustat
  • CUSIP: 9-digit numeric CUSIP code with check digit
  • DATADATE: Date of fundamentals – end of fiscal period

Compustat, maintained by S&P Global, contains fundamental data on public companies worldwide. It includes full income statements, balance sheets, statements of cash flows, ratios, pricing, earnings estimates and more. Key variables include:

The CRSP US Stock Database, from the University of Chicago’s Center for Research in Security Prices, provides historical market data on common stocks. CRSP contains security-level data, with multiple rows per company for each trading day, one for each security. The PERMNO field uniquely identifies securities, while PERMCO groups securities into companies. Here are some of the key variables in the CRSP US stock database:

  • PERMNO – Unique permanent security identifier
  • PERMCO – Unique permanent company identifier
  • DATE – Trade date
  • RET – Holding period return
  • VOL – Share volume traded
  • SHROUT – Shares outstanding
  • PRCC_C – Closing price
  • CFACPR – Cumulative factor to adjust price
  • CFACSHR – Cumulative factor to adjust shares
  • BIDLO – Bid price low
  • ASKHI – Ask price high
  • OPENPRC – Opening price
  • NUMTRD – Number of trades
  • SHRCD – Share code
  • EXCHCD – Exchange code
  • SICCD – Standard industrial classification code
  • NCUSIP – Historical CUSIP
  • CUSIP – Current CUSIP
  • TICKER – Ticker symbol
  • COMNAM – Company name

Some key uses of these variables include calculating returns, trading volume, liquidity measures, identifying missing returns, merging on historical CUSIPs, analyzing bid-ask spreads, and linking CRSP to Compustat data.

.

Compustat variables needed for merge  CRSP variables needed for merging

  • PERMNO: Unique permanent security identifier
  • PERMCO: Unique permanent company identifier
  • CUSIP: 8-character CUSIP code without check digit
  • DATE: Trading day of stock returns.

Steps in merging compustat with crsp Steps for Merging Compustat and CRSP


Merging CRSP with Compustat can be done in two ways: using the CCM database or using the CUSIP and DATADATE variables. The CCM-based merging requires access to the CCM database, which may not be available to your university or college. In the following section, we will discuss merging the two databases using the second approach.

1. Extract relevant variables

Pull the variables you will need for analysis from both datasets.

For Compustat, this typically includes GVKEY, CUSIP, DATADATE and fields for your research like assets, liabilities, earnings, cash flows, etc.

For CRSP, extract CUSIP, PERMNO, PERMCO, DATE, RET and any other return variables.

2. Standardize date formats

Ensure that the date fields from both sources use the same format. In Stata or SAS, it is recommended to convert the data to the software’s format and then extract date frequency at which both the dataset must be merged. For example, if you are merging annual data from Compustat with monthly data from CRSP, first extract the year variable from the date variables in the CRSP dataset. Match the variable names in both datasets. So, if the year variable is named fyear in the Compustat dataset, rename the year variable as fyear in the CRSP dataset as well. See data excerpts from the two datasets and note how we extract the year from the CRSP dataset.

 * Extract fyear from date variable in CRSP dataset

gen fyear = year(date)

Data excerpt from Compustat database
gvkey datadate fyear cusip at
001000 31dec1963 1963 000032102 .
001000 31dec1964 1964 000032102 1.416
001000 31dec1965 1965 000032102 2.310
001000 31dec1966 1966 000032102 2.430
001000 31dec1967 1967 000032102 2.456
001000 31dec1968 1968 000032102 5.922
001000 31dec1969 1969 000032102 28.712
001000 31dec1970 1970 000032102 33.450
001000 31dec1971 1971 000032102 29.330
001000 31dec1972 1972 000032102 19.907
001000 31dec1973 1973 000032102 21.771

3. Prepare CUSIP

Compustat’s CUSIP has 9 digits, having three parts.

1. Cusip issuer number – 6 digits (i.e. the company)
2. Cusip issue number – 2 digits (the particular stock or bond offering)
3. Cusip check digit – 1 digit

Therefore, for merging purpose, we just need 6 digits. The Stata function to extract six digits from the CUSIP is substr().

 * Extract 6 digits from CUSIP

gen cusip_6 = substr(cusip, 1, 6)

4. Merge datasets using CUSIP and date

With date and CUSIP formatted consistently, merge CRSP and Compustat using an inner join on both CUSIP and date. This attaches market data from CRSP to each company-date row from Compustat. The inner join combines records from two tables whenever there are matching values in a field common to both tables. In Stata’s parlance, inner join refers to :

keep if _merge==3

Since the CRSP data will have multiple records for the same CUSIP and date variable, more often than not, we shall use the m:1 merge.

* Open the Compustat data

use compustat, clear

.

* Now merge on CUSIP and TIME

merge 1:m CUSIP timevar using "CRSP"

.

*Keep only matching observations

keep if _merge == 3

5. Check for non-merges

Some Compustat observations may not find a match in CRSP on the same date due to non-trading days. Re-merge using date +/- 1 day to maximize merge rate.

6. Finalize dataset

Keep only the variables you need for analysis. With a single company per row but multiple securities, you may aggregate CRSP data like taking the mean return across all matched securities.

Proper data preparation is crucial prior to merging. Bad merges lead to inaccurate or misleading results. But carefully following these steps will to a properly merged dataset.

Merge Considerations and Choices

Merging Compustat and CRSP yourself offers flexibility compared to pre-merged data products. Here are some key considerations:

  • 1:M merges: A Compustat company may have multiple securities in CRSP. Analyze multi-security firms carefully.
  • Delisting returns: Merge on PERMCO not PERMNO to capture delisting returns spread across securities.
  • Time shifts: Use date +/- 1 merges to maximize observations. Choose approach based on research goals.
  • Pre- vs. post-merging aggregations: Decide whether to aggregate before or after merging based on needs.
  • Alternative identifiers: CUSIP standardization is common, but can also use PERMCO-GVKEY or WRDSEID maps.
  • CCM vs. self-merging: Pre-merged CCM data handles historical CUSIPs better, but self-merging allows more control.

Think through these options to determine the optimal merging approach for your research.

Can I use Pre-Merged CCM Data

Merging Compustat and CRSP yourself provides the most flexibility. But researchers at subscribing institutions can also access pre-merged data from WRDS in the form of the CRSP/Compustat Merged (CCM) Database.

CCM has merged CRSP and Compustat historically, simplifying many tricky aspects. Benefits include:

  • Seamless handling of historical CUSIP changes
  • Pre-built PERMCO-GVKEY links
  • Treatment of non-trading days
  • Delisting return calculations

However, limitations of CCM include:

  • Less control over how merges handled
  • Fixed variable list – no customizations
  • More difficult to merge additional data sources

CCM offers convenience for common merger of datasets.