How to Request Historical Stock Price data from Google Sheets

Google Sheets provides for a lot of ways for retrieving, organizing and manipulating data. Among these is the functionality of retrieving historical stock price data using the GOOGLEFINANCE() function . What you do with it afterwards is up to you. This is a detailed guide on how you may do so reliably.

The basic syntax for requesting historical stock price data is :

=GOOGLEFINANCE("ticker", "attribute", "start_date", "end_date")

To retrieve ONLY open, high, low, close, or volume for the date range, use “open”, “high”, etc. instead of “all” in the formula at the top of the image below. The final parameter, not included in the example is “DAILY” or “WEEKLY”, to specify daily or weekly prices.

You should take note of the data structure i.e. the ordering of the labels and how the date column is arranged in ascending order. It is better to use the today() function which returns today’s date, this will allow the data to update automatically.

In the below example, today() is 10/06/2021 minus 10 calendar days which will return data starting from 31/05/2021

By comparing, observe the change made in the formula in the image above to the one below.

Using Query function in a Google Finance formula

Best way to describe the Query function would be, a function for making special requests and manipulating data.

A simple application would be to find the highest traded price in the past 10 trading days,

=MAX(Query(GOOGLEFINANCE("JSE:MTN","All",today()-20 ,today()),"Select Col3 order by Col1 Desc limit 10"))

Query: I requested data from column 3 (“high”) to be arranged in descending date order ( Col1) and then requested the retrieved data to be limited to the first 10 results ( 10 trading days). I then wrapped my request in the MAX() function.

Another important note, is to request more days then you require, this will add a buffer for weekends and holidays, then “limit” your request with the query function, to the amount of days your analysis requires.


Leave a Reply