Sunday, February 24, 2013

How to Automatically Include Stock Dividend Information in Google Docs

Microsoft, please pay attention to this article.  Excel 2013 is no better than any previous versions of Excel at automatically importing stock information which is why Google Docs has become my spreadsheet of choice.

Google provides a great stock market oriented function called GoogleFinance that is very easy to use.  For example, if an investor wants to retrieve a near real-time stock price into a Google Docs spreadsheet all they need to is include the function--- =GoogleFinance("GOOG", "price") or =GoogleFinance(B2, "price"), where cell B2 contains the ticker symbol.

The following types of real-time market data are currently available:
  • price: market price of the stock - delayed by up to 20 minutes.
  • priceopen: the opening price of the stock for the current day.
  • high: the highest price the stock traded for the current day.
  • low: the lowest price the stock traded for the current day.
  • volume: number of shares traded of this stock for the current day.
  • marketcap: the market cap of the stock.
  • tradetime: the last time the stock traded.
  • datadelay: the delay in the data presented for this stock using the googleFinance() function.
  • volumeavg: the average volume for this stock.
  • pe: the Price-to-Earnings ratio for this stock.
  • eps: the earnings-per-share for this stock.
  • high52: the 52-week high for this stock.
  • low52: the 52-week low for this stock.
  • change: the change in the price of this stock since yesterday's market close.
  • beta: the beta value of this stock.
  • changepct: the percentage change in the price of this stock since yesterday's close.
  • closeyest: yesterday's closing price of this stock.
  • shares: the number of shares outstanding of this stock.
  • currency: the currency in which this stock is traded.
Notice that information on dividends is lacking from the list of options.  This is not a good thing if you are a dividend oriented investor.  Luckily, there is a relatively easy way to correct his oversight using either the google or yahoo finance web sites.

Current Dividend Yield

To obtain the current dividend yield of a stock, simply copy one of the two formulas into the cell in google docs where you want dividend yield to appear.

=REGEXreplace(REGEXextract(REGEXreplace(index (importhtml("http://finance.yahoo.com/q?s=NLY&ql=1", "table", 3), 8, 2); "[()]"; "") ; "..[^a-zA-Z][.0-9].%$"); "[%]"; "") -- obtains dividend yield for NLY from yahoo finance

=REGEXextract(REGEXreplace(index (importhtml("https://www.google.com/finance?q=NYSE%3ABIP", "table", 2), 1, 2); "[()]"; "") ; "/([^/]*)") -- obtains dividend yield for BIP from google finance

Current Dividend Amount

To obtain the current dividend amount of a stock,  copy one of the two formulas into the cell in google docs where you want dividend amount to appear.  These sites are inconsistent in how they display dividends-- sometimes they show the quarterly dividend amount and other times the yearly amount.  So you might have to make the appropriate adjustments.

=REGEXextract(REGEXreplace(index (importhtml("http://finance.yahoo.com/q?s=NLY&ql=1", "table", 3), 8, 2); "[()]"; "") ; "([^/]*) ") -- obtains dividend amount for NLY from yahoo finance

=REGEXextract(REGEXreplace(index (importhtml("https://www.google.com/finance?q=NYSE%3ABIP", "table", 2), 1, 2); "[()]"; "") ; "([^/]*)/") -- obtains dividend amount for BIP from google finance

A Word of Caution

If either site changes their layout, these formulas will have to be adjusted to compensate.




48 comments:

  1. Thank a lot! Mind providing advice on how to replace the symbol reference with a cell, such that his formula could be applied automatically across a range of equities?

    e.g. instead of MMM, call cell B2?

    ReplyDelete
  2. Perfect! Was looking for this. Thanks so much!

    ReplyDelete
  3. How do you call a cell. Just as Geoff Dudgeon said. thanks for the post. very helpful

    ReplyDelete
  4. I was able to reference a cell rather than the ticker directly. In the NLY example above, go into the formula and replace the letters NLY with the following notation, referencing the cell of the ticker you are trying to use(in my example, cell C12): "&C12&"

    ReplyDelete
    Replies
    1. Anyway you can post the entire formula you used. I am having trouble referencing the cell. Thank you!

      Delete
    2. =REGEXreplace(REGEXextract(REGEXreplace(index (importhtml(CONCATENATE("http://finance.yahoo.com/q?s=", A2, "&ql=1"), "table", 3), 8, 2), "[()]", "") , "..[^a-zA-Z][.0-9].%$"), "[%]", "")

      Delete
  5. A nobel and informational website about financial market. I like your website surely. Thank's for published this website. If you want more informastion about stock quotes to visit stock quotes After hitting the Go button, core data for the stock quote you are searching for will appear in a few seconds. The first line of the stock quote will show the name of the stock or security. It is then followed by the ticker symbol, which you will find is placed in parentheses. The second line of the retrieved stock quote will display four important pieces of information.

    ReplyDelete
  6. For those looking to pull yield on mutual funds from Yahoo!, I thought I'd add this:
    =index (importhtml("http://finance.yahoo.com/q?s=DODFX&ql=1", "table", 3), 2, 2)

    or pull the ticker from a a cell (in this example B53):
    =index (importhtml("http://finance.yahoo.com/q?s="&$B$53&"&ql=1", "table", 3), 2, 2)

    ReplyDelete
    Replies
    1. Why not just use the built in GOOGLEFINANCE() function?

      Yield: =GOOGLEFINANCE("DODFX","yieldpct")
      Dividend: =GOOGLEFINANCE("DODFX","incomedividend")

      Delete
    2. Why the heck doesn't google have the equivalent function for common stocks? Makes no sense.

      Delete
  7. Definitely, It is an elegant and fundamental website about financial markets. It is very useful for us. So, I loved it. Many, many thanks to you for creating such an informative website. If you would like more information about this please visit stock screener The advantage of these stock screener is they allow for a vast amount of customization by the user. That same advantage can create a disadvantage for some users. Those users may not really know what they are looking for or what data criteria might produce a stock screener of stocks that present the highest possible probability of a successful stock trade.

    ReplyDelete
  8. Any help with importing dividend since purchase date? or for a date range?

    ReplyDelete
  9. Is it possible to automatically pull up the next ex-dividend or dividend pay date?

    ReplyDelete
    Replies
    1. If you can find a website that has the information you are looking for, then yes - you can.

      Delete
    2. I have found a website that has the next ex-dividend date. What do I have to put into my Google Docs Spreadsheet cell to call up that information? r.gurak@att.net

      Delete
  10. This comment has been removed by the author.

    ReplyDelete
  11. This comment has been removed by the author.

    ReplyDelete
  12. Google Current Dividend Amount:

    =REGEXextract(index(importhtml("https://www.google.com/finance?q=AAPL", "table", 3), 1, 2),"(.*?)/")+0

    ReplyDelete
  13. Any way to have the dividend formulas grab the dividend info from a cell on your sheet (i.e C23 rather than AAPL)?

    ReplyDelete
    Replies
    1. Rob, try this formula below and just put the cell that you want it to reference in place of where it says A10

      =REGEXextract(index(importhtml("https://www.google.com/finance?q="&A10, "table", 3), 1, 2),"(.*?)/")+0

      Delete
    2. Hi,
      thanks for the function. It comes as a result but always an error message:

      #Value
      "Parameter 1 the" ADD "" has number values. However, "0.24", a text value and can not be forced into a number value.
      What can I do?

      Alex

      Delete
    3. Drew - please modify the formula to pull the dividend YIELD instead of the dividend amount.

      I think it is in this formula "(.*?)/" ... HELP says it returns the FIRST part of the text that matches the expression. I want the part "after" the / ... thanks, Jimbo

      Delete
    4. Jim, this should do the trick:

      =REGEXextract(index(importhtml("https://www.google.com/finance?q="&A10, "table", 3), 1, 2),"/(.*$)")+0

      Delete
    5. 3rd reply attempt: Thanks - worked perfectly. Jim

      Delete
    6. Drew have used the formula to pull div amount for some time and it works perfectly. However your post of Feb 17th 2015 for yield does not seem to work. The data is available on GoogleFinance website. I am UK based so as an example lookup LON:RB.
      Would appreciate any comments

      Delete
    7. Drew in the few minutes between this and my last post I have managed to find the reason for yield not working. I find that the NASDAQ exchange on GoogleFinance has two simple numbers for Div Amt and Div Yield. The UK exchange complicates the issue by including a * at the end of yield (meant to indicate yield in UK Pounds). The question now is how do you ignore that star ???

      Thanks in anticipation
      Ray

      Delete
    8. Hi,
      If I have the ticker for WAL MART in cell A1
      NYSE:WMT
      How would the formula look then?

      Delete
    9. The formula works perfectly even for Canadian Stock Exchange (TSE) listings ..... Until you seek the yield of a preferred share FOR EXAMPLE (TSE:CWB-C). Then I get the following error message.

      Can you help please?

      Error
      Function INDEX parameter 3 value is 2. Valid values are between 0 and 1 inclusive.

      Delete
    10. Drew Clausen - thank you, the formula for div yield worked perfectly. Strangely, when I came back to my watch list a few hours later all the cells were populated with the #REF! error code:

      Function INDEX parameter 3 value is 2. Valid values are between 0 and 1 inclusive.

      The really weird part is that one single cell (the yield for VZ) was populated with the correct numeric value. The formula clearly works, and was working fine earlier, but now on 48/49 cells in the column the formula is returning an error message.

      Why does google docs do this and as mentioned above why can't G simply add DIVYIELD as a function in Google Finance?

      Really, really irritating....

      Delete
  14. Thank you very much...
    Do you know how could I get the "payout" ratio for a ticker?
    Thanks again.

    ReplyDelete
  15. Thanks for a very useful and informative piece. Adds missing data that the GoogleFinance function should have included at the outset.

    At least on my tablet, the data seems to be returned more quickly than from Yahoo.

    I also note that Google returns a quarterly dividend amount, whereas Yahoo returns an annual amount. Given the dividend amount, it is fun to play games in calculating a potential number of shares forthcoming on reinvestment of dividends.

    Thanks again.

    ReplyDelete
  16. Thank you for that REGEXextract and I can fetch Dividend from Yahoo Finance. However the REGEXextract command can only work on Text type of information.

    Yahoo Finance offers Netbook Value, and more but are in Numerics which the REGEXextract refused to work.

    Could you please, please introduce some other similar ways to fetch these values from Yahoo's Stock Table.

    This will be of great help to me, and I'd like to thank you in advance.

    ReplyDelete
    Replies
    1. OK, for non-text contents this is working
      =INDEX(IMPORTHTML("https://hk.finance.yahoo.com/q?s=0945.HK&ql=0", "table", 3), 7, 2)

      But, where there is no table from Yahoo's HTML, i.e., Netbook Value, I am not sure there is simple retrieval command line other than importing the CSV or XML. If there is, please let me know.

      Many thanks.

      Delete
  17. This comment has been removed by the author.

    ReplyDelete
  18. Very interesting blog ! I wish to thank the administrator for it sharing such useful information.

    ReplyDelete
  19. Ideas on extracting 5 year dividend yield for stocks from Morning Star?

    Yahoo no longer pulls this in from morning star (See eg. http://finance.yahoo.com/q/ks?s=SRCE+Key+Statistics. The field has been N/A for months, for just about every north american stock).

    Morning star has 5 years of dividend yield data available: http://quote.morningstar.ca/Quicktakes/Stock/DivAndSplit.aspx?t=XNAS:SRCE&region=USA&culture=en-CA.

    Each of the 5 yields has to be pulled in, added together, and divided by 5 to get the average yield. Suggestions on going about this?

    ReplyDelete
  20. This comment has been removed by the author.

    ReplyDelete
  21. For those enountering #REF errors for the Current Dividend Amount calculation, change the value for the "table" lookup from 3 to 0. For some reason Yahoo changed this recently (and might as well change it back soon, so stay alert).

    =REGEXextract(REGEXreplace(index (importhtml("http://finance.yahoo.com/q?s=KMI&ql=1", "table", 0), 8, 2); "[()]"; "") ; "([^/]*) ")

    Thank you, The Grouch, for originally posting this expression. It's extremely useful.

    Regards,
    Andreas

    ReplyDelete
    Replies
    1. I was really having a problem with the dividend amount and yield in my spreadsheet. They were working great until 3 days ago. Now the whole sheet works like it is corrupted. I like to be able to pull the stock symbol from a column (A). Is there a way to set this up in the formula you designed?

      Delete
    2. still encounter #REF in google for dividen amount, any suggestion?

      Delete
  22. This comment has been removed by the author.

    ReplyDelete
  23. I've been getting loading errors for weeks now. Both Google and Yahoo formulas. Am I the only one?

    ReplyDelete
    Replies
    1. Nope. Very, very frustrating considering it wouldn't take Google much effort to establish "d" and "y" as stock attributes for Google Sheets.

      Delete
  24. I had been using MarketXLS for this and it's great.
    http://marketxls.com/stock-quotes-in-excel/

    ReplyDelete
  25. Looks like things changed with Yahoo Finance [again].
    Here is the latest that's working for me to get the dividend (where B26 is something like AAPL):
    =VALUE(REGEXextract(REGEXreplace(index (importhtml("http://finance.yahoo.com/quote/"&B26&"", "table", 2), 6, 2), "[()]", "") , "([^/]*) "))

    Cheers - good luck - Dude from Utah!

    ReplyDelete
  26. This comment has been removed by the author.

    ReplyDelete
  27. Articles and content in this section of the website are really amazing. Great ideas indeed! I will surely keep this in my mind!
    best paying dividend stocks

    ReplyDelete