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.
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?
ReplyDeletee.g. instead of MMM, call cell B2?
Perfect! Was looking for this. Thanks so much!
ReplyDeleteHow do you call a cell. Just as Geoff Dudgeon said. thanks for the post. very helpful
ReplyDeleteI 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&"
ReplyDeleteAnyway you can post the entire formula you used. I am having trouble referencing the cell. Thank you!
Deletebrilliant! many thanks!
Delete=REGEXreplace(REGEXextract(REGEXreplace(index (importhtml(CONCATENATE("http://finance.yahoo.com/q?s=", A2, "&ql=1"), "table", 3), 8, 2), "[()]", "") , "..[^a-zA-Z][.0-9].%$"), "[%]", "")
DeleteA 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.
ReplyDeleteFor those looking to pull yield on mutual funds from Yahoo!, I thought I'd add this:
ReplyDelete=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)
Why not just use the built in GOOGLEFINANCE() function?
DeleteYield: =GOOGLEFINANCE("DODFX","yieldpct")
Dividend: =GOOGLEFINANCE("DODFX","incomedividend")
Why the heck doesn't google have the equivalent function for common stocks? Makes no sense.
DeleteDefinitely, 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.
ReplyDeleteAny help with importing dividend since purchase date? or for a date range?
ReplyDeleteIs it possible to automatically pull up the next ex-dividend or dividend pay date?
ReplyDeleteIf you can find a website that has the information you are looking for, then yes - you can.
DeleteI 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
DeleteThis comment has been removed by the author.
ReplyDeleteThis comment has been removed by the author.
ReplyDeleteGoogle Current Dividend Amount:
ReplyDelete=REGEXextract(index(importhtml("https://www.google.com/finance?q=AAPL", "table", 3), 1, 2),"(.*?)/")+0
Any way to have the dividend formulas grab the dividend info from a cell on your sheet (i.e C23 rather than AAPL)?
ReplyDeleteRob, try this formula below and just put the cell that you want it to reference in place of where it says A10
Delete=REGEXextract(index(importhtml("https://www.google.com/finance?q="&A10, "table", 3), 1, 2),"(.*?)/")+0
Hi,
Deletethanks 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
Drew - please modify the formula to pull the dividend YIELD instead of the dividend amount.
DeleteI 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
Jim, this should do the trick:
Delete=REGEXextract(index(importhtml("https://www.google.com/finance?q="&A10, "table", 3), 1, 2),"/(.*$)")+0
3rd reply attempt: Thanks - worked perfectly. Jim
DeleteDrew 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.
DeleteWould appreciate any comments
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 ???
DeleteThanks in anticipation
Ray
Hi,
DeleteIf I have the ticker for WAL MART in cell A1
NYSE:WMT
How would the formula look then?
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.
DeleteCan you help please?
Error
Function INDEX parameter 3 value is 2. Valid values are between 0 and 1 inclusive.
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:
DeleteFunction 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....
Thank you very much...
ReplyDeleteDo you know how could I get the "payout" ratio for a ticker?
Thanks again.
Thanks for a very useful and informative piece. Adds missing data that the GoogleFinance function should have included at the outset.
ReplyDeleteAt 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.
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.
ReplyDeleteYahoo 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.
OK, for non-text contents this is working
Delete=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.
This comment has been removed by the author.
ReplyDeleteVery interesting blog ! I wish to thank the administrator for it sharing such useful information.
ReplyDeleteIdeas on extracting 5 year dividend yield for stocks from Morning Star?
ReplyDeleteYahoo 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®ion=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?
This comment has been removed by the author.
ReplyDeleteFor 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).
ReplyDelete=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
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?
Deletestill encounter #REF in google for dividen amount, any suggestion?
DeleteThis comment has been removed by the author.
ReplyDeleteI've been getting loading errors for weeks now. Both Google and Yahoo formulas. Am I the only one?
ReplyDeleteNope. Very, very frustrating considering it wouldn't take Google much effort to establish "d" and "y" as stock attributes for Google Sheets.
DeleteLooks like things changed with Yahoo Finance [again].
ReplyDeleteHere 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!
This comment has been removed by the author.
ReplyDeleteArticles and content in this section of the website are really amazing. Great ideas indeed! I will surely keep this in my mind!
ReplyDeletebest paying dividend stocks