Dividend Tracker With Google Sheet: Build Yours in 6 Steps

Tracking your portfolio performance is a really important step to successful investing. If you don’t know how your portfolio is doing, you won’t know if you are making the right decisions.

If you can’t evaluate your investment decisions, you will go in a circle and realize years later your portfolio is not really growing the way it should. It can lead to drastic investment decisions to recover time lost … We don’t want that. A portfolio & dividend tracker can help assess the income gap in your retirement and allow you to take appropriate action.

Here is a simple view on how money flows when it comes to investing. Are you asking yourself those questions? Are you able to answer them?

You also need to know what’s in your portfolio and what it means to your performance such as your exposure to the different sectors or the dividend income your investments bring in. With respect to planning for retirement, you certainly want to know how much dividend you can earn in a year or by the month. The dividend income should grow and you can then forecast how much to save and invest to reach your goals. Can your portfolio keep up with inflation is a critical question you also need to be aware of.

As you can see, it all works together but it’s not easy to get set up properly to answer all of those questions. You need to use Google Sheet (Excel or any other spreadsheet can also work) and understand how to lookup data in other sheets.

Fear not, below are the steps to do it yourself for your free dividend tracker or you can save the time and get the Dividend Snapshot Tracker all setup and ready to go. You can have your own personal dividend calculator and make it the best dividend tracker!

DIY Portfolio & Dividend Tracker

Before we dive through the steps to build your portfolio tracker, we need to discuss the status of quotes retrieved online.

The Yahoo Finance API (Application Program Interface) used to allow fetching of the stock quote but it has been deprecated for use outside of their website. Google Sheet, with the GoogleFinance API, is now the only spreadsheet capable of fetching quotes. Excel is manual or requires add-ins.

If you go down the manual route, getting a stock quote can be done through Google Finance, Yahoo Finance, Morningstar or your discount broker. Your discount broker is probably best as you have all your holdings in one location (or a few if you have multiple accounts).

Do note that Google Finance changed their portfolio management and I have research alternatives to Google Finance with MSN Money leading as the alternative.

IMPORTANT – There are very limited services that can pull quotes in a reliable manner for spreadsheet since Yahoo deprecated their web API. You either need to use Google Sheet with the GoogleFinance function or you have to manually enter the stock price.

Step 1 – Stock Info List

The first step is to itemize your holdings and the details pertinent for reporting. You can track more information if you want such as the industry, international exposure, growth vs income investment. It’s really up to you to track what you want. The information entered here can be looked up in the other tabs (note that the second row, hidden in the image, is a column number starting with 0 to facilitate lookups).

Step 2 – Setup Your Stock Details

The stock details tab is where you list all of your holdings per account. The light grey cells can be automated through a formula or a lookup into another tab.

The following columns are up to you to define but I track at a minimum the following columns:

• The account the stock is held. I sometimes have the same stock in multiple accounts.
• The stock ticker (to reference the other tab).
• The sector if you care about it. Each stock exchange appears to track their own categorization of a company. Sometimes it’s obvious but not always and the industry, which is a sub-category of a sector, can also be different. Stick to one source of truth for the sector.
• Total shares represent what your account has in total.
• Quote is retrieved from the other tab using a VLOOKUP function
• Market value is the simple math of total shares multiplied by the quote.
• Primary currency value is where I multiply the market value by the CADUSD=X exchange rate where applicable. If the ticker ends in a “.TO”, then the exchange rate is applied.
• Dividend yield can be listed once you enter the dividend and you can track your yield on cost if that matters to you based on your average purchased cost.

Following that, you can decide on the information you want on this tab. At a minimum, you have the current market value of your investment. The amount invested can tell you how your holdings are doing vs your purchase price but it’s not a good performance metric.

The stock valuation is an important step in setting up your portfolio and dividend tracker to get an overall picture of your accounts. I prefer to look at the account performance rather than the individual stock performance otherwise, it becomes a stock picking process and you get into bad habits of trying to predict the performance of an individual stock.

Ater this step, you should have a second tab listing all of your stocks with all the columns you want per holdings per account. The summary portfolio performance is optional at this point.

Step 3 – Reporting & Sector Diversification

All you need to do is assign a sector to your stock holdings as seen above and you can report on your sector exposure by using the SUMIF function in Excel or Google Sheet. The target per sector is for you to identify based on your investment goals. Over time, you will see that sectors perform differently and you can use that to decide which investment to add to. It took me a few years to really adjust the target to what worked for me. Do a first pass and review it every quarter or year to see if it matches your investment philosophy.

After this step, you should have a third tab summing up your holding value per sector if that’s of interest.

Step 4 – ROR and Contribution Tracking

This is where the magic happens for my portfolio. I know exactly what the performance of my portfolio is and better yet, with Google Sheet, I compare it to indexes and I have a benchmark.

As I have explained a number of times, the performance of your portfolio is a factor of your contributions to your accounts and your overall portfolio value. The individual stock transactions or stock holdings are not the focus or the DRIP price for that matter. You just need to track the money going in and out of the account versus the total value set up in summary tab of Step 2 above. With that information, you just have to apply the XIRR Excel function and you will have the ROR for your portfolio.

After this step, you should have your 4th tab tracking all the money going in and out of each account. This is simply set up to use the XIRR function to calculate the annual rate of return.

Step 5 – Transactions Tracking

You either want to track all of your transactions or you want to track those in accounts where you need to pay taxes. Either way, it’s going to take time on your part to track the BUY, SELL and DRIP.

As seen above, all of the ‘light grey’ shaded cells are computed and white cells require information from your accounts. It’s all the information you need to track your capital gains. The following fields are needed for the capital gains tax calculation.

• Stock Ticker – To know which stock your transaction is for
• Trade Action – Is it an addition or a subtraction
• Shares – The number of shares you added or removed
• Quotes – The price of the stock at the time of the transaction
• Amount – This is just the share count multiplied by the price of the transaction
• Transaction Fee – That’s usually tax deductible, don’t forget it.
• Currency Exchange – Currency exchange needs to be considered for your transactions as profit from currency is taxable

Remember, you can hold those investments for years if not decades and it’s your responsibility to file the capital gains properly. The onus is therefore on you to stay on top of it.

After this step, you should have a 5th tab providing you with all the data necessary to calculate your capital gains if you want to track that.

Step 6 – Dividend Income Tracking

If one of your investment goals is to have your dividend income provide in retirement, tracking it is a must. Otherwise, you won’t be able to extrapolate future income, especially once you stop adding investment money.

This is quite a bit of work as every month, you need to update the dividend income for each stock paying for that month. The dividend rate may have changed so it needs to be looked up to keep it accurate.

The effort is worth it as you can holistically have access to your dividend history. You can also start breaking down which stocks contributes the most to your dividend income. If you choose to track your dividend income, you have a 6th tab to your spreadsheet.

The Google Portfolio Tracker

Google Sheets is now the best framework to build a portfolio or dividend tracker spreadsheet and see you value updated automatically. All other options are more complicated.

The good news is that for what you need, Google Sheets is as good as Excel. You can also use it on your phone easily – that’s a bonus.

They are the basic introduction to tracking capital gains and dividend income but it’s a good start depending on what you need.

One key point to be aware of is that Canadians will usually deal with both USD and CAD dollars whereas Americans will usually only have USD. It becomes important that USD stock holdings are converted to CAD dollars to establish the proper market value in the home currency. Google Sheets can automatically convert the currency as well.

Don’t forget to use the DLR Norbert’s Gambit to execute your currency conversion.

Dividend Snapshot Tracker

Google Sheets is the default for the Dividend Snapshot Tracker. If you choose to get the spreadsheet, all you need to do is enter the information needed:

• Accounts where you hold stocks
• Stock information such as sector, and dividend (not automatic)
• All your transactions such as buy, sell, div and DRIP
• Your contributions to your account to calculate your true Rate of Return

You end up with a proven, and tested, portfolio tracker with dividend history per stock, per month or annually.

Provided above is the step by step process to creating your own tracker with examples and options to get you started. I spent over 5 years getting to this stage and refining my process for tracking all the details I need to make informed decisions.

The discount brokers I have used and Quicken have all failed in giving me a clear picture of my performance until I got set up with this spreadsheet.

No excuses, now you have it all available. With some minimal spreadsheet skills, you should know exactly what your rate of return is and be confident in making good portfolio decisions.

With all of the options offered above, there really are no reasons to not have a dividend tracker and understand the performance of your portfolio.