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

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

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