EPS for a market in Google Sheets

Question

In Google Sheets, I can use the GOOGLEFINANCE function to get a number of stock data points including:

• price
• volume
• eps

I have used the GOOGLEFINANCE function on market data points for the Dow (ticker .DJI), S&P (.INX), and NASDAQ(.IXIC). Specifically, I have successfully gotten the close points from GOOGLEFINANCE using:

=INDEX(GOOGLEFINANCE(".INX","close",DATE(2018,01,01),2,2))

(See school finance project on Google Sheets here)

Is it possible to get the eps data point for a market in Google Sheets? Ultimately, I want to get the Shiller PE or CAPE ratio. In order to do that, I need EPS.

Answer 1

No, it is not. Indices like the Dow or the NASDAQ are just that: Indices. EPS (earnings per share) isn't a measurement that makes sense for those, as an index itself doesn't have any earnings — nor indeed any shares.

Markets thus don't have an EPS data point for Google Sheets to get. This is why Google Sheets gives you a tool tip saying that "eps" is not a valid attribute for the ticker symbol specified.

If you want to calculate the Shiller PE or CAPE ratio for an index, you need to calculate it individually for each company listed on that index and then aggregate the data.

I am not a financial mathematician, so I couldn't tell you how this is done — and it's somewhat outside the scope of this forum. I suggest taking this to either the Math or Quantitative Finance StackExchanges.

Answer 2

What I have done is do an ImportXML from a website. I am looking up stocks from the Toronto Stock exchange so I have been using the TMXMoney website for the EPS. For Example: Say you are wanting Telus (T.TO) stocks EPS then use the command in Google Sheets:

=Index(Split(Index(IMPORTXML("https://web.tmxmoney.com/quote.php?qm_symbol=T", "//*[@class='tmx-panel-body']/div"),6,14)," "),1,2)

Take note that importXML is slow to initially populate but it does.