RunRandom: Quasi Monte Carlo in Excel and VBA
RunRandom VBA and Excel spreadsheet addin functions generate multidimensional
quasi random vectors. Components of these vectors  called quasi random numbers 
can be used in various Monte Carlo applications, such as:
 Evaluation of multidimensional integrals.
 Financial evaluation of securities.
 Multivariate stochastic optimization.
 Stochastic differential equations.
 System simulation.
In many Monte Carlo
and simulation applications, using quasi random numbers
 instead of the usual pseudo random numbers provided by C, VBA, Excel, and other programming languages 
provide  on the average  faster convergence.
This means that you can get the same result in less time with less
computation. Conversely, for a set simulation time, using quasirandom vectors provide
 on the average  more accurate answers for Monte Carlo applications.
See, for example:
 Hoogland, Jiri, Fred James, and Ronald Kleiss.
QuasiMonte Carlo, discrepancies and error estimates.
Monte Carlo and QuasiMonte Carlo Methods 1996. Springer New York, 1998. 266276.

Ramamoorthy, Subramanian, et al.
"Lowdiscrepancy curves and efficient coverage of space."
Algorithmic Foundation of Robotics VII. Springer Berlin Heidelberg, 2008. 203218.
RunRandom is based on a very fast precomputed version of the Richtmeyer scheme.
It requires Microsoft Excel 2010 and later (64bit installations preferred).
RunRandom generates quasirandom vectors up to 5,000 dimensions.
Integration with Excel and VBA. RunRandom Excel formulas follow the same conventions
as the Excel =RAND() function and the VBA RND function.
The difference is that instead of an empty argument, the RunRandom function calls specify
a dimension. For example, suppose we want to specify a random vector in one row and two columns.
Using the standard Excel rand() function, enter formulas in cells A1 and B1:
For RunRandom, we use an array formula on the spreadsheet range A1:B1
(use ControlShiftEnter to enter the array formula):
 A  B 
1 
{=RRAND(1, 2)}

{=RRAND(1, 2)} 
The array formula {=RRAND(1, 2)} specifies a 2dimensional quasirandom vector of one row and two columns.
Similarly, the array formula {=rrand(2,3)} specifies a 6dimensional quasirandom vector
of that can be used to fill a region of 2 rows and 3 columns.
Note that all RunRandom functions are
callable from other VBA modules as well.
The Monte Carlo and and ComputerGenerated "Random"
Numbers How can one assess the future? One way is
to simulate a large number of alternative future scenarios by computer and
seeing what the most likely scenario is by averaging all future scenarios
together. This is the idea behind Monte Carlo Simulation
methods.
Computer generated numbers are not really random but only
appear so when subjected to certain statistical tests.
Quasi random numbers (components of quasi random vectors) are based on properties of prime numbers and
prime polynomial fields. They are
also called low discrepancy sequences because
when they are generated, they fill in multidimensional rectangular regions evenly and
uniformly. Pseudo random
numbers  those provided by builtin functions in C, VBA, Excel,
and other programming languages 
do not have this multidimensional "low discrepancy" property. In multiple dimensions, sets of
pseudo random numbers leave gaps and holes in multiple dimensional space.
Examples of Quasi Monte Carlo Quasi Monte Carlo methods use
samples of quasi random vectors instead of samples of sets of pseudo random numbers. For example:
 To evaluate an Ndimensional multidimensional integral:
 Generate an Ndimensional quasi random vector.
 Use the vector components as arguments to an Ndimensional multivariate integrand function.
 Evaluate the Ndimensional multivariate integrand function.
 Repeat with another Ndimensional quasi random vector.
 Compute the average  over integrand function evaluations  for the final result.
 To evaluate a financial security over a time period T:
 Decompose the time period T into N increments of Δt duration.
 Generate an Ndimensional quasi random vector.
 Use the vector components to generate an interest rate or market "scenario" for each Δt.
 Repeat with another Ndimensional quasi random vector.
 Compute the Average  over all scenarios  for the final result.
For example, suppose we want to compute the 2dimensional integral
via Monte Carlo integration.
(This integral is evaluated via calculus as exp(2)2*exp(1)+1 = 0.399576401... .
When comparing the performance of
100 quasi random vectors (x,y) with 100 pseudo random pairs,
one simulation run showed that the relative error
for quasi Monte Carlo was about 42.5% of the pseudo Monte Carlo error (3.06% vs. 7.20%); for
500 random vectors (x,y),
the relative error for quasi Monte Carlo error was about 6.5% of the pseudo Monte Carlo error (0.12% vs. 1.84%). The details are summarized in this
spreadsheet; the spreadsheet formulas
are shown here ).
Comparison of Quasi Random Vectors with Sets of Pseudo Random Numbers
Here is an example that compares a set of 100 two dimensional quasi random vectors generated with
{=rrand(1,2)} (left chart)
with a set of 100 pairs of pseudo random numbers generated with
=rand() (right chart):
100 point Excel scatter plot of dimensions 1x2:(left) quasi random; (right) pseudo random
The following charts compare a set of 500 twodimensional quasi random vectors generated with
{=rrand(1,2)} (left chart) with a set of 500 pairs of pseudo random numbers generated with
=rand() (right chart):
500 point Excel scatter plot of two dimensions 1x2:(left) quasi random; (right) pseudo random
Note that gaps or holes are distributed more uniformly across space with the twodimensional quasi random vectors than with pairs of pseudo random numbers. This is one reason why many theoretical and empirical results indicate that simulations based on quasi random vectors converge an
order of magnitude faster than simulations based on sets of pseudo random
numbers.
In multiple dimensional space, twodimensional scatter plots (projections) show
how quasi random numbers fill in space with different patterns.
Projections in two dimensions. 500 point Excel scatter plot of dimensions (left to right): 1x3; 1x4; 1x27.
Projections in two dimensions. 500 point Excel scatter plot of dimensions (left to right): 2x3; 5x27; 8x23.
RunRandom Instructions To generate a uniformly distributed random vector that updates with spreadsheet calculation:
 Select a region of size myrows x mycols.
 Insert the RRAND function as an array formula (ControlShiftEnter).
The array function =RRAND(myrows, mycols)
returns a uniformly distributed (between 0 and 1)
quasirandom vector of dimension myrows * mycols.
RRAND is volatile  like Excel's rand() function:
RRAND computes a new random vector at every spreadsheet recalculation.
To generate a uniformly distributed random vector that does not update with spreadsheet calculation:
 Select a region of size myrows x mycols.
 Insert the RRANDN function as an array formula (ControlShiftEnter).
The array function =RRANDN(myrows, mycols) returns uniformly distributed (between 0 and 1) quasirandom vector
of dimension myrows * mycols.
RRANDN is the "nonvolatile" version of RRAND: RRANDN does not automatically recalculate.
To generate a zeromean unitvariance normally distributed random vector that does updates
with spreadsheet calculation:
 Select a region of size myrows x mycols.
 Insert the RRNORM function as an array formula (ControlShiftEnter).
The array function RRNORM(myrows,mycols) returns standard (0 mean unit variance) normally distributed
quasirandom vector of dimension myrows * mycols.
RRNORM is volatile  like Excel's rand() function:
RRNORM computes a new random vector at every spreadsheet recalculation.
To generate a zeromean unitvariance normally distributed random vector that does not update
with spreadsheet calculation:
 Select a region of size myrows x mycols.
 Insert the RRNORMN function as an array formula (ControlShiftEnter).
The array function RRNORMN(myrows,mycols) returns standard (0 mean unit variance) normally distributed
quasirandom vector of dimension myrows * mycols.
RRNORMN is the "nonvolatile" version of RRNORM: RRNORMN does not automatically recalculate.
The following charts compare a set of 500 two dimensional normally distributed (zero mean unit variance) quasi random vectors generated with
{=rrnorm(1,2)} (left chart) with a set of 500 pairs of normally distributed (zero mean unit variance) pseudo random numbers generated with
=NORMSINV(RAND()) (right chart):
500 point Scatter plot of dimensions 1x2:(left) standard normal quasi random; (right) standard normal pseudo random
To use the RunRandom functions from your own VBA Module:
 Open your project module with the Excel VBA tools.
 Navigate on the VBA Module Ribbon to: Tools=>References...
 Search for "RunRandom" on the list of "Available References."
 Check the Box next to "RunRandom" .
 Click OK.
You can now call the RunRandom functions from your own VBA macro.
NOTE: For 64 bit versions of Excel, the total number of quasiRandom multidimensional vectors
(independent of dimension) before recycling is
approximately 2^631 ~ 9 x 10^18.