If you’re like me, you track things on spreadsheets and often times on Google Sheets (the Google Docs version of Excel essentially). The Google sheets have the GoogleFinance function that is really handy for pulling in live and historic stock data. In this quick tutorial, we’ll look at how you can pull a stock price from a specific date in history.
FREE DOWNLOAD: GOOGLE FINANCE CHEAT SHEET
Featuring:- An easy-to-print, single-page cheat sheet
- Quick reference for formulas and available attributes for the GoogleFinance function
- Quickly build formulas for real-time or historical stock data in your Google Sheets
First, let’s do a quick overview of the GoogleFinance function. It looks like this:
=GOOGLEFINANCE(stock symbol,"price")
The above function will essentially pull in the current price for whatever stock you tell it to. You can tell it to pull in a stock using an explicit ticker, or as you get used to working in these sheets, you can use a different column to tell it what the ticker should be. Let’s look at both examples.
Here is how you can pull in the live price for “NFLX”:
Here is how you can pull in the live price for whatever stock ticker is in the cell next to it:
Now let’s look at how you can pull in the stock price from a particular day in history.
=GOOGLEFINANCE(stock symbol,"price","date")
By adding a date parameter to the same function, we can retrieve the data from that date. Note that this will retrieve the closing price on that day. Also, you’ll notice below that when we do this, it will output a 2×2 section of information. See below, while the function exists on cell B4, it’s outputting information to B4:C5.
What if we only want the closing price (which is what most people are trying to do)? Then we have to add in the index function as you’ll see below:
=index(GOOGLEFINANCE(stock symbol,"price","date"),2,2)
The index function lets you pull only the specific cell you want from the 4-cell output we had on the previous step. Since the closing price is in the bottom right cell (or at an index of 2,2) we will specify 2,2 in the code. You can see it in action here:
Now that you’ve got all the basics down, why don’t we check out how we can utilize this to build more robust spreadsheets of stock data.
In this next example you can see how the stock ticker and the dates are all dynamically provided to the GoogleFinance function which lets us quickly compile a look at how multiple stocks are trending over time.
Now you know how to use the Google Finance function on Google Sheets in order to pull stock share price information from a specific date in time. Stay tuned for more helpful tips on using Google Sheets for tracking stock market data.
Build a Dividend Machine with M1
Automate and grow a cash flow machine with dividend stocks at M1 Finance. M1's "pie" approach of building a dividend portfolio lets investors build incredible dividend growth portfolios with zero fees. Investing minimum: $100.
