Posted by
Peter Caspers on
URL: http://quantlib.414.s1.nabble.com/suggested-improvement-to-the-quantlib-excel-addin-tp8240p8243.html
Philip,
Quantlib has an object oriented design. Of course there are other ways to
write computer programs like functional, logical, aspect oriented ... but
there is no per se best way.
It makes no sense to disguise this design principle when exposing it to
excel (although excel is definitely not object oriented). QLXL is - to my
mind - a very good way to use QL in an easy way on excel spreadsheets.
I also have stability problems with the update mechanism in excel for some
sheets (even when triggering carefully), but you have to blame excel for
this (I have the same problems with Numerix / Excel). I solved this by
writing VBA macros which update the necessary cells in the right order
(using range.calculate). This works very stable.
What better frameworks did you see?
Best, Peter
P Nelnik
<
[hidden email]
m> An
Ferdinando Ametrano
06.05.2010 04:33 <
[hidden email]>
Kopie
[hidden email]
et
Thema
Re: [Quantlib-users] suggested
improvement to the quantlib excel
addin <erfolgreich auf
Virenfreiheit geprueft>
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------------------------------------------------------------------------------------------------------------------------------------------------------
WGZ BANK AG Westdeutsche Genossenschafts-Zentralbank
Sitz: Düsseldorf, Registergericht: Amtsgericht Düsseldorf, HRB 52363
Vorstand: Werner Böhnke (Vors.), Karl-Heinz Moll, Hans-Bernd Wolberg,
Uwe Berghaus (stellv.), Dr. Christian Brauckmann (stellv.), Michael Speth
(stellv.)
Vorsitzender des Aufsichtsrats: Dieter Philipp
Ueber das Internet versandte E-Mails koennen unter fremdem Namen erstellt
oder inhaltlich veraendert werden. Aus diesem Grund sind unsere als E-Mail
verschickten Nachrichten grundsaetzlich keine rechtsverbindlichen
Erklaerungen. Der Inhalt dieser E-Mail samt Anlagen ist vertraulich und u.
U. rechtlich geschuetzt. Der Inhalt ist ausschließlich an einen bestimmten
Empfaenger gerichtet. Eine Weitergabe, die Herstellung von Kopien oder der
sonstige Gebrauch durch Nichtadressaten ist nicht erlaubt.
Messages sent by e-mail can be manipulated by third parties. For this
reason our e-mail messages are generally not legally binding. This
electronic message (including any attachments) contains confidential
information and may be privileged or otherwise protected from disclosure.
The information is intended to be for the use of the intended addressee
only. Please be aware that any copy, distribution or use of the contents of
this message by any other person than the intended addressee is prohibited.
------------------------------------------------------------------------------
_______________________________________________
QuantLib-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/quantlib-users