Using the smfUpdateDownloadTable macro
---In smf_addin@yahoogroups.com, <rharmelink@...> wrote :
See also [Tip 2.1a] below from Randy.
This will give some visuals of creating a range to fill using the smfUpdateDownloadTable macro:
- The upper left hand corner cell of the table needs to have a range name of "Ticker" (see green-shaded cell on diagram). The text in the cell can be anything you want it to be.
- The cells below the "Ticker" cell should be filled in with Yahoo ticker symbols, one per cell (see yellow-shaded cells on diagram).
- The cells to the right of the "Ticker" cell should be filled with column titles (see orange-shaded cells on diagram. The text in these cells can be anything you want them to be.
- The
cells above the column titles need to be filled in with SMF add-in
formulas or element numbers (see blue-shaded cells on diagram). Use five
tildas as a substitute for a ticker symbol. For example, any of the
following text strings could be used to get "Market Capitalization" from
Yahoo:
941
RCHGetElementNumber("~~~~~", 941)
RCHGetTableCell("http://finance.yahoo.com/q/ks?s=~~~~~",1,">Market Cap")
If you place an "X" in the cell of the element definition, it would tell the smfUpdateDownloadTable macro to skip it and leave that column alone. I do this when I want to create a column with my own formulas in, or to use as a divider column in the table.
The macro continues processing rows with ticker symbols (i.e.
green-shaded cell) until it runs into an empty cell with no ticker
symbol in it. It continues process across the row of element definitions
(i.e. blue-shaded cells) until it runs into an empty cell with no
element definition in it.
So, this is what it would look like after doing the four steps above:
After running the smfUpdateDownloadTable macro, it would look like this:
If you use the Referback feature I found a couple of bugs:
1. It will quit processing if it does a referback to a number that has been converted to display trillions as for example 1.35T
The other is if the referback column contains N/A
Tip -- 2.1a - Using the smfUpdateDownloadTable macro
I use the smfUpdateDownloadTable macro for such situations. This would actually place values into the table, so no recalculation occurs. When I want "fresh" values, I just rerun the macro. Here is the documentation for the table setup from the macro's module:
- The upper left hand corner cell of the table needs to have a range name of "Ticker"
- The cells below the "Ticker" cell should be filled in with ticker symbols, one per cell
- The cells to the right of the "Ticker" cell should be filled with column titles
- The
cells above the column titles need to be filled in with SMF add-in
formulas or element numbers. Use five tildas as a substitute for a
ticker symbol. For example, any of the following text strings could be
used to get "Market Capitalization" from Yahoo:
941
RCHGetElementNumber("~~~~~", 941)
RCHGetTableCell("http://finance.yahoo.com/q/ks?s=~~~~~",1,">Market Cap")
No comments:
Post a Comment