Excel Investment Tracker Templates
Modern Excel has a built-in STOCKS data type that fetches live quotes — no add-ons. Here's how to wire up a tracker, plus a Google Sheets formula fallback.
Don't have time? Here's what you need to know:
- 1Excel's STOCKS data type (Microsoft 365) fetches delayed live quotes via formulas like =A2.Price.
- 2The tracker engine is just four formulas: value (shares x price), weight, gain in dollars, and gain in percent.
- 3Keep a separate transactions tab so you can reconstruct cost basis accurately and audit the numbers.
- 4Use the tracker to surface weight drift and total expense-ratio cost — things your broker tends to bury.
Excel or Google Sheets? Pick Based on How You Get Prices
Both Excel and Google Sheets can track an investment portfolio well, and the layout is nearly identical. The practical difference is how each fetches live prices. Google Sheets has the GOOGLEFINANCE() function built in for free. Excel's equivalent is the linked STOCKS data type, which is available in Microsoft 365 (and works best with an internet connection and a Microsoft account).
If you already pay for Microsoft 365 and prefer a desktop app with deeper charting and pivot tables, Excel is excellent. If you want a free, always-synced option, Google Sheets is hard to beat. Many investors keep the master copy in whichever they open more often and don't overthink it.
Using Excel's STOCKS Data Type for Live Quotes
In Microsoft 365, type a ticker into a cell — for example VOO — select it, then go to the Data tab and click 'Stocks.' Excel converts the cell into a linked data type with a small icon. From there you can pull dozens of fields into adjacent cells. If your ticker cell is A2, the formula =A2.Price returns the current price and =A2.[52 week high] returns that field.
Refresh updates the linked data, and like most free quote sources it is delayed, not real-time. The STOCKS type covers many ETFs and stocks but not every fund; if a ticker won't convert, fall back to entering its price manually. Older, non-365 versions of Excel don't include this data type, in which case a manual price column or an external data import is your route.
Important: Quotes from Excel's data types are provided 'as is,' may be delayed, and are not guaranteed accurate. Use the tracker to monitor, and confirm cost basis and realized gains against your broker's official 1099 statements.
Columns and Formulas That Do the Work
A clean tracker needs one row per holding and a handful of columns. Enter shares and cost basis by hand; let formulas handle everything downstream so the sheet recalculates itself whenever prices refresh.
Market value is shares times live price. Weight is each holding's value divided by the portfolio total — anchor the total with absolute references (the $ signs) so the formula copies down correctly. Gain is value minus cost basis. That's the entire engine.
| Column | Formula (row 2, total in E10) | Purpose |
|---|---|---|
| Live price | =A2.Price | Current quote via STOCKS type |
| Market value | =B2*D2 | Shares x price |
| Weight | =E2/$E$10 | Position's share of the portfolio |
| Gain/loss ($) | =E2-C2 | Value minus cost basis |
| Gain/loss (%) | =(E2-C2)/C2 | Return on this position |
| Total value | =SUM(E2:E9) | Portfolio total |
Tip: Format gain/loss with conditional formatting (green positive, red negative) and the weight column as a percentage — the whole portfolio becomes readable in one glance.
Want the full framework? This 2-hour ETF course teaches you exactly how to pick, buy, and hold profitable ETFs — from zero to confident investor. Under $15.
Templates Versus Building Your Own
Excel's template gallery and plenty of free downloads offer pre-built portfolio and net-worth trackers. They're a fine starting point, but many are heavier than you need and some rely on macros or paid add-ins for prices. Building the six or seven columns above yourself takes about 20 minutes and leaves you understanding every formula — which matters when something breaks a year later.
Whichever route you take, keep a separate tab for transactions (date, ticker, buy or sell, shares, price). That running log is what lets you reconstruct cost basis accurately, and it's the part brokerages don't always make easy to export in one place.
What the Tracker Should Actually Tell You
The point of a tracker isn't to recompute a number your broker already shows. It's to surface three things your broker buries: total weight per holding across all accounts, your blended cost of ownership (the expense ratios you're paying), and whether your allocation has drifted from plan. Add a small reference column for each fund's expense ratio so you can see the annual cost you're carrying.
When a weight drifts far from target, that's your cue to rebalance. To see what your combined funds really hold underneath — and whether two 'different' ETFs overlap heavily — run them through our Portfolio X-Ray, and use the ETF return calculator to project contributions forward.
Ready to invest? Open an IBKR account in 10 minutes and get free stock. $0 commissions on US ETFs • Fractional shares from $1 • 150+ global markets.
Frequently Asked Questions
How do I get live stock prices in Excel?
In Microsoft 365, type a ticker, select the cell, then click 'Stocks' on the Data tab to convert it to a linked data type. You can then pull the price with a formula like =A2.Price. The data is delayed, not real-time, and older non-365 versions don't include this feature.
Does the Excel STOCKS data type work for all ETFs?
It covers many ETFs and stocks but not every fund. If a ticker won't convert to the Stocks data type, enter that fund's price manually and refresh it occasionally. Newer launches and some foreign or niche products are the most likely to be missing.
Is Excel or Google Sheets better for tracking investments?
The layout is nearly identical; the difference is price fetching. Google Sheets has GOOGLEFINANCE() for free, while Excel uses the STOCKS data type in Microsoft 365. Choose Excel if you already pay for 365 and want desktop charting; choose Sheets if you want a free, always-synced option.
Should I use a downloaded template or build my own?
A free template is a fine starting point, but many are bloated or depend on macros for prices. Building the seven core columns yourself takes about 20 minutes and means you understand every formula — which matters when you need to fix or extend it later.
Further Reading
Free Tools
Alex Harrington
CFA Level II Candidate, Finance & Economics
Alex Harrington is an independent ETF researcher and personal finance writer with over 8 years of experience analyzing exchange-traded funds. A CFA Level II candidate with a background in economics, Alex has reviewed 800+ ETFs and helped thousands of beginners build their first investment portfolios through clear, jargon-free education.
This content is for educational purposes only and does not constitute financial advice. Past performance does not guarantee future results. Consult a licensed financial advisor before making investment decisions.