How to Reconstruct a Stock Index from Scratch

Update

Regular readers will know that I often mention a desire to accumulate good data to add to the collection here.  Reliable old stock market history is notoriously difficult to come by, and the sources that do exist are either very limited in the indexes they track, heavily copyrighted, or insanely expensive and targeted to financial institutions.  So everyday investors like you and me have very few resources available and we do the best with what we can find.

After looking far and wide, I eventually came to the realization that finding good long-term returns data for individual indices that predates modern index fund equivalents with no strings attached just isn’t going to happen.  But being a naturally resourceful person, I wondered if that wasn’t a roadblock but an opportunity.  What can I personally do to fill the glaring hole in historical market data?

Long story short — I believe I’ve figured out how to simulate stock index returns from freely available source data. This covers all nine categories with every combination of large/mid/small and growth/blend/value, with numbers dating all the way back to 1927.

So here’s where I need your help.  I’ve done a lot of the legwork to deconstruct the methods of the various index funds and work out the math of translating the available data to real-world index returns.  After comparing the numbers to known returns, I’m even pretty confident in the results.  But it’s a complex topic that deserves more eyes, and I’d appreciate any feedback you can offer.

I’ve started a page that goes into the details of the methodology, and also am offering the spreadsheet to anyone who would like to see how I did it.  You can find it all here:

///  Stock Index Calculator  ///

I won’t claim it’s simple, and I’ll say outright that this is much more technical than I typically venture on the site.  Frankly, it has been a learning experience for me as well.  But I’ve done my best to make it as clear and easy to follow as possible, and I know for a fact that some of you guys and gals are in the finance industry and probably have some insights to offer.

For any fellow spreadsheet geeks out there, feel free to dissect it and let me know what you think.  If you find any errors, have any questions, or would like to suggest any improvements, please contact me.  I’ll update the documentation with anything we learn and provide a central hub for continuous updates.

There are lots of cool applications for having this information available, and my ultimate goal is to greatly expand the tools on the site.  But it all starts with good data.  So study it for yourself and join me in the effort.  Good decisions require good information — let’s work together to provide it!