Making a Markov Model Probabilistic

Abstract

The deterministic model outlined in the previous chapter is often used as an important step towards constructing a stochastic model. Constructing the deterministic model and conducting one-way sensitivity analyses can aid the analyst in checking the validity of the underlying model. Given the importance of checking that a model is performing as expected, it is good practice to build your models with the ability to operate in either deterministic or stochastic mode.

Appendix

10.1.1 Appendix 10.1: Optimising Visual Basic Macros in Excel

The example given in the text is designed to step a novice user through the content of a basic macro. This macro (or something like it) is based on the text that you would get if you ask Excel to record a small series of cut and paste tasks, and then insert commands to tell Excel to repeat this process.

It is possible, and recommended, that you optimise your macros as much as possible. One area where things can go wrong is when we use cutting and pasting. When Excel has to use the operating system’s clipboard, it means that it becomes hard to do other normal tasks whilst your model runs ‘in the background’.

For this reason, it can be easier to make greater use of names in Excel. If we use names that refer to the cells with our results, and other names to refer to the cells into which we want to copy these results, we can avoid using the clipboard.

Our original macro appears below:

Box A.1: Sample Macro for Probabilistic Sensitivity Analysis

1	Sub PSAruns ()
2
3	Dim NumRuns As Integer = 10000
4
5	Application.ScreenUpdating = False
6	Application.Goto Reference:="opt_modeltype"
7	ActiveCell.FormulaR1C1 = "Stochastic"
8
9	Application.Goto Reference:="psa_source"
10	Selection.Copy
11
12	Application.Goto Reference:="psa_target“
13	For counter = 1 to NumRuns
14	ActiveCell.Offset(1, 0).Select
15	Selection.PasteSpecial Paste:=xlPasteValues, _
16	Operation:=xlNone, SkipBlanks:=False, Transpose:=False
17	Next
18
19	Application.Goto Reference:="opt_modeltype"
20	ActiveCell.FormulaR1C1 = "Deterministic"
21	Application.ScreenUpdating = True
22	End Sub

Lines 6–7 change the value of the cell referred to as “opt_modeltype” to the value “Stochastic”, whilst Lines 19–20 change in back to “Deterministic”. Another way of doing this would be to refer to the formula attached to the name directly, with the following used at Lines 6 and 19:

Range(“opt_modeltype”).Formula = “Stochastic”

Range(“opt_modeltype”).Formula = “Deterministic”

In Lines 9–17, the macro copies a range that includes our results and then pastes this 10,000 times. If we want to do this a little quicker, we could avoid copying the initial results. In Visual Basic, we can refer to a range relative to a cell using the Offset property. For instance, three rows down, and two rows across would mean identifying our initial cell and stating “.Offset(3, 2)”. In the model, the results in run ‘counter’ of the macro will be entered ‘counter’ rows down (and no rows across) from “psa_target”. So here, we are interested in:

Range(“psa_target”).Offset(counter, 0)

As we want to change the value in these cells, we add “.Value” to the end of this and refer to the values contained in cells referenced by “psa_source”. So, we can replace Lines 9–10 and 14–16 as:

Range(“psa_target”).Offset(counter, 0).Value = Range(“psa_source”). Value

If we make our changes, the macro is a little shorter and quite a bit quicker (Box 10.2). Note that Line 9 here continues over two lines, with “&_” indicating to Visual Basic to read both Line 9 and Line 10 as a single instruction.

Box A.2: Sample Macro for Probabilistic Sensitivity Analysis: Improved Efficiency

1	Sub PSAruns ()
2
3	Dim NumRuns As Integer = 10000
4
5	Application.ScreenUpdating = False
6	Range("opt_modeltype").Formula = "Stochastic"
7
8	For counter = 1 to NumRuns
9	Range("psa_target").Offset(counter, 0).Value = &_
10	Range("psa_source").Value
11	Next
12
13	Range("opt_modeltype").Formula = "Deterministic"
14	Application.ScreenUpdating = True
15
16	End Sub

When we run macros, we normally turn off screen updating in Excel. This means that the computer does not spend resources refreshing the screen that could be spent in calculation. However, it can be comforting for a user to know that the computer is ‘busy’ rather than ‘stuck’, and the status bar (at the bottom of the Excel window) can be used for this purpose.

As we do not normally need the macro to update the status every period, it is worth deciding how often you want this to happen. This might be every 100 iterations (i.e. each 1 %), as in Box 10.3 below. The commands in Lines 12–15 establish whether the current value of counter is perfectly divisible by 100 (i.e. having a remainder or “modulus” of 0) and, if so, setting the value of the Status Bar to display progress in percentage terms. Having a status update of this type also gives a user a better indication as to when the model is likely to finish running.

Box A.3: Sample Macro for Probabilistic Sensitivity Analysis: Feedback in Status Bar

1	Sub PSAruns ()
2
3	Dim NumRuns As Integer = 10000
4
5	Application.ScreenUpdating = False
6	Range("opt_modeltype").Formula = "Stochastic"
7
8	For counter = 1 to NumRuns
9	Range("psa_target").Offset(counter, 0).Value = &_
10	Range("psa_source").Value
11
12	If (counter Mod 100 = 0) Then
13	Application.StatusBar = &_
14	"% complete: " & (counter/NumRuns)*100
15	End If
16
17	Next
18
19	Range("opt_modeltype").Formula = "Deterministic"
20	Application.ScreenUpdating = True
21
22	End Sub