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
=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),"")
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(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}