Stock Portfolio Tracker in Google Sheets: Track Gains, Dividends & Allocation (2026)

Your brokerage app shows your current balance. It does not show you your actual return rate by position, your dividend income trend over 3 years, or whether your asset allocation still matches your target. A Google Sheets portfolio tracker does all of that — for free, with live prices, on your terms.

Why Serious Investors Use Spreadsheets (Even With Brokerage Apps)

Brokerage apps have improved dramatically. Fidelity, Schwab, and Vanguard all show unrealized gains, asset allocation, and performance. So why build a separate tracker?

Three reasons: consolidation, customization, and history.

Consolidation: Most investors have accounts at 2-3 brokerages — a Fidelity 401(k) through work, a Roth IRA at Vanguard, and a taxable account at Robinhood. Each brokerage shows only its own accounts. A Google Sheets tracker merges everything into one view of your actual net investment position.

Customization: Your brokerage can't show you the metrics you care about. Want to track dividend growth year-over-year? Compare your small-cap allocation against your target by quarter? Calculate your true IRR after accounting for dollar-cost averaging at different price levels? A spreadsheet can. Apps can't.

History: Brokerages delete or hide old transaction history. Some only go back 5-10 years. Your spreadsheet keeps everything, forever, and you control the export.

What Your Portfolio Tracker Should Measure

Before building anything, be clear on which metrics actually matter. Most investors track too little (just current value) or too much (17 ratios they never look at). The right set is:

The 4-Tab Portfolio Tracker Structure

Tab 1: Holdings — Your Current Positions

This is the main view. One row per position (ticker). Columns pull live data via GOOGLEFINANCE and calculate gain/loss automatically.

Holdings Tab Columns

Example Holdings View

Tab 2: Transaction History — Every Buy, Sell & Dividend

This is the audit trail. Log every transaction here: purchases, sales, dividend reinvestments, and cash dividends. This tab powers your cost basis calculations and is essential for accurate tax reporting.

Transaction Log Columns

• Date — trade date (not settlement date)
• Ticker — stock symbol
• Account — which account (use dropdown)
• Transaction Type — Buy / Sell / Dividend / DRIP (dividend reinvestment)
• Shares — number of shares bought/sold (or blank for cash dividends)
• Price Per Share — execution price
• Total Amount — Shares × Price (or dividend amount for cash dividends)
• Commission/Fee — broker commission (usually $0 now, but track it)
• Net Amount — Total ± Commission
• Notes — optional context

Tab 3: Performance Dashboard

The performance dashboard gives you the portfolio-level view: total value, total gain/loss, savings rate into investments, and a monthly snapshot history for charting.

Key Performance Metrics to Display

• Total Portfolio Value — sum of all current position values
• Total Cost Basis — sum of all invested capital
• Total Unrealized Gain/Loss $ and %
• Total Realized Gains This Year — from sold positions (tax planning)
• Total Dividends Received This Year
• Portfolio vs. Benchmark — your return % vs. S&P 500 YTD
• Asset Allocation: Actual vs. Target — chart format
• Monthly Portfolio Value History — line chart over time

Asset Allocation: Example Dashboard

In this example, international stocks and bonds are underweight (red). The next contribution should flow there to rebalance — or you sell a sliver of US stocks to buy VXUS. The tracker makes this decision obvious rather than leaving it to memory.

Tab 4: Dividend Tracker

If you hold dividend stocks or ETFs, this tab is especially valuable. It tracks dividend income by stock and by quarter, calculates your forward annual dividend income, and shows dividend growth year-over-year.

Dividend Log Columns

• Date Received
• Ticker
• Dividend Per Share
• Shares Held at Ex-Date
• Total Dividend Received (Shares × Dividend/Share)
• Reinvested? (Yes/No — was it a DRIP?)
• Shares Added (if DRIP)
• Account

Annual Dividend Summary

At $662 in dividends last year and growing 8.5% annually, you're projected to receive ~$718 in 2026. That's not life-changing alone — but tracked consistently, it shows whether your dividend income is on the trajectory you need.

GOOGLEFINANCE: Live Prices in Google Sheets

The GOOGLEFINANCE function is the core of a portfolio tracker in Google Sheets. It pulls live (or slightly delayed) market data directly into your cells — no API key, no subscription, no manual updates.

Basic Syntax

Most Useful Attributes

Tracking Cost Basis and Unrealized Gains

Cost basis is the amount you paid for shares including commissions. It determines your taxable gain when you sell. For most buy-and-hold investors, average cost basis (ACB) per share is the simplest approach.

Average Cost Basis Per Share (from Transactions Tab)

Unrealized Gain/Loss

Dollar-Cost Averaging: Tracking Multiple Purchases

If you buy the same stock at different prices over time (which you should — DCA is a proven strategy), your average cost basis changes with each purchase. The SUMPRODUCT formula above handles this automatically by calculating the weighted average across all buys logged in the Transactions tab.

Example: You bought VTI three times: 10 shares at $190, 15 shares at $205, and 20 shares at $195. Your average cost basis = (10×190 + 15×205 + 20×195) / 45 = $196.78. The formula handles this regardless of how many lots you have.

Asset Allocation & Rebalancing in Google Sheets

Asset allocation is more important than stock picking for long-term returns. Tracking it in Google Sheets lets you see drift in real time and make rebalancing decisions with actual data.

Calculating Actual Allocation % Per Asset Class

Rebalancing Decision Logic

When actual allocation drifts more than 5% from target, it's time to rebalance. There are two ways:

1. Buy to rebalance: Direct new contributions toward underweight asset classes. No taxes, just buying more of what's cheap.
2. Sell to rebalance: Sell overweight positions to buy underweight ones. Triggers a taxable event in non-retirement accounts — calculate the tax cost first.

Capital Gains: What to Track for Taxes

When you sell shares, you owe taxes on the gain. The rate depends on how long you held the shares:

What to Log on Every Sale

• Date of purchase (for each lot sold)
• Date of sale
• Cost basis of shares sold
• Sale proceeds (net of commission)
• Realized gain/loss
• Short-term or long-term (based on holding period)

7 Portfolio Tracker Mistakes to Avoid

1. Tracking Current Price Without Cost Basis

Knowing your portfolio is worth $85,000 is useless without knowing you invested $70,000. Always track cost basis. The gain, not the total value, tells you how you're doing.

2. Forgetting Dividend Reinvestment in Cost Basis

If you reinvest dividends (DRIP), those reinvested shares have their own cost basis equal to the price on the reinvestment date. Log every DRIP in your transaction history. Missing these makes your cost basis artificially low and overstates your taxable gain when you sell.

3. Only Tracking Your Best Performers

It's tempting to only log the stocks you're proud of. Track everything — including the losers. You need the full picture, and losers might be tax-loss harvesting candidates.

4. Not Separating Accounts

Your taxable account and your Roth IRA are fundamentally different. Capital gains in the Roth are tax-free forever. Realizing a gain in your taxable account is a taxable event. Track them separately to avoid confusion during tax season.

5. Using Live GOOGLEFINANCE for Historical Records

When you record a monthly snapshot of portfolio value, copy and paste as values only. If you leave the GOOGLEFINANCE formula in your historical column, it will update to today's price and destroy your historical record. Values only. Every time.

6. Tracking Too Many Metrics

P/E ratios, beta coefficients, Sharpe ratios, sector exposure — these are interesting but only useful if you act on them. Start with the basics (current value, cost basis, gain %, allocation) and add complexity only when you find yourself needing it.

7. Checking Too Frequently

A portfolio tracker is for monthly reviews, not daily anxiety. Set up the system, do a monthly check-in, and let compounding do its work. The tracker exists to give you information when you need to make a decision — not to give you heart palpitations when the market drops 2% on a Tuesday.

Building the System: What to Do This Weekend

Your portfolio tracker won't be perfect on day one. That's fine. A rough tracker you actually use beats a perfect system you abandon after two weeks. Start simple — Holdings tab with GOOGLEFINANCE prices and manual cost basis — and add the other tabs one by one as you get comfortable.

The goal isn't to beat the market (though tracking your returns vs. a benchmark helps you know if you are). The goal is to know — clearly, at any moment — exactly where your money stands, whether your allocation still matches your goals, and what decisions you need to make next.