Short answer: make one row per holding with the ticker, account, number of shares and what you paid. Pull current prices in automatically, multiply to get each value, and add a few totals: your allocation, your contributions against your room, and your net worth. A spreadsheet works across every bank and brokerage you use, which no single brokerage app does.
What to track
| Track this | Why it matters |
|---|---|
| Holdings: ticker, account, shares, cost | The base for everything else |
| Current value and gain | See how each fund and account is doing |
| Allocation vs. your target | Shows which fund to buy next to stay balanced |
| Contributions by account | Stops you going over your TFSA, RRSP, FHSA, 401(k), IRA or HSA limits |
| Dividends | Your income from investing, month by month |
| Net worth | The one number that shows your overall progress |
Live prices in Google Sheets
Google Sheets has a built-in function that fetches prices:
-
US funds and stocks:
=GOOGLEFINANCE("VTI", "price") -
Canadian (TSX) funds and stocks: add the exchange, like
=GOOGLEFINANCE("TSE:XEQT", "price") -
Exchange rate:
=GOOGLEFINANCE("CURRENCY:USDCAD"), to convert US holdings to Canadian dollars or the other way around
Prices can be delayed by up to 20 minutes and not every fund is covered, so keep a column where you can type a price by hand. The tickers above are examples, not recommendations.
In Excel with Microsoft 365, type the tickers, select them and choose Data > Stocks to turn them into linked stock data, then pull the price from there.
A simple layout
- Holdings tab: one row per fund per account, with Ticker, Account, Shares, Total cost, Price (formula), Value = Shares × Price, and Gain = Value − Total cost.
- Allocation tab: group the values by asset class (for example US stocks, Canadian stocks, international, bonds) and divide each by the total. Put your target percentages next to them.
- Contributions tab: one row each time you add money, with the date, account and amount. Total them per account per year and compare with your limit.
- Net worth tab: investments plus cash minus debts, recorded once a month.
Using it to decide where to invest next
Compare your actual allocation with your target. If your target is 60% US stocks and you're at 65%, send your next contribution to the funds that are under target instead of selling anything. In a taxable account, that avoids triggering capital gains.
Common mistakes
- Not tracking contribution room. Brokerages only see their own accounts, so going over a limit across two providers is easy. The tax penalties can be steep.
- Forgetting the currency. Mixing USD and CAD values without converting them makes your totals wrong.
- Checking every day. Updating once a month is enough, and it makes market drops easier to ignore.
Common questions
Excel or Google Sheets?
Google Sheets is free and updates prices on its own, so it's the easier choice for most people. Excel is better if you want everything offline.
Is it safe to put my investments in a spreadsheet?
Your tracker only needs tickers and amounts, never account numbers or passwords. Keep it that way.
Skip the setup
The Asset Class Co. Portfolio Tracker has all of this built in: Holdings, Allocation, Dividends, Contributions, Contribution Room for the TFSA, FHSA, RRSP, 401(k), IRA and HSA, Net Worth, and Growth & Goals. It works in Excel, or in Google Sheets with live prices, and it shows where your next contribution should go to stay on target.
Educational content only, not financial, tax or legal advice. Tickers shown are examples, not recommendations.