Functions and macros of the XllMonte add-in.
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().
| \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). |
| 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. |
| 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. |