Bloomberg V3COM API wrapper update for VBA

In one of my previous posts, I presented one possible approach for creating a wrapper for Bloomberg API snapshot data http://mikejuniperhill.blogspot.fi/2013/05/bloomberg-v3com-api-wrapper-for-vba.html

In this post, I am updating that implementation for creating that Bloomberg API wrapper, but now enabling data retrieving process for

1. Reference data (Ex. previous close bid for EUR Curncy)
2. Bulk reference data (Ex. all curve members for USD swap curve - retrieving ISIN codes + yellow key)
3. Historical data (Ex. daily close prices for EONIA Index between 31.5 - 24.6.2013)

In our tester program, we have data retrieving examples for all of these three different categories.

• First, we retrieve curve members for four different swap curves (bulk reference data).
• Then, we retrieve security name and previous close value for first members (overnight index) for each of the four curves (reference/snapshot data).
• Finally, we retrieve daily historical data between 31.5.2013 - 24.6.2013 for overnight index for each of the four curves (historical data). Note, that historical data retrieves date/value-pairs.

In that tester program below, the result data from Bloomberg server is returned into a variant array. You should investigate the content of this array in your VB editor (Locals window), to get some idea about how the data is packed in that result array. Needless to say, you have to know how to handle multidimensional arrays, to be able to "unpack" data from that result array.

The idea of this wrapper class is to serve as a simple black box interface into BBCOM server to retrieve any type of desired market data. Design is, that there is no design at all. The functionality of Bloomberg API can safely assumed to be quite stable, so I made a decision to keep everything inside one monolithic class for the maximum user convinience reasons. By that way, it is now very easy to import this one wrapper class into a new VBA project and just start to use it. Remember to create reference to Bloomberg API COM 3.5 Type Library in your VB editor before using this class. 

If there is anything unusual going on with this, just let me know. For the time being, I will collect all the possible fixes and updates into this posting. I hope this helps in your working with Bloomberg-related market data. Have a nice day.
- Mike

UPDATES

8.7.2013
Most of the data types from BCOM API are converted implicitly into corresponding VBA data types without any problems, such as

BLPAPI_CHAR = VBA String
BLPAPI_DATE = VBA Date
BLPAPI_FLOAT64 = VBA Double
BLPAPI_STRING = VBA String

However, BCOM data type BLPAPI_INT32 was causing runtime error 458 (Variable uses an automation not supported in Visual Basic). I assume this data type to be Unsigned Integer. Problem has been fixed by converting this data type explicitly into VBA Long data type. You can investigate Datatype property for bFieldValue object in wrapper method getServerData_reference.

' VBA standard module
Option Explicit
'
Private b As BCOM_wrapper
Private r As Variant
Private s() As Variant
Private f() As Variant
'
Sub tester()
    '
    Set b = New BCOM_wrapper
    '
    ' EXAMPLE 1 - retrieve bulk data (curve members)
    ' create security array for four curves ID
    ReDim s(0 To 3)
    s(0) = "YCSW0023 Index" 'USD swap curve
    s(1) = "YCSW0045 Index" 'EUR swap curve
    s(2) = "YCSW0004 Index" ' CAD swap curve
    s(3) = "YCSW0018 Index" ' ZAR swap curve
    ' redim field array
    ReDim f(0 To 0)
    f(0) = "INDX_MEMBERS" 'curve members
    r = b.getData(BULK_REFERENCE_DATA, s, f) ' <---- investigate array content
    '
    '
    '
    ' EXAMPLE 2 - retrieve snapshot/reference data (security name and previous close)
    ReDim s(0 To 3)
    s(0) = r(0, 0) 'USD overnight
    s(1) = r(1, 0) 'EUR overnight
    s(2) = r(2, 0) 'CAD overnight
    s(3) = r(3, 0) 'ZAR overnight
    ' redim field array
    ReDim f(0 To 1)
    f(0) = "SECURITY_NAME"
    f(1) = "PX_CLOSE_1D"
    r = b.getData(REFERENCE_DATA, s, f) ' <---- investigate array content
    '
    '
    '
    ' EXAMPLE 3 - retrieve daily historical data for all overnight indices between 31.5 - 24.6
    ReDim f(0 To 0)
    f(0) = "PX_CLOSE_1D"
    r = b.getData(HISTORICAL_DATA, s, f, "CALENDAR", "DAILY", CDate("31.5.2013"), CDate("24.6.2013")) ' <---- investigate array content
    '
    '
    '
    ' UPDATE 08.07.2013 -  (BCOM server returns data type BLPAPI_INT32, which will be converted into VBA Long data type)
    ' EXAMPLE 4 - retrieve current ask size for IBM US Equity
    ReDim s(0 To 0): s(0) = "IBM US Equity"
    f(0) = "ASK_SIZE"
    r = b.getData(REFERENCE_DATA, s, f) ' <---- investigate array content
    Debug.Print r(0, 0)
    '
    '
    ' release object
    Set b = Nothing
End Sub
'
'
'
'
' VBA Class module (Name=BCOM_wrapper)
Option Explicit
'
' public enumerator for request type
Public Enum ENUM_REQUEST_TYPE
    REFERENCE_DATA = 1
    HISTORICAL_DATA = 2
    BULK_REFERENCE_DATA = 3
End Enum
'
' constants
Private Const CONST_SERVICE_TYPE As String = "//blp/refdata"
Private Const CONST_REQUEST_TYPE_REFERENCE As String = "ReferenceDataRequest"
Private Const CONST_REQUEST_TYPE_BULK_REFERENCE As String = "ReferenceDataRequest"
Private Const CONST_REQUEST_TYPE_HISTORICAL As String = "HistoricalDataRequest"
'
' private data structures
Private bInputSecurityArray() As Variant
Private bInputFieldArray() As Variant
Private bOutputArray() As Variant
'
' BCOM objects
Private bSession As blpapicomLib2.Session
Private bService As blpapicomLib2.Service
Private bRequest As blpapicomLib2.REQUEST
Private bSecurityArray As blpapicomLib2.Element
Private bFieldArray As blpapicomLib2.Element
Private bEvent As blpapicomLib2.Event
Private bIterator As blpapicomLib2.MessageIterator
Private bIteratorData As blpapicomLib2.Message
Private bSecurities As blpapicomLib2.Element
Private bSecurity As blpapicomLib2.Element
Private bSecurityName As blpapicomLib2.Element
Private bSecurityField As blpapicomLib2.Element
Private bFieldValue As blpapicomLib2.Element
Private bSequenceNumber As blpapicomLib2.Element
Private bFields As blpapicomLib2.Element
Private bField As blpapicomLib2.Element
Private bDataPoint As blpapicomLib2.Element
'
' class non-object data members
Private bRequestType As ENUM_REQUEST_TYPE
Private bNumberOfDataPoints As Long
Private bCalendarType As String
Private bFrequency As String
Private bMaxDataPoints As Long
Private bStartDate As String
Private bEndDate As String
Private nSecurities As Long
Private nSecurity As Long
'
Public Function getData(ByVal requestType As ENUM_REQUEST_TYPE, _
ByRef securities() As Variant, ByRef fields() As Variant, _
Optional ByVal calendarType As String, Optional ByVal dataFrequency As String, _
Optional ByVal startDate As Date, Optional ByVal endDate As Date) As Variant()
    '
    bRequestType = requestType
    bInputSecurityArray = securities
    bInputFieldArray = fields
    '
    If (bRequestType = ENUM_REQUEST_TYPE.HISTORICAL_DATA) Then
        '
        bCalendarType = calendarType
        bFrequency = dataFrequency
        '
        If ((startDate = CDate(0)) Or (endDate = CDate(0))) Then _
            Err.Raise vbObjectError, "Bloomberg API", "Input parameters missing for historical data query"
        bStartDate = convertDateToBloombergString(startDate)
        bEndDate = convertDateToBloombergString(endDate)
    End If
    '
    openSession
    sendRequest
    catchServerEvent
    releaseObjects
    getData = bOutputArray
End Function
'
Private Function openSession()
    '
    Set bSession = New blpapicomLib2.Session
    bSession.Start
    bSession.OpenService CONST_SERVICE_TYPE
    Set bService = bSession.GetService(CONST_SERVICE_TYPE)
End Function
'
Private Function sendRequest()
    '
    Select Case bRequestType
        Case ENUM_REQUEST_TYPE.HISTORICAL_DATA
            ReDim bOutputArray(0 To UBound(bInputSecurityArray, 1), 0 To 0)
            Set bRequest = bService.CreateRequest(CONST_REQUEST_TYPE_HISTORICAL)
            bRequest.Set "periodicityAdjustment", bCalendarType
            bRequest.Set "periodicitySelection", bFrequency
            bRequest.Set "startDate", bStartDate
            bRequest.Set "endDate", bEndDate
            '
        Case ENUM_REQUEST_TYPE.REFERENCE_DATA
            Dim nSecurities As Long: nSecurities = UBound(bInputSecurityArray)
            Dim nFields As Long: nFields = UBound(bInputFieldArray)
            ReDim bOutputArray(0 To nSecurities, 0 To nFields)
            '
            Set bRequest = bService.CreateRequest(CONST_REQUEST_TYPE_REFERENCE)
            '
        Case ENUM_REQUEST_TYPE.BULK_REFERENCE_DATA
            ReDim bOutputArray(0 To UBound(bInputSecurityArray, 1), 0 To 0)
            Set bRequest = bService.CreateRequest(CONST_REQUEST_TYPE_BULK_REFERENCE)
            '
    End Select
    '
    Set bSecurityArray = bRequest.GetElement("securities")
    Set bFieldArray = bRequest.GetElement("fields")
    appendRequestItems
    bSession.sendRequest bRequest
End Function
'
Private Function appendRequestItems()
    '
    Dim nSecurities As Long: nSecurities = UBound(bInputSecurityArray)
    Dim nFields As Long: nFields = UBound(bInputFieldArray)
    Dim i As Long
    Dim nItems As Integer: nItems = getMax(nSecurities, nFields)
    For i = 0 To nItems
        If (i <= nSecurities) Then bSecurityArray.AppendValue CStr(bInputSecurityArray(i))
        If (i <= nFields) Then bFieldArray.AppendValue CStr(bInputFieldArray(i))
    Next i
End Function
'
Private Function catchServerEvent()
    '
    Dim bExit As Boolean
    Do While (bExit = False)
        Set bEvent = bSession.NextEvent
        If (bEvent.EventType = PARTIAL_RESPONSE Or bEvent.EventType = RESPONSE) Then
            '
            Select Case bRequestType
                Case ENUM_REQUEST_TYPE.REFERENCE_DATA: getServerData_reference
                Case ENUM_REQUEST_TYPE.HISTORICAL_DATA: getServerData_historical
                Case ENUM_REQUEST_TYPE.BULK_REFERENCE_DATA: getServerData_bulkReference
            End Select
            '
            If (bEvent.EventType = RESPONSE) Then bExit = True
        End If
    Loop
End Function
'
Private Function getServerData_reference()
    '
    Set bIterator = bEvent.CreateMessageIterator
    Do While (bIterator.Next)
        Set bIteratorData = bIterator.Message
        Set bSecurities = bIteratorData.GetElement("securityData")
        Dim offsetNumber As Long, i As Long, j As Long
        nSecurities = bSecurities.Count
        '
        For i = 0 To (nSecurities - 1)
            Set bSecurity = bSecurities.GetValue(i)
            Set bSecurityName = bSecurity.GetElement("security")
            Set bSecurityField = bSecurity.GetElement("fieldData")
            Set bSequenceNumber = bSecurity.GetElement("sequenceNumber")
            offsetNumber = CInt(bSequenceNumber.Value)
            '
            For j = 0 To UBound(bInputFieldArray)
                If (bSecurityField.HasElement(bInputFieldArray(j))) Then
                    Set bFieldValue = bSecurityField.GetElement(bInputFieldArray(j))
                    '
                    If (bFieldValue.DataType = BLPAPI_INT32) Then
                        bOutputArray(offsetNumber, j) = VBA.CLng(bFieldValue.Value)
                    Else
                        bOutputArray(offsetNumber, j) = bFieldValue.Value
                    End If
                End If
            Next j
        Next i
    Loop
End Function
'
Private Function getServerData_bulkReference()
    '
    Set bIterator = bEvent.CreateMessageIterator
    nSecurity = nSecurity + 1
    '
    Do While (bIterator.Next)
        Set bIteratorData = bIterator.Message
        Set bSecurities = bIteratorData.GetElement("securityData")
        Dim offsetNumber As Long, i As Long, j As Long
        Dim nSecurities As Long: nSecurities = bSecurities.Count
        '
        Set bSecurity = bSecurities.GetValue(0)
        Set bSecurityField = bSecurity.GetElement("fieldData")
        '
        If (bSecurityField.HasElement(bInputFieldArray(0))) Then
            Set bFieldValue = bSecurityField.GetElement(bInputFieldArray(0))
            '
            If ((bFieldValue.NumValues - 1) > UBound(bOutputArray, 2)) Then _
                ReDim Preserve bOutputArray(0 To UBound(bOutputArray, 1), 0 To bFieldValue.NumValues - 1)
            '
            For i = 0 To bFieldValue.NumValues - 1
                Set bDataPoint = bFieldValue.GetValue(i)
                bOutputArray(nSecurity - 1, i) = bDataPoint.GetElement(0).Value
            Next i
        End If
    Loop
End Function
'
Private Function getServerData_historical()
    '
    Set bIterator = bEvent.CreateMessageIterator
    Do While (bIterator.Next)
        Set bIteratorData = bIterator.Message
        Set bSecurities = bIteratorData.GetElement("securityData")
        Dim nSecurities As Long: nSecurities = bSecurityArray.Count
        Set bSecurityField = bSecurities.GetElement("fieldData")
        Dim nItems As Long, offsetNumber As Long, nFields As Long, i As Long, j As Long
        nItems = bSecurityField.NumValues
        If (nItems = 0) Then Exit Function
        If ((nItems > UBound(bOutputArray, 2))) Then _
            ReDim Preserve bOutputArray(0 To nSecurities - 1, 0 To nItems - 1)
        '
        Set bSequenceNumber = bSecurities.GetElement("sequenceNumber")
        offsetNumber = CInt(bSequenceNumber.Value)
        '
        If (bSecurityField.Count > 0) Then
            For i = 0 To (nItems - 1)
                '
                If (bSecurityField.Count > i) Then
                    Set bFields = bSecurityField.GetValue(i)
                    If (bFields.HasElement(bFieldArray(0))) Then
                        '
                        Dim d(0 To 1) As Variant
                        d(0) = bFields.GetElement(0).GetValue(0)
                        d(1) = bFields.GetElement(1).GetValue(0)
                        bOutputArray(offsetNumber, i) = d
                    End If
                End If
            Next i
        End If
    Loop
End Function
'
Private Function releaseObjects()
    '
    Set bFieldValue = Nothing
    Set bSequenceNumber = Nothing
    Set bSecurityField = Nothing
    Set bSecurityName = Nothing
    Set bSecurity = Nothing
    Set bSecurities = Nothing
    Set bIteratorData = Nothing
    Set bIterator = Nothing
    Set bEvent = Nothing
    Set bFieldArray = Nothing
    Set bSecurityArray = Nothing
    Set bRequest = Nothing
    Set bService = Nothing
    bSession.Stop
    Set bSession = Nothing
End Function
'
Private Function convertDateToBloombergString(ByVal d As Date) As String
    '
    ' convert date data type into string format YYYYMMDD
    Dim dayString As String: dayString = VBA.CStr(VBA.Day(d)): If (VBA.Day(d) < 10) Then dayString = "0" + dayString
    Dim MonthString As String: MonthString = VBA.CStr(VBA.Month(d)): If (VBA.Month(d) < 10) Then MonthString = "0" + MonthString
    Dim yearString As String: yearString = VBA.Year(d)
    convertDateToBloombergString = yearString + MonthString + dayString
End Function
'
Private Function getMax(ByVal a As Long, ByVal b As Long) As Long
    '
    getMax = a: If (b > a) Then getMax = b
End Function
'

Using MS Solver Foundation and C# in Excel with Excel-DNA

In one of my latest posts http://mikejuniperhill.blogspot.fi/2013/06/using-excel-solver-with-vba.html I was presenting my approach for using Frontline Solver in Excel with VBA. I was also mentioning, that I would like to get rid of linking my program with concrete ranges in Excel worksheet. Why? To be able to create convinient User Defined Functions (UDF) for solving optimization routines. I would like to have an optimization tool, in which I take all given parameters and data inside to a program and perform any desired optimization routines inside a program, without interacting with Excel ranges. Now it is a good time to present one such a tool - Microsoft Solver Foundation (MSF).

You can use MSF for creating a wide range of different types of optimization models in C# for example. From there, you can link your solver model back to Excel with Excel-DNA http://exceldna.codeplex.com/. This means, that you can set up your data and other parameters in Excel workbook, then use C# and MSF to perform desired optimization routine without interacting with Excel and finally, push the results back to Excel worksheet. In other words, you can create Excel UDF for solving optimization routines.

Let us first go through, what we are going to reach in this post:

1. We set up MSF to be used in Visual C# 2010 Express.
2. We create optimization model in C# by using MSF.
3. We set up Excel-DNA linking for C# program.
4. We create interface function for handling data between Excel and C# program.
5. We verify MSF model results against Excel Frontline Solver results.

So, let us get into it. First, open your Visual C# 2010 Express.

Microsoft Solver Foundation setup

First, we have to find MSF dll library file. We need to have Solver Foundation v3.0 - DLL only. I found it from here
http://archive.msdn.microsoft.com/solverfoundation/Release/ProjectReleases.aspx?ReleaseId=1799

When you have that dll file, we create a new C# Class Project in your Visual C# 2010 Express (File - New Project - Class Library - Name=MSF_Solver). Remember to save your project. Now, create reference to that MSF dll library file (Solution Explorer - References - Add Reference - Browse). If everything goes correctly, you should be able to build the following test program without any errors. This program is just for quick testing, that your MSF is ready for servicing.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SolverFoundation.Services;

namespace MSF_Solver
{
    public class ExcelFunctions
    {
        SolverContext solver = SolverContext.GetContext();
    }
}

I assume that everything went correctly without any errors in your build. At this stage, we have set up MSF to be used in our Visual Studio. One word about the tools: I am using Visual C# 2010 Express and having .NET Framework 4.0 as my Target framework.

The C# Program

Now we need to create the actual program, which will be using MSF to perform desired optimization routine. First, we create ISolver interface, from which all possible Solver models are going to be implemented. This interface is having only one method (solve) and it returns Dictionary data structure (string, double). In your Visual Studio, add new Interface to your project (Solution Explorer - MSF_Solver Project - Add - Add New Item - Interface - Name=ISolver). You can copy-paste the following program into this interface.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace MSF_Solver
{
    public interface ISolver
    {
        Dictionary<string, double> solve();
    }
}

Next, we need to create an implementation for this interface. Add a new Class to your project (Solution Explorer - MSF_Solver Project - Add - Add New Item - Interface - Name=PolynomialFitting). You can copy-paste the following program into this class.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SolverFoundation.Services;
//
namespace MSF_Solver
{
    //
    // PolynomialFitting implementation for ISolver interface is fitting nth degree polynomial 
    // approximation function to a given set of data points by using ordinary least squares method
    public class PolynomialFitting : ISolver
    {
        private Dictionary<double, double> curve; // data points to be fitted (x-value, y-value)
        private int degree; // define degree for polynomial function
        //
        public PolynomialFitting(Dictionary<double, double> curve, int degree)
        {
            this.curve = curve;
            this.degree = degree;
        }
        //
        // ISolver interface method implementation
        public Dictionary<string, double> solve()
        {
            // create solver model
            SolverContext solver = SolverContext.GetContext();
            solver.ClearModel();
            Model model = solver.CreateModel();
            //
            // create decision variables for solver model, based on given polynomial degree
            Dictionary<string, double> coefficients = new Dictionary<string, double>();
            for (int i = 0; i <= degree; i++)
            {
                coefficients.Add("b" + i.ToString(), 0.0);
            }
            //
            // add decision variables to solver model
            var decision = coefficients.Select(it => new Decision(Domain.Real, it.Key));
            model.AddDecisions(decision.ToArray());
            //
            // create objective function by using SumTermBuilder
            var objectiveFunction = new SumTermBuilder(curve.Count);
            //
            // itarate through all given data points
            foreach (KeyValuePair<double, double> kvp in curve)
            {
                // create term object incrementally for squared error (y estimate - y actual)^2
                Term subTerm = 0;
                for (int i = 0; i < model.Decisions.Count(); i++)
                {
                    // y-estimate = (b0 * maturity^0 + b1 * maturity^1 + ... + bn * maturity^n)
                    subTerm += model.Decisions.ElementAt(i) * Math.Pow(kvp.Key, i);
                }
                subTerm -= kvp.Value; //  subterm = (y estimate - y actual) = e
                subTerm *= subTerm; // subterm = e^2
                objectiveFunction.Add(subTerm); // add subterm to objectiveFunction (SumTermBuilder)
            }
            //
            // define optimization type and give objective function SUM(e^2) to be minimized
            model.AddGoal("SumOfSquaredErrors", GoalKind.Minimize, objectiveFunction.ToTerm());
            //
            // solve model and transfer results (optimized decision variables) from 
            // model into a dictionary object which will be returned for the caller
            Solution solution = solver.Solve();
            Dictionary<string, double> parameters = new Dictionary<string, double>();
            foreach (Decision parameter in model.Decisions)
            {
                parameters.Add(parameter.Name, parameter.ToDouble());
            }
            return parameters;
        }
    }
}

The previous class takes in a given set of data points (x, y) inside Dictionary data structure, along with the information about the required polynomial degree of the approximation function to be fitted with data. Relevant comments are included inside the program. More information on MSF can be found with Google. Also, Here are two excellent hands-on examples on using MSF from Mathias Brandewinder: 
http://www.clear-lines.com/blog/post/First-steps-with-the-Microsoft-Solver-Foundation.aspx http://www.clear-lines.com/blog/post/Create-optimization-programs-dynamically-with-C-and-the-Microsoft-Solver-Foundation.aspx

At this point, we have created our desired optimization model in C# by using MSF. Next, we need to set up Excel-DNA for linking our C# program with Excel Worksheet.

Excel-DNA part

First we have to find Excel-DNA files. I was able to find these from here http://exceldna.codeplex.com/ by going to Downloads. I have been using ExcelDna-0.29 so far, but there is now version 0.30 available, as I am writing this post. Download zip file and unzip the content into some appropriate folder.

Next, in our C# program, we need to create reference to Excel-DNA dll file (Solution Explorer - References - Add Reference - Browse). You should find ExcelDna.Integration.dll file. In Properties window, mark this reference as Copy Local = False.

Next, Add a new file (dna file) to the project (Solution Explorer - MSF_Solver Project - Add - Add New Item - Text File - Name=MSF_Solver.dna) with the following content:

<DnaLibrary Name="MSF_Solver" RuntimeVersion="v4.0">
  <ExternalLibrary Path="MSF_Solver.dll" />
</DnaLibrary>

In the properties for this file, set that this file will be copied to the Output directory. Set Copy to Output Directory = Copy if newer.

Then, create a copy of the ExcelDna.xll file (you can find this xll file in your unzipped Excel-Dna folder) into the project directory, and rename it to be MSF_Solver.xll. Add this file to the project (Solution Explorer - MSF_Solver Project - Add - Add Existing Item - MSF_Solver.xll), and in the properties for this file, ensure that it will be copied to the Output directory. Set Copy to Output Directory = Copy if newer.

After these important settings, build your solution. I assume everything has gone well to this point. Setting up Excel-DNA is not difficult, but still one needs to be very careful. One slip in settings and you will spend a lot of time with your compiler, wondering why this is not working. So, be careful out there.

Detailed instructions for setting up Excel-DNA functionalities in Visual Studio can be found from here http://exceldna.codeplex.com/documentation. Find the following Word documentation Excel-DNA - Step-by-step C# add-in.doc In that documentation, find the section Creating a new add-in library in C#. The process of setting up Excel-DNA has been described in a very detailed way in that section, along with some screen captures.

At this point, we have set up Excel-DNA files for linking our C# program with Excel Workbook. Next, we are going to create interface function for handling data between Excel and C# program.

Interface function between Excel and C#

At this point, you should have that one Class (ExcelFunctions) existing in your current project (the one, in which we tested MSF originally). You can copy-paste the following code into that class.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ExcelDna.Integration;

namespace MSF_Solver
{
    public class ExcelFunctions
    {
        [ExcelFunction(Category="Excel-DNA functions", Description = "Coefficients for nth degree polynomial curve fit to a given set of data points by using OLS method")]
        public static object[,] PolynomialCurveFitting([ExcelArgument(Description = "2-dimensional array of (x, y) values")] Object[,] curve, [ExcelArgument(Description = "Degree for polynomial function")] int degree)
        {
            // number of data points for a given curve is dynamic
            int rows = curve.GetUpperBound(0);
            //
            // read a given curve data into a dictionary
            Dictionary<double, double> dict = new Dictionary<double, double>();
            for (int i = 0; i <= rows; i++)
            {
                dict.Add((double)curve.GetValue(i, 0), (double)curve.GetValue(i, 1));
            }
            //
            // create appropriate solver model, perform curve fitting and receive optimized coefficients
            ISolver solver = new PolynomialFitting(dict, degree);
            Dictionary<string, double> coefficients = solver.solve();
            //
            // create array for output
            // key is coefficient name (string) and value is coefficient value (double)
            object[,] arr = new object[coefficients.Count , 2];
            //
            for (int i = 0; i < coefficients.Count; i++)
            {
                arr[i, 0] = (string)coefficients.ElementAt(i).Key;
                arr[i, 1] = (double)coefficients.ElementAt(i).Value;
            }
            return arr;
        }
    }
}

First, PolynomialCurveFitting method is receiving data as 2-dimensional object array from Excel. The program then reads that data into a Dictionary data structure. This Dictionary is a feed for ISolver implementation (solver), which performs the optimization part and returns optimized coefficients inside a new Dictionary back to this method (coefficients). Finally, the content of this result Dictionary is read into a new object array (arr) and returned back to Excel.

Now, build your fully completed project. At this stage, you should have the following four files in your Project directory (MSF_Solver\bin\Debug):

1. MSF_Solver.dll
2. MSF_Solver.dna
3. MSF_Solver.pdb
4. MSF_Solver.xll

Program test run - curve fitting

We test our program and verify our MSF model results against Frontline Solver results. First, set up the following swap curve data into a new Excel workbook.

0,08	0,19
0,25	0,27
0,5	0,29
1	0,35
2	0,51
5	1,38
10	2,46
20	3,17
30	3,32

While your Excel is open, go to your Project folder (MSF_Solver\bin\Debug) and double-click MSF_Solver.xll file. You may not notice anything, but behind the scenes your xll file is loaded into your Excel Workbook. If something has gone wrong, you will get an error message at this point.

Now, let us perform a curve fitting for that data with second degree polynomial function. We are going to have three output coefficients from the program (b0, b1, b2). Our C# method is returning the name of the coefficient and its optimized value. All in all, we are going to receive 3x2 output variant array in this example case. So, when using PolynomialCurveFitting function in Excel, remember to select first 3x2 array in your worksheet before creating your function formula and then press CTRL+SHIFT+ENTER to get the result array from C#.

Below here is my test run results. I have been testing this program also for other degree polynomial approximations and it seems to be working well at least against Excel Solver. We could make preliminary conclusion, that our C# MSF solver model is working as required.

MSF solver			Excel solver
b0	0,1434		b0	0,1434
b1	0,2684		b1	0,2684
b2	-0,0055		b2	-0,0055

Before publishing this post, I have been following all my instructions described on this posting, re-created this program and used PolynomialCurveFitting function in Excel successfully two times. So, if you follow all the instructions carefully, you should get the working program.

Some Afterthoughts

Let us face one fact: people in banks are accustomed with Excel and they want to use it. Excel is - and probably will be for a long time - the daily workhorse. Tools like Excel-DNA can open up a lot of new doors what we can do in Excel behind the scenes. Personally I find this approach of creating a program completely outside Excel (VBA) and linking that program back to be used in Excel, to be a wonderful tool. It feels a bit like using new Rolls Royce engine in your old Volkswagen.

My personal Thank You this time goes directly to Govert Van Drimmelen, who AFAIK has developed Excel-DNA pretty much on his own. What this world really would be without such innovators? Thank You also for Mathias Brandewinder and his clear-lines.com blog for those two excellent articles on MSF. Also, Thank You for MSF team for developing such a great optimization tool.

And finally, Thank You for reading this blog again. I really hope that you have got something out from this. Have a great midsummer day!
-Mike

Implementing binomial solver design in VBA

In this long post, I will open up my current implementation for binomial option solver. The reader is expected to be familiar and comfortable with theory of pricing option by using binomial model. If you feel a bit rusty with the topic, you can get some refreshing overview from here, for example: http://en.wikipedia.org/wiki/Binomial_options_pricing_model 

Let us say, that we would like to create a program to price options by using binomial model, but keep everything as flexible as possible. This means, that we have to abandon the idea of creating one big monolithic function (traditional VBA approach). Anyway, what do we need to accomplish this, and how could we create such a design?

Components

1) Parameters source - this is a "place", from which we read in all option-related parameters. We are not hard-coding anything, but instead we are going to create interface IOptionFactory for different possible data sources. IOptionFactory has only two public methods: createOptionParameters and getOptionParameters. Hereby, it is only a container for all option-related parameters needed in this design.

In essence, we could create different interface implementations for reading parameters from system database or text file, for example. However, In this example our IOptionFactory implementation is going to be ExcelFactory class, which reads parameters from Excel Worksheet into optionParameters parameter wrapper.

Parameter wrapper is Dictionary data structure, into which we save all needed parameters in this program (remember to reference Microsoft Scripting Runtime library). For parameter wrapper, we need to have public Enumerator for all field key values used in parameter wrapper. If you have no idea what I am explaining here, check out my post http://mikejuniperhill.blogspot.fi/2013/05/handling-parameters-dynamically-with.html

2) Data structure - this is a data structure (Tree) for storing data (the actual binomial tree structure). For this purpose, we are simply wrapping jagged array (array of arrays) into a separate class. This class has only the most vital functionalities, such as access to Tree items (nodes) and information about number of Tree periods and number of states in each period. Hereby, Tree class is only a data container and for any operations to be performed on items of this container, we create separate iterator interface ITreeIterator.

By having data and algorithms separately means, that we can create new iterator implementations for operating on Tree structure nodes in a different way and we do not need to change anything in Tree class. Moreover, we can always replace any existing iterator in our design, just by plugging in a new iterator and there is no need to break the existing program design. I would say, that these benefits will clearly override the costs of implementing this scheme.

So, ITreeIterator interface has all the needed methods for performing operations on Tree nodes. The next question is, what are those operations? If we think about the pricing option with binomial method, these operations could be the following:

1. Forward iteration - for creating the actual spot Tree.
2. Terminal payoff calculation - for calculating option payoffs for each state at the maturity date of the option.
3. Backward iteration - for discounting option payoffs and calculating option payoffs on each node from maturity to present date.

Creating a spot tree (forward iteration) is basically quite straightforward process. Calculating payoffs at maturity and backward iteration (option valuation parts) can be a bit more tricky issue, depending on option type. Since our iterator is an implementation, for backward iterating process we could implement different iterators, such as American iterator or Bermudan iterator for example. We can also change Payoff function to calculate any possible payoff, since it is a separate object what we are feeding to our iterator. Example Implementation given in this program is European iterator (EuropeanTreeIterator).

3) Payoff function - option payoff structure is going to be implemented also as an interface (IOneFactorPayoff). Along with its init method (artificial constructor), it has only one public method - getPayoff, which calculates option payoff for a given spot price. Example implementation is for vanilla call option (VanillaCallPayoff).

4) Binomial process parameters - as we know, there are a lot of different models for creating binomial trees. We want to leave an option for the user to use different binomial models. For this reason, we create interface ILatticeStrategy. This interface has only one public method - init (artificial constructor), which takes in parameter wrapper as argument. The purpose of this method is to create binomial process-related parameters (u, d and p) and save these back into parameter wrapper. In this example, we implement Cox-Ross-Rubinstein model without drift (CRRNoDrift).

Program flow

Now, how on earth do we manage all this mess, what I have just described? I admit, that this design candidate might feel overly complex - at first. However, after some further investigations you should see, that it is actually pretty straightforward. Well, of course not as straightforward as that traditional monolithic VBA function, but our "extra complexity" is not there without some very good reasons. Let us talk about these reasons later in our afterthoughts section. At this moment, let us try to get some sense about this design by looking our test program first.

Option Explicit
'
Sub Tester()
    '
    ' create option parameters in option factory
    Dim optionFactory As IOptionFactory: Set optionFactory = New ExcelFactory
    optionFactory.createOptionParameters
    '   
    ' create option payoff object
    Dim payoff As IOneFactorPayoff: Set payoff = New VanillaCallPayoff
    payoff.init optionFactory.getOptionParameters
    '
    ' create process type for creating spot tree
    Dim latticeStrategy As ILatticeStrategy: Set latticeStrategy = New CRRNoDrift
    latticeStrategy.init optionFactory.getOptionParameters
    '
    ' create iterator for traversing tree structure
    Dim latticeIterator As ITreeIterator: Set latticeIterator = New EuropeanTreeIterator
    latticeIterator.init payoff, optionFactory.getOptionParameters
    '
End Sub

As we can see, ExcelFactory is creating all option-related parameters into parameter wrapper in the first stage. Then, we create VanillaCallPayoff and feed it with parameter wrapper which is "centrally hosted" by ExcelFactory. After this, we create CRRNoDrift and use it for calculating binomial process parameters, by feeding it with parameter wrapper. Finally, we create EuropeanTreeIterator and feed it with parameter wrapper and VanillaCallPayoff function. It should be noted, that iterator has the actual Tree data structure aggregated inside it. Let us go forward.

Option Explicit
'
Sub Tester()
    '
    ' create option parameters in option factory
    Dim optionFactory As IOptionFactory: Set optionFactory = New ExcelFactory
    optionFactory.createOptionParameters
    '
    ' create option payoff object
    Dim payoff As IOneFactorPayoff: Set payoff = New VanillaCallPayoff
    payoff.init optionFactory.getOptionParameters
    '
    ' create process type for creating spot tree
    Dim latticeStrategy As ILatticeStrategy: Set latticeStrategy = New CRRNoDrift
    latticeStrategy.init optionFactory.getOptionParameters
    '
    ' create iterator for traversing tree structure
    Dim latticeIterator As ITreeIterator: Set latticeIterator = New EuropeanTreeIterator
    latticeIterator.init payoff, optionFactory.getOptionParameters
    '
    ' create solver which uses parameters and process to calculate option value
    Dim binomialSolver As New BinomialMethod
    binomialSolver.init latticeIterator
    Debug.Print binomialSolver.getPrice(2.614)    '
End Sub

We create class called BinomialMethod for technically hosting our EuropeanIterator implementation class. This class has init method (artificial constructor) and method getPrice method, which uses iterator to perform forward iteration (create binomial tree), calculate terminal payoffs (calculate option payoffs at maturity) and perform backward iteration (discount payoffs along the tree to current date). Finally, it returns the present value of the option for its caller (Tester).

Interfaces, Classes and Tester program

All classes mentioned above, have been presented here below. You can copy-paste these into your VBA project for testing (remember to reference Microsoft Scripting Runtime library in VB editor).

Tree data structure class. Copy into VBA Class Module (Name = Tree)

Option Explicit
'
' ZERO-INDEXED data structure (array of arrays)
' example indexing access: period 2, state 1 = outer(2)(1)
Private outer() As Variant
Private dt As Double
'
Public Function init(ByVal timeInYears As Double, ByVal numberOfPeriods As Long)
    '
    ' init function serves as artificial constructor
    ' create tree structure having n periods
    dt = (timeInYears / numberOfPeriods)
    ReDim outer(0 To numberOfPeriods)
    '
    Dim i As Long
    For i = 0 To numberOfPeriods
        Dim inner() As Double
        ReDim inner(0 To i)
        outer(i) = inner
    Next i
End Function
'
Public Function push(ByVal period As Long, ByVal state As Long, ByVal value As Double)
    ' setter function
    outer(period)(state) = value
End Function
'
Public Function at(ByVal period As Long, ByVal state As Long) As Double
    ' getter function
    at = outer(period)(state)
End Function
''
Public Function n_periods() As Long
    ' return number of periods in tree structure, minus 1
    n_periods = UBound(outer, 1)
End Function
'
Public Function n_states(ByVal period As Long) As Long
    ' return number of states within a periods, minus 1
    Dim stateArray() As Double: stateArray = outer(period)
    n_states = UBound(stateArray)
End Function
'
Public Function t_at(ByVal period As Long) As Double
    ' get time in years for a node
    t_at = dt * period
End Function
'

Tree iterator interface. Copy into VBA Class Module (Name = ITreeIterator)

Option Explicit
'
Private lattice As Tree
'
Public Function forward(ByVal spot As Double)
End Function
'
Public Function backward()
End Function
'
Public Function terminalPayoff()
End Function
'
Public Function init(ByRef oneFactorPayoff As IOneFactorPayoff, ByRef parameters As Scripting.Dictionary)
End Function
'
Public Function getLattice() As Tree
End Function
'

One possible implementation for Tree iterator interface. Copy into VBA Class Module (Name = EuropeanTreeIterator)

Option Explicit
'
Implements ITreeIterator
'
Private payoff As IOneFactorPayoff
Private p As Scripting.Dictionary
Private lattice As Tree
'
Private Function ITreeIterator_forward(ByVal spot As Double)
    '
    ' get process-related parameters for filling the tree
    Dim u As Double: u = p.Item(E_UP)
    Dim d As Double: d = p.Item(E_DOWN)
    lattice.push 0, 0, spot ' initialize index (0,0) to be the user-given spot price
    '
    ' create spot tree from 0 to n
    Dim i As Long, j As Long, periods As Long
    periods = lattice.n_periods
    '
    For i = 1 To periods
        For j = 0 To (lattice.n_states(i) - 1)
            lattice.push i, j, lattice.at(i - 1, j) * d
            lattice.push i, j + 1, lattice.at(i - 1, j) * u
        Next j
    Next i
End Function
'
Private Function ITreeIterator_backward()
    '
    ' modify this - node-to-node iterating is not required (use binomial probabilities)
    ' transform spot tree to option tree from n to 0 (index 0,0 is the option value)
    ' get discount factor
    Dim df As Double: df = VBA.Exp(-p.Item(E_RATE) * (p.Item(E_TIME) / p.Item(E_PERIODS)))
    Dim w As Double: w = p.Item(E_PROBABILITY)
    Dim q As Double: q = (1 - w)
    '
    ' re-calculate option tree backwards from n to 0
    Dim i As Long, j As Long, periods As Long
    periods = lattice.n_periods
    '
    For i = periods To 0 Step -1
        For j = (lattice.n_states(i) - 1) To 0 Step -1
            '
            Dim value As Double
            value = (w * (lattice.at(i, j + 1)) + q * (lattice.at(i, j))) * df
            lattice.push i - 1, j, value
        Next j
    Next i
End Function

Private Function ITreeIterator_getLattice() As Tree
    Set ITreeIterator_getLattice = lattice
End Function

Private Function ITreeIterator_init(ByRef oneFactorPayoff As IOneFactorPayoff, _
ByRef parameters As Scripting.Dictionary)
    '
    Set payoff = oneFactorPayoff
    Set p = parameters
    Set lattice = New Tree: lattice.init p.Item(E_TIME), p.Item(E_PERIODS)
End Function
'
Private Function ITreeIterator_terminalPayoff()
    '
    ' calculate terminal payoffs for a tree at maturity
    Dim j As Long, periods As Long
    periods = lattice.n_periods
    '
    For j = (lattice.n_states(periods)) To 0 Step -1
        '
        Dim terminalValue As Double
        terminalValue = payoff.getPayoff(lattice.at(periods, j))
        lattice.push periods, j, terminalValue
    Next j
End Function
'

Lattice strategy interface. Copy into VBA Class Module (Name = ILatticeStrategy)

Option Explicit
'
Public Function init(ByRef parameters As Scripting.Dictionary)
End Function
'

One possible implementation for Lattice strategy interface. Copy into VBA Class Module (Name = CRRNoDrift)

Option Explicit
'
' this class implements Cox, Ross and Rubinstein model with no drift factor
Implements ILatticeStrategy
'
Private p As Scripting.Dictionary
'
Private Function ILatticeStrategy_init(ByRef parameters As Scripting.IDictionary)
    '
    ' init parameter dictionary
    Set p = parameters
    '
    ' calculate process-related parameters into parameters
    Dim dt As Double: dt = p.Item(E_TIME) / p.Item(E_PERIODS)
    '
    ' calculate up and down factors into parameters
    p.Item(E_UP) = VBA.Exp(p.Item(E_VOLATILITY) * VBA.Sqr(dt))
    p.Item(E_DOWN) = VBA.Exp(-p.Item(E_VOLATILITY) * VBA.Sqr(dt))
    '
    ' calculate risk-neutral probability factor into parameters
    p.Item(E_PROBABILITY) = ((VBA.Exp(p.Item(E_RATE) * dt) - p.Item(E_DOWN)) / (p.Item(E_UP) - p.Item(E_DOWN)))
End Function
'

Payoff interface. Copy into VBA Class Module (Name = IOneFactorPayoff)

Option Explicit
'
Public Function getPayoff(ByVal spot As Double) As Double
End Function
'
Public Function init(ByRef parameters As Scripting.Dictionary)
End Function
'

One possible implementation for payoff interface. Copy into VBA Class Module (Name = VanillaCallPayoff).

Option Explicit
'
' one factor vanilla call option payoff
Implements IOneFactorPayoff
'
Private x As Double
'
Private Function IOneFactorPayoff_getPayoff(ByVal spot As Double) As Double
    IOneFactorPayoff_getPayoff = maxPayoff(0, spot - x)
End Function
'
Private Function IOneFactorPayoff_init(ByRef parameters As Scripting.Dictionary)
    x = parameters.Item(E_STRIKE)
End Function
'
Private Function maxPayoff(ByVal a As Double, ByVal b As Double) As Double
    '
    maxPayoff = b
    If (a > b) Then maxPayoff = a
End Function
'

Binomial method class. Copy into VBA Class Module (Name = BinomialMethod).

Option Explicit
'
Private it As ITreeIterator 
'
Public Function init(ByRef iterator As ITreeIterator)
    '
    ' artificial constructor
    Set it = iterator
End Function
'
Public Function getPrice(ByVal spot As Double) As Double
    '
    ' this function builds tree and iterates it forward and backward to calculate option value
    it.forward spot ' create spot tree
    it.terminalPayoff ' calculate all payoffs at maturity
    it.backward ' calculate option value
    getPrice = it.getLattice.at(0, 0)
End Function
'

Interface for option factory. Copy into VBA Class Module (Name = IOptionFactory).

Option Explicit
'
Public Function createOptionParameters()
End Function
'
Public Function getOptionParameters() As Scripting.Dictionary
End Function
'

One possible implementation of option factory. Copy into VBA Class Module (Name = ExcelFactory).

Option Explicit
'
' class reads parameters data from specific excel worksheet
Implements IOptionFactory
'
Private optionParameters As Scripting.Dictionary ' data structure to hold all needed option parameters
'
Private Function IOptionFactory_createOptionParameters() As Variant
    '
    Set optionParameters = New Scripting.Dictionary
    Dim r As Range: Set r = Sheets("Sheet1").Range("D2:D6")
    '
    optionParameters.Item(E_STRIKE) = VBA.CDbl(r(1, 1))
    optionParameters.Item(E_VOLATILITY) = VBA.CDbl(r(2, 1))
    optionParameters.Item(E_TIME) = VBA.CDbl(r(3, 1))
    optionParameters.Item(E_RATE) = VBA.CDbl(r(4, 1))
    optionParameters.Item(E_PERIODS) = VBA.CLng(r(5, 1))
    '
    Set r = Nothing
    End Function
'
Private Function IOptionFactory_getOptionParameters() As Scripting.IDictionary
    Set IOptionFactory_getOptionParameters = optionParameters
End Function
'

Then we also need that Enumerator for our parameter wrapper. Copy into VBA Standard Module.

Option Explicit
'
Public Enum PRM
    '
    ' process-related parameters (calculated by ILatticeStrategy implementation)
    E_UP = 1
    E_DOWN = 2
    E_PROBABILITY = 3
    '
    ' option-related parameters (created by IOptionFactory implementation)
    E_RATE = 4
    E_STRIKE = 5
    E_VOLATILITY = 6
    E_PERIODS = 7
    E_TIME = 8
    '
End Enum
'

Program example

We create our tester program for vanilla equity call option, which is not paying any cashflows. Set the following data into Excel Worksheet. Make sure, that the range reference in ExcelFactory class is referring to this range in your Excel.

parameter	value
strike	2,600
vol	42,9 %
time	0,271
rate	0,3 %
periods	250

Note, that in this design we are giving spot value to BinomialMethod object as an argument in its getPrice method. Below here is the actual tester program. Copy-paste it into VBA Standard Module.

Option Explicit
'
Sub Tester()
    '
    ' create option parameters in option factory
    Dim optionFactory As IOptionFactory: Set optionFactory = New ExcelFactory ' can be switched at runtime!
    optionFactory.createOptionParameters
    '
    ' create option payoff object
    Dim payoff As IOneFactorPayoff: Set payoff = New VanillaCallPayoff ' can be switched at runtime!
    payoff.init optionFactory.getOptionParameters
    '
    ' create process type for creating spot tree
    Dim latticeStrategy As ILatticeStrategy: Set latticeStrategy = New CRRNoDrift ' can be switched at runtime!
    latticeStrategy.init optionFactory.getOptionParameters
    '
    ' create iterator for traversing tree structure
    Dim latticeIterator As ITreeIterator: Set latticeIterator = New EuropeanTreeIterator ' can be switched at runtime!
    latticeIterator.init payoff, optionFactory.getOptionParameters
    '
    ' create solver which uses parameters and process to calculate option value
    Dim binomialSolver As New BinomialMethod
    binomialSolver.init latticeIterator
    Debug.Print binomialSolver.getPrice(2.614)
    '
    ' object releasing tasks
    Set binomialSolver = Nothing
    Set latticeIterator = Nothing
    Set latticeStrategy = Nothing
    Set payoff = Nothing
    Set optionFactory = Nothing
End Sub
'

Some afterthoughts

First of all, I got my valuation for this equity option (NOK1V FH, September 13 Call, 2.6 strike) to be approximately 0.24 today when the spot was on 2.614. I confirmed this valuation to be close enough to the market by using Bloomberg OMON<GO> function.

So, what is so great about this design after all? What is the reason, why we have to have all this complexity? When we investigate that tester program, we can realize, that the following "components" can be switched at will - meaning, that we could create a new implementation for these, without breaking our existing design:

1) the source from which we create option-related parameters (Excel, txt file, database, etc)
2) payoff function (vanilla put, digital call, etc)
3) process for creating binomial tree parameters (CRR, JR, etc)
4) iterator, which calculates payoffs from binomial tree (european, american, etc)

This example program hopefully shows, that it is possible to create very flexible and extendable designs in VBA by using Interface implementation mechanism and a couple of other tricks presented in this post (parameter wrapper). Some of the components employed in this example are also quite generic in nature. We could use Tree data structure and its iterator, when creating a program for building short-term interest rate trees, for example.

My thanks about some of the central ideas presented here belongs to Daniel Duffy for his inspiring C++ design pattern example papers and C++ book chapter on this particular topic.

Well, it is time to say goodbye again. First of all, a big hats off for you, if you really have gone through this posting. Thank You, I hope you could get a bit of some idea from it.
-Mike