Some questions about forcing recalculation

[Andrew Kolesnikov]

Hello guys.
Thank you for your priveous help - it was relly useful.
Currently, i try to solve one popular Excel problem - force recalculation within UDFs (in this case - QLXL functions). The main problem is string (ID) referring to QL add-inn objects. In QLXL there is one sophisticated way to force the correct dependency recalculation - usage trigger method for observable objects. Unfortunately this way is not relly convinient when you work within Excel workbook which contains a lot of objects (such as option trader workbooks) and data (so Ctrl+Alt+F9 method is also unaffordable). Could you suggest any decisions to clear this obstacle? For example, i tried to constuct a VBA UDFs with QLXL functions call and force recalculation in case of hitting F9 or Shift+F9, but i couldn't.
P.S. Am i right that the only way to solve this problem via C++ is to use trigger method?

[Eric Ehlers-2]

Hello,

On Mon, August 25, 2008 09:52, a.p. wrote:
>
> Hello guys.
> Thank you for your priveous help - it was relly useful.

With pleasure.

> Currently, i try to solve one popular Excel problem - force recalculation
> within UDFs (in this case - QLXL functions). The main problem is string (ID)
> referring to QL add-inn objects. In QLXL there is one sophisticated way to
> force the correct dependency recalculation - usage trigger method for
> observable objects. Unfortunately this way is not relly convinient when you
> work within Excel workbook which contains a lot of objects (such as option
> trader workbooks) and data (so Ctrl+Alt+F9 method is also unaffordable).

I wouldn't say that triggers are inconvenient - I would say that your Excel
application should be carefully designed to ensure that object dependencies
are maintained through the use of triggers.  You're absolutely right that in a
live desktop trading environment it should not ever be necessary for the user
to hit Ctrl-Alt-F9 to force a full recalculation.

The design of QLXL favors the performance of member functions over
constructors, and constructors are allowed certain more expensive operations
which are not called from members.  Construct objects as a separate step, when
the application is initialized or in response to menu events.  Workbooks
containing constructors should be opened once, calculated and closed to
prevent unnecessary object reconstruction.  Even better, construct objects by
deserializing them from XML initialization data that you create in advance,
constructing objects this way is many times faster than constructing them from
worksheet formulas.  The objective is a workbook which is hooked up to the
live data feed with calculation set to automatic, prices recalculated in real
time, and any objects updated non-destructively.

> Could you suggest any decisions to clear this obstacle?
> For example, i tried
> to constuct a VBA UDFs with QLXL functions call and force recalculation in
> case of hitting F9 or Shift+F9, but i couldn't.

The XLL can use xlcOnRecalc to trap the recalculation event but I would
strongly advise against that in this situation.

> P.S. Am i right that the only way to solve this problem via C++ is to use
> trigger method?

Triggers are the best way to maintain object dependencies that are not
apparent to Excel, for example where QuantLib's Observer/Observable pattern is
exploited (http://quantlib.org/quantlibaddin/observer.html).

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


-------------------------------------------------------------------------
This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
Build the coolest Linux based applications with Moblin SDK & win great prizes
Grand prize is a trip for two to an Open Source event anywhere in the world
http://moblin-contest.org/redirect.php?banner_id=100&url=/
_______________________________________________
QuantLib-dev mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/quantlib-dev

[Andrew Kolesnikov]

"I wouldn't say that triggers are inconvenient - I would say that your Excel
application should be carefully designed to ensure that object dependencies
are maintained through the use of triggers."

In fact object dependencies are absolutly maintained through the use of triggers, but this usage is inconvenient. For example, you've created an object ("my_obj") and calculate some functions (maybe dozens) with this object as a parameter. So the user of created application should directly reference to the cell with returning string ID (for instance "my_obj#001") through trigger argument. In case of you example:

"Construct objects as a separate step, when the application is initialized or in response to menu events.  Workbooks containing constructors should be opened once, calculated and closed to prevent unnecessary object reconstruction."

it becomes impossible at all, cause object workbook have been closed. Is this another way to reference all necessary objects as trigger argument when the application is initialized or maybe some other algorithm to prevent user from referencing trigger cell every time he calculates new function? Maybe, i missed smth, but i don't know what to do :(

"The XLL can use xlcOnRecalc to trap the recalculation event but I would strongly advise against that in this situation."

Absolutely agree with you.

Regards,
Andrew

[Eric Ehlers-2]

Hi Andrew,

Please review the documentation below:

    http://www.objecthandler.org/references.html
    http://www.quantlibaddin.org/observer.html

On Wed, August 27, 2008 10:29, a.p. wrote:

>
> "I wouldn't say that triggers are inconvenient - I would say that your Excel
> application should be carefully designed to ensure that object dependencies
> are maintained through the use of triggers."
>
> In fact object dependencies are absolutly maintained through the use of
> triggers, but this usage is inconvenient. For example, you've created an
> object ("my_obj") and calculate some functions (maybe dozens) with this
> object as a parameter. So the user of created application should directly
> reference to the cell with returning string ID (for instance "my_obj#001")
> through trigger argument.

In Book1.xls, create the object with ID "my_obj", then close Book1.xls.

Now, from anywhere else in the Excel session, when that object is required as
an input to a function, 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.

The fact that Book1.xls is closed, prevents "my_obj" from being recreated
and spares you the hassle of ensuring that dependents of "my_obj" are updated
after "my_obj" is recreated.

(In fact ObjectHandler 0.9.6 includes an enhancement to prevent such problems
but let's not get into that now).

In this simple example you don't even need any triggers of any kind.

> In case of you example:
>
> "Construct objects as a separate step, when the application is initialized
> or in response to menu events.  Workbooks containing constructors should be
> opened once, calculated and closed to prevent unnecessary object
> reconstruction."
>
> it becomes impossible at all, cause object workbook have been closed.

Sounds like you're using a reference to the range in the closed book from
which "my_obj" was constructed.  Use instead the string "my_obj".

> Is
> this another way to reference all necessary objects as trigger argument when
> the application is initialized or maybe some other algorithm to prevent user
> from referencing trigger cell every time he calculates new function? Maybe,
> i missed smth, but i don't know what to do :(

So far we haven't even gotten into a case where triggers are required.  An
example of that is in the docs I referenced above, please get back to me if
it's unclear.

> "The XLL can use xlcOnRecalc to trap the recalculation event but I would
> strongly advise against that in this situation."
>
> Absolutely agree with you.

Ooops.  I mentioned xlcOnRecalc in response to this comment of yours:

> For example, i tried
> to constuct a VBA UDFs with QLXL functions call and force recalculation in
> case of hitting F9 or Shift+F9, but i couldn't.

But I misread - you're trying to implement the UDF not in the XLL but in VBA.
I wouldn't do that either ;-)

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


-------------------------------------------------------------------------
This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
Build the coolest Linux based applications with Moblin SDK & win great prizes
Grand prize is a trip for two to an Open Source event anywhere in the world
http://moblin-contest.org/redirect.php?banner_id=100&url=/
_______________________________________________
QuantLib-dev mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/quantlib-dev

[Andrew Kolesnikov]

Hello Eric,
thank you for participation

"Please review the documentation below:

    http://www.objecthandler.org/references.html
    http://www.quantlibaddin.org/observer.html"

I could say, that i'm a big fan of QL, so of course i've already read all useful articles :)

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

[Eric Ehlers-2]

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


-------------------------------------------------------------------------
This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
Build the coolest Linux based applications with Moblin SDK & win great prizes
Grand prize is a trip for two to an Open Source event anywhere in the world
http://moblin-contest.org/redirect.php?banner_id=100&url=/
_______________________________________________
QuantLib-dev mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/quantlib-dev

[Andrew Kolesnikov]

Eric wrote:

"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"

Sure, moreover this example doesn't conflict with my previous statement about
argument change. But, you see, if we discuss trader workbook it's absolutely
inconvinient to use range reference: there are a lot of instuments(objects),
which are created at start up and upgraded during whole day, and many functions
(different calculations, in fact, created in different workbooks) dependent on
these objects. So string reference (e.g. "my_obj") is more applicable in practice.

"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."

So, does it include support for dependecy functions, i.e. will funcion with
string reference f("my_obj", some static arguments...) be recalculated if
my_obj is updated?

"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."

It could be term structure, but dependency functions use string reference instead
of cell, so it doesn't matter.

Regards,
Andrew

[Eric Ehlers-2]

Hi Andrew,

On Thu, September 4, 2008 10:13, a.p. wrote:

>
> Eric wrote:
>
> "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"
>
> Sure, moreover this example doesn't conflict with my previous statement
> about
> argument change. But, you see, if we discuss trader workbook it's absolutely
> inconvinient to use range reference: there are a lot of instuments(objects),
> which are created at start up and upgraded during whole day, and many
> functions
> (different calculations, in fact, created in different workbooks) dependent
> on
> these objects. So string reference (e.g. "my_obj") is more applicable in
> practice.

I would most certainly use an Excel range reference in this case, not raw
strings nor triggers.  You say the range reference is inconvenient, I would
argue that it's a question of carefully designing the structure of your
workbooks and controlling their interdependencies.

But I appreciate that you have a different opinion so let's agree to disagree.
:-)

> "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."
>
> So, does it include support for dependecy functions, i.e. will funcion with
> string reference f("my_obj", some static arguments...) be recalculated if
> my_obj is updated?

No, updating my_obj does not immediately force recalculation of dependent
functions.  Rather, any time you retrieve an object B which depends on my_obj,
OH ensures that B is newer than my_obj.  The new feature is not relevant to
the case you describe above.  When f() is recalculated, it receives by
definition an up to date reference to my_obj.

> "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."
>
> It could be term structure, but dependency functions use string reference
> instead
> of cell, so it doesn't matter.

I'm curious to know.  Why is your user recreating the term structure
repeatedly throughout the session?  Why not create it just once?  Changes in
the rates will update the state of the original TS without recreating it.  If
the user needs to change the structure of the TS, you could create multiple
TSs at startup, and use a Handle to point to whichever TS is desired at a
given moment.  Dependent objects could reference the Handle without needing to
know to which TS it points.

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


-------------------------------------------------------------------------
This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
Build the coolest Linux based applications with Moblin SDK & win great prizes
Grand prize is a trip for two to an Open Source event anywhere in the world
http://moblin-contest.org/redirect.php?banner_id=100&url=/
_______________________________________________
QuantLib-dev mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/quantlib-dev