Pseudorandom number generation in Excel

[JMO]

Originally Posted by whisper

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.

If you are doing stochastic within stochastic, you need a very robust PRNG.

The practical question of "how many scenarios is enough?" also requires that adding more scenarios to your test does not result in repeating scenarios in the original set. Remember that for many applications, you need another random numbers for each year of the projection.

[Gedankenexperiment]

Originally Posted by JMO

The practical question of "how many scenarios is enough?" also requires that adding more scenarios to your test does not result in repeating scenarios in the original set.

I assumed this question is answered by the output. If your distributions are smooth high resolution curves, then you're doing well. If not, you need more scenarios. If introducing more scenarios results in the same resolution, then it's time to sue Microsoft....I'm curious what happened with Mag's study.

[campbell]

Originally Posted by campbell

[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].

The other thread reminds me: I did end up writing an article about floating point arithmetic (not specific to Excel)

https://www.soa.org/News-and-Publica...rithmetic.aspx

[campbell]

https://medium.freecodecamp.com/a-br...s-9498737f5b6c

A Brief History of Random Numbers

well, random & pseudo random. has some good stuff in there. check out the video

[campbell]

hmmm

https://www.google.com/search?q=rand...hrome&ie=UTF-8

https://www.google.com/search?q=flip...hrome&ie=UTF-8

https://www.google.com/search?rlz=1C...67.600GKe0n-iQ

[Meshuga]

How random can you be?

[Rick_G]

Originally Posted by Meshuga

How random can you be?

That is just freaky!!

[campbell]

https://www.reddit.com/r/dataisbeaut...random_number/



https://torvaney.github.io/projects/human-rng

[campbell]

https://arxiv.org/abs/1503.08376

Assessing Excel VBA Suitability for Monte Carlo Simulation
Alexei Botchkarev
(Submitted on 29 Mar 2015)
Monte Carlo (MC) simulation includes a wide range of stochastic techniques used to quantitatively evaluate the behavior of complex systems or processes. Microsoft Excel spreadsheets with Visual Basic for Applications (VBA) software is, arguably, the most commonly employed general purpose tool for MC simulation. Despite the popularity of the Excel in many industries and educational institutions, it has been repeatedly criticized for its flaws and often described as questionable, if not completely unsuitable, for statistical problems. The purpose of this study is to assess suitability of the Excel (specifically its 2010 and 2013 versions) with VBA programming as a tool for MC simulation. The results of the study indicate that Microsoft Excel (versions 2010 and 2013) is a strong Monte Carlo simulation application offering a solid framework of core simulation components including spreadsheets for data input and output, VBA development environment and summary statistics functions. This framework should be complemented with an external high-quality pseudo-random number generator added as a VBA module. A large and diverse category of Excel incidental simulation components that includes statistical distributions, linear and non-linear regression and other statistical, engineering and business functions require execution of due diligence to determine their suitability for a specific MC project.

No! Don't do it!