Monte Carlo analysis in Excel
30/11/08 19:48 Filed in: Analysis
Monte-Carlo analysis is one of those tools that I
have always found useful to keep in my pocket as an
engineer. I like to find an elegant solution to
problems limiting the number of variables and
amount of analysis that has to be done. Sometimes a
brute force approach can not however be avoided and
Monte Carlo analysis is a very effective way of
solving a complex risk or probability problem.
Because I don't use it much specialist tools such as @risk are not really an option for me. The basic version of Excel is however more than capable of performing Monte-Carlo analysis with relative simplicity.
The first stage is to build your model with the usual input and output parameters using the same format as you would normally do. Once the is done create a new worksheet for the input parameters that you want to use in he Monte-Carlo analysis. To generate the random numbers to put into the Monte-Carlo analysis you will need to use a Rand() function for each input parameter you want to consider. You can then use Excel's built in distribution functions to generate your output value or you can create your own - I will consider some of the different distribution functions in later posts.
If you have done this correctly, every time you amend your spreadsheet now the values should change on this worksheet. Now link the input values on your model worksheet to the values on the inputs worksheet. The entire model should now change each time values are adjusted on the spreadsheet. At this point it is worth taking time to check how your model is performing. Just pressing the delete key on an empty cell should run one Monte-Carlo step. It is worth doing this a few times because Monte-Carlo analyses often test the limit of a spreadsheet with combinations of very high and very low values.
It is possible to manually run a Monte-Carlo analysis manually with the spreadsheet in this form. The power of this solution is however when it is automated, and to do that we can make use of a Macro.
First create a new worksheet to hold the outputs from the model. Now create a new Macro. The typical code that should be used for the Macro is something like:
Sub MonteCarlo()
With Application
.Calculation = xlManual
End With
For Counter = 1 To 500
Sheets("Model").Select
Calculate
Output1 = Range("L3").Value
Output2 = Range("H17").Value
Set curCell = Worksheets("Results").Cells(Counter, 2)
curCell.Value = Counter
Set curCell = Worksheets("Results").Cells(Counter, 3)
curCell.Value = Output1
Set curCell = Worksheets("Results").Cells(Counter, 4)
curCell.Value = Output2
Next Counter
With Application
.Calculation = xlAutomatic
End With
End Sub
This Macro runs the Monte-Carlo analysis 500 times. The results from the analysis are taken from cells L3 and H17 from the worksheet Model. They are then put into columns 3 and 4 of the worksheet Results.
Post processing of the output data should be done in a separate workbook. If this is not done then re-running the Monte-Carlo analysis will take much longer as the post processing will be done for each step of the Monte-Carlo analysis.
Because I don't use it much specialist tools such as @risk are not really an option for me. The basic version of Excel is however more than capable of performing Monte-Carlo analysis with relative simplicity.
The first stage is to build your model with the usual input and output parameters using the same format as you would normally do. Once the is done create a new worksheet for the input parameters that you want to use in he Monte-Carlo analysis. To generate the random numbers to put into the Monte-Carlo analysis you will need to use a Rand() function for each input parameter you want to consider. You can then use Excel's built in distribution functions to generate your output value or you can create your own - I will consider some of the different distribution functions in later posts.
If you have done this correctly, every time you amend your spreadsheet now the values should change on this worksheet. Now link the input values on your model worksheet to the values on the inputs worksheet. The entire model should now change each time values are adjusted on the spreadsheet. At this point it is worth taking time to check how your model is performing. Just pressing the delete key on an empty cell should run one Monte-Carlo step. It is worth doing this a few times because Monte-Carlo analyses often test the limit of a spreadsheet with combinations of very high and very low values.
It is possible to manually run a Monte-Carlo analysis manually with the spreadsheet in this form. The power of this solution is however when it is automated, and to do that we can make use of a Macro.
First create a new worksheet to hold the outputs from the model. Now create a new Macro. The typical code that should be used for the Macro is something like:
Sub MonteCarlo()
With Application
.Calculation = xlManual
End With
For Counter = 1 To 500
Sheets("Model").Select
Calculate
Output1 = Range("L3").Value
Output2 = Range("H17").Value
Set curCell = Worksheets("Results").Cells(Counter, 2)
curCell.Value = Counter
Set curCell = Worksheets("Results").Cells(Counter, 3)
curCell.Value = Output1
Set curCell = Worksheets("Results").Cells(Counter, 4)
curCell.Value = Output2
Next Counter
With Application
.Calculation = xlAutomatic
End With
End Sub
This Macro runs the Monte-Carlo analysis 500 times. The results from the analysis are taken from cells L3 and H17 from the worksheet Model. They are then put into columns 3 and 4 of the worksheet Results.
Post processing of the output data should be done in a separate workbook. If this is not done then re-running the Monte-Carlo analysis will take much longer as the post processing will be done for each step of the Monte-Carlo analysis.
Applescript in iWork
28/11/08 06:57 Filed in: Applescript
Applescript has the potential to significantly
improve many work flows. Like all programming or
scripting languages it does have a learning curve,
but because of the simple structures and plain
language the curve is one of the easiest languages
to learn. The real power of the language is its
ability to interface between different programmes
allowing data to be transferred from one
application to another. It is also a quick way of
creating similar tools to Macros that are used in
Microsoft Office.
It comes as a surprise that one of Apples flagship applications, iWork, does not fully support Applescript. Neither Pages nor Numbers have applescript dictionaries associated with them, although Keynote does have a dictionary. This means that Pages and Numbers do not have publicly documented specific Applescript abilities. The good news is that this doesn't mean that Applescript can't be used with this applications.
Using the 'application' System Events you can easily control keystrokes, and occasionally buttons within most applications. To demonstrate how this is done, the following simple script is used to insert a standard reference into the active Pages document. Applescript Studio can then be used to create an interface which allows different references to be inserted all at the click of a button.
set the_text to "Hoek-Brown failure criterion - 2002 edition, 2002, E.Hoek; C. Carranza Torres; B. Corkum, Proc. NARMS-TAC Conference, Toronto, 2002, 1, 267-273, www.rocscience.com/hoek/references/H2002.pdf"
set the clipboard to the_text
tell application "System Events"
tell application "Pages" to activate
tell process "Pages" to keystroke "v" using command down
end tell
I find this script a real timesaver because I don't have to look up references every time I need to insert them into a document. All it takes is a simple click of a button.
It comes as a surprise that one of Apples flagship applications, iWork, does not fully support Applescript. Neither Pages nor Numbers have applescript dictionaries associated with them, although Keynote does have a dictionary. This means that Pages and Numbers do not have publicly documented specific Applescript abilities. The good news is that this doesn't mean that Applescript can't be used with this applications.
Using the 'application' System Events you can easily control keystrokes, and occasionally buttons within most applications. To demonstrate how this is done, the following simple script is used to insert a standard reference into the active Pages document. Applescript Studio can then be used to create an interface which allows different references to be inserted all at the click of a button.
set the_text to "Hoek-Brown failure criterion - 2002 edition, 2002, E.Hoek; C. Carranza Torres; B. Corkum, Proc. NARMS-TAC Conference, Toronto, 2002, 1, 267-273, www.rocscience.com/hoek/references/H2002.pdf"
set the clipboard to the_text
tell application "System Events"
tell application "Pages" to activate
tell process "Pages" to keystroke "v" using command down
end tell
I find this script a real timesaver because I don't have to look up references every time I need to insert them into a document. All it takes is a simple click of a button.