Excel VBA Simulation Basic Tutorial 102

Excel VBA Simulation Basic Tutorial 102

This page is the second part of the Excel VBA Simulation Basic Tutorial series. It provides Excel VBA tutorials on how to create statistic estimates that are used to analyze the data from a simulation. Many of the examples used are already available in Excel functions. Users can use these Excel functions as tools to check against the results that come from the examples. These examples require basic programming skills in VBA. Users are encouraged to read Simulation Based Tutorial 101 if they have problem understanding the programming concepts and terms used on this page.

This document contains information about the following topics.

Random Number and Randomize Statement

Standard Deviation and Mean

Skewness and Kurtosis

Percentile and Confidence Interval

Profitablity

Creating a Histogram

Random Number and Randomize Statement

To generate random number from 0 to 1 uniformly, one can use the Rand() function in Excel or the Rnd function in VBA. These two functions are the mother of all random numbers. You will need either one of these functions to generate random numbers from any probability distributions.

The following example generate 5 random numbers and then display them in a message box:

Sub rndNo()
    Dim str As String

    For i = 1 To 5
        str = str & CStr(Rnd) & vbCrLf
    Next i

    MsgBox str
End Sub

* CStr() function converts the random numbers into string.

So far so good. But when we close the file, reopen it, and run the sub routine again, the same 5 numbers come up!

The reason why this happens is that the random numbers were actually being generated from the same set of numbers (called seed). By placing the Randomize statement in the sub routine, the numbers will be generated from a new seed. (Randomize uses the return value from the Timer function as the new seed value.)

The new routine can be as followed:

Sub rndNo()
    Dim str As String

    Randomize
    For i = 1 To 5
        str = str & CStr(Rnd) & vbCrLf
    Next i

    MsgBox str
End Sub

Sometimes we might want to use the same seed over and over again by just changing the values of certain variables in our simulations to see how the change affects the outcomes. In such case, omit the Randomize statement in your sub routine.

For more information, refer to Excel VBA Help in your Excel program.

Return to Top of Page

Standard Deviation and Mean

Standard deviaiton and mean are the two mostly used statistic estimates of all times. Mean is the average. Standard deviation measures the 'spreadness' of the distribution.

The following are functions that compute mean and standard deviation. These functions are similar to other functions used in our examples; they take array as their arguments.

Function Mean(Arr() As Single)
     Dim Sum As Single
     Dim i As Integer

    Sum = 0
    For i = 1 To UBound(Arr)
        Sum = Sum + Arr(i)
    Next i

    Mean = Sum / UBound(Arr)
End Function

Function StdDev(Arr() As Single)
     Dim i As Integer
     Dim avg As Single, SumSq As Single

     avg = Mean(Arr)
     For i = 1 To UBound(Arr)
          SumSq = SumSq + (Arr(i) - avg) ^ 2
     Next i

     StdDev = Sqr(SumSq / (UBound(Arr) - 1))
End Function

The following sub routine reads the data in column one from row 1 to 10 (of Sheet1) into the array, calls both functions by passing the arguements to them, computes the mean (average) and the standard deviation, then returns the values in a message box.

Sub compute()
     Dim Arr(10) As Single
     Dim Average As Single
     Dim Std_Dev As Single

     For i = 1 To UBound(Arr)
          Arr(i) = Sheets("Sheet1").Cells(i, 1)
     Next i

     Average = Mean(Arr)
     Std_Dev = StdDev(Arr)
     MsgBox "Average:" & vbTab & Average & vbCrLf & "StdDev :" & vbTab & Std_Dev
End Sub

The figures below show the data and the result.

Similar example is also used in the Standard Deviation and Mean examples on the VBA section.

(These functions are similar to the AVERAGE() and the STDEV() functions provided by Excel.)

Return to Top of Page

Skewness and Kurtosis

Skewness measures the degree of asymmetry of a distribution. For example, the skewness of a normal distribution is 0 since a normal distribution is symmetric. Positive skewness indicates a distribution with an asymmetric tail extending toward more positive values, where as negative skewness extending toward more negative values.

Kurtosis measures the degree of peakedness or flatness of a distribution compared with normal distribution. Positive kurtosis indicates a relatively peaked distribution. Negative kurtosis indicates a relatively flat distribution.

Often, these two estimates along with mean and standard deviation are used to test to see if the simulated data from a distribution is sound (if the data represents the distribution).

The following sub routine, compute(), reads the following data in column one from row 1 to 10 (of the active sheet) into the array,

calls both functions by passing the arguements, computes the four moments (namely mean, standard deviation, skewness, and kurt) and returns the values in a message box.

Sub compute()
    Dim arr(10) As Single

    For i = 1 To 10
        arr(i) = Cells(i, 1)
    Next i

    MsgBox "Mean:" & vbTab & Format(Mean(arr), "0.0000") & vbCrLf & _
    "SD:" & vbTab & Format(Var(arr) ^ 0.5, "0.0000") & vbCrLf & _
    "Skew:" & vbTab & Format(Skew(arr), "0.0000") & vbCrLf & _
    "Kurt:" & vbTab & Format(Kurtosis(arr), "0.0000")
End Sub

Function Skew(arr() As Single)
    Dim i As Long, n As Long
    Dim avg As Single, sd As Single, SumTo3 As Single

    n = UBound(arr)
    avg = Mean(arr)
    sd = (Var(arr)) ^ 0.5

    SumTo3 = 0
    For i = 1 To n
        SumTo3 = SumTo3 + ((arr(i) - avg) / sd) ^ 3
    Next i

    Skew = SumTo3 * (n / ((n - 1) * (n - 2)))
End Function

Function Kurtosis(arr() As Single)
    Dim i As Long, n As Long
    Dim avg As Single, sd As Single, SumTo3 As Single

    n = UBound(arr)
    avg = Mean(arr)
    sd = (Var(arr)) ^ 0.5

    SumTo4 = 0
    For i = 1 To n
        SumTo4 = SumTo4 + ((arr(i) - avg) / sd) ^ 4
    Next i

    Kurtosis = SumTo4 * (n * (n + 1) / ((n - 1) * (n - 2) * (n - 3))) - (3 * (n - 1) ^ 2 / ((n - 2) * (n - 3)))
End Function

Function Mean(arr() As Single)
    Dim Sum As Single
    Dim i As Long, k As Long

    k = UBound(arr)
    Sum = 0
    For i = 1 To k
        Sum = Sum + arr(i)
    Next i

    Mean = Sum / k
End Function

Function Var(arr() As Single)
    Dim i As Long
    Dim avg As Single, SumSq As Single

    k = UBound(arr)
    avg = Mean(arr)
    For i = 1 To k
        SumSq = SumSq + (arr(i) - avg) ^ 2
    Next i

    Var = SumSq / (k - 1)
End Function

The figures below show the data and the result.

(These functions are similar to the SKEW() and the KURT() functions provided by Excel.)

Return to Top of Page

Percentile and Confidence Interval

Percentile returns the k-th percentile of values in a range. A confidence interval is the interval between two percentiles. For example: if a set of data has 20 numbers ranging from 2.5 to 50 with an increment of 2.5 (2.5, 5, ...., 50), the 80th percentile would be 40. This means that 80% of the elements from the set will be equal to or below than 40. If the alpha value is 10%, for a two tails test, the lower percentile should be set to 5% (alpha/2) and the upper percentile should be set to 95% (1 - alpha/2).

In order to get the percentile, the data needs to be sorted. In the sub routine (GetPercentile()) below, 10 random numbers between 1 to 50 are assigned to an array. The sub routine calls the percertile function (u_percentile()). The function calls the Sort sub routine to sort the array. The function gets the value from the array based on the percentile (40%), and returns the percentile value back to the sub routine.

Notice that Application.Max(Application.Min(Int(k * n), n), 1) in the percentile function makes sure that first, the array index is an integer and second, the maximum value and the minimum value for the array index will not excess the number of elements in the data set or below 1, respectively.

The data and the result are as followed:

The numbers in blue are below the 40% percentile. Nineteen (19), in this case, is the value that the function returns at 40% percentile.

Here is the complete program for the above example:

Sub GetPercentile()
    Dim arr(10) As Single

    For i = 1 To 10
        arr(i) = Int(Rnd * 50) + 1
        Cells(i, 1) = arr(i)
    Next i

    Cells(10, 2) = u_percentile(arr, 0.4)
End Sub

Function u_percentile(arr() As Single, k As Single)
    Dim i As Integer, n As Integer

    n = UBound(arr)
    Call Sort(arr)
    x = Application.Max(Application.Min(Int(k * n), n), 1)
    u_percentile = arr(x)
End Function

Sub Sort(ByRef arr() As Single)
    Dim Temp As Single
    Dim i As Long
    Dim j As Long

    For j = 2 To UBound(arr)
        Temp = arr(j)
        For i = j - 1 To 1 Step -1
            If (arr(i) <= Temp) Then GoTo 10
            arr(i + 1) = arr(i)
        Next i
        i = 0
10      arr(i + 1) = Temp

        If j Mod 100 = 0 Then
            Cells(26, 5) = j
        End If
    Next j
End Sub

Similar concept from this tutorial is used in many of our simulation examples.

(This function is similar to the PERCENTILE() and the QUARTILE() functions provided by Excel.)

Return to Top of Page

Profitablity

The previous percentile example shows how to get the value that corresponds to a specific percentile. In this example, we will show you on how to get the percentile with a given value.

We are going to start this tutorial by showing you a very simple simulation. However, simulation is not necessary to get the answer in this example because we are using very loss assumptions. The result can actually be computed in your head if your math is that good.

Assume your profit is distributed uniformly. From the past records, you know that your annual average profit flucturates between -$100,000 to $500,000. We want to know what is the probabilty that you will be making over $300,000 next year holding all other things constant. Interesting enough? Now watch this:

1 - (300,000-(-100,000))/(500,000-(-100,000)) = 1 - 0.666 = 0.333

The probabilty will be 33%.

Now, let's run the simulation and see what will happen.

Five simulations were ran, each with 1000 iterations. The result shows 5 probability values in a message box. Each result is closed to the mathematic computation of 33%.

Here is the sub routine that runs the simulation:

Sub GetProb()
    Dim high As Single, low As Single, profit As Single
    Dim counter As Integer
    Dim str As String

    high = 500000
    low = -100000
    profit = 300000

    srt = ""
    For j = 1 To 5
        counter = 0
        For i = 1 To 1000
            If profit <= Rnd * (high - low + 1) + low Then
                counter = counter + 1
            End If
        Next i
        str = str & counter / 1000 & vbCrLf
    Next j

    MsgBox str
End Sub

This example is also implemented in the Monte Carlo Simulation tutorial.

(This function is similar to the PERCENTRANK() function provided by Excel.)

Return to Top of Page

Creating a Histogram

A histogram from a simulation shows the graphical representation of the derived probability distribution.

The following sub procedure is an improved model for generating a histogram. The first parameter, M, is the number of bins (breaks) that you want to have for the histogrm. The second parameter is the array that contains that values for the histogram.

In order for this procedure to work properly, the array needs to be sorted for calling the histogram procedure. This way, the maximum and the minimum values can be derived and used for setting up the bin values. Please see the following examples for the implementation:

Normal Distribution Random Number Generator, Bootstrap - A Non-Parametric Approach,
and Monte Carlo Simulation.

Here are the codes that generate a histogram:

Sub Hist(M As Long, arr() As Single)
    Dim i As Long, j As Long
    Dim Length As Single
    ReDim breaks(M) As Single
    ReDim freq(M) As Single

    For i = 1 To M
        freq(i) = 0
    Next i

    Length = (arr(UBound(arr)) - arr(1)) / M

    For i = 1 To M
        breaks(i) = arr(1) + Length * i
    Next i

    For i = 1 To UBound(arr)
        If (arr(i) <= breaks(1)) Then freq(1) = freq(1) + 1
        If (arr(i) >= breaks(M - 1)) Then freq(M) = freq(M) + 1
        For j = 2 To M - 1
            If (arr(i) > breaks(j - 1) And arr(i) <= breaks(j)) Then freq(j) = freq(j) + 1
        Next j
    Next i

    For i = 1 To M
        Cells(i, 1) = breaks(i)
        Cells(i, 2) = freq(i)
    Next i
End Sub

The following is an example output from the procedure:

The class is the bins or the breaks. The frequency contains the number of simulated values for each of the classes.

Here is the histogram chart from this example:

Return to Top of Page