'%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)
How to Fix Excel STOCKHISTORY Errors (#CALC!, #CONNECT!)
Question details
Users are experiencing errors or receiving incorrect stock symbol results when utilizing the STOCKHISTORY function in large workbooks.
- Product
- Microsoft Excel
- Device & OS
- Mac
- Scenario
- Pulling historical stock data and exchange rates using the dynamic STOCKHISTORY function in financial workbooks.
- Observed behavior
- The function returns various error codes such as #CALC!, #CONNECT!, #SPILL!, #N/A, and #UNKNOWN!, or intermittently fetches quotes for the wrong stock symbol.
Ensure you have a stable internet connection and that you are logged into a Microsoft 365 account, as the STOCKHISTORY function relies on live cloud services to fetch financial data.
Format the Ticker Input Cell as a Stock Data Type
Formatting the cell containing the stock symbol as a Stock data type allows Excel to correctly identify the specific company and financial exchange, resolving #CALC! and #UNKNOWN! errors.
Often, errors occur because the ticker symbol is supplied through a variable or standard text cell, causing Excel to misinterpret the exchange or company. Explicitly assigning the Stock data type forces Excel to validate the symbol.
Click on the cell that contains your stock ticker symbol (e.g., AAPL).
Go to the 'Data' tab on the Excel ribbon menu.
In the 'Data Types' group, click on 'Stocks'. Wait a moment for Excel to link the text to the financial data source. A small bank or building icon will appear next to your symbol.
Clear Space for Dynamic Array Results (#SPILL! Error)
The STOCKHISTORY function returns multiple values in an array. If there is data blocking the output range, Excel will return a #SPILL! error.
Verify Service Status and Submit Feedback
Because STOCKHISTORY relies on an external Microsoft financial data provider, intermittent #CONNECT! errors may indicate a server-side outage rather than an issue with your workbook.
Try WPS Office for Reliable Spreadsheet Management
If frequent cloud service disruptions and dynamic array errors in Microsoft Excel are slowing down your workflow, consider switching to WPS Office. It offers a powerful, lightweight, and completely free spreadsheet application that supports all your data analysis needs without the hassle.
- 1. Download and Install: Visit the official WPS website to download the free suite for your device.
- 2. Open your Excel files: Launch WPS Spreadsheet and open your existing .xlsx financial workbooks seamlessly.
- 3. Analyze your data: Use built-in functions, charts, and pivot tables to continue your data analysis without interruption.
Frequently Asked Questions
Why does the STOCKHISTORY function return a #CONNECT! error?
The #CONNECT! error occurs when Excel cannot establish a connection to the internet or when Microsoft's external financial data provider is experiencing a temporary outage. Check your internet connection and try refreshing the sheet later.
What causes the #CALC! error when using STOCKHISTORY?
A #CALC! error usually means the function's calculation engine encountered an issue. For STOCKHISTORY, this often happens if the specified stock exchange does not carry the requested ticker, or if the date range provided in the formula includes non-trading days where no data exists.
How do I fix the #UNKNOWN! error for a stock symbol?
The #UNKNOWN! error indicates Excel does not recognize the ticker symbol you entered. You can fix this by selecting the cell with the ticker, navigating to the Data tab, and clicking 'Stocks' to format it as a linked Stock data type, which helps Excel accurately identify the company.
Is STOCKHISTORY available in older versions of Excel?
No, STOCKHISTORY is a dynamic array function exclusive to Microsoft 365 subscribers and Excel for the Web. If you open a workbook containing this formula in a standalone version of Excel (like Excel 2019 or 2016), it will return a #NAME? error.
