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: Optimize Large Data Sets With The SmfUpdateDownloadTable Macro 0 comments
    May 8, 2013 3:34 AM

    Introduction

    The newer smfUpdateDownloadTable macro from Randy Harmelink offers several advantages over the traditional, cell-by-cell method, introduced in the previous article:

    1. The macro places values into each cell of the workbook, instead of having formulas in each cell that can recalculate.

      This means they will not change unless you run the macro again, and also there is no recalculation that takes place when you open the workbook. The values will be the same as they were when you saved the workbook, saving the wait time when the workbook is initially opened.

    2. You can control which rows and columns will update, since it continues filling the table until it finds a blank row or column specification. So, for example, if you insert a blank line after the 10th ticker symbol of 500, it will only update the first 10 ticker symbols.
    3. The macro gives a better idea of how the update is progressing, since the status line tells you which line of data it is currently processing.

    Getting Started

    The easiest way to get started is to simply download the smfUpdateDownloadTable sample template, and then simply replace the sample values with the indicators you want to look up.

    Assumptions

    This article is assuming you already have installed the Stock Market Function Add-in, signed up for the Yahoo Group, and have access to the files area. See the previous article for more on the Yahoo Group or getting started the SMF Add-in.

    Download Location

    Once in the group, proceed to the following menu path to locate the file smfUpdateDownloadTable-Sample.xls:

    Files > Templates and Examples > smfUpdateDownloadTable

    (click to enlarge)

    Multiple Methods for Retrieving Data

    Now go ahead and open the file up, and let's take a look. The spreadsheet will retrieve data from the providers and supports all the SMF functions.

    (click to enlarge)

    In particular, notice in row 2, there are three ways for calling up the "Market Cap" value, but notice all 3 return the same value.

    Constructing a Dividend Growth Workbook

    Now, building on a sample Dividend Growth spreadsheet we used in the previous article, let's plug in our sample values.

    1. Simply select the contents of columns D-J

    (click to enlarge)

    2. Then delete them

    (click to enlarge)

    3. Now fill in the tickers in column B, NextEra Energy (NYSE:NEE), Coca-Cola (NYSE:KO), Microsoft (NASDAQ:MSFT), and Wells Fargo (NYSE:WFC)

    B4 --> NEE

    B5 --> KO

    B6 --> MSFT

    B7 --> WFC

    (click to enlarge)

    4. Then fill in the column headings for row 3:

    C3 --> DIVIDEND GROWTH RATE (5-Year Annualized)

    D3 --> YIELD

    E3 --> PAYOUT RATIO (<.50)

    F3 --> DEBT/EQUITY (<1)

    G3 --> Current P/E (Below 15)

    H3 --> SECTOR

    (click to enlarge)

    5. Next, we are going to add in the formulas. We will use the RCHGetElementNumber for D2 and E2, so we can format these later into percentages. But for all other cells, we will use the native macro format for ease of use.

    Enter the following formulas into row 2:

    C2 --> RCHGetElementNumber("~~~~~",63)/100

    D2 --> RCHGetElementNumber("~~~~~",13666)/100

    E2 --> 47

    F2 --> 42

    G2 --> 54

    H2 --> 878

    (click to enlarge)

    6. To populate the data, press Ctrl - Shift - J, and the spreadsheet will now go out and start pulling data. You should see a message at the bottom of Excel, showing the percentage completed, at the bottom of the spreadsheet while it is pulling data:

    "25% Completed -- now processing KO -- #2 of 4"

    Finishing Touches

    7. Now select cells C4 - C7 and D4 - D7, and format these as Percentage.

    (click to enlarge)

    8. Select cells E4 - E7, F4-F7, and G4-G7, and then format these as General, to remove any formats from the sample template.

    (click to enlarge)

    Final Product

    Now that we have completed our spreadsheet, it should look like the following screenshot:

    (click to enlarge)

    Limitations

    1. Note that any internal strings are limited to 255 bytes, so in cases where you are having trouble pulling very long values, like using the RCHGetTableCell function, you may want to refer to this limit.

    This limitation is not on the macro but comes from the Visual Basic script (VBA) "evaluate" function.

    2. The add-in is designed so that it can only access data from 1000 web pages at any given time. The add-in is not an appropriate tool to load up a database, simply because one can. That is an unfair burden to place on the free data sources.

    Screening tools should be used to get to a small subset of companies, where more detailed information would be useful.

    Conclusion

    The smfUpdateDownloadTable macro is a powerful tool for pulling big data sets, and is easy to get up and running quickly.

    Again, 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 NEE, KO, MSFT.

    Themes: Tools
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 (0)
Track new comments
Be the first to comment
Full index of posts »
Latest Followers

StockTalks

  • Sold $NLY puts, January at 11
    Jul 1, 2014
  • Sold to open $ADM Sept at 41
    Jun 25, 2014
  • No new positions this week, got tons of dry powder coming back online though after buying to close.
    Jun 19, 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.