The Volatility Virus

I mentioned in a recent post about some hassles we had with RTD. Here is a bit of background.

In a daily reporting environment it’s pretty common to use =NOW()-1 or better still =TODAY()-1, or even better still (in 2007 anyway) =WORKDAY(TODAY(),-1) to get yesterdays date to run your close of business reporting.
All of these are volatile, and in the grand scheme of things that’s probably not a massive issue. They are volatile because they have no way of checking if their inputs have changed, mainly because they don’t have any. So they have no choice but to calculate each time Excel does.

Unfortunately volatility is like a virus, a bad one, oh I don’t know, like say that flu bug that sir cough-a-lot at the desk next door has. Every function that depends on a cell that contains a volatile function becomes sneezy volatile.

Here is what I did to test that hypothesis:
I created a xll udf, that takes one parameter that it ignores completely, that logs each time its called.
I pointed it a static cell, change an unrelated part of the worksheet and the function is not called. I take this as evidence that my udf is not naturally volatile.
I then entered =NOW() in an unrelated cell, my function is not called
I then enter = NOW() in that precedent cell, now any change anywhere in that excel session causes =NOW() to update, which causes my udf to log another call. My udf argument is declared as an integer (J) so the bit of =NOW() that makes it into the function never changes, but unfortunately Excel is not checking that level of detail and calls my udf anyway.

I also checked about 20 levels of indirection and got the same result – the whole stack becomes volatile. It might only infect udfs, I don’t know, I’m not sure how I could reliably test native functions, any ideas?

None of this is a catastophe until your function return causes a recalc – exactly what RTD does.

XLL PLus to the rescue again.
So we have this common use case where people use a volatile argument to an rtd function. The RTD return will trigger a recalc which will update the volatile function (potentially with the exact same value). That cell update will trigger a recalc of all dependent cells which will trigger an RTD call…
So one solution is to avoid calling RTD if the inputs haven’t materially changed. And this is exactly what you can do with a couple of clicks in XLL+. You just click the ‘cache results’ checkbox and first time through your RTD version will call, next time XLL+ will take the value from the cache without calling RTD, no return, no recalc – problem fixed.

I’m sure there are plenty of other ways around this, but fundamentally I think in Excel 2007 they will all depends on the principle of avoiding that second RTD call by checking nothing important has changed since the last time that function was called with those parameters. Or mandating that users don’t use volatile functions as input parameters (good luck with that…).

Interestingly we started to go that route, and came up with a LastValidAsOfDate(ticker) function. And guess what? we realised we needed to make it volatile to update overnight…

I wonder if a more finely grained volatility is needed? something like the throttle that RTD already has but maybe update every hour or something? But then would you have different levels of volatility, how would you tell Excel the update frequency? Perhaps Excel could always check the first argument of volatile functions as use it like a timer?

I don’t know the answer but as it is in Excel 2007 the RTD implementation is pretty fragile, I think. Unless I’m missing something?

Anyway another big thumbs up for XLL+, the more I use it the more useful I find it.

Have you experienced this issue? Any suggestions? A better way to get some asynchronousicity into Excel udfs?

cheers

Simon

Advertisements

4 Responses to “The Volatility Virus”

  1. Charles Williams Says:

    The volatility virus effect is not just UDFs its all cells that are in the downstream chain and directly dependent on a volatile cell.
    You can check this using a function like CalcSeqCountRef which takes a cell ref as input and increments & returns a static counter each time its executed (which is every time its ref is recalculated)
    (see http://www.decisionmodels.com/calcsecretsi.htm for an explanation of cells that are only indirectly dependent on a volatile not recalculating)

  2. Charles Williams Says:

    But to solve the basic issue what we need is a way for a UDF to return a value (Empty or Null or something?) that tells Excel:
    don’t change the return value in the calling cell
    and do not calculate dependents of this cell (although it would have to be cleverer than that to handle cases where the calling formula was more than just a single call to the UDF).

  3. Dashing Blade Says:

    Occam’s Razor tells me RTD is pretty flakey simply because it has not taken off in popularity, for me the showstopper was when I realsied it didn’t throw a captuarable event . . .

    Anyway, the way I get around it’s “features” (whjen I have to ) is to call it via Application.WorksheetFunction.RTD call wrapped in in a UDF or via vba.

  4. Harlan Grove Says:

    Use a few hidden names to store state information to avoid as much volatility as possible. Also use a visible defined name to store latest date/time stamp and refresh it via open, save and print event handlers. And if you have particular types of users, also include a Refresh button/menu command.

    I’d have to figure that in decent production database environments there are system timer triggers which run canned procedures after midnight and maybe other times during the day. The basic idea being that current tables are maintained in the background with foreground tasks simply always using the current tables.

    You could use a similar approach for spreadsheet-based reporting systems with workbooks meant to be open all the time. An outside scheduled process can take over a running Excel session via GetObject call, and if it succeeds in taking over that session, it calls a macro in the always-open workbook to refresh reporting data. More fragile than a DBMS environment, but possible.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: