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: Click the “New” button and select Google Sheets: Click “Untitled Spreadsheet” and rename it whatever you like, such as “My Dividend Meter”: 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: 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. 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: 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: 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) Column F will be used to import the most recent stock price. Here is the formula: =(GOOGLEFINANCE($C2, “price”)) 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”)) For the “Yield” in column H, simply enter a calculation formula to divide the Dividend in G2 by the Price in F2: =($G2/$F2)
Part 2 Column I, “Annual Income”, is another simple calculation, multiply B2, “Shares”, by G2, “Dividend”: =($G2*$B2) Format Columns E,F,G, and I for “Currency”: Format Column H, “Yield”, for Percent: 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:
Part 3 Enter your share quantities and stock ticker symbols in columns B and C. The other columns should automatically update: In an empty cell, sum the total annual dividend column. This is the figure you’ll use to create the gauge chart (Dividend Meter): Click the cell containing the total sum of dividends, then click Insert and select Chart: In the Chart Editor window, select “Gauge chart” from Other in the Chart Types tab: 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:
Part 4 Insert the gauge chart, resize as necessary, and drag into column A: 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: In the Chart Editor window, select Chart Types, “Bar”, and Insert: 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: From <http://dividendmeter.com/how-to-create-a-dividend-tracker-spreadsheet/>