Introduction

QuantSA uses ExcelDNA to expose functions to Excel. The Excel layer should be a wrapper layer for the rest of the library as far as possible. No business logic should be implemented in this layer except to the extent that input validation requires some business logic.

Technicallly any public static method can be exposed by giving it the ExcelDNA ExcelFunctionAttribute, however it is preferable to rather use the attribute QuantSAExcelFunctionAttribute. The latter allows for the function visibility to be controlled at runtime and allows function definitions to be split between a hand written part and an auto generated part that handles much of the data conversion.

Function Attributes

When exposing a function to Excel it must have the QuantSAExcelFunctionAttribute and set:

  • Description
  • Name - must start with QSA in the main library
  • Category - should match the name of the public static class in whihc the function is contained, which should in turn match the location of the underlying function in the main libary
  • ExampleSheet - a sheet that shows a user how to create the neccessary inputs for the function and call it.
  • IsHidden - The default hidden state of the function. This can be overridden at runtime. Most functions should be false. If the function is sufficiently obscure that it should be hidden from most users then consider putting it in an add-in. In future if we need to deprecate a function it will make sense to hide it so that new users don’t start using it.

Parameter Attributes

Each parameter should have the QuantSAExcelArgument attribute and set the description of the parameter as would be useful to a user of the function. This will in general not be the same description in the documentation of the method being called in the main library which should be written to be useful to someone developing in the QuantSA solution.

Argument attributes can actually be either ExcelDNA’s ExcelArgumentAttribute or QuantSA’s QuantSAExcelArgumentAttribute. The latter extends the former and adds a field Default that can be used by the autogenerated data conversion methods to provide a default value.

Automatically exposing functions

As described below, exposing functions comes with some boiler plate code that needs to be written. This can be reduced by generating some of it. An excel function can be written as normal with the attributes as described above but a new attribute property:

  • HasGeneratedVersion

Can be set to true.

It is preferable to use the code generation because then future enhancements to the Excel interface can easily be applied to all functions.

When this is set to true the input and output types are not limited to ones handled by ExcelDNA, type conversions using QuantSA.Excel.ExcelUtilities will be generated. Adding and getting objects off the object map is generated and the input and output descriptions are extended to include a link to the type of the argument in the generated help.

To effectively use the automatic linking to types in the help, the return type must be selected as the most general version, for example a swap should be returned as a Product so that the help can automatically link the Product page and it is more likely that subseqent functions that the user will call will require the more general type.

Generating the ExcelWrapper Code

The project ExcelFunctions has a post build step that generates Excel wrappers usings Excel types. To be able to use this the GenerateXLFunctions solution needs to have been run once and the post build event must be modified to point to the output folder of that build.

Manually exposing functions

When methods are manually exposed as Excel there are a few conditions that need to be met:

  • The input arguments must all be ExcelDNA supported types
  • Conversion from ExcelDNA supported type to QuantSA types must be done by methods in QuantSA.Excel.ExcelUtilities
  • The whole method body must be wrapped in a try catch and any errors must be sent back to Excel using ExcelUtilities.Error0D or its higher dimensional versions
  • When non primitive objects are created they must be added to the object map with ExcelUtilities.AddObject
Tags: developers