Dividend Meter in Google Sheet

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...
 
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.
 
Last edited:
dirt_dobber said:
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...
Click to expand...
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 said:
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.
Click to expand...

That is awesome - Thanks! :cool:
 
target2019 said:
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.
Click to expand...

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.
 
target2019 said:
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=1uXts1bL7Ek_rtDM_28N_slPpKMkfiE_GDzXXHlhHIyA
Click to expand...

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 said:
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?
Click to expand...
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.
 
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.
 
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.
 
Thanks for this very useful thread.

Ha
 
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.
 
Last edited:
W2R said:
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.
Click to expand...

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.
 
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.
 
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.
 
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.
 
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 said:
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.
Click to expand...

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 ;)
 
Last edited:
You're welcome. Those dividends are pesky, for several reasons. In some cases I just type in the div.

Here's a bonus tip I just found. In Chrome, open a new tab with:
Code: 
data:text/html, <html contenteditable>
Copy and paste that code into the address bar.

You can paste or type text into the browser window. I usually use a text editor to paste and temporarily store things, like formula versions. But this is pretty cool.
 
You must log in or register to reply here.

Similar threads

Lsbcal
Could bonds be great in 2024?
2
Replies
25
Views
3K
pb4uski
pb4uski
K
Lost phone, recovered from perp
2
Replies
49
Views
3K
Gremlin
Gremlin
S
Google Sheets (or excel) experts...
Replies
9
Views
2K
marko
M
audreyh1
Can eating mostly Ultra-processed Food help you gain weight?
Replies
19
Views
1K
Katsmeow
Katsmeow
E
Roth conversion study surprised result
2 3
Replies
66
Views
9K
Dtail
D

Latest posts

Back
Top Bottom