Excel : implementing multithreading using C# and Excel-DNA

Multithreading possibilities for VBA has been chewed in this post. My conclusion back then was, that there is no multi-threading possibilities for VBA. One reader has then been commenting this as follows :

"Correct to say there is no multithreading in VBA however, you can very easily create your multithreading functionality in a c# dll and expose the interface via Interops for VBA to consume. Then all you need to do is to pass in the data into the function and walla, you have multithreading. Interops is a very powerful feature and VBA fully supports it."

For the sake of completeness, I will present one possible program to implement such multithreading scheme for Excel, by using Parallel class for the actual multi-threading part and Excel-DNA for interfacing the program back to Excel (using Excel only as Input-Output platform). Excel interfacing part used in this post, has already been completely chewed within this post. Note, that by using instructions given in here or here, one can relatively easy interface this multithreading program back to VBA, instead of Excel.

The program

This unsurprising program is simply processing a task (simulate N amount of normally distributed random numbers) shared for M processors. Amount of simulated numbers and number of processors used is decided by the client (Excel). Finally, the program will print the aggregated time consumed for this task back to Excel. Needless to say, the effect of multithreading can be demonstrated by changing the number of processors in Excel worksheet.

Copy-Paste the following program presented below into a new C# project. Also, follow carefully all instructions given in this post.

Thanks for reading this blog.
-Mike

// ExcelInterface.cs
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Threading.Tasks;
using System.Windows.Forms;
using ExcelDna.Integration;

namespace ExcelInterface {
    // type definition, delegate for exporting data from processor to queue
    using Processor = Action<int, DataMessenger>;
    public delegate void DataMessenger(List<double> result);

    public static class ExcelInterface {
        // method available in excel
        public static void execute() {
            try {
                // create Excel application object
                dynamic Excel = ExcelDnaUtil.Application;
                //
                // get parameters from worksheet named ranges
                int n = (int)Excel.Range["_n"].Value2;
                int nProcessors = (int)Excel.Range["_nProcessors"].Value2;
                int nPerProcessor = n / nProcessors;
                // create data thread-safe queue and random processors
                RandomQueue messageQueue = new RandomQueue();
                List<Processor> processors = new List<Processor>();
                for (int i = 0; i < nProcessors; ++i) {
                    processors.Add(Factory.CreateProcessor());
                }
                // host timer, execute all processors in parallel
                Stopwatch timer = new Stopwatch();
                timer.Start();
                Parallel.For(0, nProcessors, i => {
                    processors[i].Invoke(nPerProcessor, messageQueue.Enqueue); 
                });
                timer.Stop();
                //
                // print processing time to excel
                double timeElapsed = timer.Elapsed.TotalSeconds;
                Excel.Range["_timer"] = timeElapsed;
            }
            catch (Exception e) {
                MessageBox.Show(e.Message);
            }
        }
    }
}

// RandomQueue.cs
using System;
using System.Collections.Concurrent;
using System.Collections.Generic;

namespace ExcelInterface {
    // wrapper class for ConcurrentQueue data structure
    public class RandomQueue {
        private ConcurrentQueue<List<double>> randomQueue;
        public RandomQueue() {
            randomQueue = new ConcurrentQueue<List<double>>();
        }
        // add
        public void Enqueue(List<double> data) {
            randomQueue.Enqueue(data);
        }
        // remove
        public List<double> Dequeue() {
            List<double> data = null;
            bool hasValue = false;
            while (!hasValue) hasValue = randomQueue.TryDequeue(out data);
            return data;
        }
    }
}

// Factory.cs
using System;
using System.Collections.Generic;

namespace ExcelInterface {
    // type definition
    using Processor = Action<int, DataMessenger>;
    
    public static class Factory {
        // factory method for creating processor for random generator
        public static Processor CreateProcessor() {
            //
            // create standard normal variable generator
            // using action delegate and lambda expression
            Processor process =
            (int n, DataMessenger dataMessenger) => {
                Random generator = new Random(Guid.NewGuid().GetHashCode());
                List<double> normalRandomList = new List<double>();
                // loop through n
                for (int i = 0; i < n; ++i) {
                    double uniformRandomSum = 0.0;
                    // create standard normal random variable using CLT
                    for (int j = 0; j < 12; ++j) {
                        double uniformRandom = 0.0;
                        while (true) {
                            uniformRandom = generator.NextDouble();
                            if (uniformRandom > 0.0) break;
                        }
                        uniformRandomSum += uniformRandom;
                    }
                    normalRandomList.Add(uniformRandomSum - 6.0);
                }
                // send list of normal random variables to queue
                dataMessenger(normalRandomList);
            };
            return process;
        }
    }
}

C# : flexible design for processing market data

Third-party analytics softwares will usually require a full set of market data to be feeded into system, before performing any of those precious high intensity calculations. Market data (curves, surfaces, fixing time-series, etc.) has to be feeded into system following some specific file configurations. Moreover, source data might have to be collected from several different vendor sources. Needless to say, the process can easily turn into a semi-manageable mess involving Excel workbooks and a lot of manual processing, which is always a bad omen.

For this reason, I finally ended up creating one possible design solution for flexible processing of market data files. I have been going through some iterations starting with Abstract Factory, before landing with the current one using Delegates to pair data and algorithms. With the current solution, I start to feel quite comfortable already.

UML

Each market data point (such as mid USD swap rate for 2 years) is presented as a RiskFactor object. All individual RiskFactor objects are hosted in a list inside generic MarketDataElements<T> object, which enables hosting any type of data. MarketDataElements<T> object itself is hosted by static BaseMarket class.

The actual algorithms needed for creating any type of vendor data are captured in static ProcessorLibrary class. During the processing task, MarketDataElements<T> object will be handled for a specific library method implementation, which will then request values from vendor source for all involved RiskFactor objects. In the example program, ProcessorLibrary has a method for processing RiskFactor objects using Bloomberg market data API. This specific method is then using DummyBBCOMMWrapper class for requesting values for a RiskFactor object.

For the purpose of pairing specific data (MarketDataElements<T>) and specific algorithm (ProcessorLibrary), BaseMarket class is hosting a list of ElementProcessor objects as well as a list of Delegates bound with specific methods found in ProcessorLibrary. For the processing task, each ElementProcessor object is feeded with delegate method for specific ProcessorLibrary implementation method and information on MarketDataElements<T> object.

Finally, (not shown in UML) program is also using static TextFileHandler class for handling text files and static Configurations class for hosting hardcoded configurations, initially read from App.config file.

Files

 

App.config

CSV for all RiskFactor object configurations
Fields (matching with RiskFactor object properties) :

• Data vendor identification string, matching with the one given in configuration file
• Identification code (ticker) for a market data element, found in the system for which the data will be created
• Vendor ticker for a market data element (Bloomberg ISIN code)
• Field name for a market data element (Bloomberg field PX_MID)
• Divider (Bloomberg is presenting a rate as percentage 1.234, but target system may need to have an input as absolute value 0.01234)
• Empty field for a value to be processed by specific processor implementation for specific data vendor. 

 

Result CSV

The program

Create a new console project and CopyPaste the following program into corresponding CS files. When testing the program in a real production environment, just add reference to Bloomberg API DLL file and replace DummyBBCOMMWrapper class with this.

Adding a new data vendor processor XYZ involves the following four easy steps :

1. Update providers in App.config file : <add key="RiskFactorDataProviders" value="BLOOMBERG,XYZ" />
2. Create a new method implementation into ProcessorLibrary :  public static void ProcessXYZRiskFactors(dynamic marketDataElements) { // implement algorithm}
3. Add selection for a new processor into BaseMarket class method createElementProcessors: if(dataProviderString.ToUpper() == "XYZ") elementProcessors.Add(new ElementProcessor(ProcessorLibrary.ProcessXYZRiskFactors, elements));
4. Create new RiskFactor object configurations into source file for a new vendor XYZ

Finally, thanks for reading my blog.
-Mike

// MainProgram.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace MarketDataProcess
{
    class MainProgram
    {
        static void Main(string[] args)
        {
            try
            {
                // process base market risk factors to file
                BaseMarket.Process();
                BaseMarket.PrintToFile();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
        }
    }
}
//
//
//
//
// BaseMarket.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace MarketDataProcess
{
    // class for administrating risk factors and processors for base market
    public static class BaseMarket
    {
        private static List<string> inputFileStreams = new List<string>();
        private static MarketDataElements<RiskFactor> riskFactors = new MarketDataElements<RiskFactor>();
        private static List<ElementProcessor> elementProcessors = new List<ElementProcessor>();
        private static int nRiskFactors = 0;
        //
        public static void Process()
        {
            // read all source data string streams from file into list
            TextFileHandler.Read(Configurations.BaseMarketSourceDataFilePathName, inputFileStreams);
            //
            // extract string streams and create risk factor objects
            foreach (string inputFileStream in inputFileStreams)
            {
                RiskFactor element = new RiskFactor();
                element.Create(inputFileStream);
                riskFactors.AddElement(element);
            }
            nRiskFactors = riskFactors.elements.Count;
            //
            // create and execute market data element processors
            // finally run technical check on created risk factors
            BaseMarket.createElementProcessors();
            elementProcessors.ForEach(processor => processor.Process());
            BaseMarket.Check();
        }
        public static void PrintToFile()
        {
            // write created base market risk factors into file
            StringBuilder stream = new StringBuilder();
            for (int i = 0; i < riskFactors.elements.Count; i++)
            {
                stream.AppendLine(String.Format("{0},{1}", riskFactors.elements[i].systemTicker, riskFactors.elements[i].value));
            }
            TextFileHandler.Write(Configurations.BaseMarketResultDataFilePathName, stream.ToString(), false);
        }
        private static void createElementProcessors()
        {
            // market data element processor types are defined in configuration file
            List<string> dataProviderStrings = Configurations.RiskFactorDataProviders.Split(',').ToList<string>();
            //
            foreach (string dataProviderString in dataProviderStrings)
            {
                if (dataProviderString == String.Empty) throw new Exception("No element processor defined");
                List<RiskFactor> elements = riskFactors.elements.Where(factor => factor.provider == dataProviderString).ToList<RiskFactor>();
                if(dataProviderString.ToUpper() == "BLOOMBERG") elementProcessors.Add(new ElementProcessor(ProcessorLibrary.ProcessBloombergRiskFactors, elements));
            }
        }
        public static MarketDataElements<RiskFactor> GetRiskFactors()
        {
            // create and return deep copy of all base market risk factors
            return riskFactors.Clone();
        }
        private static void Check()
        {
            int nValidRiskFactors = 0;
            //
            // loop through all created risk factors for base market
            for (int i = 0; i < riskFactors.elements.Count; i++)
            {
                // extract risk factor to be investigated for valid value
                RiskFactor factor = riskFactors.elements[i];
                //
                // valid value inside risk factor should be double-typed converted to string, ex. "0.05328"
                // check validity of this value with Double
                // TryParse-method returning TRUE if string value can be converted to double
                double value = 0;
                bool isValid = Double.TryParse(factor.value, out value);
                if (isValid) nValidRiskFactors++;
                //
                // if value is not convertable to double, get user input for this value
                if (!isValid)
                {
                    while (true)
                    {
                        Console.Write(String.Format("Provide input for {0} >", factor.systemTicker));
                        bool validUserInput = Double.TryParse(Console.ReadLine(), out value);
                        if (validUserInput)
                        {
                            factor.value = value.ToString();
                            nValidRiskFactors++;
                            break;
                        }
                        else
                        {
                            // client is forced to set (at least technically) valid value
                            Console.WriteLine("Invalid value, try again");
                        }
                    }
                }
            }
        }
    }
}
//
//
//
//
// Configurations.cs
using System;
using System.Configuration;

namespace MarketDataProcess
{
    // static class for hosting configurations
    public static class Configurations
    {
        // readonly data for public sharing
        public static readonly string RiskFactorDataProviders;
        public static readonly string BaseMarketSourceDataFilePathName;
        public static readonly string BaseMarketResultDataFilePathName;
        //
        // private constructor will be called just before any configuration is requested 
        static Configurations()
        {
            // configuration strings are assigned to static class data members for easy access
            RiskFactorDataProviders = ConfigurationManager.AppSettings["RiskFactorDataProviders"].ToString();
            BaseMarketSourceDataFilePathName = ConfigurationManager.AppSettings["BaseMarketSourceDataFilePathName"].ToString();
            BaseMarketResultDataFilePathName = ConfigurationManager.AppSettings["BaseMarketResultDataFilePathName"].ToString();
        }
    }
}
//
//
//
//
// MarketDataElement.cs
using System;
using System.Collections.Generic;
using System.Linq;

namespace MarketDataProcess
{
    // generic template for all types of market data elements (risk factors, fixings)
    public class MarketDataElements<T> where T : ICloneable, new()
    {
        public List<T> elements = new List<T>();
        public void AddElement(T element)
        {
            elements.Add(element);
        }
        public MarketDataElements<T> Clone()
        {
            // create a deep copy of market data elements object
            MarketDataElements<T> clone = new MarketDataElements<T>();
            //
            // copy content for all elements into a list
            List<T> elementList = new List<T>();
            foreach (T element in elements)
            {
                elementList.Add((T)element.Clone());
            }
            clone.elements.AddRange(elementList);
            return clone;
        }
    }
    // risk factor as market data element
    public class RiskFactor : ICloneable
    {
        public string provider;
        public string systemTicker;
        public string vendorTicker;
        public string vendorField;
        public string divider;
        public string value;
        //
        public RiskFactor() { }
        public void Create(string stream)
        {
            List<string> fields = stream.Split(',').ToList<string>();
            this.provider = fields[0];
            this.systemTicker = fields[1];
            this.vendorTicker = fields[2];
            this.vendorField = fields[3];
            this.divider = fields[4];
            this.value = fields[5];
        }
        public object Clone()
        {
            RiskFactor clone = new RiskFactor();
            clone.provider = this.provider;
            clone.systemTicker = this.systemTicker;
            clone.vendorTicker = this.vendorTicker;
            clone.vendorField = this.vendorField;
            clone.divider = this.divider;
            clone.value = this.value;
            return clone;
        }
    }
    // fixing as market data element
    public class Fixing : ICloneable
    {
        public string provider;
        public string systemTicker;
        public string vendorTicker;
        public string vendorField;
        public string frequency;
        public string nYearsBack;
        public string divider;
        public string value;
        public Dictionary<string, string> timeSeries = new Dictionary<string, string>();
        //
        public Fixing() { }
        public void Create(string stream)
        {
            List<string> fields = stream.Split(',').ToList<string>();
            this.provider = fields[0];
            this.systemTicker = fields[1];
            this.vendorTicker = fields[2];
            this.vendorField = fields[3];
            this.frequency = fields[4];
            this.nYearsBack = fields[5];
            this.divider = fields[6];
            this.value = fields[7];
        }
        public object Clone()
        {
            Fixing clone = new Fixing();
            clone.provider = this.provider;
            clone.systemTicker = this.systemTicker;
            clone.vendorTicker = this.vendorTicker;
            clone.vendorField = this.vendorField;
            clone.divider = this.divider;
            clone.value = this.value;
            //
            // create deep copy of timeseries dictionary
            Dictionary<string, string> timeSeriesClone = new Dictionary<string, string>();
            foreach (KeyValuePair<string, string> kvp in this.timeSeries)
            {
                timeSeriesClone.Add(kvp.Key, kvp.Value);
            }
            clone.timeSeries = timeSeriesClone;
            return clone;
        }
    }
}
//
//
//
//
// ElementProcessor.cs
using System;
using System.Collections.Generic;

namespace MarketDataProcess
{
    // algorithm for creating market data element objects
    public delegate void Processor(dynamic marketDataElements);
    //
    // class for hosting data and algorithm
    public class ElementProcessor
    {
        private Processor taskProcessor;
        dynamic marketDataElements;
        public ElementProcessor(Processor taskProcessor, dynamic marketDataElements)
        {
            this.taskProcessor = taskProcessor;
            this.marketDataElements = marketDataElements;
        }
        public void Process()
        {
            taskProcessor.Invoke(marketDataElements);
        }
    }
}
//
//
//
//
// ProcessorLibrary.cs
using System;
using System.Collections.Generic;
using System.Linq;

namespace MarketDataProcess
{
    public static class ProcessorLibrary
    {
        public static void ProcessBloombergRiskFactors(dynamic marketDataElements)
        {
            List<RiskFactor> riskFactors = (List<RiskFactor>)marketDataElements;
            BBCOMMWrapper.BBCOMMDataRequest request;
            dynamic[, ,] result = null;
            string SYSTEM_DOUBLE = "System.Double";
            int counter = 0;
            //
            // group data list into N lists grouped by distinct bloomberg field names
            var dataGroups = riskFactors.GroupBy(factor => factor.vendorField);
            //
            // process each group of distinct bloomberg field names
            for (int i = 0; i < dataGroups.Count(); i++)
            {
                // extract group, create data structures for securities and fields
                List<RiskFactor> dataGroup = dataGroups.ElementAt(i).ToList<RiskFactor>();
                List<string> securities = new List<string>();
                List<string> fields = new List<string>() { dataGroup[0].vendorField };
                //
                // import securities into data structure feeded to bloomberg api
                for (int j = 0; j < dataGroup.Count; j++)
                {
                    securities.Add(dataGroup[j].vendorTicker);
                }
                //
                // create and use request object to retrieve bloomberg data
                request = new BBCOMMWrapper.ReferenceDataRequest(securities, fields);
                result = request.ProcessData();
                //
                // write retrieved bloomberg data into risk factor group data
                for (int k = 0; k < result.GetLength(0); k++)
                {
                    string stringValue;
                    dynamic value = result[k, 0, 0];
                    //
                    if (value.GetType().ToString() == SYSTEM_DOUBLE)
                    {
                        // handle output value with divider only if retrieved value is double
                        // this means that data retrieval has been succesfull
                        double divider = Convert.ToDouble(dataGroup[k].divider);
                        stringValue = (value / divider).ToString();
                        dataGroup[k].value = stringValue;
                        counter++;
                    }
                    else
                    {
                        // write non-double value (bloomberg will retrieve #N/A) if retrieved value is not double
                        stringValue = value.ToString();
                        dataGroup[k].value = stringValue;
                    }
                }
            }
        }
    }
}
//
//
//
//
// DummyBBCOMMWrapper.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace BBCOMMWrapper
{
    // abstract base class for data request
    public abstract class BBCOMMDataRequest
    {
        // input data structures
        protected List<string> securityNames = new List<string>();
        protected List<string> fieldNames = new List<string>();
        //
        // output result data structure
        protected dynamic[, ,] result;
        //
        public dynamic[, ,] ProcessData()
        {
            // instead of the actual Bloomberg market data, random numbers are going to be generated
            Random randomGenerator = new Random(Math.Abs(Guid.NewGuid().GetHashCode()));
            //
            for (int i = 0; i < securityNames.Count; i++)
            {
                for (int j = 0; j < fieldNames.Count; j++)
                {
                    result[i, 0, j] = randomGenerator.NextDouble();
                }
            }
            return result;
        }
    }
    //
    // concrete class implementation for processing reference data request
    public class ReferenceDataRequest : BBCOMMDataRequest
    {
        public ReferenceDataRequest(List<string> bloombergSecurityNames,
            List<string> bloombergFieldNames)
        {
            securityNames = bloombergSecurityNames;
            fieldNames = bloombergFieldNames;
            result = new dynamic[securityNames.Count, 1, fieldNames.Count];
        }
    }
}
//
//
//
//
// TextFileHandler.cs
using System;
using System.Collections.Generic;
using System.IO;

namespace MarketDataProcess
{
    public static class TextFileHandler
    {
        public static void Read(string filePathName, List<string> output)
        {
            // read file content into list
            StreamReader reader = new StreamReader(filePathName);
            while (!reader.EndOfStream)
            {
                output.Add(reader.ReadLine());
            }
            reader.Close();
        }
        public static void Write(string filePathName, List<string> input, bool append)
        {
            // write text stream list to file
            StreamWriter writer = new StreamWriter(filePathName, append);
            input.ForEach(it => writer.WriteLine(it));
            writer.Close();
        }
        public static void Write(string filePathName, string input, bool append)
        {
            // write bulk text stream to file
            StreamWriter writer = new StreamWriter(filePathName, append);
            writer.Write(input);
            writer.Close();
        }
    }
}

Builder Pattern for C# Basket Default Swap Pricer

Keep your options open is a phrase, we often hear when making decisions concerning our lives. Needless to say, this great advice is directly applicable to any programming scheme. Now, I have to confess that many times (too many times) I feel a big hurry just to finish something quickly and that kind of an approach then leads to programs having no options for changes or modifications. Good example of this is BDS pricer program presented in the previous post.

PROBLEM

So, what is then wrong with that program? There should be nothing wrong with the program, but experienced programmers should catch a lot of different issues to be improved. Program is actually not very flexible as it is at the moment. However, I will concentrate on just one specific problem: the object creation part of the program. When looking at its client (class Program), we can see how it is polluted with object creation procedures (DiscountCurve, CreditCurve, NAGCopula) and hard-coded data. What if I would like to create my objects and data from text file or from Excel workbook? The mess (main code size explosion) would be even greater. Needless to say, the program as it is now, is not leaving much of those options open.

SOLUTION

Solution for this problem is Builder design pattern. Gang of Four (GOF) definition for Builder is the following.

"The intent of the Builder design pattern is to separate the construction of a complex object from its representation. By doing so the same construction process can create different representations."

In the new program design, BasketCDS class needs three different objects: Copula, CreditMatrix and DiscountCurve. Ultimately, client will feed BasketCDS class with these three objects wrapped inside Tuple. Before this, client is using concrete ExcelBuilder (implementation of abstract ComponentBuilder) for creating all three objects. So, client is effectively outsourcing the creation part of this complex object (a tuple, consisting of three objects) for ExcelBuilder. Just for example, instead of ExcelBuilder we could have TextFileBuilder or ConsoleBuilder. So, the source from which we are creating these objects (Copula, CreditMatrix and DiscountCurve) can be changed. Moreover, our client has no part in the object creation process. As far as I see it, this scheme is satisfying that GOF definition for Builder pattern. With this small change in design, we are now having some important options open.

PROGRAM FLOW

In order to understand the program data flow a bit better, a simple class UML has been presented in the picture below. I would like to stress the fact, that in no way this presentation is formally correct. However, the point is just to open up how this program is working on a very general level.

Client is requesting for creation of several objects from ExcelBuilder object, which is an implementation of abstract ComponentBuilder class. ExcelBuilder is then creating three central objects (CreditMatrix, DiscountCurve and Copula) into generic Tuple.

Client is then feeding BasketCDS with Tuple, which is consisting all required objects. BasketCDS object is starting Monte Carlo process and sending calculated leg values to Statistics object by using delegate methods. Finally, client is requesting the result (basket CDS spread) from Statistics object.

MathTools and MatrixTools classes are static utility classes, used by several objects for different mathematical or matrix operations. 

EXCEL BUILDER

For this example program, Excel is the most convenient platform for feeding parameters for our C# program. At this posting, I am only going to present the Excel interface and the results. For this kind of scheme, interfacing C# to Excel with Excel-DNA has been thoroughly covered in this blog posting.

My current Excel worksheet interface is the following.

The idea is simple. Client (Main) will delegate the creation part of three core objects (Copula, CreditMatrix and DiscountCurve) for ExcelBuilder class. ExcelBuilder class has been carefully configured to get specific data from specific ranges in Excel workbook. For example, it will read co-variance matrix from the named Excel range (_COVARIANCE). Personally, I am always using named Excel ranges instead of range addresses, in order to maintain flexibility when changing location of some range in the worksheet. By using this kind of a scheme, no further modifications will be needed in the program, since it communicates only with named Excel ranges.

After creating all those named ranges, a Button (Form Control) has been created into Excel worksheet, having program name Execute in its Assign Macro Box. I have been using Form Controls in order to get rid of any VBA code completely. Any public static void method in .NET code will be registered automatically by Excel-DNA as a macro in Excel. Thanks for this tip goes to Govert Van Drimmelen (inventor, developer and author of Excel-DNA).

PROGRAM RUN

After pressing Execute button, my C# program will write the following Basket CDS prices and program running time in seconds back to Excel worksheet (yellow areas).

THE PROGRAM

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Diagnostics;
using ExcelDna.Integration;
using NagLibrary;

namespace ExcelBasketCDSPricer
{
    // CLIENT
    public static class Main
    {
        private static Stopwatch timer;
        private static dynamic Excel;
        private static ComponentBuilder builder;
        private static Tuple<Copula, CreditMatrix, DiscountCurve> components;
        private static List<BasketCDS> engines;
        private static List<Statistics> statistics;
        //
        public static void Execute()
        {
            try
            {
                // create timer and Excel objects
                timer = new Stopwatch();
                timer.Start();
                Excel = ExcelDnaUtil.Application;
                //
                // create Excel builder objects and request for required components for all pricers
                builder = new ExcelBuilder();
                components = builder.GetComponents();
                int nSimulations = (int)Excel.Range["_N"].Value2;
                int nReferences = (int)Excel.Range["_REFERENCES"].Value2;
                //
                // create basket pricers and statistics gatherers into containers
                engines = new List<BasketCDS>();
                statistics = new List<Statistics>();
                //
                // order updating for statistics gatherers from basket pricers
                for (int kth = 1; kth <= nReferences; kth++)
                {
                    statistics.Add(new Statistics(String.Concat(kth.ToString(), "-to-default")));
                    engines.Add(new BasketCDS(kth, nSimulations, nReferences, components));
                    engines[kth - 1].updateDefaultLeg += statistics[kth - 1].UpdateDefaultLeg;
                    engines[kth - 1].updatePremiumLeg += statistics[kth - 1].UpdatePremiumLeg;
                }
                //
                // run basket pricers
                engines.ForEach(it => it.Process());
                double[] prices =
                    statistics.Select(it => Math.Round(it.Spread() * 10000, 3)).ToArray<double>();
                timer.Stop();
                //
                // print statistics back to Excel
                Excel.Range["_PRICES"] = Excel.WorksheetFunction.Transpose(prices);
                Excel.Range["_RUNTIME"] = Math.Round(timer.Elapsed.TotalSeconds, 3);
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }
        }
    }
    //
    // *******************************************************************************
    // abstract base class for all component builders
    public abstract class ComponentBuilder
    {
        public abstract Copula CreateCopula();
        public abstract CreditMatrix CreateCreditMatrix();
        public abstract DiscountCurve CreateDiscountCurve();
        public abstract Tuple<Copula, CreditMatrix, DiscountCurve> GetComponents();
    }
    //
    // implementation for builder reading parameters from Excel
    public class ExcelBuilder : ComponentBuilder
    {
        dynamic Excel = null;
        private Copula copula = null;
        private CreditMatrix credit = null;
        private DiscountCurve curve = null;
        //
        public ExcelBuilder()
        {
            Excel = ExcelDnaUtil.Application;
        }
        public override Copula CreateCopula()
        {
            // create covariance matrix
            dynamic matrix = Excel.Range["_COVARIANCE"].Value2;
            int rows = matrix.GetUpperBound(0);
            int columns = matrix.GetUpperBound(1);
            double[,] covariance = new double[rows, columns];
            //
            for (int i = 0; i < rows; i++)
            {
                for (int j = 0; j < columns; j++)
                {
                    covariance[i, j] = (double)matrix.GetValue(i + 1, j + 1);
                }
            }
            //
            // create copula source information
            string source = (string)Excel.Range["_COPULA"].Value2;
            //
            // special parameters for NAG copula
            if (source == "NAG")
            {
                // generator id
                int genid = (int)Excel.Range["_GEN"].Value2;
                //
                // sub generator id
                int subgenid = (int)Excel.Range["_SUBGEN"].Value2;
                //
                // mode
                int mode = (int)Excel.Range["_MODE"].Value2;
                //
                // degrees of freedom
                int df = (int)Excel.Range["_DF"].Value2;
                //
                // create NAG copula
                copula = new NAGCopula(covariance, genid, subgenid, mode, df);
            }
            return copula;
        }
        public override CreditMatrix CreateCreditMatrix()
        {
            // create cds matrix
            dynamic matrix = Excel.Range["_CDS"].Value2;
            int rows = matrix.GetUpperBound(0);
            int columns = matrix.GetUpperBound(1);
            double[,] cds = new double[rows, columns];
            //
            for (int i = 0; i < rows; i++)
            {
                for (int j = 0; j < columns; j++)
                {
                    cds[i, j] = (double)matrix.GetValue(i + 1, j + 1);
                }
            }
            //
            // recovery estimate and discounting method as generic delegate
            double recovery = (double)Excel.Range["_RECOVERY"].Value2;
            Func<double, double> df = curve.GetDF;
            //
            CreditMatrix credit = new CreditMatrix(cds, df, recovery);
            return credit;
        }
        public override DiscountCurve CreateDiscountCurve()
        {
            // create zero-coupon curve
            dynamic matrix = Excel.Range["_ZERO"].Value2;
            int rows = matrix.GetUpperBound(0);
            int columns = matrix.GetUpperBound(1);
            double[,] zeroCouponCurve = new double[rows, columns];
            //
            for (int i = 0; i < rows; i++)
            {
                for (int j = 0; j < columns; j++)
                {
                    zeroCouponCurve[i, j] = (double)matrix.GetValue(i + 1, j + 1);
                }
            }
            //
            // create interpolation method
            int interpolationMethodID = (int)Excel.Range["_INTERPOLATION"].Value2;
            InterpolationAlgorithm interpolation = null;
            switch (interpolationMethodID)
            {
                case 1:
                    interpolation = MathTools.LinearInterpolation;
                    break;
                default:
                    throw new Exception("interpolation algorithm not defined");
            }
            //
            // create discounting method
            int discountingMethodID = (int)Excel.Range["_DISCOUNTING"].Value2;
            DiscountAlgorithm discounting = null;
            switch (discountingMethodID)
            {
                case 1:
                    discounting = MathTools.ContinuousDiscountFactor;
                    break;
                default:
                    throw new Exception("discounting algorithm not defined");
            }
            DiscountCurve curve = new DiscountCurve(zeroCouponCurve, interpolation, discounting);
            return curve;
        }
        public override Tuple<Copula, CreditMatrix, DiscountCurve> GetComponents()
        {
            copula = CreateCopula();
            curve = CreateDiscountCurve();
            credit = CreateCreditMatrix();
            Tuple<Copula, CreditMatrix, DiscountCurve> components =
                new Tuple<Copula, CreditMatrix, DiscountCurve>(copula, credit, curve);
            return components;
        }
    }
    //
    // *******************************************************************************
    public class BasketCDS
    {
        public PremiumLegUpdate updatePremiumLeg; // delegate for sending value
        public DefaultLegUpdate updateDefaultLeg; // delegate for sending value
        //
        private Copula copula; // NAG copula model
        private CreditMatrix cds; // credit matrix
        private DiscountCurve curve; // discount curve
        private int k; // kth-to-default
        private int m; // number of reference assets
        private int n; // number of simulations
        private int maturity; // basket cds maturity in years (integer)
        //
        public BasketCDS(int kth, int simulations, int maturity,
            Tuple<Copula, CreditMatrix, DiscountCurve> components)
        {
            this.k = kth;
            this.n = simulations;
            this.maturity = maturity;
            this.copula = components.Item1;
            this.cds = components.Item2;
            this.curve = components.Item3;
        }
        public void Process()
        {
            // request correlated random numbers from copula model
            double[,] randomArray = copula.Create(n, Math.Abs(Guid.NewGuid().GetHashCode()));
            m = randomArray.GetLength(1);
            //
            // process n sets of m correlated random numbers
            for (int i = 0; i < n; i++)
            {
                // create a set of m random numbers needed for one simulation round
                double[,] set = new double[1, m];
                for (int j = 0; j < m; j++)
                {
                    set[0, j] = randomArray[i, j];
                }
                //
                // calculate default times for reference name set
                calculateDefaultTimes(set);
            }
        }
        private void calculateDefaultTimes(double[,] arr)
        {
            // convert uniform random numbers into default times
            int cols = arr.GetLength(1);
            double[,] defaultTimes = new double[1, cols];
            //
            for (int j = 0; j < cols; j++)
            {
                // iteratively, find out the default tenor bucket
                double u = arr[0, j];
                double t = Math.Abs(Math.Log(1 - u));
                //
                for (int k = 0; k < cds.CumulativeHazardMatrix.GetLength(1); k++)
                {
                    int tenor = 0;
                    double dt = 0.0; double defaultTenor = 0.0;
                    if (cds.CumulativeHazardMatrix[k, j] >= t)
                    {
                        tenor = k;
                        if (tenor >= 1)
                        {
                            // calculate the exact default time for a given reference name
                            dt = -(1 / cds.HazardMatrix[k, j]) * Math.Log((1 - u)
                                / (Math.Exp(-cds.CumulativeHazardMatrix[k - 1, j])));
                            defaultTenor = tenor + dt;
                            //
                            // default time after basket maturity
                            if (defaultTenor >= maturity) defaultTenor = 0.0;
                        }
                        else
                        {
                            // hard-coded assumption
                            defaultTenor = 0.5;
                        }
                        defaultTimes[0, j] = defaultTenor;
                        break;
                    }
                }
            }
            // proceed to calculate leg values
            updateLegValues(defaultTimes);
        }
        private void updateLegValues(double[,] defaultTimes)
        {
            // check for defaulted reference names, calculate leg values 
            // and send statistics updates for BasketCDSStatistics
            int nDefaults = getNumberOfDefaults(defaultTimes);
            if (nDefaults > 0)
            {
                // for calculation purposes, remove zeros and sort matrix
                MatrixTools.RowMatrix_removeZeroValues(ref defaultTimes);
                MatrixTools.RowMatrix_sort(ref defaultTimes);
            }
            // calculate and send values for statistics gatherer
            double dl = calculateDefaultLeg(defaultTimes, nDefaults);
            double pl = calculatePremiumLeg(defaultTimes, nDefaults);
            updateDefaultLeg(dl);
            updatePremiumLeg(pl);
        }
        private int getNumberOfDefaults(double[,] arr)
        {
            int nDefaults = 0;
            for (int i = 0; i < arr.GetLength(1); i++)
            {
                if (arr[0, i] > 0.0) nDefaults++;
            }
            return nDefaults;
        }
        private double calculatePremiumLeg(double[,] defaultTimes, int nDefaults)
        {
            double dt = 0.0; double t; double p = 0.0; double v = 0.0;
            if ((nDefaults > 0) && (nDefaults >= k))
            {
                for (int i = 0; i < k; i++)
                {
                    if (i == 0)
                    {
                        // premium components from 0 to t1
                        dt = defaultTimes[0, i] - 0.0;
                        t = dt;
                        p = 1.0;
                    }
                    else
                    {
                        // premium components from t1 to t2, etc.
                        dt = defaultTimes[0, i] - defaultTimes[0, i - 1];
                        t = defaultTimes[0, i];
                        p = (m - i) / (double)m;
                    }
                    v += (curve.GetDF(t) * dt * p);
                }
            }
            else
            {
                for (int i = 0; i < maturity; i++)
                {
                    v += curve.GetDF(i + 1);
                }
            }
            return v;
        }
        private double calculateDefaultLeg(double[,] defaultTimes, int nDefaults)
        {
            double v = 0.0;
            if ((nDefaults > 0) && (nDefaults >= k))
            {
                v = (1 - cds.Recovery) * curve.GetDF(defaultTimes[0, k - 1]) * (1 / (double)m);
            }
            return v;
        }
    }
    //
    // *******************************************************************************
    // abstract base class for all copula models
    public abstract class Copula
    {
        // request matrix of correlated uniform random numbers 
        // number of rows are given argument n
        // Number of columns are inferred from the size of covariance matrix and
        public abstract double[,] Create(int n, int seed);
    }
    //
    // NAG G05 COPULAS WRAPPER
    public class NAGCopula : Copula
    {
        private double[,] covariance;
        private int genID;
        private int subGenID;
        private int mode;
        private int df;
        private int m;
        private int errorNumber;
        private double[] r;
        private double[,] result;
        //
        public NAGCopula(double[,] covariance, int genID,
            int subGenID, int mode, int df = 0)
        {
            // ctor : create correlated uniform random numbers
            // degrees-of-freedom parameter (df), being greater than zero, 
            // will automatically trigger the use of student copula
            this.covariance = covariance;
            this.genID = genID;
            this.subGenID = subGenID;
            this.mode = mode;
            this.df = df;
            this.m = covariance.GetLength(1);
            r = new double[m * (m + 1) + (df > 0 ? 2 : 1)];
        }
        public override double[,] Create(int n, int seed)
        {
            result = new double[n, m];
            G05.G05State g05State = new G05.G05State(genID, subGenID, new int[1] { seed }, out errorNumber);
            if (errorNumber != 0) throw new Exception("G05 state failure");
            //
            if (this.df != 0)
            {
                // student copula
                G05.g05rc(mode, n, df, m, covariance, r, g05State, result, out errorNumber);
            }
            else
            {
                // gaussian copula
                G05.g05rd(mode, n, m, covariance, r, g05State, result, out errorNumber);
            }
            //
            if (errorNumber != 0) throw new Exception("G05 method failure");
            return result;
        }
    }
    //
    // *******************************************************************************
    public class CreditMatrix
    {
        private Func<double, double> df;
        private double recovery;
        private double[,] CDSSpreads;
        private double[,] survivalMatrix;
        private double[,] hazardMatrix;
        private double[,] cumulativeHazardMatrix;
        //
        public CreditMatrix(double[,] CDSSpreads,
            Func<double, double> discountFactor, double recovery)
        {
            this.df = discountFactor;
            this.CDSSpreads = CDSSpreads;
            this.recovery = recovery;
            createSurvivalMatrix();
            createHazardMatrices();
        }
        // public read-only accessors to class data
        public double[,] HazardMatrix { get { return this.hazardMatrix; } }
        public double[,] CumulativeHazardMatrix { get { return this.cumulativeHazardMatrix; } }
        public double Recovery { get { return this.recovery; } }
        //
        private void createSurvivalMatrix()
        {
            // bootstrap matrix of survival probabilities from given CDS data
            int rows = CDSSpreads.GetUpperBound(0) + 2;
            int cols = CDSSpreads.GetUpperBound(1) + 1;
            survivalMatrix = new double[rows, cols];
            //
            double term = 0.0; double firstTerm = 0.0; double lastTerm = 0.0;
            double terms = 0.0; double quotient = 0.0;
            int i = 0; int j = 0; int k = 0;
            //
            for (i = 0; i < rows; i++)
            {
                for (j = 0; j < cols; j++)
                {
                    if (i == 0) survivalMatrix[i, j] = 1.0;
                    if (i == 1) survivalMatrix[i, j] = (1 - recovery) / ((1 - recovery) + 1 * CDSSpreads[i - 1, j] / 10000);
                    if (i > 1)
                    {
                        terms = 0.0;
                        for (k = 0; k < (i - 1); k++)
                        {
                            term = df(k + 1) * ((1 - recovery) * survivalMatrix[k, j] -
                            (1 - recovery + 1 * CDSSpreads[i - 1, j] / 10000) * survivalMatrix[k + 1, j]);
                            terms += term;
                        }
                        quotient = (df(i) * ((1 - recovery) + 1 * CDSSpreads[i - 1, j] / 10000));
                        firstTerm = (terms / quotient);
                        lastTerm = survivalMatrix[i - 1, j] * (1 - recovery) / (1 - recovery + 1 * CDSSpreads[i - 1, j] / 10000);
                        survivalMatrix[i, j] = firstTerm + lastTerm;
                    }
                }
            }
        }
        private void createHazardMatrices()
        {
            // convert matrix of survival probabilities into two hazard rate matrices
            int rows = survivalMatrix.GetUpperBound(0);
            int cols = survivalMatrix.GetUpperBound(1) + 1;
            hazardMatrix = new double[rows, cols];
            cumulativeHazardMatrix = new double[rows, cols];
            int i = 0; int j = 0;
            //
            for (i = 0; i < rows; i++)
            {
                for (j = 0; j < cols; j++)
                {
                    cumulativeHazardMatrix[i, j] = -Math.Log(survivalMatrix[i + 1, j]);
                    if (i == 0) hazardMatrix[i, j] = cumulativeHazardMatrix[i, j];
                    if (i > 0) hazardMatrix[i, j] = (cumulativeHazardMatrix[i, j] - cumulativeHazardMatrix[i - 1, j]);
                }
            }
        }
    }
    //
    // *******************************************************************************
    // delegate methods for interpolation and discounting
    public delegate double InterpolationAlgorithm(double t, ref double[,] curve);
    public delegate double DiscountAlgorithm(double t, double r);
    //
    public class DiscountCurve
    {
        // specific algorithms for interpolation and discounting
        private InterpolationAlgorithm interpolationMethod;
        private DiscountAlgorithm discountMethod;
        private double[,] curve;
        //
        public DiscountCurve(double[,] curve,
            InterpolationAlgorithm interpolationMethod, DiscountAlgorithm discountMethod)
        {
            this.curve = curve;
            this.interpolationMethod = interpolationMethod;
            this.discountMethod = discountMethod;
        }
        public double GetDF(double t)
        {
            // get discount factor from discount curve
            return discountMethod(t, interpolation(t));
        }
        private double interpolation(double t)
        {
            // get interpolation from discount curve
            return interpolationMethod(t, ref this.curve);
        }
    }
    //
    // *******************************************************************************
    // collection of methods for different types of mathematical operations
    public static class MathTools
    {
        public static double LinearInterpolation(double t, ref double[,] curve)
        {
            int n = curve.GetUpperBound(0) + 1;
            double v = 0.0;
            //
            // boundary checkings
            if ((t < curve[0, 0]) || (t > curve[n - 1, 0]))
            {
                if (t < curve[0, 0]) v = curve[0, 1];
                if (t > curve[n - 1, 0]) v = curve[n - 1, 1];
            }
            else
            {
                // iteration through all given curve points
                for (int i = 0; i < n; i++)
                {
                    if ((t >= curve[i, 0]) && (t <= curve[i + 1, 0]))
                    {
                        v = curve[i, 1] + (curve[i + 1, 1] - curve[i, 1]) * (t - (i + 1));
                        break;
                    }
                }
            }
            return v;
        }
        public static double ContinuousDiscountFactor(double t, double r)
        {
            return Math.Exp(-r * t);
        }
    }
    //
    // *******************************************************************************
    // collection of methods for different types of matrix operations
    public static class MatrixTools
    {
        public static double[,] CorrelationToCovariance(double[,] corr, double[] stdev)
        {
            // transform correlation matrix to co-variance matrix
            double[,] cov = new double[corr.GetLength(0), corr.GetLength(1)];
            //
            for (int i = 0; i < cov.GetLength(0); i++)
            {
                for (int j = 0; j < cov.GetLength(1); j++)
                {
                    cov[i, j] = corr[i, j] * stdev[i] * stdev[j];
                }
            }
            //
            return cov;
        }
        public static void RowMatrix_sort(ref double[,] arr)
        {
            // sorting a given row matrix to ascending order
            // input must be 1 x M matrix
            // bubblesort algorithm implementation
            int cols = arr.GetUpperBound(1) + 1;
            double x = 0.0;
            //
            for (int i = 0; i < (cols - 1); i++)
            {
                for (int j = (i + 1); j < cols; j++)
                {
                    if (arr[0, i] > arr[0, j])
                    {
                        x = arr[0, i];
                        arr[0, i] = arr[0, j];
                        arr[0, j] = x;
                    }
                }
            }
        }
        public static void RowMatrix_removeZeroValues(ref double[,] arr)
        {
            // removes zero values from a given row matrix
            // input must be 1 x M matrix
            List<double> temp = new List<double>();
            int cols = arr.GetLength(1);
            int counter = 0;
            for (int i = 0; i < cols; i++)
            {
                if (arr[0, i] > 0)
                {
                    counter++;
                    temp.Add(arr[0, i]);
                }
            }
            if (counter > 0)
            {
                arr = new double[1, temp.Count];
                for (int i = 0; i < temp.Count; i++)
                {
                    arr[0, i] = temp[i];
                }
            }
            else
            {
                arr = null;
            }
        }
    }
    //
    // *******************************************************************************
    public delegate void PremiumLegUpdate(double v);
    public delegate void DefaultLegUpdate(double v);
    //
    public class Statistics
    {
        // data structures for storing leg values
        private List<double> premiumLeg;
        private List<double> defaultLeg;
        private string ID;
        //
        public Statistics(string ID)
        {
            this.ID = ID;
            premiumLeg = new List<double>();
            defaultLeg = new List<double>();
        }
        public void UpdatePremiumLeg(double v)
        {
            premiumLeg.Add(v);
        }
        public void UpdateDefaultLeg(double v)
        {
            defaultLeg.Add(v);
        }
        public void PrettyPrint()
        {
            // hard-coded 'report' output
            Console.WriteLine("{0} : {1} bps", ID, Math.Round(Spread() * 10000, 2));
        }
        public double Spread()
        {
            return defaultLeg.Average() / premiumLeg.Average();
        }
    }
}

CONCLUSIONS

In this blog posting, Builder design pattern was applied to solve some of the problems observed in hard-coded program. By outsourcing the object creation for Builder (ExcelBuilder), client code size explosion has been avoided completely. Moreover, the source for objects creation is now open for new implementations (ConsoleBuilder, TextFileBuilder, etc). The program is still not as flexible as it could be, but that is another story.

Again, Thanks for Govert Van Drimmelen for his amazing Excel-DNA. For learning more things about Excel-DNA, check out its homepage. Getting more information and examples with your problems, the main source is Excel-DNA google group. Excel-DNA is an open-source project, and we (the happy users) can invest its future development by making a donation.

Last week, I spent three days in Datasim training course. During the course, we went systematically through most of the GOF design patterns in C#, using traditional object-oriented approach. Plus, the instructor was also presenting, how to use C# generics (delegates) for implementing some of the design patterns, using functional programming approach. The course was truly having a great balance between theory and programming. So, if anyone is looking for very practical hands-on training for this GOF stuff, this course is getting all five stars from me.

Thank You for reading my blog. I wish you have discovered some usage for Builder pattern in your programs. Mike.