Forum Discussion
Excel
I use formula =STOCKHISTORY(K6,$G$3,,0,0,1) and I get #VALUE, even though it has worked previously and the number is in the "Price" and "Previous Close" for that stock symbol.
3 Replies
- Can you share the screenshot of the stock name, start date or a glimpse of your worksheet ?
The #VALUE! error in Excel usually occurs when a function or formula is trying to perform an operation that is not valid for the given data. In the case of the STOCKHISTORY function, this error can be caused by a few different issues. Here are some things to check to resolve the #VALUE! error:
- Valid Stock Symbol: Make sure that the stock symbol in cell K6 is valid and exists in the data source you're using with the STOCKHISTORY function.
- Data Source: Verify that the data source specified in cell G3 is correctly set up and contains the historical stock data you need. If the data source is invalid or unavailable, the function will return an error.
- Date Parameters: Check the date parameters you are using in the function. Ensure that the start and end dates are valid and in the correct format. Dates must be entered as valid Excel date serial numbers or text representations of dates.
- Data Availability: Ensure that historical stock data is available for the specified stock symbol and dates. Sometimes, data sources may not have data for specific stocks or time periods.
- Data Type: Check if the data types for the "Price" and "Previous Close" columns are compatible with the function. The data should be in a numerical format for the function to work.
- Commas and Decimal Points: Make sure your Excel is set up with the correct regional settings for commas and decimal points. In some regions, you may need to use semicolons (;) instead of commas (,) as function argument separators.
- Quotes and Parentheses: Ensure that your formula does not contain extra or mismatched quotation marks or parentheses, which can lead to errors.
- Refresh Data: If you are using an external data source, try refreshing the data to make sure you have the latest information.
- Data Cache: In some cases, Excel may have cached data, and clearing the cache might help. You can do this by going to the "Data" tab and selecting "Refresh All."
- Function Syntax: Double-check that the function syntax is correct, and there are no typos in the formula.
If you've checked all these factors and are still encountering the #VALUE! error, it may be worth verifying that the function is compatible with the specific data source you are using. Some data sources may have limitations or requirements that affect the function's behavior.
If the issue persists, please provide more details about the specific data source and your formula so that I can offer more targeted assistance.
My knowledge of this topic is limited, but since no one has answered it for over a day and more, I entered your question in various AI. The text and the steps are the result of various AI's put together.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and like it!
This will help all forum participants.
To save space, I did not mention that there are over 30 other symbols in that column that printed the proper number. The previous 8 - 10 weeks this symbol printed the proper number in that particular row. I have tried deleting the formula and rewriting it, to no avail.
I worked with a Microsoft Tech on a different problem and I was told to ask "the Community".
