4.3 CRSP Market Returns Indices#
The CRSP (Center for Research in Security Prices) dataset provides two indices
for market returns: an equal-weighted index and a value-weighted index (both provided
in terms of returns with and without dividends). The equal-weighted index
computes the simple average of returns across stocks. This series is available as EWRETD and EWRETX, (with and without dividends, respectively).
The value-Weighted Returns index represents a stock market index that calculates the return on investment by considering both the price changes and dividends of each component security, weighted by its market capitalization. This means that larger companies have a greater impact on the index’s performance compared to smaller companies. The value-weighting approach aims to reflect the actual investment returns that an investor would achieve by holding a market portfolio, mirroring the performance of the overall market or specific market segments more accurately than equal-weighted indices. The CRSP indices are widely used in academic research and financial analysis to study market trends, evaluate investment strategies, and benchmark the performance of portfolios against the broader market. This series is available in the CRSP tables under the mnemonic VWRETD and VWRETX (with and without dividends, respectively).
In this guide, we’ll discuss the construction of the equal- and value-weighted market return indices. To construct these indices, we’ll follow the suggestions here: https://wrds-www.wharton.upenn.edu/pages/support/support-articles/crsp/index-and-deciles/constructing-value-weighted-return-series-matches-vwretd-crsp-monthly-value-weighted-returns-includes-distributions/
These suggestions boil down to the most important part: we must select the correct universe of stocks that comprise “the market”.
# Why might these commands be helpful?
%load_ext autoreload
%autoreload 2
import pandas as pd
import config
import load_CRSP_stock
import calc_CRSP_indices
import misc_tools
DATA_DIR = config.DATA_DIR
df_msf = load_CRSP_stock.load_CRSP_monthly_file(data_dir=DATA_DIR)
df_msix = load_CRSP_stock.load_CRSP_index_files(data_dir=DATA_DIR)
df_msix.info()
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 45 entries, 0 to 44
Data columns (total 35 columns):
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 caldt 45 non-null datetime64[ns]
1 vwretd 45 non-null float64
2 vwindd 45 non-null float64
3 vwretx 45 non-null float64
4 vwindx 45 non-null float64
5 ewretd 45 non-null float64
6 ewindd 45 non-null float64
7 ewretx 45 non-null float64
8 ewindx 45 non-null float64
9 sprtrn 45 non-null float64
10 spindx 45 non-null float64
11 decret1 45 non-null float64
12 decind1 45 non-null float64
13 decret2 45 non-null float64
14 decind2 45 non-null float64
15 decret3 45 non-null float64
16 decind3 45 non-null float64
17 decret4 45 non-null float64
18 decind4 45 non-null float64
19 decret5 45 non-null float64
20 decind5 45 non-null float64
21 decret6 45 non-null float64
22 decind6 45 non-null float64
23 decret7 45 non-null float64
24 decind7 45 non-null float64
25 decret8 45 non-null float64
26 decind8 45 non-null float64
27 decret9 45 non-null float64
28 decind9 45 non-null float64
29 decret10 45 non-null float64
30 decind10 45 non-null float64
31 totval 45 non-null float64
32 totcnt 45 non-null int64
33 usdval 45 non-null float64
34 usdcnt 45 non-null int64
dtypes: datetime64[ns](1), float64(32), int64(2)
memory usage: 12.4 KB
df_msix[[
"caldt",
"vwretd",
"vwretx",
"vwindx",
"ewretd",
"ewretx",]].tail()
| caldt | vwretd | vwretx | vwindx | ewretd | ewretx | |
|---|---|---|---|---|---|---|
| 40 | 2023-05-31 | 0.002027 | 0.000129 | 3222.812 | -0.006612 | -0.008389 |
| 41 | 2023-06-30 | 0.067583 | 0.066073 | 3435.752 | 0.054809 | 0.052556 |
| 42 | 2023-07-31 | 0.035849 | 0.034858 | 3555.517 | 0.046141 | 0.045216 |
| 43 | 2023-08-31 | -0.020300 | -0.022082 | 3477.003 | -0.060090 | -0.061725 |
| 44 | 2023-09-29 | -0.048003 | -0.049226 | 3305.845 | -0.061929 | -0.063713 |
Inclusion into the CRSP Market Index:#
Our experiments with different VWRETD replication methods show that it is relatively easy to come close to this data series using PERMNO-based returns in the CRSP datasets, but exact matches to every data month is not possible because we do not know the exact sample set of PERMNOs used by CRSP. Their criteria is listed in the CRSP manual and is roughly:
CRSP CAP-BASED PORTFOLIOS – The following types of securities, listed on NYSE, AMEX, and Nasdaq National Market, are eligible for inclusion in the Cap-Based Indices:
Common Stocks
Certificates
Shares of Beneficial Interest
Units (Depository Units, Units of Beneficial Interest, Units of Limited Partnership Interest, Depository Receipts, etc.)
The following types of securities are NOT eligible for inclusion in the Cap-Based Indices:
ADRs
Closed-End Mutual Funds, WEBS Index Funds, Unit Investment Trusts
All Common Stocks with non-US Incorporation
Americus Trust Components
HOLDRs Trusts
REITs (Real Estate Investment Trusts)
Rights and Warrants
Preferred stock
“Packaged” Units (Common Stocks Bundled with Rights or Warrants)
Over-the-Counter Bulletin Board Issues
N.B. The Cap-Based Indices do include returns from time ranges during which eligible securities trade on “leading prices” or “reorganization” when-issued status. The Cap-Based Indices do NOT include returns from time ranges during which eligible securities trade on “ex-distribution” or “additional” when-issued status.
Note that VWRETD is not computed by WRDS but provided directly by CRSP along with the PERMNO based returns. For general SAS coding help for this problem see the WRDS Research Application: Portfolios by Size and Book-to-Market. This WRDS Support document provides examples of cap-based decile breakdowns, but the same general principles apply to the total market index.
I’ve provided code for you that will take care of this subsetting in the function pull_CRSP_monthly_file:
SELECT
date,
msf.permno, msf.permco, shrcd, exchcd, comnam, shrcls,
ret, retx, dlret, dlretx, dlstcd,
prc, altprc, vol, shrout, cfacshr, cfacpr,
naics, siccd
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 '{start_date}' AND '{end_date}' AND
msenames.shrcd IN (10, 11, 20, 21, 40, 41, 70, 71, 73)
To best understand this, please look up shrcd in the Data Manual here: https://wrds-www.wharton.upenn.edu/documents/396/CRSP_US_Stock_Indices_Data_Descriptions.pdf . You’ll find the information on p. 81.
Calculation of Equal-Weighted Returns and Value-Weighted Returns#
With the proper universe of stocks in hand, all that is left is to group the returns by permno (the identifier of choice here) and average. However, the equal weighted average is a mere simple average. To calculate the value-weighted average, we need to calculate the lagged market cap of each stock \(i\) at time \(t\).
That is, the value-weighted return is given by the following formula:
where \(w_{i,t-1}\) is the market capitalization of stock \(i\) at time \(t-1\) and
\(r_t\) can be the returns with dividends ret or the returns without dividends retx.
The market capitalization of a stock is its price times the shares outstanding,
$\(
w_{it} = \text{SHROUT}_{it} \times \text{PRC}_{it}.
\)$
df_eq_idx = calc_CRSP_indices.calc_equal_weighted_index(df_msf)
df_vw_idx = calc_CRSP_indices.calc_CRSP_value_weighted_index(df_msf)
df_idxs = calc_CRSP_indices.calc_CRSP_indices_merge(df_msf, df_msix)
df_idxs[[
'vwretd', 'vwretx', 'ewretd', 'ewretx',
'vwretd_manual', 'vwretx_manual', 'ewretd_manual', 'ewretx_manual',]].head()
| vwretd | vwretx | ewretd | ewretx | vwretd_manual | vwretx_manual | ewretd_manual | ewretx_manual | |
|---|---|---|---|---|---|---|---|---|
| date | ||||||||
| 2020-01-31 | -0.001066 | -0.002282 | -0.012758 | -0.013789 | -0.000965 | -0.002037 | -0.012917 | -0.013474 |
| 2020-02-28 | -0.078865 | -0.080954 | -0.073532 | -0.075278 | -0.077996 | -0.079926 | -0.067065 | -0.068259 |
| 2020-03-31 | -0.142565 | -0.144375 | -0.222260 | -0.224499 | -0.133205 | -0.134881 | -0.196421 | -0.198053 |
| 2020-04-30 | 0.132193 | 0.130848 | 0.170388 | 0.168871 | 0.131750 | 0.130657 | 0.156846 | 0.156012 |
| 2020-05-29 | 0.054521 | 0.052321 | 0.069879 | 0.067784 | 0.054298 | 0.052300 | 0.066700 | 0.065393 |
df_idxs[[
'vwretd', 'vwretx', 'ewretd', 'ewretx',
'vwretd_manual', 'vwretx_manual', 'ewretd_manual', 'ewretx_manual',]].corr()
| vwretd | vwretx | ewretd | ewretx | vwretd_manual | vwretx_manual | ewretd_manual | ewretx_manual | |
|---|---|---|---|---|---|---|---|---|
| vwretd | 1.000000 | 0.999984 | 0.885229 | 0.885232 | 0.999607 | 0.999621 | 0.914436 | 0.914552 |
| vwretx | 0.999984 | 1.000000 | 0.885343 | 0.885371 | 0.999551 | 0.999599 | 0.914517 | 0.914658 |
| ewretd | 0.885229 | 0.885343 | 1.000000 | 0.999980 | 0.879427 | 0.879557 | 0.996912 | 0.996897 |
| ewretx | 0.885232 | 0.885371 | 0.999980 | 1.000000 | 0.879403 | 0.879565 | 0.996875 | 0.996916 |
| vwretd_manual | 0.999607 | 0.999551 | 0.879427 | 0.879403 | 1.000000 | 0.999981 | 0.909613 | 0.909724 |
| vwretx_manual | 0.999621 | 0.999599 | 0.879557 | 0.879565 | 0.999981 | 1.000000 | 0.909701 | 0.909855 |
| ewretd_manual | 0.914436 | 0.914517 | 0.996912 | 0.996875 | 0.909613 | 0.909701 | 1.000000 | 0.999945 |
| ewretx_manual | 0.914552 | 0.914658 | 0.996897 | 0.996916 | 0.909724 | 0.909855 | 0.999945 | 1.000000 |
As you can see above, our manually-created return index doesn’t match the CRSP index perfectly but is still very close. In this HW, you’ll be required to construct this index only approximately. A loose match, as seen here, will be fine.
Note, a helpful tool to create the lagged time series for market capitalization is provided in misc_tools.
Use the function with_lagged_column, which will create a lagged column that accounts for the fact that multiple stocks show up in a flat file. See the following example:
a=[
[1,'1990/1/1',1],
[1,'1990/2/1',2],
[1,'1990/3/1',3],
[2,'1989/12/1',3],
[2,'1990/1/1',3],
[2,'1990/2/1',4],
[2,'1990/3/1',5.5],
[2,'1990/4/1',5],
[2,'1990/6/1',6]
]
data=pd.DataFrame(a,columns=['id','date','value'])
data['date']=pd.to_datetime(data['date'])
data
| id | date | value | |
|---|---|---|---|
| 0 | 1 | 1990-01-01 | 1.0 |
| 1 | 1 | 1990-02-01 | 2.0 |
| 2 | 1 | 1990-03-01 | 3.0 |
| 3 | 2 | 1989-12-01 | 3.0 |
| 4 | 2 | 1990-01-01 | 3.0 |
| 5 | 2 | 1990-02-01 | 4.0 |
| 6 | 2 | 1990-03-01 | 5.5 |
| 7 | 2 | 1990-04-01 | 5.0 |
| 8 | 2 | 1990-06-01 | 6.0 |
data_lag = misc_tools.with_lagged_columns(df=data, column_to_lag='value', id_column='id', lags=1, freq="BME")
data_lag
| id | date | value | L1_value | |
|---|---|---|---|---|
| 4 | 1 | 1990-02-28 | NaN | 1.0 |
| 6 | 1 | 1990-03-30 | NaN | 2.0 |
| 8 | 1 | 1990-04-30 | NaN | 3.0 |
| 3 | 2 | 1990-01-31 | NaN | 3.0 |
| 5 | 2 | 1990-02-28 | NaN | 3.0 |
| 7 | 2 | 1990-03-30 | NaN | 4.0 |
| 9 | 2 | 1990-04-30 | NaN | 5.5 |
| 11 | 2 | 1990-05-31 | NaN | 5.0 |
As you can see, naively using shift to create our lag would miss the fact that observation 1989-12-01 for stock id=2 should have a missing lagged value. For example, the following would be incorrect:
data['value'].shift(1)
0 NaN
1 1.0
2 2.0
3 3.0
4 3.0
5 3.0
6 4.0
7 5.5
8 5.0
Name: value, dtype: float64