How to Sum Stock Prices Using STOCKHISTORY Without Displaying Data
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.

- 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.
Ensure you have an active internet connection to retrieve live stock data and that your spreadsheet software supports dynamic array functions.
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.
Click on the cell where you want the final aggregated value (e.g., the total sum) to appear.
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.
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.
Press Enter. The cell will now display the total sum dynamically without spilling the historical data across multiple cells.

Use INDEX to Extract Specific Values
If you are passing STOCKHISTORY data into functions like TREND or LINEST, use INDEX to isolate and return a specific row or column without displaying the whole array.
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. Download and Install: Get WPS Office from the official website and complete the quick installation.
- 2. Open Your Workbooks: Launch WPS Spreadsheet and seamlessly open your existing .xlsx files without losing formatting.
- 3. Analyze Data: Utilize powerful built-in formulas and charts to process your financial data efficiently.

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.




