Skip to main content

Create a dividend income tracker with Google Sheets by simply using pivot tables

As my investment strategy is to buy stocks that pay regular and stable dividends during a long-term period, I need to monitor my dividends income by stocks, by months, and by years, so that I can answer quickly and exactly the following questions:

  • How much dividend did I receive on a given month and a given year?
  • How much dividend did I receive for a given stock in a given year?
  • Have a given stock's annual dividend per share kept increasing gradually over years?
  • Have a given stock's annual dividend yield been stable over years?

In this post, I explain how to create a dividend tracker with Google Sheets.

How to create dividend tracker with Google Sheets

Manage stock transactions with Google Sheets

I use a spreadsheet on Google Sheets to keep track of my stock portfolio's transactions. I insert a new transaction into the spreadsheet whenever I deposit money, withdraw money, use deposited money to buy stocks, receive money by selling stocks, and receive dividends by holding stocks. Each transaction is a row containing information about Date, Type, Symbol, Amount and Shares of that transaction. For further details, you should read my post Manage stock transactions with Google Sheets.

Use Google Sheets to manage transactions of a stock portfolio

Create dividend tracker with Google Sheets

My questions in the introduction can be answered by finding relationships in terms of dividends between months and years or between stocks and years. In the same time, a pivot table helps to see relationships between data points in a spreadsheet. Therefore a dividend tracker here is a combination of several pivot tables originated from the Transactions sheet.

Track annual dividend amount of stocks

Here are the steps to create a pivot table to track annual dividend amount of stocks:

  • As a preparation step, I create a support column (Year) for the year of each transaction. Year is derived from the Date column with this function: =YEAR(A2)
  • To create a pivot table fom the transactions, I follow the steps below:
    • Select all used columns in the Transactions sheet.
    • In the menu at the top, click Insert and then Pivot table.
    • Select the newly created pivot table sheet, if it’s not already open.
  • As I want to know the annual dividend amount contributed by a given stock, I need to configure a pivot table as follow:
    • In the Rows section, I click Add and select Symbol from the dropdown list.
      • All symbols that appeared in transactions will be displayed as rows in the pivot table.
      • I keep checked the Show totals check box because I want to know the total dividend contributed by a given stock since the beginning.
    • In the Columns section, I click Add and select Year from the dropdown list.
      • All years that appeared in transactions will be displayed as columns in the pivot table.
      • I keep checked the Show totals check box because I want to know the total dividend contributed by all stocks on a given year.
    • In the Values section, click Add, select Amount from the dropdown list, and choose Sum in the Summarize by select box.
    • In the Filters section, I choose to filter by Type of transactions and select only DIVIDEND value in the dropdown list.
Configure pivot table in Google Sheets to make a dividend tracker

As a result, I have a table with columns being years (sorted ascending), rows being stocks, and cells being the dividend amounts. I can quickly know the dividend amount contributed by any stock on any year by simply locating the corresponding cell in the table. In short, the pivot table gives me an overview picture of how much dividend each stock has contributed in each year and helps me to compare their performance.

Track annual dividend amount of stocks with Google Sheets by using a pivot table

Note: In fact, with all transactions tracked, it is not difficult to compute the amount of dividend contributed by a given stock on a given year, without using a pivot table.

For example, to know the amount of dividends contributed by the stock EPA:CS during the year 2020, I just simply need to sum the column Amount of all transactions, where:

  • TYPE is DIVIDEND
  • SYMBOL is EPA:CS
  • DATE is after 31/12/2019
  • DATE is before 01/01/2021

I can use the built-in SUMIFS function:

=SUMIFS(Transactions!D:D,Transactions!B:B,"DIVIDEND",Transactions!C:C,"EPA:CS",Transactions!A:A,">12/31/2019",Transactions!A:A,"<01/01/2021")

However, I need to repeat that formula if I want to query for another symbol or another year. Moreover, this way doesn't give me an overview picture of the dividends contribution among all stocks over years, as does a pivot table.

Track dividend amount by month and by year

To track dividend amount by month and by year, I just need to do another pivot table based on the same approach.

  • As a preparation step, I create a support column (Month) for the month of each transaction. Month is derived from the Date column with this function: =MONTH(A2)
  • I follow the same steps to create a pivot table from the transactions, and then configure it:
    • In the Rows section, I add Month as rows and sort them ascending. I keep checked the Show totals check box because I want to know the total dividend collected on a given month over all years.
    • In the Columns section, I add Year as columns and sort them ascending. I keep checked the Show totals check box because I want to know the total dividend collected in a given year.
    • In the Values section, I add Amount and choose Sum in the Summarize by select box.
    • In the Filters section, I choose to filter by Type of transactions and select only DIVIDEND value in the dropdown list.
Configure pivot table in Google Sheets to make a dividend tracker

As a result, I have a table with columns being years (sorted ascending), rows being months (sorted ascending), and cells being the dividend amounts. I can quickly know the dividend amount collected in any month of any year by simply locating the corresponding cell in the table. In short, the pivot table gives me an overview picture of how regularly and stably I have collected dividends.

Track dividend distribution by month and by year with Google Sheets by using a pivot table

Track annual dividend per share of stocks

Because each transaction is registered with the Amount and the number of Shares, it is easy to calculate the dividend per share on each transaction. To know the annual dividend per share of a stock, I can simply sum dividend per share of all transactions for that stock on a year.

To track the annual dividend per share of stocks, I just need to do another pivot table based on the same approach.

  • As a preparation step, I create a support column (Transaction Unit) for amount by share of each transaction. The column is derived from the Amount column and the Shares column with this function: =IF(ISBLANK(E2),,D2/E2)
  • I follow the same steps to create a pivot table from the transactions:
    • In the Rows section, I add Symbol as rows.
    • In the Columns section, I add Year as columns and sort them ascending.
    • In the Values section, I add Transaction Unit and choose Sum in the Summarize by select box.
    • In the Filters section, I choose to filter by Type of transactions and select only DIVIDEND value in the dropdown list.

As a result, I have a table with columns being years (sorted ascending), rows being stocks, and cells being the annual dividend per share. If I hold shares of a company long enough, the table allows me to track how the annual dividend per share of my holding stocks evolve over time.

Track annual dividend per share of stocks with Google Sheets by using a pivot table

Note: On the table, the dividend per share might not be the actual dividend per share of a stock in a given year. For example, in 2019, a company distributed dividends every quarter and I hold its shares for only 3 quarters, I would receive only 3 dividend payments. As a result, the tracker takes only into account those 3 dividend payments while calculating the dividend per share for that stock in 2019.

Track annual dividend yield of stocks

The annual dividend yield is a result of dividing the annual dividend per share by the price per share. As the annual dividend per share is already computed in the previous section, to compute the annual dividend yield, I still need to identify the price per share. However, during a year, a stock has many price points, and which one should be used among the price on the last day of year, the average price during a year, etc.? As an investor who keep track carefully transactions for a stock, I can easily know the cost per share of a stock in my portfolio. Therefore, I use the cost per share as a reference price to compute the dividend yield.

The cost per share of a stock in my portfolio is a result of dividing its cost by the number of shares that I have for that stock.

  • The cost is the sum of Amount for all BUY an SELL transactions related to that stock.
  • The number of shares is the sum of Shares for all BUY and SELL transactions related to that stock.

For example, to compute the unit cost for the EPA:CS stock until 01/01/2021, I need to compute how much money I have invested in that stock in exchange for how many shares (until 01/01/2021). It is not a difficult task by using SUMIFS function of Google Sheets:

Sum all Amount of BUY, SELL transactions for the EPA:CS stock until 01/01/2021 =ABS((SUMIFS(D:D,C:C,"EPA:CS",A:A,"<=01/01/2021",B:B,"BUY") + SUMIFS(D:D,C:C,"EPA:CS",A:A,"<=01/01/2021",B:B,"SELL"))) Sum all Shares of BUY, SELL transactions for the EPA:CS stock until 01/01/2021 =(SUMIFS(E:E,C:C,"EPA:CS",A:A,"<=01/01/2021",B:B,"BUY") + SUMIFS(E:E,C:C,"EPA:CS",A:A,"<=01/01/2021",B:B,"SELL")) The unit cost for the EPA:CS stock on 01/01/2021 is: =ABS((SUMIFS(D:D,C:C,"EPA:CS",A:A,"<=01/01/2021",B:B,"BUY") + SUMIFS(D:D,C:C,"EPA:CS",A:A,"<=01/01/2021",B:B,"SELL"))) / (SUMIFS(E:E,C:C,"EPA:CS",A:A,"<=01/01/2021",B:B,"BUY") + SUMIFS(E:E,C:C,"EPA:CS",A:A,"<=01/01/2021",B:B,"SELL"))

To track the annual dividend yield of stocks, I just need to do another pivot table based on the same approach.

  • As a preparation step, I create 4 support columns in the Transactions sheet:
    • Cost After A Transaction: is the cost for the stock presented in the transaction, taken into account the transaction's impact
    • Shares After A Transaction: is the number of shares for the stock presented in the transaction, taken into account the transaction's impact
    • Unit Cost After A Transaction: is the unit cost for the stock presented in the transaction, taken into account the transaction's impact
    • Dividend Yield Based On Unit Cost: is the transaction unit divided by the unit cost in case of a DIVIDEND transaction, taken into account the transaction's impact
Cost After A Transaction on the cell I2: =IF(ISBLANK(C2),"",ABS((SUMIFS(D:D,C:C,C2,A:A,"<="&A2,B:B,"BUY") + SUMIFS(D:D,C:C,C2,A:A,"<="&A2,B:B,"SELL")))) Shares After A Transaction on the cell J2: =IF(ISBLANK(C2),,(SUMIFS(E:E,C:C,C2,A:A,"<="&A2,B:B,"BUY") + SUMIFS(E:E,C:C,C2,A:A,"<="&A2,B:B,"SELL"))) Unit Cost After A Transaction on the cell K2: =IF(J2>0,I2/J2,"") Dividend Yield Based On Unit Cost on the cell L2: =IF(B2="DIVIDEND",H2/K2,"")

  • I follow the same steps to create a pivot table from the transactions:
    • In the Rows section, I add Symbol as rows.
    • In the Columns section, I add Year as columns and sort them ascending.
    • In the Values section, I add Dividend Yield Based On Unit Cost and choose Sum in the Summarize by select box.
    • In the Filters section, I choose to filter by Type of transactions and select only DIVIDEND value in the dropdown list.

As a result, I have a table with columns being years (sorted ascending), rows being stocks, and cells being the approximate annual dividend yield. The important thing is that those dividend yields are calculated based on my actual cost per share for those stocks in my portfolio. In short, the pivot table gives me an insightful picture to compare the performance in terms of annual dividend yield among my holding stocks.

Note: The annual dividend yield calculated by this way must be understood with cautious, because it is the sum of all dividend yields during one year. For example, if a stock pays dividend several times a year and in the same time, its unit cost fluctuates greatly during that one year, the sum of dividend yields might be abnormal.

Demo

You can take a look and make a copy of the sample dividend tracker spreadsheet to getting started. The sample dividend tracker spreadsheet includes:

  • Transactions sheet stores all transactions of the sample portfolio.
  • Annual Dividend Amount sheet contains the pivot table showing dividend amount by stocks and by years.
  • Dividend Amount By Month By Year sheet contains the pivot table showing dividend amount by months and by years.
  • Annual Dividend Per Share sheet contains the pivot table showing annual dividend per share of stocks.
  • Annual Dividend Yield sheet contains the pivot table showing annual dividend yield of stocks.
  • Annual Dividend Amount, Dividend Per Share, Yield sheet contains the pivot table showing annual dividend amount, annual dividend per share, annual dividend yield of stocks.

Conclusion

In this post, I have explained step-by-step how to create a dividend tracker with Google Sheets. The process involves registering a portfolio's transactions in a spreadsheet and then creating pivot tables based on those transactions. The dividend tracker gives me an overview picture of my dividend income and helps me adjust my decisions to make my investment strategy always on track.

References

Comments

People also enjoyed…

Create personal stock portfolio tracker with Google Sheets and Google Data Studio

I have been investing in the stock market for a while. I was looking for a software tool that could help me better manage my portfolio, but, could not find one that satisfied my needs. One day, I discovered that the Google Sheets application has a built-in function called GOOGLEFINANCE which fetches current or historical prices of stocks into spreadsheets. So I thought it is totally possible to build my own personal portfolio tracker with Google Sheets. I can register my transactions in a sheet and use the pivot table, built-in functions such as GOOGLEFINANCE, and Apps Script to automate the computation for daily evolutions of my portfolio as well as the current position for each stock in my portfolio. I then drew some sort of charts within the spreadsheet to have some visual ideas of my portfolio. However, I quickly found it inconvenient to have the charts overlapped the table and to switch back and forth among sheets in the spreadsheet. That's when I came to know the existen

Use SPARKLINE to create 52-week range price indicator chart for stocks in Google Sheets

The 52-week range price indicator chart shows the relative position of the current price compared to the 52-week low and the 52-week high price. It visualizes whether the current price is closer to the 52-week low or the 52-week high price. In this post, I explain how to create a 52-week range price indicator chart for stocks by using the SPARKLINE function and the GOOGLEFINANCE function in Google Sheets. Concept Demo Conclusion Concept With the GOOGLEFINANCE function, it's possible to retrieve the current price, the 52-week low price, and the 52-week high price of a stock by using the below formulas: =GOOGLEFINANCE("AAPL") returns the latest price of APPLE stock =GOOGLEFINANCE("AAPL","low52") returns the 52-week low price of APPLE stock =GOOGLEFINANCE("AAPL","high52") returns the 52-week high price of APPLE stock To measure the relative position of the current price compared to the 52-week low price and 52-wee

How to convert column index into letters with Google Apps Script

Although Google Sheets does not provide a ready-to-use function that takes a column index as an input and returns corresponding letters as output, we can still do the task by leveraging other built-in functions ADDRESS , REGEXEXTRACT , INDEX , SPLIT as shown in the post . However, in form of a formula, that solution is not applicable for scripting with Google Apps Script. In this post, we look at how to write a utility function with Google Apps Script that converts column index into corresponding letters. With the solution in the form of a formula , we don't even need to understand how column index and letters map each other. With apps script, we need to understand the mapping to come up with an algorithm. In a spreadsheet, columns are indexed alphabetically, starting from A. Obviously, the first 26 columns correspond to 26 alphabet characters, A to Z. The next 676 columns ( 26*26 ), from 27th to 702nd, are indexed with 2 letters. [AA, AB, ... AY, AZ], [BA, BB, ... BY, BZ],

How to copy data in Google Sheets as HTML table

I often need to extract some sample data in Google Sheets and present it in my blog as an HTML table. However, when copying a selected range in Google Sheets and paste it outside the Google Sheets, I only get plain text. In this post, I explain how to copy data in Google Sheets as an HTML table by writing a small Apps Script program. Concept Implementation Source Code Demo HTML table code HTML table visualization Getting Started Conclusion Concept On a spreadsheet, users select a range that they want to copy as HTML table. With the selected range, users trigger a command Copy AS HTML table . The command can be added to the toolbar, or to the contextual menu, or accessed via a keyboard shortcut. The command is executed to transform the selected range into HTML code for table. The HTML code can be added to the clipboard or can be displayed somewhere so users can copy it manually. The HTML table must consist of all displayed cells of the selected range and the widths

Stock Correlation Analysis With Google Sheets

Correlation is a statistical relationship that measures how related the movement of one variable is compared to another variable. For example, stock prices fluctuate over time and are correlated accordingly or inversely to one another. Understanding stock correlation and being able to perform analysis are very helpful in managing a stock portfolio investment. In this post, we will look at how to perform stock correlation analysis with Google Sheets. Understanding correlation and its applications in stock investing Stock correlation analysis with Google Sheets Getting started User guide Conclusion Understanding correlation and its applications in stock investing The most familiar correlation measure is the Pearson product-moment correlation coefficient . The strength of the relationship between two variables is expressed numerically between -1 and 1. For example: Two stocks are positively correlated when their prices always go up or go down together. Their coefficient

Stock Portfolio Tracker Dashboard With Google Data Studio

In the series of building personal stock portfolio tracker, we have learned how to use Google Sheets to register transactions . We have then used the pivot table and GOOGLEFINANCE function to compute the latest position of the stock portfolio . We have made a step further to use Apps Scripts to compute automatically and daily the stock portfolio's evolution . However, after all, we have several tables of data as the result which do not tell any story yet. We need to present those data in graphs to understand the portfolio's performance and make improvements accordingly. We can effectively plot graphs in different aspects directly in Google Sheets as it provides many charting tools. However, in my experience, having charts and data in the same spreadsheet is not very convenient because charts and tables tend to overlap each other and of lack of interactivity. We should have a dedicated dashboard to have an overview of the stock portfolio and we can do it greatly with Google Data

Monitor stock portfolio with Google Sheets (Pivot table and GOOGLEFINANCE function)

As an investor, it is important to know the latest state of the stock portfolio. We need to know what stocks currently owned in the portfolio, how many shares for each one, how much dividend or gain contributed so far by each stock, etc. As we have registered stock transactions in a spreadsheet with Google Sheets, we can easily have the latest update from the stock portfolio by using pivot tables and GOOGLEFINANCE function. Use a pivot table to group transactions by symbols Configure the pivot table Positions Use GOOGLEFINANCE function to get real-time information for stocks Conditional formatting columns Demo Note References Use a pivot table to group transactions by symbols The pivot table helps to see relationships between data points. To see how each stock contributes to the portfolio, we will create a pivot table that originated from the Transactions sheet. Select the Transactions sheet. Select the 5 columns A:E . In the menu at the top, click Data and then

Manage Stock Transactions With Google Sheets

The first task of building a stock portfolio tracker is to design a solution to register transactions. A transaction is an event when change happens to a stock portfolio, for instance, selling shares of a company, depositing money, or receiving dividends. Transactions are essential inputs to a stock portfolio tracker and it is important to keep track of transactions to make good decisions in investment. In this post, I will explain step by step how to keep track of stock transactions with Google Sheets. Define the structure of transactions Use Google Sheets to register transactions Demo Note References Define the structure of transactions In the example, I assume that a transaction generally has 5 main attributes: Date : It is the moment when a transaction happened. Type : It can be one of the following values: DEPOSIT : When money is added to the portfolio BUY : When money in the portfolio is used to buy shares of a company SELL : When money is added into

Compute cost basis of stocks with FIFO method in Google Sheets

After selling a portion of my holdings in a stock, the cost basis for the remain shares of that stock in my portfolio is not simply the sum of all transactions. When selling, I need to decide which shares I want to sell. One of the most common accounting methods is FIFO (first in, first out), meaning that the shares I bought earliest will be the shares I sell first. As you might already know, I use Google Sheets extensively to manage my stock portfolio investment, but, at the moment of writing this post, I find that Google Sheets does not provide a built-in formula for FIFO. Luckily, with lots of effort, I succeeded in building my own FIFO solution in Google Sheets, and I want to share it on this blog. In this post, I explain how to implement FIFO method in Google Sheets to compute cost basis in stocks investing. FIFO example How to do FIFO in Google Sheets How to use FIFO formula in Google Sheets Simple usage Use FIFO with QUERY formula Demo Conclusion FIFO exampl