Historical Adjusted Close Price
Returns the adjusted closing price for a stock on a specific historical date. The adjusted close accounts for stock splits and dividend payments, making it ideal for calculating true returns over time.
Why Use Adjusted Close?
| Event | Effect on Raw Price | Adjusted Close |
|---|---|---|
| 2:1 Stock Split | Price halves overnight | No discontinuity |
| Dividend Payment | Price drops by dividend | Accounts for income |
| Reverse Split | Price increases | No discontinuity |
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 |
When to Use Each
| Use Case | Function |
|---|---|
| Calculating long-term returns | Adjusted_Close_Historical() |
| Comparing prices over years | Adjusted_Close_Historical() |
| Looking up actual traded price | Close_Historical() |
| Current holdings value | Close_Historical() |
Notes
- Essential for accurate historical return calculations
- Adjusted prices may differ significantly from traded prices for old dates
- Recent prices are usually very close to unadjusted
Examples
=Adjusted_Close_Historical("AAPL",DATE(2024,1,15))=Adjusted_Close_Historical("TSLA",DATE(2020,8,28))=Adjusted_Close_Historical("NVDA",DATE(2021,7,19))=Adjusted_Close_Historical("SPY",DATE(2024,1,13),"Yes")=Adjusted_Close_Historical(B1,A1)When to Use
- Calculating historical returns accurately
- Building long-term performance charts
- Comparing prices across stock splits
- Backtesting trading strategies
- Dividend reinvestment analysis
When NOT to Use
| Scenario | Use Instead |
|---|---|
| Need actual traded price | Close_Historical() |
| Need current price | Last() or Close() |
| Need real-time data | QM_Stream_Last() |
| Recent prices (no splits) | Either function works |
Common Issues & FAQ
Q: Why is the adjusted close different from the close I remember? A: Stock splits and dividends are retroactively applied to all historical prices. After a 2:1 split, all prior adjusted prices are halved.
Q: When should I use adjusted vs unadjusted? A:
- Adjusted: Returns calculation, long-term analysis, charts
- Unadjusted: Actual traded price, order history, recent data
Q: Why am I getting "NA"?
A: The market was closed on that date. Use "Yes" as the third parameter to get the most recent available price.
Q: How do I calculate total return?
A: =(Adjusted_Close_Historical("AAPL",DATE(2024,1,15))/Adjusted_Close_Historical("AAPL",DATE(2023,1,15))-1)*100
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?