Rule #1 Analysis Spreadsheet: Updated with Auto External Data Import

My cousin, Kelvin, works a major Canadian bank.  So, he's used to working with automated spreadsheets with VB scripts, etc.  So, when I sent him my manual Rule #1 spreadsheet, he mocked at how rudimentary it was.  So, thanks to him, I've decided to educate myself on how to further automate my spreadsheet.  I already knew how to pull external data from websites, but didn't know how to dynamically update the query when I changed the stock symbol.  The solution was simple: use the record macro function on Excel.  What I did essentially was record how I would manually change the web query and modify the code that was recorded.  It actually took about 10 minutes to make the change.  I wonder why I didn't do that earlier!

So, with all its glory, I present to you my automated Rule #1 Analysis Spreadsheet!  With this spreadsheet, you really only need to look at 3 tabs.

1. Go to the "Stock" tab.  Fill out the 3 pieces of information there.  Click on "Refresh Data" and wait for Excel to update the data from the web.
2. (Optional) Go to the "Sticker Price" tab.  Change the growth and P/E numbers if you want.
3. Go to the "Summary" tab.  Look at the results.  Voila!

The previous spreadsheets weren't bad.  One would need to spend about 2 minutes populating the data by copy and pasting.  Now, you can save about 90 seconds every time you use the sheet!

The story with Kelvin didn't just end here.  I sent the spreadsheet to my friends (thanks Jit), including Kelvin, for some real-life stress testing.  The sheet worked perfectly, for all of them...except Kelvin.  D'oh!  I use Excel 2007 and he's got 2003.  I believe that could be the root cause of the problem.  Since I don't have Excel 2003 on my computer, I couldn't do much debugging.  I've given up!

Instead, I realized that there could be potentially a better solution: I moved to Google Spreadsheets!  Now, investors, take note.  This is a classic example of how Google continues to draw user base from Microsoft, and how cloud computing is starting to take hold.  Mind you, I'm almost always an early adopter in technology...In any case, I'm now working on a Google Spreadsheets version of the Rule #1 spreadsheet.  I have it running already, but there is some fine tuning to be done.  If you want to check out the current version, it's here: http://goo.gl/goru5.  You'll need to log into your Google account and make a copy of the file in order to actually use it.

Give me some feedback, on either version of the sheet!  Enjoy!

Update December 3, 2011: I've updated the sheet.  Go to the Investing Resources to download the latest sheet.