Building a Return Series: The Monthly Compustat Construction

Data companion for Lecture 8’s asset-pricing application (Finance track)

How to use this document

This is the data-cleaning companion behind Lecture 8’s asset-pricing application: where the column of monthly returns the slides simply assume actually comes from, and the two mistakes (raw un-adjusted prices; an unscreened panel) that would quietly corrupt it.

Every construction step here reads from Compustat Security Monthly, which is licensed WRDS data this course cannot redistribute — so, unlike the economics track’s replication.qmd, nothing in this document runs end to end for you. Every code chunk that touches comp.secm is shown eval: false: read it as exact, runnable code, not pseudocode, but you will need your own WRDS credentials to execute it. Where this document states a number (a validation correlation, a screen’s effect on a value-weighted return, a delisting-return convention), that number is quoted from a ledger built by actually running this construction once, with the exact source kept in the private working notes. Only the closing sessionInfo() chunk executes when this document renders.

Two companion pieces pick up where this one stops. daily-returns-companion.qmd covers the harder, automated cleaning a daily Compustat pull needs (decimal-shift errors, an eight-filter survivor chain, a missing-shares-outstanding fallback) that a monthly panel mostly avoids — see the closing section below. The BAB companion (bab-companion.qmd) picks up the return series built here and turns it into test assets, factor exposures, and the Betting-Against-Beta application itself; that machinery used to live in this file and has moved out, because it is about pricing returns, not building them.

Building a genuine return

The examples discussed in class assume a column of returns. This section builds one from Compustat Security Monthly, since our WRDS access does not include CRSP.¹ but also because international series need it (there is no “european CRSP”).

Ghost returns

Differencing raw closing prices (prccm) directly, a 2-for-1 split shows up as a \(-50\%\) “return” that never happened and looks like an error to be capped. This is a red flag, proper adjustment (next) is the data cleaning answer.

Proper adjustments

The standard total return (dividend-reinvested, split-adjusted) from Compustat Security Monthly is

\[ \text{adj\_prc}_t = \text{prccm}_t \cdot \frac{\text{trfm}_t}{\text{ajexm}_t}, \qquad \text{ret}_t = \frac{\text{adj\_prc}_t}{\text{adj\_prc}_{t-1}} - 1. \]

ajexm (cumulative split factor) and trfm (cumulative total-return factor) are indices normalized so the most recent observation equals 1. Earlier values scale away from 1 going back in time, so a trfm of 30,000 is a long-lived, high-dividend stock, and an ajexm of \(10^{-7}\) is an extreme cumulative split history. Notice that only the consecutive-month ratio matters, since both cumulative levels cancel by construction. Dropping trfm from the formula gives a price-only return, the analogue of CRSP’s RETX.

The same idea carries over to daily data, only with more that can go wrong before the ratio is taken; the closing section below points at the companion that covers it.

One-time setup: your WRDS password in ~/.pgpass

The pull scripts read your WRDS username from the WRDS_USER environment variable and your password from a ~/.pgpass file, so nothing sensitive is typed into or stored in the code. Set both up once, from a terminal:

export WRDS_USER=yourwrdsid          # add to ~/.zshrc to make it permanent
echo 'wrds-pgdata.wharton.upenn.edu:9737:wrds:yourwrdsid:yourpassword' >> ~/.pgpass
chmod 600 ~/.pgpass                  # libpq refuses the file if it is more open

The ~/.pgpass line is host:port:database:user:password (fill in your own yourwrdsid and yourpassword). The chmod 600 is not optional: libpq silently ignores the file if it is group- or world-readable, and you’ll be left without a password. After this, RPostgres connects with no prompt.

The WRDS pull for this:

library(RPostgres)

wrds <- dbConnect(Postgres(),
                   host = "wrds-pgdata.wharton.upenn.edu", port = 9737,
                   dbname = "wrds", sslmode = "require",
                   user = Sys.getenv("WRDS_USER"))

secm <- dbGetQuery(wrds, "
  SELECT s.gvkey, s.iid, s.datadate, s.prccm, s.ajexm, s.trfm,
         s.cshom, s.curcdm, co.dlrsn, co.dldte, sec.secstat
  FROM   comp.secm AS s
  LEFT JOIN comp.company  AS co  ON s.gvkey = co.gvkey
  LEFT JOIN comp.security AS sec ON s.gvkey = sec.gvkey AND s.iid = sec.iid
  WHERE  s.iid = '01'
    AND  s.curcdm = 'USD'
    AND  s.datadate BETWEEN '2019-01-01' AND '2019-12-31'
")

Recall: validated joins (Lecture 1 / Tutorial 1)

The two LEFT JOINs above attach comp.company and comp.security to the price panel on gvkey (and iid) before anything downstream is built on the result — the same “declare what the join should do, then check it did that” discipline Tutorial 1 teaches under “Give variables meaning and validate a join” (tutorials/core/tutorial1). Here it matters twice over: comp.company is one row per gvkey, so that join cannot duplicate rows, but a careless join to comp.security on gvkey alone (dropping the iid match) would — and every return built downstream would silently inherit the duplication.

The complete, runnable version of this pull is the standalone pull script: the same query as the chunk above, plus the two lines that save the result to a CSV. That script, not the inline chunk, is what you actually run.

And the construction itself, in R:

library(data.table)

build_returns <- function(d) {
  setorder(d, gvkey, datadate)

  # Null broken factor/price values BEFORE any arithmetic, not after.
  d[prccm <= 0 | is.na(prccm) | ajexm <= 0 | is.na(ajexm) |
      trfm <= 0 | is.na(trfm), c("prccm", "ajexm", "trfm") := NA]

  d[, adj_prc := prccm * trfm / ajexm]
  d[, adj_prc_lag := shift(adj_prc), by = gvkey]
  d[, ret := adj_prc / adj_prc_lag - 1]
  d
}

secm <- fread("data/compustat_secm_2019_2019.csv")   # never committed
secm[, datadate := as.IDate(datadate)]
secm <- build_returns(secm)

Recall: this is now a panel (Lecture 4)

shift(adj_prc), by = gvkey is the within-unit lag Lecture 4 builds fixed-effects estimation on (lectures/core/lecture4): secm stops being a stack of independent security-month rows here and becomes a firm-month panel, indexed by gvkey and time. Everything from here on — the screens below, the delisting correction, any regression run on the result — inherits that panel structure, and a bug that ignores it (a shift() not grouped by gvkey, say) silently borrows one firm’s last price for another firm’s first return.

Two screens before any of this runs

The formula assumes one row per firm per month, in one currency. Two screens belong at the query, before any return is computed:

Primary issue only (iid = '01', or the more principled iid == priusa numeric comparison with ADRs excluded): a gvkey can carry several issues, and without this screen it is not a unique key.
Single currency (curcdm = 'USD'): keeps foreign-currency issues, whose market caps would otherwise sit in Hong Kong dollars or Korean won, out of a US sample.

Skipping either screen costs tens of percentage points on a value-weighted return; the exact numbers behind that statement are kept in the private working notes.

Delisting returns

A return series that only exists while a firm is listed has a one-directional problem: firms that delist for bad reasons (bankruptcy, being dropped from an exchange) vanish right before they would have posted a large negative return, biasing the average return upward. The fix, standard since Shumway (1997) and used by Chaieb, Langlois & Scaillet (2021, JFE) in their published international construction, is to impute a return at the delisting event instead of dropping it. The line comes from the paper’s Internet Appendix, not the main text, which is itself a small lesson in where data-cleaning conventions actually get written down:

“We follow Shumway (1997) and apply a \(-30\%\) delisting return when delisting is performance related (using the delisting reason dlrsni).”

dlrsn (dlrsni internationally) identifies why a security delisted, so the correction only fires on performance-related exits, not mergers or voluntary going-private. One schema wrinkle worth stating, because it will bite anyone who copies the obvious query: on the current WRDS install the US dlrsn (and its date dldte) live on the company header comp.company, one row per gvkey, not on comp.security — which exposes only the international dlrsni. That is why the pull above joins comp.company separately. Performance-related US codes are dlrsn 02 (bankruptcy) and 03 (liquidation); dldte tells you which month carries the imputed return.

The \(-30\%\) is not a round number chosen for convenience. Shumway (1997) tracked down over-the-counter prices for the NYSE/AMEX firms CRSP’s delisting file simply drops, and reports the result plainly:

“With 71 percent of the delisting returns accounted for, the average return is \(-30\) percent.”

— and, for researchers unwilling to replicate that legwork, an explicit recommendation:

“They can also be tested with returns of \(-0.3\) replacing the missing performance delisting returns in CRSP.”

“Performance related” here means CRSP delisting codes 500 and 520 to 584 (Shumway’s Table I). Nasdaq delistings are both more frequent and more costly, so Shumway and Warther (1999) do not simply reuse \(-30\%\) for that market — they derive a separate number and say so directly:

“We estimate that using a corrected return of \(-55\) percent for missing performance-related delisting returns corrects the bias.”

“This delisting bias is 4.7 times larger than the delisting bias in NYSE and AMEX data documented by Shumway (1997).”

Three papers, three explicit constants, each defended in these words rather than assumed — that is the discipline worth taking away even on a sample where the exact number might not transfer unchanged. The correction is an imputation, not data: state which value you used and show a sensitivity check.

Beyond monthly: the daily construction

Everything above is the monthly construction, and two screens plus one imputed return are close to the whole story at that frequency. Several of this course’s own applications — the levered beta estimator behind Betting-Against-Beta, in particular — need daily returns instead, and daily data reopens problems that monthly mostly closes: a firm’s share count going missing for months at a time rather than just one, adjustment factors sitting close enough to zero to blow up a single day’s ratio, and outright data errors (a price or share count off by a factor of ten or a hundred) that a \(\pm 20\%\) cap would hide rather than fix. daily-returns-companion.qmd is where that machinery is written up as a lesson: a fallback for the missing-shares problem, an automated decimal-shift detector validated against a vendored reference implementation, and the eight-filter survivor chain that turns a raw daily pull into a usable panel. Read it once this document’s construction — the one idea that both frequencies share — is solid.

Sources

Shumway, T. (1997). “The Delisting Bias in CRSP Data.” The Journal of Finance, 52(1), 327-340.
Shumway, T., & Warther, V. A. (1999). “The Delisting Bias in CRSP’s Nasdaq Data and Its Implications for the Size Effect.” The Journal of Finance, 54(6), 2361-2379.
Chaieb, I., Langlois, H., & Scaillet, O. (2021). “Factors and Risk Premia in Individual International Stock Returns.” Journal of Financial Economics, 141(2), 669-692, and its Internet Appendix.
Compustat Security Monthly / comp.secm, comp.company, comp.security (WRDS).
compustat_returns.qmd — the full hand-worked exercise (international construction, hand-curated error table), a deeper dive than this companion attempts.

sessionInfo()

R version 4.2.2 (2022-10-31)
Platform: x86_64-apple-darwin17.0 (64-bit)
Running under: macOS Big Sur ... 10.16

Matrix products: default
BLAS:   /Library/Frameworks/R.framework/Versions/4.2/Resources/lib/libRblas.0.dylib
LAPACK: /Library/Frameworks/R.framework/Versions/4.2/Resources/lib/libRlapack.dylib

locale:
[1] en_GB.UTF-8/en_GB.UTF-8/en_GB.UTF-8/C/en_GB.UTF-8/en_GB.UTF-8

attached base packages:
[1] stats     graphics  grDevices utils     datasets  methods   base     

loaded via a namespace (and not attached):
 [1] compiler_4.2.2  fastmap_1.1.1   cli_3.6.1       tools_4.2.2    
 [5] htmltools_0.5.5 yaml_2.3.7      rmarkdown_2.23  knitr_1.51     
 [9] jsonlite_1.8.7  xfun_0.60       digest_0.6.33   rlang_1.1.1    
[13] evaluate_0.21

Footnotes

CRSP (Center for Research in Security Prices) hands out a RET field: an already dividend-reinvested, already split-adjusted total return, and most published US asset-pricing work is built on it. This course’s WRDS access is Compustat-only, so we construct the return ourselves. Built this way, our monthly series reproduces Ken French’s aggregate market return at \(\rho = 0.9986\) over the full 1976–2010 window; separately, a parallel project built the daily analogue with the identical formula and validated it security-by-security against a CRSP extract (\(\rho = 0.9993\), firm-day).↩︎