Create a Dividend Meter Spreadsheet

Discussion in 'Educational videos and material' started by Gray Wolf, Apr 21, 2016.

  1. Gray Wolf

    Gray Wolf Well-Known Member

    Joined:
    Apr 3, 2016
    Messages:
    728
    Likes Received:
    398
    If you have dividend stocks in your portfolio and you might find this helpful. What is also nice about this is that it helps you to learn how you can leverage Google Sheets for other purposes as well. I had to cut this into Multiple posts because of the number of images so Part 2, 3 and 4 will appear as a reply to this to make it easier to find (I hope)

    Part 1

    Creating your own Dividend Meter, a dividend tracking spreadsheet, can be accomplished in about ten minutes. While I still prefer Excel for most of my personal spreadsheet documents, 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. (If you prefer to view a video tutorial, please see my first blog post, “How to Build a Dividend Tracker Spreadsheet – Video Tutorial”.)

    If you would like to download a ready-made Dividend Meter spreadsheet, including a version set up to track the Dividend Aristocrats, visit the Members Club page. Access to the template is only $8.00.

    Assuming you have already created a Google account, go to Google Drive, and sign in with your Gmail address and password:
    upload_2016-4-21_18-32-6.png

    Click the “New” button and select Google Sheets:
    upload_2016-4-21_18-32-6.png

    Click “Untitled Spreadsheet” and rename it whatever you like, such as “My Dividend Meter”:
    upload_2016-4-21_18-32-6.png

    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:
    upload_2016-4-21_18-32-6.png

    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.
    upload_2016-4-21_18-32-6.png

    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:
    upload_2016-4-21_18-32-6.png

    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:
    upload_2016-4-21_18-32-6.png

    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)
    upload_2016-4-21_18-32-6.png

    Column F will be used to import the most recent stock price. Here is the formula:
    =(GOOGLEFINANCE($C2, “price”))
    upload_2016-4-21_18-32-6.png

    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”))
    upload_2016-4-21_18-32-6.png

    For the “Yield” in column H, simply enter a calculation formula to divide the Dividend in G2 by the Price in F2: =($G2/$F2)
    upload_2016-4-21_18-32-6.png
     
    #1 Gray Wolf, Apr 21, 2016
    Last edited: Apr 21, 2016
    T0rm3nted likes this.
  2. Gray Wolf

    Gray Wolf Well-Known Member

    Joined:
    Apr 3, 2016
    Messages:
    728
    Likes Received:
    398
    Part 2

    Column I, “Annual Income”, is another simple calculation, multiply B2, “Shares”, by G2, “Dividend”: =($G2*$B2)
    upload_2016-4-21_18-43-43.png

    Format Columns E,F,G, and I for “Currency”:
    upload_2016-4-21_18-43-43.png

    Format Column H, “Yield”, for Percent:
    upload_2016-4-21_18-43-43.png

    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:
    upload_2016-4-21_18-44-6.png
     
    T0rm3nted likes this.
  3. Gray Wolf

    Gray Wolf Well-Known Member

    Joined:
    Apr 3, 2016
    Messages:
    728
    Likes Received:
    398
    Part 3
    Enter your share quantities and stock ticker symbols in columns B and C. The other columns should automatically update:
    upload_2016-4-21_18-53-8.png

    In an empty cell, sum the total annual dividend column. This is the figure you’ll use to create the gauge chart (Dividend Meter):
    upload_2016-4-21_18-53-8.png



    1. Click the cell containing the total sum of dividends, then click Insert and select Chart:
    upload_2016-4-21_18-51-35.png

    In the Chart Editor window, select “Gauge chart” from Other in the Chart Types tab:
    upload_2016-4-21_18-50-34.png

    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:
    upload_2016-4-21_18-57-56.png
     
    T0rm3nted likes this.
  4. Gray Wolf

    Gray Wolf Well-Known Member

    Joined:
    Apr 3, 2016
    Messages:
    728
    Likes Received:
    398
    Part 4

    1. Insert the gauge chart, resize as necessary, and drag into column A:
    upload_2016-4-21_18-59-55.png

    1. 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:
    upload_2016-4-21_18-59-55.png

    1. In the Chart Editor window, select Chart Types, “Bar”, and Insert:
    upload_2016-4-21_18-59-55.png

    1. 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:
    upload_2016-4-21_19-0-41.png

    From <http://dividendmeter.com/how-to-create-a-dividend-tracker-spreadsheet/>
     
    T0rm3nted likes this.

Share This Page