Function Problems

How to Fix Excel STOCKHISTORY Errors for S&P 500 and Dow Jones Indexes

Huda Qurayshi Sep 28, 2026 869 views

Question details

Users need to retrieve historical data for the S&P 500 and Dow Jones Industrial Average using the STOCKHISTORY function in Excel, but are encountering errors despite the function working for other indexes.

How to Fix Excel STOCKHISTORY Errors for S&P 500 and Dow Jones Indexes

Product: Microsoft Excel
Device & OS: not provided
Scenario: Pulling historical stock market data for major indexes using the STOCKHISTORY function for financial analysis and tracking.
Observed behavior: The STOCKHISTORY function returns errors or incorrect results when referencing the S&P 500 and Dow Jones indexes, while successfully returning data for the Nasdaq Composite.

Before you start

Ensure you have an active Microsoft 365 subscription and a stable internet connection, as the STOCKHISTORY function requires online connectivity to fetch financial data from Refinitiv.

Solution 1Recommended

Use the Correct Financial Provider Ticker Symbols

Excel uses specific security identifiers provided by Refinitiv. Updating your formula to use the exact index symbols required by the data provider usually resolves the error.

Because stock market indexes are not traded like regular stocks, different data providers use different prefixes or symbols to identify them. If you use standard abbreviations, Excel may not recognize the index.

Locate the S&P 500 identifier

For the S&P 500, instead of typing SPX or S&P, use the exact identifier INDEXSP:.INX.

Locate the Dow Jones identifier

For the Dow Jones Industrial Average, use the exact identifier INDEXDJX:.DJI.

Update your STOCKHISTORY formula

Modify your cell formula to include the correct symbol enclosed in quotes. For example: =STOCKHISTORY("INDEXSP:.INX", "1/1/2023", "12/31/2023").

Execute the formula

Press Enter to execute the formula. The historical data should now populate correctly without errors.

Use the Correct Financial Provider Ticker Symbols

Alternative Symbols: If the primary identifiers fail, try using the standard Yahoo Finance equivalents such as ^GSPC for the S&P 500 and ^DJI for the Dow Jones, as Excel's data recognition engine occasionally updates its accepted formats.

Solution 2

Verify Microsoft 365 Subscription and Update Excel

The STOCKHISTORY function is exclusive to Microsoft 365. Verifying your subscription and updating the application can fix #NAME? or #BLOCKED! errors.

Free Microsoft Office alternative

Analyze Your Financial Data with WPS Office

While dynamic linked data types like STOCKHISTORY are specific to Microsoft 365 subscriptions, WPS Spreadsheet provides a completely free, highly compatible alternative for analyzing exported financial data, building custom investment trackers, and managing your personal finances effortlessly.

1. Download WPS Office: Visit the official WPS website and download the free WPS Office suite for your operating system.
2. Import Financial Data: Export your historical stock data as a CSV or Excel file from your preferred financial website, then open it directly in WPS Spreadsheet.
3. Analyze and Visualize: Use WPS Spreadsheet's powerful charting tools and built-in financial functions to track market trends and analyze index performance.

Completely free and lightweight spreadsheet software.Fully compatible with Microsoft Excel formats including .xlsx, .xls, and .csv.Rich set of built-in financial, logical, and statistical functions for data analysis.Familiar user interface requiring no learning curve for Microsoft Office users.

microsoft office alternative - wps office

Frequently Asked Questions

Why does STOCKHISTORY work for Nasdaq but not the S&P 500?

The Nasdaq Composite ticker (often recognized easily as ^IXIC) is more universally standardized. The S&P 500 and Dow Jones use proprietary, provider-specific prefixes (such as INDEXSP: and INDEXDJX:) in Excel's Refinitiv database, which causes errors if standard abbreviations are used.

What does the #BLOCKED! error mean in STOCKHISTORY?

The #BLOCKED! error typically occurs when Excel's connected experiences are disabled in your privacy settings, or if you are not signed into a Microsoft account with an active Microsoft 365 subscription that permits downloading online data.

Can I use STOCKHISTORY in Excel 2019 or Excel 2021?

No, the STOCKHISTORY function relies on live online data services and is exclusively available to Microsoft 365 subscribers. Standalone perpetual licenses like Excel 2019 and Excel 2021 do not support this function.

Excel & Data Problems