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

Data structure for matrix in VBA

Large part of programming financial models has something to do with handling and operating matrices. However, for handling any matrix data, there is not ready-made data structure available for this purpose in VBA. The only way to get around this limitation, is to write your own custom data structure by using existing data structures, such as arrays. Within my previous posting on Gaussian Copula implementation, I created one such custom data structure, a Matrix class. Technically speaking, this class is just wrapping arrays into a manageable object with setters and getters, plus provides some of the most common matrix operations for the user.

The idea was very noble and I was quite happy with the first development candidate. I especially liked the idea of data structure being an object, also for schematic and semantic reasons. Plus, I thought that I would finally get rid of all those boring extra lines of code needed, when using plain arrays. I also thought that only a small performance penalty would be paid, with all function calls made using class accessors (push, at). However, as I was prototyping Monte Carlo basket equity option pricing and doing matrix operations with large matrices, I quickly realized that the cost of processing matrix operations was definitely way too high.

Testing processing times

The issue was bothering me in a such way, that I finally wanted to get some hard-tested facts on handling and operating matrix structures. For this reason, I prepared test cases for three different matrix schemes. Within these test cases, matrix data structure was implemented by using

1. Two-dimensional array
2. Variant array of double arrays (jagged array)
3. Matrix class

The use of Collection and Dictionary data structures has been left out of this experiment completely, since these data structures are empirically known to be more costly than the use of arrays. Nick Webber has been doing some serious woodshedding with these issues in his excellent book Implementing Models of Financial Derivatives: Object Oriented Applications with VBA within the chapter 16.

Within test cases mentioned, a simple procedural program performs the following operations for all matrix schemes described above

1. Creates three matrices (A, B, C)
2. Fills two matrices (A, B) with random numbers
3. Performs matrix multiplication (A, B) and returns the result into matrix (C)

Time elapsed was recorded only for the actual matrix multiplication operation. For each matrix schemes, matrix B rows (A columns) were dimensioned from 100 000  to 2 000 000 and matrix B columns (A rows) were assumed to be constant 10. For example, in the first calculation, we multiplied matrix A (10 * 100 000) with matrix B (100 000 * 10) and received matrix C (10 * 10). In the second calculation matrix dimensions were A (10 * 200 000) and B (200 000 * 10) and we received matrix C (10 * 10), and so on.

Cold shower

The following chart is presenting the findings of this experiment.



We can clearly see, that a simple two-dimensional array is the most efficient data structure for handling large matrix operations in VBA. There is just no way out of this, period. Testing program is presented below. You can just copy-paste it into a new standard VBA module, if you are interested to run it in your own laptop. Remember to create reference to Microsoft Scripting Runtime library.

Option Explicit
'
Private Declare Function GetTickCount Lib "kernel32.dll" () As Long
Private Const filePath As String = "C:\temp\matrix_log.txt"
Private fileSystem As Scripting.FileSystemObject
Private textWriter As TextStream
Private textWriterEnabled As Boolean
Private stream As String
Private startTime As Long
Private endTime As Long
Private timeElapsed As Double
'
Sub tester()
    '
    Set fileSystem = New Scripting.FileSystemObject
    Set textWriter = fileSystem.OpenTextFile(filePath, ForWriting, True)
    textWriterEnabled = True
    '
    Dim r As Long: r = 100000
    Dim c As Long: c = 10
    Dim i As Long
    For i = 0 To 19
        testRun_2DArray r + i * r, c
        testRun_arrayOfArrays r + i * r, c
        testRun_matrixClass r + i * r, c
    Next i
    '
    MsgBox "completed", vbInformation, "timer log file for matrix operations"
    Set textWriter = Nothing
    Set fileSystem = Nothing
End Sub
'
Private Function testRun_2DArray(ByVal r As Long, ByVal c As Long)
    '
    ' create 2-dim arrays needed
    Dim m1() As Double: ReDim m1(1 To r, 1 To c)
    Dim m2() As Double: ReDim m2(1 To c, 1 To r)
    Dim m3() As Double: ReDim m3(1 To c, 1 To c)
    Dim i As Long, j As Long, k As Long
    '
    ' fill array 1
    For i = 1 To r
        For j = 1 To c
            m1(i, j) = Rnd
        Next j
    Next i
    '
    ' fill array 2
    For i = 1 To r
        For j = 1 To c
            m2(j, i) = Rnd
        Next j
    Next i
    '
    ' perform matrix multiplication
    startTime = GetTickCount()
    Dim v As Double
    '
    For i = 1 To c
        For j = 1 To c
            v = 0
            '
            For k = 1 To r
                v = v + m2(i, k) * m1(k, j)
            Next k
            m3(i, j) = v
        Next j
    Next i
    '
    ' write timer results into log file
    endTime = GetTickCount()
    timeElapsed = (endTime - startTime) / 1000
    stream = "testRun_2DArray;" & r & ";" & timeElapsed
    If (textWriterEnabled) Then textWriter.WriteLine stream
End Function
'
Private Function testRun_arrayOfArrays(ByVal r As Long, ByVal c As Long)
    '
    ' create arrays of arrays needed
    Dim i As Long, j As Long, k As Long
    Dim inner() As Double
    '
    Dim m1() As Variant: ReDim m1(1 To r)
    For i = 1 To r
        ReDim inner(1 To c)
        m1(i) = inner
    Next i
    '
    Dim m2() As Variant: ReDim m2(1 To c)
    For i = 1 To c
        ReDim inner(1 To r)
        m2(i) = inner
    Next i
    '
    Dim m3() As Variant: ReDim m3(1 To c)
    For i = 1 To c
        ReDim inner(1 To c)
        m3(i) = inner
    Next i
    '
    ' fill array 1
    For i = 1 To r
        For j = 1 To c
            m1(i)(j) = Rnd
        Next j
    Next i
    '
    ' fill array 2
    For i = 1 To r
        For j = 1 To c
            m2(j)(i) = Rnd
        Next j
    Next i
    '
    ' perform matrix multiplication
    startTime = GetTickCount()
    Dim v As Double
    '
    For i = 1 To c
        For j = 1 To c
            v = 0
            '
            For k = 1 To r
                v = v + m2(i)(k) * m1(k)(j)
            Next k
            m3(i)(j) = v
        Next j
    Next i
    '
    ' write timer results into log file
    endTime = GetTickCount()
    timeElapsed = (endTime - startTime) / 1000
    stream = "testRun_arrayOfArrays;" & r & ";" & timeElapsed
    If (textWriterEnabled) Then textWriter.WriteLine stream
End Function
'
Private Function testRun_matrixClass(ByVal r As Long, ByVal c As Long)
    '
    ' create 2 matrix objects
    Dim m1 As New Matrix: m1.init r, c
    Dim m2 As New Matrix: m2.init c, r
    Dim i As Long, j As Long
    '
    ' fill matrix 1
    For i = 1 To r
        For j = 1 To c
            m1.push i, j, Rnd
        Next j
    Next i
    '
    ' fill matrix 2
    For i = 1 To c
        For j = 1 To r
            m2.push i, j, Rnd
        Next j
    Next i
    '
    ' perform matrix multiplication
    Dim m3 As New Matrix
    startTime = GetTickCount()
    Set m3 = m3.multiplication(m2, m1)
    '
    ' write timer results into log file
    endTime = GetTickCount()
    timeElapsed = (endTime - startTime) / 1000
    stream = "testRun_matrixClass;" & r & ";" & timeElapsed
    If (textWriterEnabled) Then textWriter.WriteLine stream
    '
    Set m3 = Nothing
    Set m2 = Nothing
    Set m1 = Nothing
End Function
'

Small matrices

Additionally, I also tested using MMULT function with simple two-dimensional arrays. Efficiency of this method is only marginally better, than using two-dimensional arrays with the code provided above (testRun_2DArray). Moreover, there is a limit of the sizes of matrices what we can feed for this worksheet function and those are surprisingly low. For example, trying to multiply A (10 * 100 000) with B (100 000 * 10) leads to runtime error.

The chart below is presenting the results for test cases with small matrices, including test case for using MMULT worksheet function. For each matrix schemes, matrix B rows (A columns) were dimensioned from 1 000  to 65 000 and matrix B columns (A rows) were assumed to be constant 10. For example, in the first calculation, we multiplied matrix A (10 * 1 000) with matrix B (1 000 * 10) and received matrix C (10 * 10). In the second calculation matrix dimensions were A (10 * 2 000) and B (2 000 * 10) and we received matrix C (10 * 10), and so on.



The direction of the results is the same as with large matrices. Using MMULT worksheet function is the most efficient choice, but only marginally better than using simple two-dimensional arrays. The use of Matrix wrapper class for small matrix operations can still be seen as reasonable choice, since the time loss compared to more efficient choices is after all, relatively small.

Final run

Just for the curious, I wanted to compare VBA matrix operations efficiency results with the corresponding C++ results. For this reason, I used dynamically allocated arrays. Otherwise, the actual testing program was basically the same as for VBA cases: allocate memory for arrays, fill arrays with random numbers, perform matrix multiplication and finally release the allocated memory. Time elapsed was recorded only for the actual matrix multiplication operation. The chart below is presenting the results.



In a nutshell, average efficiency ratio (VBA processing time / C++ processing time) is 5.24 for this experiment sample. Moreover, larger arrays can be handled in C++ than in VBA, since the memory is allocated from the heap memory instead of stack memory.

Afterthoughts

So, for any large and time-critical matrix operations performed in VBA, a simple two-dimensional array is the most efficient data structure which can be provided by VBA. For a small matrix operations, arrays wrapped in class can still be used. For real hardcore calculations (very large matrices, fast processing times), VBA is unfortunately not efficient tool for handling such calculations.

Life goes on - have a great weekend!

-Mike