I am trying to automate the analysis of a subset of a simulation say to examine the input values that produced 85-100 percentiles of the an output.

When I am examining the tails (or any other subset) of a simulation I use the SortedSimulationIndex together with the SimulationValue functions to extract the input value for any given output percentile. for example if I have input distrbutions in A1 and B1 and an output in C1 (=A1+B1) I can look at the value of A1 or B1 at the 85% percentile of C1 as follows:

D1=SortedSimulationIndex(C1,85%*simulationtrials()) to give the iteration producing the 85% of C1

then the value of A1 at that interation is:

E1=SimulationValue(A1,D1)

Repeat for other percentiles etc

I think thats all correct so far.

Normally I would then use these formulae for a percentile range say 85 to 100% calculate the median (say) and compare it to the overall simulation (perhaps normalising it with the standard deviation).

I was trying to automate this perhaps using the SequentialValue function eg SequentialValue(85%,100%) to step through the percentiles and use the SimulationMedian function on the SimulationValue cell rather than using excels median function on a spreadsheet range. I am not sure if this works properly as some of the other functions give questionable results, noticeably the minimum is often zero when it shouldn't be.

Should this approach work or are (as I am beginning to suspect) there too many moving targets ie the percentiles aren't settled until the simulation is completed and the subset can only be analysed after a simulation is complete and not during a simulation


The other quirk is it only works at all if screen updating in on, if screen updating is off then the values are zero.

I have an example spreadsheet if needed.

I was trying to create a VBA function to store the values of the SimulationValue cell and calculate the median, I haven't made much progress so far (I am not storing the values in the array correctly). But if the approach is futile anyway then I'd rather know now and not waste any more time.


Thanks


Andrew

Hi,

You're correct in noting that values don't settle until the simulation is complete. As a result of this minimums are often invalid (i.e. zero). You're also correct that it behaves differently with screen updates on or off.

That said, there is almost certainly a way to do what you want. The issue may be whether you can do it during a simulation or after the simulation.

Is this a design requirement? That is, are you planning to abort the simulation if intermediate values don't comport to a certain profile?


If you can wait until it completes, you can retrieve any value from the simulation as an individual value or as an array. I can help work through whatever model you're trying to construct.

If you can't wait, it is likely still possible, although it might require some more complex VBA, and you may have to use Excel functions rather than the simulation functions.

Alex
support@riskamp.com

Hi Alex

I am perfectly happy to wait until the simulation completes to extract the values. At the moment this just curiosity to see if it works. At the moment I use excels functions on a spreadsheet range of the extracted values. I had thought that an array within an excel userfunction (VBA) might be more elegant.


As I mentioned I am looking at writing a VBA function but was stuck on the array part (never used them in the past)

Here's what I have so far:


Function MedianArr(X, Trials) As Variant

'test function to calculate the median of X (X is a RiskAmp SimulationValue function or a distribution function - used in testing)


Dim i As Long
Dim Arr() As Variant
Dim wsf As Object
'sets wsf as Application.WorksheetFunction
Set wsf = Application.WorksheetFunction

ReDim Arr(1 To Trials)


For i = 1 To 10 '10 replacing trials for testing

Arr(i) = X

Debug.Print i, X, Arr(i)

Next i

MedianArr = wsf.Median(Arr)


End Function



The bit I am stuck on so far is that i to 10 is filled with the same value, not 10 different values. I am sure there are other problems to come but if I can't get past this then it's moot.

Maybe using a function is the wrong way to go anyway.


Regards


Andrew

Hi,

There's one thing to consider before using VBA for this. The way our add-in works, we only store values that we think you want. That's the "reference cell" concept we talk about in the help files. That may be why you're not getting the right values - if there's no reference cell, we may not be storing them.

To work around that, just make sure you have a cell in your spreadsheet with some statistics function, like SimulationMean, referring to the cell you want to work with. This is kind of annoying but it's simple enough. You can have as many of these as you need.

So let's assume you're modeling the values in cell C5. In the spreadsheet, put another cell with

=SimulationMean( C5 )

Then you can be sure it will be available in VBA.

To get the whole list as an array, you can use the function

=SimulationValuesArray( C5 )

That will return the whole set. From VBA, you have to use either the Application.ExecuteExcel4Macro syntax or the Application.Run syntax. The end result will look like this:

---

Sub TestValues()

' create an array first, but leave the size blank.

Dim v() As Variant

' get the values

v = Application.Run("SimulationValuesArray", Range("C5"))

' check the size of the array: should be the same as the
' number of trials from the last run

MsgBox "Size? " & UBound(vx)

' use some worksheet function: here, the mean. compare
' this with the mean reported in the worksheet.

MsgBox "Average? " & Application.WorksheetFunction.Average(vx)


End Sub


---

If you did want to get just one value, you can use the same syntax with the SimulationValue function; for example,


...

dim d as double
d = Application.Run("SimulationValue", Range("C5"), 1 )

...

Let me know if that works for you.

Cheers,

Alex
support@riskamp.com

Thanks I'll try this and let you know how I get on.

Regards, Andrew

Just to follow up on this, I managed to put together a VBA user function do everything I needed, the only problem is it's a bit slow. I don't know if that's because VBA functions are inherently slower or inefficient programming on my part.

Here is the code if anyone finds it useful - use at your own risk. If anyone can improve it then by all means do so and perhaps you could post the improvements.


Function CondMed(EvalCell, RefCell, Lower As Double, Upper As Double, Optional Alpha As Boolean)
'
'Conditional median analysis
'function to find the median of the evaluation cell values at given percentile range of the reference cell
'
'created 04 May 2009
'
'EvalCell is the evaluation cell
'RefCell is the reference cell
'Lower is the lower percentile in the range
'Upper is the upper percentile in the range
'Alpha is an option to show the "zscore" rather the median = (testfunc median- overall median)/ overall standard deviation

Dim Iteration
Dim i As Integer, j As Integer
Dim p As Double 'percentile
Dim Arr() As Variant
Dim Trials As Integer
Dim uLimit As Integer
Dim Interval As Double
Dim EvalMed
Dim OverMed
Dim OverSD
Dim wsf As Object

Set wsf = Application.WorksheetFunction

On Error GoTo FuncFail:

Trials = Application.Run("simulationtrials")
'exits function if trials are zero
If Trials = 0 Then
Exit Function
End If

uLimit = ((Upper - Lower) * Trials) + 1
Interval = (Upper - Lower) / (uLimit - 1)
'set sampling interval
p = Lower 'lower percentile

ReDim Arr(1 To uLimit)
For i = 1 To uLimit
'determines the iteration that returned the percentile value
Iteration = Application.Run("SortedSimulationIndex", RefCell, p * Trials)
'exits function if iteration is error or zero
If IsError(Iteration) = True Then
Exit Function
End If

If Iteration = 0 Then
Exit Function
End If
Arr(i) = Application.Run("SimulationValue", EvalCell, Iteration)

'percentile plus increment
p = Round(p + Interval, 4)
Next i

EvalMed = wsf.Median(Arr())
OverMed = Application.Run("Simulationmedian", EvalCell)
OverSD = Application.Run("SimulationStandardDeviation", EvalCell)

If IsError(OverSD) = True Or OverSD = 0 Then
Exit Function
End If

'values below 0.5 standard deviations are not shown - NOT IMPLEMENTED YET

'If Abs((EvalMed - OverMed) / OverSD) >= 0.5 Then
If IsMissing(Alpha) = True Or Alpha = 0 Then
CondMed = EvalMed
Exit Function
Else
CondMed = (EvalMed - OverMed) / OverSD
Exit Function
End If
'Else

'placeholder for code

'Exit Function

'End If


Exit Function
FuncFail:
CondMed = CVErr(xlErrValue)

End Function


Regards, Andrew