How to Return the Last Trading Day Value for Each Month in Excel
Question details
The user needs a formula to accurately extract the final available trading value for each month from a dataset of daily stock prices.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- When analyzing financial or stock market data, the calendar month-end dates often fall on weekends or holidays, meaning the exact month-end dates are entirely missing from the daily trading dataset.
- Observed behavior
- Standard exact-match lookups fail because the calendar month-end date does not exist in the records. The goal is to successfully return the value corresponding to the latest available trading date on or just before each month-end.
Ensure your stock dataset's date column is properly formatted as date values and sorted in ascending order (oldest to newest) before applying lookups.
Use the XLOOKUP Function with Approximate Match
This is the most efficient method. XLOOKUP can search for the exact month-end date and automatically step backward to the previous available date if an exact match isn't found.
By setting the match mode to -1, XLOOKUP will search for your target date. If that day is a weekend or market holiday, it will return the next smaller item, which perfectly corresponds to the most recent trading day.
Select your entire dataset and sort the date column in ascending order (from oldest to newest).
In a separate column, generate the exact calendar month-end dates you want to query. You can use the EOMONTH function to do this automatically.
Enter the formula `=XLOOKUP(month_end_dates, dates, values, , -1)` into your result cell. Replace 'month_end_dates' with your target date, 'dates' with your data's date column, and 'values' with the stock price column.
Use TAKE, SORT, and FILTER Dynamic Arrays
If you are using an Excel version that supports dynamic arrays, you can filter all records for a specific month and extract the last row.
Easily Extract Trading Data Using WPS Spreadsheet
WPS Spreadsheet natively supports modern dynamic array formulas and the powerful XLOOKUP function. You can execute complex financial data analysis seamlessly using the exact same methods.
- 1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open your .xlsx file containing the daily trading stock data.
- 2. Sort the dates: Highlight your data and navigate to Data > Sort to arrange your records from oldest to newest.
- 3. Enter the XLOOKUP formula: In an empty cell, type `=XLOOKUP(target_date, date_column, value_column, , -1)`.
- 4. Retrieve your data instantly: Press Enter to execute the formula and instantly retrieve the last available trading value before the month-end.
Frequently Asked Questions
Why does VLOOKUP fail to find the last trading day?
VLOOKUP typically looks for an exact match or an approximate match that defaults to the next largest value under certain sorting conditions. It lacks the specific -1 match mode parameter found in XLOOKUP, which strictly tells the software to step backward to the next smaller available item when an exact date is missing.
How can I generate a list of exact month-end dates automatically?
You can use the EOMONTH function. By referencing a standard date and adding 0 months, for example `=EOMONTH("2023-01-15", 0)`, the function will automatically return the final calendar day of that specific month (January 31, 2023).
How does XLOOKUP handle missing weekdays that are holidays?
The XLOOKUP formula with the -1 match mode simply jumps to the next most recent available date in your dataset. It does not differentiate between weekends and holidays; it only looks at the mathematical value of the dates present in your array.
Can I return the whole row of stock data instead of just the price?
Yes. When setting up your XLOOKUP formula, you can select multiple columns (like Price, Volume, and High/Low) as the 'return_array'. XLOOKUP will automatically spill the results into adjacent cells to show the complete record for that trading day.
