Tracking individual stock holdings doesn't have to be complicated. An Excel stock tracker gives you complete visibility into your positions, entry prices, current values, and gains/losses—all in one place, without paying for premium software.

In this guide, we'll walk through building a functional stock tracker spreadsheet from scratch, including formulas, best practices, and when to consider upgrading to automated tools.

Why You Need a Stock Tracker

Without a tracking system, you might:

Forget how much you paid for a stock (entry price)
Lose track of positions across multiple brokers
Miss rebalancing opportunities
Fail to recognize concentrated positions
Overpay in taxes by not tracking cost basis

Even a simple Excel tracker solves these problems. You'll always know your exact position sizes, entry prices, and current gains/losses.

How to Build Your Excel Stock Tracker: Step-by-Step

Step 1: Create the Column Headers

Start with these essential columns:

        A: Ticker | B: Company Name | C: Shares | D: Entry Price | E: Entry Date
        | F: Current Price | G: Total Value | H: Gain/Loss | I: % Return | J: %
        of Portfolio
      

These columns give you everything you need for basic stock tracking. The "% of Portfolio" column is key—it shows concentration risk at a glance.

Step 2: Add Your Holdings

Example Data:

Step 3: Add Formulas for Calculations

Total Value (Column G):

=C2*F2

Multiplies shares by current price.

Gain/Loss (Column H):

=G2-(C2*D2)

Current value minus original cost basis.

% Return (Column I):

=(F2-D2)/D2*100

Shows percentage gain or loss since entry.

% of Portfolio (Column J):

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

Divides each position by total portfolio value. Use absolute references ($G$2:$G$20) so the formula doesn't break when copied down.

Step 4: Add Summary Statistics

Below your holdings, add totals:

        Total Portfolio Value: =SUM(G2:G20) Total Gain/Loss: =SUM(H2:H20)
        Portfolio % Return: =(Total Gain/Loss / Total Cost Basis) * 100
      

Step 5: Update Prices Daily or Weekly

This is the hardest part of Excel tracking: keeping prices current. You have three options:

Manual: Visit Yahoo Finance or your broker each day, copy prices into column F
Semi-automated: Use Excel's data import features (Data → From Web)
Fully automated: Use Google Sheets' =GOOGLEFINANCE() function (easier than Excel)

Excel Stock Tracker Template Example

Here's what a functional tracker looks like (simplified):

        Ticker | Shares | Entry Price | Current Price | Total Value | Gain/Loss
        | % Return | % of Portfolio AAPL | 50 | $150 | $180 | $9,000 | $1,500 |
        +10% | 45% MSFT | 30 | $300 | $350 | $10,500 | $1,500 | +16.7% | 52.5%
        TSLA | 10 | $200 | $180 | $1,800 | -$200 | -10% | 9% --- TOTAL: | - | -
        | - | $21,300 | $2,800 | +13.1% | 100%
      

Best Practices for Excel Stock Tracking

1. Update Prices Consistently

Pick a day (e.g., Friday close) and update all prices at once. This prevents confusion from intraday fluctuations and keeps your data clean.

2. Track Cost Basis Accurately

For tax purposes, record your exact entry price and date. This matters for wash sales and long-term vs. short-term capital gains. Don't round numbers.

3. Use Separate Sheets for Different Account Types

Create tabs for:

Taxable brokerage account
401(k) / IRA accounts
Crypto wallet holdings

This makes tax reporting easier and prevents accidentally mixing account types.

4. Format for Readability

Use conditional formatting to highlight positive returns in green and losses in red. This makes monitoring your portfolio at a glance much easier.

5. Backup Your File

Store it in OneDrive, Google Drive, or Dropbox. A lost spreadsheet is a financial headache.

When to Upgrade Beyond Excel

Excel works well for small, simple portfolios. But here's when you should consider a better solution:

Multiple brokerage accounts: Manually consolidating accounts gets tedious quickly
Real-time monitoring: If you need prices updated throughout the day, not just daily
Risk analysis: Calculating volatility, correlation, or concentration risk in Excel requires advanced skills
Mobile access: You can't check your portfolio reliably on your phone with Excel
Tax reporting: Exporting cost basis data for tax filing is manual and error-prone

When any of these apply, tools like Guardfolio's free portfolio tracker automate the work, keeping you focused on strategy rather than spreadsheet maintenance.

Excel vs. Automated Portfolio Trackers: When to Use Each

Use Excel if: You have 5–10 holdings in a single account, you enjoy spreadsheet work, and you're willing to update prices manually.

Use an automated tracker if: You have multiple brokers, want real-time data, need risk monitoring, or prefer not to maintain a spreadsheet.

Read our full portfolio tracker comparison for a detailed breakdown of both approaches.

Next Steps

Once your Excel tracker is running:

Review it weekly to spot imbalances
Check your risk profile to ensure allocation matches your tolerance
Look for advanced techniques to automate price updates
Consider tracking all your accounts together if you have multiple brokers

Excel Stock Tracker