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
  #61  
Old 10-10-2011, 11:35 AM
dumples dumples is offline
Member
CAS
 
Join Date: Sep 2003
Posts: 1,240
Default

MPC, do you still recommend NTRand or have you found something better?
Reply With Quote
  #62  
Old 10-10-2011, 01:58 PM
limabeanactuary's Avatar
limabeanactuary limabeanactuary is offline
Mary Pat Campbell
 
Join Date: Jan 2010
Studying for Anglo-Saxon
Favorite beer: Bass Ale
Posts: 14,142
Default

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
__________________

Now offering online seminars, live seminars, and everything else under the sun and over the moon for actuarial exams.
Reply With Quote
  #63  
Old 10-11-2011, 05:17 PM
Gedankenexperiment Gedankenexperiment is offline
Member
 
Join Date: Dec 2010
Posts: 443
Default

Quote:
Originally Posted by campbell View Post
That sucker reevaluates every time the spreadsheet recalculates, which could be every time you change a cell if you have it on automatic calculation.
Real men don't use autocalc!
Quote:
2. RAND() is an unseeded PRNG
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
Do you actually test PRNG's? Or are you worried that nobody else will? Would it really matter if they were repeating every million values?

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 random-inputs. 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.
Reply With Quote
  #64  
Old 10-11-2011, 05:20 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: 79,353
Blog Entries: 6
Default

For many "off-the-shelf" 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.
__________________
It's STUMP

LinkedIn Profile
Reply With Quote
  #65  
Old 10-11-2011, 05:30 PM
whisper's Avatar
whisper whisper is offline
Member
CAS AAA
 
Join Date: Jan 2002
Location: Chicago
Favorite beer: Hefewizen
Posts: 34,098
Default

Quote:
Originally Posted by Gedankenexperiment View Post
Do you actually test PRNG's? Or are you worried that nobody else will?
I agree that testing an off-the-shelf PRNG from reputable sources would not be worth the effort.

Quote:
Would it really matter if they were repeating every million values?

Conceivably it could, if your cycle lined up perfectly with the PRNG repetition - but this is more than not unlikely.
Reply With Quote
  #66  
Old 10-11-2011, 05:44 PM
magillaG magillaG is offline
Member
 
Join Date: Jun 2007
Posts: 2,827
Default

Quote:
Originally Posted by Gedankenexperiment View Post
Real men don't use autocalc!
Do you actually test PRNG's? Or are you worried that nobody else will? Would it really matter if they were repeating every million values?

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 random-inputs. 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.
I've been involved with an analysis that started producing screwy results because the random number generator wasn't random enough. It was a very difficult problem to diagnose. You want to avoid it if you can.
Reply With Quote
  #67  
Old 10-11-2011, 05:45 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: 79,353
Blog Entries: 6
Default

Quote:
Originally Posted by whisper View Post
I agree that testing an off-the-shelf PRNG from reputable sources would not be worth the effort.




Conceivably it could, if your cycle lined up perfectly with the PRNG repetition - but this is more than not unlikely.
Depends on what you're doing with the PRNGs.

Autocorrelations are more troublesome, of course.
__________________
It's STUMP

LinkedIn Profile
Reply With Quote
  #68  
Old 10-11-2011, 06:14 PM
Gedankenexperiment Gedankenexperiment is offline
Member
 
Join Date: Dec 2010
Posts: 443
Default

Quote:
Originally Posted by campbell View Post
Depends on what you're doing with the PRNGs.

Autocorrelations are more troublesome, of course.
My biggest problem with rand() is that I alt-tab into the model and VBA forgets it was supposed to be recalculating and just starts copying and pasting repeats...
Reply With Quote
  #69  
Old 10-11-2011, 06:20 PM
whisper's Avatar
whisper whisper is offline
Member
CAS AAA
 
Join Date: Jan 2002
Location: Chicago
Favorite beer: Hefewizen
Posts: 34,098
Default

Quote:
Originally Posted by campbell View Post
Depends on what you're doing with the PRNGs.

Autocorrelations are more troublesome, of course.
Meh, anything that has to do with correlation is problematic.
Reply With Quote
  #70  
Old 10-11-2011, 06:25 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: 79,353
Blog Entries: 6
Default

Quote:
Originally Posted by whisper View Post
Meh, anything that has to do with correlation is problematic.
Unless you wanted correlations....
__________________
It's STUMP

LinkedIn Profile
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 06:16 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.27322 seconds with 9 queries