A spreadsheet tracker needs two tabs: a log with every buy and sale, and a holdings tab that adds up shares and cost per ticker with SUMIFS. Prices come from =GOOGLEFINANCE("NASDAQ:AAPL") in Google Sheets, delayed up to 20 minutes, or from Excel's Stocks data type with =A2.Price. Value, gain and weight are simple arithmetic, and XIRR gives your yearly return. What no formula does for you is dividends and stock splits: you type them in, or the sheet is wrong.
=GOOGLEFINANCE(A5)=A5.Price| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Ticker | Shares | Avg cost | Cost | Price | Value | Gain | Gain % |
| 2 | VOO | 10 | 357.74 | 3,577.40 | 698.58 | 6,985.80 | 3,408.40 | 95.3% |
| 3 | AAPL | 10 | 180.09 | 1,800.90 | 347.73 | 3,477.30 | 1,676.40 | 93.1% |
| 4 | MSFT | 8 | 382.77 | 3,062.16 | 524.51 | 4,196.08 | 1,133.92 | 37.0% |
| 5 | SCHD | 60 | 79.68 | 4,780.80 | 35.27 | 2,116.20 | -2,664.60 | -55.7% |
| 6 | Total | 13,221.26 | 16,775.38 |
Row 5 is wrong. SCHD isn't down 55.7%: it split 3 for 1 in October 2024, so the 60 shares in the log are 180 today, worth $6,348.60 and up 32.8%. The example, in full
Keep one row per trade and let every other number come from formulas, so a new trade is one new line. The column letters match the holdings tab above.
Columns A to E: Date, Action, Ticker, Shares, Price. Write "Buy" or "Sell" in Action, and add a Fees column if your broker charges them. Never type a total here; the other tabs add up this log.
=D2*E2One row per ticker. Shares are everything bought minus everything sold; average cost is what you paid for the shares you bought, divided by how many. That average stays right as long as every buy of a ticker comes before its first sale. Buy again after selling and you need a running average, which takes a helper column in the log.
=SUMIFS(Transactions!D:D,Transactions!C:C,A2,Transactions!B:B,"Buy")
-SUMIFS(Transactions!D:D,Transactions!C:C,A2,Transactions!B:B,"Sell")=SUMIFS(Transactions!F:F,Transactions!C:C,A2,Transactions!B:B,"Buy")
/SUMIFS(Transactions!D:D,Transactions!C:C,A2,Transactions!B:B,"Buy")=B2*C2Google Sheets fetches prices itself. Put the exchange before the ticker when a symbol trades in more than one place.
In Excel, type the tickers in column A, select them and choose Data > Stocks. Each cell becomes a linked stock, and its fields come out with a dot. Refresh with Data > Refresh All.
=GOOGLEFINANCE(A2)=A2.Price=INDEX(GOOGLEFINANCE("NASDAQ:AAPL","close",DATE(2025,2,3)),2,2)=INDEX(STOCKHISTORY("AAPL",DATE(2025,2,3)),2,2)=GOOGLEFINANCE("CURRENCY:EURUSD")Type EUR/USD in a cell and choose Data > Currencies (Microsoft 365), then:=A2.PricePlain arithmetic on the columns before. Format H and I as percentages, and lock the total range with dollar signs so the formula copies down.
=B2*E2 =F2-D2=G2/D2 =F2/SUM(F$2:F$5)Gain % ignores when you put money in, so a holding bought last month and one held for ten years look alike. XIRR gives the yearly rate that fits every dated cash flow: each buy as a negative amount, each sale and dividend as a positive one, and today's total value as the last positive amount dated today. Excel and Google Sheets both have it, with the same arguments.
=XIRR(B2:B60,A2:A60)Four buys and one sale between March 2023 and February 2025, each at that day's closing price, valued at the close on 23 October 2026. Every formula above is working correctly. The sheet is still wrong in two ways.
$4,232.40 missing, all of it SCHD's 3-for-1 split.
49 payments that no formula returns.
With the split and the dividends counted. The split alone gives 16.73%.
| Date | Action | Ticker | Shares | Price |
|---|---|---|---|---|
| 15 Mar 2023 | Buy |  VOO | 10 | $357.74 |
| 1 Jun 2023 | Buy |  AAPL | 15 | $180.09 |
| 10 Jan 2024 | Buy |  MSFT | 8 | $382.77 |
| 20 May 2024 | Buy |  SCHD | 60 | $79.68 |
| 3 Feb 2025 | Sell | AAPL | 5 | $228.01 |
The AAPL sale realised $239.60 of gain at the average cost of $180.09. SCHD was $79.68 a share before its split.
| Holding | Shares | Value | Gain | Dividends |
|---|---|---|---|---|
| VOOVanguard S&P 500 ETF | 10 | $6,985.80 | 95.3% | $257.86 |
| AAPLApple | 10 | $3,477.30 | 93.1% | $40.35 |
| MSFTMicrosoft | 8 | $4,196.08 | 37.0% | $73.68 |
| SCHDSchwab US Dividend Equity ETF | $6,348.60 | 32.8% | $470.56 | |
| Total | $21,007.78 | $842.45 |
Dividends are what each holding paid on the shares owned at each ex-dividend date, through 23 October 2026.
Neither error comes from a broken formula. The split changed the number of shares without a trade, so the log never saw it. The dividends are cash that arrived without a trade either. Fix them by hand: add a line to the log for each split (here, a buy of 120 SCHD at $0 on 11 October 2024, with the cost unchanged), and type each dividend into its own tab.
Three built-in ways to get market prices into a sheet. Each covers part of the job.
| GOOGLEFINANCE | Stocks data type | STOCKHISTORY | |
|---|---|---|---|
| What you need | A Google account, free | Microsoft 365, or a free Microsoft account | Microsoft 365 Personal, Family, Business Standard or Business Premium |
| Latest price | Yes, delayed up to 20 minutes | Yes, delayed | No, it generally updates after the trading day ends |
| Past closes | Yes, daily or weekly | No | Yes, daily, weekly or monthly |
| Other fields | P/E, EPS, market cap, 52-week high and low, beta, currency | Change, previous close, exchange and more, by field name | Open, high, low and volume |
| Your dividends | No | No | No |
| Splits in your log | You add them | You add them | You add them |
Two limits catch people out. Google Sheets doesn't support mutual fund attributes, and its historical prices can't be read by the Sheets API or Apps Script, so a script that copies past closes gets #N/A. A quote can also be up to 20 minutes old, which is fine for a tracker and too slow to trade on.
No built-in function returns the dividends you were paid, so a dividend tracker spreadsheet is a log you fill in from your broker statements: one row per payment.
Columns A to F: Pay date, Ticker, Per share, Shares, Amount, Tax withheld. Shares are the ones you held going into the ex-dividend date, which is not always what you hold on the pay date.
=C2*D2Add up one ticker's payments in a calendar year, then divide by what you paid for the holding.
=SUMPRODUCT((YEAR(Dividends!A2:A200)=2025)
*(Dividends!B2:B200="SCHD"),Dividends!E2:E200)=income/Holdings!D5In the example, SCHD paid $188.57 in 4 payments in 2025 on 180 shares, a 3.94% yield on the $4,780.80 paid for them. A sheet that still holds 60 shares would multiply each 2025 payment by 60 and show a third of that. More on the formula in dividend yield and yield on cost.
None of them shows up as an error. The numbers just drift away from your broker's.
A split changes your share count without a trade. The price formula follows the split the next morning and the log doesn't, which is how 60 SCHD shares became a 55.7% loss in the example.
Every payment is a row you type. Reinvested dividends are also new shares at a new cost, so they belong in the trades log too, or your share count falls behind.
Each broker exports a different layout, with its own column names, date formats and ways of writing a sale. Pasting them into one log means reshaping every file, every time.
A Toronto holding needs a price in Canadian dollars and an exchange rate, and London prices usually come in pence, so divide by 100 first. For an honest gain, the cost needs the rate on the day you bought, not today's.
Average cost is easy in a sheet. Tax rules often aren't: the US and many other countries match sales to specific lots, first in, first out by default, which takes one row per lot and a formula that walks through them. See FIFO vs LIFO.
Mutual fund attributes aren't supported in Google Sheets, and neither app quotes option contracts, so option prices are typed in or pulled from somewhere else. For bonds, Excel's Stocks data type covers some issues, and PRICE works out a price from the coupon and a yield you supply.
Every column of the holdings tab, from price and shares to gain, weight and the XIRR, is what AllInvestView shows for each holding without a formula. Bring the trades tab you already have and it goes further: dividends arrive on their own, splits get caught, and every broker and currency sits in one portfolio.
Download the tab as CSV or Excel and upload it. Magic Import reads your own column names, here Date, Action, Ticker, Shares and Price, with no template to fill in. It lists what it booked, with an undo, and it caught the SCHD split from October 2024 that the sheet missed: one click books the 120 new shares.
See Magic Import
AllInvestView
Dividends for your stocks and ETFs come in by themselves, counted on the shares you held going into each ex-dividend date. The report adds them up by payer, month and year and forecasts the next 12 months.
See the dividend trackerAllInvestView
The dashboard shows your money-weighted return as Growth Rate (IRR), the same sum as XIRR, and the time-weighted CAGR beside a benchmark. Drawdown, volatility, beta and the wealth multiple sit underneath, for any start and end date.
See the portfolio trackerAllInvestView
A sheet gives you any layout and any formula you can write. A tracker gives you the parts no formula can fetch.
| Excel or Google Sheets | AllInvestView | |
|---|---|---|
| Prices | Stocks and ETFs through a function, delayed | Stocks, ETFs, funds, bonds, options and crypto, each in its own currency |
| Your trades | Typed or pasted, one broker layout at a time | CSV or Excel files from your broker or your sheet through Magic Import, or synced from a supported broker on paid plans |
| Splits | Added by hand | Flagged after an import and booked in one click |
| Dividends | Typed in from statements | Filled in, with a 12-month forecast |
| Return | XIRR on a cash flows tab you keep | IRR and time-weighted return against a benchmark |
| Sales and tax | Average cost, unless you build lot matching | FIFO, LIFO or average cost, and a capital gains report by country |
| Layout | Anything you can build | Set screens, with holdings and transactions exporting to Excel |
| Cost | Free with a Google account; Excel's stock data needs Microsoft 365 or a Microsoft account | Free up to 10 holdings; paid plans for more holdings and broker sync |
Yes. GOOGLEFINANCE("NASDAQ:AAPL") returns the latest price, delayed by up to 20 minutes, and GOOGLEFINANCE(ticker, "close", date) returns past closes. Keep your trades on one tab and add up shares per ticker with SUMIFS on another. Google Sheets does not return dividends, and its mutual fund attributes are not supported.
Type the tickers in cells, select them and choose Data > Stocks. Each cell becomes a Stocks data type, and =A2.Price returns its price. It needs a Microsoft 365 subscription or a free Microsoft account, and the quotes are delayed. STOCKHISTORY returns past closes but needs a paid Microsoft 365 plan and generally updates only after the trading day ends.
Not with the built-in functions. Neither GOOGLEFINANCE nor STOCKHISTORY returns dividends, so you type each payment from your broker statement into a dividends tab. In our example that is 49 payments worth $842.45 over three and a half years.
Use XIRR. List every buy as a negative amount, every sale and dividend as a positive amount, and today's value as the last positive amount, each with its date. =XIRR(amounts, dates) gives your annual return. Our example sheet returns 18.63% a year with the dividends and the split counted, and 8.6% without them.
Nothing, and that is the problem. After SCHD split 3 for 1 in October 2024, its price fell to about a third, but a sheet that still holds 60 shares reports a 55.7% loss on a holding that is up 32.8%. You have to add the split to your trade log yourself.
Yes. Download your trades tab as CSV or Excel and upload it to Magic Import, which reads your own column names without a template and lists what it booked, with an undo. Dividends are then filled in for you, a split after your purchase is flagged to book in one click, and you can still export your holdings and transactions to Excel.
Upload the sheet you already have and AllInvestView fills in the dividends, flags the splits and works out your return, for every broker you use.
Prices are each day's closing price; dividends are the amounts each fund and company paid per share. Checked on 26 October 2026.
