XllMonte

Functions and macros of the XllMonte add-in.

Usage

This add-in performs Monte Carlo simulation in Excel. Instead of creating many cell with random values and using the built-in Excel functions to aggregate those, XllMonte can use one cell that produces random values and aggregate those over the course of a simulation. XllMonte trades space for time.

Action Key  Description
Run Ctrl-Shift-R  Start a simulation. Press the Escape key to stop.
stop Esc  Terminate a simulation.
Step Ctrl-Shift-S  Single step through a simulation.
reseT Ctrl-Shift-T  Prepare a new simulation.

Over the course of a simulation you can collect statistics such as maximum, minimum, mean, and standard deviation. XllMonte does not store any simulation results so it is possible to run as many iterations as you wish.

The Excel message bar on the bottom left displays the number of iterations, the elapsed time, and the iterations per second. The functions MONTE.COUNT and MONTE.ELAPSED can be used to display this information in the spreadsheet.

XllMonte adds the time dimension to your spreadsheet. If you enter =MONTE.MEAN(RAND()) in a cell and start a simulation then every time RAND() is recalculated the cell will be updated with the running average of the calls to RAND().

Random functions

\BROWNIAN Create a handle to multi-dimensional Brownian motion.
BROWNIAN.SAMPLE Return array of correlated Brownian samples at the given times.
CHOLESKY Return lower triangular Cholesky factor.
\RANDOM Return a handle to a random number generator.
RANDOM.BROWNIAN Generate Brownian samples at given times.
RANDOM.NORMAL Generate a normal random variate having given mean and stdev.
RANDOM.SEED Use array of numbers to seed a random number generator.
RANDOM.UNIFORM Generate random variates uniformly in the interval [a, b).

XLL functions

COLLECT Collect cells into a range
DEPENDS Return cell after dependent is calculated.
EVAL Call xlfEvaluate on cell.
THIS Return the contents of the calling cell.
TRACE Alert when a cell is evaluated in a calculation.
XLL.ALERT.LEVEL Set the current alert level using a mask and return the old mask.

XLL macros

REPLACE.EQ.BY.EQ Recalculate all formulas in a workbook. (Ctrl-Shift-F9)
XLL.PASTE.ARGS Paste function using default arguments. (Ctrl-Shift-@)
XLL.PASTE.BASIC Paste a function with default arguments. (Ctrl-Shift-B)

XllMonte functions

MONTE.AVERAGE Calculate the average of cell over a simulation.
MONTE.BUFFER Buffer rows to ouput range from the top.
MONTE.CALCULATION Get or set calculation mode. Default is MONTE_CALCULATE_DOCUMENT.
MONTE.CDF Return the probabilities cell is less than or equal to values.
MONTE.CONDITIONAL Return range when condition is true, otherwise do nothing.
MONTE.COUNT Return current iteration count starting from 1.
MONTE.ELAPSED Return elapsed time in seconds since the start of the simulation.
MONTE.FIRST Return cell if the first simulation and FALSE otherwise.
MONTE.MAX Return the maximum values of a cell over a simulation.
MONTE.MEAN Calculate the mean of a cell over a simulation.
MONTE.MIN Store the minimum values of a cell over a simulation.
MONTE.PAUSE Pause simulation if condition is true.
MONTE.RANGE Return rows of a range during a simulation.
MONTE.RECOUNT Return a two cell array of condition and current recount.
MONTE.REMAINING Time in seconds until XllMonte stops running.
MONTE.RUNNING Return TRUE if the simulation is running and FALSE otherwise.
MONTE.STDEV Calculate the mean and standard deviation of a cell over a simulation.
MONTE.STOP Stop the simulation if condition is true.
MONTE.SUM Calculate the running sum of a cell over a simulation.
MONTE.UPDATE Set the update interval for the simulation.
MONTE.WHEN When condition is true return range, otherwise do nothing.

XllMonte macros

MONTE.RESET Prepare for a new simulation using Ctrl-Shift-T.
MONTE.RUN Run a simulation using Ctrl-Shift-R.
MONTE.STEP Single step a simulation using Ctrl-Shift-S.