How to Make a Stock Chart in Excel with STOCKHISTORY

Most stock-chart tutorials still have you copying price data out of a website by hand, pasting it into a grid, and babysitting five columns of numbers so a finicky chart type doesn’t throw an error. You don’t need to do any of that anymore. Since Microsoft added the STOCKHISTORY function, you can pull a security’s full price history straight into the sheet with one formula and chart it directly. The data refreshes on its own. No copy-paste, no manual updates.

This guide builds a stock chart the modern way: one dynamic-array formula feeding a native Open-High-Low-Close chart. Then it covers the manual fallback for anyone who can’t use STOCKHISTORY, and the handful of errors that trip up nearly everyone the first time.

Educational content only — not financial advice. This article explains how to build and read a chart in Excel. It is not investment advice, does not recommend any security or trading action, and nothing here should be treated as a signal to buy or sell. Historical price data shows what happened in the past, which tells you nothing certain about the future. If you’re making financial decisions, talk to a licensed professional.

What STOCKHISTORY actually does (and what it doesn’t)

STOCKHISTORY retrieves historical price data for a financial instrument and drops it into your sheet as a dynamic array — a range that spills automatically to whatever size it needs. Give it a ticker and a start date and it returns dated rows of prices without any add-in or web query.

Two limits matter before you start, because they cause most of the disappointment.

First, it requires a Microsoft 365 subscription — Personal, Family, Business Standard, or Business Premium. This trips people up constantly: Excel 2019, 2021, and 2024 are one-time purchases, and they look almost identical to the 365 app, but they don’t include connected data features like STOCKHISTORY. If the function isn’t recognized, this is almost always why. There’s a manual method further down for exactly this situation.

Second, the data is not live. STOCKHISTORY returns prices recorded after the market closes, and it updates once per trading day — typically a few hours after close. It’s built for historical analysis, not for watching a price tick during the trading day. If you want the latest close as a single number, the Stocks data type does that better. STOCKHISTORY is for the chart.

The underlying data comes from LSEG Data & Analytics (formerly Refinitiv), provided as-is. Keep that in mind for the troubleshooting section — when the chart breaks, the cause is often on their end, not yours.

The syntax, in plain terms

Here’s the full signature:

=STOCKHISTORY(stock, start_date, [end_date], [interval], [headers], [property0], [property1], ...)

Only the first two arguments are required. The rest are optional and control what you get back.

• stock — the ticker in quotes, like "MSFT". To be unambiguous about which exchange, prefix it: "XNAS:MSFT" for NASDAQ.
• start_date — the first date you want. Use a real date, ideally with the DATE function so regional date formats don’t bite you: DATE(2025,1,2).
• end_date — the last date. Leave it out and you get data through the most recent close.
• interval — 0 for daily, 1 for weekly, 2 for monthly.
• headers — 0 for no header row, 1 for headers, 2 for headers plus a ticker label.
• property0, property1, … — which columns you want, by number: 0 Date, 1 Close, 2 Open, 3 High, 4 Low, 5 Volume. You list them in the order you want them to appear.

That last point is the key to charting. A stock chart needs its columns in a specific order, and the property arguments are how you force that order.

Step 1: Pull the data with the right columns

For an Open-High-Low-Close chart, Excel demands the columns in exactly this sequence: Date, Open, High, Low, Close. Not close-first, not alphabetical — that precise order. Get it wrong and the chart won’t build.

The property numbers for that order are 0, 2, 3, 4, 1 (Date, Open, High, Low, Close). So click an empty cell with plenty of room below and to the right, and enter:

=STOCKHISTORY("XNAS:MSFT", DATE(2025,1,1), DATE(2025,3,31), 0, 1, 0, 2, 3, 4, 1)

Press Enter. Excel spills a table: a header row, then one row per trading day, with Date, Open, High, Low, and Close in the order the chart wants. You’ll see a faint blue border around the whole range — that’s the spill boundary, and it tells you which cells the formula owns.

A few practical notes:

• Wrapping the dates in DATE() avoids the classic problem where "1/3/2025" means January 3rd to you and March 1st to Excel.
• Want it to always run to today? Swap the end date for TODAY().
• Don’t overlap the spill range with anything. If there’s data below or to the right, you’ll get a #SPILL! error until you clear the space.

Step 2: Insert the stock chart

Select the entire spilled range, including the header row. Then:

1. Go to the Insert tab.
2. In the Charts group, click the Insert Waterfall, Funnel, Stock, Surface, or Radar Chart button (the icon that looks like a small stock chart).
3. In the dropdown, under Stock, choose Open-High-Low-Close.

Excel builds the candlestick chart. Each day gets a vertical line spanning the low to the high, with a filled box marking the range between open and close. When the close is above the open, the box fills one color; when it’s below, it fills another. That’s the whole visual grammar of the chart, and the candlestick chart guide covers how to read those shapes in detail.

Because the chart is built on a STOCKHISTORY spill, it updates when the data does. New trading day, new candle — no editing required. This is the same dynamic-array principle behind charts that update themselves, applied to live market data.

Step 3: Make it readable

The default chart works, but a few fixes make it genuinely usable.

Fix the colors. Excel’s out-of-the-box up/down candles are often red and green. That’s a bad default: red-green color blindness affects a meaningful share of the population, and for those readers the entire chart collapses into one indistinguishable color. Use blue for up and orange for down instead — they’re distinguishable for nearly everyone, and they’re already in Excel’s default palette. Click a candle to select all the up (or down) candles, then right-click, choose Format Data Series, and set the fill.

Thin the date axis. Pull three months of daily data and the horizontal axis becomes an unreadable smear of dates. Right-click the date axis, choose Format Axis, and increase the interval between labels — showing every fifth or tenth date is usually plenty.

Drop the legend. The auto-generated “Open High Low Close” legend adds nothing once you understand the chart. Click it and delete it.

Add a title that says something. “Chart Title” is not a title. Name it after the security and the period — “MSFT Daily, Q1 2025” — so the chart stands on its own when you paste it into a report or a deck.

Keeping it fast (and not hammering the data feed)

STOCKHISTORY is a connected function, which means every recalculation can trigger a fresh data pull. On a workbook with one chart, you’ll never notice. On a workbook with a dozen tickers, each on its own daily-interval formula stretching back years, recalculation starts to drag and you’re making far more calls to the data service than you need.

A few habits keep it snappy:

• Pull once, reference many times. Instead of writing several STOCKHISTORY formulas that each fetch overlapping data, fetch the full range once and use formulas like INDEX or XLOOKUP to pull specific values out of the spilled array. One fetch, many reads.
• Prefer coarser intervals when you can. If you only need the highest high over three months, a monthly interval returns three data points instead of sixty-odd daily ones — and Excel does far less work. For example, =MAX(STOCKHISTORY("XNAS:MSFT","1/1/2025","3/31/2025",2,0,3)) grabs the high column at a monthly interval and takes the max.
• Cache when the history is fixed. Historical data that won’t change — last year’s prices — doesn’t need to stay live. Once you have the range, copy it and paste as values. The chart keeps working, the recalculation cost disappears, and you’ve got a frozen snapshot that won’t break the day the feed has an outage.
• Reference TODAY() from one cell. If several formulas all use TODAY(), put it in a single cell and point everything at that cell. It reduces redundant recalculation.

None of this matters for a one-chart demo. It matters a lot the moment your workbook grows into something you actually rely on.

The manual method (no Microsoft 365)

If STOCKHISTORY isn’t available — because you’re on Excel 2019, 2021, or 2024, or a work machine with connected features locked down — you can still build the exact same chart. You just supply the data yourself.

1. Get the price history from any source that exports Open, High, Low, and Close per day, and paste it into a sheet.
2. Arrange the columns in the required order: Date, Open, High, Low, Close. This is non-negotiable for a stock chart — the chart type reads columns by position, so a wrong order means an error, not a re-mappable mistake.
3. Select the range and insert the Open-High-Low-Close chart exactly as above.

The chart behaves identically. The only thing you lose is the automatic refresh — update the numbers and the chart follows, but you’re the one updating them.

If you’d rather stack a second visual on top, like a moving-average line over the candles, that’s a combo chart: keep the OHLC series and add your average as a line on the same plot.

Troubleshooting the errors everyone hits

The chart won’t build at all. Almost always a column-order problem. Stock charts are the fussiest chart type in Excel — the columns must be Date, Open, High, Low, Close, in that order, with no stray columns in between. Fix the order and rebuild.

#CONNECT! where your data should be. This means Excel couldn’t reach the data service. Common causes, roughly in order:

• You’re offline, or Connected Experiences is disabled. Check File > Options > Trust Center > Privacy Settings and make sure connected experiences are allowed.
• You’re on a corporate or school network. Firewalls and proxies frequently block Excel’s outbound calls to the data feed. Test on a personal network; if that fixes it, IT needs to whitelist the endpoints.
• The feed itself is down. LSEG’s service has had outages — a widely reported one hit users across Windows, Mac, and the web in early January 2026, and Microsoft confirmed it as a server-side issue with no fix on the user’s end. When this happens, your formula is fine. Wait it out; these usually clear within hours to a couple of days. Don’t reinstall Excel or rewrite the formula chasing a problem that isn’t yours.

#SPILL! — something is blocking the range the formula wants to fill. Clear the cells below and to the right of the formula.

Ghost data after deleting the formula. STOCKHISTORY doesn’t stamp formatting on the cells it spills into, but it does leave the values looking real if you delete the formula carelessly. Always clear the entire spilled range — the whole blue-bordered area — not just the formula cell.

No data for a ticker. Some instruments aren’t in the LSEG database. Direct cryptocurrency prices generally aren’t available, though some crypto-linked ETFs are. If a ticker returns nothing, try the exchange-prefixed form ("XNAS:MSFT") before assuming it’s unsupported.

FAQ

Do I need a paid subscription for this? For STOCKHISTORY, yes — a Microsoft 365 subscription. The perpetual versions (Excel 2019, 2021, 2024) don’t include it. The manual method works in any version.

Is the data real-time? No. It’s recorded after market close and updates roughly once per trading day, a few hours after close. For a live single price, the Stocks data type is the better tool.

Where does the price data come from? LSEG Data & Analytics, formerly Refinitiv. Microsoft provides it as-is, which is why outages occasionally show up as #CONNECT! even when your formula is correct.

Can I chart intraday (hourly) movements? Not with STOCKHISTORY. It works at daily, weekly, and monthly intervals only.

Can I add technical indicators like a moving average? Yes — calculate the average in its own column and add it as a line series over the candles. That’s a combo chart. Keep in mind this is for visualizing the data, not for generating trading decisions.

My candles are red and green and I can’t tell them apart. Why? That’s Excel’s default, and it’s genuinely hard to read for anyone with red-green color blindness. Change the up/down fills to blue and orange in Format Data Series.

This article is for educational purposes only and does not constitute financial, investment, or trading advice. Charts built in Excel illustrate historical data; past price movements are not indicative of future results. No security, product, or course of action is recommended here. Consult a licensed financial professional before making investment decisions.