vendredi 7 avril 2017

Using random number draws to repopulate an array for analysis

I'm trying to create a subroutine to perform an analysis.

There are two declared ranges. I want to randomly draw three of the values from the results range, perform returns analysis, and pass this result to one cell of the results range. I then want to repeat this until the second, larger range is populated with this random draw analysis.This is the code I have so far:

Sub boostrap()

Dim returns As Variant
Dim Results As Variant
Dim n As Integer
Dim m As Integer
Dim ret As Variant

Dim firstrow As Variant, lastrow As Variant
Dim annualised_return As Variant
Dim i As Long, j As Long


returns = Range("returns")
Results = Range("Results")
n = Range("returns").count
m = Range("Results").count

lastrow = Range("returns").End(xlDown).Row
firstrow = Range("returns").Row


For j = 1 To m

        ReDim draw(1 To 3) 'to create a draw reference number from the second range
            draw(1) = Application.WorksheetFunction.RandBetween(firstrow, lastrow)
            draw(2) = Application.WorksheetFunction.RandBetween(firstrow, lastrow)
            draw(3) = Application.WorksheetFunction.RandBetween(firstrow, lastrow)

    ReDim ret(1 To 3)
        ret(1) = returns(draw(1).Value)
        ret(2) = returns(draw(2).Value)
        ret(3) = returns(draw(3).Value)




 Results(j) = annualised_return = (((1 + ret(1)) * (1 + ret(2)) * (1 + ret(3))) ^ (1 / 3) - 1)
Next j

End Sub

Stepping through the sub, the error seems to stem from when i try and pass the numbers from the draw as a reference to the results range. i.e if the draw produces #22, then i want to select the value in row 22 to be placed in the array of results, which i will then analyse (along with the other two draws) to populate each cell of the results range.

Can anyone provide assistance on how to remedy this? Muchos thanks




Aucun commentaire:

Enregistrer un commentaire