Are you using the GoogleFinance function in Google Sheets to fetch historical data? If so, you may be wondering how to find the maximum and minimum values in that data. In this article, I will show you multiple formula options to find the max and min values in GoogleFinance historical data.
Options to Find Max and Min Values in GoogleFinance Historical Data
When using the GoogleFinance function with the start_date and end_date parameters, it returns two-column data. The first column contains date and time, while the second column contains the historical values. To find the max and min values in this data, you can use any of the following functions:
- Query.
- DMAX/DMIN Database Functions.
- MAX/MIN function with INDEX (combo formula).
Let’s take a look at the structure of a Google Finance historical data output. For example, the formula =GOOGLEFINANCE("NSE:HDFC","VOLUME",TODAY()-15,TODAY()) fetches the trading volumes for the last 15 days of the security “HDFC”. The historical data is returned in a two-column format like this:
You can also use other attributes such as “open,” “close,” “high,” “low,” and “all” to fetch different types of data. If you use the “all” attribute, it will return a multi-column output like this:
Now let’s dive into the formulas to find the max and min values in the historical data.
Finding Max Values in GoogleFinance Two Column Historical Data
Query to Find Max Value in GoogleFinance Historical Data
The Query formula is simple and easy to read. Use the following syntax:
QUERY(data, query, [headers])In the “data” parameter, use the GoogleFinance historical data formula. You can use the “Max” aggregation function in the “query” parameter. Here’s an example formula:
=QUERY(GOOGLEFINANCE("NSE:HDFC","VOLUME",TODAY()-15,TODAY()),"SELECT Max(Col2) label Max(Col2)''",1)DMAX Database Function in GoogleFinance Historical Data (Multi-Row Data)
The GoogleFinance historical data is well structured as a database table, with field labels in the first row. This makes it eligible for using the DMAX function. In this case, the field label for the first column is “Date,” and the field label for the second column (depending on the attribute used) is “Volume.” Use the following syntax:
DMAX(database, field, criteria)Here’s an example formula:
=DMAX(GOOGLEFINANCE("NSE:HDFC","VOLUME",TODAY()-15,TODAY()),2,{"Date";">"&text("30/12/1899","dd/mm/yyyy")})MAX and INDEX Combo to Find Max in Multi-Row Data
You can also use the combination of the MAX and INDEX functions to find the max value in multi-row data. Use the INDEX formula to offset 2 columns and extract the second column, and then wrap it with the MAX function. Here’s the formula:
=max(index(GOOGLEFINANCE("NSE:HDFC","VOLUME",TODAY()-15,TODAY()),0,2))Finding Min Values in GoogleFinance Two Column Historical Data
To find the min values from Google Finance historical data, simply change the MAX functions in the previous formulas to MIN. Here are the updated formulas:
Query:
=QUERY(GOOGLEFINANCE("NSE:HDFC","VOLUME",TODAY()-15,TODAY()),"SELECT Min(Col2) label Min(Col2)''",1)DMIN:
=DMIN(GOOGLEFINANCE("NSE:HDFC","VOLUME",TODAY()-15,TODAY()),2,{"Date";">"&text("30/12/1899","dd/mm/yyyy")})MIN/Index:
=MIN(index(GOOGLEFINANCE("NSE:HDFC","VOLUME",TODAY()-15,TODAY()),0,2))Finding Max and Min Values in Multi-column Historical Data in Google Sheets
In the previous formulas, we extracted data from column 2 to find the max or min values. However, in multi-column data, you may need to extract data from a different column. Simply modify the formulas by changing the column number. Here’s an example:
Query:
=QUERY(GOOGLEFINANCE("NSE:HDFC","ALL",TODAY()-15,TODAY()),"SELECT Max(Col5) label Max(Col5)''",1)DMAX:
=DMAX(GOOGLEFINANCE("NSE:HDFC","ALL",TODAY()-15,TODAY()),5,{"Date";">"&text("30/12/1899","dd/mm/yyyy")})MIN/Index:
=MAX(index(GOOGLEFINANCE("NSE:HDFC","ALL",TODAY()-15,TODAY()),0,5))That’s it! With these formulas, you can easily find the max and min values in GoogleFinance historical data in Google Sheets. If you’re interested in learning more about Google Sheets functions, check out the Google Sheets Functions Guide.
Remember, don’t hesitate to explore and experiment with different functions to achieve the desired results. Happy spreadsheeting!
Additional Resources:
- How to Find Max Value in Each Row in Google Sheets [Array Formula].
- Vlookup to Only Return Values from Max Rows in Google Sheets.
- How to Exclude 0 From MIN Function Result in Google Sheets.
- Sum Large/Max n Values Based on Criteria in Google Sheets.
- Find Max N Values in a Row and Return Headers in Google Sheets.
