Spreadsheets

[rayray]

I'm not a spreadsheet person, never actually did a spreadsheet that I can recall? I've always just used the data/info from my Fidelity or Vanguard accounts and when I had mostly mutual funds with a couple of individual stocks that was good enough for my needs. However, since I've been moving mostly out of mutual funds and into DGI's I realized that I actually need a spreadsheet in order to be more organized and see my progress easier then what the brokerages offer.

So I decided to try out Google's spreadsheet and so far I like what I'm able to do; however, I'm trying to import the dividend/yield info from Google Finance and well I can't seem to do it.

Does anyone know how to import the dividend or yield of a stock?


Thanks in advance!

[cannew]

Don't think it's possible. You can copy a block of data in Finance with Ctrl C, then paste it into a worksheet with Crl V. Once the info is in a worksheet and have the data you want in the order you want, block the just data you want with Ctrl C and then copy it into your actual worksheet with Ctrl V

[EricL]

I haven't found a way to import the dividend data either. I have resorted to manually typing in the annual dividend rate in one column and then just have another column show the yield by dividing by the share price.

[earthtodan]

It can be done.

1. Type a ticker symbol into cell A2

2. Paste this string into the cell where you want to show the yield:
=index(split(ImportXML("http://finance.google.com/finance?q="&A2, "//td[@data-snapfield='latest_dividend-dividend_yield']/following-sibling::*"),"/"),1,2)/100

I have a Google Doc that streams yields for about 40 companies. Once in a while they don't load properly, but for the most part it works great.

[rayray]

(08-09-2015, 12:39 PM)earthtodan Wrote: It can be done.

1. Type a ticker symbol into cell A2

2. Paste this string into the cell where you want to show the yield:
=index(split(ImportXML("http://finance.google.com/finance?q="&A2, "//td[@data-snapfield='latest_dividend-dividend_yield']/following-sibling::*"),"/"),1,2)/100

I have a Google Doc that streams yields for about 40 companies. Once in a while they don't load properly, but for the most part it works great.

Dan,

Thank you but for some reason it's not working for me. I either get #NAME? or #N/A in the cell.

[earthtodan]

Make sure the cell referenced in the middle of the string (A2 in the example) has the ticker symbol. Other than that, I'm not sure, I just took the formula from someone else.

[Caversham]

(08-09-2015, 02:05 PM)rayray Wrote:

(08-09-2015, 12:39 PM)earthtodan Wrote: It can be done.

1. Type a ticker symbol into cell A2

2. Paste this string into the cell where you want to show the yield:
=index(split(ImportXML("http://finance.google.com/finance?q="&A2, "//td[@data-snapfield='latest_dividend-dividend_yield']/following-sibling::*"),"/"),1,2)/100

I have a Google Doc that streams yields for about 40 companies. Once in a while they don't load properly, but for the most part it works great.

Dan,

Thank you but for some reason it's not working for me. I either get #NAME? or #N/A in the cell.

I use the exact same formula in my spreadsheet. I have found that sometimes copy+pasting formulas into Google Spreadsheet does not work. You can either try typing the formula manually, or sometimes I've found that just deleting and retyping the double quotes (") works.

If you want a pre-built spreadsheet, I really like the ones here:
http://www.twoinvesting.com/2013/08/inve...eadsheets/

[rayray]

Yea, I manually typed everything into the cell and still nada on the divi

[earthtodan]

One more thing, just as a last ditch try. Change the cell format to %.

[rayray]

(08-09-2015, 06:16 PM)earthtodan Wrote: One more thing, just as a last ditch try. Change the cell format to %.

Okay this worked but I had to replace all the "


=REGEXextract(REGEXreplace(index (importhtml(“http://finance.yahoo.com/q?s=”&A2&”&ql=1″, “table”, 3), 8, 2); “[()]”; “”) ; “([^/]*) “)


Found on this web page:

http://www.allaboutinterest.com/2014/05/...folio.html

This absolutely drove me crazy!! lol

[Roadmap2Retire]

Its ridiculous that Google Finance does not provide an easy way to import div yield - after a lot of experimentation and trial, I finally found the the two versions mentioned here worked.

Both the Yahoo and Google Finance options mentioned above work for me. Btw, you can import other data from Yahoo by replacing the code. Here are the details.

https://dubd.wordpress.com/2010/05/10/go...-stoxpage/

[Rasec]

I built a spreadsheet a couple of weeks ago to track my portfolio and my watch list, feel free to make a copy of the template and modify it to your needs:

https://docs.google.com/spreadsheets/d/1...1130311726

It pulls dividends and dividend yield, every so often it doesn't load properly but 5 mins later it shows the data again correctly.

Notes:
**Modify Yellow cells only
** All numbers and stocks shown as for demonstration only, it does not reflect my book and investments