Posted by
P Nelnik on
URL: http://quantlib.414.s1.nabble.com/suggested-improvement-to-the-quantlib-excel-addin-tp8240p8246.html
Simon,
That's an interesting idea, but it would seem like you're battling against excel rather than working with it!
Suppose we have 3 cells (i), (ii) and (iii) which must be calc'ed in that order.
For example (i) creates a bond, (ii) associates a yield curve to the bond and (iii) asks for the clean price.
Now if we've obeyed rules 1 and 2 mentioned below, then the spread-sheet developer has absolutely no choice but to pass (i) in as a parameter into (ii) and in turn (ii) gets passed into (iii).
I.e. you can't associate a yield curve with a bond before the bond has been created
and you can't ask the bond for a price before a yield curve has been associated with it.
If we then 'dirty' cell (ii) then excel will know at the next incremental calc ( F9) that (iii) must be updated and that (i) does not need to be.
So if you work with excel, it will do much of the work for you.
On the other hand if you allow violations of excel's dependency tree,
then you should not blame excel for unstable, unreliable sheets.
Cheers
Philip
On Thu, May 6, 2010 at 10:37 PM, Simon Ibbotson
<[hidden email]> wrote:
Hi all,
A possible enhancement would be to be able to get a list of Excel references from QuantLib for objects that are uncalculated. Then a macro could overwrite the F9 function in Excel to
A) Calculate Excel.
B) Get a list of object cell references from QLXL for objects that are now uncalculated (using the QuantLib calculation tree of Observer / Observable).
C) Set those cells to "dirty" using Range.Dirty function.
D) Calculate Excel again.
Has anyone tried this?
Cheers,
Simon
Sent from my BlackBerry® wireless device
-----Original Message-----
From: P Nelnik <
[hidden email]>
Date: Thu, 6 May 2010 16:08:58
To: Peter Caspers<
[hidden email]>
Cc: Ferdinando Ametrano<
[hidden email]>; <
[hidden email]>
Subject: Re: [Quantlib-users] suggested improvement to the quantlib excel
addin <erfolgreich auf Virenfreiheit geprueft>
On Thu, May 6, 2010 at 4:08 PM, P Nelnik
<[hidden email]>
wrote:
Peter,
I
too am a fan of QLXL, though it could be better!
The change I'm
suggesting is not big at all.
For example the function
qlInstrumentSetPricingEngine(.
.) or its replacement should return
the handle to an object which contained a pointer to the original (
unaltered ) bond and also the yield curve.
On the other hand, with the situation as we have now, suppose I'm
developing a sheet and call qlInstrumentSetPricingEngine(..) in
some distant far off cell and then forget about it. Then later I call
the same function again passing in the same bond. I now have a sheet
that is very unreliable. It may also be very difficult to track down the
bug. On the other hand if rules one and two below were followed, then
there would be no problem.
When writing the addin code we just need to obey:
1:
no global variables. ( global constants are fine )
2: all inputs are treated as strictly read-only (i.e. no side effects)
This
does mean that sometimes behind the scenes some objects need to be
cloned.
It is amazing how easy it is to obey those two rules.
It is also amazing what a difference it makes to the stability of sheets
that are developed particularly when they are big.
In one of the
big investment banks that I used to work in, their addins strictly obey
rules 1
& 2 above.
Bearing in mind this email thread is going to be posted online and
remain there for a very long time.
I'd prefer not to mention the name of the bank.
I used to
critise excel until I learned more about how its dependency
(calculation) tree worked.
Now I know how to build big, stable, efficient, sheets with excel.
Obeying
rules 1 and 2 above when building addins really helps.
Cheers
PhilipOn Thu, May 6, 2010 at 3:30
PM, Peter Caspers
<[hidden email]>
wrote:
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
------------------------------------------------------------------------------
_______________________________________________
QuantLib-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/quantlib-users