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

#21
07-16-2009, 07:03 PM
 Runner Member Join Date: Aug 2008 Posts: 2,429

Quote:
 Originally Posted by campbell 1. RAND() is volatile 2. RAND() is an unseeded PRNG
I use RAND() to generate a seqence of random numbers, and then I save them in the spreadsheet. All the other calculations depend on those saved values. What's the problem?
#22
07-16-2009, 07:57 PM
 Dr T Non-Fan Member SOA AAA Join Date: Sep 2001 Location: Just outside of Nowhere Posts: 91,158

Quote:
 Originally Posted by campbell 1. RAND() is volatile That sucker reevaluates every time the spreadsheet recalculates, which could be every time you change a cell if you have it on automatic calculation. If you have too many RAND() calls, your spreadsheet will be slower than molasses even if you just type a short string in a cell.
Um, so don't use a RAND() function in a formula. Duh.
You create a set of random numbers using a simple macro:
1. Calc;
2. Copy RAND() cell;
3. PSV to row x;
4. x = x+1; GOTO 1
5. End when you feel like it.

Is this difficult? I don't think so.
__________________
DTNF's Basic Philosophy Regarding Posting: There's no emoticon for what I'm feeling! -- Jeff Albertson (CBG)
DTNF's Trademarked Standard Career Advice: "pass some exams and get back to us."
DTNF's Major advice: "Doesn't matter. Choose major that helps you with goal of Career Advice."
DTNF's Résumé Advice: Have a good and interesting answer to every item on it for the interviews.
DTNF's Law of Job Offers: You not only have to qualify for the position, but you also have to be the best candidate available for the offer.
DTNF's Work Philosophy: I am actuary. Please insert data. -- Actuary Actuarying Rodriguez.
#23
07-16-2009, 07:58 PM
 Dr T Non-Fan Member SOA AAA Join Date: Sep 2001 Location: Just outside of Nowhere Posts: 91,158

Runner = DNTF, just a faster version.
__________________
DTNF's Basic Philosophy Regarding Posting: There's no emoticon for what I'm feeling! -- Jeff Albertson (CBG)
DTNF's Trademarked Standard Career Advice: "pass some exams and get back to us."
DTNF's Major advice: "Doesn't matter. Choose major that helps you with goal of Career Advice."
DTNF's Résumé Advice: Have a good and interesting answer to every item on it for the interviews.
DTNF's Law of Job Offers: You not only have to qualify for the position, but you also have to be the best candidate available for the offer.
DTNF's Work Philosophy: I am actuary. Please insert data. -- Actuary Actuarying Rodriguez.
#24
07-16-2009, 08:01 PM
 Brad Gile Member CAS SOA AAA Join Date: Sep 2001 Studying for whatever I feel like College: Alumnus of Brown and UW-Madison Posts: 11,341

Quote:
 Originally Posted by campbell 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.

If you MUST use Excel (as I did at work), avoid RAND like the plague. Use rnd in VBA. My limited testing of Monte Carlo simulations with vba generally indicated that the VB LCG (used in vba) produced good results for moderate sized samples, where "moderate" really depends on the sophistication for what you are trying to do with the results.

There is at least one Excel add-in I have seen uses the Mersenne Twister, which is a heavy duty RNG suitable, I suspect, for just about anything you want to do. That was a product called ZRandom, created by the AO's Banquet of Chestnuts. http://www.zrandom.com/zrand/
Disclaimer: I have no connection with either the product or its developer/company.
__________________
Affiliate Member of the CAS
Dedicated Retired Actuary

Spoiler:
Obama sucks and we all know it-TDA

Spoiler:

That's been the funniest subplot of this whole thing, the people on the left attacking this bill for not being even more of a steaming pile. - erosewater
#25
07-16-2009, 08:03 PM
 Dr T Non-Fan Member SOA AAA Join Date: Sep 2001 Location: Just outside of Nowhere Posts: 91,158

I think that being able to reproduce a list of random numbers would prove that they aren't random. Typical trick question by an auditor, designed to prolong the hourly-rate-paid investigation.
__________________
DTNF's Basic Philosophy Regarding Posting: There's no emoticon for what I'm feeling! -- Jeff Albertson (CBG)
DTNF's Trademarked Standard Career Advice: "pass some exams and get back to us."
DTNF's Major advice: "Doesn't matter. Choose major that helps you with goal of Career Advice."
DTNF's Résumé Advice: Have a good and interesting answer to every item on it for the interviews.
DTNF's Law of Job Offers: You not only have to qualify for the position, but you also have to be the best candidate available for the offer.
DTNF's Work Philosophy: I am actuary. Please insert data. -- Actuary Actuarying Rodriguez.
#26
07-16-2009, 08:05 PM
 Dr T Non-Fan Member SOA AAA Join Date: Sep 2001 Location: Just outside of Nowhere Posts: 91,158

Question: is RAND() random? Meaning, can you create a program that predicts the next number it will create?
__________________
DTNF's Basic Philosophy Regarding Posting: There's no emoticon for what I'm feeling! -- Jeff Albertson (CBG)
DTNF's Trademarked Standard Career Advice: "pass some exams and get back to us."
DTNF's Major advice: "Doesn't matter. Choose major that helps you with goal of Career Advice."
DTNF's Résumé Advice: Have a good and interesting answer to every item on it for the interviews.
DTNF's Law of Job Offers: You not only have to qualify for the position, but you also have to be the best candidate available for the offer.
DTNF's Work Philosophy: I am actuary. Please insert data. -- Actuary Actuarying Rodriguez.
#27
07-16-2009, 08:33 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: 82,437 Blog Entries: 6

Quote:
 Originally Posted by Dr T Non-Fan Question: is RAND() random? Meaning, can you create a program that predicts the next number it will create?
Let's see if I can find the quote....

Ah, here it is:

Quote:
 As John von Neumann joked, "Anyone who considers arithmetical methods of producing random digits is, of course, in a state of sin."
[from http://en.wikipedia.org/wiki/Pseudor...ber_generator]

For those unfamiliar with the theoretical/practical issues with PRNGs, the Wikipedia article is a good start. PRNGs are used for more than just Monte Carlo modeling [which is generally an attempt to approximate some probability distribution, or derived features of said distribution [e.g., expected value]]... many of the PRNGs used for financial stochastic modeling are considered deficient for cryptographic concerns.

As for audits, if you save the number sequence that gives you your results, that should be sufficient. They can run their own tests of the goodness of the stats of your PRNs or your model results as they wish. If you're not drawing on the order of a trillion+ numbers for your modeling, you might not have to worry about the repeat.

[As atomic mentions, there are some weird things that happen due to the floating point arithmetic in Excel... fixed point is obviously problematic, but even floating point has issues. I've been thinking of writing something up on floating point computation issues for CompAct, or maybe even one of the more technical actuarial journals. This can end up a real problem.... this is not necessarily getting OT with PRNGs, because some of the things I've come across on Excel discussion boards are weird results from RAND() because of precision issues. If you're using RAND() to feed into an infinite range distribution such as the gaussian, you're going to end up with NaN [not a number] if the PRNG gives you either 0 or 1].
__________________
It's STUMP

#28
07-16-2009, 10:07 PM
 Brad Gile Member CAS SOA AAA Join Date: Sep 2001 Studying for whatever I feel like College: Alumnus of Brown and UW-Madison Posts: 11,341

Quote:
 Originally Posted by Dr T Non-Fan Question: is RAND() random? Meaning, can you create a program that predicts the next number it will create?
It is somewhat ironic that "random" or, more correctly, "pseudorandom" numbers are not only NOT truly random but are, in fact, almost always generated using a well defined deterministic process. I say "almost" because it is actually possible to use atomic decay of radioactive isotopes to create these numbers! Now THAT is hardcore!
See e.g., http://www.fourmilab.ch/hotbits/
__________________
Affiliate Member of the CAS
Dedicated Retired Actuary

Spoiler:
Obama sucks and we all know it-TDA

Spoiler:

That's been the funniest subplot of this whole thing, the people on the left attacking this bill for not being even more of a steaming pile. - erosewater
#29
07-16-2009, 11:06 PM
 Actuarialsuck Member Join Date: Sep 2007 Posts: 6,111

I may be completely off base here but couldn't you use strange attractors to generate random numbers? I think I recall someone talking about that in college but I could be just making it up... atomic to the rescue?
__________________
Quote:
 Originally Posted by Buru Buru i'm not. i do not troll.
#30
07-17-2009, 12:39 AM
 Jemaine Clement Member SOA Join Date: Apr 2009 Posts: 607

I'm not sure if it's all that applicable to actuarial practice, but the story of the downfall of Planet Poker is still a great example of pseudorandom number generation gone horribly wrong.

[ETA] For an example of random number generation done right, check out Pokerstars' shuffle algorithm.

[ETA2] The Pokerstars page contains cites another interesting article: a research paper on the Intel (True) Random Number Generator
__________________

Last edited by Jemaine Clement; 07-17-2009 at 01:14 AM..

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