Stock Analysis Using Google Spreadsheets

If you've been following my reading log, you may have noticed I went on a kick of stock picking, investing and the like. If you've been following the stock market, you've probably seen a lot of excitement. I've been curious about the subject ever since we played a stock market simulation in 6th grade. During our simluation the stock of some company called 'Square D' went through a hostile takeover and the price of the stock soared. Back then, we watched the prices by reading huddling around the morning newspaper!

My recent exploration of the subject has taken me mostly through books on value investing and other long-term strategies. Of all of them, I believe "Rule #1 Investing" has made the most impact on me, and it is the reason I built this fancy spreadsheet.

The approach recommended by Phil Town is to find companies that have excelled at generating sales and equity over the past 10 years, have legitamate and competent management and is trading at a large discount (compared to its 'value'). The process of evaluating a stock isn't impossible, but it is a little tedious. However, Google Spreadsheets has solved much of the tedium!

Introducing the "importHtml" function!! With it, one can easily import the data from a webpage if it's stored as a table or list format. Sites such as MSN Money and Reuters display all of the information that is needed to evaluate a company's performance and stock price according to Phil Town.

Here's a link to an example spreadsheet for Proctor and Gamble. Here's a link to a template, if you'd like to work with your own copy. Above the fold you'll find the results of the calculations and below the fold you'll find the data imported from the web. I had a geekulous time making this spreadsheet and will probably keep working on it. Load times are understandably slow, but in the end it makes quick work of data collection which is, by far, the most tedious part.

There are some irregularities; data doesn't load for all stocks and, of course, the formulae aren't perfect. The target price only makes sense if all the fundamentals are strong -- excessive penalizations are exacted on companies that have faltered even slightly. Naturally, think for yourself and read the book if you find yourself interested.

On Google's part, I think this spreadsheet really tests the limits of your browser and the software. While I love the importHtml function, it makes you a little sad that parsing html is the simplest way to gain access to freely available data. Hey, it gets the job done and that works for me!

Update: Naturally, when you're scraping data, the format is bound to change. While Google spreadsheets was an awesome and quick way to experiment, it's a bit tougher to maintain. As a result, I've cooked up another tool. Let me know if it doesn't work for you, there are definitely some rough edges. But it's a bit easier for me to maintain. Here it is: Rule One

Posted: August 11, 2011 | | comments

spinner