Example ASX stock screen and valuation

Hey guys,

Today we’re going to run through a couple of examples using my Stock Screener spreadsheet to hone in on some cheap stocks, and then my Stock Valuation spreadsheet to estimate the value of those stocks to us as investors.

At the risk of ruining the surprise, the stocks I value in this post are the following:

  • Metcash Limited (MTS);
  • St George Mining (SGQ); and
  • Crown Resort Limited (CWN).

Check out the demonstration video below to see how the three company’s performed using the Stock Screener and Stock Valuation spreadsheets.

Download the applicable spreadsheets at these links: ASX Stock Screener and Stock Valuation spreadsheet.

To hone in on cheap stocks I use the Price-EPS ratio and Price-Book Value ratio. For the former, I filter out all stocks with a ratio greater than 15. For the latter, I look for stocks with a ratio between 0.6 and 1.5. This screen yields about 150 cheap stocks, some of which worthy of further investigation. I personally like to go for large Market-Cap stocks so that is the next search criteria I use. From there, I wanted to analyse stocks not in the “Real Estate” GICS Industry Sector (personal preference really, large Market-Cap Real Estate stocks tend to dominate the low P/E universe of ASX stocks and I like to choose from the minority).

Once I’d narrowed my stock list down to MTS, SGQ and CWN, I used my Stock Valuation spreadsheet to estimate the value of these companies by calculating their systematic risk – beta, and their cost of equity via the Capital Asset Pricing Model, and finally the sum of their discounted, expected future cash flows via the Dividend Discount Model.

A note I made in the demonstration video which I’ll make again here is that my Stock Valuation spreadsheet will only tell you if the company is presently undervalued or not. There’s a bunch more research and analysis the user/investor should carry out before investing in an undervalued stock. These include the following:

  • Assessing the competency of the managers of the company;
  • Ensuring the company has long term prospects (e.g. a product which will always be in demand);
  • Ascertaining the stability and understandability of the company (look for company’s with ROE figures which consistently exceed 10% with little fluctuation).

If the above sounded familiar, you’re not crazy – I’m not trying to reinvent the wheel here. I believe these are the criteria Warren Buffett uses to select the stocks he invests in.

In addition to the above, you might also like to check the compatibility of your new stock picks with the rest of your portfolio. I’ll be soon to upload a couple of spreadsheets which will do just that. One will estimate the variance of your portfolio, and whether adding a stock adequately reduces the overall portfolio risk. The other is slightly different but more exciting in my opinion, after inputting the ticker symbols of your portfolio and clicking a few buttons, the spreadsheet will calculate and plot your portfolio’s efficient frontier. It will also calculate the optimal weights of your portfolios assets for a given target return by minimising portfolio variance.

As covered in the demonstration video, there are a couple of circumstances where my Stock Valuation spreadsheet is unsuitable for estimating the current value of a company. This was the case for St George Mining (SGQ). According to the Morningstar data available to free members, SGQ had consistently and significantly negative ROE values for the past three years. This in combination with no dividend payments and thus dividend payout ratios of zero over the same time-frame meant the company growth rate could not be calculated and the Dividend Discount Model could not be used.

During the analysis of Crown Resort Limited (CWN) a separate issue was encountered. Given CWN has only be listed on the ASX since December 2007, shortly before the GFC hit, and the sample statistics presented on the “CAPM” sheet are calculated based on common time horizons (i.e. only market data since December 2007 is included), the expected value for the market (ASX200 in this case) is negative for three out of four holding period durations. The negative expected value produces spurious cost-of-equity values when used in the Capital Asset Pricing Model, and thus the results presented on this sheet, in this instance should not be utilised. Fortunately, the results presented in the “CAPM2” sheet are sound. This is because the expected value used here is based on market returns over the life of the index (since the ASX200 began in 1992). This longer time horizon is less affected by the large, negative returns of the market in 2008 and 2009 following the GFC.

As mentioned above in the recommended additional analysis points, stability and understandability of a company is important. This is usually assessed by a fundamental analysis of the company’s financial statements. I presently have a spreadsheet in my portfolio which analyses this information but it is quite convoluted and it requires the user has access to Morningstar’s DatAnalysis service which few individual investors do. I will work on improving this spreadsheet and upload it when its ready.

As usual, let me know if you have any issues or recommendations. Happy to assist where possible.

Cheers,

Nick

ASX Stock Screener

*** Yahoo Finance discontinued their API featuring meaning this spreadsheet can no longer download stock data. Sorry guys ***

Hey guys,

Below is the link to download my ASX Stock Screener spreadsheet.

Download Spreadsheet here!

The spreadsheet allows the user to screen for ASX listed stocks based on company data obtained from Yahoo Finance using the websites API. The “tags” I used in my spreadsheet were gathered from a blog by a lad named Ido Green at the following website:

Yahoo Finance (hidden) API

More information can be found here if the user desires.

The following instructional video briefly explains how to use the Screener.

After downloading and opening the spreadsheet the user first updates the list of ASX companies using button 1 on the “Screener” sheet. This extracts a .csv file from www.asx.com [full link here] and populates the “Screener” sheet with a complete and current list of ASX listed companies along with their ticker symbols and GICS Industry Group.

Next the user has the option of downloading the pre-set array of information for each company or customising the information that will be downloaded from Yahoo Finance via the websites API. To download the pre-set information, the user simply clicks button 2 on the “Screener” sheet. Alternatively, the user can add to or delete items shown in the list on the “Tags” sheet. The full list of headings available for download via Yahoo Finance API is shown on the “Yahoo API” page. The user can simply copy the desire headings along with their tags and paste them at the bottom of the list on the “Tags” sheet. Clicking button 2 now will download the user defined headings.

The user may need to apply a filter to the top row once all downloads have completed otherwise the screener is good to go. The user is free to use all the convenient features of Excel as they wish. I have already applied some conditional formatting to the first two columns, Price-EPS ratio and Price-Book Value ratio, which help to filter out possibly overpriced stocks. The user should keep in mind that a number of columns have already been formatted based on the pre-set headings which means the user may need to re-format some columns if the list of tags is changed substantially.

I hope you find this spreadsheet useful. Let me know if you have any trouble or if you come up with any improvements I might be able to incorporate.

Happy screening!

Cheers,

Nick

%d bloggers like this: