Instablogs are Seeking Alpha's free blogging platform customized for finance, with instant set up and exposure to millions of readers interested in the financial markets. Publish your own instablog in minutes.
-
Instablogged Stocks
Stocks that instabloggers have most recently written about -
Latest Instablog Posts
-
1
Choose The Best Coaching For Engineering Exams
-
2
Best Yields With Ex-Dividend Day Between May...
-
3
17 High Yields With Additional Potential To ...
-
4
U.S.A. Becoming A Banana Republic
-
5
Where Is Cliffs Natural Resources Inc. (CLF)...
-
1
-
Top Instablogs
See all Top Instablogs »
Automating Alpha: Getting Started With Stock Market Functions (SMF) Add-In For Excel 37 comments
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 (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
(click to enlarge)
(click to enlarge)
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.
(click to enlarge)
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)
(click to enlarge)
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
(click to enlarge)
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)
(click to enlarge)
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!
(click to enlarge)
2. Now, locate the 5 year dividend growth rate indicator from MSN
(click to enlarge)
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)
(click to enlarge)
4. If everything worked correctly, you should now see the cell B2 populate with a number.
(click to enlarge)
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):
(click to enlarge)
How do we repeat this process for Coca-Cola (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
(click to enlarge)
2. Paste into row 3
(click to enlarge)
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.
(click to enlarge)
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:
(click to enlarge)
Disclosure: I am long KO, MSFT.
Instablogs are blogs which are instantly set up and networked within the Seeking Alpha community. Instablog posts are not selected, edited or screened by Seeking Alpha editors, in contrast to contributors' articles.
Share this Instablog
This post has 37 comments:
Ron Spruell
Thanks for the comment. Can you maybe send an example over of something you've had to adjust in IE to get it to work? I've never really had to adjust IE for the Add-in, I always thought the tool was agnostic to IE since it links directly from Excel and to the data providers.
Would be curious to see in case it's something that needs addressed.
Derek
Some people have had to change the cache setting in IE to avoid getting stale, previously retrieved data. The FAQ below from the SMF group describes the setting and reason.
http://bit.ly/13yf2Oz
I'll just add that Randy is fantastic about responding to questions and problems. If you ever have any issues just post a message in the group and you'll get it resolved.
Gary Hartling
Would you mind telling us the required IE setting changes required to make the smf add-in work on Windows 7?
This would be of great help for many of us still struggling to make it work.
Thank you in advance,
Chuck Amlinger
Have you tried downloading the newest add-in? Sometimes the data providers update their layout, and so you might need to get a new version of the add-in. A new version came out late December so that might solve alot of the problems, particularly with MSN.
From the SMF message board, the IE setting needing to be changed are:
In Internet Explorer : Tools, Internet Options, Security, Custom Level button (for the zone that's currently highlighted); Find the 'Access Data Sources Across Domains' option and set to 'Enable'. Click OK to save the settings change.
This was useful for getting advfn data from outside the US (in my case Australia)
(thanks to laveys for providing the solution)
Miron
I typically use the tool when I know the symbols, but I believe others have been using it the way you describe, as a filter.
In the meantime (you may already be using it), FinViz has a nice screener, I was able to locate all of Arie's metrics except for the Enterprise Value < Market Cap calculation:
http://bit.ly/10EE80m
I did some digging but was not able to find any example templates that work the way Goren's screen would, by entering in the parameters and then producing the stock symbols.
Another idea you may try if Finviz doesn't work out is to post to the SMF Yahoo group with what you're looking for, they probably have a ton of ideas for you to go through.
Bob
35 Yr HS MATH teacher LAUSD
New to finance and stocks
Using Merrill Edge
Just installed the ADD-IN to my 2007 Excel
Following Cramer on MAD MONEY
Using Yahoo Finance Portfolio to get a feeling for stocks that have caught my attention
Love using spreadsheets for everything...this add-in is remarkable...great job for the author!..Professor Randy Harmelink
I am learning something new everyday and this add-in will accelerate acquiring new understanding....(poss... meteorically)
Thank you Randy!
1. I use David Fish's CCC files that he updates each month. Tell me please how I (a blue chip dividend investor and index fund investor) would benefit from the SMF add-in. How is the SMF add-in superior to D.Fish's file? I'm curious whether I would create a data overload for myself by going all the trouble of installing the SMF & making sure if works properly instead of spending my limited free time for something more relaxing (like hiking with my small kids or just reading a book). I don't trade or check my investments every week or month either. Is this SMF add-in more for people who actively monitor their stocks?
2. Would this SMF add-in be useful in any way for fund monitoring?
3. The file uses MSN as the main data source. What if MSN discontinues this service? Do we need to wait for an updated file then?
4. If the author stops updating this FREE file, then it's all for naught using the file going forward (e.g. MSN service stops or other glitches develop, etc.).
I don't mean to be critical at all, but it really shows how much work and committment this project entails. And it definitely shows that a person can learn much more about Excel working with this file. I just basically wonder how a person like me would benefit from this. My contributions to dividend stocks and/or idx funds are automated, I follow news about my held stocks and stop investing when overpriced, but I don't check their quantitative data (P/E, payout rations, etc.) every day/week. It suffices quarterly or semi-annually and yearly by using D.Fish's file.
Thanks
You aren't being critical at all. The questions you are asking are good ones.
1) For me personally, I use the add-in to monitor my existing stock holdings, or for running due diligence on a potential buy. So I have created a bunch of columns with my favorite indicators, like PEG, Payout Ratio, Percentage over Graham Number, etc. I put my current holdings into the spreadsheet and using color coding, I can see if they are undervalued, fair valued, or overvalued. I rank my current holdings and will only add to them when a stock is undervalued.
So that's how I use it. I use the same criteria for evaluating a new stock. So I will plug the stock ticker into the spreadsheet and see if the stock meets my screening requirements. For me this takes a TREMENDOUS amount of guesswork out of picking stocks and makes it very systematic, and I can make a decision of whether to consider a stock within 1 minute.
Regarding David Fish's spreadsheet, nothing will replace that. It's still the best starting point for finding dividend blue chips on the planet. My spreadsheet complements his, because I have personalized my spreadsheet to meet my own personal criteria. My spreadsheet is the product of years of work of figuring out my own personality and what strategies work for it.
2) I have not personally used it for that, but pretty much any data that is being displayed in any of the data services like MSN or Yahoo, should be able to be scraped by tool to the spreadsheet. I don't buy too many funds nowadays so don't really screen them. I prefer stocks when I can to avoid fees.
3) The tool actually uses multiple data sources. I use MSN to pull most of my data since I invest in Microsoft, and like to use products of companies I am invested in. (I buy Chevron gas for example, or contact lens solutions from Abbott Labs). That's a type of investing energy I learned from mbkelly, a master on these forums, who says he maintains a 9 figure portfolio. So you can also pull data from Yahoo and Morningstar, but there are literally thousands of indicators from dozens of data providers.
4) That depends. The tool has multiple ways of pulling data. There is a pre-populated method where the author has done all the legwork for us. So all you have to do is put in the number of the indicator you want, and it will go out and grab it. Data providers periodically change their webpages, so these have to be updated. If the author no longer supports that, and nobody else picks it up, you do run the risk of no longer having the tool work.
The more advanced, more manual method should always work, since it is going straight to the webpage and is using HTML tags to pull the actual data.
I'd say in your case if you are happy with your system, you may not see as much value out of it. For me I have a preset list of indicators for getting into a stock, and this tool has been invaluable for when to do that, so I check it a couple of times per month.
It's typically a lot of work upfront but saves exponential amount of work over time. Once I got it going it saved hundreds of hours of time, leaving me free to focus on things like company news or qualitative analysis.
Derek
To give you an idea of what is possible, check out the link below. It's a pdf print of one of my analysis spreadsheets. All I do is enter the Ticker symbol, press enter and voila - all the rest happens automatically. I prefer charts to lists of data - as you'll see.
http://bit.ly/Y2vC5H
To address your concern about what happens if SMF quits working, well, any vendor may choose to discontinue their product - free or not. So, that is not unique to SMF. If you do wish or need to switch, there are paid products which provide similar functionality and Excel has its own built-in method for doing it (although it's clumsy).
Thank you.
To remove it, you'll need to first remove it from the Excel Add-ins menu, then all you need to do from there is simply remove the C:\SMF folder from Windows.
thanks for the article..i built a spreadsheet using this add-in a couple months ago after reading your article, to quickly value numerous companies at once in just the way i like to do it. The thing I like about this solution is that in the past I wouldnt bother to analyse a company properly if i thought I didnt have the time, i was relying more on tips, flying blind, this is a big help for me to be a better investor. In other words I agree with your last paragraph.
Nice article.
Could you elaborate on what is the "smfUpdateDownloadTable" and could you give an example. I seem to have alot of trouble downloading from msn, it a 50/50 hit or mis when using msn.
Thanks,
"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."
Let me take a look and see if I can get you a good example, if I can't answer it in the comments section I'll do another quick blog with screenshots. Will take a look this weekend and see what I can come up with.
Thanks for the comment.
"Yep. Ever since MSN redesigned their print page last year, they've been defining "Last Price" as the previous day's closing price.
For current quotes, I'd suggest using the RCHGetYahooQuotes() function. It can get multiple data items on up to 200 different stocks with a single Internet request, so it can be much quicker than doing it one web page at a time with RCHGetElementNumber() or RCHGetTableCell()."
I just published a quick tutorial on using the smfUpdateDownloadTable.
I found it pretty easy to use and liked the control over when I update the workbook.
It's pretty cool because you don't have to wait for the workbook to update when you first open it. You have to force it to refresh once its loaded, and it also gives you a status with how its doing:
http://seekingalpha.co...
Great job this is a fantastic tool, I had another method which pulls data from yahoo exclusively but requires a lot of coding using VBA which is more difficult than this preset tool.
I do have a question regarding getting the current price
"For current quotes, I'd suggest using the RCHGetYahooQuotes() function"
Can you please elaborate on this a bit more?
Kindly,
George
But, the best part of it is that if you enter it as an array formula (Ctrl-Shift-Enter) for a series of stock tickers, it will retrieve all the data in a single internet access - very quick.
See its doc on the SMF group page:
http://bit.ly/10kCpH2
George,
Take a look at No Free Cake's post and his link, and if you still have questions feel free to post here as well and we can get them figured out.
thanks,
I downloaded and am using the smf-addin, the rchgetelementnumber for whatever reason has trouble downloading with the msn element numbers, and are a big hit and miss, many times I just get an errror, so the download table sounds more reliable for me.
Again, thanks.
One quick thing to start with also is the newest version of the add-in, looks like a new version was updated on March 29, 2013, this is under the Files -> Works-In-Progress section, sometimes that will solve problems.
Last year when MSN updated a lot of their pages a lot of the elements didn't show up, until the addin was updated.
RCH_Stock_Market_Funct...
Taking a look at smfUpdateDownloadTable to get something going.
Look for the article in the next couple of days.
SMF has vastly improved my monitoring and analysis. I'm surprised I don't see it mentioned on S/A more often.
I agree that it's a great tool and a huge time saver and improves analysis. I use it as a dashboard and can very quickly within seconds make a decision based on visual cues that would have taken me much longer in the past, since all the information consolidated is right there in front and color coded for further diligence.
You definitely have a generous heart, offering and then following up with the execution help to others. I see already others have benefited from you advise. May many blessing come back to you.
keep up the great work.
I am new to investing in stocks, I have lost more than I have won. I am going to keep trying until I get it right.
Thanks, a $ million. :+}
At least that's mine an dyour goal $$$.
"I am going to keep trying until I get it right."
Then you have already decided to succeed.
Victory isn't a question, it's a DECISION, and something you have embraced. So now it's only matter of time before you get to where you want to be.
Before I got on this site I was just blindly picking stocks and hoping I got them at the right time and got it right.
But I have spent the past few years spending hours every night reading everything I could get my hands on. I tried all kinds of different styles and experimented until I found something that works for me.
I am really happy where I am at, but still wanting to learn more every day.
Keep up the positive attitidue and thanks again for the compliments.
Your a good man.
The world would be a better place if we had more people like you.
The world needs more people like yourself.
My motto is do the right thing, it will come back to you. Helping other people is not a bad behavior.
Keep it up...I too will try to help others...
Latest Followers
StockTalks
-
Sold $NBG June puts at 3
May 17, 2013
-
Sold $DOV after reaching Graham 50% price target. Still holding in 401k.
May 17, 2013
-
$YUM: Talk about BRAND POWER. People smuggling KFC under the Egyptian border! http://bit.ly/19ztoyZ
May 16, 2013
More »Latest Comments
Most Commented
Posts by Themes