Customizing QLAddin: mini how-to + question

[ago-2]

I was trying to customize the QLAddin. I first tried to edit the .cpp files
manually and I failed miserably. Then I discovered srcgen, boy was I pleased! If
this can save some time to others the procedure to add new functionality is
fairly simple and it involves the following steps:

1) Edit QuantLibAddin-Z.Y.X\srcgen\XYZ.xml
2) python srcgen.py -s (or other flag according to the target addin)
3) Recompile QuantLibAddin
4) Use the addin in QuantLibAddin-Z.Y.X\Addins\ExcelStatic\xll

Further info here http://quantlib.org/quep/quep012.htm

There are some glitches:

* Most annoying is that if the xll is not properly loaded, the "Insert Function"
wizard will cause excel to crash.

* Some of the exposed objects do not have a getObject() method, so, in order to
use their methods you also need to modify the appropriate QuantLibAddin/XYZ.hpp
file and add a line like this to the relevant object:

const QuantLib::RateHelper& getObject() const {return *rateHelper_;}

I think that a good policy would be to add getObject to all objects.

* I do not know how to "concatenate" objects in a single function call (assuming
it is possible to do so). E.G. I was trying to get the lastDate of a RateHelper
in excel format (=long). The call to RateHelper->lastDate() returns a Date()
object, on which I then need to execute the serialNumber method. I am not sure
how to specify that in the XML file used by srcgen.

Some help on the last point would be appreciated. As a side note, it would be
nice if the Date object could be cast to a long.

[eric ehlers]

Hello

> If this can save some time to others the procedure to add new functionality
> is fairly simple and it involves the following steps:

There's already a how-to of sorts on extending QuantLibAddin:

http://quantlib.org/quantlibaddin/extending.html

However it's slightly out of date and will become slightly more so
with the upcoming release of QuantLibAddin 0.3.12 which includes
further enhancements to srcgen.

Any corrections/clarifications to the doc would be gratefully received! ;-)

> Further info here http://quantlib.org/quep/quep012.htm

That doc is superceded by the QuantLibAddin design doc:

http://quantlib.org/quantlibaddin/design.html

I'll update the QuEP with a pointer to the design doc (which
incidentally _is_ completely up to date in CVS).

> There are some glitches:
>
> * Most annoying is that if the xll is not properly loaded, the "Insert Function"
> wizard will cause excel to crash.

Annoying indeed - this needs to be fixed.  However I'm unable to
recreate the error, please could you email me directly with steps to
recreate the crash and I'll provide a fix.

> * Some of the exposed objects do not have a getObject() method, so, in order to
> use their methods you also need to modify the appropriate QuantLibAddin/XYZ.hpp
> file and add a line like this to the relevant object:
>
> const QuantLib::RateHelper& getObject() const {return *rateHelper_;}
>
> I think that a good policy would be to add getObject to all objects.

I agree, I'll put this in when I get the chance.

> * I do not know how to "concatenate" objects in a single function call (assuming
> it is possible to do so). E.G. I was trying to get the lastDate of a RateHelper
> in excel format (=long). The call to RateHelper->lastDate() returns a Date()
> object, on which I then need to execute the serialNumber method. I am not sure
> how to specify that in the XML file used by srcgen.

This isn't supported.  srcgen does support the conversion of a long to
a Date - see parameter settlementDate in function qlBondAccrued.
Given the current implementation of srcgen you'll need to add a
wrapper function to qla/termstructures.*pp.  Alternatively srcgen
could easily be extended to support the conversion of a Date to a
long.  Support for more generic "concatenation" would be harder.

Regards,
Eric

[ago-2]

On the same topic

1) I am not sure if it is possible, but it wold be nice to have a generic
function in excel like "ohMethodValue(handle, methodName as string,
parameterArray) as variant". The difference from "ohFieldValue" in this case is
that "methodName" is a method of getObject, so that if methodName = "XYZ", it
will call : returnValue = objectPointer->getObject().XYZ(parameterArray). If
returnValue is an object the handle is returned.

2) Is it possible to access the addin functionality from VB? If so, how?

[ago-2]

Thanks Eric,

I missed the docs, that would have saved me some time. If it is not too much
trouble I think that it would be nice to add a few conversions for Date->long,
InterestRate->double...

[eric ehlers]

> 1) I am not sure if it is possible, but it wold be nice to have a generic
> function in excel like "ohMethodValue(handle, methodName as string,
> parameterArray) as variant". The difference from "ohFieldValue" in this case is
> that "methodName" is a method of getObject, so that if methodName = "XYZ", it
> will call : returnValue = objectPointer->getObject().XYZ(parameterArray). If
> returnValue is an object the handle is returned.

It's a neat idea and yes it's possible.  There would have to be some
kind of message map which would associate strings with member
functions (not a straightforward task).  The code to initialize this
container might be generated by srcgen based on metadata.

This would greatly increase the complexity of the design and my first
reaction is that the cost exceeds the benefit but I'm open to
discussion.

> 2) Is it possible to access the addin functionality from VB? If so, how?

Here's an example VBA function to price an option.  This runs against
the latest CVS snapshot of QuantLibAddin - I'm pretty sure the same
approach works against the current production release 0.3.11.

Sub priceEuropeanOption()

Dim blackVolHandle As String

Dim blackScholesHandle As String

Dim optionHandle As String

Dim npv As Double

blackVolHandle = Application.Run("qlBlackConstantVol",
"blackconstantvol", 40250, 0.2, "Actual360")

blackScholesHandle = Application.Run("qlBlackScholesProcess",
"stoch1", blackVolHandle, 36, "Actual360", 40250, 0.06, 0)

optionHandle = Application.Run("qlVanillaOption", "eur1",
blackScholesHandle, "Put", "Vanilla", 35, "European", 43903, 0, "JR",
801)

npv = Application.Run("qlNPV", optionHandle)

Debug.Print npv

End Sub



> I missed the docs, that would have saved me some time.

That's my fault for not pointing the QuEP at the website, this is now
rectified.  Apologies for the confusion.

> If it is not too much
> trouble I think that it would be nice to add a few conversions for Date->long,
> InterestRate->double...

Ok I'll have a look at this when I get the time.

Regards,
Eric

[François du Vignaud]

> 1) I am not sure if it is possible, but it wold be nice to have a 
generic
> function in excel like "ohMethodValue(handle, methodName as string,
> parameterArray) as variant". The difference from "ohFieldValue" in 
this case is
> that "methodName" is a method of getObject, so that if methodName = 
"XYZ", it
> will call : returnValue = 
objectPointer->getObject().XYZ(parameterArray). If
> returnValue is an object the handle is returned.

It's a neat idea and yes it's possible. There would have to be some
kind of message map which would associate strings with member
functions (not a straightforward task). The code to initialize this
container might be generated by srcgen based on metadata.

This would greatly increase the complexity of the design and my first
reaction is that the cost exceeds the benefit but I'm open to
discussion.

Hello Eric,

I'm a new user of Quantlib and I must say that I'm really impressed by the quality of your work, many thanks for easing quant life so much !
I agree with Ago's idea about a generic function wrapping every object creation. I would also like to suggest to make arguments generic by storing them in some sort of map object. User would always have to define two arrays for every object instanciation. One array would contain arguments names and the second one would contain the corresponding values. A generic function would parse and store these arrays in an "argument map" then call the right function. I have good examples of such design to provide you with if you are interested in that idea. Thanks in advance for your attention,
rgds,
François

---

To help you stay safe and secure online, we've developed the all new Yahoo! Security Centre.

[eric ehlers]

Hi François

> I'm a new user of Quantlib and I must say that I'm really impressed by the
> quality of your work, many thanks for easing quant life so much !

Thanks very much for the feedback!  QLA is a group effort so I'll
accept your compliments on behalf of everyone.  :-)

> I agree with Ago's idea about a generic function wrapping every object
> creation. I would also like to suggest to make arguments generic by storing
> them in some sort of map object. User would always have to define two arrays
> for every object instanciation. One array would contain arguments names and
> the second one would contain the corresponding values. A generic function
> would parse and store these arrays in an "argument map" then call the right
> function. I have good examples of such design to provide you with if you are
> interested in that idea. Thanks in advance for your attention,

Yes I'm interested.  I'm familiar with the approach but I'd like to
hear your thoughts in greater detail.

I've updated the design docs to reflect Ago's feedback:
    http://quantlib.org/objecthandler/design.html#sec_3
    http://quantlib.org/quantlibaddin/design.html#sec_5

I'll email you directly to discuss your examples, once I've worked
through that I'll update the design doc with any additional info, and
we can get back to quantlib-dev about any next steps.

I'll be busy packaging the release this week and would hope to make
some progress on this in the next couple of weeks.

Regards,
Eric