MS Solver Foundation: Excel interface through COM/C# (2)

This is a follow-up on http://yetanothermathprogrammingconsultant.blogspot.com/2009/07/ms-solver-foundation-excel-interface.html. Here we explore an LP approximation to the QP models used in the original post. In the original model we used

risk = Σ w_t²/T

Here we will replace the quadratic expression by an absolute value:

risk ≈ Σ |w_t|/T

The big advantage of an LP model is that we can extend this to a MIP for more complicated modeling situations. Although some solvers support MIQP models, the MIQP algorithms are often not as fast as MIP algorithms. (Microsoft Solver Foundation does have a QP algorithm, but as it is based on an interior point algorithm it is not really suited to be used inside a branch-and-bound framework, so there is no standard MIQP capability).

The LP model is included in the DLL code as follows:

The absolute values are implemented using a standard variable splitting technique: w_t = y_t⁺ − y_t^- with y_t⁺,y_t^- ≥ 0. Then it follows that |w_t| = y_t⁺ + y_t^-.

Everything else can stay the same. The results are shown in the screen dump below. As you can see the LP offers a reasonable approximation: the portfolios for different trade-offs between risk and return are quite similar. (Click on the picture to enlarge).

This exercise shows that beyond using the MSF Excel plug-in, the .NET API’s in combination with the modeling language OML are a powerful tool to write applications and that these solutions can be easily integrated in an Office Environment (Access, Excel).