Loop Macro for Simulation

[SonofaBish]

Hello all,

I am by no means new to Excel, but i am very new to writing code for macros... I am working on a side project that I need to use ONLY the resources available to me at home. I use SAS frequently at work, and I know how to do this in SAS, but I can't use my work resources for personal use ...

I am running a bit of a simplified Monte Carlo simulation using random normal variables to calculate the price of a commodity based upon historical information and trends... What i am doing is simulating a certain number of prices, and calculating an average... This average is considered 1 "trial". I want to "paste special" this average into a cell as Trial 1, and then recalculate and "paste special" the next simulated average into a cell for trial two. I want this to loop for 1000+ trials...

Here is a little bit of code showing what i want the macro to do... just paste a value, move down one cell and paste the next simulated value. Can anyone add to this macro to make it loop until i get to a specified number of trials??

Thanks so much!!

Please Login or Register  to view this content.

*** since the RAND() function recalculates with every cell change or function, there's no need to re-calculate...

[Richard Buttrey]

Hi,
How about something like:

Please Login or Register  to view this content.

HTH

[SonofaBish]

Hi,
How about something like:

Please Login or Register  to view this content.

HTH

THanks! I'll give it a shot and see what i come up with.... i don't think i'll get to til tomorrow night, but we'll see how it goes....

Thanks again!

[avadlez210]

Hello Guys,

I'm doing something similiar. I was given an excel spreadsheet which calculates the price of stock and the value of random stock every year for 37 years based on the geometric brownian motion model. Every time I press F9 excel runs a new simulation in which the prices of stock change every year. I'm supposed to write a macros that does 1000 simulations and finds the average of the stock price in year 37 over those 1000 simulations (Add the resulting value of cell F39 for every of the 1000 simulations(everytime I press F9) and divide it by 1000). I have no clue on how to set up this loop, I've done simple loops using pseudo code in excel VBA but nothing like this.

Please Help!!!!!!!!!!!