Reconstructing Shiller’s CAPE with Earnings and Monthly Data

# Replicate Shiller's CAPE index


# Replicate Shiller's CAPE index












So Robert Shiller used to update his CAPE index file monthly. It seems that he stopped doing so in September 2023. Here's the link: http://www.econ.yale.edu/~shiller/data.htm

To compute this index he needs 4 time-series of data:

- S&P 500 level

- S&P 500 earnings

- CPI data

- Long interest rate (GS10)

Now number (3) and number (4) are easy to obtain. They are literally this two time-series from FRED:

- https://fred.stlouisfed.org/data/GS10.txt

- https://fred.stlouisfed.org/series/CPIAUCSL

What I am struggling with, is to get a reliable source to S&P500 earnings. Ideally I would like a source coming directly from S&P Global (https://www.spglobal.com/en/) or from WRDS (the academic database from wharton).

Now I am aware of this link on stackexchange Which Data Sources are Available Online. I did not find what I need there and I am looking for a more direct answer to my question.

https://fred.stlouisfed.org/series/CPIAUCSL

## Answer by Enrico Schumann (score 5, accepted)

https://quant.stackexchange.com/a/78380

S&P seems to publish the earnings at https://www.spglobal.com/spdji/en/documents/additional-material/sp-500-eps-est.xlsx (though I do not regularly use this source and so I don't know how stable that source is). There is a link under https://www.spglobal.com/spdji/en/indices/equity/sp-500/#overview (see "Additional Info"). [Note that you can get price data from the same site, by using the "Export" option in the performance chart.]

Robert Shiller's earnings data, with the last two quarters marked (the non-quarter months are interpolated):

S&P's data with the last quarterly numbers marked:

June differs slightly, but perhaps not all companies have had reported when Professor Shiller had last updated the file.

For the price level, at least in the past, I think he used an average price for the month. Following the comment of @nbbo2 , here is a quick check (in R), comparing Robert Shiller's data with Yahoo:

```
library("NMOF")
library("tseries")
library("zoo")

## Fetch data from Robert Shiller's website:
shiller <- Shiller(tempdir())
shiller.date <- format(shiller[["Date"]], "%Y-%m")
shiller.price <- shiller[["Price"]]

## Fetch S&P 500 data from Yahoo:
yhoo <- get.hist.quote("^SPX", quote = "Close")
yhoo <- tapply(coredata(yhoo), FUN = mean,
               format(index(yhoo), "%Y-%m"))
yhoo <- yhoo[names(yhoo) %in% shiller.date]

## Compare data
keep <- shiller.date %in% names(yhoo)
data.frame(Shiller = shiller.price[keep],
           Yahoo = yhoo,
           Difference = round(shiller.price[keep] - yhoo, 2))
##          Shiller     Yahoo Difference
## 1991-01  325.490  325.4855       0.00
## 1991-02  362.260  362.2632       0.00
## 1991-03  372.280  372.2790       0.00
## 1991-04  379.680  379.6786       0.00
## 1991-05  377.990  377.9923       0.00
## ....
## 1997-07  925.290  925.2945       0.00
## 1997-08  927.240  927.7357      -0.50
## 1997-09  937.020  937.0243       0.00
## ....
## 2002-03 1153.790 1153.7910       0.00
## 2002-04 1111.930 1112.0345      -0.10
## 2002-05 1079.250 1079.2673      -0.02
## 2002-06 1014.020 1014.0480      -0.03
## 2002-07  903.590  903.5855       0.00
## ....
## 2022-11 3917.489 3917.4886       0.00
## 2022-12 3912.381 3912.3810       0.00
## 2023-01 3960.657 3960.6565       0.00
## 2023-02 4079.685 4079.6847       0.00
## 2023-03 3968.559 3968.5591       0.00
## 2023-04 4121.467 4121.4674       0.00
## 2023-05 4146.173 4146.1732       0.00
## 2023-06 4345.373 4345.3728       0.00
## 2023-07 4508.076 4508.0755       0.00
## 2023-08 4457.359 4457.3587       0.00
## 2023-09 4515.770 4409.0950     106.68
```

I have suppressed most of the output -- for most months, the difference between the mean S&P and Shiller's price series is zero.

```
summary(round(shiller.price[keep] - yhoo, 2))
##    Min. 1st Qu.  Median    Mean 3rd Qu.    Max. 
##  -6.530   0.000   0.000   0.258   0.000 106.680
```

The single large deviation of 106 happens in September 2023, because the file had not been updated.

## Answer by AlRacoon (score 2)

https://quant.stackexchange.com/a/78321

Can't speak to the veracity of this data series but found these for (1) S&P 500 level and (2) S&P 500 earnings

(1) https://finance.yahoo.com/quote/%5EGSPC/history

https://www.multpl.com/s-p-500-historical-prices/table/by-month

(2) https://www.multpl.com/s-p-500-earnings/table/by-month

The fourth link down from the following search downloads a spreadsheet with quarterly S&P 500 earnings from what appears to be spglobal.com.

https://www.google.com/search?q=s%26p+500+earnings+per+share&sca_esv=f685e4e4fadb486b&ei=z3DNZauSAfzgkPIPsoyoMA&oq=sp500+earnings+data&gs_lp=Egxnd3Mtd2l6LXNlcnAiE3NwNTAwIGVhcm5pbmdzIGRhdGEqAggEMgQQABhHMgQQABhHMgQQABhHMgQQABhHMgQQABhHMgQQABhHMgQQABhHMgQQABhHSJdZUABYAHAAeAGQAQCYAUigAUiqAQExuAEByAEA4gMEGAAgQYgGAZAGCA&sclient=gws-wiz-serp

Another way you may be able to back into the earnings is divide the index level by the PE ratio. PE ratios can be found at https://www.multpl.com/s-p-500-pe-ratio/table/by-Year by year and https://www.multpl.com/s-p-500-pe-ratio/table/by-month for monthly data.

## Answer by nbbo2 (score 0)

https://quant.stackexchange.com/a/78388

Mr. Schumann already showed how the S&P 500 Reported Earnings for 2023.03 and 2023.06 can be found in the spreadsheet from SPGLOBAL. (It makes sense BTW that SPGLOBAL would be the source of this data, they are the owners of the S&P 500 trademark after all).

The information in the SPGLOBAL sheet (sp-500-eps-est.xlsx) is quarterly (i.e. for March, June, Sept. and Dec.). It only remains to understand how Prof. Shiller is able to give monthly values.

The answer appears to be that the non-quarterly values are estimated by linear interpolation. For example for 2023.04 Shiller has 177.17.

This is found from the quarterly values as follows:

175.17+(1/3)*(181.17-175.17) = 177.17

Similarly the value for 2023.05 is found as

175.17+(2/3)*(181.17-175.17) = 179.17

HTH