Portfolio Tracker in Excel, never trust your returns before checking these 4 cells

About this tutorial

Portfolio Tracker in Excel is what we build in this live session, from an empty sheet to a working dashboard that prices every holding, tracks cost basis, and shows real gain and loss instead of a number that only looks right. This is for investors who already keep positions in a spreadsheet and want live prices, correct return math, and a clear view of what is actually driving performance. We build it step by step so you can copy the same layout into your own file. What you'll see: A holdings table set up first: ticker, share count, purchase date, purchase price, and account, laid out so every formula below can reference it by row instead of hard coded values. Live pricing pulled with MarketXLS functions such as LastPrice, PreviousClose, and ChangePercent, so each position updates with real-time market data and the sheet refreshes without manual copy and paste. The four cells most trackers get wrong: market value versus cost basis, unrealized gain in dollars and percent, position weight as a share of total portfolio, and day change contribution in dollars rather than percent, which is the one that tells you which holding actually moved your account today. Dividend income added per position using dividend yield and annual dividend functions, then rolled into an expected annual income figure and a yield on cost column, so income investors can see what the portfolio pays relative to what they paid. A summary block at the top of the sheet: total market value, total cost, total unrealized gain, portfolio day change, and cash, with conditional formatting so gains and losses are readable at a glance. Sector and asset allocation built from the same holdings table with a simple pivot and a bar or pie view, so concentration risk shows up visually instead of hiding inside a long list of rows. Why this matters: most homemade trackers compute return as a simple average of position percentages, which quietly ignores position size. A five percent gain on a small holding and a five percent loss on your largest holding are not equal, but an unweighted average treats them that way. The same problem shows up with dividends, stock splits, and added shares over time, where cost basis has to be updated or every return number downstream is wrong. Getting weights, cost basis, and day change contribution right is the difference between a spreadsheet that decorates your holdings and one you can rebalance from. Once the sheet is correct, it answers real questions: which position is now oversized, where is the income actually coming from, and what would trimming a holding do to your allocation. We also cover practical setup details, including how to structure the sheet so adding a new position takes one row and no formula edits, how to keep a transactions tab separate from the positions tab, and how to refresh prices on a schedule so the dashboard stays current during market hours. Built live in Excel and Google Sheets with MarketXLS real-time data. Demo link is in the description, and you can follow along with your own holdings as we go.