How to Fill Missing Dates in Excel STOCKHISTORY Results
Question details
The user needs to fill in missing dates (such as weekends and holidays) from Excel's STOCKHISTORY results to create a continuous date sequence with the latest available rates.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Analyzing stock or currency pair history where a continuous daily date list is required, but the default function skips non-trading days.
- Observed behavior
- STOCKHISTORY only returns trading-day data, resulting in missing dates for weekends and market holidays.
Ensure you have an active internet connection to retrieve stock data and verify that your version of Excel supports Dynamic Arrays and the STOCKHISTORY, LET, SEQUENCE, and XLOOKUP functions.
Use LET, SEQUENCE, and XLOOKUP Formulas
Combine advanced array functions to generate a complete date list and pull the closest previous trading day's value for non-trading days.
By default, the STOCKHISTORY function only pulls data for active market days. To build a continuous timeline, we can generate a complete sequence of dates and use XLOOKUP with an approximate match to fetch the most recent available rate for weekends and holidays.
Start your formula with =LET(start,DATE(2024,8,1),finish,TODAY(),... to define the start date and the end date (e.g., today).
Add dates,SEQUENCE(finish-start+1,,start) within the LET function. This generates an uninterrupted list of dates from your start to finish date.
Define the stock data array by adding stock,STOCKHISTORY("GBP/USD",start,finish,0,0,0,1). Replace "GBP/USD" with your desired ticker symbol.
Add rates,XLOOKUP(dates,CHOOSECOLS(stock,1),CHOOSECOLS(stock,2),,-1). The crucial part is the -1 match mode, which tells XLOOKUP to return the exact match or the next smaller item (the most recent trading day) if the exact date is missing.
Finish the formula with HSTACK(dates,rates)). Press Enter to spill the complete, gap-free date and rate arrays into your worksheet.
Looking for a Powerful, Free Spreadsheet Editor?
While specialized connected functions like STOCKHISTORY are unique to certain Microsoft 365 subscriptions, WPS Office provides a robust, highly compatible, and completely free alternative for your daily data analysis and financial modeling needs.
- 1. Download WPS Office: Get the free installer from the official WPS website and install it on your computer.
- 2. Open your spreadsheet: Launch WPS Spreadsheets and open your existing .xlsx file to continue your financial analysis.
- 3. Use advanced formulas: Take advantage of WPS Spreadsheet's built-in financial, logical, and lookup functions to process your data.
Frequently Asked Questions
Why does STOCKHISTORY skip weekends and holidays?
The STOCKHISTORY function retrieves real market data. Since stock exchanges are closed on weekends and major public holidays, there is no trading data available to return for those specific dates.
What does the -1 do in the XLOOKUP function?
In the XLOOKUP formula, setting the match mode to -1 tells Excel to perform an exact match or return the next smaller item. If a weekend date isn't found, it pulls the data from the Friday before it.
Can I use this formula for different stock tickers?
Yes. Simply replace the ticker string (e.g., "GBP/USD") in the STOCKHISTORY section of the formula with your desired stock symbol, such as "AAPL" or "MSFT".
Why am I getting a #NAME? error?
The #NAME? error typically occurs if you are using an older version of Excel that does not support the LET, SEQUENCE, or STOCKHISTORY functions. You need Microsoft 365 to use these dynamic array functions.
