S&P 500 Total Return: 63 years of daily data

Discuss all general (i.e. non-personal) investing questions and issues, investing news, and theory.
Post Reply
Leeraar
Posts: 4109
Joined: Tue Dec 10, 2013 8:41 pm
Location: Nowhere

S&P 500 Total Return: 63 years of daily data

Post by Leeraar » Sun Jan 05, 2014 9:22 pm

I like to look at historical data, to understand how things would have gone.

The problem often is, the data does not go back very far, or it does not reflect total return.

I discovered you can very easily get total return data for the S&P 500 in a spreadsheet for the last 63 years, since 1950. Here's how:

1. Go to http://finance.yahoo.com/

2. At the top of the page, just under the date, is some blue text: "S&P 500". Click on that.

3. On the next page, there is a menu on the left hand side. Click on "Historical Prices".

4. On the next page, you can choose a date range, and daily, weekly or monthly. Your choice. If you do change the defaults, click "Get Prices" to refresh the page. Otherwise, skip this step.

5. Note that the last column in the table is "Adj Close*". Scroll to the bottom of the page. Note that at the bottom of the table it says: "* Close price adjusted for dividends and splits". Click on "Download to Spreadsheet".

6. The result will be a downloaded csv file that you can open in Excel or any similar spreadsheet.

7. In the spreadsheet, you only want to keep the leftmost (date) column, and the rightmost column (Adj Return) which you should rename "Total Value". Delete the intervening columns.

8. Save the spreadsheet as something like "S&P 500 Total Return".

Now you have a very cool resource, and you do not need to be a total Excel or financial geek to use it. For example, if I invested $100 on January 1, 1995, what would it be worth today? The answer is $389.

Not very interesting, but if your question is, if I invested $100 a month for a child born in 1995 (who is now 18), would that pay for college, that is a very interesting question. It is easy to calculate. It gives you an estimate of the past, not a prediction of the future. But, is the number about $100, or $200, or $300, or ...?

Another interesting thing I did not have a feel for, is the following: What fraction of the time has the market been higher than today (edit: than on a given day)? After one month, after one year, ... ?

Here are the results:

Code: Select all

1 Mo 	3 Mo 	6 Mo 	1 Yr 	2 Yr 	5 Yr 	10 Yr
59.4%	64.6%	68.7%	72.7%	80.3%	80.7%	90.6%
In my opinion, the 1 year value was about what I expected. The one month value was higher than I expected, the ten year value was lower than I expected.

L.
Last edited by Leeraar on Sun Jan 05, 2014 10:26 pm, edited 1 time in total.
You can get what you want, or you can just get old. (Billy Joel, "Vienna")

User avatar
tfb
Posts: 7980
Joined: Mon Feb 19, 2007 5:46 pm
Contact:

Re: S&P 500 Total Return: 63 years of daily data

Post by tfb » Sun Jan 05, 2014 10:17 pm

I'm sorry to bust your excitement, but you don't get reliable data this way. Calculate the return from 12/31/2012 to 12/31/2013 and compare with what Vanguard reports as the benchmark return for its S&P 500 fund. You will see why.
Harry Sit, taking a break from the forums.

Leeraar
Posts: 4109
Joined: Tue Dec 10, 2013 8:41 pm
Location: Nowhere

Re: S&P 500 Total Return: 63 years of daily data

Post by Leeraar » Sun Jan 05, 2014 10:21 pm

tfb wrote:I'm sorry to bust your excitement, but you don't get reliable data this way. Calculate the return from 12/31/2012 to 12/31/2013 and compare with what Vanguard reports as the benchmark return for its S&P 500 fund. You will see why.
Please explain.

The data on Yahoo is incorrect?

L.
You can get what you want, or you can just get old. (Billy Joel, "Vienna")

User avatar
jimb_fromATL
Posts: 2278
Joined: Sun Nov 10, 2013 12:00 pm
Location: Atlanta area & Piedmont Triad NC and Interstate 85 in between.

Re: S&P 500 Total Return: 63 years of daily data

Post by jimb_fromATL » Sun Jan 05, 2014 10:34 pm

Great source of data, but unfortunately, while their data table shows a column for data adjusted for dividends that is used for mutual funds and individual stocks, it doesn't have the dividend data for ^gspc and other indexes themselves.

If you click on "dividends only" and "get data" for ^gspc it shows no data.

If you look at similar data for mutual funds, it does. For instance, HERE is the historical data for VFINX (Vanguard''s S&P index fund) The "adjusted data" column prior to dividends being paid gives a much smaller number because they do readjust it in relation to the latest day you choose for the table. Long existing funds like VFINX and VWELX have daily data going back to around 1987, which is probably more relevant than data from the post-war and early baby-boomer eras anyway.

Incidentally, be aware that when choosing monthly and weekly data, the date used is for the beginning of the week or month, and the data is for the last day of the month or the most recent posted day for the current month.

Have you tried their interactive charts? You can do some dandy comparisons with several funds and/or the indexes -- though the indexes don't have dividends reinvested. They also let you display a number of different technical indicators like simple and exponential moving averages, bolinger bands, and several I don't know anything about.

VFINX vs VWELX CHART

Most online reports of long-term historical averages reflect the average APY for a lump sum invested x number of years ago and use only year-end data. However, most folks are more likely to be contributing a monthly amount via their 401(k)s or IRAs, or perhaps yearly amounts. in IRAs.

So it can be pretty interesting to download the data and do the math in a spreadsheet table using actual monthly gains and losses for "dollar-cost-averaging" with monthly contributions. The historical data adjusted for dividends reinvested will give a much more accurate idea of how the funds would really perform in real-life retirement accounts.

With monthly data like Yahoo provides, I think we have a relatively unique opportunity to see how many funds compare during good times and bad --- when you have data from the irrational exuberance of the 90's, followed by the crash of the economy and market in 2001-2002, then recovery from it, and then the crash of 2008 -- which was the worst since 1929-- and now the recovery from it.

This also gives us a chance to see how some theories about dollar-cost averaging and about balancing between bonds and equities really have worked in real life through good times and bad.

jimb

User avatar
jimb_fromATL
Posts: 2278
Joined: Sun Nov 10, 2013 12:00 pm
Location: Atlanta area & Piedmont Triad NC and Interstate 85 in between.

Re: S&P 500 Total Return: 63 years of daily data

Post by jimb_fromATL » Sun Jan 05, 2014 10:40 pm

tfb wrote:I'm sorry to bust your excitement, but you don't get reliable data this way. Calculate the return from 12/31/2012 to 12/31/2013 and compare with what Vanguard reports as the benchmark return for its S&P 500 fund. You will see why.
In general the data on Yahoo is the actual published closing data. The problem is that no two sites seem to use exactly the same starting and end dates for their calculations.

Plus, most of the data from all the online sources show yearly data as though you either invest only on the 1st day of the year or only a lump sum at the beginning of a 3, 5, or 10 year period. And the long term averages and gains are only for a lump sum invested at the beginnning of the time frame.

In real life, most people invest periodically like monthly, effective Dollar-Cost-Averaging. Often the real compound annual growth rate or average APY for monthly contributions can be considerably bettor or worse than the yearly totals -- especially in the wild rides up and down we've seen since around 2000-2001. So your actual results for any fund probably aren't anywhere near exactly the same that even Vanguard themselves show.

jimb

User avatar
tfb
Posts: 7980
Joined: Mon Feb 19, 2007 5:46 pm
Contact:

Re: S&P 500 Total Return: 63 years of daily data

Post by tfb » Sun Jan 05, 2014 10:47 pm

jimb_fromATL wrote:If you look at similar data for mutual funds, it does [include dividends]. For instance, HERE is the historical data for VFINX (Vanguard''s S&P index fund) The "adjusted data" column prior to dividends being paid gives a much smaller number because they do readjust it in relation to the latest day you choose for the table. Long existing funds like VFINX and VWELX have daily data going back to around 1987, which is probably more relevant than data from the post-war and early baby-boomer eras anyway.
Even for mutual funds it's not very accurate. It gives you a ballpark for stock funds but it doesn't work well at all for bond funds, especially Vanguard bond funds. Try VBMFX and compare with what Vanguard reports.
Harry Sit, taking a break from the forums.

Leeraar
Posts: 4109
Joined: Tue Dec 10, 2013 8:41 pm
Location: Nowhere

Re: S&P 500 Total Return: 63 years of daily data

Post by Leeraar » Sun Jan 05, 2014 10:54 pm

jimb,

I completely agree with the last few paragraphs of your post.

Can we agree that the Yahoo data is a valid baseline for historical data?

If not, can someone please propose a better baseline?

L.
You can get what you want, or you can just get old. (Billy Joel, "Vienna")

User avatar
JoMoney
Posts: 6291
Joined: Tue Jul 23, 2013 5:31 am

Re: S&P 500 Total Return: 63 years of daily data

Post by JoMoney » Mon Jan 06, 2014 2:22 am

I like the data available at Morningstar , just look up any fund and you can add in the S&P500 Total Return benchmark.
The monthly data available from the Shiller Excel spreadsheet http://www.econ.yale.edu/~shiller/data.htm has been good enough for most purposes that I can see. Daily data might be nice, but monthly is good enough for me.
"To achieve satisfactory investment results is easier than most people realize; to achieve superior results is harder than it looks." - Benjamin Graham

JW-Retired
Posts: 6999
Joined: Sun Dec 16, 2007 12:25 pm

Re: S&P 500 Total Return: 63 years of daily data

Post by JW-Retired » Mon Jan 06, 2014 8:31 am

Leeraar wrote:jimb,
I completely agree with the last few paragraphs of your post.
Can we agree that the Yahoo data is a valid baseline for historical data?
If not, can someone please propose a better baseline?
L.
It's valid but it is only the daily closing index value, not the total return. It doesn't include the effect of reinvesting dividends. If you want total return you have to get data on SP500 dividends and add that in separately.

You can get such dividend data from Shiller's website. http://aida.wss.yale.edu/~shiller/data.htm
JW
Retired at Last

Leeraar
Posts: 4109
Joined: Tue Dec 10, 2013 8:41 pm
Location: Nowhere

Re: S&P 500 Total Return: 63 years of daily data

Post by Leeraar » Mon Jan 06, 2014 9:36 am

JW Nearly Retired wrote:
Leeraar wrote:jimb,
I completely agree with the last few paragraphs of your post.
Can we agree that the Yahoo data is a valid baseline for historical data?
If not, can someone please propose a better baseline?
L.
It's valid but it is only the daily closing index value, not the total return. It doesn't include the effect of reinvesting dividends. If you want total return you have to get data on SP500 dividends and add that in separately.

You can get such dividend data from Shiller's website. http://aida.wss.yale.edu/~shiller/data.htm
JW
JW,

Please read the OP and the information at Yahoo. I believe it is Total Return data.

L.
You can get what you want, or you can just get old. (Billy Joel, "Vienna")

JW-Retired
Posts: 6999
Joined: Sun Dec 16, 2007 12:25 pm

Re: S&P 500 Total Return: 63 years of daily data

Post by JW-Retired » Mon Jan 06, 2014 10:32 am

Leeraar wrote:
JW Nearly Retired wrote:
Leeraar wrote:jimb,
I completely agree with the last few paragraphs of your post.
Can we agree that the Yahoo data is a valid baseline for historical data?
If not, can someone please propose a better baseline?
L.
It's valid but it is only the daily closing index value, not the total return. It doesn't include the effect of reinvesting dividends. If you want total return you have to get data on SP500 dividends and add that in separately.

You can get such dividend data from Shiller's website. http://aida.wss.yale.edu/~shiller/data.htm
JW
JW,

Please read the OP and the information at Yahoo. I believe it is Total Return data.

L.
Not for SP500. Download and look at the spreadsheet for ^GSPC. Column E (close) and column G (adj. close) are identical numbers.
JW
Retired at Last

wcvarones
Posts: 1
Joined: Fri Aug 10, 2018 11:11 am

Re: S&P 500 Total Return: 63 years of daily data

Post by wcvarones » Fri Aug 10, 2018 11:14 am

The Yahoo data is bad because there are no dividends included in the "adjusted" column. It's the same as the non-adjusted.

But you can get good annual data from Shiller:

http://www.econ.yale.edu/~shiller/data.htm

FactualFran
Posts: 749
Joined: Sat Feb 21, 2015 2:29 pm

Re: S&P 500 Total Return: 63 years of daily data

Post by FactualFran » Fri Aug 10, 2018 1:30 pm

As others have mentioned, the historical data at Yahoo for the symbol ^GSPC are the price index values, with the values in the Adj Close column not adjusted for dividends. The Adj Close column is adjusted for distributions with the historical data for mutual funds.

Yahoo has daily S&P 500 Total Return index values back to 1988 at https://finance.yahoo.com/quote/%5ESP50 ... tory?ltr=1

User avatar
JoMoney
Posts: 6291
Joined: Tue Jul 23, 2013 5:31 am

Re: S&P 500 Total Return: 63 years of daily data

Post by JoMoney » Fri Aug 10, 2018 1:35 pm

"To achieve satisfactory investment results is easier than most people realize; to achieve superior results is harder than it looks." - Benjamin Graham

stablegenius
Posts: 1
Joined: Wed Aug 29, 2018 4:15 pm

Re: S&P 500 Total Return: 63 years of daily data

Post by stablegenius » Wed Aug 29, 2018 4:29 pm

[OT comment removed by admin LadyGeek]

SPX does NOT pay dividends and it is NOT adjusted for dividends or ^GSPC data on Yahoo is actually accurate. If you want to see the dividend information look at S&P 500 Total Return Index. On Yahoo, it is ^SP500TR. You can alternatively look at ETFs (or SPY for SPX) for dividends, and the data with any dividends is also available on Yahoo, but they will be probably quarterly or monthly payments, not daily dividend payments as ETFs don't pay daily dividends...

If you want to extract the daily dividend payments of S&P 500 Index from the two data streams (above), you just have to find the daily returns of the cash only index and total return index (today's close / previous day's close - 1), take the difference and multiply it with the PREVIOUS day's close, this will give you THAT day's dividend payment. Then you add them up for 1 year to find the running dividend payments. You can divide that to the daily close to find the dividend yield on any index publishing nicely its cash and total returns separately...

There are potential errors in any data, even from paid sources, so you should have a way of detecting and better correcting them. You'd better get your data from two different sources for accuracy with a quick comparison...

Post Reply