Fix Excel STOCKHISTORY Returning Market Holidays for CBOE VIX
Question details
The user needs a resolution for the STOCKHISTORY function retrieving incorrect historical data entries on known market holidays.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Retrieving historical financial data for the CBOE:VIX ticker using the STOCKHISTORY function in Excel.
- Observed behavior
- The function returns closing-price entries for market holidays (such as December 26, 2022, and January 2, 2023) instead of skipping them.
Verify your Excel version and ensure your internet connection is stable, as the STOCKHISTORY function relies on a live connection to Microsoft's third-party market data providers.
Report the Data Feed Issue to Microsoft Support
Because this issue originates from the backend market data feed provided to Microsoft for specific tickers, escalating the issue to Microsoft 365 support is the most effective way to get the data source corrected.
The STOCKHISTORY function retrieves its data from third-party financial exchanges. When holiday schedules for specific instruments like CBOE:VIX are improperly mapped by the provider, Excel displays the erroneous data. Microsoft must investigate the feed directly.
Write down the exact STOCKHISTORY formula you are using, the specific dates returning incorrect data, and the ticker symbol (CBOE:VIX).
If you are using a Microsoft 365 Business account, navigate to the Microsoft 365 Admin Center or ask your IT administrator to do so.
Go to the Support or Help section and submit a new service request, including all the documented details so the engineering team can investigate the market-data feed.
Filter Out Holidays Manually Using Helper Formulas
As a temporary workaround while waiting for a backend fix, you can use Excel's dynamic array functions to manually exclude known market holidays from your data.
Experience Seamless Data Management with WPS Office
If you frequently encounter data fetch limitations or backend formula issues in Microsoft Excel, consider exploring WPS Office. It provides a highly compatible, lightweight, and completely free alternative for your complex spreadsheet and data analysis needs.
- 1. Download WPS Office: Visit the official WPS Office website and download the free installation package for your operating system.
- 2. Install and Launch: Run the installer and follow the quick setup instructions. Once installed, open WPS Spreadsheets.
- 3. Open Your Workbooks: Click 'Open' and select your existing Excel workbooks to continue your data analysis without losing any formatting.
Frequently Asked Questions
Why does STOCKHISTORY show data for weekends or holidays?
STOCKHISTORY relies entirely on third-party market data providers. If the provider's database incorrectly includes a closing price for a non-trading day on a specific exchange or index, Excel will retrieve and display that data.
Can I automatically exclude weekends when using STOCKHISTORY?
By default, STOCKHISTORY retrieves whatever dates the provider sends. However, you can combine the WORKDAY function or use the FILTER function to dynamically remove non-working days from the final output array.
Are specific tickers like CBOE:VIX handled differently?
Yes. Different indexes and instruments are often routed through distinct data feeds or exchanges by Microsoft's data partners. This can occasionally result in calendar discrepancies specific to a single ticker, while standard stock tickers work correctly.
