I love using the GoogleFinance functions on Google spreadsheets for tracking and analyzing stock price movement and data. The Google team has made their Google Sheets very handy for stock market analysis and tracking through the simple use of the GoogleFinance function. Using this function, you can pull in live and historical stock price data and perform some nice analysis on the data fairly easily. In this article we’ll look at how you might pull the high or low stock price over a specific date range. Note that the GoogleFinance does have a built in 52 week high (or low) function, but what we’re attempting to do is different. We’re looking to pull the high or low price during a specific date range. For example, maybe the year-to-date high.
First, let’s do a quick overview of the GoogleFinance function. It looks like this:
=GOOGLEFINANCE(stock symbol,"price")
The above function will pull in the current price for whatever stock you specify. You can plug in a specific stock ticker, or as you get used to working in these sheets, you can use a column or cell to tell it what the ticker should be. Here’s how you would do both scenarios:
To pull in the live price for Amazon (AMZN), you would do the following:
Or, you can do it this way:
To get the high price or low price over a specific duration, there are a few steps involved, so let’s go step by step so you can fully understand how this works. First, we’re going to use the following function:
=GOOGLEFINANCE(stock ticker,"high",starting date,ending date,"DAILY")
Now, we’re going to plug in some specific values here. Let’s say we want to do the high price for Amazon over the duration of May 1, 2020 to May, 10, 2020. Here’s how the above function would change:
=GOOGLEFINANCE("AMZN","high","05/01/2020","05/10/2020","DAILY")
Let’s see what happens when we do this.
As you can see in the above example, the formula spits out a row for each date in the time span with the date and the high price for that given day. Note that this is not the closing price. It is the high price for that trading day.
Now, our next step will be to kill off the extra formatting, and just do the high values. We do that by adding in the index formula wrapped around what we did above:
=index(GOOGLEFINANCE("AMZN","high","05/01/2020","05/10/2020","DAILY"),0,2)
When we do that, it strips the first column out as follows:
Now, if we want to get the single high value over that range, we’ll add in another wrapper. The max function.
=max(index(GOOGLEFINANCE("AMZN","high","05/01/2020","05/10/2020","DAILY"),0,2))
The results in the following:
If you want to get the low price during a time span, you just adjust the max function to min and change the “high” parameter to “low”:
=min(index(GOOGLEFINANCE("AMZN","low","05/01/2020","05/10/2020","DAILY"),0,2))
And here is the result:
So, what else can we do to do even more interesting things? Let’s look at two examples.
First, rather than doing a set range of two explicit dates, you can be a bit more dynamic. You could do a running date range over the previous 7 days by doing this:
=GOOGLEFINANCE("AMZN","high",today() - 7, today(),"DAILY")
Again, you can wrap the above function with the index and/or the max/min functions in order to get the right results as we showed above.
Lastly, some folks like to look at YTD performance. In this scenario, you might do a starting date of “01/01/2020” and an ending date of today(). Here is how you would do this:
=GOOGLEFINANCE("AMZN","high","01/01/2020", today(),"DAILY")
Putting it all together
These GoogleFinance functions become pretty useful when you begin putting together more complex sheets of multiple stocks and tracking various performance across multiple durations. Here’s an example of what I’m referring to.
In this example, I’m essentially tracking the current year performance for a list of 8 stocks. I have the starting price from January 1 of the year, then the high and low for the year as well as the year to date return. Then on the far right of the spreadsheet, I’ve got performance over the last week, by looking at the stock price from a week ago and current price. This gives me an idea of how the stock is trending in more recent trading sessions.
if I were to expand upon this, I’d probably look at adding in the tracking for the major indices like the S&P 500 and the Dow Jones, then compare each of these stocks to the indices as well. This would give me an idea of how these stocks are doing both year-to-date and in the recent week against the major averages. Hopefully, this gives you a good idea of not only how to find the highs and lows of a specific stock over a date range using GoogleFinance but also how to build more complicated tracking spreadsheets.