VBA Monte Carlo risk analysis spreadsheet with correlation using the Iman-Conover method. Part 4

After the hard work of last Thursday’s post, where we developed functions to induce correlation onto our random samples, today’s is light relief by comparison, though a bit boring. This post discusses the mechanical aspects of putting it all together into something that works.

First recall the module structure (Figure 3 from Post 1). We have already completed nine of the fourteen functions, which are shown shaded grey in the figure below.

Figure 3 (modified): Module structure (click on image to enlarge)

The completed functions contain all the mathematics. The remaining ones just create worksheets and graphs and transfer data to and from them. As well as these nine, we have also done the block of code that generates individual random samples, which is part of the user input module, so it’s more like \( 9 \frac{1}{2}\) really. The function with the most arrows going into and out of it is the ‘random variable subset class’, so it would make sense to do that first.

Random variable subset class

This is where we use VBA’s (limited) object-oriented capability. The aim is to hide away as much as possible of the code that would otherwise have to go into the user-input module so that the latter can be clean, simple and easy to read.

Also, we want to be able to divide the set of input variables into subsets whose correlation structure is treated separately. That is, we could have one subset that is independent and others whose members are correlated only with other members of the same subset. Using a class makes this easier too.

Although it isn’t strictly necessary to divide the problem up in this way—we could instead have just one sample matrix and one big correlation matrix, with zeroes for pairs that are not correlated—the computational expense of matrix computations increases faster than linearly with the size of the matrix, so it speeds things up to use several smaller matrices instead of one big one where possible. Also, if one of the subsets is independent then the whole business of inducing correlation can be avoided altogether for that subset.

To enable the required matrix computations to be carried out we want one array for each subset, with one column for each variable. Using a class is ideal for this. We can have one instance of the class correspond to one subset of the variables, and group instances into collections that can be passed to functions as a single entity. This enables us to have a collection of subsets of the input (or output) variables and pass the collection to the graph plotting function, which can then arrange all the graphs on a single worksheet. If we did it any other way we’d have to change the code in the graph plotting function every time we changed the number of subsets in the model.

In VBA you can’t pass arrays into and out of an object that is an instance of a user-written class, but you can store arrays inside the object, so we can pass the randomly generated input variables into the object one value at a time, store them in an internal array and then perform all the matrix computations inside the object.

The class, which I have called ‘ClsRandomVariableSubset‘, contains two copies of the array, one that is ordered and one that is not. The way these arrays are generated depends on the type of variable the object refers to:

  1. If the object refers to a set of independent input variables, then values generated using the ‘latin hypercube’ method are stored in the ordered array. The non-ordered array is then generated by invoking a method that takes a copy of the ordered array and calls the ‘shuffle’ function to randomly shuffle it.

  2. If the object refers to a set of correlated input variables, then values generated by ‘latin hypercube’ are stored in the ordered array. The non-ordered array is then generated by invoking a method that calls the Iman-Conover function on it.

  3. If the object contains output variables, then values that appear in the output cells of the spreadsheet-model during the course of a simulation are stored in the non-ordered array. The ordered array is then generated by invoking a method that takes a copy and calls the quicksort function on it.

The class will need the following properties and methods:

Properties

The usual convention for properties of classes in VBA is that the value passed into and out of each property is stored in a ‘private’ variable inside the object whose name is the same as that of the property except that it is prepended with a lower-case letter ‘p’. Most of them involve only let and get statements, that is, they simply read values into the class object, store them internally and then make them available externally. Only those that involve something more complicated than this have their code listed below. The properties are:

NumVars

Represents the number of variables in the subset. Type Long.

NumIters

Represents the number of ‘iterations’ to be used in a simulation.Technically these are not iterations, but repeated runs of the model. However, I can’t think of a better word at the moment. Type Long. Must have the same value for all subsets in a simulation.

VariableName

A one-dimensional array of strings, indexed from 1 to NumVars. Contains descriptive names for each of the variables in the subset. Examples include ‘Capital cost’, ‘Average wind speed’, ‘electricity price’, ‘Interest rate’ and so on.

VariableSheet

A one-dimensional array of strings, indexed from 1 to NumVars, that contains the names of the worksheet(s) that contain the cells that contains each variable.

VariableRange

A one-dimensional array of strings, indexed from 1 to NumVars. Contains the The cell that contains the variable, e.g. ‘B3‘, ‘G11‘ or ‘AF22‘.

CorrelationMatrixSheet

A string variable that contains the name of the worksheet that contains the cells that contain the correlation matrix.

CorrelationMatrixRange

A string variable that contains the name of the range that contains the correlation matrix.

TempStore

A one-dimensional array of Variants, indexed from 1 to NumVars. Stores whatever values happen to be written in the input cells of the spreadsheet model before the simulation is run, so they can be put back after it has finished.

OrderedSample

A two-dimensional array of Doubles, with rows indexed from 1 to NumIters and columns from 1 to NumVars.

Sample

A two-dimensional array of Doubles, with rows indexed from 1 to NumIters and columns from 1 to NumVars.

CumulativeAbscissa and CumulativeOrdinate

These are the x- and y- axes of a cumulative distribution function. They are expressed parametrically as functions of the row index of the ordered sample array, which is a Long in the range 1 to NumIters. A single value of this index will produce a pair of values, (CumulativeAbscissa, CumulativeOrdinate), that can be plotted as a point on a graph. The complete set of indices from 1 to NumIters would contain too many points (e.g.103 or 104) to be conveniently plotted on a graph, so we can use a subset, say 100 of them, equally spaced between 1 and NumIters, and generate one pair of (CumulativeAbscissa, CumulativeOrdinate) for each of these.

CumulativeAbscissa

Read-only calculated property. A two-dimensional array of Doubles, with rows indexed from 1 to NumIters and columns indexed from 1 to NumVars. Takes as its argument the row index of the array, and returns the value of the ordered sample; OrderedSample(rowIndex, colIndex).

CumulativeOrdinate

Read-only calculated property. A two-dimensional array of Doubles, with rows indexed from 1 to NumIters and columns indexed from 1 to NumVars. Takes as its argument the row index of the array, and returns the value of rowIndex / (NumIters + 1). When plotted against CumulativeAbscissa will produce a curve of the cumulative probability distribution function.

SampleMean

Read-only calculated property. A one-dimensional array of Doubles, indexed from 1 to NumVars. Contains the arithmetic means of the columns of the sample array.

Min

Read-only calculated property. A one-dimensional array of Doubles, indexed from 1 to NumVars. Min(i) contains the value of OrderedSample(1, i).

Max

Read-only calculated property. A one-dimensional array of Doubles, indexed from 1 to NumVars. Max(i) contains the value of OrderedSample(NumIters, i).

Quantile

Read-only calculated property. A one-dimensional array of Doubles, indexed from 1 to NumVars. Quantile(p, i) returns the value of \( x\) such that the fraction of the members of the ith column of Sample that are less than or equal to \( x\) is p. The code that calculates the return value is as follows:

Public Property Get Quantile(Probability As Double, colIndex As Long) As Double

' Returns a value, x, such that the fraction of members of the sample that are
' <= x is equal to "probability". Values that are not exactly equal to numbers
' in the sample are calculated by linear interpolation between the two adjacent
' sample elements. "colIndex" is the column number.

' Test that probability is valid
    If (Probability < 1 / (NumIters + 1)) Or (Probability > NumIters / (NumIters + 1)) Then
      ' Error message
        Err.Raise Number:=vbObjectError + 4, _
        Source:="Subset.Quantile", _
        Description:="Probability outside of valid range"
        Exit Function
    End If
    
    pQuantileAbscissa = Probability * (pNumIters + 1)
    pQuantileIndex = WorksheetFunction.RoundDown(pQuantileAbscissa, 0)
    Quantile = pOrderedSample(pQuantileIndex, colIndex) + _
              (pOrderedSample((pQuantileIndex + 1), colIndex) - _
               pOrderedSample(pQuantileIndex, colIndex)) * _
              (pQuantileAbscissa - pQuantileIndex)

End Property

This property can then be called by the graph plotting function to get the median, the upper and lower quartiles and the 10th and 90th percentiles, which it then writes into the output sheets.

Variance

Read-only calculated property. A one-dimensional array of Doubles, indexed from 1 to NumVars. The value of Variance(i) is calculated to be the variance of the ith column of Sample. The code that implements this is:

Public Property Get Variance(colIndex As Long) As Double
    w = 0
    i = 0
    Do
        v = 0
        Do
            i = i + 1
            v = v + pSample(i, colIndex) ^ 2
        Loop Until (v >= 9E+300 Or i = pNumIters)
        w = w + (v / pNumIters)
    Loop Until i = pNumIters
    Variance = w - (pSampleSum(colIndex) / pNumIters) ^ 2
End Property

The reason for the unusual loop structure is to prevent the variable overflowing if the distribution contains very large values. In such cases the sum of the squares of the members of the sample could be too large for the Double variable type to contain, even though the final answer isn’t. This can be prevented by dividing the squares by NumIters before summing them. However, doing this once every iteration would add unnecessary computational burden and if the members of the sample are small could cause loss of precision. These effects can be prevented by dividing the summation into blocks such that each block is just small enough not to cause the variable to overflow, and then dividing the blocks by NumIters.

We could add other statistics, such as a measure of skewness or peakedness (kurtosis), or indeed anything else that might be useful. Feel free to include whatever you want.

Methods

I have not listed the code for the following methods because it is straightforward. If you would like to see how they are written, then a complete listing of the entire ClsRandomVariableSubset module can be seen here.

Size

Allocates the sizes of all the arrays inside the object using the ReDim command.

GenerateIndependentSample

Makes a copy of the ordered sample matrix and invokes the function ‘shuffle‘ on each column. Each column is shuffled independently of the others.

GenerateCorrelatedSample

Imports the correlation matrix from the worksheet by calling the private subroutine ‘ImportCorrelationMatrix‘, then calls the Iman-Conover function, ‘ic(X, C)‘, taking the ordered sample array and the correlation matrix as arguments, and assigning the result to the unordered sample array. The ‘ImportCorrelationMatrix‘ subroutine also performs some tricks to help the user ensure that the correlation matrix that is imported is the same as the one they wanted to import.

GenerateOrderedSample

Copies the unordered sample array to the ordered sample array and invokes the function ‘shuffle‘ on it.

Internal private subroutines and functions

To make some of the above properties and methods work properly, we also need some routines and functions whose sole purpose is to perform tasks or return values that are needed by the properties and methods themselves. These are called by properties and methods within the class module but are not accessible from the outside. These are:

pSampleSum(colIndex As Long) As Double

This function returns the sum of the ith column of the sample matrix. The return value is used in two properties, Variance and SampleMean.

ImportCorrelationMatrix

This subroutine is called by the ‘GenerateCorrelatedSample‘ method and, as its name suggests, imports the correlation matrix so that it can be used as an argument to the Iman-Conover function ‘ic(X, C)‘. The correlation coefficients must be contained in the upper triangular half of a square array of cells specified by the values of the ‘CorrelationMatrixSheet‘ and ‘CorrelationMatrixRange‘ properties. These are assumed to form the upper triangular half of a symmetric matrix. The lower half is generated by reflection in the diagonal. The user only needs to enter the upper triangular half. Row and column headings are added to the user-inputted array to enable the user to check that the correlation coefficients s/he entered correspond to the correct pair of variables. The colour of the cells from which values were imported is changed to enable the user to tell that the correct cells have been imported. A copy of the full array is printed below the user-inputted array to confirm that it has been imported correctly. A number of tests are also included to make sure that the matrix is valid.

The code is as follows (sorry about its length, feel free to skip over it if you wish):

Private Sub ImportCorrelationMatrix()

' Imports values from a worksheet and assigns them to an array inside the
' object. The values must be contained in the upper triangular half of a square
' array of cells. These are assumed to form the upper triangular half of a
' symmetric matrix. The lower half is generated by reflection in the diagonal.
' The user only needs to enter the upper triangular half. Row and column
' headings are added to the user-inputted array to enable the user to check
' that the correlation coefficients s/he entered correspond to the correct pair
' of variables. The colour of the cells from which values were imported is
' changed to enable the user to tell that the correct cells have been imported.
' A copy of the full array is printed below the user-inputted array to confirm
' that it has been imported correctly.

    ReDim pCorrelationMatrix(1 To pNumVars, 1 To pNumVars)
            
    With ThisWorkbook.Sheets(pCorrelationMatrixSheet).Range(pCorrelationMatrixRange)
        
      ' Test that the chosen range is square
        If Not .Rows.Count = .Columns.Count Then
      ' Error message
            Err.Raise Number:=vbObjectError + 1, _
            Source:="ImportCorrelationMatrix", _
            Description:="Correlation matrix range not square"
        End If
        
      ' Test that the range is the correct size for the number of variables
        If Not (.Rows.Count = pNumVars And .Columns.Count = pNumVars) Then
      ' Error message
            Err.Raise Number:=vbObjectError + 2, _
            Source:="ImportCorrelationMatrix", _
            Description:="Correlation matrix size doesn't match number of variables"
        End If
        
      ' Test that the diagonal elements are all unity
        For i = 1 To pNumVars
        ' Debug.Print "diagonal element = " & .Cells(i, i).Value
            If Not (.Cells(i, i).Value = 1) Then
                Debug.Print "error criterion met: " & i
                Err.Raise Number:=vbObjectError + 3, _
                Source:="ImportCorrelationMatrix", _
                Description:="Correlation matrix diagonal elements not all unity"
            End If
        Next i
        
        For i = 1 To pNumVars
            For j = i To pNumVars
              
              ' Import cells into array
                pCorrelationMatrix(i, j) = .Cells(i, j)
                
              ' Write column headings above or below matrix range
                If .Cells(1, 1).Row > 1 Then
                    'Debug.Print .Cells(1, 1).Row
                   .Cells(1, j).Offset(rowOffset:=-1).Value = pVariableName(j)
                Else
                   .Cells(1 + pNumVars, j).Value = pVariableName(j)
                End If
                
              ' Write row headinsg to the right of matrix range
               .Cells(i, pNumVars + 1).Value = pVariableName(i)
              
              ' Shade cells or, if already shaded, change colour
                With .Cells(i, j).Interior
                    If .Color = RGB(240, 240, 240) Then
                        .Color = RGB(200, 240, 240)
                    Else
                        .Color = RGB(240, 240, 240)
                    End If
                End With
            Next j
        Next i
    End With
        
    For i = 2 To pNumVars
        For j = 1 To i - 1
            pCorrelationMatrix(i, j) = pCorrelationMatrix(j, i)
        Next j
    Next i
    
    ' Write a copy of the full correlation matrix below the user-entered one to
    ' confirm that it has been imported correctly.
    pGap = 5
    With ThisWorkbook.Sheets(pCorrelationMatrixSheet).Range(pCorrelationMatrixRange)
        For i = 1 To pNumVars
            For j = 1 To pNumVars
                .Cells(i + pNumVars + pGap, j).Value = pCorrelationMatrix(i, j)
                .Cells(i + pNumVars + pGap, pNumVars + 1).Value = pVariableName(i)
                .Cells(pNumVars + pGap, j).Value = pVariableName(j)
            Next j
        Next i
    End With
    
End Sub

Statistics, graphs and sheets

If you thought that the above discussion of the ‘ClsRandomVariableSubset‘ class was dull, then the graph plotting function is even duller. Before that, however, there are two shorter functions to slog through that manipulate worksheets. These are ‘IsSheetThere‘ and ‘TestAndAdd‘.

IsSheetThere

This function takes a string, “SheetName” and returns a Boolean. It determines whether or not a sheet named “sheetName” exists in the workbook. If it does it returns ‘True‘ and if not, ‘False‘.

The code that does this is:

Public Function IsSheetThere(ByVal SheetName As String) As Boolean

' Usage: Y = IsSheetThere(SheetName)
'
' Determines whether or not a sheet named "sheetName" exists in the active
' workbook.

Dim TestSheet As Worksheet
Dim bReturn As Boolean
bReturn = False
    For Each TestSheet In ThisWorkbook.Worksheets
        If TestSheet.Name = SheetName Then
            bReturn = True
            Exit For
        End If
    Next TestSheet
IsSheetThere = bReturn
End Function

TestAndAdd

This function calls IsSheetThere("SheetName"). If False is returned, it creates a sheet called “SheetName“. If it returns True then it calls IsSheetThere("SheetName2"). If that returns False then it creates a sheet called "SheetName2". If returns True then it calls IsSheetThere("SheetName3") and so on until it finds a value that doesn’t already exist in the workbook. It also takes an optional argument to set the zoom level (i.e. magnification) of the worksheet. The code that does this is:
Public Function TestAndAdd(ByVal SheetName As String, Optional Zoom As Integer) As String

' Usage: Y = TestAndAdd(String, Zoom)
'
' Finds the smallest value of i such that a sheet called sheetNamei doesn't
' already exist and then creates a sheet called "SheetNamei".
'
' That is, it looks in this workbook for a worksheet named sheetName. If it
' doesn't find it, it creates it. If it does find sheetName, it searches for
' sheetName1. If it doesn't find sheetName1 it creates it. If it does find it,
' it sesarches for sheetName2 and so on.
'
' Returns the name of the added sheet as a string.

Dim TestName As String
Dim TempName As String
Dim i As Integer

' Assign the desired name to the string variable testName
If IsSheetThere(SheetName) = False Then
    TestName = SheetName
Else
    i = 2
    TempName = SheetName & i
    Do While IsSheetThere(TempName) = True
        i = i + 1
        TempName = SheetName & i
    Loop
TestName = TempName
End If

'Add a new sheet and change its .Name property to testName
Dim newSheet As Worksheet
Set newSheet = ThisWorkbook.Sheets.Add
newSheet.Name = TestName

' Adjust magnification
If Not IsMissing(Zoom) Then
    ThisWorkbook.Sheets(TestName).Activate
    ActiveWindow.Zoom = Zoom
End If

' Return name of added sheet as string
TestAndAdd = TestName
    
End Function

Graphs

Unfortunately there is no getting away from the fact that writing data into a worksheet, plotting graphs of them and arranging it all neatly requires the specification of an extremely large number of fiddly little details. These would be too tedious to list here, but if you really want to look at it, you can find it listed out in full here.

However, there are a couple of subtleties that are worth mentioning. These are as follows:

Collections

The fact that we have chosen to store data arrays and associated functions inside a class object means that one or more subsets can be added to a collection. The Graphs function can take one of these collections as an argument and loop through the members of the collection. We can have one collection for output variables and one for input variables. The Graphs function can then plot all the input functions on one worksheet and all the output variables on another, irrespective of how many variable subset objects there are in the collection.

Histogram axis labels and bin boundaries

No spreadsheet application that I am aware of has a built-in histogram chart type, so we have to use the nearest thing, which is a bar chart. This draws all its bars exactly the same width and puts the x-axis labels under the middle of each bar, rather than on bar boundaries as in a proper histogram. To cope with this, the data labels have to indicate a range, such as “0.781 to 0.924”, rather than a single value. A label like this must be a string, which the VBA code has to construct from the numbers that represent the upper and lower bin boundaries.

If the labels displayed these numbers to 15 decimal places the histogram would look silly, so they need to be rounded to a sensible length.

If the labels were rounded but the variables holding the underlying numbers still had full precision, then displayed bin widths could differ from each other by one in their last (rounded) decimal place and some values could fall into bins adjacent to the ones that the axis labels indicate they should be in.

To avoid this, the underlying variables that represent the bin boundaries need to have the same precision as their corresponding axis labels. This can be done as follows:

  1. Choose the largest exact value with precision p that is less than or equal to the smallest value in the sample and make this the left boundary of bin 1. This can be done using the worksheet function “RoundDown”.

  2. Choose the bin width (DeltaX) to be the smallest exact value with precision p such that NumBins * DeltaX is greater than or equal to the largest value in the sample. This can be done by dividing the largest value in the sample by NumBins and then applying the RoundUp worksheet function.

The worksheet functions RoundUp and RoundDown take an argument that specifies the number of decimal places to round to. We, on the other hand, want to specify the number of significant figures. We therefore need to be able to calculate the number of decimal places that corresponds to a given number of significant figures.

The key to doing this is the base-10 exponent of the number, which is the integer \( b\) such that \( x = a \times 10^{b}\), where \( x\) is the number that \( b\) is the exponent of and \( a\) is constrained to lie in the range \( 1 \leqslant a < 10\).

Taking logs gives:

\[ \begin{equation} \textrm{log}_{10}\left(x\right)=b+\textrm{log}_{10}\left(a\right) \end{equation} \]

The fact that \(a\) is constrained to lie in the range \( 1 \leqslant a < 10\) implies that:

\[ \begin{equation} 0 \leqslant \textrm{log}_{10}\left(a\right) < 1 \end{equation} \]

By definition \(b\) is an integer so \(\textrm{log}_{10}\left(x\right)\) is a decimal number whose integer part (i.e. the digits to the left of the decimal point) is equal to \(b\) and its fractional part (i.e. the digits to the right of the decimal point) is equal to \(\textrm{log}_{10}\left(a\right)\), from which it follows that:

b = Int(log10(x))

Where int() is the built-in BASIC function that returns the integer part of a decimal.

Having got the exponent, the number of decimal places is given by n = p - b - 1, where n is the number of decimal places after the point and p is the required number of significant figures. I have put the code to calculate the exponent in a stand-alone function in the maths module, which goes like this:

Public Function expon(x As Double) As Integer

' Usage: Y = expon(x)
'
' Returns the exponent to the base 10 of x. That is the value 'b' such that
' x = a * 10^b, where 1 <= a < 10.

Dim y As Double

y = Log(x) / Log(10)
expon = Int(y)

End Function

The overall code to generate the boundaries is shown here and the code that generates the axis labels is here. This includes an if statement that formats the number in standard notation if it is in the range 0.1 to 10b and scientific notation if it is outside this range.

Histogram axis limits

Samples from distributions such as the normal or log-normal, which have long asymptotic tails that extend to infinity, can sometimes contain one or two values that are a very long way from the majority of other values in the sample. If we calculate the range of the x-axis of a histogram so that it extends from the minimum to the maximum of the dataset, and then divide that range into, say, 20 bins, then it could happen that one member of the sample is so large that all the other members end up in only one or two bins. In such a situation the resulting histogram is not very informative.

This can be avoided by setting the limits of the range of the histogram in a more sophisticated way. In this project I have chosen to define the upper and lower limits of the range as being the minimum and maximum values in the sample dataset unless they are further than three standard deviations from the mean, in which case the limits are set to be three standard deviations from the mean. This has the effect of zooming in on the interesting bit of the distribution so that we can see its shape.

The code that does this is as follows:

LeftX = WorksheetFunction.Max(SampleMin, (SampleMean - 3 * SampleSigma))
RightX = WorksheetFunction.Min(SampleMax, (SampleMean + 3 * SampleSigma))

which fits here in the Graphs function.

Output

The Graphs function produces output that looks like:

Figure 8: Typical output
Figure 8: Typical output

Running the model

The routine that actually runs the model is a function called ‘RunModel‘, and carries out the following tasks:

  1. performs tests on the input and output arrays to ensure they are suitable for the calculations;

  2. copies the values currently in the input cells of the spreadsheet model into the TempStore property of the members of the input variables collection;

  3. turns off automatic screen updating and worksheet calculations;

  4. for i = 1 toNumIters
    1. reads the values of the ith row of all the input arrays into the appropriate cells of the spreadsheet model
    2. calculates the worksheet
    3. reads the values of the output cells of the spreadsheet model into the ith row of the output array
    next i
  5. turns back on automatic screen updating and worksheet calculations;

  6. copies the values of TempStore back into appropriate cells of the worksheet;

  7. generates the ordered array of the output subset(s).

The code that does this can be found here. It is called from within the user input routine after the input arrays have been generated.

User input

The user input module is a blank module into which the user writes some code that tells the other modules where to find the input and output variables, correlation matrices and so on and what to do with them. As with all subroutines in VBA, it starts with some declarations, including declaring and setting the subsets of the input and output variables as ClsRandomVariableSubset objects and assigning these objects to one of two collections—one for the input variables and one for the output variables.

These are followed by a series of blocks of code, of the form:

'3rd input variable in subset A
k = 3
InVarA.VariableName(k) = "Input 3"
InVarA.VariableSheet(k) = "Model"
InVarA.VariableRange(k) = "B4"
U = unifun(n)
For i = 1 To n
    InVarA.OrderedSample(i, k) = TriangularInv(U(i), -1.1, 1.9, 5.2)
Next i

one for each input and output variable. The module then calls the functions ‘RunModel‘ and ‘Graphs‘, which results in the simulation being run and the worksheets containing the graphs and statistics being plotted. The next post in this series will illustrate the structure of the user input module in more detail by describing a few examples of how the modules can be applied in some typical uses.

Module structure

I have decided to put the various functions and subroutines into five different modules. The reasons are as follows:

In Excel, classes must be put in their own module, and you can only have one class per class-module, so ClsRandomVariableSubset has to have its own module and this has to be called by the same name as the class.

We need to keep the user input in its own module, so that the user-input code can be kept separate from all other code. It would also be helpful if this appeared first in the list of modules in the Excel IDE. This doesn’t allow module names to begin with numbers, or to contain spaces, I have decided to call it ‘a_user_inputs

The module that contains the inverse distribution functions would also benefit from being separate, so that it is easy to see what functions are already available and to add new ones. This is called ‘mc_quantile_functions‘.

Finally, though not really necessary, I thought it would be logical to separate the mathematical functions, such as ‘chol‘ and ‘finvs‘, from the functions that carry out mechanical tasks such as plotting graphs and inserting worksheets, such as ‘TestAndAdd‘ and ‘Graphs‘. I originally thought of calling the latter ‘boring’ functions, but this could sound a bit flippant, so I have decided to call them ‘admin’ functions instead, which is less flippant but which means the same thing.

Consequently, our five modules are:

  1. a_user_inputs
  2. ClsRandomVariableSubset
  3. mc_admin_functions
  4. mc_maths_fucntions
  5. mc_quantile_functions

A zip file containing all the modules except the user input module (which needs to be written afresh for each model) can be downloaded here, and an xlsm spreadsheet containing a placeholder model and all five modules can be downloaded here.

We now have an application that works. All that remains now is to apply it to a few interesting problems. That will be the subject of my next post. Unfortunately, I have some urgent other stuff to get on with, so the next and final post of this series will be sometime next week. See you then.

© Copyright 2015 Howard J. Rudd all rights reserved.

One thought on “VBA Monte Carlo risk analysis spreadsheet with correlation using the Iman-Conover method. Part 4

  • 22 Feb 2016 at 08:20
    Permalink

    Howard, this is great stuff. Theory, programming, and practical application, and very clearly explained. I really appreciate your generosity.

Comments are closed.