Fix Excel STOCKHISTORY #VALUE! Non-Trading Day Error for XNAS Stocks
Question details
Users need to resolve a #VALUE! error in Excel's STOCKHISTORY function that inaccurately labels valid dates as non-trading days for NASDAQ stocks.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Retrieving historical price data for NASDAQ-listed symbols (like AMZN, META, MSFT, and AAPL) using the STOCKHISTORY formula.
- Observed behavior
- The STOCKHISTORY formula returns a #VALUE! error, identifying valid trading dates as non-trading days, even though the standard Excel Data Types feature still returns current stock prices.
Ensure your internet connection is stable and verify that your Microsoft 365 subscription is active, as STOCKHISTORY relies on premium cloud services to fetch market data.
Use the Excel Stocks Data Type for Current Prices
Since the STOCKHISTORY function is experiencing backend data source issues with XNAS, you can use the built-in Stocks Data Type to fetch the current price and basic details as a temporary workaround.
This data source glitch specifically affects historical data retrieval via STOCKHISTORY. The standard Stocks data type uses a different data feed for current metrics, which remains unaffected.
Type the NASDAQ stock symbol (e.g., AAPL or MSFT) into a blank cell in your spreadsheet.
Select the cell, navigate to the Data tab on the ribbon, and click on Stocks in the Data Types group.
Click the small 'Insert Data' icon that appears next to the cell and select 'Price' (or any other required metric) to extract the current trading data into adjacent cells.
Report the Market-Data Issue to Microsoft
The error stems from an issue with Microsoft's market-data provider misclassifying XNAS trading days. Reporting it directly helps prioritize a backend fix.
Experience Error-Free Spreadsheets with WPS Office
Tired of encountering subscription-based data errors in Microsoft Excel? Switch to WPS Office. It provides a free, lightweight, and highly compatible alternative with robust features, an intuitive interface, and seamless migration for all your spreadsheet tasks.
- 1. Download WPS Office: Visit the official WPS website to download the free installation package for your device.
- 2. Install the Software: Run the installer and follow the quick on-screen instructions to set up the suite.
- 3. Open Your Spreadsheets: Launch WPS Spreadsheets and instantly open your existing Excel workbooks without losing any formatting.
Frequently Asked Questions
Why does STOCKHISTORY show a #VALUE! error for a regular trading day?
This occurs when Excel's third-party market-data provider supplies incorrect trading calendar data to the system, mistakenly identifying a regular business day as a market holiday or non-trading day.
Does this data error affect all global stock exchanges in Excel?
No. Based on user reports, this specific error is predominantly isolated to the XNAS (NASDAQ) data source. Other exchanges, such as European markets, typically continue to function normally during these outages.
Can I still get the current price of XNAS stocks when STOCKHISTORY is broken?
Yes, you can use the built-in Stocks Data Type found in the Data tab. Even when historical data retrieval fails due to a STOCKHISTORY glitch, the real-time or delayed current price data usually remains accessible.
Will clearing my cache fix the STOCKHISTORY non-trading day error?
Unfortunately, no. Because the data originates from Microsoft's remote servers and their market-data providers, local troubleshooting steps like clearing cache or repairing Office will not resolve a backend data feed issue.
