Use access key #2 to skip to page content.

ValuePicksOnly (82.78)

Automatic value analysis spreadsheet

Recs

9

October 28, 2011 – Comments (11)

Hello everyone,

A few months ago I build an advanced spreadsheet for analyzing stocks using Google Docs. This spreadsheet retrieves data from several online sources (e.g. finance.yahoo, Google Finance), performs a DCF- and a P/E valuation, creates graphs and comes up with an analysis of the data plus a recommendation whether to buy the stock or not. But here comes the best part: 

It only requires you to input one variable: the ticker symbol!

This spreadsheet allows me to analyze 30 companies in the time it would normally take me to analyze one, and I want to share it with you. 

Click here for the Google Docs Value Investing spreadsheet 

You can only edit the first ticker sheet, the rest is locked. It is just to give you an idea and maybe inspire others. Value investing is not an exact science, so don't blindly buy a stock if 'the sheet says so'. Be sure to read the disclaimer on the final sheet.

Comments and ideas for improvement are certainly welcome!

 

p.s. this will probably get messy as multiple people fill in different tickers... hmm... exporting to Excel is not an option, as Excel does not allow the automatic harvesting of information from websites. We'll see how it goes.

11 Comments – Post Your Own

#1) On October 28, 2011 at 11:05 AM, Teacherman1 (57.57) wrote:

Interesting way to bring data together, but after a quick trial, I find myself disagreeing with about 75% of the recommendations.

It seems to be good for companies that are well established and have many years of meaningful information, so is good at determining "past" results.

However, it does not seem to be able to make very useful future valuations.

Still, it's fun to play with.

Thanks for posting this.

Report this comment
#2) On October 28, 2011 at 7:34 PM, ValuePicksOnly (82.78) wrote:

Hello Teacherman1, thank you for your comment!

It is true that the recommendations are rather conservative and might not fit everyone's investing strategy. It is based on a combination of Value Investing strategies and I have set the parameters quite strictly. It is also based on my personal investing strategy in which I do not even consider investing in companies with less than 5 years of data available. Buffett said: A startup is like swinging at a ball while it is still in the pitcher's glove.

Some principles to keep in mind when using this spreadsheet:

1. Investing is not an exact science and the sheet is not true or false, it is simply a quick screening tool to see whether it is worth your time to put more effort into further analyzing this company

2. Predictions about the future are by definition not very useful, as many unpredictable things can happen. However, as Chalie Munger said, it is better to be roughly right than precisely wrong. So the sheet analyzes whether the company has performed consistently well over the past 5 years, uses analyst predictions about the future growth, applies a margin of safety and gives a rough, conservative estimate of potential future value. This might not be entirely correct, but it is an educated guess.

3. It will be very hard to find a company which will get a BUY recommendation from this spreadsheet. This helps you live up to Buffett's first rule: never lose any money! The fact that you are an investor does not mean you have to trade a certain amount of times per quarter. Only invest in those true gems. At least, that's the school of thought I'm basing my investing decisions on ;-)

Report this comment
#3) On October 28, 2011 at 8:29 PM, Hawmps (< 20) wrote:

I love what you have done with this.  The ability to pull the data from the web by just inputing the ticker is very clever and short cuts a lot of time and manually inputing the same data (and data entry can be easily messed up with as many inputs).  I only wish you left the "discount rate" unlocked because no matter what you look at you are stuck with Yo=9%.  Then you can do a bit of sensitivity analysis and come up with a range by being able to change the rate.  Food for thought.  Great work!!

Report this comment
#4) On October 28, 2011 at 10:17 PM, CROIC (73.35) wrote:

Pretty cool.  I found 1 stock that it rated a buy - DLB.

It claims CYD is undervalued by 1631% but still not a buy. Apparently the 3.7 P/E is too high and the 5 year ROE of 17.2% is too low.

Report this comment
#5) On October 28, 2011 at 10:35 PM, Momentum21 (94.21) wrote:

nice...thanks VPO...you should set up a portfolio based on recommended tickers. It would be interesting to follow...

#4 - DLB does seem like a no brainer... : )  

Report this comment
#6) On October 29, 2011 at 8:46 AM, ValuePicksOnly (82.78) wrote:

Thank for the feedback everyone! 

@Hawmps: I have now also made the discount rate and other parameters editable. The reason I locked them was exactly because of the reason you mention: data entry can be easily messed up with many inputs, especially with a publicly shared spreadsheet like this one ;-)

@CROIC: nice tip about DLB, I'm going to look into that company some more! Regarding CYD: the sheet only gives a BUY recommendation when the most important fundamentals are cosistently high or growing and both valuation methods yield approximately the same value. However, in the case of CYD the P/E method yields a value of $0,43 while the DCF method yields a value of $525. This makes the valuation completely unreliable and useless. Therefore no buy recommendation is given.
The current P/E ratio is compared to the median P/E ratio of the past 5 years, which is 0.1 (should already be an indication that something is off). So yeah, a P/E of 3.7 is high relative to the P/E ratio's of CYD in the past. 
ROE is considered to be good if it has been above 15% in each of the past 5 years. In the case of CYD, ROE levels went as low as 4.1 in 2006. So although the ROE of this year is high enough, it has not been consistently high over the past 5 years and so no buy recommendation is given. 

@Momentum21: Good idea, I might do that! I could even include links to PDF exports of the spreadsheet analysis which led me to including the company in the portfolio :-)

Report this comment
#7) On October 30, 2011 at 8:53 PM, CROIC (73.35) wrote:

"The current P/E ratio is compared to the median P/E ratio of the past 5 years, which is 0.1 (should already be an indication that something is off)."

Yes, of course something is off.  CYD's median P/E over the last 5 years is roughly 7.5.  Maybe your data source has a currency based error.

http://finapps.forbes.com/finapps/jsp/finance/compinfo/Ratios.jsp?tkr=cyd

Report this comment
#8) On October 31, 2011 at 12:32 PM, ValuePicksOnly (82.78) wrote:

This is my source:

http://moneycentral.msn.com/investor/invsub/results/compare.asp?Page=TenYearSummary&symbol=US%3aCYD

Not sure why MSN Moneycentral has incorrect data on this company, but you might be right that it has something to do with currency.

About the source you provided: you cannot tell from the data on that page what the median P/E ratio is. Median is not the same as average. I decided to use the median because it is not influenced too much by extreme events.

Report this comment
#9) On October 31, 2011 at 9:33 PM, Hawmps (< 20) wrote:

Played with your "toy" again.  Still very cool.  I really like the way you use two different approaches to value the asset (P/E & DCF).  And then there is a consensus between the two showing the reliability of the valuation.  Also, very cool. 

Most people don't understand that valuation (of any type of asset) is a science and an art.  Your model really nails down the science part very well and does the most you could possibly do with the data available from the public filings and historical pricing.  The art part of the equation is, taking what is being spit out through the "plug-n-chug" of the numbers crunching and interpreting these conclusions and putting them into context of the specific asset you trying to value.  What are the numbers telling you?  I think this is where some of the other comments you have here are not exactly connecting all the dots.  You can't just rely on numerical answers, you must insert judgment, like, why is there such a gap between the DCF and the P/E valuation?  There is a reason the two are so different and it could just be that the market is willing to accept a lower rate of return than you are. Or, the market does not truly realize the value of the asset you are looking at.  IMO, if there is such a disparity between the two, we should try to find out why.  The market could be missing something and you could be looking at something truly undervalued.  Also, there could be some really big swings in prior performance and parts of the past data may not be reflecting what is happening now.

Thanks for the hard work.  I have this bookmarked and will be playing with it quite a bit.  Oh, and thanks for unlocking the discount rate.  ;) 

 

Report this comment
#10) On November 01, 2011 at 12:30 AM, spreadsheetV1 (98.82) wrote:

I created one of these years ago to handle hundreds of tickers (if desired) using excel +program but have yet to port it to the web for easy access.  I still plan to do that eventually...

Greatminds think alike. 

=) 

Report this comment
#11) On November 01, 2011 at 3:26 PM, MrPecuniam (< 20) wrote:

Nice sheet gonna check it out some more.

 

Is there any way to save a copy for myself so I can do some tweaking of my own? 

Report this comment

Featured Broker Partners


Advertisement