Referencing a 3d array using Index

[GrantLocke35]

hi guys, I would be extremely grateful for any support in solving this problem. In short I have created a cube of data, it is a series (Sx300, Px5,Tx50). S = simulations, p=portfolio and t is time horizon. In short it is a series of 300 stochastic simulations for each portfolio for each year. I need to calculate a confidence interval for each P for each T. This means I need to reference a column (p) and calculate the 90th percentile on each of the time horizons. I am trying to use an Index function but cannot see how it can reference a 3rd dimension - seems to only work in two!

Sub Forecasting_System()

Dim OUT1() As Variant
Dim OUT2() As Variant
Dim OUT3() As Variant
Dim OUT4() As Variant
Dim OUT5() As Variant
Dim Time_H As Integer
Dim Simulations As Integer
Dim OUT_Param() As Variant
Dim Annual_Contribution As Variant
Dim Lump_Sum As Variant
Dim Output_Table() As Double
Dim Percentile() As Double

Simulations = Range("Simulations")
ReDim Output_Table(1 To Simulations, 1 To 5, 1 To 50)
ReDim Percentile(1 To 5, 1 To 3, 1 To 50)

ReDim OUT_Param(1 To 5, 1 To 2)
 OUT_Param = Range("Out_Param")
 For Z = 1 To 50 ' Time horizon
  x = 1
  y = 1
  For y = 1 To 5 ' Number of portfolios
   For x = 1 To Simulations ' Number of simulations
   Output_Table(x, y, Z) = 1
   Next x
 Next y
Next Z

 
x = 1
y = 1
Z = 1
 

For Z = 1 To 50
 
 Time_H = Z

 Lump_Sum = Range("Lump_Sum")
 Annual_Contribution = Range("Mnthly_Contribution") * 12


 For x = 1 To Simulations
  a = 1
  For a = 1 To Time_H
  Output_Table(x, 1, Z) = (Output_Table(x, 1, Z) * (1 + Application.WorksheetFunction.NormInv(Rnd(), OUT_Param(2, 1), OUT_Param(2, 2))))
  Output_Table(x, 2, Z) = (Output_Table(x, 2, Z) * (1 + Application.WorksheetFunction.NormInv(Rnd(), OUT_Param(2, 1), OUT_Param(2, 2))))
  Output_Table(x, 3, Z) = (Output_Table(x, 3, Z) * (1 + Application.WorksheetFunction.NormInv(Rnd(), OUT_Param(3, 1), OUT_Param(3, 2))))
  Output_Table(x, 4, Z) = (Output_Table(x, 4, Z) * (1 + Application.WorksheetFunction.NormInv(Rnd(), OUT_Param(4, 1), OUT_Param(4, 2))))
  Output_Table(x, 5, Z) = (Output_Table(x, 5, Z) * (1 + Application.WorksheetFunction.NormInv(Rnd(), OUT_Param(5, 1), OUT_Param(5, 2))))
  Next a
 Next x

Next Z


x = 1
Z = 1

For Z = 1 To 50
 x=1
 For x=1 to 5
 Percentile(Z, 1, 1) = Application.WorksheetFunction.Percentile(Application.WorksheetFunction.Index(Output_Table,0,Output_table(1,x,z), 0.9) ' This is where my problem is here. You can see that z is a reference to the depth of the cube, i.e. time horizon 1 to 50. 
Next Z

End Sub

 

[mikerickson]

You can't use INDEX to address a 3-D array.

your array is 300x5x50 What I would do is set up (on one sheet) 50 300x5 cell ranges in a regular pattern.
One could either define a discontinuous range A1:E300, G1:K300, ...
One could use the third argument of INDEX.

But since you a are using VBA, once the data is loaded into a VBA 3-D array, addressing that array in the normal manner would be easiest.

 

[GrantLocke35]

Thanks Mike, I am trying to keep it out of a sheet just because I find the referencing more difficult (just the way my brain works. I think that another way to solve this would be to change the 3d array to 2d. Then you loop to create the simulations and analyse the simulations using the second loop and store the data in another 2 d array. Also, I didn't quite understand your last sentence, are you saying there is another way to reference a 3d array using VBA?

Thanks

Grant

 

[pgc01]

... are you saying there is another way to reference a 3d array using VBA? ...

Hi Grant

Well, you are using Index() and that's not (at all) the usual way to address an element of an array, Index() is a worksheet function that you call from vba.

The way to adress elements in an array is directly, like

Dim dTest(1 To 5, 1 To 3, 1 To 6, 1 To 7) As Double

dTest(1, 2, 3, 4) = 3.45

MsgBox dTest(1, 2, 3, 4)

I don't understand what you need

I understand that you have an array (S, P, T)

I don't know if you need

- for a specific pair (S, P) to get all the values for T (an array 1D)
or
- for a specific value of S to get all the corresponding values (P,T) (an array 2D)

Anyway, one simple solution is just to loop through the corresponding dimensions and get the values you need.

 

[GrantLocke35]

Hi PG. Thanks for the reply. In short I need to reference a subarray within an existing array. If the major array is 3d (300 x5x50) I need to reference (r x 1 x 1). In other words I need to reference column 1 in dimension 1, and loop the column across 50 dimensions. I want to actually calculate the 90th percentile for column 1.

Column 1 contains a range of numeric values and I need to reference it to calculate the percentile as I cannot loop through every element of the array to calculate a percentile as you need the range.

The only solution I have found is to create a 2 dimensional subarray (temp) and then loop through the major array to populate the 2d array and then use index to reference the column and perform the calcs.

Thoughts?

 

[pgc01]

Still not sure about what you need, but it seem it's the second option in my post.

For the array (S, P, T) you want for a value of S to get the corresponding 2D array (P, T)

If that's the case, I see 2 options:

- one equivalent to what you're using, for a value of S loop and extract a temp 2D array (P, T) and use it in your calculation. You can do it directly in your code or write some function that does the extraction.

- another one is to use a jagged array.

Instead of a 3D array (S, P, T) you'd build an array 1Dx2D, you'd build and array 1D (S) were each element S is itself and 2D array.

This way you'd reference the 2D array you need directly given the simulation.

This would mean means changing the structure of the data and the way load the values.

 

[GrantLocke35]

Thank you Mike and PGC - you guys have been great and I think I am there now. I wish there was an easy way to basically use a VBA function to reference any range in a multi-dimensional array - there must so many of us that could benefit from this.

Thanks once again and I look forward to throwing some more problems this way

 

[pgc01]

I'm glad it helped. Thanks for the feedback.