Using Google Finance for dividend investment

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.

Here are the syntax I used for querying cell items:

To get 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 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.

Written by Tawcan
Hi I’m Bob from Vancouver Canada, I am working toward joyful life and financial independence through frugal living, dividend investing, passive income generation, life balance, and self-improvement. This blog is my way to chronicle my journey and share my stories and thoughts along the way. Stay in touch on Facebook and Twitter. Or sign up via Newsletter