Stock Spreadsheets on Google Sheets

Google Sheets spreadsheets can retrieve stock market data. Youtube has a video that shows how–

[USING GOOGLE FINANCE TO ANALYZE STOCKS IN GOOGLE SHEETS - YouTube](https://Stock Spreadsheets on Google Sheets)

You can get a wide variety of info including 20 min delay stock price, PE, close, earnings per share, high low price as well as historical data.

A useful way to track your portfolio or to sort through a list of stocks.

The basic formula is–

=Googlefinance(ticker), [attribute],[start date],enddate|number days], [interval]

Ticker Name Price PE High52 Low52
=GoogleFinance(A3), “name” or B2

Price =Googlefinance (A3,C2) Chg to $A$3 and drag across.

Sample Usage
GOOGLEFINANCE(“NASDAQ:GOOG”, “price”, DATE(2014,1,1), DATE(2014,12,31), “DAILY”)

GOOGLEFINANCE(“NASDAQ:GOOG”,“price”,TODAY()-30,TODAY())
=GOOGLEFINANCE(“NASDAQ:GOOG”,“price”,TODAY()-30,TODAY())

=GOOGLEFINANCE(“GOOG”, “price”, DATE(2019,1,1)) returns one date
=GOOGLEFINANCE(“GOOG”, “price”, DATE(2019,1,1-90)) returns one date 90 days previous

GOOGLEFINANCE(A2,A3)

Syntax
GOOGLEFINANCE(ticker, [attribute], [start_date], [end_date|num_days], [interval])

Attributes

“price” - Real-time price quote, delayed by up to 20 minutes.

“pe” - The price/earnings ratio.

“eps” - The earnings per share.

“closeyest” - The previous day’s closing price.

“close” - The closing price for the specified date(s).

“return13” - Thirteen-week total return. (Mutual funds only)

9 Likes

I am told this is possible with Excel too, but not with the Student edition. You need the Microsoft 365 level edition. The Google Sheet works fine and its free.

1 Like

I replaced my Excel sheets with Google sheets for this purpose a few years ago.
The historical price download is just awesome - enables quick backtests, comparisons, etc.
THERE ARE A FEW GAPS:
1.) Their prices are NOT dividend-adjusted (as far as I’ve seen anywhere). If you want adjusted closes, go to Yahoo Finance and download or copy/paste their “adj. close” column.
2.) Preferred stocks and some other fixed income vehicles (especially bonds) are not available.
3.) After 8pm and on weekends, the quoting service becomes unresponsive/stale. You’ve been working in a sheet for 20 to 30 minutes and suddenly all the GF calls display “N/A”. Only solution is to close the sheet, wait 1/2 an hour and reopen it; GF will report the last available price - until you leave the sheet open for >20 minutes.

Another really cool feature is IMPORTHTML. You can bring in a publicly available table or list from a website that hasn’t blocked it. Unfortunately more websites are not using straight HTML tables / lists for $$ reasons.

3 Likes