Monte Carlo Method

Jimeshia

New Member
Joined
Feb 25, 2018
Messages
12
Can anyone help? I am needing to write a code in monte carlo method to solve the integral from 0 to 1 for the function e^-xdx. I am struggling with this project. This code should start with an amount of 10,000 and increase by 5,000 until the difference between the answers is less than 0.001. I am VERY NEW to VBA and need help. Thanks.
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Re: Monte Carlo Method Help

This sounds like an assignment? What's the actual question being asked?

Have you written any code to get this started?
 
Upvote 0
Re: Monte Carlo Method Help

Yes, this is an assignment and no I have not. The only thing we have done is watched some videos and this is PROJECT 1. I have researched for weeks for this and just truly need help. I know the basic of vba but nothing about this.
 
Upvote 0
Re: Monte Carlo Method Help

  1. Design an algorithm and write a code to calculate the integral from 0 to 1 for the function e^-xdx
    by using random number generator and the region under the graph of f(x) inscribed in a rectangle [a, b] × [c, d] where c = 0 and d fmax. Notation: fmax is the maximum value of f(x) on interval [a,b].
  2. Use the fact that the ratio of randomly generated points below the graph of f(x) to the total number of points is the same as the ratio between the respective areas.
  3. Use the formulas
    x = a + (b a)RND()


and to generate a random point (x, y) in the rectangle [a, b]×[c, d] for any interval


y = c + (d c)RND() [a,b] on X-axis, and any interval [c,d] on Y-axis.



  1. Sketch of the algorithm. The first total number of generated random points is Ntotal=10,000. The first approximation of integral is S1. Then increment: Ntotal = Ntotal +5000; calculate next approximation S2. Continue sequential increment of Ntotal to obtain approximations S3, S4, S5, ....
  2. Use DO WHILE loop to stop calculations when |Sn Sn+1| < 0.001 for some value of n. Display the final result in cell A1 and report by using Speech object.
 
Upvote 0
Re: Monte Carlo Method Help

Let's break it down into steps ....

- Do you understand what you're being asked to do, i.e. calculate the integral from 0 to 1 for the function e^-xdx ?
- How would you represent this graphically?
- Purely based on this graph, what would be a reasonable first guess as to the expected result of the calculation?
- Do you understand the concept of testing whether random points are above or below f(x)?

If you are clear on these points, you'll know what your code will need to do, and we can help you translate this into VBA.
 
Upvote 0
Re: Monte Carlo Method Help

Yes, I know that the integral of my function given the numerical limits is 0.632121. From doing research I know that I need my function represented in the module and I need to call my function into my sub. I know the number of subdivisions will be 10,000 and increase by 5,000. I understand that if it is included in the area of my sub I need to count it and if it is not it needs to be excluded. I understand that would be an If-Then statement. I know that I need my code to calculate numerous random values to see if it falls into that area to calculate my integral. I guess my problem is translating what I am doing into vba code
 
Upvote 0
Re: Monte Carlo Method Help

OK, that's a great start. The exact solution is 1-EXP(-1) as you note. But if you graph the function, you should also be able to see that a reasonable starting guess would be around 0.6 - 0.7?

Rather than generating partitions (i.e. the way you usually start learning to integrate) you're actually generating 10,000 random points in a boundary rectangle, and testing whether these lie above or below f(x).

I guess my problem is translating what I am doing into vba code

We're very happy helping you translate instructions into VBA. In words, how would you describe the algorithm you'd like the VBA code to run?

I'm going to be off-air now until tomorrow morning my local time. In the meantime, someone else may want to jump in and help you along.
 
Upvote 0
Re: Monte Carlo Method Help

I know that I need to declare my variables, so let's say
a=1 upperbound
b=0 lowerbound
n=10000
count=0
I need to have my function in the module. So the integral of e^-x from 0 to 1
I need to randomize my x.
So x=RND().
For the function, if it lies under the curve count if not do not count
From there I am a little confused.
 
Upvote 0
Re: Monte Carlo Method Help

This is what I think I have so far
Dim x as Double
Dim y as Double
Dim index as integer
Dim numtries as integer
Dim count as integer
numtries=10000
count=0
For index = T to numtries
x=RND()
y=RND()
If my function <= .6 or .7
count=count + 1
End If
Then I need to Do While the difference of the numtries changes from 10,000 to 15000 until the difference is <.001
 
Upvote 0
You need to test whether the random point is below the function, and tally the points that are.
 
Upvote 0

Forum statistics

Threads
1,223,231
Messages
6,170,884
Members
452,364
Latest member
springate

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?

Disable AdBlock

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
Back
Top