How to Create a Dividend Tracker Spreadsheet

Creating your own Dividend Meter, a dividend tracking spreadsheet, can be accomplished in just a few minutes.   While Excel is often utilized to track investment portfolios, there are import functions in Google Sheets that are extremely useful for automatically updating stock quotes and dividend figures.   Below are step-by-step instructions for creating your own Dividend Meter in Google Sheets.   (You may also view a video tutorial, please see the post: “How to Build a Dividend Tracker Spreadsheet – Video Tutorial”.)

Please note, the following is a how-to tutorial for your own personal use.  Dividend Meter is unable to provide technical support and is not responsible for data quality and spreadsheet reliability.  If you are having trouble copying and pasting any of the formulas, try manually typing them – once you have the first row working, you can simply pull down the cells to copy formulas to additional rows.

  1. If you have already created a Google account, go to Google Drive, and sign in with your Gmail address and password:    Google_Drive
  2. Click the “New” button and select Google Sheets:  Google_Sheets
  3. Click “Untitled Spreadsheet” and rename it whatever you like, such as “My Dividend Meter”:  Rename_Spreadsheet
  4. Widen Column A to provide room for a gauge chart which will serve as the “Dividend Meter” and a portfolio summary bar chart that can be inserted below the gauge chart: Widen_Spreadsheet
  5. In row one, starting with column B, add column headers for Shares, Symbol, Company Name, Value, Price, Dividend, Yield, Annual Income. Later, you can include additional columns as desired to import additional security data. Here’s a resource to help determine what financial fields are available from Google Finance:  Google Finance Syntax.  Column_Headers
  6. Enter a sample share quantity and ticker symbol in cells B2 and C2 so you can make sure import formulas work successfully as they are entered:  Shares_Ticker
  7. In cell D2, underneath the header, “Company Name”, we’ll enter our first import formula:
    =(GOOGLEFINANCE($C2, “name”))
     – this will look at the ticker symbol in column C and automatically pull the company name from Google Finance:  Import_Company_Name
  8. Column E will calculate the value of the stock position by multiplying the Share quantity in B1 times the Price in F2.   Here is the formula:  =($B2*$F2)  Stock_Value
  9. Column F will be used to import the most recent stock price. Here is the formula:  
    =(GOOGLEFINANCE($C2, “price”))  Stock_Price
  10. In Column G, we’ll pull in the annual stock dividend dollar amount.  I prefer the date from Yahoo Finance for this field.  The following formula will import the annual dividend figure from Yahoo Finance:
    =IMPORTDATA(CONCATENATE(“http://finance.yahoo.com/d/quotes.csv?s=”,$C2,”&f=d”)) Import_Dividend
  11. For the “Yield” in column H, simply enter a calculation formula to divide the Dividend in G2 by the Price in F2:  =($G2/$F2)Calculate_Dividend_Yield
  12. Column I, “Annual Income”, is another simple calculation, multiply B2, “Shares”, by G2, “Dividend”:  =($G2*$B2)  Dividend_Income
  13. Format Columns E,F,G, and I for “Currency”:  Currency_Format
  14. Format Column H, “Yield”, for Percent:  Percent_Format
  15. Drag each data column down to the desired number of rows for columns D through I (Note: columns B and C will be manually entered fields for number of shares and ticker symbols).   For example, if you want to track 20 stocks, drag each field down to row 21 on the spreadsheet.   To drag down, click the cell, and then click and drag the small colored box in the lower right-hand corner of the cell:  Drag_Down2
  16. Enter your share quantities and stock ticker symbols in columns B and C.   The other columns should automatically update: Shares_Tickers
  17. In an empty cell, sum the total annual dividend column.  This is the figure you’ll use to create the gauge chart (Dividend Meter):  Sum_Total_Dividends
  18. Click the cell containing the total sum of dividends, then click Insert and select Chart:  Insert_Dividend_Chart
  19. In the Chart Editor window, select “Gauge chart” from Other in the Chart Types tab:  Gauge_Chart
  20. Click the Customization tab and personalize the ranges.   For my Dividend tracking spreadsheet, I used a negative minimum to represent a debt situation.  The yellow range represents the savings and growth phase, and the green range indicates financial independence: Customize_Dividend_Meter
  21. Insert the gauge chart, resize as necessary, and drag into column A:  Dividend Tracker
  22. To add a portfolio summary bar chart, which is very useful for identifying over-concentrated positions, highlight the stock positions and values in columns D and E, go to the Insert tab, and select Chart:  Position_Bar_Chart
  23. In the Chart Editor window, select Chart Types, “Bar”, and Insert: Bar_Chart
  24. Resize the Bar chart accordingly and move into position under the gauge chart.   Aside from formatting (bolding, centering, column shading, etc.), your Dividend Meter tracking spreadsheet is complete.   You may also want to explore additional Members Only articles to learn how to add a Buy/Sell indicator column and dividend increase alerts:  Dividend_Tracking_Spreadsheet