2
$\begingroup$

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:

  1. S&P 500 level
  2. S&P 500 earnings
  3. CPI data
  4. Long interest rate (GS10)

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

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

$\endgroup$
2
  • $\begingroup$ Why did Robert Shiller stop producing the CAPE Index? $\endgroup$
    – AlRacoon
    Commented Feb 21 at 4:25
  • $\begingroup$ I don’t know the reason why. $\endgroup$
    – phdstudent
    Commented Feb 21 at 5:11

3 Answers 3

5
+50
$\begingroup$

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):

Shiller's data

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

S&P data

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.

$\endgroup$
1
  • $\begingroup$ Thanks. I have added a comparison between Yahoo data and Shiller's price series. $\endgroup$ Commented Feb 18 at 16:33
2
$\begingroup$

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.

$\endgroup$
3
  • $\begingroup$ multpl.com uses data from the Shiller website. So data also stopped in September 2023. $\endgroup$
    – phdstudent
    Commented Feb 15 at 16:31
  • $\begingroup$ @phdstudent 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 multpl.com/s-p-500-pe-ratio/table/by-Year $\endgroup$
    – AlRacoon
    Commented Feb 15 at 20:40
  • $\begingroup$ That's fair. But again it would be useful to know where multpl is getting their data from. It certainly is not from Shiller's page, $\endgroup$
    – phdstudent
    Commented Feb 15 at 22:38
0
$\begingroup$

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

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.