How to Return a Stock Opening or Closing Price for a Specific Date
Question details
The user needs to retrieve the opening or closing price of a stock on or around a specific historical date, taking into account non-trading days like weekends and market holidays.
- Product
- Spreadsheets
- Device & OS
- not provided
- Scenario
- Extracting historical stock market data from a specific past date (e.g., exactly 5 years ago) for financial analysis and portfolio tracking.
- Observed behavior
- Querying a single exact past date often results in errors or missing data because the target date might fall on a weekend or a market holiday when the stock exchange was closed.
Ensure you have an active internet connection, as stock data functions require online access to retrieve historical market prices from financial databases.
Use the STOCKHISTORY Function with a Date Buffer
Create a date range around your target date to ensure you capture the most recent trading day's closing or opening price, preventing weekend and holiday data errors.
When querying historical stock prices, specifying a single exact date may fail if that day was a weekend or a market holiday. To prevent this, you can define a date range (e.g., 10 days prior) and extract the last available trading day within that period using a combination of the STOCKHISTORY and TAKE functions.
Click on the cell in your spreadsheet where you want the historical stock price to appear.
Type the formula: =TAKE(STOCKHISTORY("MSFT", EDATE(TODAY(),-60)-10, EDATE(TODAY(),-60), 0, 0, 1), -1). This example looks up Microsoft ("MSFT") data from 5 years ago.
The EDATE(TODAY(), -60) function calculates the date exactly 60 months (5 years) ago. Subtracting 10 creates a 10-day buffer to ensure at least one valid trading day is included in the range.
The final parameter '1' in the STOCKHISTORY formula returns the closing price. If you need the opening price instead, change that '1' to '2'. Press Enter to execute the formula.
Use WPS Spreadsheet for Seamless Financial Data Analysis
WPS Office provides powerful built-in functions, dynamic arrays, and excellent compatibility for managing financial portfolios and analyzing historical stock data effortlessly.
- 1. Install WPS Office: Download and install WPS Office from the official website to access the powerful Spreadsheet tool.
- 2. Create a financial workbook: Open WPS Spreadsheet and create a new blank document to start building your stock portfolio tracker.
- 3. Apply financial formulas: Input your stock ticker symbols and use built-in functions to pull, filter, and analyze historical market data.
- 4. Format your data: Select your price columns and apply 'Currency' or 'Accounting' number formats from the Home tab for clear financial reporting.
Frequently Asked Questions
Why does my STOCKHISTORY formula return a #VALUE! or #BLOCKED! error?
A #VALUE! error usually occurs if the specified date is in the future or formatted incorrectly. A #BLOCKED! error generally means your account does not have access to the required connected data services, or your device is currently offline.
How do I get both the opening and closing prices at the same time?
You can adjust the properties arguments at the end of the STOCKHISTORY function. Adding multiple property numbers, such as 0, 1, 2, will instruct the function to return the Date, Close, and Open prices together in adjacent columns.
Can I use STOCKHISTORY to look up international stock exchanges?
Yes. To ensure you retrieve data from the correct exchange, you can prepend the exchange abbreviation to the ticker symbol. For example, use "XNAS:MSFT" for the NASDAQ or "XLON:VOD" for the London Stock Exchange.
