
#62




I haven't found anything specific recently. NTRand has gotten the job done for me.... and, more to the point, I haven't done any serious Monte Carlo modeling in Excel in years. Most of the "random" stuff I've done in Excel is for illustrative purposes only, for which RAND() is good enough (and I usually want to show the Cholesky decomposition myself for those illustrations... these are for teaching purposes, not calculating "real" models).
There is a PRNG embedded in the most recent release of the Academy's economic scenario generator here: http://www.actuary.org/life/phase3.asp ...if you'd like something you can look at the guts of. There's also the code from Numerical Recipes in Fortran and/or C which should be adaptable to VBA: http://www.nr.com/ I usually take the Fortran code to adapt to VBA, but that's because I learned Fortran 77 before I learned C (and I find the data structures more straightforward in Fortran). Also, I have an excellent text on numerical algorithms for Fortran (somewhere...probably on the 3rd floor of my house, where most of my programming books are). One of the free NR books for Fortran: http://apps.nrbook.com/fortran/index.html (Chapter 7 is the PRNG chapter... has some good stuff even if you don't use their variants...) And of course, you can go to Wikipedia, which has good pseudocode for stuff. Here's Mersenne Twister: http://en.wikipedia.org/wiki/Mersenn...ter#Pseudocode List of PRNGs: http://en.wikipedia.org/wiki/List_of...rs_.28PRNGs.29 
#63




Quote:
Quote:
Okay since starting (still entry level here) I've dealt with two stochastic models one RND() the other RAND(), and I agree the seeds are nice. You can fiddle with the calculations without losing your randominputs. Copying and pasting values does that too, but the model's complicated enough that it would be a total mess, Rand()s in 2 different workbooks, 4 different worksheets, the distributions mixed and correlated in different ways, etc. Also with RND(), when overhauling the algorithm for speed, it's quite satisfying to see all 10,000 scenarios produce the exact same results. 
#64




For many "offtheshelf" algorithms, I don't necessarily perform my own tests (unless I coded it, and then I will test it, because chances are pretty good I screwed it up somehow).
I have also spat out text files of the "random" numbers used for auditing purposes, as space is cheap. So seeds aren't necessarily important there. 
#65




Quote:
Quote:
Conceivably it could, if your cycle lined up perfectly with the PRNG repetition  but this is more than not unlikely. 
#66




Quote:

#67




Quote:
Autocorrelations are more troublesome, of course. 
#68




My biggest problem with rand() is that I alttab into the model and VBA forgets it was supposed to be recalculating and just starts copying and pasting repeats...

Tags 
data science, excel, predictive analytics, prngs, pseudorandom numbers, rand, random 
Thread Tools  Search this Thread 
Display Modes  

