MarketXLS

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

Syntax

=Open_Historical(Symbol, OnDate)
Excel Desktop (Windows)

Parameters

symbolstringRequired

Stock ticker symbol

onDatedateRequired

The date for which to retrieve the opening price

Returns

number

Opening price on the specified date

Examples

=Open_Historical("AAPL",DATE(2024,1,15))
Apple open on Jan 15, 2024
=Open_Historical("MSFT",DATE(2024,1,2))
Microsoft open on Jan 2, 2024
=Open_Historical("SPY",DATE(2023,12,29))
SPY open on Dec 29, 2023
=Open_Historical("AAPL",A1)
Date from cell reference
=Open_Historical(B1,A1)
Symbol and date from cells

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

Scenario Use Instead
Need current day's open Open()
Need closing price Close_Historical()
Need adjusted price Adjusted_Close_Historical()
Need high/low High_Historical(), Low_Historical()

Common Issues & FAQ

Why am I getting "NA"?

The market was likely closed on that date (weekend, holiday) or the date is before the stock was listed.

How do I format the date?

Use Excel's DATE function: =Open_Historical("AAPL",DATE(2024,1,15)) for January 15, 2024.

Why is the price different from what I see elsewhere?

Prices may be unadjusted. For split-adjusted prices, use Adjusted_Close_Historical() for close prices.

Excel Templates Using Historical Open Price

These ready-made MarketXLS templates call Open_Historical() in their worksheet formulas. Open one to see the function working inside a complete model.

Related Formulas

More MarketXLS Price History formulas you can use in the same worksheet:

See Open_Historical used in a complete workbook: Excel Stock Price on a Specific Date: How to Get Historical Prices With Formulas

Get Access to 1 Billion Usable Market data points IN YOUR EXCEL SHEETS WITH EASY TO USE EXCEL FUNCTIONS

Get started today
MarketXLS Excel Add-in Tutorial - How to Use Historical Open Price and Other Financial Formulas
▶
How does MarketXLS work?

Related Functions