|
Google Sheets (or excel) experts...
04-09-2021, 03:17 PM
|
#1
|
Recycles dryer sheets
Join Date: Feb 2011
Location: Arlington Heights
Posts: 271
|
Google Sheets (or excel) experts...
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
|
|
|
|
Join the #1 Early Retirement and Financial Independence Forum Today - It's Totally Free!
Are you planning to be financially independent as early as possible so you can live life on your own terms? Discuss successful investing strategies, asset allocation models, tax strategies and other related topics in our online forum community. Our members range from young folks just starting their journey to financial independence, military retirees and even multimillionaires. No matter where you fit in you'll find that Early-Retirement.org is a great community to join. Best of all it's totally FREE!
You are currently viewing our boards as a guest so you have limited access to our community. Please take the time to register and you will gain a lot of great new features including; the ability to participate in discussions, network with our members, see fewer ads, upload photographs, create a retirement blog, send private messages and so much, much more!
|
04-09-2021, 05:38 PM
|
#2
|
Give me a museum and I'll fill it. (Picasso) Give me a forum ...
Join Date: May 2006
Location: west coast, hi there!
Posts: 8,809
|
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.
|
|
|
04-09-2021, 06:11 PM
|
#3
|
Recycles dryer sheets
Join Date: Feb 2011
Location: Arlington Heights
Posts: 271
|
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.
|
|
|
04-09-2021, 06:39 PM
|
#4
|
Give me a museum and I'll fill it. (Picasso) Give me a forum ...
Join Date: Dec 2008
Location: On a hill in the Pine Barrens
Posts: 9,722
|
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.
|
|
|
04-10-2021, 09:49 AM
|
#5
|
Recycles dryer sheets
Join Date: Feb 2011
Location: Arlington Heights
Posts: 271
|
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
|
|
|
04-10-2021, 10:13 AM
|
#6
|
Full time employment: Posting here.
Join Date: Feb 2017
Location: Severn
Posts: 947
|
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.
|
|
|
04-10-2021, 10:43 AM
|
#7
|
Thinks s/he gets paid by the post
Join Date: Feb 2007
Posts: 2,526
|
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.
|
|
|
04-10-2021, 01:25 PM
|
#8
|
Give me a museum and I'll fill it. (Picasso) Give me a forum ...
Join Date: Mar 2017
Location: City
Posts: 10,351
|
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.
__________________
Ignoramus et ignorabimus
|
|
|
04-10-2021, 03:38 PM
|
#9
|
Give me a museum and I'll fill it. (Picasso) Give me a forum ...
Join Date: Dec 2008
Location: On a hill in the Pine Barrens
Posts: 9,722
|
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.
|
|
|
04-10-2021, 07:31 PM
|
#10
|
Give me a museum and I'll fill it. (Picasso) Give me a forum ...
Join Date: Mar 2011
Posts: 8,421
|
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. 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.
__________________
Living well is the best revenge!
Retired @ 52 in 2005
|
|
|
|
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
|
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
» Recent Threads
|
|
|
|
|
|
|
|
|
|
|
|
|
» Quick Links
|
|
|