Posted by
P Nelnik on
URL: http://quantlib.414.s1.nabble.com/suggested-improvement-to-the-quantlib-excel-addin-tp8240.html
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