- Create Cholesky Decomposition matrix A of input correlation matrix
- Generate a vector of independent normal random variables Z
- Compute a vector of correlated normal random variables by using Cholesky matrix X = AZ
- Convert X back to uniform plane [0,1] to get the matrix U containing correlated uniform random variables
- Use matrix U and Inverse Transform Sampling to generate correlated random variables for any marginal distributions.
Main program flow
Director (main program) creates parameter wrapper (Dictionary data structure) for Copula. Director creates correlation matrix for Copula as Matrix object (custom data structure). Director creates random number generator implementation for Copula. Director sets the number of simulations and uniform transformation condition for Copula. Director creates Copula implementation as Gaussian Copula and initializes the model with required data and objects (init).
Copula model gets independent normal random numbers as Matrix object from Random implementation (aggregated in Copula). Copula uses Cholesky decomposition for creating correlated normal random numbers. Copula transforms simulated correlated normal random numbers into uniform plane (optional). Finally, director gets correlated random numbers from Copula as Matrix object.
Generator for random numbers
Readers might be aware, that random number quality produced by Excel Rand function has been reported to be insufficient. Also, the use of Excel NormsInv function is irritatingly slow. For this implementation, the more efficient tools have been employed.
The source for an algorithm implementation of Mersenne Twister was found in this page. Download a zip file from the page and look for mt19937.dll. Save this dll file into C:\temp folder. The example program presented below has been configured so, that it searches that dll file from that folder. The source code for an algorithm for computing the inverse normal cumulative distribution function was acquired from Peter Acklam's web page.
The following program is a direct implementation for the UML presented above. Director is the main program in VBA (tester). At this point, we should reference the library for Dictionary data structure (VB editor - Tools - References - Microsoft Scripting Runtime).
CopyPaste enumerators into new VBA standard module (name = Enumerators)
Next, we create the actual tester program (director). CopyPaste the following code into standard VBA module (name = MainProgram). There are some source data read from Excel worksheet Sheet1 in the program presented below. Set correlation matrix into Excel and give a name for that range ("_correlation"). Similarly, give range names for number of simulations ("_simulations") and uniform transform condition ("_transform"). Also, set up a range name for output ("_dataDump").
After this, we create ICopula interface. CopyPaste the following code into a new VBA class module (name = ICopula).
CopyPaste the following ICopula implementation into a new VBA class module (name = GaussianCopula).
Then, we create interface for random number generator. CopyPaste the following code into a new VBA class module (name = IRandom).
Next, we create implementation for IRandom. CopyPaste the following code into a new VBA class module (name = MersenneTwister).
Finally, we create our custom data structure class, which is used extensively in this design. CopyPaste the following code into a new VBA class module (name = Matrix).
After creating all the previous components in VBA, the program is ready for the use.
Correlated random numbers has been simulated for bi-variate case. The following scatter graph shows the results for 1000 simulated correlated normal random numbers (rho = 0.76). Note, that in Copula implementation, class member transform is FALSE and hereby, uniform transformation is not performed.
Setting class member transform to be TRUE, performs uniform transformation and the results are plotted within the following scatter graph.
So, this Copula implementation is leaving an option for its user to receive correlated random numbers as normal, or receive these numbers mapped into uniform plane.
The latter scheme might be useful, if we need to simulate correlated random numbers from any other distributions. For this task, we can then use inverse transform sampling. As an example, the following scatter chart is showing the results for 1000 simulated correlated exponential random numbers (rho = 0.76, lambda = 0.085).
Correlated normal random numbers were first mapped into uniform plane (setting class member transform to be TRUE) and then transformed to exponentially distributed correlated random numbers with the inverse cumulative distribution function.
Presented design for VBA is relatively easy to implement. Also, it has a lot of flexibility. Say, we would like to create implementation for Student Copula, we would just have to create new implementation from ICopula interface. Doing this does not have any changes to be made into existing program design, since we are programming to an interface, not to an implementation. The same applies for generating random numbers. It is now easy to create your own generator for any existing Copula design, if you some day manage to create something better than MT algorithm. A small performance penalty is paid with all the function calls made using Matrix object (which is technically just wrapping arrays into a manageable class).
Creating numerical algorithms and solutions is extremely interesting and rewarding. However, there are a lot of time spent for testing and debugging and still, after all efforts, you might feel like walking on thin ice sometimes. Self-made algorithms could be sometimes unreliably, unstable or not accurate enough and for that reason, doing "anything serious" with Copulas (such as creating production pricing tools) I would recommend to check numerical libraries with "proven industrial strength", such as NAG by Numerical Algorithms Group. Along with the existing libraries for C++/C#, NAG has also Fortran library, which can be easily used also with VBA.
Needless to say, of course, that we get the same results with just a few lines of code with the tools like Matlab or Python. However, the interest of this blog is on the development side of programs. My view is that, as a developer, implementing such algorithms are excellent way to learn in order to become a better developer. However, this is only my personal view on the matter.
Anyway, that's all I wanted to share this time. Again, I hope that this post could help you to solve some of your programming problems with VBA. Happy New Year for everybody!