Re: suggested improvement to the quantlib excel addin

Posted by P Nelnik on
URL: http://quantlib.414.s1.nabble.com/suggested-improvement-to-the-quantlib-excel-addin-tp8240p8242.html

Thanks for the response Nando,

One of the problems of having a global variable, such as the evaluation date, is that when a sheet is left up over-night and a user re-calcs it the next morning (F9) all the 'dirty' cells get re-calced. But excel has no way of knowing which cells actually need re-calced. So in the sheet some of the cells show results left over from the previous day and used yesterday's date.
You can re-calc (F9) as many times as you want but the problem won't go away until a full recalc (Ctrl+Alt+F9) is done which will update all cells.
Moreover, if a user does set the evaluation date, say when doing historical analysis, he then needs to go through the entire workbook inserting dependencies on the cell that updates the evaluation date. Otherwise results will be very unreliable.
The thing is there is absolutely no need to introduce this vulnerability.
You just need to stick to two strict rules when writing an addin: ( both are very standard rules of functional programming)
1: no global variables. ( global constants are fine )
2: all inputs are treated as strictly read-only

As for comparing the QLXL treatment of objects, I must say I have seen much better.
Though to be honest I have also seen much worse, though here, I won't mention names (of my pervious employers).

Regards
Philip

On Thu, May 6, 2010 at 12:07 AM, Ferdinando Ametrano <[hidden email]> wrote:

Hi Philip

thank you for your observations, it's clear you spent some time using
QuantLibXL and your feedback is appreciated, even if I don't agree.

Let's address the two examples you provide.

1) qlSettingsSetEvaluationDate (the set function)

I agree that global variables might be problematic, so my suggestion
is not to play with them: just avoid qlSettingsSetEvaluationDate.
99% of use cases you will never have to change the evaluation date
which defaults to Excel TODAY()

The only case _I_  use qlSettingsSetEvaluationDate is when I have to
reconstruct a market snapshot from the past. In that case I change the
evaluation date at Excel start up and then never play with it anymore.

That's also why I encourage usage of qlSettingsEvaluationDate (the
accessor/get function) instead of Excel's native TODAY(), since the
EvaluationDate is what is relevant for quantLib analytics. An
additional benefit is that qlSettingsEvaluationDate is non volatile.

2) qlInstrumentSetPricingEngine

Instrument::setPricingEngine is a non-cost method, so it alters the
Instrument status. Of course your instance of Instrument will return
different results depending on its status, i.e. depending on the
Engine you've set.
I don't see how this could be criticized or improved: it will be up to
the user to ensure there are not multiple engines competing for the
same object in unordered way. Two way to deal with this issue:

a) You could use Trigger to rule the order of engine set, results get,
engine set, results get, etc

b) You could have different instances of the same Instrument, each one
with its own engine.

The only marginal improvement I see for case b)  is that we could
provide Excel Instrument constructors with an optional engine
parameter: this engine, if provided, could be set automatically with
no need to play later with qlInstrumentSetPricingEngine. Anyway I
would not force the user to stick to the approach b) since proper
usage of the approach a) is more efficient even if more complex



I agree that a lot could be done to improve QLXL (especially
documentation and user-friendliness), but unless I've missed your
point I don't see a real design flaw. And I bet that a similar design
is shared by most object-oriented Excel libraries out there

ciao -- Nando

On Wed, May 5, 2010 at 11:00 AM, P Nelnik <[hidden email]> wrote:
> Greetings all,
>
> Summary:
> The treatment of objects in the quanlib excel addin could be very
> significantly improved to make spread sheets more safe, efficient and
> robust.
>
> Details:
> I should start by saying that I hope the tone of this email is understood.
> It is not meant as criticism for its own sake, but rather the aim of this
> email is to help improve quantlib. And I'm willing to help out doing some of
> the work.
>
> Recently I've been working with quantlib in xll and I've found a few
> flaws.In particular the current interaction with the excel dependency tree
> has the potential to be very unsafe. We can consider excel to be a
> functional programming language. Excel builds and internal dependency tree
> based on the contents of the cells. When used correctly it can be very
> efficient at doing incremental calculation (F9) of all cells that are
> currently 'dirty'. Full calculations ( Ctrl+Alt+F9 ) can also work well.
>
> It is crucially important that if cell A must be called before cell B, then
> we have to tell excel about it, i.e. cell A must be a precendent of B.
> Currently with quantlib (0.9.7),  we can use the Target argument to achieve
> this. However it is not essential and it is very easy to set-up a sheet
> which works most of the time, but sometimes fails to calc correctly. When
> that happens tracking down the bug can be rather difficult.
>
> Right now, if I create a bond and in two different cells call
> qlInstrumentSetPricingEngine(..) with two different yield curves,
> then I have a sheet which very unreliable. I then have an excel calc-order
> dependence.
> When getting the clean price of the bond, sometimes one yield curve will be
> used and sometimes the other.
> I could blame the person who built the sheet for being careless, but if we
> take a step back we can see that there is no need to have this
> vulnerability.
> Suppose we were to ban 'side-effects'. I.e. when
> qlInstrumentSetPricingEngine(..) is called it was not allowed modify the
> bond object that is passed in.
> Well, it is very easy to do that when writing the addin the
> qlInstrumentSetPricingEngine(..) would just return an object which would
> contain a pointer to the unaltered bond and the yield curve, we would then
> have a safe sheet where qlInstrumentSetPricingEngine(..) could be called as
> many times as we like.
> When calling say qlBondCleanPrice(..) we would passing a reference to the
> object created by qlInstrumentSetPricingEngine(..)
> On the other hand if we were to call qlBondCleanPrice(..) and pass in a
> reference to the original bond it would always error out.
>
> Another example of a function that causes a problem is
> qlSettingsEvaluationDate(). In functional languages (such as excel) having
> global variables is asking for trouble. Any function which needs an
> evaluation date should either (i) take in the evaluation date as an argument
> or (ii) take in an object which contains the evaluation date.
>
> By having a function such as qlSettingsEvaluationDate() and the other
> non-safe functions it means that at all times the excel programmer needs to
> be very careful about the depenceny tree. If he's not careful he'll end up
> with a sheet that will work most of the time but sometimes will give
> incorrect results.
> Rather than demanding that the excel programmers are constantly vigilent, it
> would be significantly preferable just to think about it when writing the
> addin code, i.e. get it right once.
> Currently we have a situation where we have a huge trap that has been set
> for excel programmers.
> I accept that as it stands now, with enormous care a safe sheet can be
> built,
> Alternatively, if there were just a little more care in the design of the
> quantlib addin in the first place
> then building stable safe workbooks that work consistently would be much
> more straight forward.
>
> The moral of the story is that in functional programming, such as excel,
> global variables and side-effects are very ugly and are asking for touble.
> And the good news is that they are absolutely not necessary.
>
> Regards
> Philip
>
>

> ------------------------------------------------------------------------------
>
> _______________________________________________
> QuantLib-users mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/quantlib-users
>
>

------------------------------------------------------------------------------

_______________________________________________
QuantLib-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/quantlib-users