wilmott.com

I heard many on here bashing the vba rnd() uniform random number generator. By making a sample of 50000 I havent been able to find any real problems with it. It doesnt fail the tests for uniformity and it doesnt seem to show any significant serial correlation. It might not be 100% great but for most it is probably good enough? Many also say that Excel isnt made for Monte Carlo. What do you guys use instead?

If you are only using 50,000 and you are using 2003 (i think?) or later then it should be ok. The biggest problem is that most monte carlo are making millions if not billions of calls to the RNG and the MS Excel one will actually start repeating the sequence after a while.If you want to do a proper test for randomness then I suggest you try DIEHARD. To even run the test you need about 10Mb of random numbers.The other big problem with it is it is very very slow.As far as what to use instead, c#.net or VB.net (both work very well behind excel if you need to) or Java if that's you thing.

I also use Java from time to time, but I dont see any speed improvements when using Javas built in generator over VBAs. Therefore I am tempted to just stay with Excel for Monte Carlo since outputting into a spreadsheet can sometimes be convinient. How would I go about programming something in Java and then using the functions in excel? And would it be any faster?

The thing with monte carlo is its a short phrase for a HUGE number of different things.For example the ATLAS particle detector at cern, has been tested with monte carlo. This equipment samples more data in a second than high frequency hedge funds take in in a year. Clearly they are not using excel!Then you've got all the "grown up" MC, the variations on metropolis hastings, such as gibbs, slice and hamilton mc. Each iteration is a rather meaty process in itself so doing something like that would just take far too many cells with if statements etc etc in them.Then there are the VaR programs that have to revalue huge portfolios many many times, in a spreadsheet thats just going to be messyThen we've got the CDO models etc, and implementing even gaussian copula in a spreadsheet is a bit annoying, but this works ok if you code it in VBA.Then you've got the toy problems and prototypes, the price this using monte carlo. These work really well in excel, except when you then want to encorporate that into your business wide portfolio management software...

QuoteOriginally posted by: European81I heard many on here bashing the vba rnd() uniform random number generator. By making a sample of 50000 I havent been able to find any real problems with it. It doesnt fail the tests for uniformity and it doesnt seem to show any significant serial correlation. It might not be 100% great but for most it is probably good enough? Many also say that Excel isnt made for Monte Carlo. What do you guys use instead?I just like packing my own parachute. The problem with using a built-in random number generator is not that it is good or bad, but that it is unspecified. If I use a specified random number generator, then if something weird happens, then I know enough about that part of the code that I can either decide whether or not to look at it further.

QuoteI just like packing my own parachute. The problem with using a built-in random number generator is not that it is good or bad, but that it is unspecified. If I use a specified random number generator, then if something weird happens, then I know enough about that part of the code that I can either decide whether or not to look at it further.One should also always try out for consistency atleast 2-3 Different generators, before goin to production. That's enough to bring eventually the blame on the generator without knowing it well. But yeah, even better knowing all your code.Besides, Excel's gen was not simply bad, it was horrible

I just used Mersenne Twister after reading their publication.