4

I'd like to be able to access historical total (market) return data from a Google Sheet with the end goal of creating a self-updating rolling return histogram of various asset allocations.

However, it looks like the GoogleFinance functions in Google Sheets can only access share prices, not dividends. For this data to be useful to me, I would like to include the effects of reinvesting the dividends. I don't know of what use historical data is without any indication of dividend returns.

Is there a way to include dividends in a Google docs spreadsheet? Or even better, is there an online tool that will already show histograms (or show some variance metric) for various distributions?

Improve this question
3
  • 1
    +1 from me. Community: Why are people downvoting this question without commenting? Please, consider it impolite to do so. If you think a question can be improved, try and say how. Drive-by downvotes are seldom helpful.
    – Chris W. Rea
    Commented Apr 30, 2013 at 17:33
  • 1
    @ChrisW.Rea thanks. I have been experiencing this (downvotes without comments) with my other questions as well and it's quite discouraging--and I think harmful to the fledgling site's future.
    – glenviewjeff
    Commented Apr 30, 2013 at 17:35
  • 1
    @glenviewjeff - Yahoo data is adjusted, so numbers at two end points will reflect dividends and splits. Have you looked at Yahoo?
    – JTP - Apologise to Monica ♦
    Commented Apr 30, 2013 at 18:40

2 Answers 2

Reset to default
3

This is the same answer as for your other question, but you can easily do this yourself:

( initial adjusted close / final adjusted close ) ^ ( 1 / ( # of years sampled) )

Note: "# of years sampled" can be a fraction, so the one week # of years sampled would be 1/52.

Crazy to say, but yahoo finance is better at quick, easy, and free data. Just pick a security, go to historical prices, and use the "adjusted close".

money.msn's best at presenting finances quick, easy, and cheap.

Improve this answer
11
  • How does this account for dividends?
    – glenviewjeff
    Commented Apr 30, 2013 at 17:25
  • This doesn't answer the question--as far as I can tell, there is no adjusted close data accessible from GoogleFinance.
    – glenviewjeff
    Commented Apr 30, 2013 at 17:31
  • 1
    @glenviewjeff Why are you strictly bound to use Google Finance? There are numerous data sources out there (of similarly mediocre quality) that do provide adjusted closing prices. Alternatively, there are plenty of data sources with much more robust data packages. See this question.
    – John Bensin
    Commented Apr 30, 2013 at 17:34
  • 4
    @glenviewjeff Yahoo offers free direct access to financial data as well and many other data providers will offer an API, which is about as automatic as you can get. I don't think using an API is much work if it gives you the flexibility to create what you need (especially since it looks like Google Finance doesn't offer you the charts you need anyway).
    – John Bensin
    Commented Apr 30, 2013 at 17:38
  • 1
    +1 @JoeCoderGuy I didn't think of looking at SPY on Yahoo instead of ^INX.
    – glenviewjeff
    Commented Apr 30, 2013 at 21:26
0

At this time, Google Finance doesn't support historical return or dividend data, only share prices. The attributes for mutual funds such as return52 are only available as real-time data, not historical.

Yahoo Finance offers "adjusted return" prices for many tickers, which reflect total returns. Normally, one cannot access this data from within Google Sheets, but there is a plugin from WebDataHub.com that presently costs at least $19/month for their service.

There is also an article that walks you through how to write Google Apps Script that will allow Google Sheets to scrape data from Yahoo Finance.

Improve this answer
5
  • I take it you didn't find an online tool to show the histograms you were looking for? Otherwise you didn't answer your own question.
    – John Bensin
    Commented Apr 30, 2013 at 17:01
  • @JohnBensin no, I haven't. The question is still open.
    – glenviewjeff
    Commented Apr 30, 2013 at 17:26
  • 1
    I guess I'm confused why you would answer your own question immediately if you don't have an answer for it.
    – John Bensin
    Commented Apr 30, 2013 at 17:36
  • @JohnBensin It is an answer--it shows how to access total return data from GoogleFinance. It does not answer the "better yet, ...histograms" part, which is why the answer has not been accepted. Though if nobody else can find a canned solution, I will accept my own answer after the waiting period.
    – glenviewjeff
    Commented Apr 30, 2013 at 17:40
  • 1
    Except that the Google Finance data doesn't account for dividends, which is part of what you're asking about. I guess it seems strange when someone asks a question and immediately posts a fairly low-quality answer to it. Mind you, I'm not penalizing/downvoting you for it (you can check my reputation to see that), but it's worth mentioning.
    – John Bensin
    Commented Apr 30, 2013 at 17:42

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .