How to Use STOCKHISTORY to Retrieve the CBOE Volatility Index (VIX) in Excel
Question details
The user needs to retrieve historical data for the CBOE Volatility Index (VIX) using the STOCKHISTORY function.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Tracking financial and stock market volatility using Excel's built-in data types and functions.
- Observed behavior
- Needs the exact ticker symbol and formula structure to successfully pull VIX data without errors.
Ensure you have an active Microsoft 365 subscription and a stable internet connection, as the STOCKHISTORY function relies on cloud-connected financial data providers.
Use the CBOE:VIX Symbol in the STOCKHISTORY Formula
The most reliable method to retrieve the Volatility Index is by specifying the exchange and ticker explicitly in your formula.
Because financial data providers track thousands of indexes globally, specifying the exact exchange (CBOE) alongside the ticker (VIX) ensures Excel retrieves the correct historical data without ambiguity.
Open your Excel workbook and click on the cell where you want the top-left corner of your historical data array to begin.
Type the formula `=STOCKHISTORY("CBOE:VIX", "2023-01-01", "2023-01-31")`. Replace the date strings with your desired start and end dates, or use cell references containing those dates.
Press Enter. Excel will connect to the financial data provider and the historical data array will automatically spill into the adjacent cells.
Use the Generic VIX Symbol
In some regions, entering just the VIX ticker symbol directly into the function may work if Excel automatically resolves it to the S&P 500 volatility index.
Try WPS Office for Your Financial Spreadsheets
While live connected data types like STOCKHISTORY are specific to Microsoft 365, WPS Office provides a powerful, free, and lightweight alternative for comprehensive financial modeling. Enjoy seamless compatibility with Microsoft Excel formats and a highly familiar user interface without the subscription fees.
- 1. Download WPS Office: Visit the official WPS website and download the free WPS Office suite.
- 2. Open your workbooks: Launch WPS Spreadsheet and open your existing .xlsx financial tracking files.
- 3. Analyze your data: Use built-in financial formulas and charts to continue analyzing your market data without any format loss.
Frequently Asked Questions
Why am I getting a #BLOCKED! or #UNKNOWN! error with STOCKHISTORY?
This usually happens if you are not signed into a Microsoft 365 account, do not have an active internet connection, or if the financial data provider temporarily doesn't support the specific ticker symbol in your region.
Do I need quotation marks around the ticker symbol in the STOCKHISTORY formula?
Yes, if you are typing the symbol directly into the formula (e.g., "CBOE:VIX" or "VIX"), it must be enclosed in quotation marks. If you are referencing a cell that contains the text symbol, quotation marks are not needed.
Are other international stock volatility indexes available?
Availability depends on the connected financial-data provider. While major US indexes like VIX and VXN are widely supported, availability for regional indexes (like the Oslo Stock Exchange Index) may vary or lack complete historical records.
