StochasticRoutine

' Runs the stochastic analisys for selected countries
' Result Row 489
Sub StochasticAnalisysCountries()

    Application.Calculation = xlSemiautomatic
    'Application.ScreenUpdating = False

    Range("OilPriceSelect") = "Stochastic Price Live"
    Range("GasPriceSelect") = "Stochastic Price Live"
    
    Dim maxPrice, minPrice, tot, err As Double
    Dim period, iteration, i, j, col As Long
    Dim useMinMax As Boolean
   
    useMinMax = minMaxIsActive()
   
    col = Range("H7").Column

    iteration = Range("Iterations_result").Value
    period = Range("pricePeriods").Value
    maxPrice = Range("maxPrice").Value
    minPrice = Range("minPrice").Value
    stdDev = Range("priceStdDev").Value ' M85
    stConstant = Range("stockConstant") ' M83
    autoRegFactor = Range("autoregresionFactor") ' M84
    
    
    ReDim TP(iteration - 1) As Double
    ReDim MatFreq(39, period - 1) As Long
    ReDim MatPrice(39, period - 1) As Double
    ReDim MatPer(21, period - 1) As Double
    ReDim vals(period - 1) As Double
    ReDim Averages(period - 1) As Double
    ReDim FixedError(iteration - 1) As Double
    ReDim BigMat(iteration - 1, period)
    
    'Sheets("stochasticOutput").Select
    'Application.Goto Reference:="Debug_1"
    'Selection.Copy
    'Application.Goto Reference:="Debug_2"
    'ActiveSheet.Paste
    'Sheets("Result").Select
    
    If Application.Names("mode_chosen").RefersToRange.Value <> 2 Then
        Application.Names("mode_chosen").RefersToRange.Value = 2
    End If
   
    For j = 0 To period - 1
        For i = 0 To iteration - 1
            '=SI(H7=1,$M$89,MAX($M$87,MIN($M$86,EXP($M$83+$M$84*LN(G95)+DISTR.NORM.INV(ALEATORIO(),0,1)*$M$85))))
            If Sheets("Prices").Cells(7, col + j).Value = 1 Then
                tot = Range("initialPrice").Value
            Else
                ' EXP($M$83+$M$84*LN(G95)+ DISTR.NORM.INV(RANDOM(),0,1)*$M$85)
                If Sheets("Prices").CheckBoxes("checkBoxRepeatError").Value = 1 Then
                    If FixedError(i) = 0 Then
                        FixedError(i) = WorksheetFunction.NormInv(Rnd, 0, 1) * stdDev
                    End If
                   
                    err = FixedError(i)
                Else
                    err = WorksheetFunction.NormInv(Rnd, 0, 1) * stdDev
                End If
               
                tot = Exp(stConstant + autoRegFactor * Log(vals(j - 1)) + err)
 
                If useMinMax Then
                    ' compare with max price and get the MIN
                    tot = getMin(tot, maxPrice)
                    ' compare with min price and get the MAX
                    tot = getMax(tot, minPrice)
                End If
            End If
           
            BigMat(i, j) = Round(tot, 2)
        Next
        vals(j) = BigMat(0, j)
    
    Next j
    ' Iterates the prices trough Excel and creates new Matrixes
    Call CopyBigMatrixValuesCountries(BigMat)
    
    Range("OilPriceSelect") = "Constant real"
    Range("GasPriceSelect") = "Constant real"
    
    If Application.Names("mode_chosen").RefersToRange.Value <> 2 Then
        Application.Names("mode_chosen").RefersToRange.Value = 2
    End If
    
    Range("Iterations_result").Select
    
    'Application.ScreenUpdating = True
    
    'Call Stochastic_Modul_cesar
    
    
End Sub

' Iterates the prices trough Excel and creates new Matrixes
' creating statistical data
Sub CopyBigMatrixValuesCountries(ByRef mat As Variant)
    Dim rows, cols, colStart, rowStart, colSize As Long
    Dim rangeAddress As String
    Dim iteration_result As Integer
    
    iteration_result = CInt(Range("Iterations_result").Value)
    
    rows = UBound(mat, 1)
    cols = UBound(mat, 2)
    ' Postion
    rowStart = Range("resultStochasticPosStart").row
    colStart = Range("resultStochasticPosStart").Column
    
    ' Create vectors that hold information
    colSize = getRegimeSize() - 1
    
    ReDim preTaxProject(iteration_result - 1, colSize) As Double
    ReDim preTaxIRR(iteration_result - 1, colSize) As Double
    ReDim postTaxFinance(iteration_result - 1, colSize) As Double
    ReDim govRevenue(iteration_result - 1, colSize) As Double
    ReDim postTaxFinanceInv(iteration_result - 1, colSize) As Double
    ReDim AERT_NPV0(iteration_result - 1, colSize) As Double
    ReDim AERT_NPV10(iteration_result - 1, colSize) As Double
    
    
    'ReDim debugMat((rows + 1) * 8 + 8, 100) As Double
    
    rangeAddress = Range(Cells(rowStart, colStart), Cells(rowStart, colStart + cols)).Address
    ' row holds the values of the line
    ReDim row(cols) As Double
    Dim rowStochastic, colStochastic As Long
    
    'Debug.Print rows
    For i = 0 To iteration_result - 1
        For j = 0 To cols
            row(j) = mat(i, j)
        Next j
        ' Important
        Sheets("Result").Range(rangeAddress) = row
        
        With Sheets("Result")
            rowStochastic = Range("resultsStochasticPosStart").row
            colStochastic = Range("resultsStochasticPosStart").Column
            ' Fill matrixes
            For col2 = 0 To colSize
                preTaxProject(i, col2) = .Cells(rowStochastic, colStochastic + col2).Value
                'debugMat((i * 8) + 1, col2) = preTaxProject(i, col2)
                
                preTaxIRR(i, col2) = .Cells(rowStochastic + 1, colStochastic + col2).Value
                'debugMat((i * 8) + 2, col2) = preTaxIRR(i, col2)
                
                postTaxFinance(i, col2) = .Cells(rowStochastic + 2, colStochastic + col2).Value
                'debugMat((i * 8) + 3, col2) = postTaxFinance(i, col2)
                
                govRevenue(i, col2) = .Cells(rowStochastic + 3, colStochastic + col2).Value
                'debugMat((i * 8) + 4, col2) = govRevenue(i, col2)
                
                postTaxFinanceInv(i, col2) = .Cells(rowStochastic + 4, colStochastic + col2).Value
                'debugMat((i * 8) + 5, col2) = postTaxFinanceInv(i, col2)
                
                AERT_NPV0(i, col2) = .Cells(rowStochastic + 5, colStochastic + col2).Value
                'debugMat((i * 8) + 6, col2) = AERT_NPV0(i, col2)
                
                AERT_NPV10(i, col2) = .Cells(rowStochastic + 6, colStochastic + col2).Value
                'debugMat((i * 8) + 7, col2) = AERT_NPV10(i, col2)
            Next col2
        End With
    Next i
    
    
    ' Standard deviation
    'dump debugMat res
    'debugAdrs1 = Cells(1, 1).Address
    'debugAdrs2 = Cells(1 + UBound(debugMat, 1), 2 + UBound(debugMat, 2)).Address
    
    'Sheets("debug").Range(debugAdrs1, debugAdrs2) = debugMat
    Dim curRow As Integer
    Dim iter As Integer
    iter = iteration_result - 1
    
    Sheets("stochasticOutput").Range("A1:CX6000").ClearContents
    
    With Sheets("stochasticOutput")
        .Range("A1") = "Pre tax project net cashflow NPV10"
        curRow = 2
        .Range(Cells(curRow, 1).Address, Cells(curRow + iter, colSize + 1).Address) = preTaxProject
        
        .Range(Cells(curRow + iter + 1, 1).Address) = "Pre tax IRR"
        curRow = curRow + iter + 2
        .Range(Cells(curRow, 1).Address, Cells(curRow + iter, colSize + 1).Address) = preTaxIRR
        
        .Range(Cells(curRow + iter + 1, 1).Address) = "Post tax pre finance IRR"
        curRow = curRow + iter + 2
        .Range(Cells(curRow, 1).Address, Cells(curRow + iter, colSize + 1).Address) = postTaxFinance
        
        .Range(Cells(curRow + iter + 1, 1).Address) = "Goverment revenues NPV10"
        curRow = curRow + iter + 2
        .Range(Cells(curRow, 1).Address, Cells(curRow + iter, colSize + 1).Address) = govRevenue
        
        .Range(Cells(curRow + iter + 1, 1).Address) = "Post Tax Finance investors NPV10"
        curRow = curRow + iter + 2
        .Range(Cells(curRow, 1).Address, Cells(curRow + iter, colSize + 1).Address) = postTaxFinanceInv
        
        .Range(Cells(curRow + iter + 1, 1).Address) = "AERT NPV0"
        curRow = curRow + iter + 2
        .Range(Cells(curRow, 1).Address, Cells(curRow + iter, colSize + 1).Address) = AERT_NPV0
        
        .Range(Cells(curRow + iter + 1, 1).Address) = "AERT NPV10"
        curRow = curRow + iter + 2
        .Range(Cells(curRow, 1).Address, Cells(curRow + iter, colSize + 1).Address) = AERT_NPV10
        
        .Range(Cells(curRow + iter + 1, 1).Address) = "Prices"
        curRow = curRow + iter + 2
        .Range(Cells(curRow, 1).Address, Cells(curRow + iter, colSize + 1).Address) = mat
    End With
    
    'Call CalculateFullPack(preTaxProject, "stddev01")
    'Call CalculateFullPack(preTaxIRR, "stddev02")
    'Call CalculateFullPack(postTaxFinance, "stddev03")
    'Call CalculateFullPack(AERT_NPV0, "stddev04")
    'Call CalculateStdPack(AERT_NPV10, "stddev05")
    'Call CalculateFullPack(AERT_NPV10, "stddev05")
    'Call CalculateFullPack(govRevenue, "stddev06")
    'Call CalculateFullPack(postTaxFinanceInv, "stddev07")
    
End Sub


' Iterates matrix to calculate standard deviation, mean and coefficient  of variation
' and positions on the result
Sub CalculateStdPack(ByRef mat As Variant, pos As String)
    Dim rows, cols, colPos, rowPos As Integer
    
    rows = UBound(mat, 1)
    cols = UBound(mat, 2)
    rowPos = Range(pos).row
    colPos = Range(pos).Column
    
    ReDim res(rows) As Double
    
    'Application.DisplayStatusBar = False
    Application.Calculation = xlCalculationManual
    'Application.EnableEvents = False

    For j = 0 To cols
        For i = 0 To rows
            res(i) = mat(i, j)
        Next i
        
        Sheets("Result").Cells(rowPos, colPos + j) = WorksheetFunction.StDev(res)
        Sheets("Result").Cells(rowPos + 1, colPos + j) = WorksheetFunction.Average(res)
        On Error Resume Next
        Sheets("Result").Cells(rowPos + 2, colPos + j) = WorksheetFunction.Skew(res)
        
    Next j
    
    'Application.DisplayStatusBar = True
    Application.Calculation = xlCalculationSemiautomatic
    'Application.EnableEvents = True
End Sub

' Iterates matrix to calculate standard deviation, mean and coefficient of variation
' as well as Expected Negative NPV Check NPV Proportion negative NPV
' and positions on the result
Sub CalculateFullPack(ByRef mat As Variant, pos As String)
    Dim rows, cols, colPos, rowPos As Integer
    
    rows = UBound(mat, 1)
    cols = UBound(mat, 2)
    rowPos = Range(pos).row
    colPos = Range(pos).Column
    
    ReDim res(rows) As Double
    Dim nProp As Double
    
    'Application.DisplayStatusBar = False
    Application.Calculation = xlCalculationManual
    'Application.EnableEvents = False
    
   
    For j = 0 To cols
        For i = 0 To rows
            res(i) = mat(i, j)
        Next i
        nProp = proportionNegativeNPV(res)
        
        Sheets("Result").Cells(rowPos, colPos + j) = WorksheetFunction.StDev(res)
        Sheets("Result").Cells(rowPos + 1, colPos + j) = WorksheetFunction.Average(res)
        On Error Resume Next
        Sheets("Result").Cells(rowPos + 2, colPos + j) = WorksheetFunction.Skew(res)
        ' First to calculate expected positive and negative

        Sheets("Result").Cells(rowPos + 6, colPos + j) = nProp
        
        Sheets("Result").Cells(rowPos + 3, colPos + j) = expectedPositive(res) * (1 - nProp)
        Sheets("Result").Cells(rowPos + 4, colPos + j) = expectedNegative(res) * nProp
        'Sheets("Result").Cells(rowPos + 5, colPos + j) = checkNPV(res)
    Next j
    
    'Application.DisplayStatusBar = True
    Application.Calculation = xlCalculationSemiautomatic
    'Application.EnableEvents = True
End Sub

' Calculates the expected positive value of a matrix
Function expectedPositive(mat) As Double
    Dim c As Integer
    Dim sum As Double
    
    For i = 0 To UBound(mat)
        If mat(i) > 0 Then
            sum = sum + mat(i)
            c = c + 1
        End If
    Next i
    
    If c > 0 Then
        expectedPositive = sum / c
    Else
        expectedPositive = 0
    End If
End Function

Sub ttt()
    Dim arr(13) As Double
    
    arr(0) = 221
    arr(1) = 516
    arr(2) = 389
    arr(3) = 292
    arr(4) = 584
    arr(5) = 504
    arr(6) = 322
    arr(7) = 880
    arr(8) = 685
    arr(9) = 341
    arr(10) = 776
    arr(11) = 142
    arr(12) = 1107
    arr(13) = 100
        
    Debug.Print WorksheetFunction.Average(arr)
    Debug.Print expectedPositive(arr)
    Debug.Print expectedNegative(arr)
    Debug.Print proportionNegativeNPV(arr)
    
End Sub



' Calculates the expected negative value of a matrix
Function expectedNegative(mat) As Double
    Dim c As Integer
    Dim sum As Double
    
    For i = 0 To UBound(mat)
        If mat(i) < 0 Then
            sum = sum + mat(i)
            c = c + 1
        End If
    Next i
    
    If c > 0 Then
        expectedNegative = sum / c
    Else
        expectedNegative = 0
    End If
End Function


' Percentage of positive values
Function proportionNegativeNPV(mat) As Double
    Dim c As Double

    For i = 0 To UBound(mat)
        If mat(i) < 0 Then
            c = c + 1
        End If
    Next i
    
    proportionNegativeNPV = c / (UBound(mat) + 1)
End Function


Sub CopyTest()
    Dim arr(1, 1) As Integer
    arr(0, 0) = 1
    arr(0, 1) = 2
    arr(1, 0) = -3
    arr(1, 1) = 4
    
    Dim vals(4) As Double
    vals(0) = 2
    vals(1) = -2
    vals(2) = -3
    vals(3) = -2
    'vals = Range("E501:I501")
    'Debug.Print expectedPositive(vals)
    'Debug.Print expectedNegative(vals)
    Debug.Print UBound(arr, 2)
    
    Dim arr2 As Variant
    'Sheets("debug").Range("b1:c2") = arr
    arr2 = Sheets("debug").Range("e1:f2").Value
    
    Debug.Print UBound(arr2, 1), UBound(arr2, 2)
    
    'Debug.Print arr2(1, 1)
    'Debug.Print arr2(1, 2)
    'Debug.Print arr2(2, 1)
    'Debug.Print arr2(2, 2)
    'Debug.Print WorksheetFunction.StDev(vals)
    'Debug.Print UBound(vals, 1)
    'Debug.Print vals(1)
    'Dim size As Integer
    Sheets("debug").Range("h1:i2") = arr2
    
    arr2 = Sheets("debug").Range("A1:C3").Value
    Sheets("debug").Range("B5:D7") = arr2
    
    'size = (UBound(arr, 1) + 1) * (UBound(arr, 2) + 1)
    'ReDim arr2(size - 1)
    'Debug.Print getRegimeSize()
    
    
    'Sheets("Result").Range("E489:G489") = arr
    
    'Debug.Print Range(Cells(2, 3), Cells(2, 5)).Address
    
End Sub

Function getRegimeSize() As Long
    Dim col, row As Long
    col = Range("selectRegimesStartPos").Column
    row = Range("selectRegimesStartPos").row
    
    Do While col < 100
        If Sheets("Result").Cells(row, col).Value = 0 Then
            getRegimeSize = col - Range("selectRegimesStartPos").Column
            col = 100
        End If
        col = col + 1
    Loop
End Function

Function getMin(a, b) As Long
    If a < b Then
        getMin = a
    Else
        getMin = b
    End If
End Function
 
 
Function getMax(a, b) As Long
    If a > b Then
        getMax = a
    Else
        getMax = b
    End If
End Function

' test created functions
Sub testPropNeg()
    Dim arr(13) As Double

    arr(0) = 221
    arr(1) = 516
    arr(2) = 389
    arr(3) = 292
    arr(4) = 584
    arr(5) = 504
    arr(6) = 322
    arr(7) = 880
    arr(8) = 685
    arr(9) = 341
    arr(10) = 776
    arr(11) = 142
    arr(12) = 1107
    arr(13) = -30
        
    Debug.Print proportionNegativeNPV(arr) ' Val should be 0.5
    Debug.Print CInt("2.2")
End Sub

Sub Stochastic_Modul_cesar()
'
' Macro10 Macro
'
  Set rng = Range("HideFlags_1")
'
    Sheets("debug").Select
    Application.Goto Reference:="Stochastic_result_source_3"

    
    Selection.AutoFill Destination:=Range("DD31:EG10031"), Type:=xlFillValues
    Range("DD31:EG10031").Select


    'Pre tax project net cashflow NPV10 = Stochastic_result_1
    
        Sheets("debug").Select
    
        Range("Stochastic_Selector").Value = "Pre tax project net cashflow NPV10"
    
        Application.Goto Reference:="Stochastic_result_source_2"
        Selection.Copy

        Sheets("Result").Select
    
        Range("Stochastic_result_1").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
    
    'Pre tax IRR = Stochastic_result_2
    
        Sheets("debug").Select
        
        Range("Stochastic_Selector").Value = "Pre tax IRR"
    
        Application.Goto Reference:="Stochastic_result_source_1"
        Selection.Copy

        Sheets("Result").Select
    
        Range("Stochastic_result_2").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        
        
    'Post tax, pre finance  IRR = Stochastic_result_3
    
        Sheets("debug").Select
    
        Range("Stochastic_Selector").Value = "Post tax, pre finance  IRR"
        
        Application.Goto Reference:="Stochastic_result_source_1"
        Selection.Copy

        Sheets("Result").Select
    
        Range("Stochastic_result_3").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
            
    'AETR NPV0 = Stochastic_result_4
    
        Sheets("debug").Select
    
        Range("Stochastic_Selector").Value = "Government RevenuesNPV10"
        
        Application.Goto Reference:="Stochastic_result_source_1"
        Selection.Copy

        Sheets("Result").Select
    
        Range("Stochastic_result_4").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        
     'AETR NPV10 = Stochastic_result_5
     
        Sheets("debug").Select
     
        Range("Stochastic_Selector").Value = "Post tax, Pre finance investor NPV10"
     
        Application.Goto Reference:="Stochastic_result_source_1"
        Selection.Copy

        Sheets("Result").Select
    
        Range("Stochastic_result_5").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
            
     'Government Revenues NPV10 = Stochastic_result_6
     
        Sheets("debug").Select
     
        Range("Stochastic_Selector").Value = "AETR NPV0"
     
        Application.Goto Reference:="Stochastic_result_source_2"
        Selection.Copy

        Sheets("Result").Select
    
        Range("Stochastic_result_6").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False

     'Post tax, Pre finance investor NPV10 = Stochastic_result_7
     
        Sheets("debug").Select
     
        Range("Stochastic_Selector").Value = "AETR NPV10"
     
        Application.Goto Reference:="Stochastic_result_source_2"
        Selection.Copy

        Sheets("Result").Select
    
        Range("Stochastic_result_7").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
            
    '**************************************
            
    Application.Goto Reference:="Stochastic_result_source_4"
    Selection.ClearContents
    
    Columns("B:CZ").Select
    Selection.ClearContents
    
    Sheets("Iterations_result").Select


End Sub


'Pre tax project net cashflow NPV10 = Stochastic_result_1
'Pre tax IRR = Stochastic_result_2
'Post tax, pre finance  IRR = Stochastic_result_3
'AETR NPV0 = Stochastic_result_4
'AETR NPV10 = Stochastic_result_5
'Government Revenues NPV10 = Stochastic_result_6
'Post tax, Pre finance investor NPV10 = Stochastic_result_7