Join Early Retirement Today
Reply
 
Thread Tools Search this Thread Display Modes
Dividend Meter in Google Sheet
Old 04-02-2016, 10:59 AM   #1
Thinks s/he gets paid by the post
target2019's Avatar
 
Join Date: Dec 2008
Posts: 3,462
Dividend Meter in Google Sheet

How To Create A Dividend Tracker Spreadsheet | Dividend Meter

Above linked article shows how to construct a simple tracker sheet for stock or investments.

Icing on the cake is it shows how to put a chart/meter on the sheet.

One thing I've noticed is that dividend data from the referenced formula does not work for some reason.
__________________

__________________
target2019 is offline   Reply With Quote
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!

Old 04-02-2016, 11:36 AM   #2
Recycles dryer sheets
 
Join Date: Aug 2015
Location: Bee Cave, TX
Posts: 108
that is really neat - wish they would have attached an .xlxs (excel) download that all you would have to do is modify it for your funds. Would save me a lot of time...
__________________

__________________
dirt_dobber is offline   Reply With Quote
Old 04-02-2016, 03:56 PM   #3
Recycles dryer sheets
 
Join Date: Feb 2011
Posts: 84
This is pretty cool. I wonder if it can work for mutual funds?

I'll need to explore further on this.
__________________
SoReady is offline   Reply With Quote
Old 04-02-2016, 04:17 PM   #4
Thinks s/he gets paid by the post
steelyman's Avatar
 
Join Date: Feb 2011
Location: Triangle
Posts: 3,218
That is very nice, thanks. I'm a Sheets devotee and use it a lot for my own investments. Apart from some of the advantages of that model (free, cloud, etc), the GoogleFinance extensions are very useful. I'm also about to bail from Excel completely.

I'd tried to see if there was a dividend-related attribute to GoogleFinance and had no luck. I didn't know anything about using, specifically, Step 10 in this procedure which is key, with IMPORTDATA and CONCATENATE. That seems the do the trick, the rest of everything I'd already had in a separate tab for an investment workbook I've had for a while. That tab (my organization, which works for my needs) is labeled "Securities" and contains a listing of securities that may be common to different accounts in separate tabs (stocks/funds that may appear in multiple places, for example a brokerage account, an IRA, etc). They are referenced across tabs.

I've also been working with setting up a separate Sheet for a friend, and she wanted yields so I punted and just hard-wired in the information you'd get from any number of quote sources available on line. But the ability to import data from Yahoo! automatically could fix that.

My tests of the examples with stocks of interest over here went OK except one result briefly returned an array reference, which caused Sheets to generate an error. That error soon disappeared and the data came back all right, no idea why. I don't care especially.

The other thing I did was separate the strings ("http://..." and the trailing "&f=d" string) into their own cells and referenced those in the per-stock lines rather than cut-and-paste the whole thing.

I like the presentation of the process, with the graphics to help people follow along, step by step. I will pass that on.

Good post! I might have skipped it if you hadn't included "Google Sheet" in the title.

Great to try while waiting for the NCAA basketball games to start.
__________________

steelyman is offline   Reply With Quote
Old 04-02-2016, 06:32 PM   #5
Thinks s/he gets paid by the post
target2019's Avatar
 
Join Date: Dec 2008
Posts: 3,462
Quote:
Originally Posted by SoReady View Post
This is pretty cool. I wonder if it can work for mutual funds?

I'll need to explore further on this.
This link has help for googlefinance functions:
https://support.google.com/docs/answer/3093281?hl=en

I have a shared sheet with examples for stocks, and for funds:
https://drive.google.com/open?id=1uX...E_GDzXXHlhHIyA
__________________
target2019 is offline   Reply With Quote
Old 04-02-2016, 06:36 PM   #6
Thinks s/he gets paid by the post
target2019's Avatar
 
Join Date: Dec 2008
Posts: 3,462
Quote:
Originally Posted by dirt_dobber View Post
that is really neat - wish they would have attached an .xlxs (excel) download that all you would have to do is modify it for your funds. Would save me a lot of time...
Our lucky day!

Today I was rooting around trying to find out why my importdata function wasn't working for Yahoo Finance and google sheets. I did not find the answer to that, but found an excel sheet does this tracking:

How to Import Share Price Data into Excel - Market Index

There are other ways to do this with Excel, and I'll post to this thread if I find other ways. That sample has a custom addin that is protected (it does the download work), and also requires you to enable macros. I've done that, and have had no problems.
__________________
target2019 is offline   Reply With Quote
Old 04-03-2016, 06:15 AM   #7
Recycles dryer sheets
 
Join Date: Aug 2015
Location: Bee Cave, TX
Posts: 108
Quote:
Originally Posted by target2019 View Post
Our lucky day!

Today I was rooting around trying to find out why my importdata function wasn't working for Yahoo Finance and google sheets. I did not find the answer to that, but found an excel sheet does this tracking:

How to Import Share Price Data into Excel - Market Index

There are other ways to do this with Excel, and I'll post to this thread if I find other ways. That sample has a custom addin that is protected (it does the download work), and also requires you to enable macros. I've done that, and have had no problems.
That is awesome - Thanks!
__________________
dirt_dobber is offline   Reply With Quote
Old 04-03-2016, 07:38 AM   #8
Thinks s/he gets paid by the post
steelyman's Avatar
 
Join Date: Feb 2011
Location: Triangle
Posts: 3,218
Quote:
Originally Posted by target2019 View Post
Our lucky day!

Today I was rooting around trying to find out why my importdata function wasn't working for Yahoo Finance and google sheets. I did not find the answer to that, but found an excel sheet does this tracking:

How to Import Share Price Data into Excel - Market Index

There are other ways to do this with Excel, and I'll post to this thread if I find other ways. That sample has a custom addin that is protected (it does the download work), and also requires you to enable macros. I've done that, and have had no problems.
There you go again (sound familiar?).

I was curious about the use of Yahoo! Finance and this page you linked provides that API. There is a selector to directly query the yield (by using "y" rather than "d" in the string passed to IMPORTDATA in Sheets) rather than calculate from annual dividend and current price. I tried it out that way, and the results are very close. Thanks again.
__________________

steelyman is offline   Reply With Quote
Old 04-03-2016, 09:04 AM   #9
Recycles dryer sheets
 
Join Date: Feb 2011
Posts: 84
Quote:
Originally Posted by target2019 View Post
This link has help for googlefinance functions:
https://support.google.com/docs/answer/3093281?hl=en

I have a shared sheet with examples for stocks, and for funds:
https://drive.google.com/open?id=1uX...E_GDzXXHlhHIyA
Thanks for the links it is helpful to look at. In looking at your shared sheet can I conclude that in order to get yield you are using the corresponding ETF? I've never really looked hard at how mutual funds distribute their dividends other that most hand them out monthly or quarterly. But is the only way to estimated yearly distributions is through the use of an ETF?
__________________
SoReady is offline   Reply With Quote
Old 04-03-2016, 09:47 AM   #10
Thinks s/he gets paid by the post
target2019's Avatar
 
Join Date: Dec 2008
Posts: 3,462
Quote:
Originally Posted by SoReady View Post
Thanks for the links it is helpful to look at. In looking at your shared sheet can I conclude that in order to get yield you are using the corresponding ETF? I've never really looked hard at how mutual funds distribute their dividends other that most hand them out monthly or quarterly. But is the only way to estimated yearly distributions is through the use of an ETF?
If you want to change anything, make a copy to your own drive. Forgot to mention that.

In the shared sheet, google has incomedividend function only for mutual funds, and it shows $1.08 on 12/25/2015 date. You can't get that for stocks. I added another column to the funds side, and entered VDE. That shows no results for the mf-only functions.

If you look on the YahooFunc sheet, that shows you how to get dividend data from Yahoo (for stocks and ETFs), and import to google sheet.
__________________
target2019 is offline   Reply With Quote
Old 04-03-2016, 10:20 AM   #11
Thinks s/he gets paid by the post
 
Join Date: Mar 2011
Posts: 3,297
I'm an avid and exclusive Google Sheets user and have had this type of tracker for a few years now.

My only gripe is that until about a month ago, GS would update mutual fund prices a few minutes after 6PM EST. Now it seems to take until around midnight.

From my experience, Sheets does just about everything Excel does...and it's free and autobacks up.
__________________
Living well is the best revenge!
Retired @ 52 in 2005
marko is offline   Reply With Quote
Old 04-04-2016, 07:07 AM   #12
Thinks s/he gets paid by the post
steelyman's Avatar
 
Join Date: Feb 2011
Location: Triangle
Posts: 3,218
After giving the cross-resource approach (Google Sheets and Yahoo! Finance), I've decided to not use it for my spreadsheets in Sheets.

It's not that it's a bad idea (pretty helpful, actually) but that it seems to be too much to ask of Sheets at present. Retrieving data in that way from an external source seems to tax thihgs and slow them down substantially. I think the reference errors that are common exacerbate things.

What the approach is basically trying to do is incorporate dividend data into a Google spreadsheet. That's good, and you can derive yield from that since updated security prices in near real-time directly using GoogleFinance().

I backed out all data import things from the sheets I work with and instead used annual dividend data, manually entered and retrieved from Yahoo! Finance quotes. Didn't take too long (less than a full inning in baseball terms).

The ideas presented in the original post/link in this thread are good and gave me additional thoughts about useful ways to summarize that type of information. Not "useless" at all. And the GoogleFinance() documentation link seems to be updated since the last time I looked at it - I've now added expense ratio data for my mutual fund holdings.
__________________

steelyman is offline   Reply With Quote
Old 04-04-2016, 08:27 AM   #13
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
haha's Avatar
 
Join Date: Apr 2003
Location: Hooverville
Posts: 22,073
Thanks for this very useful thread.

Ha
__________________
"As a general rule, the more dangerous or inappropriate a conversation, the more interesting it is."-Scott Adams
haha is online now   Reply With Quote
Old 04-04-2016, 09:00 AM   #14
Administrator
W2R's Avatar
 
Join Date: Jan 2007
Location: New Orleans
Posts: 37,129
Wow, I just log into Vanguard and manually copy my dividends into Excel (Open Office actually, which is free). Takes about 10 seconds.

I've been using Excel nearly every day for 27 years by now, and it is very easy for me to do the type of things I usually do in Excel. Guess if I wasn't used to it, it might be more challenging.
__________________
Fairy tales are more than true: not because they tell us that dragons exist, but because they tell us that dragons can be beaten.

― N. Gaiman (2002)








W2R is online now   Reply With Quote
Old 04-04-2016, 10:15 AM   #15
Thinks s/he gets paid by the post
steelyman's Avatar
 
Join Date: Feb 2011
Location: Triangle
Posts: 3,218
Quote:
Originally Posted by W2R View Post
Wow, I just log into Vanguard and manually copy my dividends into Excel (Open Office actually, which is free). Takes about 10 seconds.

I've been using Excel nearly every day for 27 years by now, and it is very easy for me to do the type of things I usually do in Excel. Guess if I wasn't used to it, it might be more challenging.
Well, that makes sense to me. I do the same thing, but the target is Quicken (for historical records).

I think the idea of the meter and retrieving various data leans more toward planning purposes, overall evaluation of your investments, that kind of thing.
__________________

steelyman is offline   Reply With Quote
Old 04-04-2016, 03:23 PM   #16
Thinks s/he gets paid by the post
 
Join Date: May 2008
Posts: 2,133
Thank you very much for the link. Problem with Yahoo Fiance import and I did pull the y instead of d and still it is a bit flaky, so I did what W2R does - Fill it in manually, but I pulled everything else from Google Finance! So overall, it works for me.
__________________
tmm99 is offline   Reply With Quote
Old 04-04-2016, 03:44 PM   #17
Thinks s/he gets paid by the post
target2019's Avatar
 
Join Date: Dec 2008
Posts: 3,462
Yes, IMPORTDATA went flaky recently. I'm sure the more experienced users can say why.
I offered up the link to excel sheet as it uses a different method to pull dividend data from Yahoo.

The purpose of pulling live data is obviously not everyone's cup of tea.
__________________
target2019 is offline   Reply With Quote
Old 04-05-2016, 05:59 AM   #18
Thinks s/he gets paid by the post
steelyman's Avatar
 
Join Date: Feb 2011
Location: Triangle
Posts: 3,218
Just as a clarification, probably not necessary. I don't think there is any contradiction between what W2R was saying about entering dividends and what I was describing about retrieving information regarding a declared annual dividend.

I do the same thing as she does, except using Quicken rather than Excel, to track distributions that have occurred in my accounts. I don't rely on automatic downloading features of Quicken, but enter manually: amount of distribution (dollars) and the number of shares that represents.

But companies regularly declare their per-share dividends and that's available through places like Yahoo! Finance. For example, GE has a current annual dividend of $0.92. I use that as an example because it has been a traditional "widows and orphans" stock (I am both, sad to say). That dividend is static until the company says otherwise,

The current price (available through Google Sheets with GoogleFinance()) along with the annual dividend determines the yield, and changes all the time. That's what the dividend meter is looking for. That kind of info can be useful when evaluating your current holdings.
__________________

steelyman is offline   Reply With Quote
Old 09-09-2016, 09:17 PM   #19
Thinks s/he gets paid by the post
target2019's Avatar
 
Join Date: Dec 2008
Posts: 3,462
I discovered a blog page which discusses the use of importxml in a googlesheet.

Using Google Spreadsheet for dividend investment - Tawcan

The article describes the use of SPLIT and IMPORTXML in order to bring the dividend and div yield into two successive columns.

I found that for an ETF, such as SCHD, it returns a "-", and then an accurate yield into the next column. If you need the actual yield amount, then another calculation is required.

Good luck with your tracking.
__________________
target2019 is offline   Reply With Quote
Old 01-15-2017, 02:22 PM   #20
Thinks s/he gets paid by the post
 
Join Date: May 2008
Posts: 2,133
Quote:
Originally Posted by target2019 View Post
I discovered a blog page which discusses the use of importxml in a googlesheet.

Using Google Spreadsheet for dividend investment - Tawcan

The article describes the use of SPLIT and IMPORTXML in order to bring the dividend and div yield into two successive columns.

I found that for an ETF, such as SCHD, it returns a "-", and then an accurate yield into the next column. If you need the actual yield amount, then another calculation is required.

Good luck with your tracking.
I know this is an old thread, but I wanted to thank you for this thread and most specifically, your last post with a very useful link!

I couldn't get the ImportXML function to work last year and ended up typing in the dividend info for my individual stock holdings manually (and there were many). I fixed the problem just now by retyping the double quotes (which was mentioned in your link via your last post), and it is working now
__________________

__________________
tmm99 is offline   Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are Off
Pingbacks are Off
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Do you use a water meter monitor? Nords Other topics 13 02-16-2010 04:52 PM
Truth-o-Meter mickeyd Other topics 2 03-15-2008 06:18 PM
Great Moments in Sports: 100 meter stiletto dash. calmloki Other topics 0 08-20-2007 01:32 PM
Household meter readings Khan Other topics 5 03-04-2007 06:46 PM
Can you read my electric meter? Bram Other topics 22 02-15-2007 06:56 PM

 

 
All times are GMT -6. The time now is 10:30 PM.
 
Powered by vBulletin® Version 3.8.8 Beta 1
Copyright ©2000 - 2017, vBulletin Solutions, Inc.