Embedding (Retrofitting) Monte Carlo simulation into Spreadsheets

[Mark Neal]

I am attempting to help modify a spreadsheet model to provide monte carlo simulation output using Yasaiw, for third party use by less experienced Excel users, possibly with an endpoint of an online tool.It seems this might be an approach of general interest to problem practitioners who are not VBA wizards if it works, so the journey begins thus:

My steps are:

• 1.Modify the spreadsheet for scenarios and input + output variables (as you would expect for any Yasai model, so easily done) 

• 2.Include a "Command Button" for launching Yasai. 

I have this working ok (Example instructions for replicating this part http://www.excel-easy.com/vba/create-a-macro.html)

VBA code example

Private Sub CommandButton1_Click()
  ' Message box to provide feedback something is happening, obviously optional
  MsgBox "I'm going to do me some simulation baby!   ", vbExclamation
  ' Call to Yasai
  openMenu
End Sub

One trick to make sure it works is to ensure that your workbook knows how to find tthe YASAI sub called openMenu. To do this, you need to add a reference in the VBA of your workbook, ie go to Tools, References. This process is explained  at http://peltiertech.com/Excel/SolverVBA.html where solver is the addin that needs a reference.

Keep in mind Yasia needs to be installed as an Addin on the computer where it is running. This is probably not an optimal approach for portability. I think it might be better to copy the relevant sheets of your model into a Yasai Worksheet, so it is all there together. I haven't followed this through yet, but to start with, renaming Yasaiw.xlam as yasai.xlsm is probably a good starting point, followed by copying re

• 3. Modify Yasai to simulate with defaults (ie no user feedback required with windows)

I'm not a VBA genius. I'll take suggestions on how to do this, or have a crack myself later.

•  4. Present results

This requires some thought. If you have a "cover page" where you launch and view results, it is hard to have a graph there (for example) that references the results sheet which is not populated (and doesn't exist) until the simulation is carried out.

Option one: Use graphs function of Yasai, and send the user there post simulation for viewing?

Option two: Have simulation data written to an existing sheet, which is referred to by the results graph which already exists.

Any comments on these approaches or other ideas?

• 5. Online tool

In an ideal world you magically hit a button, and your excel sheet with VBA becomes an online tool. It seems real life is not so convenient.

Office online doesn't allow macros or VBA to be used, and it seems it isn't on the agenda fro the medium term.

http://community.office365.com/en-us/w/officeapps/excel-workbooks-with-macros-do-not-open-in-excel-online.aspx

http://blogs.office.com/2014/04/14/weve-updated-excel-online-whats-new-in-april-2014/

There are excel converters that sound like they are fine for excel formulas, but probably not for VBA eg http://www.spreadsheetconverter.com/

Another option is convert the excel to an executable (eg http://www.exceltoexe.com/), and then have a website that refers to the executable (located on a server), but it seems like normal security settings could prevent that. (Also, distribution of an executable doesn't help much, as Excel is still required on the users computer.)

It also sounds like other less direct routes are troublesome for even people that know what they are doing.

http://stackoverflow.com/questions/7818987/using-excel-spreadsheets-with-macros-in-backend-of-web-application

Any other thoughts on this?

I'll post any exciting updates.

Cheers,

M

[Jonathan Eckstein]

Mark --

I would suggest that you have your button set the YASAI parameters set
by the frmSimMenu form (but do not call openMenu) and then immediately
display the frmProgressBar form. That will launch you right into the
simulation automatically.

I believe that if you add a spreadsheet object and then change yasai.xla
to yasai.xls, it will become a macro-containing workbook rather than an
add-in (or maybe the other order, I'm not sure). It can then be
distribued just like a spreadsheet.

Jonathan

On 11/12/2014 9:44 PM, Mark Neal wrote:
> I am attempting to help modify a spreadsheet model to provide monte
> carlo simulation output using Yasaiw, for third party use by less
> experienced Excel users, possibly with an endpoint of an online tool.It
> seems this might be an approach of general interest to problem
> practitioners who are not VBA wizards if it works, so the journey begins
> thus:
>
> My steps are:
>

> * 1.Modify the spreadsheet for scenarios and input + output variables

> (as you would expect for any Yasai model, so easily done)
>

> * 2.Include a "Command Button" for launching Yasai.

>
> I have this working ok (Example instructions for replicating this
> part http://www.excel-easy.com/vba/create-a-macro.html)
>
> VBA code example
>
> Private Sub CommandButton1_Click()
> ' Message box to provide feedback something is happening,
> obviously optional
> MsgBox "I'm going to do me some simulation baby! ", vbExclamation
> ' Call to Yasai
> openMenu
> End Sub
>
>
> One trick to make sure it works is to ensure that your workbook knows
> how to find tthe YASAI sub called openMenu. To do this, you need to add
> a reference in the VBA of your workbook, ie go to Tools, References.
> This process is explained
> at http://peltiertech.com/Excel/SolverVBA.html where solver is the
> addin that needs a reference.
>
> Keep in mind Yasia needs to be installed as an Addin on the computer
> where it is running. This is probably not an optimal approach for
> portability. I think it might be better to copy the relevant sheets of
> your model into a Yasai Worksheet, so it is all there together. I
> haven't followed this through yet, but to start with, renaming
> Yasaiw.xlam as yasai.xlsm is probably a good starting point, followed by
> copying re
>

> * 3. Modify Yasai to simulate with defaults (ie no user feedback

> required with windows)
>
> I'm not a VBA genius. I'll take suggestions on how to do this, or have a
> crack myself later.
>

> * 4. Present results

>
> This requires some thought. If you have a "cover page" where you launch
> and view results, it is hard to have a graph there (for example) that
> references the results sheet which is not populated (and doesn't exist)
> until the simulation is carried out.
> Option one: Use graphs function of Yasai, and send the user there post
> simulation for viewing?
> Option two: Have simulation data written to an existing sheet, which is
> referred to by the results graph which already exists.
> Any comments on these approaches or other ideas?
>

> * 5. Online tool

>
> In an ideal world you magically hit a button, and your excel sheet with
> VBA becomes an online tool. It seems real life is not so convenient.
>
> Office online doesn't allow macros or VBA to be used, and it seems it
> isn't on the agenda fro the medium term.
> http://community.office365.com/en-us/w/officeapps/excel-workbooks-with-macros-do-not-open-in-excel-online.aspx
> http://blogs.office.com/2014/04/14/weve-updated-excel-online-whats-new-in-april-2014/
>
> There are excel converters that sound like they are fine for excel
> formulas, but probably not for VBA eg http://www.spreadsheetconverter.com/
>
> Another option is convert the excel to an executable (eg
> http://www.exceltoexe.com/), and then have a website that refers to the
> executable (located on a server), but it seems like normal security
> settings could prevent that. (Also, distribution of an executable
> doesn't help much, as Excel is still required on the users computer.)
>
> It also sounds like other less direct routes are troublesome for even
> people that know what they are doing.
> http://stackoverflow.com/questions/7818987/using-excel-spreadsheets-with-macros-in-backend-of-web-application
>
> Any other thoughts on this?
>
> I'll post any exciting updates.
>
> Cheers,
> M
>

> --
> You received this message because you are subscribed to the Google
> Groups "yasai-simulation" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to yasai-simulati...@googlegroups.com
> <mailto:yasai-simulati...@googlegroups.com>.
> For more options, visit https://groups.google.com/d/optout.