VBA for Monte Carlo Simulation Question

[pplstuff]

I'm interested becoming more proficient in VBA. I have read Walkenbach Power Programming, and For Dummies. Additionally, I use macros occasionally at work.

To learn more, I have decided to try and code my own Monte Carlo Simulation. Basically, I'd like to replicate Crystal Ball, but on my own. I believe this would be both challenging and rewarding.

My question is, are there any resources available to help guide me through the process of writing this complex program? I'm look for a link the might serve as a walkthrough for how to write something like this. Please post any advice or suggestions. Thanks.

 

[xenou]

This link has been helpful to others:
http://chandoo.org/wp/2010/05/06/data-tables-monte-carlo-simulations-in-excel-a-comprehensive-guide/

googling excel monte carlo gives me quite a lot of hits that you may also find interesting. I guess if you want further resources on advanced Excel programming a book called Professional Excel Development is usually next on the reading list. Creating a program like Crystal Ball is probably more than anyone can really help you with as it would encompass many hundreds of hours of work (or thousands in my case) - but give it a go and see where you get with it. It's definitely a worthwhile type of project to get into.

ξ

 

[pplstuff]

This link has been helpful to others:
http://chandoo.org/wp/2010/05/06/data-tables-monte-carlo-simulations-in-excel-a-comprehensive-guide/

Thanks for your response. the article was very interesting. He had an interesting approach to the simulation; however, I was hoping to do something like add a "Monte Carlo" tab in my ribbon, put some buttons on it, etc.

I'd like it to do everything (for example, I want to paste in historical data and have the program tell me the distribution (normal, logarithmic, possion, uniform, triangular, etc.) and give the statistics (min, max, mean, median, std.dev.). I'd like to see tornado charts, histograms (pdf and cdf).

If anyone else has a good link/resource please feel free to share. This is not an easy project. I was hoping there was a blog or something from someone who had logged their steps and what they had used to create a program like this.

 

[xenou]

You will want to be sure you know how to work with the ribbon. Crystal Ball is probably written in another language other than VBA (C++ or C# maybe). You might look at some other addins for Monte Carlo simulation to see how others have approached the problem (my google searched turn up a couple on the first page of hits).

 

[kimosabe]

I'm interested becoming more proficient in VBA. I have read Walkenbach Power Programming, and For Dummies. Additionally, I use macros occasionally at work.

To learn more, I have decided to try and code my own Monte Carlo Simulation. Basically, I'd like to replicate Crystal Ball, but on my own. I believe this would be both challenging and rewarding.

My question is, are there any resources available to help guide me through the process of writing this complex program? I'm look for a link the might serve as a walkthrough for how to write something like this. Please post any advice or suggestions. Thanks.

You should try http://tukhi.com. It is free and does things Crystal Ball and @Risk can't. There are some cool demos under the Tukhi menu.

 

[xenou]

Did you write the Tukhi addin, kimosabe? Looks like you have a free/open approach with your work.

 

[kimosabe]

I used http://xll.codeplex.com to write it. See the Related Projects on the bottom right of that page for more open source projects.

Have you tried the Tukhi demos? They are like videos except they build a functioning workbook.

 

[xenou]

I gave the addin a try. Looks like a good tool. The codeplex project appears to give you something much like a compiled Excel addin created with Visual Studio (an xll). Very intriguing - I'll have to try that out too! Is tuhki something you want market? (Saw that there's an LLC behind the site - so I'm curious as the download is free, but not quite open source, I guess). We get a lot a questions here about creating datasets according to certain distributions - it looks like this feature could be very handy in your addin.

 

[kimosabe]

Yeah, the codeplex library makes it easy to create xll's. Not as easy as VBA, but almost.
What distributions are you looking for?

 

[xenou]

Well, often just a normal distribution is what we get requests about. Occasionally something else. In some cases it seems that the assumption of a normal distribution isn't quite justified but that's another story One of the things I wish Excel could do is plot a curve given the mean and standard deviation - would be useful for stats courses where profs want you to plot your curve. I think when a took a stats class a couple years ago I used StatCrunch to do this kind of graphing, but Excel otherwise.