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:

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:

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:

• How to Find Max Value in Each Row in Google Sheets
• VLOOKUP in Max Rows in Google Sheets
• How to Exclude Zeros from MIN Function Results in Google Sheets
• Find Top N Values with Criteria in Google Sheets (MAX/LARGE)
• Find Max N Values in a Row and Return Headers in Google Sheets