qlxl dependencies and object updates

[Peter Caspers-2]

Hi qlxl / excel experts,

can somebody help me with the following questions on qlxl (suppose
excels automatic calculation is deactivated):

1. There seems to be a difference between a) updating a cell by
range.calculate b) updating a whole sheet by worksheet.calculate, a)
being "more reliable" than b). That is, sometimes an update of an object
does not force the update of another object one of whose arguments is
the first object using b), but a) will do the trick. Btw a) seems to be
the same as place the cursor on the cell, press F2 and enter. b) same as
pressing shift F9. My guess is that a) recalculates the cell no matter,
if it is "dirty" in excels dependency tree, while b) only recalculates
if a cell is "dirty". That would however imply that excels dependency
tree is not fully reliable?

2. If I have a sheet where I construct an object depending on some input
on the same sheet and these input cells does not change, then the
version of the object is updated every time I press shift F9. Why is
that? Excel should think that no recalculation is necessary, i.e. the
cell should not be dirty in the dependency tree and the version should
not be updated?

3. The trigger argument in constructors: Is that purely dummy forcing a
dependency on excel level or is something done with that parameter in
the object handler?

4. Overwrite: I suppose that not specified means true. When I
recalculate a sheet very often it slows down until it is not usable any
more. My workaround is to call the delete all objects - method in the oh
on a suitable position in the recalculation loop. Then it works fine.
This looks like objects are not overwritten, but new objects are created
on every update?

Thanks for any hints
Peter




------------------------------------------------------------------------------
The ultimate all-in-one performance toolkit: Intel(R) Parallel Studio XE:
Pinpoint memory and threading errors before they happen.
Find and fix more than 250 security defects in the development cycle.
Locate bottlenecks in serial and parallel code that limit performance.
http://p.sf.net/sfu/intel-dev2devfeb
_______________________________________________
QuantLib-dev mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/quantlib-dev

[Eric Ehlers-2]

Hi Peter,

> 2. If I have a sheet where I construct an object depending on some input
> on the same sheet and these input cells does not change, then the
> version of the object is updated every time I press shift F9. Why is
> that? Excel should think that no recalculation is necessary, i.e. the
> cell should not be dirty in the dependency tree and the version should
> not be updated?

IIRC cell X's dependencies recalculate whenever cell X recalculates,  
whether or not the value in cell X changes.

> 3. The trigger argument in constructors: Is that purely dummy forcing a
> dependency on excel level

Yes.

> 4. Overwrite: I suppose that not specified means true.

Not specified means false.

> When I
> recalculate a sheet very often it slows down until it is not usable any
> more. My workaround is to call the delete all objects - method in the oh
> on a suitable position in the recalculation loop. Then it works fine.
> This looks like objects are not overwritten, but new objects are created
> on every update?

What does ohRepositoryObjectCount() tell you?

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


------------------------------------------------------------------------------
Enable your software for Intel(R) Active Management Technology to meet the
growing manageability and security demands of your customers. Businesses
are taking advantage of Intel(R) vPro (TM) technology - will your software
be a part of the solution? Download the Intel(R) Manageability Checker
today! http://p.sf.net/sfu/intel-dev2devmar
_______________________________________________
QuantLib-dev mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/quantlib-dev

[Peter Caspers-2]

Eric, thank you very much. I will try to set overwrite to yes in the
formulas, this is certainly the origin of the problem. Is it by the way
in general useful _not_ to overwrite objects, i.e. is there a special
reason why this parameter defaults to false?
Peter

Am 22.03.2011 23:53, schrieb Eric Ehlers:

> Hi Peter,
>
>> 2. If I have a sheet where I construct an object depending on some input
>> on the same sheet and these input cells does not change, then the
>> version of the object is updated every time I press shift F9. Why is
>> that? Excel should think that no recalculation is necessary, i.e. the
>> cell should not be dirty in the dependency tree and the version should
>> not be updated?
>
> IIRC cell X's dependencies recalculate whenever cell X recalculates,
> whether or not the value in cell X changes.
>
>> 3. The trigger argument in constructors: Is that purely dummy forcing a
>> dependency on excel level
>
> Yes.
>
>> 4. Overwrite: I suppose that not specified means true.
>
> Not specified means false.
>
>> When I
>> recalculate a sheet very often it slows down until it is not usable any
>> more. My workaround is to call the delete all objects - method in the oh
>> on a suitable position in the recalculation loop. Then it works fine.
>> This looks like objects are not overwritten, but new objects are created
>> on every update?
>
> What does ohRepositoryObjectCount() tell you?
>
> 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
>

------------------------------------------------------------------------------
Enable your software for Intel(R) Active Management Technology to meet the
growing manageability and security demands of your customers. Businesses
are taking advantage of Intel(R) vPro (TM) technology - will your software
be a part of the solution? Download the Intel(R) Manageability Checker
today! http://p.sf.net/sfu/intel-dev2devmar
_______________________________________________
QuantLib-dev mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/quantlib-dev

[Eric Ehlers-2]

Hi Peter,

> Eric, thank you very much. I will try to set overwrite to yes in the  
> formulas, this is certainly the origin of the problem. Is it by the  
> way in general useful _not_ to overwrite objects, i.e. is there a  
> special reason why this parameter defaults to false?

Happy to help.  Usually you would allow the overwrite parameter to  
default to false.  Suppose you create an object with ID foo.  Then you  
attempt to recreate a new object foo, for example during a recalc.  
The system verifies that the new object resides in the same cell as  
the existing one, which is the usual case, and the old object is  
overwritten by the new one.  If the new object resides in a different  
cell, this usually indicates an error on the part of the user - you  
have tried to create two different objects with the same name - and  
the construction of the new object fails with an error message to the  
effect that an existing object with the same name resides in a  
different cell.  If you set overwrite to true then the creation of the  
new object will succeed even if the new object resides in a different  
cell than the old one.  There are not many situations where it is  
desirable to set overwrite to true.

I am worried that you have found a memory leak so I am curious to hear  
the results of your investigations.

Kind 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


------------------------------------------------------------------------------
Enable your software for Intel(R) Active Management Technology to meet the
growing manageability and security demands of your customers. Businesses
are taking advantage of Intel(R) vPro (TM) technology - will your software
be a part of the solution? Download the Intel(R) Manageability Checker
today! http://p.sf.net/sfu/intel-dev2devmar
_______________________________________________
QuantLib-dev mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/quantlib-dev

[Ferdinando M. Ametrano-3]

Hi all

as a general rule I suggest to avoid to recreate multiple time the
same object. If such a need arise it is generally an indication of bad
design: an object should be able to alter its state without being
recreated.

Anyway if you have to recreate an object multiple time this means you
create it in a dynamically changing workbook which is open and
recalculated: in this case I suggest to keep the object anonymous.
Naming it would be useless as you will have to reference it using the
cell address (or even better a named range), otherwise Excel won't
keep track of the dependency.
If you don't use names the overwrite parameter is not effective.

ciao -- nando

------------------------------------------------------------------------------
Enable your software for Intel(R) Active Management Technology to meet the
growing manageability and security demands of your customers. Businesses
are taking advantage of Intel(R) vPro (TM) technology - will your software
be a part of the solution? Download the Intel(R) Manageability Checker
today! http://p.sf.net/sfu/intel-dev2devmar
_______________________________________________
QuantLib-dev mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/quantlib-dev

[Peter Caspers-2]

Hi Eric, Ferdinando,

ok, I completely misinterpreted the meaning of the overwrite flag up to now.

In fact there may be a problem. I attach an example sheet. Please open it
in an excel with automatic calculations disabled. Calling the macro test
will create two quotes in cells c2 and c3 and two associated rate helpers
in cells a2 and a3. Then a piecewise yield curve object in cell a6 is
created and recomputed many times in an infinite loop. During this I
observe that more and more memory is used by excel.exe. Escaping the
loop, the number of objects is 5 (seems ok). If I delete the yield curve
object (by computing cell b12) the memory allocated before is released.

I am aware that one should not recompute the yield curve object if quotes
are updated, but rather rely on the implemented observer pattern. However
the behaviour above should not be as it is, should it?

And there is one more thing I do not understand (for today): The update of a
quote by the setValue method will force the recomputation of the yield curve
(as a lazy object observing the rate helpers), as I can reproduce by
computing cell d2 (setValue on Quote) and a9 (get discount factor from yield
curve). This is as expected and working just as in a pure C++ world.

However, if I recompute e.g. cell a2 (the first rate helper) and a9
afterwards, I see the same effect, i.e. the yield curve object seems to
have received a notification from the recomputed rate helper object? I
thought that recomputation of a cell containing a ql-constructor creates a
new object using the constructor and deletes the previous version of the
object? Obviously this is not the case, but something smarter is going on.
Could you briefly explain please?

Thanks a lot
Peter

----------------------------------------------------------------------
From: "Eric Ehlers" <[hidden email]>
To: "Peter Caspers" <[hidden email]>
Date: Thu, 24 Mar 2011 01:04:56 +0100
Subject: Re: [Quantlib-dev] qlxl dependencies and object updates

> Hi Peter,
>
> > Eric, thank you very much. I will try to set overwrite to yes in the  
> > formulas, this is certainly the origin of the problem. Is it by the  
> > way in general useful _not_ to overwrite objects, i.e. is there a  
> > special reason why this parameter defaults to false?
>
> Happy to help.  Usually you would allow the overwrite parameter to  
> default to false.  Suppose you create an object with ID foo.  Then you  
> attempt to recreate a new object foo, for example during a recalc.  
> The system verifies that the new object resides in the same cell as  
> the existing one, which is the usual case, and the old object is  
> overwritten by the new one.  If the new object resides in a different  
> cell, this usually indicates an error on the part of the user - you  
> have tried to create two different objects with the same name - and  
> the construction of the new object fails with an error message to the  
> effect that an existing object with the same name resides in a  
> different cell.  If you set overwrite to true then the creation of the  
> new object will succeed even if the new object resides in a different  
> cell than the old one.  There are not many situations where it is  
> desirable to set overwrite to true.
>
> I am worried that you have found a memory leak so I am curious to hear  
> the results of your investigations.
>
> Kind 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
>
>

------------------------------------------------------------------------------
Enable your software for Intel(R) Active Management Technology to meet the
growing manageability and security demands of your customers. Businesses
are taking advantage of Intel(R) vPro (TM) technology - will your software
be a part of the solution? Download the Intel(R) Manageability Checker
today! http://p.sf.net/sfu/intel-dev2devmar
_______________________________________________
QuantLib-dev mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/quantlib-dev

[Ferdinando M. Ametrano-3]

Hi Peter

> In fact there may be a problem. I attach an example sheet. [...] During this I
> observe that more and more memory is used by excel.exe. Escaping the
> loop, the number of objects is 5 (seems ok). If I delete the yield curve
> object (by computing cell b12) the memory allocated before is released.
>
> I am aware that one should not recompute the yield curve object if quotes
> are updated, but rather rely on the implemented observer pattern. However
> the behaviour above should not be as it is, should it?

yes, I think you've found a genuine memory leak. Eric ?

> However, if I recompute e.g. cell a2 (the first rate helper) and a9
> afterwards, I see the same effect, i.e. the yield curve object seems to
> have received a notification from the recomputed rate helper object?

would you elaborate a little more here. I mean there might be a lot
going on under the surface (and undocumented :-), but before
discovering my cards I would need to understand what exactly do you do
in this second case. Namely before recomputing a2 do you setValue ?
What do you mean by "the yield curve object seems to have received a
notification from the recomputed rate helper object"

later

ciao -- Nando

------------------------------------------------------------------------------
Enable your software for Intel(R) Active Management Technology to meet the
growing manageability and security demands of your customers. Businesses
are taking advantage of Intel(R) vPro (TM) technology - will your software
be a part of the solution? Download the Intel(R) Manageability Checker
today! http://p.sf.net/sfu/intel-dev2devmar
_______________________________________________
QuantLib-dev mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/quantlib-dev

[Peter Caspers-2]

> would you elaborate a little more here.

For simplicity, first replace the references in the rate helpers to the
quotes by simple excel constants, i.e. change the formula in a2 to

=qlDepositRateHelper2("R1";0.02;"2m";2;"TARGET";"MF";;"ACT/360")

Now compute a6 (yield curve) and a9 (discount factor). I get

0.9983375805

Next, change cell a2 to

=qlDepositRateHelper2("R1";0.021;"2m";2;"TARGET";"MF";;"ACT/360")

and compute a9 (a6 is not computed before!). Now I get

0.9982546818

(All this also works when leaving the references to the quotes as they are
and instead of computing cell a2 compute c2)

There is no call of setValue() in this whole procedure. But obviously the
yield curve objects update() method must have been called through computing
a2, otherwise I would not get a different discount factor? But that would
mean that some observable / observer notification takes place and I would
like to understand how exactly.

Thx, Peter

-----Ursprüngliche Nachricht-----
Von: [hidden email] [mailto:[hidden email]] Im
Auftrag von Ferdinando Ametrano
Gesendet: Freitag, 25. März 2011 11:06
An: Peter Caspers
Cc: Eric Ehlers; [hidden email]
Betreff: Re: Re: [Quantlib-dev] qlxl dependencies and object updates

Hi Peter

> In fact there may be a problem. I attach an example sheet. [...] During
this I
> observe that more and more memory is used by excel.exe. Escaping the
> loop, the number of objects is 5 (seems ok). If I delete the yield curve
> object (by computing cell b12) the memory allocated before is released.
>
> I am aware that one should not recompute the yield curve object if quotes
> are updated, but rather rely on the implemented observer pattern. However
> the behaviour above should not be as it is, should it?

yes, I think you've found a genuine memory leak. Eric ?

> However, if I recompute e.g. cell a2 (the first rate helper) and a9
> afterwards, I see the same effect, i.e. the yield curve object seems to
> have received a notification from the recomputed rate helper object?

would you elaborate a little more here. I mean there might be a lot
going on under the surface (and undocumented :-), but before
discovering my cards I would need to understand what exactly do you do
in this second case. Namely before recomputing a2 do you setValue ?
What do you mean by "the yield curve object seems to have received a
notification from the recomputed rate helper object"

later

ciao -- Nando


------------------------------------------------------------------------------
Enable your software for Intel(R) Active Management Technology to meet the
growing manageability and security demands of your customers. Businesses
are taking advantage of Intel(R) vPro (TM) technology - will your software
be a part of the solution? Download the Intel(R) Manageability Checker
today! http://p.sf.net/sfu/intel-dev2devmar
_______________________________________________
QuantLib-dev mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/quantlib-dev

[Eric Ehlers-2]

Hi Peter,

> However, if I recompute e.g. cell a2 (the first rate helper) and a9
> afterwards, I see the same effect, i.e. the yield curve object seems to
> have received a notification from the recomputed rate helper object? I
> thought that recomputation of a cell containing a ql-constructor creates a
> new object using the constructor and deletes the previous version of the
> object? Obviously this is not the case, but something smarter is going on.
> Could you briefly explain please?

ObjectHandler has some extra logic to ensure that objects are always  
kept up to date.

For various reasons OH keeps track of all of the relationships between  
objects.  When an object is retrieved, OH verifies that the object is  
newer than its precedents and if not the object is recreated.  This  
relies on an implementation of the Observer/Observable pattern which  
is local to OH and separate from QuantLib's use of the same pattern.  
This logic in OH is also independent of Excel.  For more detail see  
class ObjectWrapper.

I recreated your test for the memory leak and was not immediately able  
to find an explanation or a solution, I continue to investigate and  
will keep you posted.  Many thanks for your feedback.

Kind 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


------------------------------------------------------------------------------
Enable your software for Intel(R) Active Management Technology to meet the
growing manageability and security demands of your customers. Businesses
are taking advantage of Intel(R) vPro (TM) technology - will your software
be a part of the solution? Download the Intel(R) Manageability Checker
today! http://p.sf.net/sfu/intel-dev2devmar
_______________________________________________
QuantLib-dev mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/quantlib-dev