Re: [Quantlib-dev] Some questions about forcing recalculation

[Eric E. <eri...@na...>]

Hi Andrew,

On Thu, August 28, 2008 10:59, a.p. wrote:
> About your example:
>
> "the object can be identified by the raw (literal) string "my_obj" - not
> "my_obj#001" and not a reference to the range in the closed Book1.xls from
> which the object was constructed"
>
> - that's the way i work with QLXL functions, but when the object is updated
> (by user or by external data), how could we force recalculation of
> dependency functions (such as qlYieldTSZeroRate, etc.)?!

My earlier advice to close the workbook in which "my_obj" resides, that was
given on the assumption that "my_obj" can be created just once at startup.

You say that "my_obj" is recreated frequently and these updates must trigger a
recalculation of dependent objects.  In that case, you could use this
approach: Ensure that the book in which "my_obj" is created remains open.  All
objects which depend on "my_obj" refer to it using an Excel range reference to
its cell (and not the raw string "my_obj").  Any time "my_obj" is recreated,
Excel recalculates its dependents.

> You see, Excel
> recalculates UDF only in case of arguments change, but all arguments are
> still the same (so if you hit F9 nothing will be changed)!

Not exactly.  Suppose Range B depends on Range A.  If A's inputs change, Excel
is smart enough to recalculate B, even if A's return value is unchanged.  In
the scenario I describe above, A's return value would in fact change on each
recalculation, as the update counter is incremented - my_obj#0000,
my_obj#0001, etc.  But even without that, B is still recalculated.  If you set
calculation to automatic then you don't even need to hit F9.

> One way to solve
> this problem - to use trigger parameter, but itsn't convinient (see my
> previous topic). So, are there another decisions?

In the approach I describe above, in which the dependents of my_obj use Excel
range references to refer directly to its cell, no trigger parameters are
required.

ObjectHandler 0.9.6 includes an enhancement that also solves this problem. 
The enhancement ensures that all object references are up to date.  Suppose
Object B depends on Object A.  Any time Object B is retrieved, OH checks
whether B is newer then A, if not, OH recreates B before returning it to the
user.

What is the class of your "my_obj"?  I have never witnessed a use case like
the one you describe.  Typically when an object is required globally, it is
something such as a term structure which can be created just once when the
application is initialized, and thereafter updated non-destructively.  If an
object is being recreated frequently, particularly in response to user input,
it is usually something with few or no dependents such as an instrument, and
all of the user-editable inputs are local to the worksheet on which the object
resides.

Regards,
Eric

-------------------------
Eric Ehlers
nazcatech sprl | Brussels | http://www.nazcatech.be
Distributed computing for pricing analytics - Use Microsoft Excel as a client
to the Grid