Stock price in Excel is one of the most common things any self-directed investor, financial advisor, or analyst tries to set up and one of the most frustrating things to get right when the default options keep breaking. Microsoft's native Stocks data type is delayed, geographically limited, and gives you a small slice of fields. Yahoo Finance scraping is fragile and against terms of service. The result is that most people end up copy-pasting prices into a spreadsheet every morning and calling it a portfolio tracker.
This guide takes a different approach. We will look at what is actually possible when you have a true Excel-native market data layer sitting next to your formulas: real-time last trades, streaming quotes, full OHLC history as a single spilled array, 50 and 200 day moving averages, RSI, beta, dividend yield, P/E, EPS, sector, and a dozen other fields, all referencing a ticker in column A. We will show the exact formulas, where they fit in a workbook, and what to do with them. There is also a free downloadable template at the bottom that already has the structure laid out so you can drop in your own watchlist and refresh.
Stock Price in Excel: The Quick Comparison
Before we dig in, here is a quick read on the three common ways people pull a stock price into Excel today.
| Method | Real-Time? | History? | Fields Available | Stability |
|---|---|---|---|---|
| Excel Stocks data type (native) | 15-20 min delayed | No native history function | About 30 fields, US-centric | Breaks when geo restricted or Microsoft updates fields |
| Web scraping (Yahoo, Google Finance) | Delayed, varies | Possible but unstable | Whatever you scrape | Fragile, violates terms, breaks on layout changes |
| MarketXLS add-in | Real-time and streaming | Yes, full OHLC as array | 1,100+ functions across price, fundamentals, options, technicals | Stable, supported, refreshes inside Excel |
The native Stocks data type works for casual look-ups. Scraping is not something we recommend at all because it routinely violates the source's terms of service and you spend more time fixing the spreadsheet than analyzing the data. For anything that needs to refresh reliably, support more than a small US watchlist, or feed into other formulas, you need a proper data layer that exposes itself as Excel functions. That is the gap MarketXLS fills.
What "Real-Time" Actually Means
Before throwing formulas around it is worth being precise about what real-time means in Excel. There are three useful definitions:
- Snapshot quote. You ask Excel for the current price, Excel returns the last traded price at that moment, and the cell does not update until you refresh.
- Streaming quote. Excel keeps a subscription open to the exchange feed and updates the cell tick by tick during market hours.
- Historical close. You ask for an OHLC series across a date range. This does not change second to second; it changes once per session.
Different parts of a workbook need different definitions. A position sizing sheet calculating dollar value of 50 shares can use a snapshot. A scrolling watchlist on a second monitor wants a stream. A backtest or 200 day moving average wants history. MarketXLS exposes a function for each.
| Use Case | Formula | Behavior |
|---|---|---|
| Latest snapshot | =QM_Last("AAPL") | Pulls last traded price on refresh |
| Live streaming during market hours | =Stream_Last("AAPL") | Updates tick by tick |
| Alias for latest quote | =Last("AAPL") | Same as QM_Last, shorter name |
| Full OHLC history | =QM_GetHistory("AAPL") | Spills an array of historical bars |
Getting Started: One Cell, One Formula
The simplest possible version of stock price in Excel is one cell. Install MarketXLS, sign in, drop the following into A1:
=QM_Last("AAPL")
Hit enter and refresh. The cell now holds the last traded price of Apple. Change "AAPL" to "MSFT" and refresh again. That is your foundation. Everything else in this guide is just building outward from that pattern: replace the hardcoded ticker with a reference to a cell, then reference that same cell from a second formula, then a third, until a single row of inputs feeds a whole dashboard.
A1: AAPL
B1: =QM_Last(A1) -> 228.41
C1: =SimpleMovingAverage(A1,"50") -> 219.10
D1: =SimpleMovingAverage(A1,"200") -> 207.85
E1: =RelativeStrengthIndex(A1,"14") -> 58.4
F1: =Beta(A1) -> 1.21
G1: =DividendYield(A1) -> 0.44%
H1: =PERatio(A1) -> 32.4
I1: =MarketCapitalization(A1) -> 3,490B
That is eight live data points from one ticker. Drag the row down to add ten more symbols and you have a screener.
A 12-Symbol Watchlist Snapshot
To make this concrete, here is the same row repeated for twelve common large-caps as of 2026-05-21. Every value below comes from the MarketXLS formula listed in the rightmost column of the sample workbook. The point is not to suggest these names are buys or sells. The point is to show what a one-page watchlist looks like when each cell is a live formula instead of a manual paste.
| Ticker | Sector | Last | 50 DMA | 200 DMA | RSI 14 | Beta | Div Yield |
|---|---|---|---|---|---|---|---|
| AAPL | Technology | $228.41 | $219.10 | $207.85 | 58.4 | 1.21 | 0.44% |
| MSFT | Technology | $452.18 | $442.05 | $415.60 | 61.7 | 0.93 | 0.70% |
| NVDA | Technology | $134.92 | $128.40 | $110.66 | 62.9 | 1.65 | 0.03% |
| AMZN | Consumer Disc | $198.50 | $190.22 | $175.45 | 59.2 | 1.18 | 0.00% |
| GOOGL | Comm Services | $178.24 | $172.95 | $158.30 | 57.6 | 1.06 | 0.46% |
| JPM | Financials | $214.66 | $208.55 | $196.30 | 55.1 | 1.09 | 2.14% |
| JNJ | Health Care | $155.10 | $153.72 | $158.40 | 47.2 | 0.55 | 3.35% |
| XOM | Energy | $116.45 | $113.20 | $110.80 | 54.8 | 0.92 | 3.35% |
| KO | Cons Staples | $68.74 | $67.22 | $64.10 | 56.2 | 0.58 | 3.01% |
| PG | Cons Staples | $169.18 | $166.55 | $162.20 | 53.7 | 0.41 | 2.37% |
| TSLA | Consumer Disc | $178.85 | $192.40 | $220.55 | 41.8 | 2.31 | 0.00% |
| UNH | Health Care | $512.30 | $524.18 | $540.62 | 44.1 | 0.65 | 1.62% |
Look at the structure rather than the levels. Most of the megacap technology names are trading above both their 50 and 200 day moving averages with RSI in the high 50s and low 60s, which is what an uptrend looks like in the data. Tesla and UnitedHealth are sitting below both moving averages with RSI in the low 40s, which is what a corrective phase looks like. None of this is investment advice. It is just an example of how a watchlist that updates itself surfaces structural reads that would be invisible if every cell were stale.
The Building Blocks: Functions You Will Actually Use
The MarketXLS library has over 1,100 functions. Most workbooks only need ten to fifteen of them. Here are the ones that come up in nearly every stock-price-in-Excel project, all verified in the function database before being listed.
Price and Quote
=QM_Last("AAPL") -> last traded price (snapshot)
=Stream_Last("AAPL") -> live streaming price during market hours
=Last("AAPL") -> alias for QM_Last
=FiftyTwoWeekHigh("AAPL") -> highest price in the trailing 52 weeks
=FiftyTwoWeekLow("AAPL") -> lowest price in the trailing 52 weeks
Technicals
=SimpleMovingAverage("AAPL","50") -> 50 day SMA
=SimpleMovingAverage("AAPL","200") -> 200 day SMA
=RelativeStrengthIndex("AAPL","14") -> 14 period RSI
=Beta("AAPL") -> beta vs market
Fundamentals
=PERatio("AAPL") -> trailing P/E ratio
=EarningsPerShare("AAPL") -> trailing EPS
=DividendYield("AAPL") -> trailing dividend yield
=Revenue("AAPL") -> trailing revenue
=MarketCapitalization("AAPL") -> market cap in dollars
=ReturnOnEquity("AAPL") -> trailing ROE
=OperatingMargin("AAPL") -> trailing operating margin
=TotalDebtToEquity("AAPL") -> debt/equity ratio
=Sector("AAPL") -> sector name
=Industry("AAPL") -> industry name
History
=QM_GetHistory("AAPL") -> returns an array of OHLC rows
QM_GetHistory is the one that surprises people. In modern Excel (Microsoft 365 with dynamic arrays) one cell spills into a multi-column, multi-row history. You can then chart it, run AVERAGE, STDEV, CORREL, or any other built-in Excel function across the spilled range. You do not need a separate database, a SQL connection, or a CSV download workflow. The history is right there next to the live price.
Building a Stock Price in Excel Dashboard, Step by Step
Here is the workflow we used to construct the downloadable template. Six sheets, each with a clear purpose, all driven by a single column of tickers.
Sheet 1: How To Use
A page that explains the workbook to its future self. Purpose, data sources, refresh instructions, links to the MarketXLS website at https://marketxls.com and the demo booking page at https://marketxls.com/book-demo. Boring but important. Six months from now you will not remember which cell drives what.
Sheet 2: Live Quotes
Column A is the ticker input (yellow background, bold border so users know to edit it). The remaining columns are live formulas:
Col B: =Name(A5) -> company name
Col C: =Sector(A5) -> sector
Col D: =QM_Last(A5) -> snapshot price
Col E: =Stream_Last(A5) -> streaming price (during market hours)
Col F: =FiftyTwoWeekLow(A5) -> 52 week low
Col G: =FiftyTwoWeekHigh(A5) -> 52 week high
Col H: =MarketCapitalization(A5) -> market cap
Col I: =Beta(A5) -> beta
Col J: =DividendYield(A5) -> dividend yield
This is the page most users live on. Change a ticker in column A, hit refresh, and ten data points update.
Sheet 3: Historical Data
The interesting trick on this sheet is using QM_GetHistory in column G. Once dynamic arrays are involved you can write:
=QM_GetHistory(A5)
and the cell spills a multi-row OHLC history starting at G5. The columns to the left compare last price to the 50 and 200 day moving averages, expressed both in dollars and as a percentage delta. That tells you trend strength at a glance.
Sheet 4: Technical Snapshot
Same row of formulas, but with an extra column that returns "Uptrend", "Downtrend", or "Mixed" based on whether last is above both moving averages, below both, or between them.
=IF(AND(B5>C5,C5>D5),"Uptrend",IF(AND(B5<C5,C5<D5),"Downtrend","Mixed"))
That single formula condenses three rows of moving average inspection into one English word. Combine it with the RSI column and you have a structural read that does not require an opinion.
Sheet 5: Fundamentals
Live P/E, EPS, dividend yield, revenue, ROE, and operating margin for every ticker in column A. The point of this sheet is to make sure that whatever you are looking at on the price side has a fundamental backdrop you can describe in one row. You do not need to commit to any particular valuation framework to benefit from having the numbers visible.
Sheet 6: Portfolio Tracker
Yellow input cells for ticker, shares, and average cost. Live MarketXLS formulas for last price, sector, and dividend yield. Calculated columns for market value, cost basis, P/L, and weight.
Col D: =QM_Last(A6) -> live last price
Col E: =B6*D6 -> market value
Col F: =B6*C6 -> cost basis
Col G: =E6-F6 -> P/L
Col H: =E6/SUM($E$6:$E$13) -> weight in portfolio
Eight rows, a totals row at the bottom, and a refresh button. That is a working portfolio tracker that is no more complex than what most investors already maintain by hand, except every price is live and every weight is right.
How This Compares to the Native Excel Stocks Data Type
Microsoft 365 ships with a Stocks linked data type that lets you type a ticker, convert it to a stock object, and reference fields with the dot notation:
=A1.Price
=A1.[52 Week High]
=A1.Industry
It is genuinely useful and, importantly, free with Office 365. So when does it stop being enough?
| Capability | Native Stocks Data Type | MarketXLS |
|---|---|---|
| Last price | Yes, 15-20 minute delayed | Yes, real-time snapshot or streaming |
| Historical OHLC | No native function | Yes, single spilled array via QM_GetHistory |
| Streaming during market hours | No | Yes, Stream_Last |
| Technicals (SMA, RSI, MACD) | No | Yes, library of indicator functions |
| Options data | No | Yes, full chain via QM_GetOptionChainActive |
| Non-US tickers and many indexes | Limited geographic coverage | Broader coverage |
| Backtesting and scenario logic | Not without manual data | Yes, via spilled history into Excel formulas |
The native data type is fine for a one-off look-up. The moment you want to chart history, calculate a moving average without typing 200 values, monitor a watchlist during the day, or analyze options, you outgrow it. That is the threshold MarketXLS was built for.
A Note on Excel "Stock Price" Workarounds That Will Bite You
A few patterns we see on Reddit and forums that you should avoid:
- Pulling prices from any consumer finance website that has not licensed the data for use in Excel. Most consumer sites prohibit this in their terms of service, the underlying HTML changes regularly, and any workbook you build on top of it stops working without warning. We do not provide instructions for this and we recommend you do not build on it.
- Manually typing prices each morning. Aside from being slow, the data is stale the moment you save the file, and any moving average or correlation you calculate is based on a number that does not match what is on the broker screen.
- Using random "free API" keys posted online. Free tiers are usually 5-15 minute delayed, low volume, and unstable. By the time you wire up retry logic and key rotation you have spent more time than a real data layer would have cost.
The MarketXLS approach is the opposite. One install, one sign-in, and the formulas just work. The data is licensed and supported, which matters when your workbook drives real decisions.
The Template
The downloadable template at the bottom of this post is two files. Both are linked. Use the sample first to see what the output looks like, then open the live template to start customizing.
Stock_Price_In_Excel_Sample.xlsx
A static snapshot of the 12-symbol watchlist as of 2026-05-21. Every value is pre-filled, but each row has a "Formula Reference" column that shows you which MarketXLS function produced that number. Six sheets: How To Use, Live Quotes, Historical Data, Technical Snapshot, Fundamentals, Portfolio Tracker. Open this if you want to see the layout without installing anything.
Stock_Price_In_Excel_Template.xlsx
The live workbook. Same six sheets, but every data cell is a MarketXLS formula referencing column A. Change a ticker, refresh, and the whole row updates. Yellow cells are inputs. Everything else is formula-driven.
Download the templates:
- - Pre-filled static snapshot as of 2026-05-21
- - Live formula version, refreshes on demand
Frequently Asked Questions
How do I get a real-time stock price in Excel?
Install MarketXLS and use =QM_Last("TICKER") for a snapshot or =Stream_Last("TICKER") for a streaming quote during market hours. Both functions reference a cell, so =QM_Last(A1) will pull whatever ticker is in A1.
Can I get historical stock prices in Excel as a single formula?
Yes. =QM_GetHistory("AAPL") returns an array of OHLC rows that spills into the cells below and to the right (in Microsoft 365 with dynamic arrays). You can then run AVERAGE, STDEV, CORREL, or any other Excel function across the spilled range.
Is the Microsoft Excel Stocks data type real-time?
No. The native Stocks data type returns delayed quotes (typically 15-20 minutes during market hours) and does not stream. It also has no native historical price function and limited international coverage. For real-time and historical data inside Excel, you need an add-in like MarketXLS.
Which MarketXLS function should I use for live streaming versus a snapshot?
Use =Stream_Last(symbol) for tick-by-tick streaming during market hours and =QM_Last(symbol) for a snapshot that updates only when you refresh. The streaming function is useful for a watchlist you keep open on a second monitor. The snapshot function is faster and lighter for larger workbooks where you do not need every cell ticking.
Can I track international stocks and ETFs with MarketXLS?
MarketXLS supports a broad range of US and international exchanges and a deep set of ETFs and indices. For specific coverage questions for a ticker you care about, the easiest path is to book a quick demo at https://marketxls.com/book-demo where the team can confirm coverage live.
Do I need to know VBA or Power Query to use these formulas?
No. Every function in this guide is just a regular Excel formula. You type it into a cell the same way you would type =SUM(A1:A10). No macros, no Power Query, no scripting required. If you can write an Excel formula, you can build a live stock price dashboard.
The Bottom Line
Stock price in Excel does not have to mean a stale data type, a fragile scraping script, or a paste-from-the-broker ritual every morning. With a true Excel-native market data layer you write one formula per data point, refresh the workbook, and get a live view of your watchlist, your technicals, your fundamentals, and your portfolio in one file. The functions are stable, the data is licensed, and the structure is portable to every spreadsheet you already know how to build.
If you want to go deeper than the downloadable template above, the full MarketXLS function library is browsable at https://marketxls.com and the team is happy to walk through your specific use case on a demo call at https://marketxls.com/book-demo. For pricing details see https://marketxls.com/pricing.
This guide is educational. Nothing here is a recommendation to buy, sell, or hold any security. Always do your own research and consult a licensed advisor for decisions about your portfolio.
