How to Quickly Get Historical Stock Prices In Google Sheets

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:

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

=GOOGLEFINANCE($B$1,B2)

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!

Import Historical Stock Data

On the second tab in my spreadsheet, I want to pull historical stock data and prices. I will also automate this so that it updates based on the stock symbol typed in on the overview tab. In the formula below Overview!B1 refers to the cell in the overview tab where the stock ticker symbol is inputted.

The parameters for bringing in historical data a more limited than what we see in the overview tab. You can bring data into columns using these self-explanatory parameters: open, close, high, low, volume, or all. In my spreadsheet, I’ve used the "all" parameter so that it displays everything.

You’ll also need to specify the date range that you’d like to pull the historical data for. You’ll need both a start date and end date formatted like this DATE(YYYY,M,D) and you’ll need two if these in the formula. The first represents the start date and the second represents the end date.

Lastly, you’ll need to indicate if you want data for every trading day or week by using the parameter daily or weekly.

Your formula should look like this:

=GOOGLEFINANCE(Overview!B1,"all",DATE(2021,1,1),DATE(2021,3,12),"daily")

Here is what your spreadsheet will look like when you chose to display “all” data. If you use “open”, “high”, or any of the other parameters only, you’ll only see those columns on your sheet.

Create Charts from GoogleFinance Historical Data

Now that you have all of the historical data, you can use that data to build charts that show your stock’s movement. Once you have a Charts tab set up on your spreadsheet, it will automatically update using the stock symbol on your Overview tab and the historical data that populates on the Historical tab.

Creating a chart is super simple. First, select the rows that display all of your data. Then, click on Insert and then select Charts.

The chart will probably insert on top of your data but don’t worry, you can cut/paste the chart into its own tab. First, let's complete the settings. You can select which rows you would like to use to display data. You can also use the checkboxes to change the look of your form.

To move your chart to its own sheet by clicking the three dots in the upper right of your chart, and then click on Move to own sheet.

Once the spreadsheet is on its own sheet, you’ll a sheet that looks like this. You can edit the chart from this tab by simply double-clicking on the chart.