Excel Stock Tracker, never add a position before checking these 5 cells

About this tutorial

Excel Stock Tracker builds are usually where portfolio mistakes start, because most people track price and cost basis and stop there. In this live session we build a working stock tracker in a spreadsheet from an empty sheet, and we add five specific check cells that tell you whether a position actually belongs in the sheet before you commit money to it. This is for self directed investors who already hold five to fifty positions and want one file that updates itself instead of a screenshot of a brokerage app. What you'll see in the spreadsheet: A holdings table laid out with ticker, shares, average cost, and live last price pulled with a MarketXLS price function, so unrealized gain and loss recalculates the moment the market moves rather than when you remember to refresh. The five check cells added next to each row: current dividend yield, payout ratio, forward P/E against the stock's own five year range, 52 week high and low position expressed as a percent, and average daily volume so you know whether you can exit the position without moving the price. A weight column that divides each position's market value by total portfolio value, with conditional formatting that flags any single name above a threshold you set, because concentration risk is the item a plain tracker hides best. Sector and asset class tags pulled per ticker so the tracker rolls up into a sector allocation summary, letting you see that four separate tickers are really one bet on the same industry. A dividend income block that multiplies shares by annual dividend per share for each holding, then totals expected annual income and shows what percent of that income comes from the top three payers. A daily change and portfolio level summary bar at the top: total market value, total cost, total unrealized gain, day change in dollars and percent, all built from the same live cells so nothing is typed by hand twice. Why this matters: a tracker that only reports what you already own is a scoreboard, not a decision tool. The moment you add valuation, payout sustainability, liquidity, and weight next to each row, the same file starts answering real questions. Is this position too large relative to the rest of the book. Is the dividend I am counting on covered by earnings. Am I buying near the top of a 52 week range without knowing it. Am I five stocks deep in one sector while believing I am diversified. Those are the checks that change what you buy next, and they cost nothing extra once the formulas are wired into the tracker you already maintain. We also cover the practical side: how to structure the sheet so adding a new ticker means one row, not a rebuild, how to keep cost basis history separate from live data so your formulas never break, and how to set the refresh behavior so the file is fast with dozens of holdings. Built live in Excel and Google Sheets using MarketXLS real time market data, formula by formula, with no pre filled template. Demo link and the function references used in this build are in the description below.