Excel Tool - Company Analysis with SMF addin

[Ross812]

Hi all,

 

I saw a few members were using Google docs to keep track of positions and run some basic quantitative analysis. Here is a spreadsheet I created a while ago to help quantitatively evaluate companies.

 

*NOTE: Be sure to install the included Stock Market Functions (SMF) Plugin before using the spreadsheet. Instructions are in the zip file.

 

In order use the first two tabs (Main and List), you need to cut and paste a list of ticker symbols to column A on the ‘List’ Tab. Once this has been accomplished you can go to the first tab ‘Main’ and press ‘Scan List’. This will pull data and update the data in columns B-O on the ‘List’ Tab.  I have used these two tabs a lot to help find companies to research more deeply.

 

 

The third tab ‘Summary and Rating’ pulls the most recent rating information from various sources.

 

 

The fourth tab is ‘Balance Sheet’. This imports all the most recent financial information for an entered ticker symbol. I added a few indicators to the right of the financial information: Piotroski F-Score, Altman Z-Score, Steve Szirom’s Red Flag Indicators, and some basic fundamental ratios all calculated from the financials.

 

 

The last tab is a graphing tab which automatically graphs up to 10 years of data and 2 years of estimates for an entered ticker symbol. The graphs are:  Actual vs. Estimated Earnings, P/E ratio, EPS, P/S Ratio, Revenue, P/BV, P/TBV, Gross Profit, Equity, Operating Margin, and FCF. This is pretty useful when checking for trends.

 

 

I haven’t updated or enhanced the spreadsheet in a couple years. I know there are other graphs, ratios, etc that would be helpful. I am planning on getting it to run with some Morningstar premium data in the near future. All I ask is if you further develop the spreadsheet, share it with the group.

 

Regards,

 

-Ross 

Company_Evaluator-v0.5All_Working.xlsx

SMF_Add-In.zip

[Parsad]

Hi Ross,

 

Great idea and looking spreadsheet, but I can't get it to work properly.  When I enter the ticker symbols and then go to Main and click "Scan List", it just gives me errors.  Anyone having problems?  Cheers!

[Ross812]

Parsad,

 

I'll take a look at it tonight. It was working on my computer, but I had to include a new version of the SMF plugin because I couldn't find the version I had. I'll let you know what I find.

[Parsad]

Ok, thanks Ross!  Seriously great looking spreadsheet when working.  Cheers!

[compoundinglife]

It has been a while since I used that Excel plugin but I have used it with the Old School Value spreadsheet, and from what I remember you had to go into the plugins section in Excel and check a box to enable it. There is a video that may help:

 

http://www.youtube.com/watch?v=BbW9-HanvzY

 

 

[zarley]

Thanks for sharing Ross; obviously a lot of work went into that. 

 

In the past when I've done that sort of thing I find that the format or cell locations on data imports changes from time to time.  Meaning I have to readjust all the cell references to get the calcs and charts to work out right.

 

Have you had that problem, and if so, how did you overcome it?

[Ross812]

First off, I'm sorry it's not working. It is working on my laptop with Excel 2003. I tried to install it using the instructions in the zip on my desktop using Excel 2010 and I can't get the darn add in to work. I'll get it fixed and let you know how I got the add in to work.

 

Zarley,

 

I set up the spreadsheet so every cell only pulls one data point, meaning it doesn't just insert the financial data using a csv format to import the table at once. Each cell grabbing it's own data takes a little longer (less than 10 seconds) but avoids the problem of varying table formats. The only problem I have had with SMF is data sources changing which takes a little bit of work to update, but its usually just one source.

 

 

[Phaceliacapital]

If it is not fixed untill the weekend I will take a closer look at it. At home I have a spreadsheet with the SMF addin working on Excel 2007/2010 so it probably wont be very hard to deduce the problem. Also, the community on yahoo groups is very responsive when you have questions/problems.

 

BTW: I cant download your file on my work desktop but it looks like you are using Google Finance, ever considered advfn as source? It provides 10 year annual and I think around 2.5 years quarterly data.

[Ross812]

Progress! The SMF element number for company name (13862) is not working correctly. I switched the company name to the entered symbol and it started working. So far I have the Graphs, Scan List sheets, Financials and Summary and Rating tabs working correctly.The updated spreadsheet has been re-uploaded on the original post.

 

As far as getting the sheet to work, the SMF addin needs to be re-referenced if you getting are errors with the new spreadsheet. To do this:

 

 

Click ‘Options…’

 

 

Select ‘Enable this content’ and click ‘OK’

 

 

Click ‘OK’

 

 

Click ‘Edit Links…’

 

 

Highlight the Source with an error and click ‘Change Source…’

 

 

Navigate to the RCH_Stock_Market_Functions.xla and double click. This will fix the broken source (if it changed from the assumed source in the spreadsheet).

 

Let me know if it's working.

 

Edited: to reflect that the spreadsheet is now working.

 

[Ross812]

All tabs in the Excel sheet now work! I edited the original post to include the new working spreadsheet. Download it there. This was tested as working on a new computer with Excel 2007.

[Ross812]

I've been doing some more work on the graphing tab in the spread sheet. I've started to add box plots to compare growth metrics for the input ticker.

 

So far I have:

1.  EPS & P/E Ratio

2.  Revenue

3.  Net Income

4.  BVPS & P/BV

5.  TBVPS & P/TBV

 

I am planning on adding Operating CF, Free CF, and Basic/Diluted Shares Outstanding graphs as well. I need some ideas to come up with other good metrics. We can add just about anything, even calculated metrics from the balance sheet. Does anyone have any ideas or metrics they would like to see?

 

Here is what the new format looks like so far. Let me know if you have any formatting recommendations to make it easier to read, or if something doesn't make sense. Graphs for 3M:

 

[Ross812]

I completed a more complete set of graphs to be used with the SMF add on. There are now 10 years of annual data, or five years of quarterly data for the following metrics:

 

-EPS

-P/E

-EPS growth box plot

-Revenue

-Revenue growth box plot

-Net Income

-Net Income growth Box plot

-Profit margin

-Book Value per Share

-BVpS growth Box plot

-Tangible BVpS

-Tangible BVpS growth Box plot

-Cash Flow

-Free Cash Flow

-Cash Flow growth box plot

-P/CF

-P/FCF

-Diluted Share Count

-Basic Share Count

-Employee Stock Options + Convertible Count

-Share count growth box plot

-Various Management Effectiveness ratios (ROA, ROI, ROE)

-Various Liquidity Ratios

-Various Solvency Ratios

 

The new file is attached. It needs to be used with the SMF add in from the first post in this thread.

CE_Graph_Pack-v0.2_-_Copy.xlsx

[Ross812]

Preview: