How to Get Stock Price Last N Months in Excel

Why This Task Matters in Excel

Tracking historical share prices is a classic finance and business requirement. Financial analysts have always needed to back-test trading strategies, update valuation models, and build dashboards that show performance over different time windows. Marketers check whether campaign announcements affected the share price. Operational managers review commodity or supplier prices to time bulk purchases. Small investors simply want a quick spreadsheet that shows how a portfolio performed over the past six months without paying for expensive terminals.

Excel shines in this area because it combines familiar grid-based modelling with built-in data types (Stocks & Geography) and increasingly powerful dynamic arrays. With a single formula you can now pull dozens, hundreds, or thousands of historical prices, transform them with Power Query, summarise them with PivotTables, and visualise them with Sparklines or Power BI – all inside the same workbook.

Not knowing how to fetch “last N months” of prices forces users into copy-and-paste habits from web pages. These manual steps introduce errors, break refreshability, and waste valuable analysis time. Worse, if historical data is misaligned with fiscal periods or contains missing dates, downstream calculations such as moving averages or value at risk become unreliable.

Mastering this task tightly connects to other spreadsheet skills: dynamic spilling, date arithmetic, conditional formatting, statistical analysis, dashboards, and even VBA or Office Scripts automation. Once you can reliably obtain rolling historical data, running sensitivity analysis, creating alerts, or building interactive scenarios becomes a matter of adding formulas, not hunting for data.

Best Excel Approach

The most streamlined modern solution is the STOCKHISTORY function, introduced in Microsoft 365 and Excel 2021. It pulls official market data directly from Microsoft’s data service and returns it as a dynamic array that automatically resizes when you change the start or end date. Because the question asks for the “last N months,” the key is to derive the correct start date relative to today, pass it to STOCKHISTORY, and let the function spill the dates and closing prices down the sheet.

Syntax we will use:

=STOCKHISTORY(
    ticker,            /* stock symbol in quotes or reference */
    start_date,        /* earliest date to retrieve */
    end_date,          /* latest date, optional */
    interval,          /* 0=daily, 1=weekly, 2=monthly */
    headers,           /* 0=no headers, 1=show headers */
    property1, ...     /* choose which price columns to return */
)

For daily prices of the last N calendar months, with headers and default properties:

=STOCKHISTORY(
   A2,                                  /* ticker cell */
   EOMONTH(TODAY(), -N)+1,              /* first day of month N months ago */
   TODAY(),                             /* up through today */
   0,                                   /* daily data */
   1                                    /* include headers */
)

When your analysis only needs the closing price on the last trading day of each month, simply switch interval to 2 and Excel returns one row per month – ideal for trend charts and YOY comparisons.

Alternative (older) methods include:

=WEBSERVICE("https://query1.finance.yahoo.com/v7/finance/download/" & A2 & parameters)

or Power Query’s “From Web” connector combined with the Stocks linked data type. We will examine them later, but STOCKHISTORY is first choice due to zero code, automatic date handling, and refreshability.

Parameters and Inputs

• Ticker symbol: Text string such as \"MSFT\" or a cell like [A2] containing that symbol.
• N (number of months): Positive integer indicating how far back you want to go. Often stored in [B2] for flexibility.
• Start date: Usually calculated with EOMONTH(TODAY(), -N)+1, which returns the first calendar day of the month N months ago.
• End date: TODAY() is typical, but any valid Excel date works. Passing a weekend date is fine; STOCKHISTORY backfills to the most recent trading day.
• Interval: 0 (daily), 1 (weekly), 2 (monthly). Daily is default and most granular.
• Headers flag: 0 hides the header row; 1 shows it.
• Optional property arguments: 0=Date, 1=Close, 2=Open, 3=High, 4=Low, 5=Volume. List multiple numbers to retrieve several columns.

Preparation tips:

• Ensure your workbook is set to an English locale if using US tickers; other markets may need correct market suffixes (for example \"6758.T\" for Sony on Tokyo exchange).
• Format the output spill range with an Excel Table or just leave it dynamic.
• Validate that N is positive and not excessively large (STOCKHISTORY tends to limit to 400 years).
• If the ticker delisted or dates fall before an IPO, expect #VALUE! or blank returns; wrap formulas in IFERROR for robustness.

Step-by-Step Examples

Example 1: Basic Scenario

Suppose you want the daily prices of Apple (AAPL) for the last 3 months to build a simple line chart.

1. In [A2] type \"AAPL\".
2. In [B2] type 3 – the desired months.
3. In [C2] enter the following formula and press Enter:

=STOCKHISTORY(
    A2,
    EOMONTH(TODAY(), -B2)+1,
    TODAY(),
    0,
    1
)

Excel spills a table with Date, Close, High, Low, Open, and Volume.
4. Create a line chart: select the spill range, go to Insert ➜ Line ➜ 2-D Line. The X-axis automatically picks up the Date column.
5. Adjust formatting: right-click Y-axis to show currency with two decimals; set chart title “AAPL – Last 3 Months”.

Why it works: EOMONTH(TODAY(), -B2)+1 backtracks to the final day of the month three months ago, then adds one day to land on the first of that month. STOCKHISTORY fills every trading day until today and, because headers flag equals 1, your chart labels update automatically if you later change [B2] to another value.

Troubleshooting: If you see #N/A, confirm internet connectivity and that your Microsoft 365 account is signed in. If the chart shows only one point, check that interval is 0 for daily data; monthly will compress to one row per month.

Variations:

• Switch interval to 2 for monthly closes:

  =STOCKHISTORY(A2,EOMONTH(TODAY(),-B2)+1,TODAY(),2,1,1)
  

• Pull only Date and Close by listing property 0 and 1:

  =STOCKHISTORY(A2,EOMONTH(TODAY(),-B2)+1,TODAY(),0,1,0,1)
  

Example 2: Real-World Application

Imagine you manage a balanced portfolio and must update a dashboard that compares six-month returns of five tickers: MSFT, AMZN, NVDA, KO, and JPM.

1. Set up a vertical list of tickers in [F2:F6].
2. In [G1] type “Months” and in [G2] place 6. Name [G2] “monthsBack” for clarity (Formulas ➜ Name Manager ➜ New).
3. Beside each ticker, enter in [G2] (relative to row 2) the formula:

=STOCKHISTORY(
     F2,
     EOMONTH(TODAY(), -monthsBack)+1,
     TODAY(),
     2,
     0,
     1             /* close only */
)

4. Because STOCKHISTORY spills vertically below each ticker, separate each block with two blank rows or wrap everything in LET+TAKE to return only the last row (latest close) for each ticker:

=LET(
    data, STOCKHISTORY(
        F2,
        EOMONTH(TODAY(), -monthsBack)+1,
        TODAY(),
        2,
        0,
        1),
    TAKE(data, -1)     /* newest month only */
)

5. Collect those single closing prices into a second column, compute percentage change with:

=(H2 / INDEX(STOCKHISTORY(F2,EOMONTH(TODAY(), -monthsBack)+1,EOMONTH(TODAY(), -monthsBack),2,0,1), 2)) -1

The formula divides the latest close by the close six months ago minus 1 to yield the percentage return.
6. Build a conditional formatting Data Bar to emphasise positive and negative returns, and add a slicer to choose 3, 6, or 12 months by simply changing the named cell monthsBack.

Integration: This technique can feed directly into a PivotTable that groups tickers by sector, or into Power Query to merge fundamental metrics. For enterprise BI, publish the workbook to Power BI Service and schedule data refresh.

Performance considerations: Retrieving multiple tickers daily is lightweight, but scaling to dozens of tickers with daily granularity can create thousands of rows. Use monthly interval when possible and store results in a helper sheet to limit spill recalculations.

Example 3: Advanced Technique

Requirement: Build a dynamic dashboard that lets users choose any ticker and any rolling period (1-to-24 months) from slicers. Additionally, weekends should be removed, and non-trading holidays must be marked.

1. Create a parameter sheet:

• [A1] “Ticker”, data validation drop-down referencing a named list of tickers.
• [B1] “Months Back”, data validation list of numbers 1 through 24.

2. Generate the base history in [History!A2]:

=STOCKHISTORY(
    Parameters!A2,                                  /* selected ticker */
    EOMONTH(TODAY(), -Parameters!B2)+1,
    TODAY(),
    0,
    0,
    0,1                       /* Date & Close only */
)

3. Use LET to wrap, remove blank dates, and flag holidays:

=LET(
    hist, STOCKHISTORY(Parameters!A2,
           EOMONTH(TODAY(), -Parameters!B2)+1,
           TODAY(), 0, 0, 0,1),
    dates, TAKE(hist,,1),
    prices, TAKE(hist,,2),
    weekdays, WEEKDAY(dates,2),
    holidayFlag, IF(ISNUMBER(XMATCH(dates,Holidays!A:A,0)), "Holiday", ""),
    cleaned, FILTER(CHOOSE({1,2,3,4},dates,prices,weekdays,holidayFlag),weekday<=5),
    cleaned
)

This advanced formula:

• Retrieves history.
• Splits into dates and prices.
• Calculates weekday numbers (Monday=1).
• Flags dates found in a named range Holidays (official market holidays).
• Filters out Saturday and Sunday.
• Returns a four-column array (Date, Close, Weekday, HolidayFlag).

4. Feed this cleaned array into a dynamic named range “ChartData” then build a combo chart (line for price, scatter with markers for holiday flags).
5. Add slicers linked to the parameter cells so managers can toggle tickers and periods during meetings.
6. Optimisation: if the dashboard lags, switch calculation options to “Manual,” or cache results in a hidden sheet and refresh with a button tied to VBA: Application.CalculateFull.

Error handling: Wrap STOCKHISTORY in IFERROR, e.g.

=IFERROR(
    STOCKHISTORY(...),
    "Data not available"
)

Edge cases: IPO-recent tickers returning fewer months than requested can be highlighted with conditional formatting using ROWCOUNT < (Parameters!B2*20).

Tips and Best Practices

1. Store N (months) in a named cell so all formulas reference one value; changing it updates everything at once.
2. Turn your spill range into an Excel Table (Insert ➜ Table) after converting formulas to values when your period stops changing. Tables auto-expand for future data without recalculating history repeatedly.
3. Use interval 2 (monthly) for charts – it simplifies the X-axis and improves performance on large dashboards.
4. Combine STOCKHISTORY with the TAKE and DROP functions to quickly get first or last rows without extra helper cells.
5. When sharing workbooks, remember STOCKHISTORY requires Microsoft 365 or Excel 2021; save a static copy for users on older versions.
6. If you run macros, disable screen updating during batch refresh to avoid flicker: Application.ScreenUpdating = False.

Common Mistakes to Avoid

1. Using TODAY()-30*N as the start date. Months are not always 30 days; EOMONTH ensures you capture whole calendar months and align with month-end reporting.
2. Forgetting the interval argument. Omitting it returns daily data by default, which might overload your sheet if you expected monthly granularity.
3. Hard-coding the ticker inside the formula. Place it in a cell so colleagues can change it without editing formulas; this reduces formula duplication and errors.
4. Ignoring error handling. Delisted or ill-typed tickers produce #VALUE!; wrap with IFERROR or DATA VALIDATION to catch mistakes early.
5. Overlooking market suffixes. International stocks often require a dot and exchange code, for example \"AIR.PA\" (Airbus in Paris). Without the suffix, you may pull the wrong instrument or get no data.

Alternative Methods

Choosing a method:

• Use STOCKHISTORY if available; it is fastest and most reliable.
• For Excel 2013/2016 users, Power Query with a free API can replicate the same results.
• Choose VBA only when you need tight integration with macros or to push data into databases.

FAQ

When should I use this approach?

Any time you need a rolling window of historical prices – back-testing strategies, charting momentum, or calculating trailing volatility. If your question starts with “What did this stock do over the last X months?” STOCKHISTORY is ideal.

Can this work across multiple sheets?

Yes. Place your history formulas on a “Data” sheet and reference them from a “Dashboard” sheet using dynamic array references like Data!A2# which automatically point to the full spill range.

What are the limitations?

STOCKHISTORY does not provide intraday data, non-US dividend adjustments, or fundamental statements. It pulls calendar dates only back to the IPO. There can be a slight delay (up to 15 minutes) compared with real-time feeds.

How do I handle errors?

Wrap all STOCKHISTORY calls in IFERROR or IFNA to show a friendly message. Validate tickers with the REGEXMATCH pattern [A-Z\.][1,5] and provide a data validation list. For empty returns, compare ROWS(result) < 2 to highlight missing data.

Does this work in older Excel versions?

STOCKHISTORY demands Microsoft 365 or Excel 2021. For Excel 2010-2019, use Power Query with a free web API or copy-paste CSV export links. You can still automate refresh with VBA.

What about performance with large datasets?

Limit the number of tickers pulled simultaneously. Use monthly interval for long horizons. Convert dynamic arrays to static values periodically. Disable automatic calculation during bulk updates: Formula tab ➜ Calculation Options ➜ Manual.

Conclusion

Being able to fetch the last N months of share prices directly into Excel transforms your workbook from a static ledger into a living analytical tool. With STOCKHISTORY you replace hours of copying web tables with a single, dynamic formula that spills as much data as your model needs. This unlocks deeper insights – live dashboards, scenario testing, and automated reporting – without leaving the spreadsheet environment. Add this technique to your toolbox, explore integrating it with Power Query or Power BI, and you will dramatically elevate both the speed and reliability of your financial analysis.