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;
        }
    }
}

Bootstrapping Libor Discount Factor and Forward Rate Curves using C# and Excel-DNA

Defining a set of correct discount factors and forward rates is the cornerstone of valuing any Libor-based products. As we know, calculation of present value for a Libor cash flow seems to be almost too easy : first define a cash flow happening in the future by using forward rates, then use discount factors to get present value for that cash flow. Then we also know, that the real deal is always getting those forward rates and discount factors from somewhere, in the first place.

For the task described above, we usually have our front office system or tools provided by some third-party vendor, which are then performing all those required complex calculations quietly behind the scenes. Figures are popping up in the screen, finding their way into some strange reports and everyone are happy. Needless to say, we should be able to perform those calculations also by hand, if so required. This is only my personal opinion on the matter, of course. During this small project, we are creating a program, which takes in market data as input parameter. From this market data, the program is then performing all those complex calculations (bootstrapping, interpolations) and returning discount factors and simply-compounded Libor forward rates.

CURVOLOGY

When constructing discount factors and forward rates from market data, one has to make a lot of different decisions. Just for an example

• How to construct short-end of the curve? For what maturities are we going to use cash securities?
• How to construct mid-part of the curve? Are we using futures contracts on Libor or Forward Rate Agreements? 
• Are we going to make adjustments for convexity effect?
• How to construct long-end of the curve? For what maturity we start to use swap rates?
• What kind of interpolation methods are we going to use?
• Do we interpolate discount factors or spot rates?

Due to this high degrees of freedom embedded in this task, it should not be a miracle, that two persons (given the same set of market data) most probably will end up having completely different set of discount factors and forward rates. As I have been studying this topic and getting a bit more familiar with the methods and procedures used by some well-known authors, I have personally come to the conclusion that there are some common guidelines and general conventions, but absolutely not any one universally correct way to do this task.

MARKET DATA

For this project, I have been using dataset and curve creation procedures as presented in Richard Flavell's excellent book Swaps and Other Derivatives. In this book, Flavell is giving complete treatment, how to construct Libor zero curve (discount factors and forward rates). One of the things why I like so much this book is the fact, that Flavell is not assuming anything. Instead, he is always deriving everything from the beginning. Great thing is also, that the book has all the example calculations dissected in the attached Excel workbooks. Personally, these Excels have been sort of a goldmine for me, when I have been studying different valuation issues.

PROJECT OUTCOME

The result of this project is XLL addin for Excel, which calculates discount factors and simply-compounded forward rates for USD Libor. We will use Excel-DNA for interfacing our C# program with Excel. The scheme what has been used for this project, has been fully explained in this blog post. So, carefully follow the instructions given in that post, when interfacing the program with Excel. Needless to say, one should be able to use the core classes of the program (the classes which are actually creating the curves) in any other C# project, without Excel interfacing.

PROGRAM DESIGN

Rough UML class diagram for this program is shown below. The purpose of this diagram is to get some conceptual overview, how the program is working and how the objects are related with each other. Since I am not UML expert, I am presenting my deepest apologies for its incompleteness and possible errors.

In the first stage, Client (CreateUSDLiborCurves, which will be presented in the section Excel Interfacing) is requesting ExcelBuilder (specialization of abstract MarketDataBuilder) to build market data and return it as a CurveData object. Then, Client is creating and using USDLiborZeroCurve object (implementation of ICurve interface). Inside USDLiborZeroCurve object, the curves (discount factors and forward rates) are going to be created in different types of sequential calculations (data checking, data selection, bootstrapping, interpolation). Finally, Client is requesting ExcelPrinter (specialization of abstract CurvePrinter) object to print the resulting dataset into Excel worksheet. During the calculation process, USDLiborZeroCurve is also using two static classes (DateConvention and Interpolation, not shown in UML diagram), which contains different types of date-related and interpolation-related methods.

THE PROGRAM

The part of the program, which is creating the outcome curves (discount factors and forward rates), is shown below. Create a new Class project and just copyPaste everything into a new cs-file.

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

namespace CurveAddin
{
    //
    // public enumerators and delegate methods
    public enum ENUM_MARKETDATATYPE { CASH = 1, FRA = 2, FUTURE = 3, SWAP = 4, DF = 5, SPOTRATE = 6, FORWARDRATE = 7 }
    public enum ENUM_PERIODTYPE { MONTHS = 1, YEARS = 2 }
    public delegate double DayCountFactor(DateTime start, DateTime end);
    public delegate DateTime AddPeriod(DateTime start, ENUM_PERIODTYPE periodType, int period);
    public delegate double Interpolator(DayCountFactor dayCountFactor, Dictionary<DateTime, double> data, DateTime key);
    public delegate double ConvexityAdjustment(double rateVolatility, double start, double end);
    //
    //
    // class hierarchy for curve printer
    public abstract class CurvePrinter
    {
        public abstract void Print();
    }
    public class ExcelPrinter : CurvePrinter
    {
        private static dynamic Excel;
        private dynamic[,] data;
        public ExcelPrinter(dynamic[,] data)
        {
            this.data = data;
        }
        public override void Print()
        {
            // Create Excel application
            Excel = ExcelDnaUtil.Application;
            //
            // clear old data from output range, resize output range 
            // and finally print data to Excel worksheet
            Excel.Range["_USDLiborZeroCurve"].CurrentRegion = "";
            Excel.Range["_USDLiborZeroCurve"].Resize[data.GetLength(0), data.GetLength(1)] = data;
        }
    }
    //
    //
    // class hierarchy for market data builder
    public abstract class MarketDataBuilder
    {
        public abstract CurveData Build();
    }
    public class ExcelBuilder : MarketDataBuilder
    {
        private static dynamic Excel;
        private DateTime settlementDate;
        public DateTime SettlementDate { get { return this.settlementDate; } }
        //
        public override CurveData Build()
        {
            // Create Excel application
            Excel = ExcelDnaUtil.Application;
            //
            // read settlement date from Excel worksheet
            settlementDate = DateTime.FromOADate((double)Excel.Range("_settlementDate").Value2);
            //
            // read source security data from Excel worksheet
            object[,] ExcelSourceData = (object[,])Excel.Range["_marketData"].CurrentRegion.Value2;
            //
            // create curve data object from source security data
            CurveData marketData = new CurveData(Interpolation.LinearInterpolation);
            int rows = ExcelSourceData.GetUpperBound(0);
            for (int i = 1; i <= rows; i++)
            {
                DateTime maturity = DateTime.FromOADate((double)ExcelSourceData[i, 1]);
                double rate = (double)ExcelSourceData[i, 2];
                string instrumentType = ((string)ExcelSourceData[i, 3]).ToUpper();
                ENUM_MARKETDATATYPE marketDataType = (ENUM_MARKETDATATYPE)Enum.Parse(typeof(ENUM_MARKETDATATYPE), instrumentType);
                marketData.AddCurveDataElement(maturity, rate, marketDataType);
            }
            return marketData;
        }
    }
    //
    //
    // interface for all curve objects
    public interface ICurve
    {
        void Create();
        double GetDF(DateTime maturity);
        double GetFWD(DateTime start);
        Dictionary<DateTime, double> GetDF(DateTime start, int nYears);
        Dictionary<DateTime, double> GetFWD(DateTime start, int nYears);
        CurveData DiscountCurve { get; }
        CurveData ForwardCurve { get; }
    }
    //
    // implementation for USD Libor curve
    public class USDLiborZeroCurve : ICurve
    {
        public readonly DayCountFactor dayCountFactor;
        public readonly AddPeriod addPeriod;
        public readonly int basis;
        public readonly Interpolator interpolator;
        public readonly DateTime settlementDate;
        public CurveData DiscountCurve { get { return this.discountCurve; } }
        public CurveData ForwardCurve { get { return this.forwardCurve; } }
        //
        private CurveData marketData;
        private CurveData curveDataSelection;
        private CurveData bootstrapCurve;
        private CurveData spotCurve;
        private CurveData discountCurve;
        private CurveData forwardCurve;
        private int nCash;
        private int nFuturesOrFRAs;
        private bool adjustmentForConvexity;
        private ConvexityAdjustment convexityAdjustment;
        private double rateVolatility;
        //
        public USDLiborZeroCurve(CurveData marketData, Interpolator interpolator, AddPeriod addPeriod,
            DayCountFactor dayCountFactor, DateTime settlementDate, int nCash, int nFuturesOrFRAs,
            bool adjustmentForConvexity = false, ConvexityAdjustment convexityAdjustment = null, double rateVolatility = 0.0)
        {
            this.marketData = marketData;
            this.interpolator = interpolator;
            this.addPeriod = addPeriod;
            this.dayCountFactor = dayCountFactor;
            this.settlementDate = settlementDate;
            this.nCash = nCash;
            this.nFuturesOrFRAs = nFuturesOrFRAs;
            this.basis = 3; // HARD-CODED !! for USD Libor curve
            this.adjustmentForConvexity = adjustmentForConvexity; // optional parameter
            this.convexityAdjustment = convexityAdjustment; // optional parameter
            this.rateVolatility = rateVolatility; // optional parameter
        }
        public void Create()
        {
            // sequence of private methods for creating spot discount curve and
            // simply-compounded forward rate curve for a given set of market data
            checkMarketData();
            selectCurveData();
            bootstrapDiscountFactors();
            createSpotCurve();
            createDiscountCurve();
            createForwardCurve();
        }
        // get discount factor for a given maturity date
        public double GetDF(DateTime maturity)
        {
            return discountCurve.GetMarketRate(ENUM_MARKETDATATYPE.DF, maturity, dayCountFactor);
        }
        // get dictionary consisting of date and discount factor for a date schedule
        public Dictionary<DateTime, double> GetDF(DateTime start, int nYears)
        {
            List<DateTime> schedule = DateConvention.CreateDateSchedule(start, nYears, basis, ENUM_PERIODTYPE.MONTHS, addPeriod);
            Dictionary<DateTime, double> curve = new Dictionary<DateTime, double>();
            schedule.ForEach(it => curve.Add(it, GetDF(it)));
            return curve;
        }
        // get simply-compounded forward rate for a given start date
        public double GetFWD(DateTime start)
        {
            return forwardCurve.GetMarketRate(ENUM_MARKETDATATYPE.FORWARDRATE, start, dayCountFactor);
        }
        // get dictionary consisting of date and simply-compounded forward rate for a date schedule
        public Dictionary<DateTime, double> GetFWD(DateTime start, int nYears)
        {
            List<DateTime> schedule = DateConvention.CreateDateSchedule(start, nYears, basis, ENUM_PERIODTYPE.MONTHS, addPeriod);
            Dictionary<DateTime, double> curve = new Dictionary<DateTime, double>();
            schedule.ForEach(it => curve.Add(it, GetFWD(it)));
            return curve;
        }
        // use interpolated spot discount factor curve for calculating
        // simply-compounded forward rates for all required maturities (basis)
        // note : maturity element of the forward curve stores the information
        // on when the 3-month period starts for a given forward rate element
        private void createForwardCurve()
        {
            forwardCurve = new CurveData(interpolator);
            int n = discountCurve.Count();
            DateTime maturity;
            double dt = 0.0;
            double fdf = 0.0;
            double f = 0.0;
            //
            for (int i = 0; i < n; i++)
            {
                if (i == 0)
                {
                    // first forward rate is the first spot rate
                    maturity = discountCurve[i].MaturityDate;
                    fdf = discountCurve[i].Rate;
                    dt = dayCountFactor(settlementDate, maturity);
                    f = ((1 / fdf) - 1) / dt;
                    forwardCurve.AddCurveDataElement(settlementDate, f, ENUM_MARKETDATATYPE.FORWARDRATE);
                }
                else
                {
                    // other forward rates are calculated recursively
                    // from previous spot discount factors
                    maturity = discountCurve[i].MaturityDate;
                    DateTime previousMaturity = discountCurve[i - 1].MaturityDate;
                    fdf = discountCurve[i].Rate / discountCurve[i - 1].Rate;
                    dt = dayCountFactor(previousMaturity, maturity);
                    f = ((1 / fdf) - 1) / dt;
                    forwardCurve.AddCurveDataElement(previousMaturity, f, ENUM_MARKETDATATYPE.FORWARDRATE);
                }
            }
        }
        // use continuously compounded spot rate curve for interpolating 
        // continuously compounded spot rates for all required maturities 
        // and convert these spot rates back to spot discount factors
        private void createDiscountCurve()
        {
            discountCurve = new CurveData(interpolator);
            DateTime finalCurveDate = spotCurve.ElementAt(spotCurve.Count() - 1).MaturityDate;
            DateTime t;
            int counter = 0;
            double dt = 0.0;
            double r = 0.0;
            double df = 0.0;
            //
            do
            {
                counter++;
                t = addPeriod(settlementDate, ENUM_PERIODTYPE.MONTHS, basis * counter);
                dt = dayCountFactor(settlementDate, t);
                r = spotCurve.GetMarketRate(ENUM_MARKETDATATYPE.SPOTRATE, t, dayCountFactor);
                df = Math.Exp(-r * dt);
                discountCurve.AddCurveDataElement(t, df, ENUM_MARKETDATATYPE.DF);
            } while (t < finalCurveDate);
        }
        // create continuously compounded spot rate curve 
        // from bootstrapped discount factors
        private void createSpotCurve()
        {
            spotCurve = new CurveData(interpolator);
            double t = 0.0;
            double r = 0.0;
            int n = bootstrapCurve.Count();
            for (int i = 0; i < n; i++)
            {
                t = dayCountFactor(settlementDate, bootstrapCurve.ElementAt(i).MaturityDate);
                r = -Math.Log(bootstrapCurve.ElementAt(i).Rate) / t;
                spotCurve.AddCurveDataElement(bootstrapCurve.ElementAt(i).MaturityDate, r, ENUM_MARKETDATATYPE.SPOTRATE);
            }
        }
        // use bootstrap algorithm to create spot discount factors
        // from all selected curve data elements
        private void bootstrapDiscountFactors()
        {
            bootstrapCurve = new CurveData(interpolator);
            double dt = 0.0;
            double r = 0.0;
            double df = 0.0;
            double Q = 0.0;
            int n = curveDataSelection.Count();
            //
            for (int i = 0; i < n; i++)
            {
                if (curveDataSelection[i].InstrumentType == ENUM_MARKETDATATYPE.CASH)
                {
                    dt = dayCountFactor(settlementDate, curveDataSelection[i].MaturityDate);
                    r = curveDataSelection[i].Rate;
                    df = 1 / (1 + r * dt);
                    bootstrapCurve.AddCurveDataElement(curveDataSelection[i].MaturityDate, df, ENUM_MARKETDATATYPE.DF);
                }
                if ((curveDataSelection[i].InstrumentType == ENUM_MARKETDATATYPE.FRA) |
                    (curveDataSelection[i].InstrumentType == ENUM_MARKETDATATYPE.FUTURE))
                {
                    dt = dayCountFactor(curveDataSelection[i - 1].MaturityDate, curveDataSelection[i].MaturityDate);
                    r = curveDataSelection[i].Rate;
                    df = bootstrapCurve.ElementAt(i - 1).Rate / (1 + r * dt);
                    bootstrapCurve.AddCurveDataElement(curveDataSelection[i].MaturityDate, df, ENUM_MARKETDATATYPE.DF);
                    //
                    if ((curveDataSelection[i + 1].InstrumentType == ENUM_MARKETDATATYPE.SWAP))
                        Q += bootstrapCurve.ElementAt(i).Rate * dayCountFactor(settlementDate, curveDataSelection[i].MaturityDate);
                }
                //
                if (curveDataSelection[i].InstrumentType == ENUM_MARKETDATATYPE.SWAP)
                {
                    r = curveDataSelection[i].Rate;
                    dt = dayCountFactor(bootstrapCurve.ElementAt(i - 1).MaturityDate, curveDataSelection[i].MaturityDate);
                    df = (1 - r * Q) / (r * dt + 1);
                    bootstrapCurve.AddCurveDataElement(curveDataSelection[i].MaturityDate, df, ENUM_MARKETDATATYPE.DF);
                    Q += (df * dt);
                }
            }
        }
        // select rate instruments to be used from a given set of curve data elements
        private void selectCurveData()
        {
            curveDataSelection = new CurveData(interpolator);
            int counter = 0;
            double rate = 0.0;
            DateTime maturityDate;
            //
            // select cash securities
            for (int i = 1; i <= nCash; i++)
            {
                counter++;
                maturityDate = addPeriod(settlementDate, ENUM_PERIODTYPE.MONTHS, basis * counter);
                // check if cash rate for required maturity exists
                if (!marketData.ElementLookup(ENUM_MARKETDATATYPE.CASH, maturityDate))
                    throw new Exception("USDLiborZeroCurve error : required cash securities are missing");
                rate = marketData.GetMarketRate(ENUM_MARKETDATATYPE.CASH, maturityDate, dayCountFactor);
                curveDataSelection.AddCurveDataElement(maturityDate, rate, ENUM_MARKETDATATYPE.CASH);
            }
            // select fra or futures contracts
            if (marketData.ElementLookup(ENUM_MARKETDATATYPE.FRA))
            {
                for (int i = 1; i <= nFuturesOrFRAs; i++)
                {
                    if (i > 1) counter++;
                    maturityDate = addPeriod(settlementDate, ENUM_PERIODTYPE.MONTHS, basis * counter);
                    // check if fra rate for required maturity exists
                    if (!marketData.ElementLookup(ENUM_MARKETDATATYPE.FRA, maturityDate))
                        throw new Exception("USDLiborZeroCurve error : required FRA contracts are missing");
                    rate = marketData.GetMarketRate(ENUM_MARKETDATATYPE.FRA, maturityDate, dayCountFactor);
                    curveDataSelection.AddCurveDataElement(addPeriod(maturityDate, ENUM_PERIODTYPE.MONTHS, basis), rate, ENUM_MARKETDATATYPE.FRA);
                }
            }
            else
            {
                for (int i = 1; i <= nFuturesOrFRAs; i++)
                {
                    if (i > 1) counter++;
                    maturityDate = addPeriod(settlementDate, ENUM_PERIODTYPE.MONTHS, basis * counter);
                    // check if implied futures rate for required maturity exists
                    if (!marketData.ElementLookup(ENUM_MARKETDATATYPE.FUTURE, maturityDate))
                        throw new Exception("USDLiborZeroCurve error : required futures contracts are missing");
                    rate = marketData.GetMarketRate(ENUM_MARKETDATATYPE.FUTURE, maturityDate, dayCountFactor);
                    //
                    // forward rate = futures rate - convexity adjustment
                    if (adjustmentForConvexity)
                    {
                        double t1 = dayCountFactor(settlementDate, maturityDate);
                        double t2 = t1 + (basis / 12.0);
                        rate -= convexityAdjustment(rateVolatility, t1, t2);
                    }
                    curveDataSelection.AddCurveDataElement(addPeriod(maturityDate, ENUM_PERIODTYPE.MONTHS, basis), rate, ENUM_MARKETDATATYPE.FUTURE);
                }
            }
            // select swap contracts
            DateTime lastSwapYear = marketData[marketData.Count() - 1].MaturityDate;
            DateTime lastFRAOrFutureYear = curveDataSelection[curveDataSelection.Count() - 1].MaturityDate;
            int nSwaps = (lastSwapYear.Year - lastFRAOrFutureYear.Year);
            for (int i = 1; i <= nSwaps; i++)
            {
                counter++;
                maturityDate = addPeriod(settlementDate, ENUM_PERIODTYPE.YEARS, i + 1);
                // check if swap rate for required maturity exists
                if (!marketData.ElementLookup(ENUM_MARKETDATATYPE.SWAP, maturityDate))
                    throw new Exception("USDLiborZeroCurve error : required swap contracts are missing");
                rate = marketData.GetMarketRate(ENUM_MARKETDATATYPE.SWAP, maturityDate, dayCountFactor);
                curveDataSelection.AddCurveDataElement(maturityDate, rate, ENUM_MARKETDATATYPE.SWAP);
            }
        }
        // rough diagnostics : check for completely non-existing market data
        // requirement : all three rate categories (cash, FRA/futures, swaps) 
        // must be provided by the client in order to create the curves
        private void checkMarketData()
        {
            // cash securities
            if (!marketData.ElementLookup(ENUM_MARKETDATATYPE.CASH))
                throw new Exception("LiborZeroCurve error : cash securities are required to build the curve");
            //
            // fra/futures contracts
            if ((!marketData.ElementLookup(ENUM_MARKETDATATYPE.FUTURE)) && (!marketData.ElementLookup(ENUM_MARKETDATATYPE.FRA)))
                throw new Exception("LiborZeroCurve error : FRA or futures contracts are required to build the curve");
            //
            // swap contracts
            if (!marketData.ElementLookup(ENUM_MARKETDATATYPE.SWAP))
                throw new Exception("LiborZeroCurve error : swap contracts are required to build the curve");
        }
    }
    //
    //
    // container class for holding multiple curve data elements
    public class CurveData : IEnumerable<CurveDataElement>
    {
        private List<CurveDataElement> curveDataElements;
        private Interpolator interpolator;
        //
        public CurveData(Interpolator interpolator)
        {
            this.interpolator = interpolator;
            curveDataElements = new List<CurveDataElement>();
        }
        public void AddCurveDataElement(DateTime maturity, double rate, ENUM_MARKETDATATYPE instrumentType)
        {
            curveDataElements.Add(new CurveDataElement(maturity, rate, instrumentType));
        }
        public void AddCurveDataElement(CurveDataElement curveDataElement)
        {
            curveDataElements.Add(curveDataElement);
        }
        // implementation for generic IEnumerable
        public IEnumerator<CurveDataElement> GetEnumerator()
        {
            foreach (CurveDataElement curveDataElement in curveDataElements)
            {
                yield return curveDataElement;
            }
        }
        // implementation for non-generic IEnumerable
        IEnumerator IEnumerable.GetEnumerator()
        {
            return GetEnumerator();
        }
        // read-only indexer
        public CurveDataElement this[int index]
        {
            get
            {
                return curveDataElements[index];
            }
        }
        public double GetMarketRate(ENUM_MARKETDATATYPE instrumentType, DateTime maturity, DayCountFactor dayCountFactor)
        {
            // filter required market data elements by instrument type
            List<CurveDataElement> group = curveDataElements.Where(it => it.InstrumentType == instrumentType).ToList<CurveDataElement>();
            //
            // extract maturity and rate into dictionary object
            Dictionary<DateTime, double> data = new Dictionary<DateTime, double>();
            group.ForEach(it => data.Add(it.MaturityDate, it.Rate));
            //
            // get market rate for a given date by using given interpolation delegate method
            return interpolator(dayCountFactor, data, maturity);
        }
        // check if elements with specific instrument type and maturity exists
        public bool ElementLookup(ENUM_MARKETDATATYPE instrumentType, DateTime maturity)
        {
            // first, filter required market data elements
            List<CurveDataElement> group = curveDataElements.Where(it => it.InstrumentType == instrumentType).ToList<CurveDataElement>();
            //
            // then, check if maturity lies between min and max maturity of filtered group
            bool hasElement = ((maturity >= group.Min(it => it.MaturityDate)) && (maturity <= group.Max(it => it.MaturityDate)));
            return hasElement;
        }
        // check if elements with only specific instrument type exists
        public bool ElementLookup(ENUM_MARKETDATATYPE instrumentType)
        {
            int elements = curveDataElements.Count(it => it.InstrumentType == instrumentType);
            bool hasElements = false;
            if (elements > 0) hasElements = true;
            return hasElements;
        }
    }
    //
    //
    // class holding information on one curve data element
    public class CurveDataElement
    {
        private DateTime maturityDate;
        private double rate;
        private ENUM_MARKETDATATYPE rateType;
        //
        public DateTime MaturityDate { get { return this.maturityDate; } }
        public double Rate { get { return this.rate; } }
        public ENUM_MARKETDATATYPE InstrumentType { get { return this.rateType; } }
        //
        public CurveDataElement(DateTime maturity, double rate, ENUM_MARKETDATATYPE rateType)
        {
            this.maturityDate = maturity;
            this.rate = rate;
            this.rateType = rateType;
        }
    }
    //
    //
    // static library class for handling date-related convention calculations
    public static class DateConvention
    {
        // calculate time difference between two dates by using ACT/360 convention
        public static double ACT360(DateTime start, DateTime end)
        {
            return (end - start).TotalDays / 360;
        }
        // create a list of scheduled dates for a given basis and date convention
        public static List<DateTime> CreateDateSchedule(DateTime start, int nYears, int basis,
            ENUM_PERIODTYPE periodType, AddPeriod addPeriod)
        {
            List<DateTime> schedule = new List<DateTime>();
            int nPeriods = nYears * (12 / basis);
            for (int i = 1; i <= nPeriods; i++)
            {
                schedule.Add(addPeriod(start, periodType, (basis * i)));
            }
            return schedule;
        }
        // add period into a given date by using modified following convention
        public static DateTime AddPeriod_ModifiedFollowing(DateTime start, ENUM_PERIODTYPE periodType, int period)
        {
            DateTime dt = new DateTime();
            //
            switch (periodType)
            {
                case ENUM_PERIODTYPE.MONTHS:
                    dt = start.AddMonths(period);
                    break;
                case ENUM_PERIODTYPE.YEARS:
                    dt = start.AddYears(period);
                    break;
            }
            //
            switch (dt.DayOfWeek)
            {
                case DayOfWeek.Saturday:
                    dt = dt.AddDays(2.0);
                    break;
                case DayOfWeek.Sunday:
                    dt = dt.AddDays(1.0);
                    break;
            }
            return dt;
        }
        // calculate value for convexity adjustment for a given time period
        public static double SimpleConvexityApproximation(double rateVolatility, double start, double end)
        {
            return 0.5 * (rateVolatility * rateVolatility * start * end);
        }
    }
    //
    //
    // static library class for storing interpolation methods to be used by delegates
    public static class Interpolation
    {
        public static double LinearInterpolation(DayCountFactor dayCountFactor,
            Dictionary<DateTime, double> data, DateTime key)
        {
            double value = 0.0;
            int n = data.Count;
            //
            // boundary checkings
            if ((key < data.ElementAt(0).Key) || (key > data.ElementAt(data.Count - 1).Key))
            {
                if (key < data.ElementAt(0).Key) throw new Exception("Interpolation error : lower bound violation");
                if (key > data.ElementAt(data.Count - 1).Key) throw new Exception("Interpolation error : upper bound violation");
            }
            else
            {
                // iteration through all existing elements
                for (int i = 0; i < n; i++)
                {
                    if ((key >= data.ElementAt(i).Key) && (key <= data.ElementAt(i + 1).Key))
                    {
                        double t = dayCountFactor(data.ElementAt(i).Key, data.ElementAt(i + 1).Key);
                        double w = dayCountFactor(data.ElementAt(i).Key, key) / t;
                        value = data.ElementAt(i).Value * (1 - w) + data.ElementAt(i + 1).Value * w;
                        break;
                    }
                }
            }
            return value;
        }
    }
}

EXCEL INTERFACING

For interfacing the previous C# program with Excel, carefully follow all the instructions given in this blog post. Add another Class module (a new cs-file) into this project and copyPaste the following program into this file. This is the program (CreateUSDLiborCurves), which will be called from our Excel worksheet.

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

namespace CurveAddin
{
    public static class CurveAddin
    {
        public static void CreateUSDLiborCurves()
        {
            try
            {
                // build market data from Excel worksheet
                MarketDataBuilder builder = new ExcelBuilder();
                CurveData marketData = builder.Build();
                DateTime settlementDate = ((ExcelBuilder)builder).SettlementDate;
                //
                // construct USD Libor curve object
                // HARD-CODED parameters : 
                // interpolation method, date conventions, number of contracts for cash (n=1) and futures (n=3)
                ICurve curve = new USDLiborZeroCurve(marketData, Interpolation.LinearInterpolation, 
                    DateConvention.AddPeriod_ModifiedFollowing, DateConvention.ACT360, settlementDate, 1, 3);
                curve.Create();
                //
                // read discount factor and forward rate data into 2d-array
                int rows = curve.DiscountCurve.Count();
                int cols = 3;
                dynamic[,] data = new dynamic[rows, cols];
                for (int i = 0; i < rows; i++)
                {
                    data[i, 0] = curve.DiscountCurve[i].MaturityDate.ToOADate();
                    data[i, 1] = curve.DiscountCurve[i].Rate;
                    data[i, 2] = curve.ForwardCurve[i].Rate;
                }
                //
                // print curve data into Excel worksheet
                (new ExcelPrinter(data)).Print();
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }
        }
    }
}

EXCEL WORKSHEET SETTINGS

Prepare the following market data (Flavell's book and Excel workbook on chapter three) and named ranges (marked with yellow color) into Excel worksheet. The program will take settlement date and market data range (date, rate, rate type) as input parameters. Note, that some of the parameters needed to create the curves have been hard-coded in the program (interpolation method, date conventions, number of contracts for cash and futures). However, it should be fairly straightforward to include all these parameters to be read directly from Excel worksheet. Finally, insert a form control button into worksheet and assign a macro for it (CreateUSDLiborCurves).

Finally, a couple of notes concerning the market data setting. When creating Curve object, Client has to provide number of cash securities and number futures/FRA contracts as input parameters in constructor. Example program has been hard-coded to use one cash security and three futures contracts and it will hereby use Libor swap contracts starting on the year two. Now, for setting market data for this program, there are some general rules. First, the latest maturity for cash securities has to be later than the first maturity for futures/FRA contracts. Also, the last future/FRA maturity has to be later than the first swap contract maturity date minus one year.

TEST RUN

After I press button in my Excel, I will get the following results (columns H to J) from the program. The range consists of dates (quarterly up to 8.2.2038), discount factors and simply-compounded Libor forward rates for USD. The first result row should be interpreted as follows : discount factor (0.99220) gives factor for discounting three-month cash flow. Simply-compounded Libor forward rate (3.1450 %) gives three-month forward rate for a period, which is starting on 6.2.2008 and ending 6.5.2008. Similarly, Libor forward rate (2.7837 %) gives three-month forward rate for a period, which is starting on 6.5.2008 and ending 6.8.2008.

After this, using generated curves (discount factors and forward rates) is straightforward. As an example, I have calculated PV for 2-year cap on 3-month USD Libor. After creating date schedule for this security, getting forward rates and discount factors can be requested by using familiar Excel worksheet functions.

AFTERTHOUGHTS

The procedure of creating discount factors and Libor forward rates programmatically in C#, has been fully opened in this blog post. Source market data and creation procedures are following examples taken from the book written by Richard Flavell. With the tools presented in this blog, one should also be able to interface this program with Excel, if so desired.

I would like to thank Govert Van Drimmelen again for his amazing Excel-DNA, what I am always using for interfacing my C# program with Excel. 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. Remember also, that Excel-DNA is an open-source project, and we (the happy users) can invest its future development by making a donation.

Finally, Thanks for spending your precious time in here and reading my blog.

-Mike Juniperhill

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.