Formula Errors

How to Fix Excel STOCKHISTORY Returning a #VALUE! Error

Steve KSteve K Nov 3, 2026 871 views

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.

How to Fix Excel STOCKHISTORY Returning a #VALUE! Error
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.
Before you start

Ensure you have an active internet connection to retrieve financial data and verify that your entered dates are formatted correctly.

Solution 1Recommended

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.

1
Select the formula cell

Click on the cell containing your STOCKHISTORY formula to view it in the formula bar.

2
Check the date parameter

Locate the date argument you entered in the formula (for example, "2020-03-05").

3
Verify calendar days

Check a calendar to see if the date falls on a weekend (Saturday or Sunday) or a known stock market holiday.

4
Adjust the date

Change the date in the formula to a valid trading day to see if Excel successfully retrieves the historical stock data.

Verify the Request Date is a Trading Day
Distinguishing Errors: If you see a #VALUE! error, Excel recognizes the STOCKHISTORY function but cannot process the input. If you see a #NAME? error instead, your version of Excel does not support the function.
Free Microsoft Office alternative

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.

Fully compatible with Microsoft Excel (.xlsx) formatsRich library of built-in financial, mathematical, and logical functionsLightweight, fast, and easy to use on multiple platformsFree alternative to expensive Microsoft 365 subscriptions
QA img-9

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.