How to Exclude Incorrect Dates from Excel STOCKHISTORY Results
Question details
The user wants to remove specific unwanted dates, such as market holidays, that are unexpectedly appearing in the data returned by the Excel STOCKHISTORY function.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Retrieving historical stock market data using the STOCKHISTORY function and finding unwanted specific dates in the returned array.
- Observed behavior
- The STOCKHISTORY function returns data for specific dates that need to be excluded, but there is no built-in parameter within the function to filter out individual dates.
Ensure you are using a version of Microsoft Excel that supports dynamic array functions, as both STOCKHISTORY and FILTER require this functionality. Additionally, verify the exact date value you wish to exclude to ensure your formula targets it accurately.
Wrap the STOCKHISTORY Function with FILTER
Since STOCKHISTORY does not have a native feature to exclude dates, you can use the FILTER function to dynamically remove unwanted dates from the returned array.
The STOCKHISTORY function retrieves historical data for a financial instrument, but it lacks an argument to skip specific dates. By wrapping the entire STOCKHISTORY formula inside a FILTER function, you can instruct Excel to output only the rows where the date does not match the one you want to exclude.
Click on the cell containing your existing STOCKHISTORY formula.
Click inside the formula bar at the top of the Excel window to modify the formula.
Wrap your existing formula with the FILTER function. Use the INDEX function to isolate the date column, and set the condition to exclude your specific date. For example: =FILTER(STOCKHISTORY("MSFT","2023-12-01","2023-12-31"), INDEX(STOCKHISTORY("MSFT","2023-12-01","2023-12-31"), 0, 1)<>DATE(2023,12,25)).
Press the Enter key. The array will dynamically update, and the specified date will be removed from your stock history results.

Analyze Financial Data Smoothly with WPS Office
If you are managing complex financial datasets and looking for a cost-effective spreadsheet solution, WPS Office provides a powerful, free alternative. Enjoy robust financial formulas, dynamic arrays, and seamless compatibility with Microsoft Excel files without the hefty subscription fees.
- 1. Download WPS Office: Download and install WPS Office for free from the official website.
- 2. Open your Excel file: Launch WPS Spreadsheet and open your existing Excel workbook (.xlsx) directly.
- 3. Analyze data seamlessly: Continue analyzing your financial data using standard spreadsheet functions and dynamic arrays without losing any formatting.

Frequently Asked Questions
Why does STOCKHISTORY return unexpected dates like holidays?
Depending on the exchange and the ticker symbol, STOCKHISTORY might return data points for days when the market was closed. It often carries over the previous day's closing price or shows zero trading volume for that date.
Can I exclude multiple dates using the FILTER method?
Yes. You can exclude multiple dates by nesting multiple conditions within the FILTER function. Use the asterisk (*) between the conditions to represent an AND statement, ensuring all specified dates are excluded.
Does STOCKHISTORY automatically adjust for weekends?
Generally, STOCKHISTORY only returns data for active trading days, which typically excludes weekends automatically. However, specific international exchanges or unexpected market closures might cause variations in the returned date array.




