# frequency distribution in VBA: how to?

#### choub

##### New Member
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

### Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

#### choub

##### New Member
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

Replies
4
Views
318
Replies
2
Views
432
Replies
0
Views
82
Replies
8
Views
2K
Replies
5
Views
147

1,191,416
Messages
5,986,430
Members
440,029
Latest member
GabSSSH

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back