Re: [Quantlib-dev] exposing a new class to Excel

[eric e. <eri...@gm...>]

Hi Misha,

I'm pleased to see a request to customize QLXL.  The architecture
behind QLXL has been carefully designed to be independent of QuantLib
and it would be great to see it used for other applications.
Documentation and examples are presently thin but I'd be happy to
extend them if there is interest and any kind of
feedback/testing/contributions anyone might care to make would be much
appreciated.

> 1) If I have a class MyClass, what is the easiest way to create an instance
> of it in Excel and use it?
>     First I want to create an instance of an object ( get a pointer into
> excel cell), then modify the object, then call some function on the object.
>     Basically, could you point me to the simplest example, which can
> exercise the following interface in Excel:
>     class MyClass
>     {
>        public:
>          MyClass( double x );
>          void change( double z);
>          double get() const;
>     }

For a "hello world" example of this type open
ObjectHandler\ObjectHandler_vc8.sln (VC2005) or
ObjectHandler\ObjectHandler_vc7.sln (VC2003) and look at the examples.
 ExampleXllStatic shows a simple class exposed to Excel.  For that
build, the ObjectHandler functionality is compiled into a standalone
XLL, demonstrated in spreadsheet
ObjectHandler\Examples\xl\exampleStatic.xls.  ExampleXllDynamic1 and
ExampleXllDynamic2 implement the same functionality but demonstrate
how the ObjectHandler XLL can be separate from multiple client XLLs as
explained at http://www.objecthandler.org/installation.html.
ExampleCpp shows the same functionality in a C++ program.

The above examples are hand-written.  Alternatively you can use gensrc
to autogenerate the source code for the Addin.  gensrc is used to
autogenerate some source code for the ObjectHandler project itself,
and this is the simplest available example of gensrc usage.  Under
directory ObjectHandler\gensrc, the configuration files are found, and
a makefile there invokes the core gensrc application (a separate
download) which outputs the OH source.

The basic idea of using gensrc is explained at
http://www.quantlibaddin.org/extending.html but that document is out
of date.

Addins created by gensrc may or may not have a dependency on
ObjectHandler, as required.  gensrc supports creation of addins with
any of the following features:
- simple stateless functions
- class constructors and member functions
- datatypes specific to the client application
- Enumerated datatypes and classes
(http://www.quantlibaddin.org/auto/enums.html)
- Automatic coercion of input datatypes
(http://www.quantlibaddin.org/coercion.html)
- Functions which loop automatically on their inputs
(http://www.quantlibaddin.org/loops.html)
- ValueObjects and (in the next release) serialization
- platforms Excel, Calc, C, C++, and Guile
- documentation (e.g. http://www.quantlibaddin.org/auto/categories.html)

The QuantLibAddin/QuantLibXL application is another example of a
collection of addins generated by gensrc.  Unfortunately QLXL's use of
gensrc is complex and undocumented.  Instructions for building QLXL
from source code are at
http://www.quantlibaddin.org/build_tutorial.html.

> 2) I need to create a function, which returns a lot of stuff. Not all the
> stuff will be used all the time. Ideally, I would return a value of type
> ObjectHandler::ValueObject into Excel (map from string to any), then get
> only the fields I need at the time. The fields may be dynamic, e.g. value is
> always calculated, but greeks and diagnostic information are optional. Could
> you point me to an example of how I can create such a function?

The purpose of ValueObjects is to capture a snapshot of the input
arguments to a constructor for use in serialization.  Under the
current design it's not possible to use a VO as the return value of a
function.  Instead you could return a matrix of datatype any,
qlSwapLegAnalysis() is an example of a function which does this.  The
configuration of qlSwapLegAnalysis() is found in file
QuantLibAddin\gensrc\metadata\Functions\swap.xml.

I'd be very happy to see ObjectHandler and gensrc used outside of the
QuantLib project and would do whatever I can to help that happen.

Regards,
Eric