Stock Portfolio Tracker Excel: Template + Auto-Update Formula

Tracking a stock portfolio doesn't require expensive software. A well-designed Excel template with the right formulas can automate most of the work and give you real-time visibility into your positions. This guide walks through building a full-featured stock tracker with auto-calculating formulas.

Why a Stock Portfolio Excel Template?

A template saves you from building from scratch. You get:

Pre-built formulas for gains/losses, allocation %, and performance metrics
Consistent structure for easy updates
Built-in error-checking and data validation
Professional formatting that's easy to read
Export-ready for tax filing and analysis

The Core Template Structure

Essential Columns:

Every column feeds into the next. Once you enter the ticker, shares, and entry price, all calculations populate automatically.

Auto-Calculating Formulas Explained

1. Total Value (Auto-Calculate)

=C2 * F2

Multiplies shares (column C) by current price (column F). As prices update, total value refreshes automatically.

2. Cost Basis (Auto-Calculate)

=C2 * D2

Your original investment: shares × entry price. Doesn't change unless you edit entry data.

3. Gain or Loss (Auto-Calculate)

=H2 - I2

Where H2 = Total Value, I2 = Cost Basis. Shows your profit/loss in dollars.

4. Percentage Return (Auto-Calculate)

=(F2 - D2) / D2 * 100

Shows return as a percentage. If you bought at $100 and it's now $120, this shows +20%.

5. Portfolio Allocation % (Auto-Calculate)

=G2 / SUM($G$2:$G$100) * 100

Divides each position by total portfolio value. Use absolute references ($) so the formula locks the range when copied.

Portfolio Summary Calculations

Add these totals at the bottom of your tracker:

        Total Portfolio Value: =SUM(G2:G100) Total Cost Basis: =SUM(I2:I100)
        Total Gain/Loss: =SUM(J2:J100) Overall Return %: =(Total Gain/Loss /
        Total Cost Basis) * 100
      

Automating Price Updates

The biggest challenge with Excel tracking is keeping prices current. Here are three approaches:

Option 1: Manual Daily Update (10 minutes)

Visit Yahoo Finance or your broker
Copy the closing price for each stock
Paste into the "Current Price" column
All calculations refresh automatically

Option 2: Google Sheets GOOGLEFINANCE (Automatic)

If you use Google Sheets instead of Excel:

=GOOGLEFINANCE("AAPL", "price")

This pulls live Apple stock price. Formula updates throughout the day. Excel doesn't have a native equivalent, but you can use Power Query or third-party add-ins.

Option 3: Broker Data Import (Semi-Automated)

Export your holdings from your broker as CSV
Use VLOOKUP to match current prices with your tracking sheet
Takes 20 minutes to set up, then 5 minutes weekly to refresh

Advanced Features to Add

Sector Allocation Breakdown

Add a "Sector" column (Technology, Healthcare, Finance, etc.). Then use SUMIF to calculate sector exposure:

        Tech Allocation: =SUMIF(Sector:Sector, "Technology",
        Allocation:Allocation) Healthcare Allocation: =SUMIF(Sector:Sector,
        "Healthcare", Allocation:Allocation)
      

Concentration Risk Alert

Flag any position that exceeds 10% of your portfolio:

        Largest Position: =MAX(K2:K100) Status: =IF(MAX(K2:K100) > 10%,
        "REBALANCE", "OK")
      

Performance vs Benchmark

Compare your returns to the S&P 500:

        Your Return %: (From template calculation) S&P 500 Return %: (Manually
        enter or use GOOGLEFINANCE("^GSPC")) Outperformance: =Your Return % -
        S&P 500 Return %
      

Best Practices for Your Template

Use data validation: Set up dropdowns for Sector to prevent typos
Conditional formatting: Color gains green, losses red
Freeze header row: Makes scrolling easier on large portfolios
Lock formula cells: Protect critical calculations from accidental editing
Backup your file: Store in Google Drive, OneDrive, or Dropbox
Version control: Save monthly snapshots (portfolio_2026_01.xlsx, portfolio_2026_02.xlsx)

When to Upgrade Beyond Excel

Your Excel tracker works great until:

You have > 20 stocks across multiple brokers
You need real-time risk monitoring
Price updates become a burden
You want correlation, volatility, or drawdown tracking
Mobile access becomes important

At that point, a dedicated tracker like Guardfolio saves time and gives you advanced insights Excel can't provide. For multi-broker consolidation specifically, see our guide on tracking multiple brokers in Excel.

Next Steps

Build your template with the columns above
Enter your current holdings (ticker, shares, entry price)
Set up your price update routine (daily, weekly, or monthly)
Review quarterly and rebalance as needed
Read our guide on advanced portfolio spreadsheets for more features

Stock Portfolio Tracker Excel