Google Sheets (or excel) experts...

[SoReady]

I am trying to pull the dividend and dividend dates using google sheets for mutual funds. The commands are
a) =googlefinance(B7,"incomedividend")
b) =googlefinance(B7,"incomedividenddate")
where B7 is the fund letters. Let's say for example VTSAX.

The results I'm getting are
a) $.35
b) 9/27/2018

When logging in to Vanguard I see the actual data should be
a) $0.32440
b) 03/23/2021

I may be just out of luck, as I know google isn't always reliable on these things (hence the 2018 date!), but I'm wondering if any experts know of a way to get this, if not from google, maybe another formula.

My goal is to project a yearly estimate on what the dividend payout may be for a holding. For example, 100 shares of VTSAX at $0.2344 is $23.44. Then I would multiply by the number of distribution per year such as 4 in this case. Total would be $93.76. I realize dividends fluctuate, but I'm just trying to get an ongoing estimate.

Thanks.

Bob

[Lsbcal]

An alternate (lazy) approach is to hand enter such data. I just go to the dividend tab and get it there for any fund. You can get to another VG fund dividend data tab by just editing the call letters in the URL so no real need to click around much. Not an elegant solution.

[SoReady]

Actually the lazy approach is what I’m trying to do. [emoji23]

I just hate to see good possible functionality go to waste. But if all else fails I can do the alternative. Considering most of my funds the maximum the dividends get distributed is 4 times a year it wouldn’t be an onerous task.

[target2019]

There is a spreadsheet template in this blog. I tried the template, but you should check everything nonetheless.
https://www.tawcan.com/step-step-gui...olio-template/

There are other threads around that discuss these function problems. It's a moving target...

Schwab calculates this income projection BTW.

[SoReady]

Thanks for the link. I couldn't get this persons formula to work in my spreadsheet. (=SPLIT(INDEX(IMPORTHTML(concatenate("https://finance.yahoo.com/quote/",B42),"table",2),4,2)," "))

So I just resorted to looking up and entering the numbers myself. For now that will have to do.

Thanks again,

Bob

[Lawrencewendall]

I loaded all my funds tickers into my free SeekingAlpha account. When any of them change their dividends, I get notified. Periodically, I will punch the tickers into dividendchannel to see if I missed anything.

[ejman]

I have a dozen funds. It really only takes me a few minutes to update dividends/cap gains to my Quicken 2010. It's so easy to do manually that I haven't bothered to update to a latter version of Quicken. The automation makes sense if one has so many funds/stocks that keeping track is burdensome. Then it becomes an issue to see if that automation is working correctly as programs/ formulas change, get modified etc. I think simple is better.

[OldShooter]

FWIW, Google Sheets is pretty buggy software. Several years ago I tried to write an application using their javascript API. The API was buggy and woefully feature incomplete. I remain subscribed to a thread on their forum that deals with this, just for amusement. It is now five years (!) long with people complaining about the same problems and Google doing nothing.

Point being I would not assume that the Sheets functions you are using actually work correctly. If at all possible I would test in Excel, which is an application managed by adults.

[target2019]

Quote:

Originally Posted by SoReady Thanks for the link. I couldn't get this persons formula to work in my spreadsheet. (=SPLIT(INDEX(IMPORTHTML(concatenate("https://finance.yahoo.com/quote/",B42),"table",2),4,2)," ")) So I just resorted to looking up and entering the numbers myself. For now that will have to do. Thanks again, Bob

For IMPORTHTML to work, it may be necessary to adjust the table refs. Funds are different than ETFs and stocks. Some here switched to other web sites for the source data.

Most developers from here and Bogleheads (and I'm not a developer, just a tinkerer) inspect HTML source, JSON, etc. And when you solve it, you will likely run into future need to make adjustments.

SeekingAlpha portfolio solution from Lawrencewendall sounds like a winner.

[marko]

While I love, love Googlesheets, I've found the dividend, dividend dates and such no longer work like they used to--or at least don't work reliably. I also found TAWCAN's info outdated.

BUT! Check out this youtube guy. A little bit of work but he shows you how to lift Yahoo Finance data into Sheets.

https://www.youtube.com/watch?v=efcyQ7GR5O8

It's relatively easy (??) and I had to follow it step by painstaking step, but it does work and what you learn you can transfer to other interests (concantation etc)

Another option I use sometimes is to enter my portfolio into M*, set up what you want to see and then download it to Sheets and massage, manage the info that way.