Excel Stock Tracker Template: Live Portfolio Watchlist, Cost Basis, and Dividend Dashboard for 2026

Excel stock tracker template is what most self-directed investors and advisors actually want when they say they need a portfolio dashboard. Not a brokerage screen that shows only what is in one account. Not a generic personal-finance app that lumps stocks, mortgages, and credit cards together. A workbook, on their own computer, with their own tickers, their own cost basis, and their own scoring rules, where every price and metric updates live. That is the workflow this guide is built around. The walkthrough below explains which fields a working stock tracker needs, how to assemble them in Excel with MarketXLS, and how the linked template wires twenty-five common holdings into a watchlist, a cost-basis ledger, a dividend projection, a sector rollup, and a benchmark comparison sheet. A live-formula template and a static sample workbook with data as of May 13, 2026 are linked further down so you can open both side by side.

Excel Stock Tracker Template: Core Fields at a Glance

A useful stock tracker template lives or dies by the columns it carries. The table below lists the fields the rest of this guide is built around, why each one matters for tracking real positions, and the MarketXLS function that pulls it directly into Excel.

Every cell in the live template is one of those formulas. The static sample workbook is pre-filled with snapshot values from May 13, 2026 so the model is fully readable even without a MarketXLS session running.

Why Build a Stock Tracker in Excel at All

Brokerage dashboards are organized around accounts, not around the way an investor actually thinks. Holdings in a Roth IRA, a taxable brokerage, and an old rollover account show up on three different screens. Watchlist stocks that have not yet been bought are not part of any account view. Dividend forecasts get crammed into a single column. Custom scoring or sector weight ceilings cannot be configured. The tooling is fine for trading; it is not fine for planning.

A workbook reverses the constraints. Holdings can be grouped any way the investor wants. A watchlist sits next to live positions in the same sheet. Dividends, cost basis, and benchmarks live as ordinary columns. Custom logic, including scoring rules and what-if scenarios, becomes a column or two of formulas. The only friction is keeping the underlying numbers fresh. That is what MarketXLS handles. With its functions installed, every price, ratio, dividend, and return is a live formula that recalculates on a configurable schedule.

The result is a stock tracker that behaves like a personal Bloomberg-lite for an Excel-fluent user. It is not a replacement for a custodial system, and it is not a recommendation engine. It is a personal dashboard that an investor controls.

What Belongs in an Excel Stock Tracker Template

There is no universal layout, but a good template covers six things:

1. A watchlist with live valuation and income metrics, so the investor can compare names side by side.
2. A position ledger that turns shares plus cost per share into market value and gain/loss.
3. A dividend projection that translates holdings into estimated annual cash flow.
4. A sector or asset-class rollup so concentration risk is obvious.
5. A benchmark comparison so each holding can be scored against an index.
6. A small set of editable inputs that drive everything else, sized for the investor's actual portfolio.

The template attached to this post follows that structure across six sheets. The rest of this article walks through each sheet, the formulas behind it, and the trade-offs to keep in mind.

Sheet 1: The Watchlist Dashboard

The Watchlist Dashboard is the core of the template. Twenty-five widely held stocks are listed in column A, with sector and cap classification in columns B and C. From column D forward every value is a live MarketXLS formula in the template version and a static snapshot in the sample.

The key formulas powering the dashboard are:

• =QM_Last("AAPL") returns the most recent traded price for a stock.
• =ChangePercent("AAPL") returns the same-session percent change.
• =PERatio("AAPL") returns the trailing twelve-month price-to-earnings ratio.
• =ForwardPE("AAPL") returns the forward twelve-month price-to-earnings ratio.
• =DividendYield("AAPL") returns the trailing twelve-month dividend yield as a percent.
• =DividendPerShare("AAPL") returns the annual dividend rate in dollars.
• =EarningsPerShare("AAPL") returns the trailing twelve-month EPS.
• =Beta("AAPL") returns the beta against the broader market.
• =MarketCapitalization("AAPL") returns the market cap in dollars.
• =FiftyTwoWeekHigh("AAPL") and =FiftyTwoWeekLow("AAPL") return the 52-week range.
• =ChangePercentYTD("AAPL") and =StockReturnYTD("AAPL") return YTD price and total return.

On top of those raw fields the dashboard derives a composite Score column. Twenty-five points are awarded for a low P/E (a P/E of zero earns a full twenty-five, a P/E of forty or more earns zero), twenty-five points are awarded for high dividend yield (a five-percent yield earns full marks), twenty-five points are awarded for low beta (a beta of zero earns full marks, two or higher earns zero), and twenty-five points are awarded for strong one-year return (negative twenty percent earns zero, positive thirty percent earns the full twenty-five). The Verdict column converts the score into a four-tier label: Strong, Solid, Watch, or Weak.

The scoring rule is not a stock pick. It is an editable formula. Investors who care about momentum over income can swap the yield component for a price-relative-strength factor. Investors building a yield portfolio can drop the beta component and weight yield more heavily. Because the rule is expressed as Excel arithmetic, the editing surface is the formula itself.

Yellow Input Cells

Above the screener, five yellow cells let the investor parameterize the dashboard:

• Portfolio size in dollars
• Target dividend yield in percent
• Maximum acceptable P/E
• Maximum acceptable beta
• Minimum one-year return in percent

The yellow cells are referenced by other sheets in the workbook. They are the single source of truth for the model. Changing the portfolio size, for example, automatically resizes every dollar allocation in the model portfolios.

Sheet 2: The Position Tracker

The Position Tracker is where a watchlist becomes a portfolio. For each ticker the investor enters two yellow inputs: shares held and average cost per share. The rest of the row is derived.

The core formulas are short and explicit:

• Total Cost is =C7*D7, shares multiplied by cost per share.
• Current Price is =QM_Last("AAPL") in the template, a snapshot value in the sample.
• Market Value is =C7*F7, shares multiplied by current price.
• Unrealized $ is =G7-E7, market value minus total cost.
• Unrealized % is =(G7-E7)/E7*100.
• Weight % divides the row's market value by the sum of all market values on the sheet.
• Day P&L $ multiplies shares by current price by today's percent change, giving a same-day dollar move on each position.

A totals row at the bottom sums every dollar column and computes a portfolio-wide unrealized percent. The unrealized percent column is wrapped in a three-color scale so winners are green and losers are red without any extra clicking.

For an Excel stock tracker template, this is the sheet that earns its keep. Brokerage statements show realized gain/loss after a sale and unrealized gain/loss inside the account. They rarely show unrealized gain/loss across accounts, weighted properly, with a same-day dollar move on each position. A spreadsheet handles that with five formulas.

Sheet 3: The Dividend Income Sheet

Dividend income is one of the few outputs in a stock tracker where doing the math by hand is almost guaranteed to drift. Dividend rates change. Companies initiate, raise, freeze, or cut payouts. A workbook that pulls trailing dividend per share live, multiplies it by shares held, and adds up the column is dramatically more useful than a spreadsheet with hardcoded dollar amounts.

The Dividend Income sheet does that. For each holding it pulls =DividendPerShare("AAPL") and multiplies by shares to produce projected annual income. It also pulls =DividendYield("AAPL") and adds a Yield on Cost column, which divides the trailing dividend per share by the original cost basis. A new investor's yield on cost equals their yield. A long-term holder's yield on cost rises every time a company increases its dividend.

A portfolio total row at the bottom sums projected income across the workbook and shows blended yield and blended yield on cost. The combination answers a question every income-focused investor wants on one line: at today's prices, how much cash will my portfolio pay me over the next twelve months, and what is that relative to what I put in.

The numbers are projections, not promises. Companies can and do cut dividend programs. The dividend column makes that math explicit; it does not freeze the outcome.

Sheet 4: The Sector Allocation Rollup

A stock tracker template that does not group by sector hides one of the most important risks in equity investing. Three large-cap technology positions can be eighty percent of a portfolio's market value if they ran in the last cycle. A workbook that surfaces that concentration is doing the investor a favor.

The Sector Allocation sheet does it with one formula per row: =SUMIF against the Position Tracker's sector column, returning the dollar market value for each sector. A second column divides by the portfolio total to produce a weight percent. Three companion columns use =AVERAGEIF to compute average yield, average P/E, and average YTD return per sector.

A blue-to-white color scale on the weight column makes concentration obvious. A sector at thirty percent jumps out of the screen even before the investor reads the number. The rule of thumb most advisors use, that any single sector above twenty percent of an equity sleeve deserves a conversation, is easy to spot at a glance.

Because the sector field comes from the static sector column on the Watchlist Dashboard, the investor can override the GICS classification for cases where it does not match how they think about a holding. Some investors classify Amazon as Technology, for example, even though it sits in Consumer Discretionary in the GICS taxonomy. Changing one cell propagates through every rollup.

Sheet 5: Performance vs Benchmark

The fifth sheet compares each holding to SPY, the most widely held S&P 500 ETF. SPY's YTD and one-year returns sit in a highlighted benchmark row at the top of the table. Each holding's YTD and one-year returns are pulled by formula from the Watchlist Dashboard, and the relative figures are expressed in basis points: a holding that returned twelve percent against an eight percent benchmark shows up as four hundred basis points of outperformance.

A Verdict column applies a simple rule: a holding that beat SPY on both YTD and one-year is labeled Outperform; one that lagged on both is labeled Lag; a mixed result is labeled Mixed.

The point of the sheet is not to pick winners. Underperformance versus an index, especially over a short window, is often not by itself a reason to trim or exit a position. The question is whether the thesis still holds and whether the investor's time horizon is long enough that recent relative weakness is acceptable. The sheet exists to make the comparison visible; the interpretation is the investor's job.

How To Use Sheet (Sheet 0)

Every workbook in this series leads with a How To Use sheet. It is the first tab the user sees. The sheet lists each subsequent sheet, what is on it, what the inputs are, and which MarketXLS functions power the data. New users tend to read it first; experienced users skip it.

The template version of the How To Use sheet explains the live formulas. The sample version explains the snapshot. Both versions list the website and book-a-demo links at the bottom along with a MarketXLS Functions Used box that names every formula in the workbook.

MarketXLS Implementation Notes

A few details matter when building the live version of this stock tracker template:

• Every formula uses double-quoted ticker arguments. =QM_Last("AAPL") works. =QM_Last(A13) works when column A holds the ticker. A bare ticker without quotes will return an error.
• =QM_Last and =Last return the same value for most tickers; =Last is the older alias.
• =ChangePercent returns the same-session percent change. For a session-open value use =Open("AAPL").
• =PERatio and =ForwardPE return zero when the underlying company has negative earnings. Wrap them with IFERROR or with an IF(PERatio("AAPL")<=0, "N/A", ...) guard if a clean display matters.
• =Beta returns a market beta calibrated by the data provider's window. For different windows, check what alternate beta functions the provider exposes.
• =Sector and =Industry return GICS classifications. They are sometimes broader than how an investor thinks about a holding. Override the static column on the dashboard if you disagree.

If a particular cell shows #NAME? after a workbook is opened, the MarketXLS session is probably not running. Open the MarketXLS ribbon and sign in; the workbook will recalculate on its own.

How To Customize the Template

The template ships with twenty-five well-known stocks, but the watchlist is meant to be replaced. Three steps cover the most common customization:

1. Replace the tickers in column A of the Watchlist Dashboard with your own list. Every formula on that row will update.
2. Replace the shares and cost-per-share values in columns C and D of the Position Tracker. Cost basis, market value, gain/loss, and weight all recalculate.
3. Edit the five yellow input cells on the Watchlist Dashboard to match your portfolio size, target yield, and screening thresholds. The model portfolios on the other sheets will resize.

For investors with more than twenty-five positions, the simplest extension is to insert rows in the Watchlist Dashboard and Position Tracker, then drag the formulas down. The Sector Allocation and Performance vs Benchmark sheets read the dashboard by absolute row reference, so widen those ranges in the SUMIF and AVERAGEIF formulas to match.

For investors with fewer positions, delete unused rows or leave the ticker cells blank. Formulas that depend on a blank ticker will return zero or #N/A. Wrap them in IFERROR if a cleaner blank-row display is preferred.

Download The Templates

Download the templates:

• - Pre-filled with data as of May 13, 2026
• - Live-updating formulas

The static version opens and reads in any copy of Excel, even without MarketXLS installed, and is useful for browsing the structure. The live version requires MarketXLS to recalculate the formula cells.

FAQ

What is the best Excel stock tracker template for a personal portfolio?

The best Excel stock tracker template is one that holds your own tickers, your own cost basis, and your own scoring rules, and that pulls live data into the workbook rather than hardcoded snapshots. The template attached to this post does all three. It carries twenty-five default holdings, but the watchlist, position ledger, and dividend projection are designed to be edited.

Does an Excel stock tracker template work without MarketXLS?

The sample version of the workbook is a static snapshot and opens without any add-in. The live template uses MarketXLS formulas. Those formulas return #NAME? if MarketXLS is not installed and authenticated. With MarketXLS running, every cell that uses one of its functions recalculates on the workbook's refresh schedule.

How do I track cost basis in Excel?

In Excel, cost basis is tracked with two columns per holding: shares and cost per share. Total cost is the product. Market value is shares times current price. Unrealized gain/loss is market value minus total cost, expressed in dollars or as a percent. The Position Tracker sheet in the linked template uses exactly that pattern, with MarketXLS pulling the current price live.

How do I project annual dividend income in Excel?

Annual dividend income for a holding is shares times annual dividend per share. With MarketXLS, the formula is =Shares*DividendPerShare("AAPL"). Summing the column gives projected portfolio income. The Dividend Income sheet adds a Yield on Cost column that divides dividend per share by your original cost basis.

What MarketXLS formulas does a stock tracker need?

A working stock tracker pulls live price with =QM_Last, valuation with =PERatio and =ForwardPE, income with =DividendYield and =DividendPerShare, profitability with =EarningsPerShare, risk with =Beta, size with =MarketCapitalization, range with =FiftyTwoWeekHigh and =FiftyTwoWeekLow, and performance with =ChangePercentYTD and =StockReturnYTD. =Sector and =Industry give classifications for rollups.

How often does the data refresh in an Excel stock tracker template?

The refresh cadence is set inside MarketXLS, not in Excel itself. The defaults pull current quotes on workbook open and on a configurable interval after that. Streaming functions, such as =Stream_Last, push updates continuously while the workbook is open.

Can I track multiple brokerage accounts in one Excel stock tracker template?

Yes. The simplest pattern is an Account column on the Position Tracker. Group rows by account, or use SUMIF on the account column to produce per-account market value rollups. Because the tracker is in your own workbook, there is no constraint on the number of accounts or the way they are grouped.

The Bottom Line

An Excel stock tracker template is the rare tool that gets more useful as a portfolio gets more complicated. The investor who only owns one ETF does not need a workbook. The investor who owns twenty positions across three accounts, watches another fifteen names, cares about sector concentration, projects dividend income, and benchmarks against the S&P 500 does. Excel is the right surface for that work because every dimension of the problem can be expressed as a column or a formula. MarketXLS is what keeps the underlying numbers fresh.

The template linked above is a starting point, not a finished product. Replace the tickers, replace the shares, edit the scoring rule, and the workbook becomes a personal dashboard rather than a generic example. The math is in the formulas; the judgment is in how the investor interprets it.

Nothing in this guide or in the template is a recommendation to buy or sell any security. Tickers appear as examples of how the formulas work. Your investment decisions are yours.

To learn more about the underlying functions, browse the MarketXLS function reference or book a demo to see how the data pipeline runs in production.