Actuarial Outpost Pseudorandom number generation in Excel
 Register Blogs Wiki FAQ Calendar Search Today's Posts Mark Forums Read
 FlashChat Actuarial Discussion Preliminary Exams CAS/SOA Exams Cyberchat Around the World Suggestions

 Upload your resume securely at https://www.dwsimpson.com to be contacted when new jobs meet your skills and objectives.

#11
07-16-2009, 04:52 PM
 ldancer911 Member Join Date: Mar 2007 Posts: 17,317

Quote:
 Originally Posted by spencerhs5 If doing simulation wouldnt you be likely to be using VBAs rnd function? Which if I recall has limitations as well but can be initialized so that its truely random. I could be wrong, but I think this is what I remember.
It also takes about 5 minutes to create a VBA function for a random number which won't recalculate as it does in excel.

I dont know anything about the randomness for the simulations though.
#12
07-16-2009, 05:01 PM
 ADoubleDot Member SOA Join Date: Nov 2007 Location: Slightly Dusty South Studying for the rest of my life Posts: 37,153

Quote:
 Originally Posted by echo This is Microsoft's solution. From the help manual under RAND(): "If you want to use RAND to generate a random number but don't want the numbers to change every time the cell is calculated, you can enter =RAND() in the formula bar, and then press F9 to change the formula to a random number. " What a wonderful solution! What could be better? Interesting that they don't even mention copy/ paste special(values) or as you suggested setting the sheet to manual calculation.
Is this as dumb as I think it is? I keep reading it trying to decipher how that solves anything.
__________________

**** Juan.
#13
07-16-2009, 05:54 PM
 Guilty Bystander Member SOA AAA Join Date: Jun 2005 College: Meeeshigan Favorite beer: Bell's Two Hearted IPA & kunhenn's DRIPA Posts: 1,775

In excel VBA, one can produce the same sequence of random numbers if desired.
#14
07-16-2009, 05:57 PM
 echo Member Join Date: Nov 2002 Posts: 883

For those serious about pseudo random number generation, a good free starting point is the Interest Rate scenario generator spreadsheet created by the Academy's Economic Scenario Work Group (ESWG), which contains a VBA random number generator (http://www.actuary.org/life/scenarios.asp). Of course, that's only a small part of the spreadsheet, but I found it interesting to look through.

Last edited by echo; 07-17-2009 at 10:24 AM.. Reason: Corrected - does NOT uses Mersenne Twister algorithm
#15
07-16-2009, 06:04 PM
 campbell Mary Pat Campbell SOA AAA Join Date: Nov 2003 Location: NY Studying for duolingo and coursera Favorite beer: Murphy's Irish Stout Posts: 86,487 Blog Entries: 6

Rnd [the VBA implementation in Excel] is seeded. That's a good point. Obviously, it's not volatile, as it's within VBA code itself, and you have to specifically run that. [Yes, you can have it run on certain actions, like ThisWorkbook_Change, but then you did it on purpose to yourself].

Given that, I'm going to go test this dang thing on my own. I have a separate PRNG I want to test [related to PBA stuff in the U.S.]

By the way, I do use RAND(). It's mainly for demo spreadsheets, for educational purposes. I don't really want to be going to the trouble of incorporating add-ins such as NtRand [more on that later], or adapting something from Numerical Recipes, if all I'm doing is demonstrating simulation techniques.

And lastly, while Pseud is a great poster, if you want to make some nice references to him regarding generating new Pseuds, there are several threads in NAT and the Reef that would be more appropriate. Just a suggestion. [I know I'm not always serious, but I intend this to be a serious thread on Monte Carlo modeling.]
__________________
It's STUMP

#16
07-16-2009, 06:04 PM
 atomic Member CAS Join Date: Jul 2006 Posts: 4,088

I use Mathematica and import the data into Excel. Or better yet, avoid Excel entirely.
__________________
Spoiler:
"No, Moslems don't believe Jesus was the messiah.

Think of it like a movie. The Torah is the first one, and the New Testament is the sequel. Then the Qu'ran comes out, and it retcons the last one like it never happened. There's still Jesus, but he's not the main character anymore, and the messiah hasn't shown up yet.

Jews like the first movie but ignored the sequels, Christians think you need to watch the first two, but the third movie doesn't count, Moslems think the third one was the best, and Mormons liked the second one so much they started writing fanfiction that doesn't fit with ANY of the series canon."

-RandomFerret
#17
07-16-2009, 06:09 PM
 campbell Mary Pat Campbell SOA AAA Join Date: Nov 2003 Location: NY Studying for duolingo and coursera Favorite beer: Murphy's Irish Stout Posts: 86,487 Blog Entries: 6

Quote:
 Originally Posted by echo For those serious about pseudo random number generation, a good free starting point is the Interest Rate scenario generator spreadsheet created by the Academy's Economic Scenario Work Group (ESWG), which contains a VBA random number generator (http://www.actuary.org/life/scenarios.asp). I think it uses the Mersenne Twister algorithm that is described as "well documented and robust". Of course, that's only a small part of the spreadsheet, but I found it interesting to look through.
No, it's not Mersenne Twister. [Not the one incorporated in the C3 Phase III interest rate scenario generator. I'm currently working on it with ESIWG.....which is the sequel to ESWG.]
__________________
It's STUMP

#18
07-16-2009, 06:09 PM
 magillaG Member Join Date: Jun 2007 Posts: 2,855

One also has to be very careful about running out of randomness. I had a colleague who spent some number of weeks trying to debug a monte carlo simulation, only to eventually find out the randon number generator wasn't "big" enough for the number of simulations. It was starting to create patterns, which was screwing up the distributions of the results, or something like that. I don't know how good the excel rand is in this respect- probably if you are doing something in excel, you don't have so many experiments to worry about it.
#19
07-16-2009, 06:12 PM
 campbell Mary Pat Campbell SOA AAA Join Date: Nov 2003 Location: NY Studying for duolingo and coursera Favorite beer: Murphy's Irish Stout Posts: 86,487 Blog Entries: 6

Quote:
 Originally Posted by atomic I use Mathematica and import the data into Excel. Or better yet, avoid Excel entirely.
This is not a bad idea. Excel is not optimized for heavy-duty numerical computation, frankly. I'm a fan of R... [being free and all], and also just plain direct coding in C [and its variants] or Fortran.

It is true that one shouldn't be doing heavy-duty Monte Carlo modeling in Excel, no matter the PRNG. But I've done it, and sometimes you gotta use the tools at hand.

Also [looking at the Academy interest rate generator above], sometimes someone has already done the work for you in Excel. So it can make sense to take advantage of that.
__________________
It's STUMP

#20
07-16-2009, 06:51 PM
 atomic Member CAS Join Date: Jul 2006 Posts: 4,088

One of the problems I have with Excel is that it is not programmed to check numerical stability. Certain mathematical operations lead to loss of precision or spurious precision, and under an iterative algorithm (as is often the case with Goal Seek or formulas that calculate off of other cells), the result of certain calculations could lose significance. Excel will give you an answer, but how many digits of that answer are known to be correct? If the calculation is performed with higher precision, how many digits will change? These are questions that Excel is not designed to address. Mathematica has built-in checking and uses known stable algorithms wherever possible to avoid losing precision. It very often errs on the conservative side, and of course it is slower because of this checking. And occasionally you have to have a better mathematical understanding of how to properly implement a particular calculation. But the result is that you have a much greater degree of confidence in the answer.

Indeed, I would say that most any arbitrary-precision computing environment has to do some of this checking that Excel does not.
__________________
Spoiler:
"No, Moslems don't believe Jesus was the messiah.

Think of it like a movie. The Torah is the first one, and the New Testament is the sequel. Then the Qu'ran comes out, and it retcons the last one like it never happened. There's still Jesus, but he's not the main character anymore, and the messiah hasn't shown up yet.

Jews like the first movie but ignored the sequels, Christians think you need to watch the first two, but the third movie doesn't count, Moslems think the third one was the best, and Mormons liked the second one so much they started writing fanfiction that doesn't fit with ANY of the series canon."

-RandomFerret

 Tags data science, excel, predictive analytics, prngs, pseudorandom numbers, rand, random