DiscoverSim™ Engine and Excel Formula Interpreter
DiscoverSim uses Excel for the user interface but random number generation, distribution fitting parameter estimation, accelerated simulation and optimization are performed using the Gauss Mathematical and Statistical Engine by Aptech (www.aptech.com). GAUSS is a fast matrix programming language widely used by econometricians and financial analysts since 1984. Communication between Excel and the GAUSS Engine is performed with Mercury by Econotron Software (www.econotron.com). Econotron Software has developed interprocess communication tools for a number of applications based on the Gauss Engine.
Accelerated mode simulation and optimization use DiscoverSim’s Excel Formula Interpreter. This is based on a Common Object Interface (COI) by Econotron Software to implement an Excel spreadsheet in GAUSS. The entire Excel workbook is represented as a GAUSS procedure using COI, with speed increases up to 40 times.
The interpreter supports the majority of all Excel numerical functions including those in the Analysis Toolpak, but currently does not support the following:
- Excel 2007 CUBE functions
- Excel 2010 AGGREGATE, NETWORKDAYS.INTL, WORKDAYS.INTL
- CONVERT, GETPIVOTDATA, HYPERLINK
- ISBLANK, ISTEXT, ISNONTEXT, ISLOGICAL, ISREF
- PowerPivot Data Analysis Expressions (DAX) Functions such as ISO.CEILING
- Third party vba functions
Functions supported but with limitations:
- Excel functions that return an array or non-single value (e.g. LINEST, LOGEST, MINVERSE, MDETERM, MMULT, STANDARDIZE, TRANSPOSE and TREND) are limited to intermediate calculations. The final output result must be a scalar.
- Excel functions that return a string value are limited to intermediate calculations. The final result must be a scalar.
If the DiscoverSim interpreter sees a function that it does not support, you will be prompted to use Excel’s Native mode, where the transfer function, Y = f(X), calculations are performed in Excel. Note that in this native mode, DiscoverSim’s random input distributions are still generated with GAUSS.
The Excel Formula Interpreter can be validated using Accelerated Mode - Run Validation using Native Excel. This runs a validation test to compare Accelerated Mode versus Native Excel. Each output is assessed by comparing the simulation means. If the worst case relative difference is less than or equal to 1e-10%, the test passes and the status is “Success”. If the relative difference is between 1e-10% to 1e-4%, the status is “Good”, and if greater than or equal to 1e-4%, the test status is “Poor”.
DiscoverSim can quickly perform millions of simulation replications, but is limited by system memory. If the user chooses to store the simulation data in Excel, then Excel’s row and column limitations will also apply (Excel 2007, 2010: 1,048,576 rows and 16,384 columns).
Our CTO and Co-Founder, John Noguera, regularly hosts free Web Demos featuring SigmaXL and DiscoverSim
Click here to view some now!
Ph: 1.888.SigmaXL (744.6295)