Stock Portfolio Tracker in Excel: Live Prices and P&L

Stock portfolio tracker in Excel: start from our portfolio tracker template or build your own. List each holding with its ticker, shares and purchase price, pull the current price into the next column with a formula such as =Last("AAPL"), and calculate market value (=shares × price), cost, and gain/loss in dollars and percent. The MarketXLS add-in supplies the price, dividend, fundamental and technical data through formulas, so the tracker updates without copying prices by hand; plans start at $70/month billed annually (Standard, with 15-minute delayed US stock quotes), and real-time streaming needs the Advanced or Business plan.

This guide walks through the core table, a portfolio summary, then optional sections for fundamentals, dividend income, technical signals, sector allocation, benchmark comparison, risk, options positions, multi-account consolidation and rebalancing.

Want this live in your own workbook? MarketXLS adds =Last, =QM_Stream_Last and 1,000+ other formulas to Excel. See plans and pricing.

Why Use a Stock Portfolio Tracker in Excel?

An Excel portfolio tracker lets you customize columns, build your own metrics and combine multiple accounts, which most brokerage portfolio views do not allow. The main benefits:

• Multi-account consolidation: track stocks, ETFs, options and bonds from multiple brokerages in one dashboard
• Custom metrics: calculate portfolio-specific ratios, weighted averages and custom scores
• Historical analysis: track performance over time with charts
• Dividend income planning: project dividend income from current holdings
• Tax lot tracking: monitor cost basis, holding periods and capital gains
• Market data in cells: pull prices, fundamentals and technical indicators into cells
• Full ownership: your data stays in your spreadsheet

Web Apps vs. Excel: What You Gain

Setting Up Your Stock Portfolio Tracker in Excel

Step 1: Define Your Portfolio Structure

Start with a clean spreadsheet. Create columns for the essential data points every portfolio tracker needs:

Step 2: Pull Live Stock Prices with MarketXLS

The current-price column drives every other calculation. With MarketXLS installed, one formula returns the price:

=Last("AAPL")

Formula documentation: Last

=Last returns a snapshot that updates when Excel recalculates (15-minute delayed on the Standard plan). In the Microsoft 365 add-in for Mac and Excel for the web, write it as =mxls.Last("AAPL").

For streaming prices that update automatically while streaming is on (real-time on the Advanced and Business plans):

=QM_Stream_Last("AAPL")

Formula documentation: QM_Stream_Last

Step 3: Build Your Core Formulas

With live prices flowing in, build the calculation columns:

Market Value (Column G):

=C2 * F2

Where C2 is shares and F2 is =Last("AAPL").

Total Cost (Column H):

=C2 * D2

Gain/Loss in Dollars (Column I):

=G2 - H2

Gain/Loss Percentage (Column J):

=(G2 - H2) / H2

Day Change (Column K):

=Change("AAPL")

Formula documentation: Change

Dividend Yield (Column L):

=DividendYield("AAPL")

Formula documentation: DividendYield

Step 4: Add Portfolio Summary Section

Above your holdings table, create a summary dashboard:

Total Portfolio Value:     =SUM(G2:G50)
Total Cost Basis:          =SUM(H2:H50)
Total Gain/Loss ($):       =SUM(I2:I50)
Total Gain/Loss (%):       =SUM(I2:I50) / SUM(H2:H50)
Number of Holdings:        =COUNTA(A2:A50)
Average Dividend Yield:    =AVERAGE(L2:L50)

Adding Fundamental Data to Your Portfolio Tracker

Fundamental data next to each holding shows valuation, financial health and dividends in the same row. MarketXLS has 1000+ functions; these are the common ones:

Valuation Metrics

=PERatio("AAPL")           → Price-to-Earnings ratio
=PriceToBook("AAPL")       → Price-to-Book ratio
=PriceToSales("AAPL")      → Price-to-Sales ratio
=EnterpriseValue("AAPL")   → Enterprise Value

Formula documentation: PERatio, PriceToBook, PriceToSales, EnterpriseValue

Financial Health

=Revenue("AAPL")                  → Annual revenue
=MarketCapitalization("AAPL")     → Market cap
=TotalDebtToEquity("AAPL")        → Debt-to-Equity ratio
=Current_Ratio("AAPL")            → Current ratio

Formula documentation: Revenue, MarketCapitalization, TotalDebtToEquity, Current_Ratio

Dividend Analysis

=DividendPerShare("AAPL")         → Annual dividend per share
=DividendYield("AAPL")            → Current dividend yield
=PayoutRatio("AAPL")              → Payout ratio
=Ex_DividendDate("AAPL")          → Next ex-dividend date

Formula documentation: DividendPerShare, DividendYield, PayoutRatio, Ex_DividendDate

Analyst Estimates

=OneYrTargetPrice("AAPL")         → Average 1-year price target
=NumberOfAnalysts("AAPL")         → Number of covering analysts
=EPSEstimateCurrentYear("AAPL")   → Current year EPS estimate

Formula documentation: OneYrTargetPrice, NumberOfAnalysts, EPSEstimateCurrentYear

Example: Enhanced Holdings Table

Building a Dividend Income Dashboard

For income-focused investors, tracking dividend income is essential. Your stock portfolio tracker Excel spreadsheet can project future income:

Annual Dividend Income per Holding

=DividendPerShare("AAPL") * C2

Formula documentation: DividendPerShare

Where C2 is your number of shares. This gives you the annual dividend income from each position.

Quarterly and Monthly Income Projection

=DividendPerShare("AAPL") * C2 / 4

Formula documentation: DividendPerShare

Most US stocks pay quarterly dividends, so dividing annual dividends by 4 gives the income per quarterly payment. Divide by 12 instead for an average monthly figure, and adjust for stocks with other payment schedules.

Dividend Growth Tracking

Create a separate tab to track dividend growth over time:

Dividend Calendar View

=Ex_DividendDate("AAPL")
=Ex_DividendDate("MSFT")
=Ex_DividendDate("JNJ")

Formula documentation: Ex_DividendDate

Sort by ex-dividend date to see upcoming payments and plan cash flow.

Technical Analysis in Your Portfolio Tracker

Add technical indicators to identify entry and exit points for your holdings:

Key Technical Indicators

=RSI("AAPL")                          → Relative Strength Index
=SimpleMovingAverage("AAPL", 50)      → 50-day SMA
=SimpleMovingAverage("AAPL", 200)     → 200-day SMA
=TwentyDayVolatility("AAPL")          → 20-day volatility
=Beta("AAPL")                         → Beta vs. market

Formula documentation: RSI, SimpleMovingAverage, TwentyDayVolatility, Beta

Signal Column

Create a simple signal column using conditional logic:

=IF(AND(RSI("AAPL")<30, Last("AAPL")>SimpleMovingAverage("AAPL",200)), "OVERSOLD - REVIEW",
 IF(RSI("AAPL")>70, "OVERBOUGHT - REVIEW", "HOLD"))

Formula documentation: RSI, Last, SimpleMovingAverage

Technical Dashboard

Sector and Allocation Analysis

Understanding your portfolio's sector exposure is critical for diversification. Build a sector allocation dashboard:

Sector Mapping

=Sector("AAPL")        → Technology
=Industry("AAPL")      → Consumer Electronics

Formula documentation: Sector, Industry

Allocation by Sector

Use SUMIF formulas to calculate sector weights:

Technology Weight:  =SUMIF(SectorColumn, "Technology", ValueColumn) / TotalPortfolioValue
Healthcare Weight:  =SUMIF(SectorColumn, "Healthcare", ValueColumn) / TotalPortfolioValue
Financials Weight:  =SUMIF(SectorColumn, "Financials", ValueColumn) / TotalPortfolioValue

Concentration Risk Check

Flag any position that exceeds your target allocation:

=IF(G2/SUM(G$2:G$50) > 0.10, "OVERWEIGHT", "OK")

This flags any single position exceeding 10% of your portfolio.

Historical Performance Tracking

Track your portfolio's performance over time using historical data:

Pull Historical Prices

=GetHistory("AAPL", "2025-01-01", "2026-01-01", "Daily")

Formula documentation: GetHistory

This returns a full history of daily prices, which you can use to calculate historical portfolio value and track performance against benchmarks.

Benchmark Comparison

Compare your portfolio returns against the S&P 500:

Portfolio Start Value:  Manual entry (sum of cost basis)
Portfolio Current Value: =SUM(G2:G50)
Portfolio Return:       =(Current - Start) / Start

SPY Start:             =Close_Historical("SPY", DATE(2025,1,2))
SPY Current:           =Last("SPY")
SPY Return:            =(Current - Start) / Start

Excess Return:         =Portfolio Return - SPY Return

Formula documentation: Close_Historical, Last

Risk Analysis Features

Advanced portfolio trackers include risk metrics:

Beta Calculation

=Beta("AAPL")          → Stock's beta vs. market

Formula documentation: Beta

Calculate weighted portfolio beta:

Portfolio Beta = SUMPRODUCT(WeightColumn, BetaColumn)

Volatility Monitoring

=TwentyDayVolatility("AAPL")    → 20-day volatility
=StockVolatilityThirtyDays("AAPL")  → 30-day volatility

Formula documentation: TwentyDayVolatility, StockVolatilityThirtyDays

Correlation Matrix

Track how your holdings move relative to each other. If multiple positions are highly correlated, your diversification is weaker than it appears.

Create a correlation matrix tab using MarketXLS functions to pull historical returns for each holding, then calculate pairwise correlations.

Options Portfolio Tracking

If you trade options alongside stocks, extend your stock portfolio tracker in Excel to handle options positions:

Option Position Tracking

=OptionSymbol("AAPL", "2026-06-19", "C", 200)

Formula documentation: OptionSymbol

This generates the standardized option symbol. Then pull the current price:

=QM_Last("@AAPL 260619C00200000")

Formula documentation: QM_Last

Covered Call Monitoring

Options P&L Dashboard

Track all options trades with entry price, current price, and Greeks:

=QM_GetOptionQuotesAndGreeks("AAPL")

Formula documentation: QM_GetOptionQuotesAndGreeks

This returns the full option chain with Greeks, helping you monitor delta exposure, theta decay, and implied volatility across your options positions.

Automating Your Portfolio Tracker

Auto-Refresh Settings

On-demand MarketXLS functions such as =Last update when Excel recalculates, including when you open the file. For continuous updates during market hours, use streaming functions (real-time on Advanced and Business plans):

=QM_Stream_Last("AAPL")

Formula documentation: QM_Stream_Last

Conditional Formatting

Set up visual alerts in your stock portfolio tracker Excel spreadsheet:

• Green: position up more than 5%
• Red: position down more than 5%
• Yellow: RSI below 30 (oversold) or above 70 (overbought)
• Bold: dividend ex-date within 7 days

Alert Formulas

=IF(Change("AAPL") < -0.05, "⚠️ DOWN 5%+", "")
=IF(RSI("AAPL") < 30, "📉 OVERSOLD", IF(RSI("AAPL") > 70, "📈 OVERBOUGHT", ""))

Formula documentation: Change, RSI

Multi-Account Portfolio Consolidation

One of the biggest advantages of an Excel stock portfolio tracker is combining multiple brokerage accounts:

Account Structure

Create separate tabs for each account:

• Tab 1: Fidelity IRA — Retirement holdings
• Tab 2: Schwab Brokerage — Taxable account
• Tab 3: Vanguard 401k — Employer plan
• Tab 4: Consolidated — Combined view with SUMIF formulas

Consolidated View Formula

Total AAPL Shares: =SUMIF(Fidelity!A:A, "AAPL", Fidelity!C:C) + SUMIF(Schwab!A:A, "AAPL", Schwab!C:C)

This approach gives you a unified view across all accounts while maintaining separate tracking for tax purposes.

Portfolio Rebalancing Tool

Build a rebalancing calculator into your stock portfolio tracker:

Target Allocation vs. Actual

Rebalancing Calculator

Shares to Trade = (Target Value - Current Value) / Current Price
Target Value = Target % × Total Portfolio Value

Downloading a Ready-Made Template

While building a stock portfolio tracker in Excel from scratch is rewarding, MarketXLS provides pre-built templates that include:

• Portfolio dashboard with real-time prices
• Dividend income tracker
• Sector allocation charts
• Performance vs. benchmark comparison
• Options position monitor
• Risk analysis dashboard

Visit MarketXLS templates to download ready-made portfolio trackers that you can customize to your needs.

Best Practices for Portfolio Tracking in Excel

Data Organization

• Keep one ticker per row — never combine positions
• Use named ranges for key cells (PortfolioValue, TotalCost)
• Separate input cells (shares, cost basis) from formula cells
• Color-code: blue for inputs, black for formulas

Performance Tips

• Use =Last() for snapshot tracking and =QM_Stream_Last() only when you need streaming (each streamed symbol uses one live symbol subscription)
• Limit streaming formulas to positions you actively monitor
• Archive historical snapshots monthly in separate tabs
• Keep your active portfolio tab under 100 rows for speed

Security

• Password-protect your spreadsheet
• Do not share files containing actual portfolio positions
• Keep backup copies on a separate drive
• Use generic examples when sharing templates with others

Stock Portfolio Tracker Excel vs. Alternative Tools

Frequently Asked Questions

How do I automatically update stock prices in my Excel portfolio tracker?

Install the MarketXLS add-in for Excel, then use =Last("AAPL") in any cell to pull the current stock price. The value updates when Excel recalculates, including when you open the file or refresh. For continuous updates during market hours, use =QM_Stream_Last("AAPL") (real-time streaming requires the Advanced or Business plan).

Can I track stocks from multiple brokerage accounts in one Excel spreadsheet?

Yes. Create separate tabs for each brokerage account (Fidelity, Schwab, Vanguard, etc.) with your holdings in each. Then build a consolidated tab that uses SUMIF formulas to combine positions across accounts. This gives you a unified portfolio view while maintaining separate records for tax reporting.

What is the best way to track dividend income in an Excel portfolio tracker?

Use =DividendPerShare("AAPL") to get the annual dividend, multiply by your shares for total income, and use =Ex_DividendDate("AAPL") to track upcoming payment dates. Create a dedicated dividend tab that calculates monthly income projections, yield on cost, and dividend growth rates for each holding.

How do I compare my portfolio performance against the S&P 500 in Excel?

Pull your portfolio's starting value (total cost basis) and current value (sum of market values). Then pull SPY's close on your start date using =Close_Historical("SPY", DATE(2025,1,2)) and the current price using =Last("SPY"). Calculate percentage returns for both and subtract to find your excess return over the benchmark.

Can I track options positions in the same Excel portfolio tracker?

Yes. MarketXLS supports full options tracking. Use =OptionSymbol("AAPL", "2026-06-19", "C", 200) to generate option symbols, then =QM_Last() with the symbol to get current option prices. You can also pull the full option chain with Greeks using =QM_GetOptionQuotesAndGreeks("AAPL") to monitor delta, theta, and implied volatility alongside your stock positions.

Is an Excel portfolio tracker better than free online portfolio trackers?

An Excel portfolio tracker is more flexible than most free online trackers: you control the columns and formulas, can consolidate multiple accounts, and keep the data in your own file. Free online trackers are quicker to set up for basic tracking. With MarketXLS, you add 1000+ financial data functions to Excel, for a subscription starting at $70/month billed annually.

Get Started with Your Stock Portfolio Tracker in Excel

A stock portfolio tracker in Excel needs no programming. MarketXLS supplies the data with formulas like =Last("AAPL"), =DividendYield("AAPL") and =PERatio("AAPL"), and works in Windows desktop Excel and, through the Microsoft 365 add-in, in Excel on Mac and the web.

US plans are Standard ($70/month billed annually, 15-minute delayed stock quotes), Advanced ($125/month billed annually, real-time streaming) and Business ($200/month billed annually). Compare MarketXLS plans or book a demo.