Investment Portfolio Tracker — a Spreadsheet for DIY Investors

To keep track of regular dividends that are received as cash:

• Enter a “dividend” transaction on the Trade Log tab, where you input the date, ticker, total $ amount, and investment account where the dividend was received
• It’s not necessary to enter the quantity of shares or amount per share (however, no issues if you do want to enter this extra info) — just entering a total amount in column G is sufficient

 
To input dividends that are reinvested into new shares (a “DRIP” strategy):

• Enter your dividend as two separate transactions. One transaction is the regular “dividend” transaction, and the second transaction is a “buy” transaction to record the purchase of additional shares
• For example, assume you get a dividend of $20 which was reinvested into 4 new shares of XYZ Corp. Enter two transactions: a “dividend” transaction where you receive a total amount of $20, and then a separate “buy” transaction where you buy 4 new shares of XYZ for $5 per share
• The spreadsheet will now record that this dividend has been reinvested to purchase additional shares of XYZ

Please go to the google finance website (https://www.google.com/finance), type the name of the name of the company or fund in the search bar, and copy the ticker that Google Finance provides (the full ticker including the exchange).

Then, use that full ticker symbol in the investment portfolio tracker, on the Setup tab. Make sure that you delete any spaces in the ticker name.

For example, for the Bank of Nova Scotia company on the Toronto Stock Exchange, enter “TSE:BNS” instead of just “BNS”.

For any stocks or mutual funds which don’t appear on Google Finance, you can do the following in order to have them included in your total portfolio:

• Add a new holding on the Setup tab — any ticker is fine as long as it isn’t an actual ticker on google finance (for example, “$TDB900” for the TD e-series 900 Canadian Index fund)
• Then, use the manual price entry columns on the Your Portfolio Holdings tab (columns H, AF, AL) — since Google Finance won’t return a price per share for these tickers automatically, the spreadsheet will use these manual entry prices instead

Now, the Dashboard tab will show these stocks or mutual funds in your total portfolio value.

Similar to the answer above, try entering the full google finance ticker for the company, including the stock exchange symbol.

For example, for the Bank of Nova Scotia company on the Toronto Stock Exchange, enter “TSE:BNS” instead of just “BNS”. There is another company with the ticker BNS that is traded on the New York stock exchange, so google finance gets confused if you don’t enter the full ticker.

If the market prices still look wonky, go back to the Dashboard and check the date cell (C2). All of the market prices will be pulled as of that date, so much sure that it’s set to the date you want to see!

Absolutely! This investment portfolio tracker can be used by investors in any country. The spreadsheet even allows you to track investments in multiple currencies (up to five), all in one place.

For the calculations and outputs in the spreadsheet, all amounts are converted into a single common currency based on the currency that you select at the top of the Dashboard tab (cell C3).

So, if you now select GBP (British Pounds), all values will now convert to GBP. Likewise for CAD, USD, JPY, etc…

If something just seems off in the spreadsheet — values of “n/a”, “#REF”, or “Error” for example, it’s possible that some formulas were broken accidentally as you inputted your data.

The best way to proceed is to open a fresh copy of the spreadsheet.

Once you’ve done that, copy the values from the “Setup” and “Trade Log” tabs of your old version, and paste them into the fresh spreadsheet.

9 times out of 10 this does the trick!

This spreadsheet calculates the total return of your portfolio over any given time period — including realized gains, unrealized gains, and dividends.

Go to the Dashboard tab, and scroll down to the Performance section. Once you enter a start date and end date, the spreadsheet will calculate the “money-weighted” return of your portfolio.

The money-weighted return is equivalent to the “internal rate of return” (or IRR) of your portfolio. This return calculation captures portfolio contributions, withdrawals, realized / unrealized gains, and dividends received.

If the time period you’ve entered is greater than 1 year, the result will be shown on an annualized basis. As an illustrative example, the tool would show a return of 7% per year instead of a total three-year return of 22.5%.

If the time period you’ve entered is less than 1 year in length, the result will not be annualized. For example, if your portfolio has earned a return of 5% over 3 months, the spreadsheet will show a return of 5%, rather than an annualized value of 21.6% (which would be misleading in my opinion).

By scrolling further down in the Performance section, you’ll also find the money-weighted returns shown for each of your custom investment categories. This allows you to compare the relative performance of different groups of investments (e.g., U.S. stocks, Canadian stocks, bonds, etc.).

Please note that the spreadsheet does not calculate the money-weighted return of individual stocks at this time.

However, you can view the unrealized gains and dividends received per stock over the life of the investment on the “Your Portfolio Holdings” tab.

You can also view your realized gains per stock on the “Realized Gains” tab.

This tool wasn’t built to track cash. However, if you do want to keep tabs on your cash balance as well, you can try this workaround:

• On the “Setup” tab, add a new stock ticker called $CASH (or something else that doesn’t return a market value on google finance)
• Whenever you get extra cash in your account — dividends, contribution, etc., enter in a buy transaction on the Trade Log where you purchase X shares of cash at $1 per share (X being the total amount of added cash)
• Then, on the Your Portfolio Holdings tab, in the manual price entry columns (H, AF, AL), input a value of $1 per share for your $CASH investment
• Now, you’ll see your new ticker $CASH, and the corresponding total $ amount reflected
• If you use up that cash, remember that you’ll have to enter a corresponding sell transaction

I’ve written a script (a.k.a., a few lines of code) that runs when you click the “Go!” button.

This allows the spreadsheet to cycle through each month in your trading history and record the performance of your portfolio — starting value, contributions, withdrawals, investment returns, and the ending value.

Specifically, the script will copy the numbers in row 76 of the Dashboard tab, and then paste those values into columns N to T of the Monthly Performance tab.

Please note that these numbers as “pasted as values”. Therefore, they will not update if you add new transactions to the trade log that impact a prior month.

If you do update your trade log to reflect new transactions in prior months, you just need to delete the numbers found in columns N to T (delete only the data, not the columns themselves), and then re-run the script.

Yes! The spreadsheet can handle stock splits and reverse splits swimmingly.

To enter a split transaction, you need to input the following information in a row on the trade log tab:

• Date when the split occurred — in column B
• Transaction type of “Split” — in column C
• Stock ticker — in column D
• Split Ratio (new shares per old share) — in column J

You can leave columns E through I empty.

To illustrate with an example, let’s assume that you purchased 10 shares of Apple (AAPL ticker) on August 1st, 2020, for $415 per share.

Apple subsequently split its shares on a 4-for-1 basis on August 28th, 2020.

This means that you hold 40 shares after the split, given that you purchased 10 shares initially, and then the split occurred, giving you 4 new shares per 1 old share.

To reflect this on the trade log, you would need to enter two rows of data — one BUY transaction, and one SPLIT transaction:

Now, when you flip to the Your Portfolio Holdings tab, you’ll see the correct values:

Notice how you now own 40 shares of AAPL, and have a cost base of $103.75 per share — this is because your initial cost base of $415 per share was divided by 4 after the split occurred.

When you enter a split, you can leave column I (Investment Account) blank.

You only need to enter a split once even if you hold the same ticker in multiple accounts — it will automatically be applied across all accounts when you enter it once on the trade log tab.

In some cases, companies will perform a “reverse” split, where your number of shares will decrease after the split. This should be handled in the same way, where you enter the appropriate split ratio in column J.

For example, if a company does a reverse split on a 1-for-10 basis (1 new share per 10 old shares), you can enter either “0.1” or “=1/10” in column J. The two methods are equivalent.

Unfortunately not.

For those keeping score at home, you’ll know that I have a preference for excel over Google Sheets. However, I haven’t been able to find a reliable way of getting current and historical stock/ETF price data to import automatically into excel.

While Office 365 has a new “Stocks” feature that lets you import real-time stock prices into excel, this unfortunately doesn’t work for historical prices (yesterday, last month, last year’s price, etc).

This spreadsheet uses historical pricing info to calculate portfolio performance between any two dates, so historical prices are a critical input.

Yes they are! This spreadsheet tracks crypto currencies in the same way that it tracks more traditional stocks and ETFs.

On the Setup tab, input the ticker as the label for the crypto currency (e.g., BTC), followed by the label for the currency that it is priced in (e.g., USD). So, if you buy bitcoin with US dollars, the code is “BTCUSD”.

In the screenshot below, I’ve shown the tickers for Bitcoin and Ethereum, first priced in US dollars, then priced in Canadian dollars.

Next, use the trade log to input your transactions as usual.

Now, the spreadsheet will track your crypto currency investments and will include them in your total portfolio value, asset allocation, and all performance calculations.

Yes — the spreadsheet does support fractional / partial shares. Here’s an example portfolio showing an investor buying 0.4 shares of Apple and 0.8 shares of JP Morgan.

If you flip between the Trade Log, Dashboard, and Your Portfolio Holdings tabs, you can see the calculations properly reflected.

In your spreadsheet, you’ll just need to increase the decimal points shown in the columns where the spreadsheet shows the number of shares you hold (I did this in the example I created). As it stands, the spreadsheet shows 0 decimals by default, but you can easily show more decimals by using the toolbar at the top of the sheet.

By default, the spreadsheet is set up to accept dates in MM-DD-YYYY format. For example, February 15, 2021 would be inputted as 02-15-2021.

To change the date formatting to your liking, you can update the format manually in the Trade Log by selecting the cells in the Date column, then Format > Number > More Formats > Custom Number, and then type in your preferred format — e.g., DD-MM-YYYY or YYYY-MM-DD.

You can use this process for any and all other dates cells as well — e.g., at the top of the Dashboard tab.

This tool was built with fairly ‘vanilla’ investors in mind (buying individual stocks / ETFs), so unfortunately it does not handle options or derivatives well.

However, you can try this workaround:
– Add a new ticker symbol on the Setup tab (for example, AAPL-CALL if you are buying calls on AAPL)
– When you buy a call, add a buy transaction on the AAPL-CALL ticker, reflecting the premium paid
– When the call is exercised or expires, enter a sell transaction on AAPL-CALL at the price at which you sell ($0 if the option expires)

The price of this ticker won’t be tracked in real time since google finance does not recognize the ticker, but once you close your position out, the returns will be reflected in the spreadsheet’s performance calculations.

ETFs can give “distributions” in a few different forms. Depending on the type of distribution, there will be different impacts on the adjusted cost base of your investment / different tax consequences.

Here are some good guides for understanding fund distributions:

• TD – how ETFs are taxed
• TaxTips.ca – ETF tax treatment

This spreadsheet allows you to enter various transaction types on the Trade Log — including dividends, return of capital, reinvested capital gains distributions — and will adjust your adjusted cost base accordingly.