DivGro is now DivGro 2.0!

DivGro moved to another platform and is now DivGro 2.0!

Please enjoy complimentary access to all the content on DivGro 2.0 until I formally launch it! You can sign up for free and join more than 1,090 existing members!

All content will be complimentary until the formal launch of DivGro 2.0. This includes my monthly newsletter and articles like
 How to Assess Dividend Quality and The Chowder Ruleand a live spreadsheet of my DivGro Portfolio.

Read more About DivGro 2.0 ...

Friday, June 20, 2014

Tracking your DGI Portfolio

Last month, All About Interest posted a nice article about using Google Docs for your portfolio. In his article he describes several ways to import data into a Google Docs spreadsheet. The easiest and most convenient way is to use the GoogleFinance function, which fetches current and historical stock information from Google Finance.

The purpose of this post is to explain the content of the above-mentioned article for readers that are not so technically inclined. I'm not presenting anything new here, except for a sample spreadsheet containing the necessary formulas.

I've been using the GoogleFinance function to automatically update the price of stocks I own in DivGro. However, there are many other bits of information you can import with GoogleFinance, including historical data.

Look at my DivGro-Portfolio spreadsheet on my Portfolio Page. At the bottom, the spreadsheet tells the viewer when the current price was last updated. Quotes may be delayed up to 20 minutes. The cool thing about using GoogleFinance is that your portfolio updates automatically, including other formulas that depend on data that GoogleFinance provides (such as the TOTAL portfolio value seen here).

Syntax


GoogleFinance(ticker, [attribute], [start_date], [num_days | end_date], [interval])

Only ticker is required. All other arguments are optional, including attribute, which defaults to "price".  Other interesting data that can be obtained this way include:
All arguments must be enclosed in quotation marks or be references to cells. 

Example


I created an example Google Docs spreadsheet in a public folder to illustrate the use of the GoogleFinance function. The spreadsheet comes pre-filled with four of the most popular dividend growth stocks:


Except for the Ticker column and the last two columns, every cell contains an instance of the GoogleFinance function. Below are three examples to illustrate, assuming the cell containing CVX is B5:
Market Price formula:
 = GoogleFinance(B5
P/E formula:
 = GoogleFinance(B5, "pe") 
Volume Avg (M) formula:
 = GoogleFinance(B5, "volumeavg")/1000000 

Annual Dividend?


For dividend growth investors, dividend data are very important. Unfortunately, GoogleFinance does not provide data about a stock's annual dividend or the compound annual growth rate (CAGR). 

Thanks to All About Interest, I've learned how to pull data from financial sites like Yahoo and from on-line spreadsheets like Dave Fish's CCC spreadsheet

An example of the first method appears in the second-to-last column in my spreadsheet. We're using the function ImportHtml to import data from a table or a list within an HTML page, in this case the annual dividend. Here's the essential formula, where, again, we're assuming the cell containing the ticker symbol CVX is B5:

Syntax:
 ImportHtml(url, query, index)
Cell formula:
 = ImportHtml("http://finance.yahoo.com/q?s="&B5&"&ql=1", "table", 3 

The first argument is the URL of the page to import data from – simply visit the page and copy the relevant text, then paste it as the first argument to ImportHtml:

You'll need to surround the URL with double quotes. To generalize the formula, replace the ticker symbol with the cell address containing the ticker symbol, using the string concatenation operator (&).

The second and third arguments refer to the type of query, either "table" or "list", and an index, starting at 1, to indicate which table or list in the HTML source import. In this case, we want the third table:


The function returns the entire table, so you'll need to leave enough space in your spreadsheet for the results. Alternatively, you can use the Index function to pick out the value you want:

Syntax:
 Index(reference, [row], [column])

In this case, we want the value in row 8, column 2 of Table 3: 4.28 (3.40%)

Of course, what you really want is the annual dividend part of the value and not the dividend yield part in parentheses. Here, we'll use the function REGEXextract:

Syntax:
 REGEXextract(text, regular_expression)

REGEXextract extracts matching substrings based on a special search string called a regular expression. It is well beyond the scope of this post to explain the intricacies of regular expressions, but, in essence, we want to find the first occurrence of a string containing any digits and a period up to but excluding a space:

Regular Expression:
 "([0-9.]*) "  

Putting all this together, the entire formula looks like this:

      = REGEXextract(
      Index(ImportHtml("http://finance.yahoo.com/q?s="&B5&"&ql=1","table", 3),
      8, 2),
      "([0-9.]*) ")  

Finally, the dividend yield in the last column is simply calculated using the price and annual dividend.

In a future post, I'll discuss how to import other interesting data into a Google Docs spreadsheet, including from Dave Fish's CCC spreadsheet. Again, most of this information have already been presented by All About Interest in his excellent and informative post. My objective here is to explain things a little more and to provide example spreadsheets you could download and edit, or start using for tracking your own dividend growth investment portfolio.

Thanks for reading (if you got this far!). Did you find this post useful? Also, let me know if anything is unclear and I'll try to fix it...

49 comments :

  1. This is a gresat concept. Based on the original article, I set up both a portfolio and a watch list using a combination of google finance and regular, plodding data entry. Amazingly, it seemed to work, with one nagging flaw. Each time I opened the spreadsheet, most of the values calculated properly. However, every time some values did not calc and I was left with #ref or #val or some other cryptic note.
    This is particularly frustrating when the values are ranked, totaled and averaged. Obviously, even one missing value screws things up.
    Interestingly, the next opening of the sheet resulted in different values missing. All in all, not a productive experience.
    Any thopughts/suggestions would be appreciated.

    PS - I am NOT a spreadsheet newbie. I have been using Excel since about 1984 and was an original beta tester.

    ReplyDelete
    Replies
    1. If you've been using Excel since 1984, you can teach me something!

      So far, I have not experienced what you're talking about, though I've only used it for my portfolio of 31 stocks. It would be interesting to see if I use the approach for my watch list of 215 stocks, if that would cause similar problems. I know there is a limit on the number of times you can use GoogleFinance in the same spreadsheet -- 1000, I believe. That you're getting missing values in different cells on reload makes me think its not the hard limit issue.

      Sorry, no other ideas right now. Maybe some other readers can help.

      Cheers!

      Delete
    2. Hi - Thanks for your suggestions. I was not aware of the total hard limit on Google Finance calls or I had forgotten it. I've just tried reducing my watch list from 50 stocks to 30. The worksheet has about 20 columns. It seems to work. I believe there is also a hard limit on the number of formulas that are used for yield calcs (e.g. (value(REGEXreplace(REGEXextract(REGEXreplace(index (importhtml("http://finance.yahoo.com/q?s="&A5&"&ql=1", "table", 3), 8, 2); "[()]"; "") ; "..[^a-zA-Z][.0-9].%$"); "[%]"; "")))/100).

      Thanks again

      Al

      Delete
    3. I'm glad you got that sorted out! Yes, there is a similar hard limit on ImportHtml, but it is being removed in the new version of Google Sheets. See https://support.google.com/docs/answer/3093339

      Take care!

      Delete
  2. Thanks for sharing this! I'm new to DGI and am setting up a blog right now.
    These GoogleFinance formulae work fine when I'm in editing mode, but my portfolio at my blog keeps saying it is 'Loading...'
    Should I change something in the iframe-area of the blog?

    Please keep up the good work!

    ReplyDelete
    Replies
    1. You're welcome, and welcome to the world of DGI blogging!

      I'm not doing anything like that, at least not that I'm aware of. What I do is select File > Publish to the web... in my Google Docs spreadsheet, get a link to the data I want to publish (choose web page and current sheet or all sheets), then cut and paste it as the argument to src in an iframe:

      src="https://docs.google.com/spreadsheet/pub?key=0AopfKA_9Y4dRdEtuVF9QckZvS2M5RkxJcHpPYkM0c2c&output=html&widget=true"

      I also select the option to "Automatically republish when changes are made".

      Hope you get this working quickly!

      Delete
  3. Hi!

    Here is a simpler and cleaner way to import dividend amount. Put this line to a cell:

    =IMPORTDATA(CONCATENATE("http://finance.yahoo.com/d/quotes.csv?s=",A1,"&f=d"))

    This will give you the dividend of a ticker in A1.

    ReplyDelete
    Replies
    1. This is intriguing, but it doesn't quite work. This reports CVX's dividend as 4.07, whereas the actual value (as viewed on finance.yahoo.com) is 4.28. Also, checking price (I guessed price is 'p'), the price is similarly mismatched: $131.99 vs $132.34.

      Could you clarify? I'll research the ImportData function as I'm not familiar with it...

      Delete
  4. It does work - it just shows a bit different data. The difference is that csv version brings ttm dividend info (past 4 quarters). I think the other version simply quadruples the laters dividend. Both are qually valid and "actual" values and give you good enough estimate of the dividend.

    Don't know about the price difference. Most likely just some delay issue. I use googlefinance() everywhere where it can be used. It gives price info too. For some odd reason googlefinance doesn't give dividend info.

    ReplyDelete
    Replies
    1. OK, that makes sense. If it is TTM data, I can understand the difference for dividends. I'm not so sure that the price difference is a delay issue. I'll check it periodically to see if I can figure out what's up. Anyway, thanks for your post -- its by sharing that we all learn.

      Cheers!

      Delete
  5. DivGro - I discovered Google Docs a few months back and continue to learn more and more each time I use it. It is from post like these that I have slowly gain confidence in building formulas and spreadsheets. Thanks for sharing. Best Wishes! AFFJ

    ReplyDelete
    Replies
    1. Isn't Google Docs great? I'm very happy using it for tracking DivGro and I'm leaning more about some sophisticated things you can do. I'll continue to share and I hope others would do so too, 'cause thats how we all get to learn faster.

      Take care!

      Delete
  6. This is great stuff, DivGro. When I inspect the Yahoo page, it says that the dividend is in table 2, but you have it as table 3. Obviously, I am missing something. I would like to add the PEG ratio to the table since I think it is a key element to analyzing stocks, and Yahoo has it in the Key Statistics, but I can't figure out how to set-up the function in Google. Any help you can provide would be much appreciated.

    ReplyDelete
    Replies
    1. KeithX, look at the source of the yahoo page and search for "table". The occurrences of table with a leading "<" are relevant. I use Chrome, so to view the source of the page, I select View > Developer > View Source. The first occurrence is not a data table, but something with a tag "role = "presentation"". So that's table 1. That's why the table I want is table 3.

      As far as pulling the PEG ratio, use the 9th table on that page. Here's the formula, assuming cell A1 contains the ticker symbol:

      =index(importhtml("http://finance.yahoo.com/q/ks?s="&A1&"+Key+Statistics", "table", 9),5,2)

      Hope that helps!

      Delete
    2. Thank you, FerdiS! Works perfectly.

      Delete
    3. Great! Glad that works for you -- I'll soon post another article on other ways of pulling data automatically...

      Take care!

      Delete
    4. What you're saying here totally makes sense FerdiS but I can't get it to pull in data from lower tables. Specifically I'm looking at debt-equity and current ratio. Any idea what I'm doing wrong? Specifically, it's telling me that the index function value has to be between 0 and 1. That doesn't make sense if I'm trying to pull out the 5th line. Thoughts?

      Delete
    5. Seems like the table numbers of these pages change sometimes.

      Here's what I suggest you do. In an empty Google sheet, copy the following formula into B1, enter the ticker (say AAPL) in A1, and enter the number 1 in A2. Then step through the tables by incrementing the number in A2 until you find the relevant tables:

      =importhtml("http://finance.yahoo.com/q/ks?s="&A1&"+Key+Statistics", "table", A2)

      Be patient. (The table you're looking for is 22). Then use the index to pull row 5 column 2 for debt-equity and row 6 column 2 for current ratio.

      Hope this helps!

      Delete
  7. Hi,

    I have a quick question on how you made your pie chart showing sector diversification in Google Spreadsheets. I'm able to automatically grab the data from Yahoo using this formula:

    =Index(ImportHTML("http://finance.yahoo.com/q/pr?s=WFC+Profile", "table", 9), 2 ,2)

    However, when I make the pie chart, each stock that has a financial sector designation, as in the example above, gets its own slice on the chart. What I would like to do is have it automatically tabulate the percentage of the portfolio taken up by all stocks with the same sector designation so that it is shown on a single slice. I could do that manually on a hidden sheet but am wondering if you know any way of making the process more automated. Thanks a lot!

    ReplyDelete
    Replies
    1. You would need at least one extra column of data, possibly more. You could automate the collection of percentages using COUNTIF, of course. I use sorted counts AND special labels with counts in parentheses, so I need more columns and, therefore, I use a hidden sheet.

      BTW, I use GICS sector names and have yet to find an automated way of pulling that from the internet. I can download the CCC list and get it from there, though. Someday, I'll set that up. For now, I'm just adding the GICS sector names manually. See:

      http://divgro.blogspot.com/2013/09/fruits-of-my-labor-day-labor.html

      Let me know if you want more details.

      Delete
  8. I read a little about importXML and made this, which imports the GICS sector names from a Fidelity research site:

    =index(importxml("https://eresearch.fidelity.com/eresearch/goto/evaluate/snapshot.jhtml?symbols="&A2&"","//div[@class='sub-heading']//span[@class='right']"),1)

    A2 in this example is the cell with the ticker symbol

    Now I just need to work on that pie chart

    ReplyDelete
    Replies
    1. Thanks, so much, Scott -- I'll give that a try!

      Let me know if you have any other questions about the pie chart part...

      Delete
  9. Thanks so much, Scott, for sharing that fix. I'm sure others have run into this issue and would welcome your note. This post is still generating some traffic, and I think its probably time for a follow-up!

    Cheers
    FerdiS

    ReplyDelete
  10. Hi. I was updating my spreadsheet this am and saw a message saying I was about to reach the limit of functions, I solved it for now in implementing some of the tab sheets (I use different tabs per years) on their own files. But I wanted to ask if you ever see issues when the retrieved annual dividend from yahoo shows as N/A?
    Happens a lot for me.

    I use this formula =Left(ImportXML(Concatenate("http://finance.yahoo.com/q?s=",A24,"&ql=1"),"//table[2]/tr[8]/td[1]"),4)

    Thanks.

    ReplyDelete
    Replies
    1. Hi Fab -- I don't see N/A that often. If you visit finance.yahoo.com, do you see the annual dividend for the ticker you're interested in? If so, I can only suggest that you break down the formula step-by-step to see where the N/A comes from. Also, check out some of the formulas other commenters have posted, including Scott's of August 31, which provides an alternative way to access the annual dividend.

      Delete
    2. Recently as Google is updating old spreadsheets I see loading, error and N?A errors more often too. It is quite frustrating.

      Delete
    3. I do see that happen, but waiting and reloading a few minutes later seems to fix the problem.

      Delete
  11. DivGro, great explanation on using Regex. I also use a shorter version as FAB uses above:

    =IMPORTDATA(concatenate("http://finance.yahoo.com/d/quotes.csv?s=",A7,"&f=y"))

    A7 contains the symbol and the very last letter "y" stands for yield. If you want a dividend rate, change it to "d", last price - change it to "l1" etc.

    Currently I am looking on how to grab sector for a stock and couldn't find anything, so it looks like I will have to use your Regex function :)

    ReplyDelete
    Replies
    1. Thanks for sharing, Martin! You may want to look at finviz as a source, too. Take care!

      Delete
    2. Any thoughts on why some of these don't work in the formula?

      Nestle (NSRGY)...Yahoo doesn't show yield or div for some reason

      Royal Dutch Shell..tried RDSB, RDS.B, and RDS-B without luck

      TDIV a tech ETF will not return yield even though other ETFs do. Yahoo even has yield listed. Some of our ETFs load fine, others are like TDIV

      Really don't understand why Google, with all of their horsepower, can't add yield and dividend to Sheets.

      Delete
    3. Hi Rob -- thanks for commenting. I second your call for Google to provide yield and dividend functions!

      As far as the rest of your question goes, I recommend experimenting with other financial sites, such as finviz and zacks. I know that's not ideal, because one would like to "just have" a single formula that works!

      Best of luck!

      Delete
  12. I'm looking to build a Graham / z-Altman score sheet - but I CANNOT seem to get ANY data from the Key+Statistics page in Yahoo FInance - does anyone know where I could see a map of the tables and columns, etc for this? As an example - I would want the PEG and Book Value Per Share from Apple here - http://finance.yahoo.com/q/ks?s=aapl+Key+Statistics

    I'd like to get them both into Google Sheets - Any Ideas??

    ReplyDelete
    Replies
    1. In an empty Google sheet, copy the following formula into B1, enter the ticker (AAPL) in A1, and enter the number 1 in A2. Then step through the tables by incrementing the number in A2 until you find the relevant tables:

      =importhtml("http://finance.yahoo.com/q/ks?s="&A1&"+Key+Statistics", "table", A2)

      For example, the one containing PEG Ratio is table 11.

      Hope this helps!

      Delete
  13. I would like to be able to import into google spreadsheets the 1-year and 5-year dividend growth for a stock from Fidelity's website. It's listed under the "Dividend Analytics" section on the dividends page, for example, here: https://eresearch.fidelity.com/eresearch/evaluate/fundamentals/dividends.jhtml?symbols=xom.

    I tried ImportXML and I found the XPath using a "Find XPath" Firefox plugin, but the result is "#N/A: Imported content is empty".

    Does anyone know what formula to use?

    Thanks.

    ReplyDelete
    Replies
    1. Hi Adam -- I'm a rookie as far as ImportXML is concerned. Looking at the source of that page, though, it looks like all the data in the Dividend Analytics section are generated through a function call. Hopefully, someone else can suggest and alternative.

      Delete
    2. FerdiS,

      I don't know what that means, but hopefully someone else can use that piece of information to come up with a solution. Right now I have to import my data from other sources.

      Delete
    3. All I meant to say is that the source of the page (most browsers allow you to look at the source code of a web page) doesn't provide any clues...

      Delete
  14. Trying to get the dividend yield from that preferred share. After many tries, I cannot get the reference. Could you help?

    https://www.google.ca/finance?q=TSE%3ABMO-K&ei=-wDzVuGvOcGaea2epEg

    Dividend yield is at the top right corner of 3rd column.

    ReplyDelete
    Replies
    1. Ah! Got it.

      Using Scott's formula, and replacing the very last number '1' by the number '2' did the trick. Thanks to both of you for that spreadsheet, which is lightning fast compared to others I have tried.

      =index((split(ImportXML("http://finance.google.com/finance?q=" & A2, "//td[@data-snapfield='latest_dividend-dividend_yield']/following-sibling::*"),"/")),1,2)

      Delete
    2. Thanks for sharing the fix! I'm fascinated by the fact that this post is still generating lots of reads. Perhaps I should revisit it!

      Delete
  15. I've noticed that around 80% of the cells in my spreadsheet (those having to do with dividend rates that I pull from websites besides Google with a REGEXTRACT function) read #REF!, which I think means Google sheets thinks there's some error with the formula or it's unable to run the command for some reason. But the strange thing is the formula works for some stock symbols, and it's the same formula for all the symbols. So that tells me there's nothing wrong with the formula itself.

    Does anyone know why this is occurring?

    ReplyDelete
    Replies
    1. Hi Adam -- I've experienced that as well, but I don't know the reason. It is intermitted, though, so sometimes it happens and sometimes not. I'm wondering if it is a server delay, or something. It is quite annoying. I know there is a limitation on the number of "requests" you can make, but for a portfolio of less than 100 stocks, I don't think it should be a problem.

      Delete
    2. I own well under 100 stocks, but I have 141 stocks in my watchlist. So maybe I'm making too many requests. I would say maybe 30 stocks show the data while all the others show #REF!. I am using the following formula to get quarterly dividend/share and annual yield: =REGEXextract(index(importhtml("https://www.google.com/finance?q="&A8, "table", 3), 1, 2),"/(.*$)")+0

      I have no issues getting dividend growth data using formulas starting with (INDEX(importhtml("URL"

      Delete
    3. I am having the exact same issue and I have less than 20 stocks in my spreadsheet. It is definitely frustrating!

      Delete
    4. Its frustrating indeed. Anyone reading this with a solution, please share it!

      Delete
    5. Any idea on #REF?

      Delete
    6. No ideas, Anonymous, except what I've speculated in other comments. It happens intermittently. Sometimes it seems to be worse than other times.

      Delete
  16. Hi, I have been downloading equity data for years from Yahoo using MS Excel, but it seems recently, Yahoo is no longer publishing dividend data and return for ETF's and some other mutual funds. After looking for days, I am almost stumped (I have several things I'm working on, but no great solution yet).

    Does anyone have a method for downloading dividend OR yield data for stocks, etf's and mutual funds? Such as Morningstar, MSN, Google Finance, etc.? I am working on importHTML using Google Sheets, but it still does not seem to pull all of the investment's data...

    Any help would really be appreciated!

    Thanks,
    Mark

    ReplyDelete
    Replies
    1. Try finviz:

      =regexextract(index(ImportHtml("http://finviz.com/quote.ashx?t="&A1, "table", 11),7,2),"/*[0-9.]+/*")

      Delete

Please don't include links in comments. I will mark such comments as spam and the comment won't be published. To make me aware of your blog or website, comment on my Blogrole page instead.

Subscribe to Portfolio Insight and Save!

Use my affiliate link to sign up for a free 14-day, no-obligation trial of Portfolio Insight. No credit card required. If you decide to subscribe during the trial period, you'll receive a 20% discount on the first year's annual subscription price of $330. Please note the 20% affiliate discount does not apply to the monthly rate.