Running Montecarlo analysis on Google Spreadsheets

Question

A complex Google Spreadsheet has many inputs and one output, and I need to run MonteCarlo analysis on it. In Excel, I would have used a "DYI" MonteCarlo approach by putting formulas like

=norminv(rand(),expected_return,st_deviation)

on the inputs, and having a simple macro with a loop, typically run 1000 times ore more, that trigger a spreadsheet recalculation (equivalent to pressing F9), and record the result of output cell.

On Google Spreadsheets, I can't find a way to trigger a spreadsheet recalculation from google apps scripts.

Is there any way, or a better/smarter architecture to do MonteCarlo analysis in google-spreadsheets (preferably not using add-ins that I don't understand what they are doing with the data, or require unlimited access to all my files)?

Thanks in advance!

score 2 · Answer 1 · answered Apr 18 '20 at 22:05

2

I just developed an add-on that lets you run Monte-Carlo simulations in Google Sheets. Note that it runs all calculations inside Google Sheets (that's why it can be a bit slow) so you don't have to worry about privacy/security issues.

answered Apr 18 '20 at 22:05

lukas1994

1,799
1
13
18

Thank you. Will take a look – Oren Pinsky Apr 19 '20 at 13:11
I've tried the add-on, and it seems that it iterates the input values in some steps. my case is a bit different, I actually need to simply multiple times refresh the sheet and only capture output of one cell. can this be somehow done? – michondr Oct 31 '20 at 10:13
@michondr that's what the add-on should be doing - it's not that flexible though (you can't choose the distribution, ...) if you want more control over your Monte-Carlo simulations you should check out https://causal.app (disclaimer: I'm the founder of Causal) – lukas1994 Nov 01 '20 at 14:03
1

I've tried it, however the point of selecting range of the input cell is not the point for me - I don't actually have any input, there are random functions in the sheet which calculate the output. I managed to get over this by just writing a script which pastes value of the outpus cell to a range on other sheet and then calls SpreadsheetApp.flush() to trigger the random functions and then iterates again – michondr Nov 02 '20 at 15:12

score 0 · Answer 2 · answered May 03 '18 at 09:53

This seems to do the trick (from this link)

/**
 * @OnlyCurrentDoc  Limits the script to only accessing the current spreadsheet.
 */


/**
 * Adds a custom menu with items to show the sidebar and dialog.
 *
 * @param {Object} e The event parameter for a simple onOpen trigger.
 */
function onOpen(e) {
  SpreadsheetApp.getUi()
      .createAddonMenu()
      .addItem('Re-calculate selected cells', 'recalculate')
      .addToUi();
}


/**
 * Force Spreadsheet to re-calculate selected cells
 */
function recalculate(){
  var activeRange = SpreadsheetApp.getActiveRange();
  var originalFormulas = activeRange.getFormulas();
  var originalValues = activeRange.getValues();

  var valuesToEraseFormula = [];
  var valuesToRestoreFormula = [];

  originalFormulas.forEach(function(outerVal, outerIdx){
    valuesToEraseFormula[outerIdx] = [];
    valuesToRestoreFormula[outerIdx] = [];
    outerVal.forEach(function(innerVal, innerIdx){
      if('' === innerVal){
        //The cell doesn't have formula
        valuesToEraseFormula[outerIdx][innerIdx] = originalValues[outerIdx][innerIdx];
        valuesToRestoreFormula[outerIdx][innerIdx] = originalValues[outerIdx][innerIdx];
      }else{
        //The cell has a formula.
        valuesToEraseFormula[outerIdx][innerIdx] = '';
        valuesToRestoreFormula[outerIdx][innerIdx] = originalFormulas[outerIdx][innerIdx];
      }
    })
  })

  activeRange.setValues(valuesToEraseFormula);
  activeRange.setValues(valuesToRestoreFormula);
}


/**
 * Runs when the add-on is installed; calls onOpen() to ensure menu creation and
 * any other initializion work is done immediately.
 *
 * @param {Object} e The event parameter for a simple onInstall trigger.
 */
function onInstall(e) {
  onOpen(e);
}

Running Montecarlo analysis on Google Spreadsheets

2 Answers2