The newer smfUpdateDownloadTable macro from Randy Harmelink offers several advantages over the traditional, cell-by-cell method, introduced in the previous article:
- 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.
- 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.
- 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.
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.
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.
Once in the group, proceed to the following menu path to locate the file smfUpdateDownloadTable-Sample.xls:
Files > Templates and Examples > smfUpdateDownloadTable
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.
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
2. Then delete them
B4 --> NEE
B5 --> KO
B6 --> MSFT
B7 --> WFC
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
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
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"
7. Now select cells C4 - C7 and D4 - D7, and format these as Percentage.
8. Select cells E4 - E7, F4-F7, and G4-G7, and then format these as General, to remove any formats from the sample template.
Now that we have completed our spreadsheet, it should look like the following screenshot:
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.
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.
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)