Home › Templates › Stock portfolio tracker

Stock Portfolio Tracker Spreadsheet

A free stock portfolio tracker spreadsheet: log each buy and sell once, type a current price, and the sheet calculates shares held, average cost, market value, unrealized and realized P/L, allocation and a simple S&P 500 comparison.

Download Excel template (.xlsx) 118 KB · no macros · Excel 2016+ and Google Sheets · v1.0, 23 Oct 2026

Use it in Google Sheets: in Sheets choose File → Import → Upload and pick the .xlsx, or upload it to Google Drive and open it with Google Sheets. All formulas are standard SUMIFS/IF/SUM, so they work in both. In Sheets you can also switch the price column to live quotes with GOOGLEFINANCE (see below).

Preview: the Example Holdings tab, calculated from 8 fictional trades (tickers and prices are made up).

Ticker	Shares held	Avg cost	Cost basis	Price	Market value	Unrealized P/L	P/L %	Allocation
CTSO	30	$146.07	$4,382.00	$162.40	$4,872.00	$490.00	11.18%	21.84%
BRDX	50	$254.00	$12,700.00	$289.10	$14,455.00	$1,755.00	13.82%	64.79%
FABR	30	$42.02	$1,260.60	$45.20	$1,356.00	$95.40	7.57%	6.08%
NWND	20	$75.03	$1,500.67	$81.30	$1,626.00	$125.33	8.35%	7.29%
Total			$19,843.27		$22,309.00	$2,465.73	12.43%	100.00%

What's inside the spreadsheet

Seven tabs. Three are empty working tabs for your data, three are the same tabs filled with a fictional example, plus a "Start here" guide.

Tab	What you type	What it calculates
Start here	Nothing	Steps, colour key (yellow = input, lavender = formula), method notes, disclaimer
Transactions	Date, ticker, Buy/Sell (dropdown), shares, price per share, fees, notes. 1,000 rows.	Net amount: shares × price + fees for a buy, shares × price − fees for a sell
Holdings	Ticker (once each), optional Google Finance symbol, current price. 50 rows.	Shares bought, sold and held; total buy cost; average cost; cost basis; market value; unrealized P/L $ and %; allocation %; sell proceeds; realized P/L
Dashboard	Benchmark start/end dates and S&P 500 levels	Portfolio totals, fees paid, positions held, simple return vs S&P 500 price return, allocation pie chart
Example Transactions / Holdings / Dashboard	Already filled	Shows how a filled tracker looks; safe to delete when you are done

How to use it

Open the three Example tabs first to see how the numbers flow.
On Transactions, add one row per trade. Pick Buy or Sell from the dropdown. Enter fees as a positive number (0 if none).
On Holdings, type each ticker once, spelled exactly as on Transactions, and its current price.
Optional (Google Sheets): put the exchange-prefixed symbol in column B, for example NASDAQ:AAPL, and replace the price with =GOOGLEFINANCE(B5,"price").
On Dashboard, enter the S&P 500 index level on your start date and today to compare.
Update prices whenever you review the portfolio. Past trades never need retyping.

Live prices in Google Sheets with GOOGLEFINANCE

Excel has no GOOGLEFINANCE function, so the downloaded file uses a typed price. After you import it into Google Sheets you can replace any price cell with a formula. Google's documented syntax is GOOGLEFINANCE(ticker, [attribute], [start_date], [end_date|num_days], [interval]). For a current price:

=GOOGLEFINANCE("NASDAQ:AAPL","price")
=GOOGLEFINANCE(B5,"price")   ← B5 holds NASDAQ:AAPL

Points from Google's GOOGLEFINANCE help page worth knowing:

Google describes "price" as a "Real-time price quote, delayed by up to 20 minutes."
"Quotes are not sourced from all markets and may be delayed up to 20 minutes."
"Information is provided 'as is' and solely for informational purposes, not for trading purposes or advice."
Use the exchange and the ticker together: Google says to use "NASDAQ:GOOG" instead of "GOOG."

If a symbol returns an error, type the price by hand. The rest of the sheet does not care where the number comes from.

The formulas, explained

These are the exact formulas in the file (row 5 shown; they are copied down). Transactions data sits in rows 5–1004: B = ticker, C = type, D = shares, E = price, F = fees, G = net amount.

Column	Formula
Net amount (Transactions G)	`=IF(D5="","",IF(C5="Buy",D5E5+F5,D5E5-F5))`
Shares bought (Holdings D)	`=IF($A5="","",SUMIFS(Transactions!$D$5:$D$1004,Transactions!$B$5:$B$1004,$A5,Transactions!$C$5:$C$1004,"Buy"))`
Shares sold (E)	Same SUMIFS with `"Sell"`
Shares held (F)	`=D5-E5`
Total buy cost (G)	`=SUMIFS(Transactions!$G$5:$G$1004,…,$A5,…,"Buy")`
Average cost / share (H)	`=IF(D5>0,G5/D5,0)`
Cost basis of shares held (I)	`=H5*F5`
Market value (J)	`=F5*C5`
Unrealized P/L $ (K) and % (L)	`=J5-I5` and `=K5/I5`
Allocation % (M)	`=J5/SUM($J$5:$J$54)`
Realized P/L (O)	`=N5-H5*E5` (sell proceeds after fees − average cost × shares sold)
Portfolio simple return (Dashboard)	`=(B7+SUM(Holdings!N5:N54)-B5)/B5` = (market value + sell proceeds − total buy cost) ÷ total buy cost
S&P 500 price return	`=B19/B18-1` (end level ÷ start level − 1)

The live formulas also wrap each line in IF($A5="","",…) so empty rows stay blank.

Worked example from the sample data

The Example tabs hold these 8 fictional trades:

Date	Ticker	Type	Shares	Price	Fees	Net amount
2025-01-15	CTSO	Buy	20	$150.00	$1.00	$3,001.00
2025-02-10	BRDX	Buy	40	$250.00	$0.00	$10,000.00
2025-03-05	FABR	Buy	50	$42.00	$1.00	$2,101.00
2025-05-20	CTSO	Buy	10	$138.00	$1.00	$1,381.00
2025-07-01	NWND	Buy	30	$75.00	$1.00	$2,251.00
2025-09-15	FABR	Sell	20	$48.50	$1.00	$969.00
2026-01-12	BRDX	Buy	10	$270.00	$0.00	$2,700.00
2026-04-08	NWND	Sell	10	$70.00	$1.00	$699.00

One ticker, two buys: CTSO

Buys: 20 × $150.00 + $1 = $3,001.00 and 10 × $138.00 + $1 = $1,381.00. Total buy cost $4,382.00 for 30 shares, so the average cost is $4,382.00 ÷ 30 = $146.0667. At a price of $162.40, market value is $4,872.00 and unrealized P/L is $490.00 (11.18%).

A partial sale: FABR

Bought 50 at $42.00 plus $1 fee: average cost $42.02. Sold 20 at $48.50 minus $1 fee: proceeds $969.00. Realized P/L = $969.00 − $42.02 × 20 = $128.60. The remaining 30 shares keep the same average cost, so their cost basis is $1,260.60.

Whole portfolio vs the benchmark

Total buy cost $21,434.00, market value $22,309.00, sell proceeds $1,668.00. Simple return = ($22,309.00 + $1,668.00 − $21,434.00) ÷ $21,434.00 = 11.86%. With made-up index levels of 5,000 and 5,600, the S&P 500 price return is 12.00%, so the example portfolio trails by 0.14 percentage points.

The benchmark block is deliberately simple. It ignores when money went in (not a time-weighted return), and the S&P 500 index level is a price index that leaves out dividends. Treat it as a rough check, not a performance report.

Common mistakes

Ticker spelled two ways. "BRK.B" on Transactions and "BRK-B" on Holdings will not match. Copy and paste the ticker.
Fees typed as negatives. Enter fees as positive numbers; the formula adds them to buys and subtracts them from sells.
Selling more than you own. Shares held turns negative. Check for a missing buy or a stock split.
Stock splits. Not automatic. After a 2-for-1 split, add a Buy row with the extra shares at $0 price, or adjust past rows. Your average cost per share halves either way.
Using this for taxes. The sheet uses average cost. IRS Publication 550 states: "Except for certain mutual fund shares, discussed later, you cannot use the average price per share to figure gain or loss on the sale of the shares." Use your broker's lot records (FIFO or specific shares) for tax forms; for an estimate try the capital gains tax calculator.
Comparing different periods. Use the same start and end dates for the portfolio and the index.

FAQ

How do I make a stock portfolio tracker in Excel?

List every trade in one table (date, ticker, Buy/Sell, shares, price, fees). On a second tab, list each ticker once and use SUMIFS to total shares bought and sold and the cost of buys. Average cost = buy cost ÷ shares bought; market value = shares held × current price. The free template on this page has all of this built in.

Can Google Sheets track stock prices automatically?

Yes. GOOGLEFINANCE returns a quote, for example =GOOGLEFINANCE("NASDAQ:AAPL","price"). Google's help page says quotes may be delayed up to 20 minutes, are not sourced from all markets, and are provided for informational purposes, not for trading.

How do I calculate average cost per share?

Add up what you paid for all shares of a stock, including fees, and divide by the number of shares bought. Example: 20 shares at $150 plus 10 at $138, with $1 fee each, cost $4,382, so the average is $146.07 per share.

What is the difference between realized and unrealized gain?

Unrealized gain is the paper profit on shares you still hold: market value minus cost basis. Realized gain is locked in when you sell: sale proceeds minus the cost of the shares sold.

How do I compare my portfolio to the S&P 500?

Note the S&P 500 level on your start date and today, and divide end by start minus 1. Compare it with your portfolio return over the same dates. The S&P 500 level is a price index without dividends, and a simple return ignores when you added money, so the comparison is rough.

Does this tracker work for ETFs and crypto?

ETFs work the same way as stocks. Crypto also works: shares accept decimals, so enter fractional coin amounts and type the price yourself. If you use a live-price formula, check that the symbol returns a value before relying on it.

Related tools and templates

Educational template only, not financial, tax or investment advice. Example data is fictional: tickers CTSO, BRDX, FABR and NWND, all prices and the index levels are made up. Check every figure against your broker statements. Quotes from Google support and the IRS were retrieved on 23 October 2026.