Tuesday, June 27, 2017

Parameter sweep in any Excel calculation

For the past few years I have dabbled a lot (and I mean a lot) in complex Excel-based quantitative finance. As a result, I would like to share a neat little trick that can be easily applied to any Excel spreadsheet.

Suppose you have a spreadsheet that does some sophisticated calculation for you (say, pricing an autocallable basket option using a Monte-Carlo simulation of a Black-Scholes model). And yes, it works; you change the input parameters and you observe the corresponding change in the output.

Now what you need is the parameter sweep analysis, i.e. you need to determine how your output changes as you vary one or more input parameters - for example, you may want to run a convergence test to see how your resulting price changes as you vary the number of Monte-Carlo simulation paths,

Of course you can do this manually, doing the simple routine "change input, run simulation, copy-paste results, repeat". But for anything more than 4-5 simulation runs his would be painfully slow and dangerously prone to error.

What's worse, if a single simulation run takes around several minutes, you'll be stuck between the need to just stare at your computer doing nothing else (and letting your productivity go to waste) or the temptation to attempt something different in the meantime (and inviting all sorts of multitasking-related errors like "aw, snap, looks like I've run this one twice, and heck, have I pasted this number to the right place?")


This is where a simple VBA macro comes to help. As simple as this:
Sub analysis1()

 Dim in1, out1, out2, out3, here As Object
 Set in1 = Worksheets("Calc").Range("Input1")
 Set out1 = Worksheets("Calc").Range("Output1")
 Set out2 = Worksheets("Calc").Range("Output2")
 Set out3 = Worksheets("Calc").Range("Output3")

 Set here = Worksheets("Report").Range("Out1D")

 While here <> Empty
  in1.Value = here.Value

  Worksheets("Calc").Calculate  ' (or do whatever you need to do to run simulation -- e.g. call some other macro)
  here.Offset(0, 1).Value = out1.Value
  here.Offset(0, 2).Value = out2.Value
  here.Offset(0, 3).Value = out3.Value
  Set here = here.Offset(1, 0)
 Wend
End Sub

Here we will need to define several named ranges: Input1 for the parameter that we will vary, Output1..3 for the calculation results we are interested in, and Out1D where the macro will look for values for input, and where, next to each input value, the result values will be output.

Why named ranges if we can specify cell addresses like.Range("F23") directly? Because it's much more robust coding style. If you decide to change the layout of your spreadsheet later, the named ranges are quite likely to point to correct locations, whereas your addresses will likely change (and your code may wreck some serious havoc on your spreadsheet). At worst (or if you decide to change parameters to vary/look at), all you will need is to re-point the named ranges to new locations rather than go through your code and edit addresses each time.

The added elegance of this example is that you don't need to specify how your sweeping parameter will vary anywhere in the code, nor do you need to count the number of runs. The program will do all of this for you, automatically running for all values you specify below the Out1D range until it hits an empty cell.
Another example will let you handle the dependence on two parameters:
Sub analysis2()

 Dim in1, in2, out1, here As Object
 Set in1 = Worksheets("Calc").Range("Input1")
 Set in2 = Worksheets("Calc").Range("Input2")
 Set out1 = Worksheets("Calc").Range("Output2D")
 Set here = Worksheets("Analysis").Range("Out2D")
 
 For i = 0 To 10
   here.Offset(-1, i + 1).Value = i * 0.01
 Next i

 While here <> Empty
  in1.Value = here.Value
  For i = 0 To 10
   in2.Value = i * 0.01
   Worksheets("Calc").Calculate '(or do whatever you need ...)
   here.Offset(0, i + 1).Value = out1.Value
  Next i
  Set here = here.Offset(1, 0)
 Wend
End Sub

As you can easily see, now we only have one output parameter (Output2D) and two input parameters (Input1, Input2). Similar to before, we look for values for parameter 1 and a place for output under the range Out2D. For the second parameter we have resorted to a simple for-loop (choosing 0, 0.1, 0.2 ... 1) but this is for simplicity and demonstration; arbitrary values for parameter 2 are possible with very little extension that I am leaving as an exercise.

A few words of warning:
  • Macros are ignorant of your spreadsheet editions! If you hardcode an address, or otherwise rely on a specific layout of your workbook, you will need to re-exaamine your code every time you edit the layout. A partial workaround is using named ranges (at least you can freely move them around).
  • Changes made by macros can't be undone! Before proceeding, make sure none of your important data gets overwritten, and do make a back-up copy before you test and debug your work. Luckily Excel will warn you that a file with macros cannot be saved as an .xlsx, prompting you to use .xlsm or .xlsb (or at least the old .xls) instead.
  • Macros won't always run! Make sure you enable them in your Excel security settings, and make sure some pesky anti-virus program won't blindly remove them from your sheets (assuming you weren't actually up to writing malware).


Bonus: There is a very quick VBA way to save an accountant's butt.

Consider this delicate scenario. You are working on a complex spreadsheet prepared a while ago by someone else, and the spreadsheet just won't tie. Soon enough the reason is clear: the person who last worked on the sheet blindly pasted some numbers over formulas to to sweep some of their mistakes under the rug. So you are left with finding all these "cover-ups"... and needless to say, that person left the company on less-than-amicable terms, so there's no one to ask about the spreadsheet structure.


How to flag "all cells where values were pasted over formulas" in a huge spreadsheet?

Consider something like this:

' The conditions may vary. We are interested in finding "all cells that have a number and are not formulas";
' the two most obvious checks would be
' (i) Left(cell.Formula,1) = "=" (i.e. cell's formula begins with "=")
' (ii) cell.Formula <> cell.Value (i.e. its formula is different from its value)
' Neither is totally fool-proof but both work fine.

For Each cell in ActiveSheet.UsedRange
 'some more code may be needed to safely work around cells that contain errors.
 If IsNumeric(cell.Value) And Left(cell.Formula,1) = "=" Then

  ' if it is a formula, everything is OK, dim it
  cell.Font.Color = RGB(128,128,128) 
 Else
  ' if it is a number, make it stand out
  cell.Interior.Color = RGB(255,255,0) 
  cell.Font.Color = RGB(255,0,0) 
 End If
Next cell

No comments:

Post a Comment