Historical Close Price
Returns the closing price for a stock on a specific historical date. This is the final traded price when the market closes for that day.
Parameters
| Parameter | Type | Required | Description |
|---|---|---|---|
| Symbol | string | Yes | Stock ticker symbol |
| OnDate | date | Yes | The historical date |
| IncludeHolidays | string | No | "Yes" to get last available price for holidays |
Date Format
The date can be provided as:
- Excel DATE function:
DATE(2024,1,15) - Cell reference containing a date
- Date serial number
IncludeHolidays Parameter
| Value | Behavior |
|---|---|
| (empty) | Returns "NA" for weekends/holidays |
| "Yes" | Returns the most recent available closing price |
| "No" | Returns "NA" for weekends/holidays |
Supported Symbol Formats
| Type | Format | Example |
|---|---|---|
| US Stocks | SYMBOL | AAPL, MSFT |
| ETFs | SYMBOL | SPY, QQQ |
| Indices | ^SYMBOL | ^SPX, ^DJI |
Notes
- For split-adjusted prices, use
Adjusted_Close_Historical() - Most commonly used historical price function
Examples
=Close_Historical("AAPL",DATE(2024,1,15))=Close_Historical("MSFT",DATE(2024,1,2))=Close_Historical("SPY",DATE(2024,1,13),"Yes")=Close_Historical("AAPL",A1)=Close_Historical(B1,A1)When to Use
- Looking up historical stock prices
- Calculating returns over a period
- Building historical price tables
- Performance analysis and backtesting
When NOT to Use
| Scenario | Use Instead |
|---|---|
| Need current/yesterday's close | Close() or PreviousClose() |
| Need split-adjusted prices | Adjusted_Close_Historical() |
| Need open/high/low | Open_Historical(), etc. |
| Date is weekend and need Friday's | Add "Yes" as third parameter |
Common Issues & FAQ
Q: Why am I getting "NA"?
A: The market was likely closed on that date (weekend, holiday). Use the IncludeHolidays parameter: =Close_Historical("AAPL",DATE(2024,1,13),"Yes")
Q: Should I use Close_Historical or Adjusted_Close_Historical? A:
Close_Historical()- Actual traded price (use for recent data, current holdings)Adjusted_Close_Historical()- Split/dividend adjusted (use for long-term analysis, returns calculation)
Q: How do I calculate return over a period?
A: =(Close_Historical("AAPL",DATE(2024,1,15))-Close_Historical("AAPL",DATE(2023,1,15)))/Close_Historical("AAPL",DATE(2023,1,15))
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?