Seeking Alpha

Derek A. Barrett's  Instablog

Derek A. Barrett
Send Message
I buy established, good companies with strong management, solid balance sheets, free cash flow, growing earnings, and increasing dividends. This is a long strategy, which buys value situations, combining the fundamentals of Growth at a Reasonable Price, with Dividend Growth Investing. This style... More
My blog:
Fully Informed by Teddi Knight
My book:
Generate Thousands by Dr. Samir Elias
  • Automating Alpha: Getting Started With Stock Market Functions (SMF) Add-In For Excel 54 comments
    Jan 2, 2013 12:59 PM

    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.


    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


    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:

    (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


    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.


    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.

Back To Derek A. Barrett's Instablog HomePage »

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.

Comments (54)
Track new comments
  • You don't mention all the settings that have to be made in IE in order to get the addin to work.


    Ron Spruell
    3 Jan 2013, 08:34 AM Reply Like
  • Author’s reply » Hi Ron!


    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.


    3 Jan 2013, 02:10 PM Reply Like
  • SMF uses the IE "engine" to retrieve data from the internet. This is the same service that Excel uses for its (clumsy) retrievals.


    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.



    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.
    8 May 2013, 09:35 AM Reply Like
  • Excellent article! I've been using Randy's add-in for many years, having to increase my skill in Excel because of it. I wouldn't know what to do without it. It also works with Excel 2013, which you can currently download in Trial mode.


    Gary Hartling
    3 Jan 2013, 09:10 AM Reply Like
  • Author’s reply » Thanks Gary! Glad to see it's still going strong in Excel 2013. I should check that trial out.
    3 Jan 2013, 02:10 PM Reply Like
  • Ron,
    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
    3 Jan 2013, 05:32 PM Reply Like
  • Author’s reply » Hi Chuck,


    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.
    4 Jan 2013, 02:28 AM Reply Like
  • Derek, Ron, Chuck,


    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)


    5 Feb 2013, 02:46 PM Reply Like
  • Author’s reply » Awesome, thanks for that tip, it will benefit users tremendously
    5 Feb 2013, 03:39 PM Reply Like
  • Hi Derek, thanks for your article. I was able to get the add in and your example running (Win7/Excel 2010), but your example specifies the stock symbol and returns the parameters, however Goren's screen specifies the parameters and returns the stock symbol. How do we get the Goren screen to work?
    18 Jan 2013, 11:20 AM Reply Like
  • Author’s reply » Nice, let me see if I can find an example for a screen.


    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:

    18 Jan 2013, 11:31 AM Reply Like
  • Author’s reply » Hey rew222,


    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.
    31 Jan 2013, 05:58 AM Reply Like
  • Thanks Derek.


    31 Jan 2013, 10:26 AM Reply Like
  • Retired 5-5-12
    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!
    25 Feb 2013, 07:10 AM Reply Like
  • Author’s reply » Very cool Dfadler, glad you are liking it. I was just up in Chinatown yesterday, we passed by Castelar School, you might be familiar with it if you were LAUSD.
    25 Feb 2013, 09:53 AM Reply Like
  • I just discovered this elaborate instruction for downloading the SMF file. It sounds that even I (not great with PC or programs) could understand and might give it a try. You've done a wonderful just with the instructions and also big thanks for the author of the SMF file. However, I've got a few questions:


    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.


    5 Apr 2013, 09:09 AM Reply Like
  • Author’s reply » Hey Aida,


    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.


    6 Apr 2013, 03:03 PM Reply Like
  • aida, I've been using SMF for several years. It's tremendously improved my analysis and monitoring of my holdings. Now that I've gotten my sheets setup, I can do in seconds what otherwise took me hours and in some cases couldn't do at all without SMF.


    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.



    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).
    8 May 2013, 09:23 AM Reply Like
  • Thank you, Derek, for your long and helpful response. I'll bookmark your article for the future reference. First I must complete my expense tracking file and then start reviewing our family's investments. I might enjoy the chalenge of installing SMF and see it I could make it work for my puposes. In case I don't want to use SMF after installing it, will uninstall command will remove all the files/cookies imbedded in the SMF file? I attempt to minimize the stuff I keep on my PC.


    Thank you.
    8 Apr 2013, 08:58 AM Reply Like
  • Author’s reply » Hi Aida,


    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.
    12 Apr 2013, 09:04 AM Reply Like
  • Derek
    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.
    8 Apr 2013, 09:16 AM Reply Like
  • Author’s reply » Very cool, glad you are getting great use out of it Freddy, this thing has saved me tons of time and given me a more systematic view of my investments (meaning taking a lot of the emotion out of it).
    12 Apr 2013, 09:05 AM Reply Like
  • Hello Derek,


    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.




    "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."
    30 Apr 2013, 11:07 AM Reply Like
  • Author’s reply » Hi rr76012,


    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.
    30 Apr 2013, 03:59 PM Reply Like
  • Author’s reply » Here's another post from Randy on performance and MSN:


    "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()."
    5 May 2013, 06:14 PM Reply Like
  • Author’s reply » Hi rr76012,


    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:

    8 May 2013, 03:37 AM Reply Like
  • Hi Derek,
    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?


    9 May 2013, 01:14 AM Reply Like
  • Perhaps Derek will chime in too but the RCHGetYahooQuotes function is great. It retrieves the latest price and/or any combination of another ~75 other items with one simple formula.


    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:
    9 May 2013, 09:12 AM Reply Like
  • Author’s reply » No Free Cake you explained it very well.




    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.
    9 May 2013, 05:49 PM Reply Like
  • Derek,




    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.
    2 May 2013, 12:17 PM Reply Like
  • Author’s reply » Hi rr76012,


    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.




    Taking a look at smfUpdateDownloadTable to get something going.
    5 May 2013, 06:10 PM Reply Like
  • Author’s reply » I completed an article on smfUpdateDownloadTable, it's pretty slick and I like this macro a lot.


    Look for the article in the next couple of days.
    5 May 2013, 08:34 PM Reply Like
  • Derek, great article as well as your followup about smfUpdateDownloadTable. I think the UpdateDownloadTable is a fairly advanced function within SMF and one that needs a little more effort to understand. You did a real nice job with both articles. Thanks.


    SMF has vastly improved my monitoring and analysis. I'm surprised I don't see it mentioned on S/A more often.
    8 May 2013, 09:26 AM Reply Like
  • Author’s reply » Thanks No Free Cake for all of your comments here, and for helping out the other users with links and settings as well !


    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.
    8 May 2013, 02:01 PM Reply Like
  • Derek,


    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 $$$.
    9 May 2013, 04:35 PM Reply Like
  • Author’s reply » Thanks for the nice words! Randy deserves big credit, I try and give back what I can, it's crazy how much of a machine he is with answering questions, hopefully these tutorials will offload some of the questions from him.


    "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.
    9 May 2013, 05:47 PM Reply Like
  • Derek,


    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...
    10 May 2013, 09:22 AM Reply Like
  • I've finally downloaded the file and thanks to your detailed instructions I've accomplished extracting your indicated metrics. Now I'm curious what other things would be meaningful for a dividend investor to add to my new spreadsheet? I'm not using it (yet), need to play around.
    However, I should say that it has just pulled EMR's sector incorrectly, IMO. It says "Technology". I anticipated "Industrials" like MMM. Who's wrong?
    25 Jul 2013, 10:18 PM Reply Like
  • Neither is wrong.


    You are using element #878 which comes from Morningstar. Their categorization differs from GICS. You can tell the source by looking in the "definitions.xlsx" file in the SMF install directory. Lookup the element and you'll see the source. Go on Morningstar's website to see their classes.


    The GICS sector is not currently available for the GetElementNumber function. It seems odd that a common website like Yahoo or Google wouldn't have that available for SMF but that's life.


    I prefer GICS too and retrieve it via a "brute force" approach from Ameritrade. See sample xlsx here for sector and industry for EMR:


    You often get rapid responses to such questions on the SMF forum. That's where I learned the above trick.
    25 Jul 2013, 10:42 PM Reply Like
  • Author’s reply » Glad you found it useful aida and thanks for the tip No Free Cake.


    There's quite a few different metrics a lot of people use depending on their style. The possibilities are endless, J.D. Welch's "Mad Method" uses quite a few indicators to pick the best selection. I've found that I've experimented over the years and settled on just a few.


    PEG is a good one to add for me.

    25 Jul 2013, 10:57 PM Reply Like
  • Well, now I've got a problem. Yesterday it worked fine, but today it's not working. I mean I have "last price" and the markets have been closed for more than 4 hours, so I assume my spreadsheet should be pulling today's close data, right? My questions then:


    1. In order to see an updated data on my spreadsheet, should I open any file from the unzipped folder first (maybe "RCH...Functions" or "RCH...Definitions") in order to retrieve new data or is there a hidden button of Refresh like in Pivot Tables? Right now I just opened the spreadsheet I created with Derek's instructions yesterday and added a few metrics of mine like Dividends, last price and 52-low/high prices. Anyway, what I'm doing wrong here?
    ETA: actually I also tried to open RCH files and try again to no avail.:-(


    2. Speaking of Dividends, I found it strange how different media outlets provided different information. I put two separate formulas from the RCH_Definition file and it pulls two different amounts. E.g. XOM's formula "=RCHGetElementNumber(... shows 2.52, but "=RCHGetElementNumber(... shows 2.34. What do they stand for? So, I had to play some math to get the meaning of them. The $2.52 is a new FISCAL (yearly) dividend and $2.34 is the calendar year dividend. Not sure which is more meaningful to an investor, but I usually go by calendar years as the IRS :-).


    Hopefully you can help me with the 1st question. The internet is connected and the connection seems fine.




    PS. BTW, after downloading and unzipping the RCH folder, was it OK to delete it? I deleted the unzipped file with all files in it, but I still have the unzipped folder with all RCH files. I opened my spreadsheet before deleting the zipped file and didn't work either.
    26 Jul 2013, 10:21 PM Reply Like
  • The answer to your "stale data" problem is found here:


    As for dividends, yes, different media outlets do report them differently. There are two common techniques.


    One popular technique is to report a "forward dividend" or "annualized dividend". This number is derived by taking the most recent dividend and multiplying it by the number of payments / year. Many US companies pay the same amount each quarter and this method shows what you would receive if they continue (and also don't increase). Note this method gives poor results when used with dividends that vary a great deal.


    The other popular method is trailing dividend. This simply adds all the dividends paid during the last 12 months. This is a reliable technique for any payment amounts. The disadvantage is that it doesn't help a new buyer or owner see how much they might receive going forward.


    Yahoo will use either depending on the ticker. It will show "yield (ttm)" for trailing dividend yield (Trailing Twelve Month) such as:
    Or, it will just say "Div & Yield" for forward dividend and yield:


    There is no one right calculation. Choose the one you want and find a reliable source. Note that dividends are rarely shown in fiscal periods unless you are looking at financial statements.


    As before, I highly recommend you address SMF questions to the board devoted to that. Not only will you get nearly instant help, there is a broad array of information already available. See:
    26 Jul 2013, 11:00 PM Reply Like
  • Thank you, No Free Cake. This time I asked questions on the Yahoo Groups. Since it's my first time of using YG, could you please enlighten me how it works:


    - Where should I ask questions? E.g. I followed your link and after reading and following instructions that unfortunately didn't work for me, I clicked Reply and wrote a message with many questions and posted. So, I basically posted under Links. Was I supposed to use "Messages" area to get attention?


    - It didn't automatically post, so I'm assuming that the administrator/author of this YG will read it first before posting. Correct?


    Anyway, my issue stayed the same, because my Internet options giving me an obstacle. Each time I choose "Every time I visit a website" and click OK, I went to check if it remained there. But no, it keeps reverting to Automatically. :-(. So, this didn't solve the problem of my stale data.
    While there I learned that a user is supposed to create a macro list (Alt+F8) in order to recalculate each time the Excel file is open, and, of course, I have no clue how to write those macros...


    Anyway, I'll see how long I'll persevere.
    Thanks for directions. Now I'll be writing my dumb questions on SMF group instead.


    Thank you.
    27 Jul 2013, 05:45 PM Reply Like
  • "Where should I ask questions? "


    Ask questions by clicking "Post" under the Messages section.


    "It didn't automatically post, so I'm assuming that the administrator/author of this YG will read it first before posting. Correct?"


    No, if you properly post a message it appears shortly after and Randy will answer - or someone else will.


    "But no, it keeps reverting to Automatically."


    You have to sort this out with Internet Explorer. If you are using a work computer perhaps you don't have authorization to make those setting changes. This setting must be correct to avoid stale data.


    "a user is supposed to create a macro list"


    No, that is not necessary. Every time you open your worksheet all the data will be refreshed. If you want to refresh the data while keeping your workbook open, then, yes, you need to create a button to trigger an SMF macro to refresh it "on the fly". But, this is not required - many novice users just close and re-open their worksheets.
    27 Jul 2013, 06:38 PM Reply Like
  • Derek, I'm trying to extract Dividend Date which is element #991, but all it returns is "N/A". Is there another element that will return Dividend Pay Date?
    11 Sep 2013, 05:00 AM Reply Like
  • Did you check what Yahoo shows for the dividend date for the ticker you are looking up? Element 991 is from Yahoo.


    For example, Yahoo shows "N/A" for IBM so if you use #991 that's what you will see:


    I found Yahoo wasn't reliable for that date so look it up on You have to extract it manually since SMF doesn't provide a simple element number for it. I wasn't able to post my formula on S/A as it kept corrupting it because of the imbedded website url. But, it uses RCHGetTableCell. If you can't figure it out on your own, go on the SMF board and Randy can help you. Or, message me privately and I can probably send you a sample spreadsheet with that in it.


    Nasdaq page for IBM:
    11 Sep 2013, 09:39 AM Reply Like
  • I've used the add-in flawlessly in Excel in a win7 Parallels Virtual Machine for more than 1 year. But just in the last week or so pulling data is horribly slow and I often get errors (cell reads Error) - requiring numerous saves until all the quotes appear. Is there some sort of limit as to the number of data elements yahoo will return? or is there some other systemic issue with the yahoo api? I am pulling close to 200 + data elements for over 50 stocks (quotes, beta, div yield and 200 MA). Does not appear to be connectivity as I am at 6MB/sec Thanks.
    6 Feb, 10:35 PM Reply Like
  • Yahoo has generated many complaints in the last weeks about very slow data retrieval. If you are using the RCHGetYahooQuotes function and are not already using it as an array entered formula then you should start. An array formula can retrieve info on up to 200 tickers at a time in a single internet retrieval.


    This has always been a much more efficient scheme but Yahoo had been so fast before that many people got away with using less efficient techniques.


    If you are retrieving data with other SMF functions (like RCHGetElementNumber), those cannot be entered as an array formula but some people have converted to using the smfUpdateDownloadTable function.


    Visit the SMF group for details on these techniques:



    Derek also wrote an article on the smfUpdateDownloadTable here:

    7 Feb, 12:43 AM Reply Like
  • Hi - Great article on a really cool way of automatically obtaining data to Excel.
    Your instructions really worked well - i have been experimenting and it seems to work. I only have a couple of problems that mainly deal with the source data. First, when I compare 5yr dividend growth rate between MSN and Fidelity, there are significant disparities - I believe Fidelity is correct. Are there any other sources besides MSN for 5yr DGR?
    Second, and it is probably me, I can't find any element number for "last price" or "latest price". If you can help with that, I would appreciate it.


    12 Feb, 02:43 PM Reply Like
  • If you can find a website with reliable data you can often get it with SMF. If there is no predefined element you can use its RCHGetTableCell or smfGetTagContent functions (see the Yahoo group for the docs and examples).


    For other ways to get dividend data you could retrieve all the actual dividends using the SMF get yahoo history function and calculate change rates yourself (adjusting any erroneous dividends by hand). Or, perhaps retrieve the dividends from Nasdaq using the RCHGetTableCell function I just mentioned.


    For pricing, use the RCHGetYahooQuotes function. Use it as an array formula if retrieving more than one ticker or more than one data element for the same ticker(s). Again, go to the group for all the details:



    Many common data items have already been asked and answered in the group's conversation stream. It's worth a search.
    12 Feb, 03:50 PM Reply Like
  • Oh, as for DGR, differences may just be how a site groups them. E.G., Yahoo uses the ex-div date for the dividend. Others may use the payment date.


    And, then you have to ask what exactly is being shown with a 5Y DGR? Is it the actual dividends paid (or declared) from 6 years ago compared to the ones paid last year? Or, perhaps 5 years ago to today's current (forward) dividend?


    Go to ADP's dividend history on Nasdaq to see what I mean. It's a good example because it's ex-div in Dec is paid in Jan. Figure the growth rate you want and see which site does it like you do.

    12 Feb, 03:58 PM Reply Like
  • No Free Cake - Thanks for the assistance
    12 Feb, 07:29 PM Reply Like
  • Author’s reply » Yes, thanks a million No Free Cake for all of your help in here
    22 Feb, 03:24 PM Reply Like
  • I would love to see a Mac version :(
    22 Feb, 11:58 AM Reply Like
Full index of posts »
Latest Followers


  • Lots of pushback on Google Glass, privacy concerns - $GOOG
    about 18 hours ago
  • Sold $VOD puts, 3 months out, 3 strikes OTM, 33 strike price, 3 contracts LOL. Yes obsessed with the number 3
    1 day ago
  • Sold puts in $ABT and ... $LNCO lol
    Apr 8, 2014
More »

Latest Comments

Posts by Themes
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.