'%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 #VALUE Error for CRH Stock
Question details
The user is experiencing a #VALUE error when using the STOCKHISTORY formula in Excel for CRH stock, with historical data incorrectly stopping at a specific past date.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Attempting to retrieve historical stock data for CRH using the STOCKHISTORY function for financial tracking and analysis.
- Observed behavior
- The formula returns a #VALUE error. Start dates after September 22, 2023, are incorrectly reported as non-trading days, even though current attributes like Price continue to work properly.
Verify that you have an active Microsoft 365 subscription and a stable internet connection, as the STOCKHISTORY function relies entirely on real-time cloud data from third-party financial providers.
Use an Exchange-Qualified Ticker Symbol
Ensure Excel pulls data from the correct stock exchange to avoid instrument mapping conflicts.
Sometimes, a simple ticker symbol like 'CRH' can be ambiguous to Excel's backend data provider, causing it to pull from an incomplete or halted data feed. Specifying the exact stock exchange resolves this mapping issue.
Find the specific exchange identifier for CRH based on where you are tracking it (e.g., XNYS for the New York Stock Exchange).
Modify your STOCKHISTORY formula to include the prefix and a colon before the ticker symbol, such as =STOCKHISTORY("XNYS:CRH", "1/1/2023", "12/31/2023").
Press Enter to apply the formula and allow Excel a few moments to fetch the updated data from the designated exchange.
Report the Data Source Issue to Microsoft
Since this error frequently stems from Microsoft's internal data provider halting historical tracking, submitting a report is necessary for a permanent fix.
Try WPS Office for Your Spreadsheet and Financial Data Needs
If you are frustrated by ongoing Microsoft Excel data provider bugs and formula errors, consider switching to WPS Office. It provides a lightweight, highly compatible spreadsheet environment that handles massive data sets and complex financial calculations smoothly.
- 1. Download WPS Office: Visit the official WPS website and download the free office suite for your operating system.
- 2. Open your Excel workbook: Launch WPS Spreadsheets and open your existing .xlsx files directly without any format conversion.
- 3. Continue your analysis: Utilize the extensive financial formula library in WPS to resume tracking and analyzing your stock data seamlessly.
Frequently Asked Questions
Why does the STOCKHISTORY formula return a #VALUE error?
A #VALUE error in STOCKHISTORY usually means Excel's data provider is missing historical data for the requested date range, the data type has failed to refresh, or the stock ticker is ambiguous and mapping to an incorrect financial instrument.
Can I manually fix the missing historical data for CRH stock?
No, the historical data is pulled directly from Microsoft's third-party financial data providers. If the provider's database is incomplete or halted for a specific stock like CRH, users must wait for Microsoft to update and fix the data feed.
How do I ensure Excel tracks the correct stock ticker for my formulas?
Always use an exchange prefix along with the ticker symbol in your formula. For example, instead of just entering 'CRH', use 'XNYS:CRH' to specify the New York Stock Exchange. This removes ambiguity and forces Excel to pull the correct data.
