RunRandom: Quasi Monte Carlo in Excel and VBA

RunRandom VBA and Excel spreadsheet add-in functions generate multi-dimensional quasi random vectors.  Components of these vectors -- called quasi random numbers -- can be used in various Monte Carlo applications, such as:

• Evaluation of multi-dimensional integrals.
• Financial evaluation of securities.
• Multivariate stochastic optimization.
• Stochastic differential equations.
• System simulation.

• Hoogland, Jiri, Fred James, and Ronald Kleiss. Quasi-Monte Carlo, discrepancies and error estimates. Monte Carlo and Quasi-Monte Carlo Methods 1996. Springer New York, 1998. 266-276.
• Ramamoorthy, Subramanian, et al. "Low-discrepancy curves and efficient coverage of space." Algorithmic Foundation of Robotics VII. Springer Berlin Heidelberg, 2008. 203-218.

RunRandom is based on a very fast pre-computed version of the Richtmeyer scheme. It requires Microsoft Excel 2010 and later (64-bit installations preferred). RunRandom generates quasi-random 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:

	A	B
1	=RAND()	=RAND()

For RunRandom, we use an array formula on the spreadsheet range A1:B1 (use Control-Shift-Enter to enter the array formula):

	A	B
1	{=RRAND(1, 2)}	{=RRAND(1, 2)}

The array formula {=RRAND(1, 2)} specifies a 2-dimensional quasi-random vector of one row and two columns. Similarly, the array formula {=rrand(2,3)} specifies a 6-dimensional quasi-random 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 Computer-Generated "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. 

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 N-dimensional multi-dimensional integral:
  1. Generate an N-dimensional quasi random vector.
  2. Use the vector components as arguments to an N-dimensional multivariate integrand function.
  3. Evaluate the N-dimensional multivariate integrand function.
  4. Repeat with another N-dimensional quasi random vector.
  5. Compute the average -- over integrand function evaluations -- for the final result.
• To evaluate a financial security over a time period T:
  1. Decompose the time period T into N increments of Δt duration.
  2. Generate an N-dimensional quasi random vector.
  3. Use the vector components to generate an interest rate or market "scenario" for each Δt.
  4. Repeat with another N-dimensional quasi random vector.
  5. Compute the Average -- over all scenarios -- for the final result.

For example, suppose we want to compute the 2-dimensional 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 two-dimensional 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 two-dimensional 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, two-dimensional 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:

1. Select a region of size myrows x mycols.
2. Insert the RRAND function as an array formula (Control-Shift-Enter).

   The array function =RRAND(myrows, mycols) returns a uniformly distributed (between 0 and 1) quasi-random vector of dimension myrows * mycols.

   RRAND is volatile - like Excel's rand() function: RRAND computes a new random vector at every spreadsheet recalculation.

1. Select a region of size myrows x mycols.
2. Insert the RRANDN function as an array formula (Control-Shift-Enter).

   The array function =RRANDN(myrows, mycols) returns uniformly distributed (between 0 and 1) quasi-random vector of dimension myrows * mycols.

   RRANDN is the "non-volatile" version of RRAND: RRANDN does not automatically recalculate.

1. Select a region of size myrows x mycols.
2. Insert the RRNORM function as an array formula (Control-Shift-Enter).

   The array function RRNORM(myrows,mycols) returns standard (0 mean unit variance) normally distributed quasi-random vector of dimension myrows * mycols. RRNORM is volatile - like Excel's rand() function:

   RRNORM computes a new random vector at every spreadsheet recalculation.

1. Select a region of size myrows x mycols.
2. Insert the RRNORMN function as an array formula (Control-Shift-Enter).

   The array function RRNORMN(myrows,mycols) returns standard (0 mean unit variance) normally distributed quasi-random vector of dimension myrows * mycols.

   RRNORMN is the "non-volatile" version of RRNORM: RRNORMN does not automatically recalculate.

To use the RunRandom functions from your own VBA Module:

1. Open your project module with the Excel VBA tools.
2. Navigate on the VBA Module Ribbon to: Tools=>References...
3. Search for "RunRandom" on the list of "Available References."
4. Check the Box next to "RunRandom" .
5. Click OK. You can now call the RunRandom functions from your own VBA macro.