Excel stock functions transform a plain spreadsheet into a live financial dashboard capable of tracking stock prices, analyzing fundamentals, and monitoring portfolio performance in real time. Whether you are a casual investor checking a few tickers or a professional trader building a multi-screen monitoring setup, Excel provides multiple methods to bring market data into your workbook. In this comprehensive 2026 guide, you will learn every available approach - from Microsoft's built-in STOCK data type and STOCKHISTORY function to Power Query connections and the professional-grade MarketXLS add-in with over 1,000 financial functions. By the end, you will know exactly which excel stock function to use for any market data task.
Excel Stock Functions: The Built-In STOCK Data Type
Microsoft introduced the Stocks linked data type in Excel 365 and Excel 2021. It is the simplest way to surface market data without any add-in or external connection.
How to Enable It
- Type a ticker symbol (e.g.,
AAPL) into a cell - Select the cell and go to the Data tab
- Click Stocks in the Data Types group
- Excel converts the text into a rich, linked data type powered by Refinitiv
Once converted, click the small icon next to the cell to see available fields including Price, Previous Close, 52-Week High, 52-Week Low, Market Cap, P/E Ratio, Volume, and Beta.
Extracting Fields
After converting cell A2 to the Stocks data type, pull fields into adjacent cells:
=A2.Price
=A2.[52-week high]
=A2.[Market cap]
=A2.[P/E]
=A2.Beta
=A2.Volume
Combine with XLOOKUP
Build a reference table with converted Stocks data-type cells in column A and extracted prices in column B. Use =XLOOKUP("AAPL",A:A,B:B) anywhere in your workbook to retrieve Apple's price dynamically.
Limitations
- Data refreshes only when you click Refresh All or reopen the workbook - not real-time
- Requires Microsoft 365 subscription (not available in Excel 2019 or earlier)
- Limited to equities and select indices - no options, futures, or crypto
- No historical time-series - only a single snapshot per field
- Field names vary by locale, which can break formulas in shared workbooks
- Roughly 15 available fields - far fewer than professional data platforms
Excel Stock Functions: The STOCKHISTORY Function
STOCKHISTORY is the primary built-in excel stock function for pulling historical price data into a dynamic spill range.
Syntax
=STOCKHISTORY(stock, start_date, [end_date], [interval], [headers], [property0], [property1], ...)
| Parameter | Description | Values |
|---|---|---|
stock | Ticker symbol | "MSFT" |
start_date | First date | DATE(2025,1,1) |
end_date | Last date (default = today) | TODAY() |
interval | 0 = Daily, 1 = Weekly, 2 = Monthly | 0 |
headers | 0 = None, 1 = Show, 2 = Show with ticker | 1 |
property | 0 = Date, 1 = Close, 2 = Open, 3 = High, 4 = Low, 5 = Volume | Multiple |
Example: Daily Closes for 2025
=STOCKHISTORY("AAPL", DATE(2025,1,2), DATE(2025,12,31), 0, 1, 0, 1)
Returns a two-column table with Date and Close for every trading day.
Example: Monthly OHLCV
=STOCKHISTORY("MSFT", DATE(2020,1,1), TODAY(), 2, 1, 0, 2, 3, 4, 1, 5)
Returns monthly Open, High, Low, Close, and Volume.
Common Errors
- #VALUE! - Ticker not recognized. Double-check the symbol
- #CALC! - Date range contains only non-trading days. Extend the range
- #SPILL! - Cells below the formula contain data. Clear them
- Empty results - International tickers may not be available through Refinitiv
Limitations
- Only available in Microsoft 365
- Data source is Refinitiv - no control over the provider
- No intraday granularity
- No fundamental data (P/E, revenue, dividends)
- No options or derivatives data
- Maximum date range may be limited for some tickers
Excel Stock Functions: Power Query for Stock Data
Power Query (Get & Transform) can connect to REST APIs, CSV feeds, or web pages to import stock data into Excel tables.
Steps
- Go to Data - Get Data - From Other Sources - From Web
- Enter the API endpoint URL (e.g., Alpha Vantage, Twelve Data)
- Power Query opens the navigator - select the data table
- Transform columns (change types, rename headers, filter dates)
- Click Close & Load to push data into a worksheet table
Advantages
- Handles JSON, XML, CSV, and HTML tables
- Supports scheduled background refresh
- Excellent for bulk historical data imports
Disadvantages
- Data lands in a table, not individual cells
- Requires manual or scheduled refresh - not streaming
- API authentication can be complex
- Not practical for real-time dashboards
Excel Stock Functions: GOOGLEFINANCE Comparison
If you also work in Google Sheets, the GOOGLEFINANCE function provides a comparison point. GOOGLEFINANCE is free, supports about 18 attributes (price, volume, P/E, EPS, market cap), and includes basic historical daily/weekly data. However, it is locked to Google Sheets, has 20-minute data delays, no dividend data, no technical indicators, and no options support. For a detailed comparison, see our Google Finance Formulas guide.
Excel Stock Functions: MarketXLS - The Professional Add-In
MarketXLS is a professional add-in for Excel (and Google Sheets) that adds over 1,000 spreadsheet functions covering real-time quotes, historical prices, fundamentals, options chains, Greeks, technical indicators, and more. It transforms Excel into a complete financial analysis platform.
Real-Time Price Functions
| Function | What It Returns | Example |
|---|---|---|
=LAST("AAPL") | Last trade price | Returns current price |
=QM_LAST("AAPL") | Last price via QuoteMedia | Returns current price |
=STREAM_LAST("AAPL") | Streaming real-time price | Auto-updates as trades happen |
=BID("AAPL") | Current bid price | Returns bid |
=ASK("AAPL") | Current ask price | Returns ask |
=PREVIOUSCLOSE("AAPL") | Previous close | Returns yesterday's close |
=CHANGE("AAPL") | Price change | Returns change since close |
=VOLUME("AAPL") | Current volume | Returns today's volume |
=LAST("AAPL") is the simplest excel stock function for getting a current price. Drop it in any cell and MarketXLS returns the most recent trade price. For streaming, auto-updating data, use =STREAM_LAST("AAPL") - prices push to your cell as trades execute without manual refresh.
Historical Data Functions
| Function | What It Returns |
|---|---|
=QM_GETHISTORY("AAPL") | Full OHLCV history table |
=GETHISTORY("AAPL","2025-01-01","2025-12-31","D") | Custom date range |
=OPEN_HISTORICAL("AAPL","2025-06-01") | Historical open price |
=CLOSE_HISTORICAL("AAPL","2025-06-01") | Historical close price |
=HIGH_HISTORICAL("AAPL","2025-06-01") | Historical high |
=LOW_HISTORICAL("AAPL","2025-06-01") | Historical low |
=ADJUSTED_CLOSE_HISTORICAL("AAPL","2025-06-01") | Split/dividend-adjusted close |
=QM_GETHISTORY("AAPL") spills a complete daily OHLCV table into your sheet - one function call replaces manual CSV downloads.
Fundamental Data Functions
| Function | What It Returns |
|---|---|
=PERATIO("AAPL") | P/E ratio |
=FORWARDPE("AAPL") | Forward P/E |
=TRAILINGPE("AAPL") | Trailing P/E |
=PEGRATIO("AAPL") | PEG ratio |
=PRICETOBOOK("AAPL") | Price-to-book ratio |
=MARKETCAPITALIZATION("AAPL") | Market capitalization |
=DIVIDENDYIELD("AAPL") | Dividend yield |
=DIVIDENDPERSHARE("AAPL") | Dividend per share |
=PAYOUTRATIO("AAPL") | Payout ratio |
=PROFITMARGIN("AAPL") | Profit margin |
=OPERATINGMARGIN("AAPL") | Operating margin |
=REVENUEGROWTH("AAPL") | Revenue growth rate |
=BETA("AAPL") | Beta |
=SECTOR("AAPL") | Sector classification |
=INDUSTRY("AAPL") | Industry classification |
=REVENUE("AAPL") | Total revenue |
=TOTALDEBT("AAPL") | Total debt |
=TOTALCASH("AAPL") | Cash on hand |
=BOOKVALUEPERSHARE("AAPL") | Book value per share |
Technical Indicator Functions
| Function | What It Returns |
|---|---|
=RSI("AAPL") | 14-day Relative Strength Index |
=SIMPLEMOVINGAVERAGE("AAPL",50) | 50-day SMA |
=SIMPLEMOVINGAVERAGE("AAPL",200) | 200-day SMA |
=EXPONENTIALMOVINGAVERAGE("AAPL",12) | 12-day EMA |
=EXPONENTIALMOVINGAVERAGE("AAPL",26) | 26-day EMA |
=FIFTYTWOWEEKHIGH("AAPL") | 52-week high |
=FIFTYTWOWEEKLOW("AAPL") | 52-week low |
Options Data Functions
Excel's built-in functions have zero options support. MarketXLS fills this gap completely:
=QM_GETOPTIONCHAIN("^SPX") // Full option chain
=OPTIONSYMBOL("AAPL","2026-06-19","C",200) // Build option symbol
=QM_LAST("@AAPL 260619C00200000") // Option contract price
=QM_GETOPTIONQUOTESANDGREEKS("^SPX") // Full Greeks and IV
=IMPLIEDVOLATILITY("AAPL") // Current implied volatility
Historical Financial Statement Data
=HF_REVENUE("AAPL",2025) // 2025 revenue
=HF_NET_INCOME("AAPL",2025) // 2025 net income
=HF_FREE_CASH_FLOW("AAPL",2025) // 2025 free cash flow
=HF_TOTAL_ASSETS("AAPL",2025) // 2025 total assets
Excel Stock Functions: Head-to-Head Comparison
| Capability | STOCK Data Type | STOCKHISTORY | Power Query | GOOGLEFINANCE | MarketXLS |
|---|---|---|---|---|---|
| Platform | Excel 365 | Excel 365 | Excel (all) | Google Sheets | Excel + Sheets |
| Cost | Included | Included | Included | Free | Subscription |
| Real-time | Delayed snapshot | No | No | 20-min delay | Real-time + streaming |
| Historical | No | Daily/Weekly/Monthly | Via API | Daily/Weekly | Full OHLCV |
| Fundamentals | ~15 fields | No | Via API | ~10 fields | 1,000+ functions |
| Options | No | No | No | No | Full chains + Greeks |
| Technical | No | No | No | No | RSI, SMA, EMA |
| Dividends | Limited | No | Via API | No | Full coverage |
| Streaming | No | No | No | No | Yes |
Excel Stock Functions: Building a Real-Time Dashboard
Here is a step-by-step guide to building a professional stock dashboard using excel stock functions:
Step 1: Set Up Your Ticker List
In column A, list your tickers: AAPL, MSFT, GOOGL, AMZN, NVDA, TSLA, META, JPM, V, UNH.
Step 2: Pull Real-Time Prices
In column B, use MarketXLS:
=LAST(A2)
Copy down for each ticker. For streaming, use =STREAM_LAST(A2) instead.
Step 3: Add Key Metrics
| Column | Formula | Description |
|---|---|---|
| C | =PREVIOUSCLOSE(A2) | Previous close |
| D | =CHANGE(A2) | Daily change |
| E | =VOLUME(A2) | Trading volume |
| F | =PERATIO(A2) | P/E ratio |
| G | =BETA(A2) | Beta |
| H | =RSI(A2) | RSI (14-day) |
| I | =SIMPLEMOVINGAVERAGE(A2,50) | 50-day SMA |
| J | =DIVIDENDYIELD(A2) | Dividend yield |
Step 4: Add Conditional Formatting
- Highlight RSI cells red when > 70 (overbought) and green when < 30 (oversold)
- Highlight dividend yields above 3% in green
- Highlight negative daily changes in red
Step 5: Create Charts
Select price and SMA columns to create a combo chart overlaying the moving average on daily prices. Add a volume bar chart below for context.
Step 6: Add a Historical Data Sheet
On a separate sheet, use =QM_GETHISTORY(A2) to spill full OHLCV history for any ticker. Reference this data in charts or analysis formulas on other sheets.
Step 7: Add an Options Monitor
On a third sheet:
=QM_GETOPTIONCHAIN("^SPX")
This pulls the full S&P 500 options chain with strikes, bids, asks, volume, open interest, and Greeks.
Automating Refresh
MarketXLS supports on-demand refresh via its ribbon. Set your workbook to refresh all data connections at a specified interval through Data - Connections - Properties. For =STREAM_LAST() functions, no manual refresh is needed - data pushes automatically.
Excel Stock Functions: Tips for Maximum Performance
- Use named ranges for ticker lists so formulas stay readable
- Combine STOCKHISTORY with XLOOKUP to pull a specific date's close from a spilled range
- Put
=TODAY()in one cell and reference it everywhere to reduce recalculation overhead - Cache historical data by pasting values once you have the range you need
- Use Excel Tables (Ctrl+T) so new tickers automatically inherit formulas
- Use
=LAST()for snapshots and=STREAM_LAST()only for actively monitored tickers - Explore MarketXLS templates - over 100 pre-built templates for portfolio tracking, options analysis, and screening
Excel Stock Functions: Download Templates
We have created ready-to-use templates demonstrating every excel stock function discussed in this guide:
- - Contains sample data and formula references
- - Contains live formulas (requires MarketXLS add-in)
Each workbook includes six sheets: How To Use, Main Dashboard, Scenario Analysis, Strategy, Portfolio Comparison, and Correlation Matrix.
Excel Stock Functions: Frequently Asked Questions
Is there a free stock function built into Excel?
Yes. If you have Microsoft 365, the Stocks data type and the STOCKHISTORY function are included at no extra cost. They provide delayed quotes and historical daily prices for most major equities. For real-time streaming, options data, dividends, or technical indicators, you will need an add-in like MarketXLS.
How do I get real-time stock prices in Excel?
The fastest method is to use the MarketXLS function =LAST("AAPL") for the latest trade price, or =STREAM_LAST("AAPL") for continuously streaming real-time prices. Excel's built-in Stocks data type provides only delayed snapshots that require manual refresh.
Can I pull options data into Excel?
Excel's built-in functions do not support options. MarketXLS fills this gap with =QM_GETOPTIONCHAIN("^SPX") for full option chains and =QM_GETOPTIONQUOTESANDGREEKS("^SPX") for Greeks, implied volatility, and bid/ask data. You can also build option symbols programmatically with =OPTIONSYMBOL().
Does STOCKHISTORY work in Excel 2019?
No. The STOCKHISTORY function requires Microsoft 365 (formerly Office 365). It is not available in perpetual-license versions like Excel 2019 or 2016. For those versions, MarketXLS or Power Query are the best alternatives for historical stock data.
What is the difference between =LAST() and =STREAM_LAST() in MarketXLS?
=LAST("AAPL") returns the most recent trade price when you refresh your workbook. It is a snapshot - the value stays static until the next refresh. =STREAM_LAST("AAPL") opens a streaming connection that continuously updates the cell as new trades execute. Use =LAST() for end-of-day analysis and =STREAM_LAST() for active intraday monitoring.
How many stocks can I track at once?
There is no hard limit with either built-in functions or MarketXLS. Performance depends on system resources and data plan. The built-in Stocks data type may slow down with hundreds of converted cells. MarketXLS is optimized for large watchlists. For best performance, reserve =STREAM_LAST() for actively monitored tickers and use =LAST() for the rest.
Excel Stock Functions: Summary
Excel stock functions have evolved significantly - from the basic Stocks data type to STOCKHISTORY, Power Query, and professional add-ins. For casual investors, the built-in tools in Microsoft 365 handle simple price lookups and historical charts. For professionals who need real-time streaming, deep fundamentals, options chains, technical indicators, and dividend analysis, MarketXLS transforms Excel into a complete financial analysis platform.
The best approach is to layer these tools: use STOCKHISTORY for quick historical pulls, Power Query for bulk imports, and MarketXLS functions like =LAST(), =PERATIO(), =RSI(), and =QM_GETHISTORY() for everything else. With the right combination of excel stock functions, your spreadsheet becomes the only financial dashboard you need.
Ready to explore professional excel stock functions? Visit MarketXLS to see available plans and unlock 1,000+ financial functions.
