Find Max and Min in GoogleFinance Historical Data in Google Sheets

When fetching historical data in Google Sheets, the GoogleFinance function typically returns two columns: one for the date and one for the corresponding value. This raises an important question: How can you find the Max and Min values in GoogleFinance historical data?

If you’re working with a single column of data, using the MIN or MAX functions is straightforward. However, since the GoogleFinance function in Google Sheets returns two columns — one for date/time and another for historical values — finding Max and Min values in GoogleFinance historical data can be a bit more involved.

In this post, I’ll show you how to extract the Max and Min values from GoogleFinance historical data using a consistent formula approach, whether you’re working with two columns or multiple columns.

Formulas to Find Max and Min Values in GoogleFinance Historical Data

To calculate the Max and Min values from multi-row historical data in Google Sheets, use the following formulas:

Maximum Value:

=LET(
   gf, gf_formula, 
   FILTER(CHOOSECOLS(gf, 1, 2), CHOOSECOLS(gf, 2)=MAX(CHOOSECOLS(gf, 2)))
)

Minimum Value:

=LET(
   gf, gf_formula, 
   FILTER(CHOOSECOLS(gf, 1, 2), CHOOSECOLS(gf, 2)=MIN(CHOOSECOLS(gf, 2)))
)

Replace gf_formula with your actual GoogleFinance formula fetching the historical data.

Finding Max and Min Values in GoogleFinance Two-Column Historical Data

Here’s an example fetching the trading volumes for the last 15 days for “GOOG”:

=GOOGLEFINANCE("NASDAQ:GOOG", "VOLUME", TODAY()-15, TODAY())

This returns a two-column expanding output like:

DateVolume
14/04/2025 16:00:0018255931
15/04/2025 16:00:0015690783

In this example, I used the VOLUME attribute. Other supported attributes for historical data include OPEN, CLOSE, HIGH, LOW, and ALL.

For all attributes except “ALL,” GoogleFinance returns two columns.

Example: Finding Max and Min from Two Columns

To get the Max value:

=LET(
   gf, GOOGLEFINANCE("NASDAQ:GOOG","VOLUME",TODAY()-15,TODAY()), 
   FILTER(CHOOSECOLS(gf, 1, 2), CHOOSECOLS(gf, 2)=MAX(CHOOSECOLS(gf, 2)))
)

To get the Min value:

=LET(
   gf, GOOGLEFINANCE("NASDAQ:GOOG","VOLUME",TODAY()-15,TODAY()), 
   FILTER(CHOOSECOLS(gf, 1, 2), CHOOSECOLS(gf, 2)=MIN(CHOOSECOLS(gf, 2)))
)

Finding Max and Min Values in GoogleFinance Multi-Column Historical Data

When using the “ALL” attribute, GoogleFinance returns multiple columns (Date, Open, High, Low, Close, Volume):

=GOOGLEFINANCE("NASDAQ:GOOG", "ALL", TODAY()-15, TODAY())

Sample output:

DateOpenHighLowCloseVolume
14/04/2025 16:00:00162.31164.03159.92161.4718255931

Suppose you want to find the Max or Min value from the Close column (column 5).

Maximum Value:

=LET(
   gf, GOOGLEFINANCE("NASDAQ:GOOG","ALL",TODAY()-15,TODAY()), 
   FILTER(CHOOSECOLS(gf, 1, 5), CHOOSECOLS(gf, 5)=MAX(CHOOSECOLS(gf, 5)))
)

Minimum Value:

=LET(
   gf, GOOGLEFINANCE("NASDAQ:GOOG","ALL",TODAY()-15,TODAY()), 
   FILTER(CHOOSECOLS(gf, 1, 5), CHOOSECOLS(gf, 5)=MIN(CHOOSECOLS(gf, 5)))
)

Explanation of the Changes:

  • CHOOSECOLS(gf, 1, 5) extracts the Date and Close columns.
  • CHOOSECOLS(gf, 5) specifically extracts the Close column values only.

Earlier, for two-column data, we extracted columns 1 and 2 instead of 1 and 5.

How to Omit the Timestamp from the Output?

If you only want the Max or Min value without the timestamp, modify the formula slightly:

  • Replace CHOOSECOLS(gf, 1, 2) with CHOOSECOLS(gf, 2), or
  • Replace CHOOSECOLS(gf, 1, 5) with CHOOSECOLS(gf, 5), depending on your dataset.

This will return just the historical value instead of a two-column result.

Additional Resources:

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Expert Prashanth KV is a Diamond Product Expert in Google Sheets, officially recognized by Google for his contributions to the Docs Editors Help Community and featured in the Google Product Experts Directory. Explore his blog to learn advanced formulas, automation tips, and problem-solving techniques to elevate your spreadsheet skills.

Calculate Trip Days by Month (Start, End, and Full Days) in Google Sheets

If you're managing business travel in Google Sheets, you may need to calculate how...

Mode of Comma-Separated Numbers in Excel (Dynamic Array)

There is no dedicated function in Excel to directly find the mode of comma-separated...

How to Find Mode of Comma-Separated Numbers in Google Sheets

Working with comma-separated numbers inside a single cell is a common scenario in Google...

How to Count Merged Cells in Google Sheets (and Get Their Size)

Sometimes, you may have blocks of merged cells in a column or row. But...

More like this

Calculate Trip Days by Month (Start, End, and Full Days) in Google Sheets

If you're managing business travel in Google Sheets, you may need to calculate how...

How to Find Mode of Comma-Separated Numbers in Google Sheets

Working with comma-separated numbers inside a single cell is a common scenario in Google...

How to Count Merged Cells in Google Sheets (and Get Their Size)

Sometimes, you may have blocks of merged cells in a column or row. But...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.