Close Price (Year End)
Returns the closing price for a stock or ETF on the last trading day of the specified year. Uses December 31 as the target date, automatically adjusting to the nearest prior trading day if Dec 31 falls on a weekend or holiday.
Notes
- Always targets Dec 31 of the given year
- Auto-adjusts to the last actual trading day (e.g., Dec 29 if Dec 31 is a Sunday)
- Only requires Symbol and Year — simpler than MonthEnd/QuarterEnd variants
Syntax
=Close_YearEnd(Symbol, Year)Examples
=Close_YearEnd("AAPL", 2024)=Close_YearEnd("MSFT", 2023)=Close_YearEnd("SPY", 2020)=Close_YearEnd(A1, B1)Calculate annual return:
=(Close_YearEnd("AAPL",2024)-Close_YearEnd("AAPL",2023))/Close_YearEnd("AAPL",2023)When to Use
- Annual performance reporting
- Year-over-year return calculations
- Tax year-end valuations
- Building annual return comparison tables
When NOT to Use
\
Common Issues & FAQ
The price isn't from Dec 31.?
If Dec 31 is a weekend or holiday, the function returns the nearest prior trading day's price. This is correct behavior.
Should I use Close or Adjusted_Close for return calculations?
Use Adjusted_Close_YearEnd for return calculations to account for splits and dividends. Use Close_YearEnd for actual traded 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?