Please Note: Blog posts are not selected, edited or screened by Seeking Alpha editors.

Automating Alpha: Getting Started With Stock Market Functions (SMF) Add-In For Excel

Analysis is typically divided into two categories - quantitative, the use of numbers to form conclusions, and qualitative, which relies more on subjective interpretation of written or verbal (narrative) data. Often it is said that quantitative analysis relies on the left brain (structured), while qualitative is a right-brained (creative) approach.

You may have heard the term "quants" used to describe Wall Street traders who rely on very sophisticated numerical algorithms, to move in and out of their trades. The MOST sophisticated out of all these quants, are the High Frequency Trading (HFT) robots, which some have estimated account for 40-70% of all trading volume in a given day.

Why Automate?

Although most of us, even independent investment advisors, do not have the billions to create and access these trading platforms, we should not fear them, and instead can emulate the idea of robots to automate the quantitative number crunching, and leave our most valuable resource - our brains - free to do other things, like qualitative analysis.

Automating your screens with pre-defined metrics to create a working system takes the guess work out of making decisions on the numbers, because once you define your criteria, you are then free to focus on subjective data like news alerts, special situations, management guidance, and understanding the underlying business.

Seeking Alpha Authors Already Automate

Here on Seeking Alpha, J.D. Welch has created a "Mad Method" for choosing stocks, and most recently, Arie Goren shows a great starting point for a Growth at a Reasonable Price screen. Both of these systems would work well with the SMF add-in, for measuring existing holdings, or for running due diligence on a prospective new addition.

The Mad Method in action:

Arie Goren's screen :

Enter The Stock Market Functions (NYSE:SMF) add-in!

Now that we understand WHY we should automate, let's look at a fantastic free tool that can make that happen. Stock Market Functions (SMF) add-in is a Microsoft Excel based add-in, written and maintained by Randy Harmelink, that will pull free end of day data from dozens of providers, among them MSN, Yahoo, Morningstar, Google, and Zacks. Why pay hundreds or even thousands for programs when you may already have a platform that can pull over 14,000 data points, and do very sophisticated number crunching.

Requirements

1. The SMF add-in works on the Windows platform. Currently no Mac or Open Office compatibility.

2. It helps to have some basic knowledge of Excel and especially typing in formulas

3. A basic working knowledge of Windows file systems is also very helpful

4. Finally, you'll need to sign up for the SMF add-in Yahoo group in order to get the SMF add-in files.

Personal Experience with Requirements

The SMF add-in is maintained on Vista with Excel 2007; however I have personally used it on Windows XP through Windows 8, with no issues.

I have also worked on all versions of Excel from 2000 through 2010 with no issues either. I have found that the newer the Excel version, the easier it is to get things done.

The example in this article was created with Excel 2010, on both Windows 7 and Windows 8.

Now let's get into it and get some numbers!

Creating the SMF folder

Create a blank folder in your C:\ drive called C:\SMF

smf.jpg

Download and extract the contents of the .zip file to the C:\SMF folder

Go to the Yahoo SMF add-in group, and locate the Add-in .zip file. Remember you need to have joined the Yahoo group to get access to this menu.

NOTE: You can also follow this direct link once you have registered and been granted access to the group.

1. Go to Files -> Add-in Files and then you will see the file:

RCH_Stock_Market_Functions-2.1.2012.12.29.zip

2. Once you have located the file, download it to your C:\SMF folder and then unzip it. The results of the unzipped file should look similar to the list of files below.

NOTE: Don't worry right now about what all the files do; we will go into that later.

Enable the add-in for Excel through the Add-ins menu

1. In Excel 2010, go to File menu, then Options, and then the Add-ins menu on the left side.

2. From the Add-ins menu, locate the drop box at the bottom of the page --> "Manage: Excel Add-ins"

3. Then click the "Go... button"

NOTE: As an alternative, to the steps listed above, to get the 2nd screen below, you can also hold Alt, then type t and then type i (ALT+t+i)

4. Click the Browse button and go the SMF folder you had previously created

5. Double-click the RCH_Stock_Market_Functions.xla file and then Click OK

Let's format the spreadsheet and pick our indicators.

Since dividend growth investing is a popular style, let's go with a typical Dividend Growth set of indicators.

1. Open a new Excel spreadsheet

2. Enter the following headings in the Excel cells

A1 --> SYMBOL
B1 --> DIVIDEND GROWTH RATE (5-Year Annualized)
C1 --> YIELD
D1 --> PAYOUT RATIO (<.50)
E1 --> DEBT/EQUITY (<1)
F1 --> Current P/E (Below 15)

NOTE: Don't worry too much right now about the notes in parenthesis; we will use those in later articles in this series.

Locate Our Desired Indicator in the Definitions File

We want to find 5-year dividend growth, and it looks like MSN has it.

1. Open up the indicators formula list file, RCHGetElementNumber-Element-Definitions.xls, from the C:\SMF folder that we previously created.

NOTE: We will use this Definitions File as a reference to populate our Dividend Growth spreadsheet, so do not edit this file!

2. Now, locate the 5 year dividend growth rate indicator from MSN

Enter the formula to pull data from MSN

1. Go back to your Dividend Growth spreadsheet

2. Type in the ticker (JNJ in this case) into cell A2

3. Then type the formula for 5-year Dividend Growth rate into cell B2:

=RCHGetElementNumber(A2, 63)

4. If everything worked correctly, you should now see the cell B2 populate with a number.

If your cell has been successfully populated, you have now mastered the foundation of SMF add-in!

Complete the Dividend Growth screen

1. Populate the rest of the cells with the following formulas:

C2 --> =RCHGetElementNumber(A2, 13666)
D2 --> =RCHGetElementNumber(A2, 47)
E2 --> =RCHGetElementNumber(A2, 42)
F2 --> =RCHGetElementNumber(A2, 54)
G2 --> =RCHGetElementNumber(A2, 878)

2. Your finished row should look similar to the following screen (as of 12/28/2012):

How do we repeat this process for Coca-Cola (NYSE:KO)? Very easily.

Copy and paste the entire 2nd row into the third row, and just change the ticker in cell A3 from JNJ to KO.

1. Copy row 2

2. Paste into row 3

3. Change the A3 cell ticker from JNJ to KO. You should see the numbers change after a few seconds to reflect Coke's values.

NOTE: Advanced users with many indicators may want to use smfUpdateDownloadTable process for optimized performance. Users can find more information in the Yahoo SMF add-in group, and we'll also take a closer look in an upcoming article.

Even More Functionality

The RCHGetElementNumber is only ONE of the many functions, and was illustrated in this article since it is simple to get up and running. The SMF add-in has numerous functions available, which can pull data from pretty much most webpages.

More functions can be found in Files area, under documentation

Conclusion

The SMF add-in has over 14,000 indicators, including historical data and every financial ratio you can think of, and probably many you never knew were available for use!

By using this free tool, along with free data providers, you can unlock your hidden potential to create very easy to understand systems and take the guesswork out of investing.

Big thanks to Randy Harmelink for his timeless dedication and efforts writing this fantastic free tool.

NOTE: Since it is a free tool, users often ask if it is possible to donate to the Stock Market Functions (SMF) add-in, and while the author certainly appreciates the suggestion, he would rather users "pay it forward" by using the money for their favorite charity instead.

Problems?

The SMF add-in is mature and stress-tested; however, if you are having trouble and need additional help, the best place to check is with the great troubleshooting documents already written, as many users have probably already encountered your issue and there will be a resolution already created for it.

You can find these help documents in the Links section of the Yahoo group:

Disclosure: I am long KO, MSFT.