Function Problems

How to Sum Stock Prices Using STOCKHISTORY Without Displaying Data

Aamir Naveed AkramAamir Naveed Akram Nov 3, 2026 870 views

Question details

The user needs to aggregate historical stock prices using functions like SUM, AVERAGE, MIN, or MAX in combination with the STOCKHISTORY function, without outputting the entire historical data array onto the spreadsheet.

How to Sum Stock Prices Using STOCKHISTORY Without Displaying Data
Product
Excel
Device & OS
not provided
Scenario
Aggregating dynamic stock market data directly within a single cell formula.
Observed behavior
By default, STOCKHISTORY returns an array that includes dates. Wrapping this default result in a SUM function causes Excel to sum the date serial numbers along with the prices, resulting in incorrect calculations.
Before you start

Ensure you have an active internet connection to retrieve live stock data and that your spreadsheet software supports dynamic array functions.

Solution 1Recommended

Specify the Property Argument in STOCKHISTORY

Directly nest the STOCKHISTORY function inside your aggregation function by defining the exact property (e.g., High, Low, Close) to exclude dates from the array.

By default, STOCKHISTORY returns dates along with prices. If you wrap this default array in a SUM function, it will add the date serial numbers, leading to incorrect results.

To fix this, you must customize the STOCKHISTORY arguments to hide headers and specify the exact data property (like High or Close) to return a single-column array of prices only.

1
Select the target cell

Click on the cell where you want the final aggregated value (e.g., the total sum) to appear.

2
Enter the nested formula

Type the formula `=SUM(STOCKHISTORY(A2,TODAY()-366,TODAY(),,0,3))` into the formula bar, replacing 'A2' with the cell containing your stock ticker symbol.

3
Customize the properties

In this formula, the '0' specifies no headers, and the '3' represents the 'High' price property. You can change '3' to '5' if you want to sum the 'Volume' instead.

4
Execute the formula

Press Enter. The cell will now display the total sum dynamically without spilling the historical data across multiple cells.

Specify the Property Argument in STOCKHISTORY
Works with other functions: Dynamic arrays can be passed directly to other statistical functions. You can safely replace SUM with AVERAGE, MIN, MAX, or AVEDEV using this exact same method.
Free Microsoft Office alternative

Experience Powerful Data Analysis with WPS Office

While specialized connected-data functions like STOCKHISTORY rely on Microsoft 365's proprietary data providers, WPS Office provides a comprehensive, lightweight, and completely free alternative for your advanced data analysis, financial modeling, and dynamic array needs.

  1. 1. Download and Install: Get WPS Office from the official website and complete the quick installation.
  2. 2. Open Your Workbooks: Launch WPS Spreadsheet and seamlessly open your existing .xlsx files without losing formatting.
  3. 3. Analyze Data: Utilize powerful built-in formulas and charts to process your financial data efficiently.
Fully compatible with Microsoft Excel (.xlsx) formats and formulasSupports extensive financial, statistical, and dynamic array functionsLightweight architecture ensures fast performance even with large datasetsFree to use with a familiar, user-friendly interface
microsoft office alternative - wps office

Frequently Asked Questions

Why does SUM(STOCKHISTORY) return a huge, incorrect number?

By default, STOCKHISTORY includes dates in its output array. Since spreadsheet software stores dates as large serial numbers (e.g., representing days since the year 1900), summing the default output adds these date serial numbers to your stock prices, resulting in an inflated and incorrect total.

What do the property numbers in the STOCKHISTORY function mean?

The property argument dictates what specific stock data is returned by the function: 0 represents Date, 1 is Close, 2 is Open, 3 is High, 4 is Low, and 5 is Volume. You can specify these numbers to filter the array.

Can I use STOCKHISTORY with functions other than SUM?

Yes. The dynamic arrays generated by STOCKHISTORY can be passed directly into other aggregation functions like AVERAGE, MIN, MAX, and AVEDEV without displaying the underlying data, provided you filter out the date column.