'%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 Retrieve a Stock Price for a Specific Historical Date in Excel
Question details
The user needs to retrieve stock prices for specific historical dates using an Excel formula, but encounters failures when the requested date is a non-trading day.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Using Excel's built-in stock functions to fetch historical financial data for specific calendar dates.
- Observed behavior
- The formula fails or returns an error for dates that do not match active trading days, such as weekends and market holidays, because no stock price data is available.
Verify that the specific historical date you are querying was an active market trading day, as stock exchanges are closed on weekends and official financial holidays.
Adjust the Target Date to a Valid Trading Day
Ensure the requested date is a valid market trading day, as stock prices do not exist for weekends or market holidays.
Excel's stock data features rely on official market data. If you query a date when the stock exchange was closed, the formula will return an error because no trading occurred.
Check a calendar to ensure the date you are querying does not fall on a Saturday, Sunday, or a recognized stock market holiday.
Modify your date cell using the WORKDAY function (e.g., =WORKDAY(A2, -1)) to automatically find the nearest previous trading day if your target date falls on a weekend.
Apply your STOCKHISTORY formula referencing the newly adjusted valid trading date to successfully retrieve the historical price.
Report the Data Detection Issue to Microsoft
If the date is a confirmed trading day but the data still fails to load, it may be a known issue with Excel's stock functions and should be reported.
Use WPS Office for Reliable Financial Data Management
If you are experiencing ongoing data retrieval bugs with built-in data types in Excel, consider switching to WPS Office. WPS Spreadsheets offers a robust, free alternative for managing financial datasets, analyzing imported historical stock data, and building complex financial models without relying on problematic native integrations.
- 1. Download and Install WPS Office: Get the free WPS Office suite from the official website and install it on your device.
- 2. Open Your Financial Workbook: Launch WPS Spreadsheets and open your existing .xlsx file containing your financial data.
- 3. Import Reliable Stock Data: Use the 'Data' tab to import historical stock CSV files directly from reliable financial sources like Yahoo Finance, avoiding native formula bugs.
Frequently Asked Questions
Why does the STOCKHISTORY formula return an error?
This typically happens if the requested date falls on a weekend, a market holiday, or if the stock ticker symbol is misspelled. Ensure the queried date is a valid trading day.
Can I get historical stock prices for weekends in Excel?
No, stock exchanges are closed on weekends. You must adjust your formula to fetch the closing price from the Friday before the weekend.
How do I find the nearest trading day for an Excel stock formula?
You can use the WORKDAY function in Excel to automatically adjust a weekend date to the nearest previous or next valid trading day before pulling the stock price.
What should I do if a valid trading date still shows no stock data?
This may be a bug in Excel's data detection. You can report the issue through the Excel Help menu or consult the Excel Management Microsoft Q&A forum for community support.
