Alert -- Updates to Zacks Element Definitions

Zacks recently changed the web page name where the earnings estimates were located, so elements on smf-elements-8.txt needed to be updated.

New files on the website: 

• smf-elements-8.txt

The best practice is to exit EXCEL when updating SMF add-in files.

I am alive, but it's been an unusual rollercoaster ride...

 Since the end of June, I've only been home for a few days. Otherwise, it's been hospitals and rehab facilities. It's going to take me a while to catch up on 4+ months of emails and doctor bills. Here's how it went:

• 8 days in hospital (infection, allergic reaction to antibiotic)
• 22 days in rehab
• 1 day home
• 17 days in hospital (infection, prostate surgery, biopsy, mastectomy of cancerous lump)
• 1 day at rehab
• 10 days at hospital (removal of 2 liters of clots and hematomas from surgical site)
• 44 days of rehab
• 1 day home
• 8 days in hospital (passed out from low blood pressure)
• 15 days in rehab
• Home as of 11/1

I'm still not strong enough to easily do walker/wheelchair transfers like I was back in June. That may take months. or longer.

Tip -- 2.1d - Using the smfUpdateDownloadTable macro ("referback")

---In smf_addin@yahoogroups.com, <rharmelink@...> wrote :

The add-in is intended for ad hoc data retrieval. The way it extracts data is to save a copy of the web page, then to extract any data elements from that saved web page (as opposed to retrieving the web page once for each data item that comes from the web page). However, it only has 1000 slots for storing web pages. After those 1000 slots are full, all requests for data from additional web pages will error out.

I really didn't want people to start building databases of information, simply because they could. That would be unfair to the free data services, and with even people doing it might encourage them to code their web pages so that something like the add-in couldn't access them.

However, having said that, I think the smfUpdateDownloadTable process might be the best way for you to go. That is a macro that fills in a 2-dimensional table -- tickers by data items -- with values instead of formulas that recalculate every time the workbook is opened. Updating is controlled by you -- whenever you run the macro.

If you use the beta version of the add-in, you can even tell it which ranges to update -- entire rows (i.e. tickers), entire columns (i.e. specific data items, or even some range within the table. I sometimes have a column with a "Timestamp" so I know when I last updated the row.

Some information on that macro can be found on the "Tips and FAQS" web page on the add-in web site. Templates are in the FILES area of the Yahoo group, including the template I always start from, because it has everything already set up for the macro:

smfUpdateDownloadTable-Sample.xls

One thing not documented on the "Tips and FAQs" web page is that there is also a "referback" method. That is, using "~~~n~~~" (where "n" is 1, 2, 3, or whatever) says to use the value from that many columns to the left. So, "~~~1~~~" says to replace the data item from the previous column where "~~~1~~~" is. One technique I've used there is to have something like:

TEXT(COLUMNS($E2:Q2)-1,"~~~0~~~")

...where I want to use the value of whatever is in column E in my formula in column Q. Otherwise I needed to hardcode a "~~~12~~~" in the formula. However, if I inserted a new column, I need to make changes to all hardcoded formulas that use column E. By using the "COLUMNS()" function, the referral stays intact if columns are inserted.

So, for example, a formula for column Q of the table might look like:

="(""~~~1~~~""+""~~~2~~~"")/"""&TEXT(COLUMNS($E2:Q2)-1,"~~~0~~~")&"""-1"

I've even evolved the process to the point of assigning the referbacks to defined names, so I can do something like:

="IF(ISNUMBER("&cBid2&"),(MIN("&cStrike2&","&cPrice&")+"&cBid2&")/"&cPrice&"-1,""--"")"

...where cBid2, cStrike2, and cPrice all look like that TEXT() formula above. The defined names make it a lot easier to understand what the formula is referring to. :)

On Wed, Apr 22, 2015 at 8:58 AM, austinmlazar@... wrote:

I have hit a road block in the midst of a very exciting project I was working on, using the RCHGetElementNumber () function I have implemented over 43 important metrics that automatically generate for every ticker entered, I have made 506 rows with the plan to reweight the S&P dynamically with an Expected Alpha based weighting system. After about 145 tickers that worked seamlessly and uploaded all 43 data points within 45 seconds almost every time. Below a certain point it starts to give me error outputs for every data point and I know this isn't right because it worked seamlessly with any random ticker I would put it. I also tried to recreate another sheet thinking maybe the worksheet was overloaded but do I have to create an entirely new workbook for every increment of 145 or are there other fixes to this?

Tip -- 2.1b - Using the smfUpdateDownloadTable macro

---In smf_addin@yahoogroups.com, <rharmelink@...> wrote :

Let me restate [Tip 2.1a] and give some visuals of creating a range to fill using the smfUpdateDownloadTable macro:

1. 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.
2. The cells below the "Ticker" cell should be filled in with Yahoo ticker symbols, one per cell (see yellow-shaded cells on diagram).
3. 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.
4. 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:

	941	X	942
Heading0	Heading1	Heading2	Heading3
MMM
IBM
AA

After running the smfUpdateDownloadTable macro, it would look like this:

	941	X	942
Heading0	Heading1	Heading2	Heading3
MMM	58800000		61300000
IBM	1.73E+08		1.88E+08
AA	15230000		23800000

Tip -- 2.1a - Using the smfUpdateDownloadTable macro

---In smf_addin@yahoogroups.com, <rharmelink@...> wrote :

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:

1. The upper left hand corner cell of the table needs to have a range name of "Ticker"
2. The cells below the "Ticker" cell should be filled in with ticker symbols, one per cell
3. The cells to the right of the "Ticker" cell should be filled with column titles
4. 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")

A recent addition to the process is that if the cell entry above a column title is an "x", that column is skipped from any update -- I use this for any calculation columns I want in the middle of my table.  In all of my workbooks, I assign the smfUpdateDownloadTable macro to a keyboard shortcut of Ctrl-Shift-J.

On 10/19/07, rjlabs <rick@...> wrote:
>
> I have a large table of =RCHGetElementNumber() that has historical
> quarterly info that I only need to update sporadically. It takes about
> 15 minutes to update (about 60 tickers x 150 fields).
>
> Is there a way I can control when those functions run and update? I
> want to be able to open the spreadsheet with perhaps stale data and
> not have it update. However I don't want to set calculation to manual
> because other parts of the workbook I want regularly calculating. (Any
> programmer guide or FAQ or embedded docs on this just point me to them.) 

Tip -- Using smfGetCSVFile() to grab Yahoo Historical quotes

It appears that Yahoo once again has an easy-to-grab CSV file of their historical quotes available directly via a URL. See the link below for an example, using the smfGetCSVFile() function. It does use the new EXCEL "spill" feature -- if you don't have that available, all you will see is "Date" for the function, as you would need to array-enter the function. The new "spill" feature automatically expands any array function to fill the range is has data for.

SMF-Sample-smfGetCSVFile-Yahoo-Historical-Quotes.xlsm

Alert -- RCHGetYahooQuotes() issue

When I wrote RCHGetYahooHistory() well over a decade ago, the default ending date was 2020-12-31 if no date information was passed.

You can either switch over and use the new smfGetYahooHistory(), or specify a different ending year (e.g. 2030) for RCHGetYahooHistory().