MS Solver Foundation: Excel interface through COM/C#

In preparation for a presentation on different modeling languages for a project at a financial firm, I implemented a demo QP/LP portfolio model with an Excel front-end and a .NET/C# DLL. 

MS Solver Foundation has a great tool to develop Excel based optimization models: the Excel plug-in.  One of the restrictions however is that only one single model can be handled this way. Sometimes we need to solve a series of models or different variants of a model. In this post I will show this can be achieved by creating a .NET DLL that uses the MS Solver Foundation API’s on one hand, and interfaces with Excel through a COM interface on the other hand.

Note that there are other possible approaches such as VSTO to handle this. VSTO allows you to create .Net applications inside an Office environment. The Microsoft Solver Foundation Excel plug-in is an example of a VSTO application. 

We used a portfolio problem as an example. Basically we have a multi-criteria problem:

minimize risk  x’Qx
maximize return  μ’x
subject to a budget constraint  Σx = 1

We attack this by solving a series of QP models

minimize λ x'Qx − μ'x
subject to a budget constraint  Σx = 1

for different values of λ.

The term for risk was rewritten as:

risk = Σ w_t²/T
w_t = Σ R_i,t x_i

where R are the mean adjusted returns. This separable formulation suggests an approximate LP formulation:

risk ≈ Σ |w_t|/T

The OML model for the QP formulation looks like:

Afterwards we can return x[i], Return and calculate the Risk term as (Obj+Return)*NumT/Lambda. We wanted to keep the quadratic term in the objective for obvious reasons.  Note that we need to substitute out {0} and {1} later so it holds values for the scalars Lambda and NumT. The substitution is done by String.Format, and we need to protect the other constructs like {t,T} by adding another pair of curly brackets: {{t,T}}.

The data for Mean and AdjRet are coming from an Access database. This is similar to http://yetanothermathprogrammingconsultant.blogspot.com/2009/05/ms-oml-model-using-c-and-data-from.html.

To generate a COM interface we use:

[ClassInterface(ClassInterfaceType.AutoDual)]

This will cause all public methods to become visible for VBA. For more information see: http://richnewman.wordpress.com/2007/04/15/a-beginner’s-guide-to-calling-a-net-library-from-excel/.

The QP model version is coded as:

This can be called from VBA as:

There is a little bit of extra code to retrieve the solution. At the end we collect solution data as:

In Excel it is easy to display this graphically:

The architecture of this demo app is as follows (click to enlarge):

The database not only stores the data, but also does some of the data manipulation: e.g. the mean adjusted returns are calculated in Access through queries.