Update:1 As per requests, I have created a Google Finance 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.
Update 3: While ImportXML still works. It seems to get errors from time to time due to how the webpages are set up.
I have updated the Google Finance dividend portfolio template with a slightly revised function that’d to the exact same thing. This new function should be able to pull dividend info reliably. This should be very useful for any dividend investor.
Update 4: The ImportFromWeb add-on has a monthly pull limit so it’s not very reliable. I have decided to utilize ImportHTML function instead.
Getting started on dividend investing is easy. Once you have a few dividend paying stocks in your dividend portfolio it can get tricky to keep track of your cost basis and various information. This is where using spreadsheets come in very handy.
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 and dividend income using a dividend spreadsheet 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 an internet connection.
Hence, I have created a Google Finance dividend spreadsheet template.
Google Spreadsheet and Google Finance for tracking dividend portfolio & dividend income
Google Spreadsheet has a very useful function called “GOOGLEFINANCE” which allows you to fetch current and historical security information from Google Finance. The function supports a lot of syntaxes 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 complaint 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 stocks. As the dividend 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 dividend spreadsheet as much as possible, so I’ve been looking for a way to track the dividend portfolio 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
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 the 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 the concatenate function. The formula would look like:
=split(IMPORTXML(“https://finance.yahoo.com/quote/RCI-B.TO”,$C$10),”()”)
Even Newer Formula
Since ImportXML does not pull the data consistently, I decided to utilize ImportHTML instead. So the “new” formula would look like the following:
=SPLIT(INDEX(IMPORTHTML(concatenate(“https://finance.yahoo.com/quote/RCI-B.TO”),”table”,2),6,2),” “)
Using Google Spreadsheet with dividend info to track our dividend portfolio
How do I use this information to track our dividend portfolio and dividend income?
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 Google spreadsheet dividend portfolio template available to download. Best of all, it’s free!
Here is the syntax I used for querying cell items:
To get the market value we use the following syntax
=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’),“()”)
With this dividend portfolio tracker, I’m able to track our dividend portfolio and dividend income very easily and able to get a quick summary of what’s going on. This is the beauty of Google spreadsheet!
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]
Even Newer Formula
Rather than relying on Xpath and using ImportXML, I decided to use ImportHTML function for an easier data pulling. So the new formula is
=SPLIT(INDEX(IMPORTHTML(concatenate(“https://finance.yahoo.com/quote/”,B2),”table”,2),6,2),” “)
As you can see, rather than calling /div[2]/table/tbody/tr[6]/td[2], ImportHTML calls table 2, 6, 2 which is more or less the same as the Xpath just in a slightly different formate.
Similar, for ETF, you’d use the following formula and pointing to a different section of the table:
=SPLIT(INDEX(IMPORTHTML(concatenate(“https://finance.yahoo.com/quote/”,B2),”table”,2),4,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 the dividend portfolio a breeze.
How do you track your portfolio? In case you want to download the template for free, please head over here.
Learning about dividend investing
In case you want to learn more about dividend investing, here are some articles you might find useful.
The first step when it comes to dividend growth investing and building your dividend portfolio is to determine how to start investing in dividend-paying stocks.
Eliminating stocks by looking at the different financial numbers is important but it is just as important to learn how to read the annual and quarterly reports. For Canadian dividend investors, you must understand how to build your portfolio so it can be as tax efficient as possible.
For further reading, please take a look at the following two FAQ’s I have put together:
- Dividend Investing FAQ (Mostly for Canadians but works for other countries too).
- Dividend & Index Investing FAQ.
Also check out the best Canadian dividend stocks where I hand picked some of the top Canadian dividend stocks that I think every Canadian dividend investor should hold in their portfolio.
