'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
Fix Excel 365 Not Returning Dow Jones Index Quotes
Question details
The user is unable to retrieve data for certain market indexes like the Dow Jones or S&P 500 using Excel 365's Stocks data type and STOCKHISTORY function.
- Product
- Microsoft Excel 365
- Device & OS
- not provided
- Scenario
- Attempting to pull historical or real-time stock market index data into an Excel spreadsheet.
- Observed behavior
- The STOCKHISTORY function or Stocks data type fails to return data, displaying errors because specific index symbols are unsupported or unavailable in the linked service.
Ensure you have an active internet connection and that you are signed into your Microsoft 365 account, as premium linked data types require a valid subscription and online access to function.
Verify Ticker Symbols in the Data Selector Pane
Check if the specific index you are looking for is supported by Excel's linked financial data provider by searching for its full name.
Excel relies on external data providers for financial information. Some common ticker symbols for major indexes might not map automatically, requiring you to manually link the cell to the correct data source using the Data Selector.
Click on the cell containing the index name or ticker symbol (e.g., DJI or Dow Jones) that you want to query.
Navigate to the 'Data' tab on the Excel ribbon and click on 'Stocks' within the Data Types group.
If Excel cannot find an exact match, a question mark icon will appear next to the cell text. Click on this icon to open the Data Selector pane on the right side of your screen.
In the Data Selector search bar, type the official name of the index (e.g., 'Dow Jones Industrial Average') instead of the abbreviation. Select the correct matching result to link the data.
Use Power Query to Import External Financial Data
If the index is entirely unsupported by Excel natively, use Power Query to pull historical data from third-party financial websites.
Use WPS Spreadsheet for Smooth Financial Data Analysis
While specific linked data types like STOCKHISTORY are proprietary to Microsoft 365, WPS Office provides a powerful, free, and lightweight alternative for all your data import, calculation, and analysis needs. You can easily import external CSV financial data and analyze market trends without expensive subscriptions.
- 1. Download and install: Download WPS Office for free from the official website and follow the quick installation process.
- 2. Import financial data: Open WPS Spreadsheet, navigate to the Data tab, and import your market data via CSV or text files.
- 3. Analyze and visualize: Use built-in formulas and professional charts to track and analyze your imported index data seamlessly.
Frequently Asked Questions
Why does STOCKHISTORY show a #BLOCKED! error for certain indexes?
This error generally occurs when the data provider restricts certain financial data for your region or account type, or if you do not have the required Microsoft 365 subscription level needed to access premium data types.
Can I use the ticker symbol 'DJI' for the Dow Jones in Excel?
The standard 'DJI' ticker might not always be recognized depending on the current data provider for Excel. It is recommended to search for 'Dow Jones Industrial Average' in the Data Selector pane to map the cell to the officially supported symbol.
How often is the Stocks data type updated in Excel?
Excel's stock data is updated frequently but is not strictly real-time. Quotes typically have a delay of about 15 minutes, depending on the specific exchange and the external data provider.
