frequency distribution in VBA: how to?

[choub]

hello!

I am running a monte carlo simulation on a financial product.
I generate 100 simulations of a product with a 1 year maturity and with monthly observations. I need to build frequency distributions and charts for 2 variables

let's take an exemple of how it works:
I generate 1 year of daily log returns of a stock
every month there is a "fixing" where I check if the stock value is above or below 2 levels (let's say 90 and 110).
so after 1 year, I have 12 observations

on each observation:
if stock < 90: I return 0
if 90 < stock < 110: I return 1
if stock > 110 I return 2

then, I count all the occurences of 0, 1 and 2 over the 100 simulations
if stock > 110 on any observation: I don't continue the calculation for this simulation and proceed to the next one.
I only count 0 when it occurs on the last observation.

Now what I'd need is to generate a distribution frequency and a chart for this simulation. I need to do that for at least 2 out of the 3 variables: when the result is either 1 or 2

The X-axis would be the observations dates (in this case, the X axis would range between 1 to 12).

does anybody know how I can do that in vba please?
I don't really know where to start from...

thansk for your attention and have a good day/week end!
A

 

[choub]

I think what I mean is that, as for a frequency distribution, I would like to get:

column 1: my fixing dates (from 1 to 12 in this exmaple but there can be more than that depending on the maturity of the product)
column 2: frequencies fo variable 1
column 3: frequencies for variable 2