How to Get Stock Price Last N Days in Excel

Why This Task Matters in Excel

Modern finance and business decisions live and die by timely market information. Whether you manage a personal investment portfolio, prepare daily trading commentary for clients, reconcile stock-based compensation, or build automated dashboards for executive briefings, you must be able to answer a deceptively simple question: “What did this stock do over the last week (or month, or quarter)?”

Having last-N-days price data instantly available in Excel empowers you to:

• Track portfolio performance and spot short-term trends without logging into a brokerage account each morning.
• Create rolling return calculations for risk analysis and Value-at-Risk models in banking or treasury departments.
• Feed investor update emails with fresh price tables and charts that refresh on open instead of manual copy-paste from websites.
• Reconcile stock option expense by pulling the grant-date price and subsequent valuation windows needed for IFRS 2 or ASC 718 compliance.

Excel is uniquely suited for this because it combines three critical capabilities in a single environment:

1. Data Acquisition – With functions like STOCKHISTORY, WEBSERVICE, and the newer data types, Excel can fetch market data directly from the internet.
2. Calculation & Modeling – Once the numbers land on the sheet, Excel’s vast formula library (XLOOKUP, FILTER, LET, LAMBDA, etc.) lets you transform those prices into returns, moving averages, or whatever your analysis demands.
3. Presentation – Tables, conditional formatting, and charting tools mean stakeholders see not raw data but intuitive visuals and KPIs that drive action.

Failing to master this task forces analysts into time-consuming manual downloads, increases risk of transcription errors, and destroys the timeliness that turns data into insight. This technique is therefore a foundational building block that connects to skills such as dynamic array manipulation, dashboard automation, and dataset cleansing—making you faster, more reliable, and ultimately more valuable in any data-driven role.

Best Excel Approach

When your goal is “price data for the last N market days,” the most streamlined solution available in Microsoft 365 (or Excel 2021 perpetual) is the STOCKHISTORY function. STOCKHISTORY pulls historical end-of-day prices from Microsoft’s internal market feed and spills them automatically into adjacent cells as a dynamic array. Unlike older methods that scrape external sites, STOCKHISTORY is built into Excel, respects trading-day calendars, and eliminates layout changes that often break web scrapes.

The core logic:

1. Decide how many calendar or trading days you need.
2. Convert that count into a start date relative to TODAY().
3. Call STOCKHISTORY with the ticker, start date, TODAY(), and desired columns.
4. (Optional) Wrap in SORT, FILTER, or TAKE to refine the output.

Basic syntax:

=STOCKHISTORY(stock, start_date, end_date, [interval], [headers], [property1], [property2], …)

• stock – Quoted ticker symbol like \"MSFT\".
• start_date / end_date – Excel date serials or text dates.
• interval – 0 = daily (default), 1 weekly, 2 monthly.
• headers – 0 no header, 1 show header row, 2 show instrument header and column headers.
• property – 0 date (always returned first), 1 close, 2 open, 3 high, 4 low, 5 volume.

Recommended approach to fetch the last N days:

=STOCKHISTORY("MSFT", TODAY()-N, TODAY(), 0, 1, 0, 1)

Where N is a cell reference or hard-coded integer. This delivers a two-column spill (Date / Close) covering the requested window.

Alternative if you only want the latest N rows regardless of date gaps (holidays, weekends) and prefer newest first:

=LET(
 data, STOCKHISTORY("MSFT", TODAY()-60, TODAY(), 0, 0, 0, 1),
 TAKE(SORT(data, 1, -1), N)
)

Here we over-request 60 days, sort descending by date, then TAKE the top N rows. This guards against holidays so you always receive exactly N trading sessions.

Parameters and Inputs

Before typing a formula, confirm you understand data types and preconditions:

• stock (text) – Must be a valid symbol recognized by the Microsoft feed. International tickers often need an exchange suffix, for example \"7203.T\" for Toyota on Tokyo.
• start_date / end_date (dates) – Accept Excel date serials or ISO text like \"2023-07-15\". If the start date is after the end date you’ll get a #VALUE! error.
• N (integer) – User-defined count of trading days. Place it in a referenced cell so dashboards can recalculate dynamically.
• interval (optional) – Leave blank for daily; weekly is useful for trend snapshots but will not return exactly N calendar days.
• headers (integer) – I recommend 1 (just column headers) when you embed the spill into a structured table; use 0 when you plan to add your own custom headers upstream.
• property list (integers) – Order determines output column order. Typical two-column set uses 0 (date) and 1 (close).
  Input validation:
• Test the ticker symbol with `=STOCKHISTORY(`\"symbol\", TODAY()-1, TODAY()) before creating larger models.
• If N ≤ 0, wrap N in MAX(N,1) to avoid errors.
• For dashboards covering many tickers, store each symbol in its own cell within an Excel Table for easy spill references and automatic expansion.

Step-by-Step Examples

Example 1: Basic Scenario – 10 Trading Days of Microsoft

Imagine you’re building a quick chart for a weekly team meeting. You need the last 10 trading days’ closing prices for Microsoft (MSFT) and nothing more.

1. In [B2], type the ticker \"MSFT\".
2. In [B3], type the trading-day count 10.
3. In [C5], enter:

=STOCKHISTORY($B$2, TODAY()-$B$3, TODAY(), 0, 1, 0, 1)

Because headers is set to 1, Excel spills a two-column table starting with Date and Close. The result updates each morning when you open the workbook, giving you an automatically rolling window.

WHY IT WORKS: TODAY()-$B$3 calculates a date that is at least N calendar days ago. STOCKHISTORY automatically filters out weekends and market holidays, so you still end up with exactly 10 rows representing trading sessions.

Variations:

• Change property list to 0,3,4,1 for Date/High/Low/Close columns if you plan to display candle charts.
• Convert the spill into a named range with `=CHOOSECOLS(`spill,2) to chart only closing prices.

Troubleshooting: If the spill fails with #BUSY! simply wait; Excel is contacting the data service. If you see #VALUE!, ensure the ticker is valid and your Office version supports STOCKHISTORY.

Example 2: Real-World Application – Multi-Ticker Dashboard

Your asset-management firm produces a daily “Top 5 Holdings” dashboard tracking Apple, Amazon, Alphabet, Meta, and NVIDIA. The dashboard must:

• Show each stock’s 20-day price path in its own small chart.
• Refresh automatically at 8 a.m. before traders arrive.
• Allow the analyst to change “20” to any other number for ad-hoc lookbacks.

Step-by-step:

1. Build an Excel Table named tbl_Tickers with one column [Symbol] containing: AAPL, AMZN, GOOGL, META, NVDA.
2. In cell [D1], place the label LookbackDays and in [D2] type 20.
3. In [E2], enter:

=STOCKHISTORY([@Symbol], TODAY()-$D$2, TODAY(), 0, 0, 0, 1)

This formula sits in an adjacent structured-table column that automatically spills for each row, giving five individual dynamic arrays.
4. To chart, select any cell in the first spill for AAPL, press Alt + F1 to insert a default chart. Excel intelligently treats the spilled area as the source range.
5. Duplicate the chart, then right-click → Select Data → Adjust the series reference to each subsequent spill (e.g., =Sheet1!E3#).

Business payoff: Each morning a single F9 recalculates all five STOCKHISTORY calls, feeding live charts and letting traders spot gaps or sudden moves without opening Bloomberg. Performance remains brisk because you only query 100 rows per ticker.

Performance considerations:

• Keep the lookback count moderate (under 200) for instant recalc.
• Place formulas on a dedicated “Data” sheet away from dashboards to prevent screen redraw overhead.
• If your organization has many Excel users hitting the feed simultaneously, stagger workbook open times or switch to Power Query (discussed later) for heavy lifting.

Example 3: Advanced Technique – Exact N Latest Rows with Holidays Handled

Suppose compliance requests \"the last 30 trading-session closes\" but exactly 30 rows, no matter how many public holidays fell in between. Relying on start-date subtraction alone risks returning 29 or 28 rows during long breaks.

Solution:

=LET(
 rowsNeeded, 30,
 bufferDays, 60,
 raw, STOCKHISTORY("META", TODAY()-bufferDays, TODAY(), 0, 0, 0, 1),
 sorted, SORT(raw, 1, -1),
 TAKE(sorted, rowsNeeded)
)

Explanation:

1. Over-request 60 calendar days (bufferDays) to ensure we capture well above 30 market sessions.
2. STOCKHISTORY returns all trading days inside that window.
3. SORT sorts the array by the Date column descending (-1).
4. TAKE grabs the first 30 rows, guaranteeing the exact count.

Edge-case handling:

• If markets close for a full week, the buffer still delivers enough history. Increase bufferDays if you need bigger safety margins.
• Wrap the whole LET inside IFERROR to fall back to a message \"Data unavailable\" if ticker delists.

Optimization:

• Replace hard-coded 60 with rowsNeeded*3 for a dynamic buffer.
• Wrap final output in SORT again ascending if you need chronological order for charting.

Tips and Best Practices

1. Reference N in a named cell (e.g., nm_Lookback) so dashboards recalculate instantly when you edit a single field.
2. Always request Date (property 0) as your first column; doing so future-proofs formulas that rely on chronological sorting.
3. Convert spill outputs into Excel Tables if you plan to VLOOKUP or create PivotCharts—tables auto-expand when start or end dates shift.
4. For performance, place volatile functions like TODAY() in a helper cell once, then reference it across all STOCKHISTORY formulas to avoid redundant evaluations.
5. If you need pre-market or intraday prices, STOCKHISTORY cannot help; integrate Power Query with an API such as Alpha Vantage or use Excel’s built-in Stocks data type for quote-level retrieval.
6. Document your workbook: add a comment to each STOCKHISTORY cell noting data source limitations so auditors know price origins.

Common Mistakes to Avoid

1. Using calendar subtraction without a buffer: During long holiday periods you will fetch fewer than N rows. Fix by wrapping with TAKE/SORT as shown earlier.
2. Hard-coding tickers directly in formulas: Makes maintenance painful. Store tickers in cells or tables, then reference them.
3. Leaving headers set to 2: This adds a redundant instrument label row above Date and Close, breaking charts expecting uniform ranges. Set headers to 1 or 0 for consistency.
4. Comparing date serials to text dates: Ensure all dates returned by STOCKHISTORY flow into date-formatted cells; otherwise, subsequent formulas treating them as numbers will fail.
5. Excessive interval requests: Weekly or monthly intervals return fewer than N trading sessions, causing misinterpretation. Use daily interval for true session counts.

Alternative Methods

While STOCKHISTORY is elegant, it may not suit every environment. Below is a comparison of three mainstream alternatives:

Key migration tips:

• You can prototype with STOCKHISTORY, then swap to Power Query by replicating the start/end dates and ticker parameterization in the query URL.
• To keep formulas intact, output Power Query to a table with the same header names previously used by STOCKHISTORY.

FAQ

When should I use this approach?

Use STOCKHISTORY when you need quick, reliable, daily close data inside Excel 365 or 2021 without external dependencies. It shines in ad-hoc analysis, lightweight dashboards, and educational settings.

Can this work across multiple sheets?

Yes. Because STOCKHISTORY spills dynamically, you can place the formulas on a hidden “Data” sheet and reference the spill with the # operator (e.g., =‘Data’!E2#) from summary sheets. Ensure you don’t cut-and-paste partial spills; always refer to the top-left cell plus #.

What are the limitations?

STOCKHISTORY provides only end-of-day information. Intraday prices, bid-ask, dividends, and splits beyond the “adjclose” logic are not available. Also, some minor or delisted tickers may not be recognized.

How do I handle errors?

Wrap your formula in IFERROR to display custom messages or leave blanks:

=IFERROR(STOCKHISTORY("XYZ", TODAY()-30, TODAY(), 0, 1, 0, 1), "Ticker not found")

Use the ISERROR function to log issues in a dedicated “ErrorLog” sheet for audit trails.

Does this work in older Excel versions?

No. STOCKHISTORY is only in Microsoft 365 and Excel 2021. For Excel 2019 or earlier, switch to Power Query or a financial add-in like XLQ.

What about performance with large datasets?

Each unique STOCKHISTORY call reaches out to Microsoft’s servers. Pulling thousands of rows for dozens of tickers may slow recalculation. Batch queries with Power Query or call STOCKHISTORY once, cache the results in a table, and use INDEX, MATCH, or XLOOKUP to reuse that dataset.

Conclusion

Mastering the skill of fetching “Stock Price Last N Days” in Excel vaults you from basic spreadsheet user to savvy data integrator. You’ll eliminate manual downloads, feed dynamic dashboards, and gain immediate insight into market movements—all without leaving Excel. As next steps, experiment with combining STOCKHISTORY outputs with technical indicators (SMA, RSI), automate refresh schedules with Office Scripts, or explore Power Query for deeper historical pulls. With these techniques under your belt, you’re ready to tackle more advanced financial modeling and deliver timely, data-driven recommendations.