WRDS, CRSP, and Compustat

Note

You are reading the work-in-progress edition of Tidy Finance with Python. Code chunks and text might change over the next couple of months. We are always looking for feedback via contact@tidy-finance.org. Meanwhile, you can find the complete R version here.

This chapter shows how to connect to Wharton Research Data Services (WRDS), a popular provider of financial and economic data for research applications. We use this connection to download the most commonly used data for stock and firm characteristics, CRSP and Compustat. Unfortunately, this data is not freely available, but most students and researchers typically have access to WRDS through their university libraries. Assuming that you have access to WRDS, we show you how to prepare and merge the databases and store them in the sqlite-database introduced in the previous chapter. We conclude this chapter by providing some tips for working with the WRDS database.

First, we load the packages that we use throughout this chapter. Later on, we load more packages in the sections where we need them.

import pandas as pd
import numpy as np
import sqlite3

from pandas.tseries.offsets import DateOffset

Accessing WRDS

WRDS is the most widely used source for asset and firm-specific financial data used in academic settings. WRDS is a data platform that provides data validation, flexible delivery options, and access to many different data sources. The data at WRDS is also organized in an SQL database, although they use the PostgreSQL engine. This database engine is just as easy to handle with Python as SQL. We use the sqlalchemy package to establish a connection to the WRDS database because it already contains a suitable driver.¹

from sqlalchemy import create_engine

To establish a connection, you use the function create_engine() with a connection string. Note that you need to replace the user and password arguments with your own credentials. We defined system variables for the purpose of this book because we obviously do not want (and are not allowed) to share our credentials with the rest of the world (these system variables are stored in the Python environment and loaded with the os.environ() function).

Additionally, you have to use multi-factor (i.e., two-factor) authentication since May 2023 when establishing a remote connection to WRDS. You have two choices to provide the additional identification. First, if you have Duo Push enabled for your WRDS account, you will receive a push notification on your mobile phone when trying to establish a connection with the code below. Upon accepting the notification, you can continue your work. Second, you can log in to a WRDS website that requires multi-factor authentication with your username and the same IP address. Once you have successfully identified yourself on the website, your username-IP combination will be remembered for 30 days, and you can comfortably use the remote connection below.

import os 
connection_string = ("postgresql+psycopg2://" + 
                      os.environ["USER"] + ":" + 
                      os.environ["PASSWORD"] +
                      "@wrds-pgdata.wharton.upenn.edu:9737/wrds")
wrds = create_engine(connection_string, pool_pre_ping=True)

The remote connection to WRDS is very useful. Yet, the database itself contains many different tables. You can check the WRDS homepage to identify the table’s name you are looking for (if you go beyond our exposition).

Downloading and Preparing CRSP

The Center for Research in Security Prices (CRSP) provides the most widely used data for US stocks. We use the wrds engine object that we just created to first access monthly CRSP return data. Actually, we need three tables to get the desired data: (i) the CRSP monthly security file msf, (ii) the identifying information msenames, and (iii) the delisting information msedelist.

We use the three remote tables to fetch the data we want to put into our local database. Just as above, the idea is that we let the WRDS database do all the work and just download the data that we actually need. We apply common filters and data selection criteria to narrow down our data of interest: (i) we keep only data in the time windows of interest, (ii) we keep only US-listed stocks as identified via share codes shrcd 10 and 11, and (iii) we keep only months within permno-specific start dates namedt and end dates nameendt. In addition, we add delisting codes and returns. You can read up in the great textbook of Bali, Engle, and Murray (2016) for an extensive discussion on the filters we apply in the code below.

crsp_monthly_query = (
  """SELECT msf.permno, msf.date, 
            date_trunc('month', msf.date)::date as month,
            msf.ret, msf.shrout, msf.altprc, 
            msenames.exchcd, msenames.siccd,
            msedelist.dlret, msedelist.dlstcd
        FROM crsp.msf AS msf
        LEFT JOIN crsp.msenames as msenames
               ON msf.permno = msenames.permno AND 
                  msenames.namedt <= msf.date AND
                  msf.date <= msenames.nameendt
        LEFT JOIN crsp.msedelist as msedelist
               ON msf.permno = msedelist.permno AND
                  date_trunc('month', msf.date)::date = 
                    date_trunc('month', msedelist.dlstdt)::date
        WHERE msf.date BETWEEN '01/01/1960' AND '12/31/2021' AND
              msenames.shrcd IN (10, 11)"""
)

crsp_monthly = (pd.read_sql_query(
    sql=crsp_monthly_query,
    con=wrds,
    dtype={"permno": np.int64, "exchcd": np.int64, "siccd": np.int64},
    parse_dates={"date", "month"})
  .assign(shrout = lambda x: x["shrout"] * 1000)
)

Now, we have all the relevant monthly return data in memory and proceed with preparing the data for future analyses. We perform the preparation step at the current stage since we want to avoid executing the same mutations every time we use the data in subsequent chapters.

The first additional variable we create is market capitalization (mktcap), which is the product of the number of outstanding shares shrout and the last traded price in a month altprc. Note that in contrast to returns ret, these two variables are not adjusted ex-post for any corporate actions like stock splits. Moreover, the altprc is negative whenever the last traded price does not exist, and CRSP decides to report the mid-quote of the last available order book instead. Hence, we take the absolute value of the market cap. We also keep the market cap in millions of USD just for convenience as we do not want to print huge numbers in our figures and tables. In addition, we set zero market cap to missing as it makes conceptually little sense (i.e., the firm would be bankrupt).

crsp_monthly = (crsp_monthly
  .assign(mktcap = lambda x: abs(x["shrout"] * x["altprc"] / 1000000))
  .assign(mktcap = lambda x: x["mktcap"].replace(0, np.nan))
)

The next variable we frequently use is the one-month lagged market capitalization. Lagged market capitalization is typically used to compute value-weighted portfolio returns, as we demonstrate in a later chapter. The most simple and consistent way to add a column with lagged market cap values is to add one month to each observation and then join the information to our monthly CRSP data.

mktcap_lag = (crsp_monthly
  .assign(
    month = lambda x: x["month"] + DateOffset(months=1),
    mktcap_lag = lambda x: x["mktcap"]
   )
  .get(["permno", "month", "mktcap_lag"])
)

crsp_monthly = (crsp_monthly
  .merge(mktcap_lag, 
         how="left", 
         on=["permno", "month"])
)

Next, we follow Bali, Engle, and Murray (2016) in transforming listing exchange codes to explicit exchange names.

def assign_exchange(exchcd):
    if exchcd in [1, 31]:
        return "NYSE"
    elif exchcd in [2, 32]:
        return "AMEX"
    elif exchcd in [3, 33]:
        return "NASDAQ"
    else:
        return "Other"

crsp_monthly["exchange"] = crsp_monthly["exchcd"].apply(assign_exchange)

Similarly, we transform industry codes to industry descriptions following Bali, Engle, and Murray (2016). Notice that there are also other categorizations of industries (e.g., Fama and French 1997) that are commonly used.

def assign_industry(siccd):
    if 1 <= siccd <= 999:
        return "Agriculture"
    elif 1000 <= siccd <= 1499:
        return "Mining"
    elif 1500 <= siccd <= 1799:
        return "Construction"
    elif 2000 <= siccd <= 3999:
        return "Manufacturing"
    elif 4000 <= siccd <= 4899:
        return "Transportation"
    elif 4900 <= siccd <= 4999:
        return "Utilities"
    elif 5000 <= siccd <= 5199:
        return "Wholesale"
    elif 5200 <= siccd <= 5999:
        return "Retail"
    elif 6000 <= siccd <= 6799:
        return "Finance"
    elif 7000 <= siccd <= 8999:
        return "Services"
    elif 9000 <= siccd <= 9999:
        return "Public"
    else:
        return "Missing"

crsp_monthly["industry"] = crsp_monthly["siccd"].apply(assign_industry)

We also construct returns adjusted for delistings as described by Bali, Engle, and Murray (2016). The delisting of a security usually results when a company ceases operations, declares bankruptcy, merges, does not meet listing requirements, or seeks to become private. The adjustment tries to reflect the returns of investors who bought the stock in the month before the delisting and held it until the delisting date. After this transformation, we can drop the delisting returns and codes.

conditions_delisting = [
    crsp_monthly["dlstcd"].isna(),
    (~crsp_monthly["dlstcd"].isna()) & (~crsp_monthly["dlret"].isna()),
    crsp_monthly["dlstcd"].isin([500, 520, 580, 584]) | 
        ((crsp_monthly["dlstcd"] >= 551) & (crsp_monthly["dlstcd"] <= 574)),
    crsp_monthly["dlstcd"] == 100
]

choices_delisting = [
    crsp_monthly["ret"],
    crsp_monthly["dlret"],
    -0.30,
    crsp_monthly["ret"]
]

crsp_monthly = (crsp_monthly
  .assign(
    ret_adj = np.select(conditions_delisting, choices_delisting, default=-1)
  )
  .drop(columns=["dlret", "dlstcd"])
)

Next, we compute excess returns by subtracting the monthly risk-free rate provided by our Fama-French data. As we base all our analyses on the excess returns, we can drop adjusted returns and the risk-free rate from our tibble. Note that we ensure excess returns are bounded by -1 from below as a return less than -100% makes no sense conceptually. Before we can adjust the returns, we have to connect to our database and load the tibble factors_ff_monthly.

tidy_finance = sqlite3.connect("data/tidy_finance.sqlite")

factors_ff_monthly = pd.read_sql_query(
  sql="SELECT month, rf FROM factors_ff_monthly",
  con=tidy_finance,
  parse_dates={"month": {"unit": "D", "origin": "unix"}}
)
  
crsp_monthly = (crsp_monthly
  .merge(factors_ff_monthly, 
         how="left", 
         on="month")
  .assign(ret_excess = lambda x: x["ret_adj"] - x["rf"])
  .assign(ret_excess = lambda x: x["ret_excess"].clip(lower=-1))
  .drop(columns = ["ret_adj", "rf"])
)

Since excess returns and market capitalization are crucial for all our analyses, we can safely exclude all observations with missing returns or market capitalization.

crsp_monthly = (crsp_monthly
  .dropna(subset=["ret_excess", "mktcap", "mktcap_lag"])
)

Finally, we store the monthly CRSP file in our database.

(crsp_monthly
  .assign(
    date = lambda x: (x["date"]- pd.Timestamp("1970-01-01")) // pd.Timedelta("1d"),
    month = lambda x: (x["month"]- pd.Timestamp("1970-01-01")) // pd.Timedelta("1d")
  )
  .to_sql(name="crsp_monthly", 
          con=tidy_finance, 
          if_exists="replace",
          index = False)
)

First Glimpse of the CRSP Sample

Before we move on to other data sources, let us look at some descriptive statistics of the CRSP sample, which is our main source for stock returns.

Figure 1 shows the monthly number of securities by listing exchange over time. NYSE has the longest history in the data, but NASDAQ lists a considerably large number of stocks. The number of stocks listed on AMEX decreased steadily over the last couple of decades. By the end of 2021, there were 2,779 stocks with a primary listing on NASDAQ, 1,395 on NYSE, 145 on AMEX, and only one belonged to the other category.

from plotnine import *
from mizani.formatters import comma_format

securities_per_exchange = (crsp_monthly
  .groupby(["exchange", "date"])
  .size()
  .reset_index(name="n")
)

securities_per_exchange_figure = (ggplot(securities_per_exchange, 
        aes(x="date", y="n", 
            color="exchange"))
  + geom_line()
  + labs(x="", y="", color="",
         title="Monthly number of securities by listing exchange")
  + scale_x_datetime(date_breaks="10 years", date_labels="%Y")
  + scale_y_continuous(labels=comma_format())
)
securities_per_exchange_figure.draw()

Figure 1: ?(caption)

Next, we look at the aggregate market capitalization grouped by the respective listing exchanges in Figure 2. To ensure that we look at meaningful data which is comparable over time, we adjust the nominal values for inflation. In fact, we can use the tables that are already in our database to calculate aggregate market caps by listing exchange. All values in Figure 2 are at the end of 2021 USD to ensure intertemporal comparability. NYSE-listed stocks have by far the largest market capitalization, followed by NASDAQ-listed stocks.

cpi_monthly = pd.read_sql_query(
  sql="SELECT * FROM cpi_monthly",
  con=tidy_finance,
  parse_dates={"month": {"unit": "D", "origin": "unix"}}
)

market_cap_per_exchange = (crsp_monthly
  .merge(cpi_monthly, 
         how="left", 
         on="month")
  .groupby(["month", "exchange"])
  .apply(
    lambda group: pd.Series({
        "mktcap": (group["mktcap"].sum() / group["cpi"].mean())
    })
  )
  .reset_index()
)

market_cap_per_exchange_figure = (ggplot(market_cap_per_exchange, 
        aes(x="month", y="mktcap / 1000", 
            color="exchange", linetype="exchange"))
  + geom_line()
  + labs(
      x="", y="", color="", linetype="",
      title="Monthly market cap by listing exchange in billions of Dec 2021 USD"
  )
  + scale_x_datetime(date_breaks="10 years", date_labels="%Y")
  + scale_y_continuous(labels=comma_format())
)
market_cap_per_exchange_figure.draw()

Figure 2: ?(caption)

Next, we look at the same descriptive statistics by industry. Figure 3 plots the number of stocks in the sample for each of the SIC industry classifiers. For most of the sample period, the largest share of stocks is in manufacturing, albeit the number peaked somewhere in the 90s. The number of firms associated with public administration seems to be the only category on the rise in recent years, even surpassing manufacturing at the end of our sample period.

securities_per_industry = (crsp_monthly
  .groupby(["industry", "date"])
  .size()
  .reset_index(name="n")
)

securities_per_industry_figure =(ggplot(securities_per_industry, 
        aes(x="date", y="n", color="industry"))
  + geom_line()
  + labs(x="", y="", color="",
         title="Monthly number of securities by industry")
  + scale_x_datetime(date_breaks="10 years", date_labels="%Y")
  + scale_y_continuous(labels=comma_format())
)
securities_per_industry_figure.draw()

Figure 3: ?(caption)

We also compute the market cap of all stocks belonging to the respective industries and show the evolution over time in Figure 4. All values are again in terms of billions of end of 2021 USD. At all points in time, manufacturing firms comprise of the largest portion of market capitalization. Toward the end of the sample, however, financial firms and services begin to make up a substantial portion of the market cap.

market_cap_per_industry = (crsp_monthly
  .merge(cpi_monthly, how="left", on="month")
  .groupby(["month", "industry"])
  .apply(
    lambda group: pd.Series({
        "mktcap": (group["mktcap"].sum() / group["cpi"].mean())
    })
  )
  .reset_index()
)

market_cap_per_industry_figure = (ggplot(market_cap_per_industry, 
        aes(x="month", y="mktcap / 1000", color="industry"))
  + geom_line()
  + labs(
      x="", y="", color="", 
      title="Monthly market cap by industry in billions of Dec 2021 USD"
  )
  + scale_x_datetime(date_breaks="10 years", date_labels="%Y")
  + scale_y_continuous(labels=comma_format())
)
market_cap_per_industry_figure.draw()

Figure 4: ?(caption)

Daily CRSP Data

Before we turn to accounting data, we provide a proposal for downloading daily CRSP data. While the monthly data from above typically fit into your memory and can be downloaded in a meaningful amount of time, this is usually not true for daily return data. The daily CRSP data file is substantially larger than monthly data and can exceed 20GB. This has two important implications: you cannot hold all the daily return data in your memory (hence it is not possible to copy the entire data set to your local database), and in our experience, the download usually crashes (or never stops) because it is too much data for the WRDS cloud to prepare and send to your R session.

There is a solution to this challenge. As with many big data problems, you can split up the big task into several smaller tasks that are easy to handle. That is, instead of downloading data about many stocks all at once, download the data in small batches for each stock consecutively. Such operations can be implemented in for()-loops, where we download, prepare, and store the data for a single stock in each iteration. This operation might nonetheless take a couple of hours, so you have to be patient either way (we often run such code overnight). To keep track of the progress, you can use txtProgressBar(). Eventually, we end up with more than 68 million rows of daily return data. Note that we only store the identifying information that we actually need, namely permno, date, and month alongside the excess returns. We thus ensure that our local database contains only the data we actually use and that we can load the full daily data into our memory later. Notice that we also use the function dbWriteTable() here with the option to append the new data to an existing table, when we process the second and all following batches.

# TODO: Migrate to SQLite

To the best of our knowledge, the daily CRSP data does not require any adjustments like the monthly data. The adjustment of the monthly data comes from the fact that CRSP aggregates daily data into monthly observations and has to decide which prices and returns to record if a stock gets delisted. In the daily data, there is simply no price or return after delisting, so there is also no aggregation problem.

Preparing Compustat data

Firm accounting data are an important source of information that we use in portfolio analyses in subsequent chapters. The commonly used source for firm financial information is Compustat provided by S&P Global Market Intelligence, which is a global data vendor that provides financial, statistical, and market information on active and inactive companies throughout the world. For US and Canadian companies, annual history is available back to 1950 and quarterly as well as monthly histories date back to 1962.

To access Compustat data, we can again tap WRDS, which hosts the funda table that contains annual firm-level information on North American companies. We follow the typical filter conventions and pull only data that we actually need: (i) we get only records in industrial data format, (ii) in the standard format (i.e., consolidated information in standard presentation), and (iii) only data in the desired time window.

compustat_query = (
  """SELECT gvkey, datadate, seq, ceq, at, lt, txditc, txdb, 
            itcb, pstkrv, pstkl, pstk, capx, oancf
        FROM comp.funda
        WHERE indfmt = 'INDL' AND 
              datafmt = 'STD' AND 
              consol = 'C' AND
              datadate BETWEEN '01/01/1960' AND '12/31/2021'"""
)

compustat = (pd.read_sql_query(
    sql=compustat_query,
    con=wrds,
    dtype={"gvkey": np.int64},
    parse_dates={"datadate"})
)

Next, we calculate the book value of preferred stock and equity inspired by the variable definition in Ken French’s data library. Note that we set negative or zero equity to missing which is a common practice when working with book-to-market ratios (see Fama and French 1992 for details).

compustat = (compustat
  .assign(be = lambda x: (x["seq"].combine_first(x["ceq"] + x["pstk"])
            .combine_first(x["at"] - x["lt"]) +
            x["txditc"].combine_first(x["txdb"] + x["itcb"]).fillna(0) -
            x["pstkrv"].combine_first(x["pstkl"]).combine_first(x["pstk"])
            .fillna(0)))
  .assign(
    be = lambda x: x["be"].apply(lambda y: np.nan if y <= 0 else y)
  )
)

We keep only the last available information for each firm-year group. Note that datadate defines the time the corresponding financial data refers to (e.g., annual report as of December 31, 2021). Therefore, datadate is not the date when data was made available to the public. Check out the exercises for more insights into the peculiarities of datadate.

compustat = (compustat
  .assign(
    year = lambda x: pd.DatetimeIndex(x["datadate"]).year
  )
  .sort_values("datadate")
  .groupby(["gvkey", "year"], as_index=False)
  .tail(1)
)

With the last step, we are already done preparing the firm fundamentals. Thus, we can store them in our local database.

(compustat
  .assign(
    datadate = lambda x: (x["datadate"]- pd.Timestamp("1970-01-01")) // pd.Timedelta("1d")
  )
  .to_sql(name="compustat", 
          con=tidy_finance, 
          if_exists="replace",
          index = False)
)

Merging CRSP with Compustat

Unfortunately, CRSP and Compustat use different keys to identify stocks and firms. CRSP uses permno for stocks, while Compustat uses gvkey to identify firms. Fortunately, a curated matching table on WRDS allows us to merge CRSP and Compustat, so we create a connection to the CRSP-Compustat Merged table (provided by CRSP). The linking table contains links between CRSP and Compustat identifiers from various approaches. However, we need to make sure that we keep only relevant and correct links, again following the description outlined in Bali, Engle, and Murray (2016). Note also that currently active links have no end date, so we just enter the current date via the SQL verb CURRENT_DATE.

ccmxpf_linktable_query = (
  """SELECT lpermno AS permno, gvkey, linkdt, 
            COALESCE(linkenddt, CURRENT_DATE) AS linkenddt
        FROM crsp.ccmxpf_linktable
        WHERE linktype IN ('LU', 'LC') AND
              linkprim IN ('P', 'C') AND
              usedflag = 1"""
)

ccmxpf_linktable = (pd.read_sql_query(
    sql=ccmxpf_linktable_query,
    con=wrds,
    dtype={"permno": np.int64, "gvkey": np.int64},
    parse_dates={"linkdt", "linkenddt"})
)

We use these links to create a new table with a mapping between stock identifier, firm identifier, and month. We then add these links to the Compustat gvkey to our monthly stock data.

ccm_links = (crsp_monthly
  .merge(ccmxpf_linktable, how="inner", on="permno")
  .query("~gvkey.isnull() & (date >= linkdt) & (date <= linkenddt)")
  .get(["permno", "gvkey", "date"])
)

crsp_monthly = (crsp_monthly
  .merge(ccm_links, how="left", on=["permno", "date"])
)

As the last step, we update the previously prepared monthly CRSP file with the linking information in our local database.

(crsp_monthly
  .assign(
    date = lambda x: (x["date"]- pd.Timestamp("1970-01-01")) // pd.Timedelta("1d"),
    month = lambda x: (x["month"]- pd.Timestamp("1970-01-01")) // pd.Timedelta("1d")
  )
  .to_sql(name="crsp_monthly", 
          con=tidy_finance, 
          if_exists="replace",
          index = False)
)

Before we close this chapter, let us look at an interesting descriptive statistic of our data. As the book value of equity plays a crucial role in many asset pricing applications, it is interesting to know for how many of our stocks this information is available. Hence, Figure 5 plots the share of securities with book equity values for each exchange. It turns out that the coverage is pretty bad for AMEX- and NYSE-listed stocks in the 60s but hovers around 80% for all periods thereafter. We can ignore the erratic coverage of securities that belong to the other category since there is only a handful of them anyway in our sample.

from mizani.formatters import percent_format

share_with_be = (crsp_monthly
  .assign(
    year = lambda x: pd.DatetimeIndex(x["month"]).year
  )
  .sort_values("date")
  .groupby(["permno", "year"], as_index=False)
  .tail(1)
  .merge(compustat, how="left", on=["gvkey", "year"])
  .groupby(["exchange", "year"])
  .apply(lambda x: pd.Series({
         "share": x["permno"][~x["be"].isnull()].nunique()/x["permno"].nunique()
    }))
  .reset_index()
)

share_with_be_figure = (ggplot(share_with_be, 
        aes(x="year", y="share", color="exchange"))
  + geom_line()
  + labs(x="", y="", color="",
         title="Share of securities with book equity values by exchange")
  + scale_y_continuous(labels=percent_format())
  + coord_cartesian(ylim=(0, 1))
)
share_with_be_figure.draw()

Figure 5: ?(caption)

Exercises

Compute mkt_cap_lag using shift() rather than joins as above. Filter out all the rows where the lag-based market capitalization measure is different from the one we computed above. Why are they different?
In the main part, we look at the distribution of market capitalization across exchanges and industries. Now, plot the average market capitalization of firms for each exchange and industry. What do you find?
datadate refers to the date to which the fiscal year of a corresponding firm refers to. Count the number of observations in Compustat by month of this date variable. What do you find? What does the finding suggest about pooling observations with the same fiscal year?
Go back to the original Compustat data in funda and extract rows where the same firm has multiple rows for the same fiscal year. What is the reason for these observations?
Repeat the analysis of market capitalization for book equity, which we computed from the Compustat data. Then, use the matched sample to plot book equity against market capitalization. How are these two variables related?

References

Bali, Turan G, Robert F Engle, and Scott Murray. 2016. Empirical asset pricing: The cross section of stock returns. John Wiley & Sons. https://doi.org/10.1002/9781118445112.stat07954.

Fama, Eugene F., and Kenneth R. French. 1992. “The cross-section of expected stock returns.” The Journal of Finance 47 (2): 427–65. https://doi.org/2329112.

———. 1997. “Industry Costs of Equity.” Journal of Financial Economics 43 (2): 153–93. https://doi.org/10.1016/s0304-405x(96)00896-3.

Footnotes

An alternative to establish a connection to WRDS is to use the (WRDS-Py)[https://pypi.org/project/wrds/] library. We chose to work with sqlalchemy to show how to access `PostgreSQL´ engines in general.↩︎