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
  #11  
Old 07-16-2009, 05:52 PM
ldancer911's Avatar
ldancer911 ldancer911 is offline
Member
 
Join Date: Mar 2007
Posts: 17,093
Default

Quote:
Originally Posted by spencerhs5 View Post
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.
Reply With Quote
  #12  
Old 07-16-2009, 06:01 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

Quote:
Originally Posted by echo View Post
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.
__________________
ADoubleDot: I'm an actuarial icon.

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

In excel VBA, one can produce the same sequence of random numbers if desired.
Reply With Quote
  #14  
Old 07-16-2009, 06:57 PM
echo echo is offline
Member
 
Join Date: Nov 2002
Posts: 786
Default

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 11:24 AM.. Reason: Corrected - does NOT uses Mersenne Twister algorithm
Reply With Quote
  #15  
Old 07-16-2009, 07:04 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

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

LinkedIn Profile
Reply With Quote
  #16  
Old 07-16-2009, 07:04 PM
atomic's Avatar
atomic atomic is offline
Member
CAS
 
Join Date: Jul 2006
Posts: 4,088
Default

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
Reply With Quote
  #17  
Old 07-16-2009, 07: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: 79,353
Blog Entries: 6
Default

Quote:
Originally Posted by echo View Post
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

LinkedIn Profile
Reply With Quote
  #18  
Old 07-16-2009, 07:09 PM
magillaG magillaG is offline
Member
 
Join Date: Jun 2007
Posts: 2,827
Default

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.
Reply With Quote
  #19  
Old 07-16-2009, 07:12 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 atomic View Post
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

LinkedIn Profile
Reply With Quote
  #20  
Old 07-16-2009, 07:51 PM
atomic's Avatar
atomic atomic is offline
Member
CAS
 
Join Date: Jul 2006
Posts: 4,088
Default

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