Excel STOCKHISTORY Function: Pull Live Market Data (2026)

July 14, 2026 · VeloraAI Team

Automation Data Analysis Excel

Ninety percent of the analysts I have seen still copy stock prices into Excel from a browser tab. Ctrl+C, Ctrl+V, transpose, clean the commas — every Monday morning, on the same tickers, for the same window. The Excel STOCKHISTORY function ended that ritual four years ago, and the analysts who adopted it saved an hour a week on data plumbing alone.

STOCKHISTORY pulls historical open, high, low, close, and volume data directly from Microsoft's market data feed into a spill range — no add-ins, no API keys, no Python bridge. In this guide we walk through the syntax, build a working portfolio tracker, wire it to return and volatility formulas, and cover the failure modes you will actually hit in production. Every formula runs in Microsoft 365 with a live internet connection.

What Is the STOCKHISTORY Function in Excel?

STOCKHISTORY is a dynamic-array function introduced in Microsoft 365 that returns historical price data for a stock, ETF, mutual fund, currency, or cryptocurrency as a spilled table. It queries Microsoft's own market data provider, so the licensing and connection are already handled by your Microsoft 365 subscription — you do not need Bloomberg, FactSet, or a Refinitiv Eikon seat to pull daily closes for a US-listed equity.

The function requires an active internet connection, Microsoft 365 (not Excel 2019 or 2021 desktop), and returns data with a headers row and one row per period. It is the closest thing Excel has to a native market data terminal, and it changed how retail-side and mid-market corporate finance teams source pricing.

ℹ️ Note: STOCKHISTORY is only available in Microsoft 365. Perpetual-license Excel 2021 and Excel LTSC do not include it. If #NAME? appears when you type the function, you are on the wrong SKU.

STOCKHISTORY Syntax at a Glance

=STOCKHISTORY(stock, start_date, [end_date], [interval], [headers], [properties1], [properties2], ...)

Argument	Required	Description
`stock`	Yes	Ticker as text (`"MSFT"`), an exchange-qualified pair (`"XNAS:MSFT"`), or a linked Stocks data type cell
`start_date`	Yes	Serial date or `DATE(y,m,d)`
`end_date`	No	Defaults to today if omitted
`interval`	No	`0` = daily (default), `1` = weekly, `2` = monthly
`headers`	No	`0` = no headers, `1` = headers (default), `2` = ticker + headers
`properties`	No	Up to 6 property codes: `0` date, `1` close, `2` open, `3` high, `4` low, `5` volume

How Do You Use STOCKHISTORY in Excel?

To pull five years of monthly closing prices for Microsoft, enter =STOCKHISTORY("MSFT", DATE(2021,1,1), TODAY(), 2, 1, 0, 1) into an empty cell and press Enter. Excel spills two columns — date and close — for every month since January 2021. To add open, high, low, and volume, extend the properties list: =STOCKHISTORY("MSFT", DATE(2021,1,1), TODAY(), 2, 1, 0, 1, 2, 3, 4, 5).

The order of the property arguments controls the column order in the output. That gives you a fully parameterizable price feed you can point at any cell — change the ticker, and the entire spill refreshes.

Step 1: Build a Parameter Block

Never hardcode the ticker or dates. Create a small input block so the whole model reruns when you change one cell:

B2: Ticker            MSFT
B3: Start date        =EDATE(TODAY(), -60)
B4: End date          =TODAY()
B5: Interval          2         (monthly)

Step 2: Wire STOCKHISTORY to the Parameters

In cell D2, drop the parameterized call:

=STOCKHISTORY(B2, B3, B4, B5, 1, 0, 1, 2, 3, 4, 5)

The result spills into D2:H63 (or however many months are in the window). Because the range is dynamic, downstream formulas need to reference the spill with the # operator — D2# — not a fixed range like D2:D63. That single character is the difference between a workbook that survives a re-pull and one that silently breaks when the ticker changes.

💡 Pro Tip: Use INDEX(D2#, 0, 5) to grab an entire column of the STOCKHISTORY spill by property index. That makes your return and volatility formulas totally position-independent — reorder the properties in STOCKHISTORY and downstream calcs still work if you index by column meaning, not column letter.

Step 3: Calculate Returns Off the Spill

Once prices are live, log returns are one formula away. In column J, assuming close is column 2 of the spill:

=LN(INDEX(D2#, ROW()-1, 2) / INDEX(D2#, ROW()-2, 2))

Or, if you want the entire return series as one dynamic array using LET:

=LET(
  close, INDEX(D2#, 0, 2),
  n, ROWS(close),
  LN(DROP(close, 1) / DROP(close, -1))
)

That single LET block returns a full monthly log-return series that automatically resizes whenever STOCKHISTORY refreshes. Point it at annualized volatility with STDEV.S(...) * SQRT(12) and you have a market-driven risk input for a CAPM-based WACC build.

Building a Live Portfolio Tracker With STOCKHISTORY

The real payoff shows up when you point STOCKHISTORY at a whole portfolio. Here is the architecture that scales cleanly from ten tickers to a hundred.

graph TD
    A[Ticker List] --> B[STOCKHISTORY per Ticker]
    B --> C[Price Matrix]
    C --> D[Log Returns]
    D --> E[Portfolio Return Series]
    D --> F[Covariance Matrix]
    E --> G[Cumulative P&L]
    F --> H[Portfolio Volatility]
    H --> I[Sharpe Ratio]

Step 1: Central Ticker List and Weight Vector

Put tickers in A2:A11 and portfolio weights in B2:B11. Confirm weights sum to 1 with =SUM(B2:B11) — a common bug is a portfolio with 97% invested that quietly understates return.

Step 2: Loop STOCKHISTORY Per Ticker Using LAMBDA

Excel does not iterate over an array through STOCKHISTORY natively — the stock argument must be a single value. Use a LAMBDA inside BYROW or MAP to fan out:

=LET(
  tickers, A2:A11,
  start, EDATE(TODAY(), -36),
  finish, TODAY(),
  pull, LAMBDA(t, STOCKHISTORY(t, start, finish, 1, 0, 0, 1)),
  MAP(tickers, pull)
)

This returns an array of arrays. To get a clean matrix of closes with one column per ticker, use HSTACK on individual STOCKHISTORY calls or place each pull on its own sheet — Excel's array-of-arrays support is still fragile in matrix operations, and one-sheet-per-pull is what production models actually use.

Step 3: Build the Return Matrix

On a Returns sheet, reference each ticker's close column and build log returns side by side:

=LN(MSFT!C3:C120 / MSFT!C2:C119)

Repeat across tickers using HSTACK:

=HSTACK(
  LN(MSFT!C3:C120 / MSFT!C2:C119),
  LN(AAPL!C3:C120 / AAPL!C2:C119),
  LN(GOOGL!C3:C120 / GOOGL!C2:C119)
)

Step 4: Portfolio Return, Volatility, and Sharpe

With returns matrix R (n periods × k tickers) and weight vector w:

Portfolio return series: =MMULT(R, w)
Portfolio mean return:   =SUMPRODUCT(w, AVERAGE(R by column))
Portfolio volatility:    =SQRT(MMULT(TRANSPOSE(w), MMULT(COV_MATRIX, w))) * SQRT(12)
Sharpe (annualized):     =(mean * 12 - rf) / vol

The MMULT(TRANSPOSE(w), MMULT(COV_MATRIX, w)) block is the closed-form portfolio variance formula from Markowitz — it is what a portfolio optimization workbook with Solver uses under the hood.

Example: A 60/40 portfolio of SPY and AGG over the last 36 monthly closes pulled via STOCKHISTORY gave a mean monthly return of 0.71%, annualized volatility of 9.8%, and — at a 4.5% risk-free rate — a Sharpe of 0.44. All four inputs came from a single STOCKHISTORY spill per ticker.

What Are the Limitations of STOCKHISTORY?

STOCKHISTORY is powerful but not a Bloomberg replacement. Understanding what it cannot do is more important than knowing what it can.

Coverage Gaps

No intraday data. Daily is the finest granularity — no minute bars, no tick data.
Limited international coverage. Most major exchanges are supported, but small-cap Asia-Pacific and frontier markets are inconsistent.
No corporate actions detail. Prices are dividend-adjusted (usually), but you cannot pull the dividend or split history separately.
No fundamentals. STOCKHISTORY is price-only. For financials, you still need the Stocks data type or an external provider.
No options, futures, or bonds. Equity, ETF, mutual fund, FX, and crypto only.

Reliability Trade-offs

The data provider changes over time. Microsoft has switched vendors twice since STOCKHISTORY launched, and there have been multi-day outages where the function returned #BUSY! for every ticker. If a client model depends on STOCKHISTORY, you need a caching layer — snapshot the pull to a values-only sheet after every refresh, so a bad Monday morning does not blank out Friday's board pack.

⚠️ Warning: STOCKHISTORY refreshes on file open and on manual recalculation with Ctrl+Alt+F9. It does not refresh automatically during the trading day. If you need real-time prices, use the Stocks linked data type (Data → Stocks) alongside STOCKHISTORY — the data type gives you a live last-price cell, STOCKHISTORY gives you the historical series.

Common Error Codes

Error	Cause	Fix
`#NAME?`	Function unavailable in your Excel SKU	Upgrade to Microsoft 365
`#BUSY!`	Data provider returned nothing or is rate-limited	Wait 30 seconds, press F9
`#FIELD!`	Property index invalid or unsupported for security	Check property list
`#VALUE!`	Ticker not recognized or dates malformed	Prefix with exchange code, e.g., `XNAS:MSFT`
`#CONNECT!`	No internet or blocked by proxy	Check network and Trust Center settings

STOCKHISTORY vs Bloomberg, Refinitiv, and Python

Where STOCKHISTORY fits in the market-data stack depends on how tight your latency requirements are and how much history you need.

Source	Cost	Intraday	History Depth	Fundamentals	Best For
STOCKHISTORY	Included in M365	No	~15 years	No	Mid-market corporate finance, retail portfolios
Stocks data type	Included in M365	Live	Snapshot only	Yes (limited)	Watchlists, single-line current quote
Bloomberg BQL/BDH	$2K–$3K/mo/user	Yes	30+ years	Full	Institutional research, trading desks
Refinitiv Eikon	$1.5K–$2K/mo	Yes	30+ years	Full	Sell-side research
yfinance (Python)	Free	Delayed	~20 years	Some	Analysts comfortable with code
Alpha Vantage API	Free tier + paid	Delayed	20 years	Some	Automated dashboards, PowerBI

For a corporate development team modeling a public target, STOCKHISTORY plus the Stocks data type covers 90% of the historical price work at zero incremental cost. For quant research or high-frequency work, you need a real API. The right question is not "which is better" but "what is the minimum data quality that makes my model defensible."

Advanced Patterns: Combining STOCKHISTORY With AI

The next unlock is pointing an AI layer at STOCKHISTORY output. Instead of hand-building return and volatility formulas, you can pull the price series and ask an AI-in-Excel add-in like VeloraAI to draft the full return, moving-average, and drawdown formula stack against your specific column layout. What used to be a 20-minute formula bake becomes a two-sentence prompt.

The same workflow supports natural-language screening — "flag any month where the drawdown exceeded 8%" — without leaving the workbook, and pairs neatly with AI prompts for Excel financial analysts for the rest of the analysis stack.

💡 Pro Tip: Snapshot the STOCKHISTORY spill to a static range weekly. Use Copy → Paste Special → Values on the spill range, then work off the snapshot for models that go into board decks. That gives you an audit trail and immunity to data provider outages.

Frequently Asked Questions

Why is STOCKHISTORY returning #BUSY!?

#BUSY! means the Microsoft data feed did not respond in time or is rate-limited. It usually resolves in 30–60 seconds — press F9 to force a recalculation. If it persists across multiple tickers, check the Microsoft 365 service status. Persistent #BUSY! on obscure tickers means the security is not covered — try prefixing with the exchange code, e.g., XLON:VOD instead of VOD.L.

Can STOCKHISTORY pull crypto prices?

Yes. Cryptocurrency pairs use the format "BTC/USD" or "ETH/USD". Coverage includes Bitcoin, Ethereum, and most top-30 tokens against USD, EUR, and GBP. Interval and property arguments work identically to equities. Historical depth is shorter than for equities — typically 5–8 years — because of when the pair was added to Microsoft's feed.

How do I refresh STOCKHISTORY automatically?

Excel does not refresh STOCKHISTORY on a timer natively. Three workarounds: press Ctrl+Alt+F9 to force a full recalc, use Data → Refresh All on file open, or drive a scheduled Office Script that recalculates the workbook every N minutes. For real-time last-price behavior, use the Stocks linked data type instead — it refreshes automatically while a workbook is open.

Does STOCKHISTORY adjust for stock splits and dividends?

Closing prices from STOCKHISTORY are adjusted for stock splits automatically. Dividend adjustments are inconsistent — for most US large-caps the close is total-return-adjusted, but for some international listings the close is the raw price. Always sanity-check against a known split date (e.g., NVDA's June 2024 10-for-1) before relying on the series for a multi-year performance calc.

Can I use STOCKHISTORY for backtesting a strategy?

Yes, for daily-and-slower strategies. Because STOCKHISTORY caps out at daily granularity and does not include bid/ask, it is not appropriate for intraday or execution-sensitive backtests. For monthly rebalancing, factor tilts, or long-horizon strategy research, it works cleanly — pair with a LAMBDA-driven return matrix and a Solver-based optimizer for a fully in-Excel backtest that never leaves the workbook.

Where STOCKHISTORY Fits in Your Workflow

STOCKHISTORY is not going to displace Bloomberg on a trading desk, but for the 80% of finance work that lives in mid-market corporate development, FP&A, private wealth, and independent research, it eliminates the single most tedious step in the modeling loop — sourcing prices. Combine it with LAMBDA, LET, and dynamic arrays, and a portfolio tracker that used to take a full day now rebuilds itself every time you open the file.

The direction of travel is clear: market data is becoming a workbook primitive, not an external dependency. The analysts who adopt STOCKHISTORY, the Stocks data type, and AI-native workflows in 2026 are the ones who will spend their time on the interesting part of the job — the investment thesis, not the data plumbing.