Historical Open Price
Returns the opening price for a stock on a specific historical date. The opening price is the first traded price when the market opens.
Parameters
| Parameter | Type | Required | Description |
|---|---|---|---|
| Symbol | string | Yes | Stock ticker symbol |
| OnDate | date | Yes | The historical date |
Date Format
The date can be provided as:
- Excel DATE function:
DATE(2024,1,15) - Cell reference containing a date
- Date serial number
Supported Symbol Formats
| Type | Format | Example |
|---|---|---|
| US Stocks | SYMBOL | AAPL, MSFT |
| ETFs | SYMBOL | SPY, QQQ |
| Indices | ^SYMBOL | ^SPX, ^DJI |
Notes
- Returns "NA" if the market was closed on that date (weekend, holiday)
- Data availability depends on the symbol's listing history
Examples
=Open_Historical("AAPL",DATE(2024,1,15))=Open_Historical("MSFT",DATE(2024,1,2))=Open_Historical("SPY",DATE(2023,12,29))=Open_Historical("AAPL",A1)=Open_Historical(B1,A1)When to Use
- Analyzing historical price gaps (open vs previous close)
- Backtesting trading strategies
- Building historical price charts
- Calculating opening gap percentages
When NOT to Use
Common Issues & FAQ
Q: Why am I getting "NA"? A: The market was likely closed on that date (weekend, holiday) or the date is before the stock was listed.
Q: How do I format the date?
A: Use Excel's DATE function: =Open_Historical("AAPL",DATE(2024,1,15)) for January 15, 2024.
Q: Why is the price different from what I see elsewhere?
A: Prices may be unadjusted. For split-adjusted prices, use Adjusted_Close_Historical() for close prices.
Get Access to 1 Billion Usable Market data points IN YOUR EXCEL SHEETS WITH EASY TO USE EXCEL FUNCTIONS
Get started today
▶
How does MarketXLS work?