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

24 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
  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. Hey, I recently had a guy comment on my site that the Yahoo Finance method which you and I use for grabbing the annual dividends was not working. What was happening was that Google and Yahoo use slightly different ticker symbols for Canadian stocks. The ticker symbol that was working for the GoogleFinance function did not work for Yahoo.

    I've since updated my template to grab the annual dividend data from Google as well. This fixes that problem. Thought I'd mention it here as well in case you had any readers run into the same problem.

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

    ReplyDelete
    Replies
    1. 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

      Delete
  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