Do firms pay dividends? What are the disadvantages of paying dividends? How do these considerations change if the payout is a share repurchase? C.) If Google were to pay $20 billion as a special dividend, what would be the effect on market value? On the share price? On net income? On earnings per share? What if Google repurchased shares instead?
Update:1 As per requests, I have created a dividend portfolio template available to download. You can use the template for Google Finance Dividend.
Update 2: As of March 2018, Google Finance has changed its formatting. Therefore, the old formula to pull dividend & yield info from Google Finance no longer works. I have updated the formula to pull dividend & yield info from Yahoo Finance instead.
I love using spreadsheets when it comes to tracking various items, so it shouldn’t surprise you that I use a spreadsheet to track our dividend portfolio. Thanks to Google Drive, or more specifically Google Spreadsheet, tracking our dividend portfolio has made life a lot easier. I’m no longer tied to one computer, I can update the spreadsheet anywhere, on a laptop or on my smartphone, as long as there’s internet connection.
Hence I have created a dividend template using Google Spreadsheet.
Google Spreadsheet and Google Finance
Google Spreadsheet has a very useful function called “GOOGLEFINANCE” which allows you to fetch current and historical securities information from Google Finance. The function supports a lot of syntax such as price, volume, PE ratio, and market cap. For more information on all the syntax supported you can take a look here.
My biggest complain with GOOGLEFINANCE function is that it does not have any syntax for querying the most important information when it comes to dividend investing – dividend amount and dividend yield. I have no idea why parameters like Google Finance dividend yield and Google Finance dividend amount aren’t part of the GOOGLEFIANCE function.
For the longest time I was updating the dividend information manually. This was easy to keep track of when we had a small portfolio with only a few handful of stocks. As the portfolio size and number of positions increased, it became more tedious to keep track of all the dividend information.
For me, I like to automate the spreadsheet as much as possible, so I’ve been looking for a way to track dividend information on Google Spreadsheet automatically.
Dividend Yield and Dividend Amount using Google Finance
After a bit of investigation, I found a way to automate the dividend amount and dividend yield on Google Spreadsheet. Unfortunately, it’s not quite the same as a simple parameter as Google Finance Dividend, Google Finance Dividend Yield, or Google Finance Google History. The trick is to use ImportXML function. The function allows me to pull data from any various structured data types including XML, HTML, CSV, TVS, and RSS feeds. By using this function I can pull feeds from Google Finance directly and show dividend amount and dividend yield.
Old Formula:
=ImportXML(concatenate(“http://finance.google.com/finance?q=”,”NAME”), “//td[@data-snapfield=’latest_dividend-dividend_yield’]/following-sibling::*”)
If I want to query dividend info for Apple (AAPL) the formula would look like this:
=ImportXML(concatenate(“http://finance.google.com/finance?q=”,”AAPL”), “//td[@data-snapfield=’latest_dividend-dividend_yield’]/following-sibling::*”)
If I want to query a Canadian stock like Royal Bank I’d simply replace “AAPL” with “RY.TO”. If I want to query a REIT like RioCan, I’d replace “AAPL” with “REI.UN”. For REITs or income trust, there’s no need to populate “.TO” at the end. The formula would look like this:
Dividend Yield and Dividend Amount using Yahoo Finance
But since Google has changed how Google Finance is structured, the old formula (below) no longer works (Boo!). We have to find another way to pull the dividend and yield information elsewhere. I searched the web and found that Yahoo Finance to be a reliable source to pull the dividend and yield data from.
We are still using the ImportXML function. For some reason, I couldn’t insert the XPath directory as part of the formula, so we need to add the Xpath in a separate cell then refer to it in the formula.
New Formula
In a separate cell copy //*[@id=”quote-summary”]/div[2]/table/tbody/tr[6]/td[2]
Then use this
=split(IMPORTXML(concatenate(“https://finance.yahoo.com/quote/”,NAME), ‘XPath cell’),“()”)
Where NAME is the stock symbol and the ‘XPath cell” would be the cell your Xpath is in. For example, if your NAME cell is in B1 and Xpath cell is in C10, then the formula would look like
=split(IMPORTXML(concatenate(“https://finance.yahoo.com/quote/”,B1),$C$10),“()”)
Similar to Google Finance, Yahoo Finance puts Div/Yield info together.
If you query AAPL, the output will be 0.47/(1.96%).
The first number 0.47 corresponds to the dividend amount received each payment period, while the second number 1.96 corresponds to the current dividend yield percentage. Since dividend amount and dividend yield percentage are combined together, I used Split function to further split the ImportXML output.
A quick note is GOOGLEFINANCE and Yahoo Finance use different symbols for stocks with . in their symbols. For example Rogers is RCI.B for Google Finanance but RIC-B.TO for Yahoo Finance. The easiest way is to manually enter the symbol in the ImportXML formula and remove concatenate function. The formula would looks like:
=split(IMPORTXML(“https://finance.yahoo.com/quote/RCI-B.TO”,$C$10),“()”)
Using Google Spreadsheet with dividend info to track our dividend portfolio
How do I use this information to track our dividend portfolio?
I’m glad that you asked! Below is a sample tracker that I put together. I like to keep life easy and only track the important stuff. The dividend portfolio you see on this blog is a simplified version. Our personal spreadsheet is very close to the sample tracker that you see here.
Update: As per requests, I have created a dividend portfolio template available to download.
The column headings are quite self explanatory I hope. ?
Note: I made the numbers in the sample tracker static so the spreadsheet loads faster. For some reason the cells would often stuck in “loading” when shared on the internet.
![Its Its](http://s.marketwatch.com/public/resources/MWimages/MW-BN346_google_MG_20131017144429.jpg)
Here are the syntax I used for querying cell items:
To get market value we use the following syntax
=GoogleFinance(B2,”PRICE”)*C2
=GoogleFinance(B2,”PRICE”)*C2
For Div$ and Yield % columns I’m using the following syntax to split the XML imported data into two fields.
Old Formula
=split(ImportXML(concatenate(“http://finance.google.com/finance?q=”,B2), “//td[@data-snapfield=’latest_dividend-dividend_yield’]/following-sibling::*”),”/”)
New Formula
Xpath: //*[@id=”quote-summary”]/div[2]/table/tbody/tr[6]/td[2]
=split(IMPORTXML(concatenate(“https://finance.yahoo.com/quote/”,B2), ‘XPath cell’),“()”)
![Does Google Pay Dividends Does Google Pay Dividends](/uploads/1/2/4/8/124864981/787182359.jpg)
With this sample tracker, I’m able to track our dividend portfolio very easily and able to get a quick summary of what’s going on.
Note: If you are trying to track ETFs, you need to use a different Xpath.
For ETFs, use Xpath: //*[@id=”quote-summary”]/div[2]/table/tbody/tr[4]/td[2]
Tracking monthly dividend income
For tracking monthly dividend I use a very simple spreadsheet like the one below. Usually, the dividend tracker is a separate sheet within the same spreadsheet document. By having everything in the same document, I can add another column called Div Rx in the portfolio tracker and link the dividend total for each position.
There are many ways to track a dividend portfolio. I like to keep it simple and the above methods work for us. Automation makes tracking dividend portfolio a breeze.
How do you track your portfolio?
Update: As per requests, I have created a dividend portfolio template available to download.
Stepping into complete darkness
February 24, 2015Canadian dividend investing tips
April 26, 2015As a hallmark of American finance, Berkshire Hathaway boasts one of the best investing track records in history. Unfortunately, Warren Buffet has personally emphasized that Berkshire will never pay a dividend. Should the Oracle of Omaha finally change his mind this year, it could mean additional billions to distribute to its investors. With a stock price of nearly $160 per share and a market cap over $390 billion, Berkshire Hathaway would only need to declare a dividend of $0.75 per share to match the average dividend yield of the Financials sector, according to Reality Shares research. Similar to Alphabet ($GOOGL), Berkshire Hathaway’s dividend initiation at that rate would increase the aggregate dividend of the S&P 500 by $7.4 billion (a 1.8% increase).
- Amazon, Inc. – $AMZN – No Current Regular Dividend
The Seattle-based multinational retailer takes the third spot on the wishlist. Offering nearly every retail product under the sun, Amazon has become one of the nation’s highest-performing Consumer Discretionary stocks. The firm has shown a commitment to innovation, working on patent-winning flying warehouses and exploring same-hour delivery by drones, but no dividend has ever been paid to shareholders. Amazon boasts 5-year annualized cash flow growth of 30% currently, and a stock price surpassing $800 after three stock splits during its 23-year history. The average dividend yield of the Consumer Discretionary sector is 1.74%. A matching yield by Amazon would increase the S&P 500’s total payout by $6.2 billion, representing a 1.5% increase.
- Facebook, Inc. - $FB – No Current Regular Dividend, 1 Special Dividend Paid
Last, but not least, is Facebook. In 2016, Facebook paid a special dividend to class C shareholders – its only dividend payout. Like many of its Information Technology sector counterparts, the social networking company does not pay a regular dividend. Since its IPO, Facebook has performed strongly, growing over 200%, and amassing a market cap of over $330 billion. The firm also shows dividend payout capability with positive earnings-per-share and cash flow growth in recent years, both showing annualized 5-year growth rates of 49% according to Charles Schwab research. At nearly 2 billion users according to Venturebeat, Facebook can make even more friends by paying a dividend and cementing investor confidence. By matching the average dividend yield of the Information Technology sector at 1.43%, Facebook would add $4 billion to the S&P 500’s total dividend, an increase of 1.1%.
Dividends: A True Indicator of Fundamental Health
Though the companies on our list all show strong investment track records despite not paying a dividend, some long-term market participants only invest confidently knowing that a company pays and grows dividends consistently. According to value investing guru Benjamin Graham, investing is in the short run a voting machine. In the long run however, it is a weighing machine. The companies listed above may be able to catch the eye of more longer-term investors by displaying consistent dividend growth paid from strong cash flows and earnings.
How Investors Can Capitalize on Dividends
Today, it’s not uncommon for investors to make investment decisions relying on historical dividend payout patterns that are based on past financial results. To better serve investors, Reality Shares created DIVCON® to analyze a stock’s future dividend health. DIVCON uses a forward-looking factor-based analysis to predict future dividend growth potential. By evaluating each firm based on seven quantitative factors, Reality Shares gives each company a DIVCON Rating of 1 to 5, with 5 having the highest probability of an increase in a company’s dividend within the next 12 months. DIVCON analysis also seeks to avoid companies and sectors of potentially low fundamental quality despite their higher dividend yields. In order to get a DIVCON rating, the stock must pay a regular dividend. Because of this and their overall potential impact on the dividend growth rate, our 2017 dividend wishlist includes Alphabet, Berkshire Hathaway, Amazon and Facebook.
Learn more about DIVCON and how we harness the power of dividends at www.realitysharesadvisors.com.
This article is commentary by an independent contributor. This material provides the observations and views of the person(s) who prepared the material. These observations and views may be different from, or inconsistent with, the views of Reality Shares or other persons within Reality Shares. The observations and views expressed in this material may change at any time without notice. This material is not intended to constitute an offer, or solicitation of an offer, for any investment product, or to purchase or sell any security or financial instruments, or to participate in any investment strategy. The securities and strategies referenced in this material are not intended as recommendations and may not be suitable for you.
Eric Ervin is President and CEO of Reality Shares, Inc. This article expresses his opinions and may not necessarily represent the opinions of Reality Shares.