However, let us first get some familiarity with the most important Solver methods needed in our example Solver program:
SolverReset - Resets all cell selections, constraints and restores all the settings to their defaults.
SolverOk - Defines a Solver model. SetCell: this single cell reference is our objective function. MaxMinVal: integer value of 1 (maximize), 2 (minimize) or 3 (value of). ValueOf: if MaxMinVal is 3, we specify the value to which the objective function value is matched. ByChange: this cell/cells reference is our changing model variable(s).
SolverOptions - Allows you to specify advanced options for Solver model. Every existing setting in Solver Options subwindow can be configured within this function. Just for an example, we configure non-negativity settings. AssumeNonNeg: True, if the lower limit for decision variable(s) need to be zero. False, if negative values for decision variable(s) are allowed.
SolverSolve - Begins a Solver solution run. UserFinish: True to return the results without displaying the Solver Results dialog box. False or omitted to return the results and display the Solver Results dialog box.
More information about Solver functions can be read from here http://msdn.microsoft.com/en-us/library/office/jj945113.aspx
Curve fitting model
We are now ready for the actual problem. Set up the following data into Excel worksheet.
Let us go through this data first. First we have some actual swap curve data in the first two columns (maturity, rate). In the third column, we have 2nd degree polynomial function rate estimate, calculated by using coefficients a, b1 and b2. In the fourth column we have the squared difference of actual rate and our estimated rate. in the cell errors, we have the sum of error column values.
rate estimate r by using 2nd degree polynomial function:
r = a + b1 * (maturity) + b2 * (maturity * maturity)
(rate estimate - actual rate) * (rate estimate - actual rate)
The cell errors (initial value of 29.6609 when all coefficients are zero) is our objective function, since it sums all model errors. Since we want the error between actual curve and our to-be-estimated curve to be as small as possible, this is minimization problem. Coefficient values should also have an option to have negative values and hereby, we set our non-negativity constraint to be false. Coefficients a, b1 and b2 are our changing variables in this model. When I run my own Solver model, I will get the following results. (If you run this example model on your own in Excel, there is some differences due to decimal roundings in my example):
Now, let us talk for a moment about the program and what do we want to have with it. Maybe the thing what you want right now is just to have a simple Solver routine to do some rough minimization. In that case, just use macro recorder and get yourself one. Personally, I always try to look for a design, what would be the most flexible and easily extendable for other similar tasks. Here is one possible solution example below. Remember to create reference to Solver (VB editor - Tools - References - Solver).
First we create ISolver interface in a Standard VBA Class Module (name = ISolver). This is interface, what all possible Solvers must implement. It has only one function - solve - what takes in parameter wrapper (parameters inside Dictionary data structure). If you are unfamiliar with this, check my posting http://mikejuniperhill.blogspot.fi/2013/05/handling-parameters-dynamically-with.html
At this point, we could set up Solver addin (VB editor - tools - references - Solver) and Microsoft Scripting Library (VB editor - tools - references - Microsoft Scripting Runtime). Next, we create implementation for our interface. Since we need only unconstrained optimization model without non-negativity (or any other) constraints, we create Solver just for this simple purpose. It should be noted, that this Solver needs only 6 external parameters inside parameter wrapper (objective function, maxMinVal, valueOf, changingVariables, assumeNonNegative and userFinish). For some other, maybe bit more complicated Solver, the model could need more parameters. In that case, it would be easy to set all needed parameters inside parameter wrapper. Anyway, create the following implementation in a Standard VBA Class Module (name = Optimization_unconstrained).
Next, we need to set up Enumerator needed for our parameter wrapper. Create the following enumerator in a Standard VBA Module. Note, that this Enumerator is public for ALL modules and classes.
Finally, we set up our tester program in a Standard VBA Module. As can be seen in the program below, my objective function cell (sum of model errors) is H7 and my changing variables cells (coefficients a, b1, b2) are H3:H5. We give the addresses of these to our parameter wrapper. MaxMinVal are given in as an integer. UserFinish and Non-negativity assumption are given in as booleans.
Time for some afterthoughts. The purpose of this posting was to show, how we could implement Solver routines in VBA. What do we achieved? I think we got quite flexible model. Now, if we ever need to make our model more complicated, we could write a new Solver implementation and plug it into our existing design instead of copy-pasting and replacing a lot of code. Pattern-wise, this example Solver design can be used in Strategy Design Pattern. Moreover, you can naturally re-use your created Solver design in some other VBA project.
Solver by Frontline for Excel is truly a great thing. However, there has always been one serious downer, at least for me: you always need to link your program with concrete ranges in Excel worksheet, because Solver itself operates directly with these ranges. I mean that you can not have everything happening inside your program. One such a tool (maybe not so well-known) what I have been woodshedding a bit, is Microsoft Solver Foundation. If you are interested to learn more about it, check out one of my recent posting about it http://mikejuniperhill.blogspot.fi/2013/06/using-ms-solver-foundation-and-c-in.html
I hope again that you have gained something new for yourself. Have a nice evening and great June there.