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?