Jump to content

Excel Tool - Company Analysis with SMF addin


Ross812
 Share

Recommended Posts

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.

 

Listview.jpg

main.jpg

 

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

 

sum+and+rating.jpg

 

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.

 

ballance+sheet.jpg

 

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.

 

graphs1.jpg

graphs2.jpg

 

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

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

 

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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:

 

Untitled.jpg

 

Click ‘Options…’

 

enable.jpg

 

Select ‘Enable this content’ and click ‘OK’

 

3.jpg

 

Click ‘OK’

 

4.jpg

 

Click ‘Edit Links…’

 

5.jpg

 

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

 

6.jpg

 

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.

 

Link to comment
Share on other sites

  • 2 weeks later...

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:

 

eps.jpg

net.jpg

rev.jpg

bv.jpg

tbv.jpg

Link to comment
Share on other sites

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

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...