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