How to Fix Excel STOCKHISTORY Returning a #VALUE! Error
Question details
The user encounters a #VALUE! error when attempting to use the STOCKHISTORY function in Excel and wants to know the cause and solution.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Using the STOCKHISTORY function to retrieve historical stock prices for specific dates.
- Observed behavior
- Excel returns a #VALUE! error instead of the stock price, which usually happens when requesting data for weekends or market holidays.
Ensure you have an active internet connection to retrieve financial data and verify that your entered dates are formatted correctly.
Verify the Request Date is a Trading Day
The most common reason for a #VALUE! error in STOCKHISTORY is querying a date when the stock market is closed.
Stock markets are closed on weekends and specific holidays. If you request daily stock data for a Sunday, for example, Excel cannot retrieve a price and returns a #VALUE! error. The #VALUE! error actually confirms that Excel recognizes the function, but the data does not exist for the provided parameters.
Click on the cell containing your STOCKHISTORY formula to view it in the formula bar.
Locate the date argument you entered in the formula (for example, "2020-03-05").
Check a calendar to see if the date falls on a weekend (Saturday or Sunday) or a known stock market holiday.
Change the date in the formula to a valid trading day to see if Excel successfully retrieves the historical stock data.

Check the Ticker Symbol Validity
An incorrect or unlisted ticker symbol can also cause the STOCKHISTORY formula to fail and return an error.
Edit Spreadsheets with WPS Office
If you're dealing with spreadsheet errors or looking for a lightweight, cost-effective solution, WPS Office provides excellent compatibility with Microsoft Excel formats. It offers a comprehensive set of formulas and data analysis tools completely free of charge.

Frequently Asked Questions
Why do I get a #NAME? error instead of #VALUE! with STOCKHISTORY?
A #NAME? error indicates that Excel does not recognize the function at all. This usually means you are using a version of Excel that does not support STOCKHISTORY, such as a non-subscription version (like Office 2019) or an update channel that hasn't received the feature yet.
How do I format the date correctly for STOCKHISTORY?
You can input the date directly as a text string enclosed in quotation marks (e.g., "2020-03-05"), or use a cell reference that contains a valid date format. Using the DATE function, like DATE(2020,3,5), is highly recommended to prevent regional formatting errors.
Does STOCKHISTORY update automatically?
Yes, if you use parameters that reference the current date (such as utilizing the TODAY() function), the data will update when you refresh the workbook. However, historical data for static past dates remains unchanged.




