Monte Carlo Simulation Vba Code: Excel help response (31 Jul 2007)

Upon purchasing the template, you will receive the password to unlock cells, sheets, and the workbook to be able to make modifications to formats, and add new sheets etc.

Since much of the functionality depends on the original workbook structure, modifications made are at the sole risk of the user.

The VBA code password is not released and the code has been protected to ensure the robustness of operation and for intellectual property purposes.

The monte carlo analysis VBA code is no secret and is as follows.

VBA Code:

Sub Charting()
Application.ScreenUpdating = False
Dim obs As Variant
Dim counta As Variant
Dim n As Variant
Dim mean As Variant
Dim sd As Variant
Dim x As Variant
Dim MaxY As Single, z As Single, f_of_z As Single
Dim Proportion As Single, Area As Single, Prob As Single, y As Single
Dim i As Long, count As Long

    Sheets("CoVar").Select
    obs = Range("G4").Value
    Sheets("Results").Select

    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SeriesCollection(1).Select
    ActiveChart.SeriesCollection(1).XValues = "=Results!R25C3:R" & obs + 24 & "C3"
    ActiveChart.SeriesCollection(1).Values = "=Results!R25C4:R" & obs + 24 & "C4"
    ActiveSheet.ChartObjects("Chart 2").Activate
    ActiveChart.SeriesCollection(1).Select
    ActiveChart.SeriesCollection(1).XValues = "=Results!R25C3:R" & obs + 24 & "C3"
    ActiveChart.SeriesCollection(1).Values = "=Results!R25C8:R" & obs + 24 & "C8"
          Randomize
          n = Range("W7").Value
          mean = Range("Y7").Value
          sd = Range("Y8").Value
          Range("Y10:Y60").Select
          counta = 0
          For Each cell In Selection
          counta = counta + 1
    Application.StatusBar = "Step 3 of 3: Performing Monte Carlo Simulation - " & Left(counta - 2, 2) & "% complete."
            x = (ActiveCell.Offset(0, -2).Value)
            count = 0
            MaxY = 1 / Sqr(2 * Application.Pi())
            For i = 1 To n
                      z = Rnd * (x - mean) / sd
                      y = Rnd * MaxY
                      f_of_z = 1 / Sqr(2 * Application.Pi()) * Exp(-z ^ 2 / 2)
                      If (y < f_of_z) Then count = count + 1
            Next i
            Proportion = count / n
            Area = MaxY * (x - mean) / sd
            Prob = Proportion * Area
            If (Abs(Prob) > 0.5) Then Prob = 0.5
            ActiveCell.Formula = 0.5 - Abs(Prob)
            ActiveCell.Offset(1, 0).Select
          Next


          Randomize
          n = Range("W7").Value
          mean = Range("Z7").Value
          sd = Range("Z8").Value
          Range("Z10:Z60").Select
          For Each cell In Selection
        counta = counta + 1
    Application.StatusBar = "Step 3 of 3: Performing Monte Carlo Simulation - " & Left(counta - 2, 3) & "% complete."
            x = (ActiveCell.Offset(0, -3).Value)
            count = 0
            MaxY = 1 / Sqr(2 * Application.Pi())
            For i = 1 To n
                      z = Rnd * (x - mean) / sd
                      y = Rnd * MaxY
                      f_of_z = 1 / Sqr(2 * Application.Pi()) * Exp(-z ^ 2 / 2)
                      If (y < f_of_z) Then count = count + 1
            Next i
            Proportion = count / n
            Area = MaxY * (x - mean) / sd
            Prob = Proportion * Area
            If (Abs(Prob) > 0.5) Then Prob = 0.5
            ActiveCell.Formula = 0.5 - Abs(Prob)
            ActiveCell.Offset(1, 0).Select
          Next
End Sub

Have fun :-P

Monte Carlo simulation VBA code

Excel Help for Monte Carlo Simulation Vba Code in Excel Portfolio Optimization Template

Monte Carlo Simulation Vba Code

Excel templates and solutions matched for Monte Carlo simulation VBA code: