How to Track Your Stock Portfolio in a Spreadsheet (Full Setup)

Your brokerage app already shows you a portfolio value, a gain number and a pie chart. So the honest question isn’t whether you can track your portfolio — it’s what a spreadsheet tells you that the app won’t.

Three things, mostly. It tells you what you own across accounts your broker can’t see — a second brokerage, an old employer plan, coins in cold storage. It tells you what a position actually cost you, including fees, in a record your broker won’t rewrite when you transfer or when a custodian changes hands. And it tells you what you’ll owe if you sell today, split short-term from long-term, before you press the button rather than after.

Here’s the full build, tab by tab, using one worked portfolio the whole way through so you can see every number connect. Every figure below is illustrative — a made-up but internally consistent portfolio — and you’d replace it with yours.

The Worked Portfolio

Six stock and ETF positions, four crypto positions, and some cash. Snapshot taken 21 August 2026.

That’s a 10.96% total return on the stock side. Crypto adds another $12,596.00 of cost basis and $14,564.50 of current value, and there’s $4,300.00 sitting in a money market fund. Total portfolio: $51,003.50.

Hold onto that number. Almost everything useful in this build is a percentage of it.

Tab 1: The Trade Log — One Row Per Purchase, Never Overwritten

This is the tab everything else depends on, and it’s the one people get wrong first.

The rule: one row per purchase lot, and the price in that row never changes. If you bought VTI three times, that’s three rows, not one row you keep editing. Each row records the date, the ticker, the share count, the price you actually paid, and the commission or fee.

Two things fall out of that automatically:

Cost basis = shares × price paid + fees. For the VTI row, 40 × $248.00 = $9,920.00. Fees get added to basis on a buy (and subtracted from proceeds on a sale), which is why the fee column belongs here and not in a general expenses list.

Holding period = today minus the purchase date, which a spreadsheet handles with =TODAY()-A4, and the short-term/long-term flag falls straight out of it: =IF(L4>=365,"Long-Term","Short-Term"). In the worked portfolio, four positions are already long-term (VTI at 893 days, SCHD at 715, MSFT at 576, O at 429) and two aren’t (VXUS at 290, NVDA at 193). That single column is worth more than most of the dashboard, because it’s the one that changes what you’d do today. What selling before that clock runs out actually costs is worth its own arithmetic.

One column worth adding. Most trade-log layouts, including the one in our template, calculate a row’s value from the same price cell that feeds cost basis. That’s fine as long as you treat the trade log as a permanent acquisition record and keep current prices somewhere else. If you’d rather see live gain per row, insert a Current Price column beside Price Paid and point the value formula at the new column — so Current Value = Shares × Current Price while Cost Basis = Shares × Price Paid + Fee stays frozen. Two minutes of setup, and it removes the only way this tab can lie to you.

Tab 2: Dividends — Because Return Isn’t Just Price

A price-only tracker quietly understates what you’ve earned. The worked portfolio holds three income positions, and on the assumed rates below they throw off real money:

$721.26 a year, against $28,964.00 of money actually invested — a 2.49% yield on cost. That’s a different and more useful number than the 2.24% current yield, because it measures the income against what you paid, not against what the market happens to charge today. It’s the number that rises over the years while the current yield stays roughly flat, and watching it climb is most of the reason dividend investors keep a spreadsheet at all. The full method, including the trap monthly payers set.

Tab 3: Crypto — One Row Per Venue, Not Per Coin

Crypto needs its own tab, and the reason is structural rather than sentimental: the same coin sitting in two places is two different tax lots.

Two ETH rows, not one. Merge them into a single 2.0 ETH row at a blended $2,809.00 and you’ve thrown away the information that decides your tax bill: sell 0.6 ETH out of the cheap wallet lot and the gain is $444.00; sell the same 0.6 ETH out of the Kraken lot and it’s $102.00. Same coin, same day, same price — a $342.00 difference in what you report, purely a bookkeeping choice you can only make if you kept the lots apart. Tracking crypto across exchanges and wallets properly is a short set of rules, and moving coins between your own wallets is where most people break their own records.

Tab 4: Asset Allocation — The Tab That Earns Its Keep

Here’s the payoff for putting everything in one file. Take the six asset classes, drop in the current values, and set the targets you actually intended:

The formula behind the Action column is one line — =IF(drift>2%,"SELL/REDUCE",IF(drift<-2%,"BUY/ADD","HOLD")) — and the dollar column is just (target% − current%) × total. A 2-percentage-point band stops the sheet nagging you about rounding noise; the three flags that survive it are real.

And they’re the sort of thing that’s genuinely invisible without this tab. This investor believes they hold 10% crypto. They hold 28.6% — nearly $9,500 more than intended, entirely because it went up and nobody re-checked. They also hold no bonds at all against a 10% target, which is the kind of gap that only ever gets noticed on purpose.

What you do about it is your call and depends on taxes, conviction and account type — selling $9,466 of appreciated crypto in a taxable account is a decision with a tax bill attached, and directing new contributions toward the underweight classes instead is the cheaper way to close a gap that isn’t urgent. The spreadsheet’s job is to put the number in front of you, not to make the trade.

Tab 5: The Monthly Snapshot

One row per month: stock value, crypto value, other assets, total, and the change on the previous month. Twelve numbers a year, each one taking about thirty seconds to record.

The reason it matters is that every other tab shows you now. This one shows you the shape of the thing — that a 10.96% gain arrived in two lumps and a long flat stretch, or that the month you felt panicky was a 4% drawdown rather than the disaster it felt like. It also catches contributions honestly: log deposits and withdrawals in their own column, or you’ll congratulate yourself for growth you actually just paid in.

Tab 6: The Realized Gains Log

Nothing goes in here until you sell. When you do, one row: what you sold, date bought, date sold, proceeds, cost basis, fees, and the resulting gain or loss with its short/long-term flag.

Two reasons to keep it separately from the trade log. First, it’s the tab you hand your preparer, and it should contain only closed positions. Second, it’s the running total that tells you mid-year whether you’re sitting on net gains — useful information in November, when there’s still time to do something about it, and useless in April.

The Ten-Minute Monthly Routine

The build is a one-off. This is the part that has to survive:

1. Paste current prices into the price column for each position — six stock rows and four crypto rows here, under two minutes.
2. Log any dividends received since last time, one row each.
3. Record the month’s total on the snapshot tab, plus any deposits.
4. Glance at the allocation drift column. If nothing’s outside the band, you’re done.
5. Log any sales in the realized gains tab, while you still remember the details.

Ten minutes, once a month. Do it on the same day each month — the first weekend, the day after payday, whatever sticks — and the tracker becomes a record instead of a project.

Should You Build This or Buy It?

Everything above is buildable in a free afternoon, and if you enjoy spreadsheets you’ll build a better-fitting one than anything you can buy, because you’ll know exactly which columns you actually use.

The case against is just the formula count. A working version of the six tabs above runs to several hundred formulas once you include the per-row cost basis, holding-period flags, yield-on-cost math, drift calculations and dashboard rollups — and formula bugs in a portfolio tracker are quiet. A wrong sign or an off-by-one range doesn’t error, it just reports a return that’s a bit wrong forever. The full trade-off against a subscription portfolio app is worth reading before you commit either way.

Featured on ReadySheetGo

Stock & Crypto Portfolio Tracker — the six tabs above, pre-built with 957 working formulas across 9 tabs. A Stock Trade Log (50 rows) with automatic cost basis, holding period and short-term/long-term classification; a Dividend Tracker calculating dividend per share, annual projected income and yield on cost per position; Crypto Positions for up to 30 holdings across any exchange or wallet with per-position gain/loss and share of the crypto book; a Watchlist with target buy prices and automatic green/amber/red buy-zone alerts; an Asset Allocation tab with target-versus-actual across six asset classes, rebalancing actions and dollar amounts; a 36-month Performance log with monthly and cumulative return; a Tax Summary splitting realized gains short-term from long-term for Schedule D prep; and a Portfolio Dashboard tying it together. Works in Excel and Google Sheets, no macros. Instant digital download — $17.99.

Nothing here is investment or tax advice. The portfolio, prices and dividend rates above are illustrative figures chosen to make the arithmetic clear, not recommendations, and tax outcomes depend on your own situation — check specifics with a qualified preparer.