Close Price (Month End)
Returns the closing price for a stock or ETF on the last trading day of the specified month. If the last calendar day of the month falls on a weekend or holiday, the function automatically uses the nearest prior trading day.
Auto-Adjustment
The function computes the last day of the month using the calendar (accounting for leap years, 28/30/31-day months), then appends /Yes to the API call to auto-adjust to the nearest prior trading day.
Validation
- Month must be 1-12
- Returns "Invalid Month. Use 1-12." for out-of-range values
Syntax
=Close_MonthEnd(Symbol, Year, Month)Parameters
Stock or ETF ticker symbol
The year (e.g., 2025)
Month number: 1 (January) through 12 (December)
Returns
Closing price on the last trading day of the specified month
Examples
=Close_MonthEnd("AAPL", 2025, 6)=Close_MonthEnd("MSFT", 2025, 12)=Close_MonthEnd("SPY", 2024, 2)=Close_MonthEnd(A1, B1, C1)When to Use
- Monthly performance tracking
- Calculating month-over-month returns
- NAV reconciliation at month end
- Building monthly return series
When NOT to Use
\
Common Issues & FAQ
I'm getting "Invalid Month. Use 1-12."?
The Month parameter must be between 1 and 12.
Feb close — does it handle leap years?
Yes. The function uses DateTime.DaysInMonth() which correctly handles leap years (Feb 29 vs Feb 28).
Related Formulas
More MarketXLS Price History formulas you can use in the same worksheet:
Get Access to 1 Billion Usable Market data points IN YOUR EXCEL SHEETS WITH EASY TO USE EXCEL FUNCTIONS
Get started today