Actuarial Outpost
 
Go Back   Actuarial Outpost > Actuarial Discussion Forum > Software & Technology
FlashChat Actuarial Discussion Preliminary Exams CAS/SOA Exams Cyberchat Around the World Suggestions


Reply
 
Thread Tools Display Modes
  #1  
Old 07-16-2009, 02:09 PM
campbell's Avatar
campbell campbell is offline
Mary Pat Campbell
SOA AAA
 
Join Date: Nov 2003
Location: NY
Studying for duolingo and coursera
Favorite beer: Murphy's Irish Stout
Posts: 78,341
Blog Entries: 6
Default Pseudorandom number generation in Excel

I was asked as to why RAND() in Excel sucks in a different thread.

I believe this is an important enough topic I'm spinning it off to here.


I'm going to give two practical reasons up front - and this holds for all versions of Excel that have a RAND() function. I can talk about tests of randomness at a later time. The first issue is relatively minor, if annoying, but the second issue is a big bold X over using the function for serious Monte Carlo modeling.


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.

A bit of info on volatile functions:
http://www.decisionmodels.com/calcsecretsi.htm

You could keep your spreadsheet in manual calculation mode, but that has dangers of its own.

2. RAND() is an unseeded PRNG

This is important for serious Monte Carlo applications. You need to be able to reproduce a particular sequence of numbers. The way this is accomplished in most PRNGs is by having a seed for the PRNG, which is an argument passed to the generator. This will set the initial "random" number for a sequence, and then you will get a specific "random" sequence out for however long it takes until it repeats.

One of the real reasons you need this seed is so you can put the PRNG through a bunch of tests - the Diehard suite of tests is common, but there are other tests you can get from the National Institute of Standards and Tech [NIST].


When you combine 1 & 2, you basically get an untestable algorithm, and I really can't rely on Microsoft statements such as this one:
http://support.microsoft.com/kb/828795

They claim that the current [Excel 2003 & 2007] RAND() has a period of >10^13 [so it will take over 10 trillion calls before it repeats] and that the underlying PRNG satisfies the Diehard tests.

But how can I test that? Sure, I can get around the volatility issue by setting calculation to manual, but then I have the issue of generating over 10 trillion numbers to try to catch the repeat... but because of the structure of Excel, I'm not quite sure what the "order" of these numbers is supposed to be. With seeded PRNGs, you can say "I want the 1000th number generated in the sequences seeded with 2362357" [or whatever]. I can't do that with Excel's RAND().

Of course, I can get around the reproducibility problem by saving all the numbers generated in my pseudorandom sequence... but why should I have to when there are perfectly decent seeded PRNGs out there?

More later.
__________________
It's STUMP

LinkedIn Profile
Reply With Quote
  #2  
Old 07-16-2009, 02:19 PM
tommie frazier tommie frazier is offline
Member
 
Join Date: Aug 2003
Favorite beer: The kind with 2 e's
Posts: 22,277
Default

thanks campbell. I have always heard that it was lousy for serious simulations, but hadn't cared to dig into the why of it.
Reply With Quote
  #3  
Old 07-16-2009, 02:30 PM
ADoubleDot's Avatar
ADoubleDot ADoubleDot is offline
Member
Non-Actuary
 
Join Date: Nov 2007
Location: Slightly Dusty South
Studying for the rest of my life
Posts: 37,035
Default

Thanks, that makes sense. The volatility thing is truly annoying, but I guess excel will work for what I need. I just wanted to make sure it wasn't wrong.
__________________
ADoubleDot: I'm an actuarial icon.

**** Juan.
Reply With Quote
  #4  
Old 07-16-2009, 03:03 PM
1695814's Avatar
1695814 1695814 is offline
Member
SOA AAA
 
Join Date: Aug 2002
Studying for GED
Favorite beer: Root
Posts: 33,274
Default

Quote:
Originally Posted by campbell View Post
This is important for serious Monte Carlo applications. You need to be able to reproduce a particular sequence of numbers.
why is that?


because of this?
Quote:
Originally Posted by campbell View Post
One of the real reasons you need this seed is so you can put the PRNG through a bunch of tests.
and why is that? to show that it's random-enough?
__________________
.
Sheep Dog Dare Game Champion!
Reply With Quote
  #5  
Old 07-16-2009, 03:05 PM
JMO's Avatar
JMO JMO is offline
Carol Marler
Non-Actuary
 
Join Date: Sep 2001
Location: Back home again in Indiana
Studying for Nothing actuarial.
Posts: 36,246
Default

Quote:
Originally Posted by 1695814 View Post
why is that?

For your auditors who want you to document your MonteCarlo process. For doing reruns of a stochastic model with corrected data. For coming up with a winning strategy at roulette.

Quote:
because of this?and why is that? to show that it's random-enough?
yes, exactly.

And, when I first started doing actuarial work, PCs had not been invented.

So we had a statistics book that included a table of random numbers. It was used to sample our activities at various times so that we wouldn't have to keep a detailed time log. This data was then used in expense allocations.

It was also useful for selecting samples, such as for selecting data for the auditors. Really.
__________________
Carol Marler, "Just My Opinion"

Pluto is no longer a planet and I am no longer an actuary. Please take my opinions as non-actuarial.


My latest favorite quotes, updated Sep 18, 2017.

Spoiler:
I should keep these four permanently.
Quote:
Originally Posted by rekrap View Post
JMO is right
Quote:
Originally Posted by campbell View Post
I agree with JMO.
Quote:
Originally Posted by Westley View Post
And def agree w/ JMO.
Quote:
Originally Posted by MG View Post
This. And everything else JMO wrote.
MORE:
Quote:
Originally Posted by Dr T Non-Fan View Post
It would be rude to turn them down rudely. Try not to do that.
Quote:
Originally Posted by Woodrow View Post
Complementing your technical skills with social skills - whatever they are - will help your career. This is not a sign that the world is unjust.
Quote:
Originally Posted by Bro View Post
I recommend you get perspective.
Quote:
Originally Posted by Enough Exams Already View Post
Dude, you can't fail a personality test. It just isn't that kind of test.
Quote:
Originally Posted by Locrian View Post
I'm disappointed I don't get to do both.

Last edited by JMO; 07-16-2009 at 03:12 PM.. Reason: Adding stuff from the olden days
Reply With Quote
  #6  
Old 07-16-2009, 03:06 PM
Bubba Colbert's Avatar
Bubba Colbert Bubba Colbert is offline
Member
 
Join Date: Mar 2008
Posts: 1,918
Default

Pseudolus is one of a kind, I don't think you can randomly generate more of him. Although Mrs. Pseudolus has the best shot at coming close.
Reply With Quote
  #7  
Old 07-16-2009, 03:43 PM
DeepPurple's Avatar
DeepPurple DeepPurple is offline
Member
 
Join Date: Jun 2004
Posts: 3,862
Default

I think that you can seed the Excel PRNG in VB.
__________________
Come on. Let's go space truckin'. Come on!
Reply With Quote
  #8  
Old 07-16-2009, 03:47 PM
E's Avatar
E E is online now
Eddie Smith
SOA AAA
 
Join Date: May 2003
College: UGA
Posts: 8,774
Default

Quote:
Originally Posted by Bubba Colbert View Post
Pseudolus is one of a kind, I don't think you can randomly generate more of him. Although Mrs. Pseudolus has the best shot at coming close.
Now I think you're alluding to the RANDY() function.

Spoiler:

sarcasm <> hijacking

__________________


Learn how FSA exams are different from the prelims

Registration is open for all of our fall 2017 FSA exam online seminars:

ILA-LP | ILA-LFV-U | ILA-LFV-C | ILA-LRM | QFI Core | QFI Adv | QFI IRM | G&H Core | G&H Adv | G&H Specialty

Check out our Technical Skills Course: Excel, VBA, Access, brand new R material, and more!

Follow us on Twitter, Facebook, and LinkedIn
Reply With Quote
  #9  
Old 07-16-2009, 04:09 PM
echo echo is offline
Member
 
Join Date: Nov 2002
Posts: 774
Default

Quote:
Originally Posted by campbell View Post
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.
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.
Reply With Quote
  #10  
Old 07-16-2009, 04:20 PM
spencerhs5's Avatar
spencerhs5 spencerhs5 is offline
Member
 
Join Date: Jan 2007
Posts: 1,447
Default

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.
Reply With Quote
Reply

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

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


All times are GMT -4. The time now is 09:35 PM.


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
*PLEASE NOTE: Posts are not checked for accuracy, and do not
represent the views of the Actuarial Outpost or its sponsors.
Page generated in 0.22375 seconds with 9 queries