Updated: Jul 28
I learned this super simple trick for getting historical stock data quickly in Google Sheets! Before I was copy/pasting data from Yahoo Finance when I could simply type in a formula and have Google do all the hard work for me! Once you have your stock data, you could combine multiple sheets into one master sheet for the ultimate analysis. You could even make the analysis easier by adding colored dropdown boxes and finding duplicate values!
I think you’ll find this method of getting historical stock prices and data super easy and helpful!
Before getting started, make sure you know the stock symbol of the company you would like to pull data for. Then I’ll show you how to quickly and easily have Google pull the rest of the data for you! With this formula, not only can you get the historical data but also pull up company data that you might find useful.
Prepare your Spreadsheet: Overview Tab
You can use this spreadsheet as a handy stock analysis tool that updates by simply changing the ticker symbol. I’ve set up my spreadsheet to have three tabs: overview, historical prices, and chart.
On the overview tab I want to display all of the companies stock data for a quick snapshot.
This is where we’re going to use the GOOGLEFINANCE formula with the different parameters to display the correct information. I have set up my Overview tab so that i only need to change the symbol cell and all of the fields will update accordingly.
Here is a list of the different parameters (in italics below) you can use in the GOOGLE FINANCE formla and what each one represents:Company Name: nameCurrency: currencyReal-time Price (20 min delay): priceLast Trade Time: tradetimeData Delay: datadelayOpen Price: priceopenHigh: highLow: lowToday’s Volume: volumeChange since EOD Yesterday: changeChange %: changepctYesterday Close: closeyestMarket Cap: marketcapAvg Daily Volume: volumeavgOutstanding Shares: sharesP/E Ratio: peEarnings per Share: eps52 Week High: high5252 Week Low: low52Beta: beta
In order to automatically populate these fields using the parameters. I have set up my spreadsheet to fill in the ticket symbol right at the top and also list the parameters that I need in the formula. This way I can grab the information I need for the formula.
Here is what my spreadsheet looks like:
For example, in order to populate the Company Name, my formula looks like this
This formula is saying, pull the stock symbol in cell B1 and the parameter in cell B2 to populate the Company Name (cell C2).
Notice that for the stock symbol, I added the dollar signs ($). This is so I can copy the formula down the sheet and it knows to ALWAYS stay on cell B1 for the symbol. Otherwise, when I copy down, it would try to pull B2, B3, B4, etc. for the stock symbol.
Now that you have the formulas and the parameters on your sheet, you can copy the formula down and automatically populate all of the information you want. By changing just the ticker symbol, you can populate this entire page in basically 1 click!