Import Dividends on Stocks to Spreadsheet

Discuss all general (i.e. non-personal) investing questions and issues, investing news, and theory.
Post Reply
Topic Author
GrowthSeeker
Posts: 1071
Joined: Tue May 15, 2018 10:14 pm

Import Dividends on Stocks to Spreadsheet

Post by GrowthSeeker »

A great frustration has been trying to import stock information into a spreadsheet in real time, ever since Yahoo did away with its API last year. If someone has an easier, reliable way to do this automatically (not manually cut and paste) please put a comment here. Also, I no longer have Excel since my laptop died; I'm using LibreOffice on the new laptop.

It turns out Google Sheets has a great function you can use to get the price, just put a ticker symbol (e.g. VTI) in cell A2 and put this formula in another cell: =GOOGLEFINANCE(A2,"price")

There is also a way to get Dividend information on Mutual Funds, just put a fund symbol (e.g. VTSAX) into cell A3 and put the following formula in another cell: =GOOGLEFINANCE(A3,"incomedividend")
You can clean things up a bit if you use that same formula on cells that contain something OTHER than a fund ticker by doing this: =iferror(GOOGLEFINANCE(A3,"incomedividend"),"") so it will be blank instead of displaying an error.

But AFAIK, Google has not yet put in a parameter similar to "incomedividend" which works on stock tickers.
I discovered that Google Sheets has some web scraping functions, and I recalled that at yahoo finance, you can have the historical data show "dividends only" (then click "Apply"). So I set the time scale for one year. This shows dividends for the past year. I thought about counting how many there were, but instead I subtract the most recent dividend date minus the prior dividend date then divide 365 by that. This could lead to some errors, but it's a start.
Also, the dividend information comes with some text wrapped around it. I use the SUBSTITUTE() function to strip away the unwanted information. It worked on the few stocks I tested. At first I used REGEXEXTRACT() but it gave errors on some stocks.

So how do you figure out where on the web page the data is that you want? Well, I'm no expert but if you put into a cell: =IMPORTHTML("some web address", "table", 1) then if there is a table on that page, it will show up in the blank cells below your cell. Then in place of the "1", try different integers until you see the information you want. It will show up in a grid of cells which you can then reference using the INDEX() function. It doesn't work on every web page.

I have not tested this on a lot of ticker symbols.

One thing I believe will probably not work over time (re Columns C and E) is that in the web address there are some large numbers referring to today's date and the date a year ago. I suspect that 6 months from now, my formula below will probably be showing data that is 6 months old. So those numbers will likely have to be changed, at least the one for today's date. (see history?period2= ) [** edit: fixed, I think]

Well, here's what I came up with: Assume the following starts in A1.

Code: Select all

Ticker	Price	Yahoo Historical       number	Days   #/Yr	Div/Yr	Yield  TodayCode
xom	77.53	*0.82*  Dividend	0.82	91	4	3.28	4.23%  1540699200
msft	106.96	*0.42*  Dividend	0.42	91	4	1.68	1.57%  1540699200
t	29.09	*0.5*   Dividend	0.5	92	4	2	6.88%  1540699200
vti	135.77	*0.714* Dividend	0.714	98	4	2.856	2.10%  1540699200
voo	243.76	*1.207* Dividend	1.207	90	4	4.828	1.98%  1540699200
Formulae: (put these into B2, C2, .... , H2, I2)
=GOOGLEFINANCE(A2,"price")

Code: Select all

=iferror(INDEX(IMPORTHTML("https://finance.yahoo.com/quote/"&A2&"/history?period1=1509203463&period2="&I2&"&interval=div%7Csplit&filter=div&frequency=1d","table",1),2,2),"")
=iferror(SUBSTITUTE(SUBSTITUTE(C2,"*","")," Dividend",""),"")

Code: Select all

=iferror(INDEX(IMPORTHTML("https://finance.yahoo.com/quote/"&A2&"/history?period1=1509203463&period2="&I2&"&interval=div%7Csplit&filter=div&frequency=1d","table",1),2,1)   -   INDEX(IMPORTHTML("https://finance.yahoo.com/quote/"&A2&"/history?period1=1509203463&period2="&I2&"&interval=div%7Csplit&filter=div&frequency=1d","table",1),3,1),"")
=iferror(round(365/E2),"")

=iferror(F2*D2,"")

=iferror(G2/B2,"")

=value(1540612800+(TODAY()-datevalue("10/27/2018"))*86400)

----
I personally don't want to put my actual financial information in the cloud on a Google Sheet, but my plan now is to have a list of tickers that I own or might own in a Google sheet, then on my LibreOffice Calc spreadsheet on my computer have the same list. Open the Google sheet which updates prices and now dividend yields. Copy those two columns and paste special (number) into my Libre sheet. The rest of my sheet refers to those cells into which I pasted updated numbers.
Any feedback would be appreciated.
----
**{edit: I added a Column I to include a formula that resolves to a number I believe yahoo will accept as the parameter for today's date}
Just because you're paranoid doesn't mean they're NOT out to get you.
Post Reply