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

Browse Open Actuarial Jobs

Life  Health  Casualty  Pension  Entry Level  All Jobs  Salaries


Reply
 
Thread Tools Display Modes
  #21  
Old 07-16-2009, 07:03 PM
Runner Runner is offline
Member
 
Join Date: Aug 2008
Posts: 2,419
Default

Quote:
Originally Posted by campbell View Post
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?
Reply With Quote
  #22  
Old 07-16-2009, 07:57 PM
Dr T Non-Fan Dr T Non-Fan is offline
Member
SOA AAA
 
Join Date: Sep 2001
Location: Just outside of Nowhere
Posts: 84,769
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. 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.
Twitches' Advice to Crazy Women: Please just go buy your 30 cats already.
Reply With Quote
  #23  
Old 07-16-2009, 07:58 PM
Dr T Non-Fan Dr T Non-Fan is offline
Member
SOA AAA
 
Join Date: Sep 2001
Location: Just outside of Nowhere
Posts: 84,769
Default

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.
Twitches' Advice to Crazy Women: Please just go buy your 30 cats already.
Reply With Quote
  #24  
Old 07-16-2009, 08:01 PM
Brad Gile's Avatar
Brad Gile Brad Gile is offline
Member
CAS SOA AAA
 
Join Date: Sep 2001
Studying for whatever I feel like
College: Alumnus of Brown and UW-Madison
Posts: 11,341
Default

Quote:
Originally Posted by campbell View Post
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.
__________________
Brad Gile, FSA, MAAA
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
Reply With Quote
  #25  
Old 07-16-2009, 08:03 PM
Dr T Non-Fan Dr T Non-Fan is offline
Member
SOA AAA
 
Join Date: Sep 2001
Location: Just outside of Nowhere
Posts: 84,769
Default

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.
Twitches' Advice to Crazy Women: Please just go buy your 30 cats already.
Reply With Quote
  #26  
Old 07-16-2009, 08:05 PM
Dr T Non-Fan Dr T Non-Fan is offline
Member
SOA AAA
 
Join Date: Sep 2001
Location: Just outside of Nowhere
Posts: 84,769
Default

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.
Twitches' Advice to Crazy Women: Please just go buy your 30 cats already.
Reply With Quote
  #27  
Old 07-16-2009, 08:33 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: 76,178
Blog Entries: 6
Default

Quote:
Originally Posted by Dr T Non-Fan View Post
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].
Reply With Quote
  #28  
Old 07-16-2009, 10:07 PM
Brad Gile's Avatar
Brad Gile Brad Gile is offline
Member
CAS SOA AAA
 
Join Date: Sep 2001
Studying for whatever I feel like
College: Alumnus of Brown and UW-Madison
Posts: 11,341
Default

Quote:
Originally Posted by Dr T Non-Fan View Post
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/
__________________
Brad Gile, FSA, MAAA
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
Reply With Quote
  #29  
Old 07-16-2009, 11:06 PM
Actuarialsuck Actuarialsuck is offline
Member
 
Join Date: Sep 2007
Posts: 6,064
Default

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 View Post
i'm not. i do not troll.
Reply With Quote
  #30  
Old 07-17-2009, 12:39 AM
Jemaine Clement's Avatar
Jemaine Clement Jemaine Clement is offline
Member
SOA
 
Join Date: Apr 2009
Posts: 606
Default

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
__________________
It's business time...

Last edited by Jemaine Clement; 07-17-2009 at 01:14 AM..
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:54 AM.


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.27151 seconds with 9 queries