Showing posts with label Dictionary. Show all posts
Showing posts with label Dictionary. Show all posts

Friday, July 5, 2019

Python: JSON serialization/deserialization

Previous story continues. This post will present one possible implementation for JSON serialization/deserialization. Class JsonHandler (technically just a wrapper for json.load and json.dump methods) has only two methods: FileToObject will re-hydrate JSON file content to a custom object (deserialization) and ObjectToFile will hydrate custom object into JSON file content (serialization).

Class serialization using Python json package works fine with class data members, which are built-in Python data types (ex. integer, string, boolean, float, list, dictionary). However, custom data types such as class instance as data member are non-serializable. JsonHandler cannot handle such non-serializable data types either. As a safety net for facing such case, ObjectToFile method will automatically remove all non-serializable items from dictionary before serialization. For handling this type of serialization issues, I assume there are several more sophisticated third-party packages available.

Finally, it should be noted that when performing deserialization, JsonHandler transforms JSON string into dictionary, then transforms dictionary into object. Correspondingly, when performing serialization, it transforms object into dictionary, then transforms dictionary into JSON string. These operations (including nested methods) are adding some extra complexity into this otherwise simple and straightforward utility class.

Assume we would like to hydrate (serialize) Configurations class instance into JSON file. Note, that this class is actually containing non-serializable data type as its data member (class Whatever). This specific data member will be completely ignored in a process of serialization. However, as JSON file will be deserialized, instance of data member G will be created on class Configurations constructor, based on data members A and B (which are both serializable data types).

import json

# class for handling transformations between custom object and JSON file
class JsonHandler:
    
    # transform json file to custom object
    def FileToObject(file):
        # nested function: transform dictionary to custom object
        def DictionaryToObject(dic):
            if("__class__" in dic):
                class_name = dic.pop("__class__")
                module_name = dic.pop("__module__")
                module = __import__(module_name)
                class_ = getattr(module, class_name)
                obj = class_(**dic)
            else:
                obj = dic
            return obj        
        return DictionaryToObject(json.load(open(file, 'r')))
    
    # transform custom object to json file
    def ObjectToFile(obj, file):
        # nested function: check whether an object can be json serialized
        def IsSerializable(obj):
            check = True
            try:
                # throws, if an object is not serializable
                json.dumps(obj)
            except:
                check = False
            return check
        # nested function: transform custom object to dictionary
        def ObjectToDictionary(obj):
            dic = { "__class__": obj.__class__.__name__, "__module__": obj.__module__ }            
            dic.update(obj.__dict__)
            # remove all non-serializable items from dictionary before serialization
            keysToBeRemoved = []
            for k, v in dic.items():
                if(IsSerializable(v) == False):
                    keysToBeRemoved.append(k)
            [dic.pop(k, None) for k in keysToBeRemoved]
            return dic
        json.dump(ObjectToDictionary(obj), open(file, 'w'))

class Configurations(object):
    def __init__(self, A, B, C, D, E, F):
        # serializable data types
        self.A = A
        self.B = B
        self.C = C
        self.D = D
        self.E = E
        self.F = F
        # non-serializable data type
        self.G = Whatever(A, B)
        
class Whatever(object):
    def __init__(self, A, B):
        self.A = A
        self.B = B

# create class instance using 'primitive types'
new_config = Configurations(100, 3.14, True, [1, 2, 3], 'qwerty', { 'd1':1, 'd2':2 })

# print class members
print('printing data members of a newly created class instance:')
print(new_config.A)
print(new_config.B)
print(new_config.C)
print(new_config.D)
print(new_config.E)
print(new_config.F)
print(new_config.G.A)
print(new_config.G.B)
print()

# write object to json file
JsonHandler.ObjectToFile(new_config, '/home/mikejuniperhill/config.json')

# read object from json file
restored_config = JsonHandler.FileToObject('/home/mikejuniperhill/config.json')

print('printing data members of a restored class instance:')
print(restored_config.A)
print(restored_config.B)
print(restored_config.C)
print(restored_config.D)
print(restored_config.E)
print(restored_config.F)
print(restored_config.G.A)
print(restored_config.G.B)

Program execution in terminal is shown below.















Thanks for reading.
-Mike


Wednesday, July 3, 2019

Python: using JSON file for increasing program configurability

As the experience tells us, wrong decisions in program design and life will usually bite back hard. In order to avoid the most obvious traps leading into horrific maintenance problems, we should always design our programs to be free of any hard-coded parameters. By using configuration scheme presented in this post, flexible programs, which can use any desired set of input configurations, can be created. This means we can (as an example) execute a specific program (for valuing batch of transactions) several times, but using different set of configurations (different set of market data) for each execution. All example files can be downloaded from my GitHub page.

In this very simple example, Python program will just print a set of market and fixings data from CSV files, based on a given set of configurations. In order to keep this example program short and sweet, our JSON configuration file has only two configurations: directory addresses for market and fixings data CSV files, as follows.

{
  "MARKETDATA":"/home/mikejuniperhill/Market.csv",
  "FIXINGSDATA":"/home/mikejuniperhill/Fixings.csv"
}

Directory address of this configuration file will be given as a command line argument for the program. Based on this given configuration, program will then read configured data from files to be used in program. 
















Example program is shown below. In the first stage, program will create configurations object. Technically, this object is just a wrapper for dictionary data structure. Any specific configuration can be accessed by using Python version of index operator overloading. After this, program reads data from configured CSV files into DataFrame objects and prints their contents to terminal.

import json
import sys
import pandas

# class for hosting configurations
class Configurations:
    inner = {}
    # read JSON configuration file to dictionary
    def __init__(self, filePathName):
        self.inner = json.load(open(filePathName))
        self.inner = {k.upper(): v for k, v in self.inner.items()}
    # return value for a given configuration key
    # 'overload' indexing operator
    def __getitem__(self, key):
        return self.inner[key.upper()]

# configuration file string is command line argument
configurationsFilePathName = sys.argv[1]

# create configurations object
config = Configurations(configurationsFilePathName)

# create market data based on configuration
market = pandas.read_csv(config['MarketData'])
print('EUR swap curve:')
print(market.head())

# create fixings data based on configuration
fixings = pandas.read_csv(config['FixingsData'])
print('6M Euribor fixings:')
print(fixings.head())

Handy tool for constructing and testing syntactic correctness of any JSON file can be found in here. Finally, thanks for reading.
-Mike

Thursday, June 13, 2013

Implementing binomial solver design in VBA

In this long post, I will open up my current implementation for binomial option solver. The reader is expected to be familiar and comfortable with theory of pricing option by using binomial model. If you feel a bit rusty with the topic, you can get some refreshing overview from here, for example: http://en.wikipedia.org/wiki/Binomial_options_pricing_model 

Let us say, that we would like to create a program to price options by using binomial model, but keep everything as flexible as possible. This means, that we have to abandon the idea of creating one big monolithic function (traditional VBA approach). Anyway, what do we need to accomplish this, and how could we create such a design?

Components

1) Parameters source - this is a "place", from which we read in all option-related parameters. We are not hard-coding anything, but instead we are going to create interface IOptionFactory for different possible data sources. IOptionFactory has only two public methods: createOptionParameters and getOptionParameters. Hereby, it is only a container for all option-related parameters needed in this design.

In essence, we could create different interface implementations for reading parameters from system database or text file, for example. However, In this example our IOptionFactory implementation is going to be ExcelFactory class, which reads parameters from Excel Worksheet into optionParameters parameter wrapper.

Parameter wrapper is Dictionary data structure, into which we save all needed parameters in this program (remember to reference Microsoft Scripting Runtime library). For parameter wrapper, we need to have public Enumerator for all field key values used in parameter wrapper. If you have no idea what I am explaining here, check out my post http://mikejuniperhill.blogspot.fi/2013/05/handling-parameters-dynamically-with.html

2) Data structure - this is a data structure (Tree) for storing data (the actual binomial tree structure). For this purpose, we are simply wrapping jagged array (array of arrays) into a separate class. This class has only the most vital functionalities, such as access to Tree items (nodes) and information about number of Tree periods and number of states in each period. Hereby, Tree class is only a data container and for any operations to be performed on items of this container, we create separate iterator interface ITreeIterator.

By having data and algorithms separately means, that we can create new iterator implementations for operating on Tree structure nodes in a different way and we do not need to change anything in Tree class. Moreover, we can always replace any existing iterator in our design, just by plugging in a new iterator and there is no need to break the existing program design. I would say, that these benefits will clearly override the costs of implementing this scheme.

So, ITreeIterator interface has all the needed methods for performing operations on Tree nodes. The next question is, what are those operations? If we think about the pricing option with binomial method, these operations could be the following:
  1. Forward iteration - for creating the actual spot Tree.
  2. Terminal payoff calculation - for calculating option payoffs for each state at the maturity date of the option.
  3. Backward iteration - for discounting option payoffs and calculating option payoffs on each node from maturity to present date.
Creating a spot tree (forward iteration) is basically quite straightforward process. Calculating payoffs at maturity and backward iteration (option valuation parts) can be a bit more tricky issue, depending on option type. Since our iterator is an implementation, for backward iterating process we could implement different iterators, such as American iterator or Bermudan iterator for example. We can also change Payoff function to calculate any possible payoff, since it is a separate object what we are feeding to our iterator. Example Implementation given in this program is European iterator (EuropeanTreeIterator).

3) Payoff function - option payoff structure is going to be implemented also as an interface (IOneFactorPayoff). Along with its init method (artificial constructor), it has only one public method - getPayoff, which calculates option payoff for a given spot price. Example implementation is for vanilla call option (VanillaCallPayoff).

4) Binomial process parameters - as we know, there are a lot of different models for creating binomial trees. We want to leave an option for the user to use different binomial models. For this reason, we create interface ILatticeStrategy. This interface has only one public method - init (artificial constructor), which takes in parameter wrapper as argument. The purpose of this method is to create binomial process-related parameters (u, d and p) and save these back into parameter wrapper. In this example, we implement Cox-Ross-Rubinstein model without drift (CRRNoDrift).

Program flow

Now, how on earth do we manage all this mess, what I have just described? I admit, that this design candidate might feel overly complex - at first. However, after some further investigations you should see, that it is actually pretty straightforward. Well, of course not as straightforward as that traditional monolithic VBA function, but our "extra complexity" is not there without some very good reasons. Let us talk about these reasons later in our afterthoughts section. At this moment, let us try to get some sense about this design by looking our test program first.

Option Explicit
'
Sub Tester()
    '
    ' create option parameters in option factory
    Dim optionFactory As IOptionFactory: Set optionFactory = New ExcelFactory
    optionFactory.createOptionParameters
    '
   
    ' create option payoff object
    Dim payoff As IOneFactorPayoff: Set payoff = New VanillaCallPayoff
    payoff.init optionFactory.getOptionParameters

    '
    ' create process type for creating spot tree

    Dim latticeStrategy As ILatticeStrategy: Set latticeStrategy = New CRRNoDrift
    latticeStrategy.init optionFactory.getOptionParameters
    '
    ' create iterator for traversing tree structure

    Dim latticeIterator As ITreeIterator: Set latticeIterator = New EuropeanTreeIterator
    latticeIterator.init payoff, optionFactory.getOptionParameters

    '
End Sub

As we can see, ExcelFactory is creating all option-related parameters into parameter wrapper in the first stage. Then, we create VanillaCallPayoff and feed it with parameter wrapper which is "centrally hosted" by ExcelFactory. After this, we create CRRNoDrift and use it for calculating binomial process parameters, by feeding it with parameter wrapper. Finally, we create EuropeanTreeIterator and feed it with parameter wrapper and VanillaCallPayoff function. It should be noted, that iterator has the actual Tree data structure aggregated inside it. Let us go forward.

Option Explicit
'
Sub Tester()
    '
    ' create option parameters in option factory
    Dim optionFactory As IOptionFactory: Set optionFactory = New ExcelFactory
    optionFactory.createOptionParameters
    '
    ' create option payoff object
    Dim payoff As IOneFactorPayoff: Set payoff = New VanillaCallPayoff
    payoff.init optionFactory.getOptionParameters
    '
    ' create process type for creating spot tree
    Dim latticeStrategy As ILatticeStrategy: Set latticeStrategy = New CRRNoDrift
    latticeStrategy.init optionFactory.getOptionParameters
    '
    ' create iterator for traversing tree structure
    Dim latticeIterator As ITreeIterator: Set latticeIterator = New EuropeanTreeIterator
    latticeIterator.init payoff, optionFactory.getOptionParameters
    '
    ' create solver which uses parameters and process to calculate option value
    Dim binomialSolver As New BinomialMethod
    binomialSolver.init latticeIterator
    Debug.Print binomialSolver.getPrice(2.614)
    '
End Sub

We create class called BinomialMethod for technically hosting our EuropeanIterator implementation class. This class has init method (artificial constructor) and method getPrice method, which uses iterator to perform forward iteration (create binomial tree), calculate terminal payoffs (calculate option payoffs at maturity) and perform backward iteration (discount payoffs along the tree to current date). Finally, it returns the present value of the option for its caller (Tester).

Interfaces, Classes and Tester program

All classes mentioned above, have been presented here below. You can copy-paste these into your VBA project for testing (remember to reference Microsoft Scripting Runtime library in VB editor).

Tree data structure class. Copy into VBA Class Module (Name = Tree)

Option Explicit
'
' ZERO-INDEXED data structure (array of arrays)
' example indexing access: period 2, state 1 = outer(2)(1)
Private outer() As Variant
Private dt As Double
'
Public Function init(ByVal timeInYears As Double, ByVal numberOfPeriods As Long)
    '
    ' init function serves as artificial constructor
    ' create tree structure having n periods
    dt = (timeInYears / numberOfPeriods)
    ReDim outer(0 To numberOfPeriods)
    '
    Dim i As Long
    For i = 0 To numberOfPeriods
        Dim inner() As Double
        ReDim inner(0 To i)
        outer(i) = inner
    Next i
End Function
'
Public Function push(ByVal period As Long, ByVal state As Long, ByVal value As Double)
    ' setter function
    outer(period)(state) = value
End Function
'
Public Function at(ByVal period As Long, ByVal state As Long) As Double
    ' getter function
    at = outer(period)(state)
End Function
''
Public Function n_periods() As Long
    ' return number of periods in tree structure, minus 1
    n_periods = UBound(outer, 1)
End Function
'
Public Function n_states(ByVal period As Long) As Long
    ' return number of states within a periods, minus 1
    Dim stateArray() As Double: stateArray = outer(period)
    n_states = UBound(stateArray)
End Function
'
Public Function t_at(ByVal period As Long) As Double
    ' get time in years for a node
    t_at = dt * period
End Function
'

Tree iterator interface. Copy into VBA Class Module (Name = ITreeIterator)

Option Explicit
'
Private lattice As Tree
'
Public Function forward(ByVal spot As Double)
End Function
'
Public Function backward()
End Function
'
Public Function terminalPayoff()
End Function
'
Public Function init(ByRef oneFactorPayoff As IOneFactorPayoff, ByRef parameters As Scripting.Dictionary)
End Function
'
Public Function getLattice() As Tree
End Function
'

One possible implementation for Tree iterator interface. Copy into VBA Class Module (Name = EuropeanTreeIterator)

Option Explicit
'
Implements ITreeIterator
'
Private payoff As IOneFactorPayoff
Private p As Scripting.Dictionary
Private lattice As Tree
'
Private Function ITreeIterator_forward(ByVal spot As Double)
    '
    ' get process-related parameters for filling the tree
    Dim u As Double: u = p.Item(E_UP)
    Dim d As Double: d = p.Item(E_DOWN)
    lattice.push 0, 0, spot ' initialize index (0,0) to be the user-given spot price
    '
    ' create spot tree from 0 to n
    Dim i As Long, j As Long, periods As Long
    periods = lattice.n_periods
    '
    For i = 1 To periods
        For j = 0 To (lattice.n_states(i) - 1)
            lattice.push i, j, lattice.at(i - 1, j) * d
            lattice.push i, j + 1, lattice.at(i - 1, j) * u
        Next j
    Next i
End Function
'
Private Function ITreeIterator_backward()
    '
    ' modify this - node-to-node iterating is not required (use binomial probabilities)
    ' transform spot tree to option tree from n to 0 (index 0,0 is the option value)
    ' get discount factor
    Dim df As Double: df = VBA.Exp(-p.Item(E_RATE) * (p.Item(E_TIME) / p.Item(E_PERIODS)))
    Dim w As Double: w = p.Item(E_PROBABILITY)
    Dim q As Double: q = (1 - w)
    '
    ' re-calculate option tree backwards from n to 0
    Dim i As Long, j As Long, periods As Long
    periods = lattice.n_periods
    '
    For i = periods To 0 Step -1
        For j = (lattice.n_states(i) - 1) To 0 Step -1
            '
            Dim value As Double
            value = (w * (lattice.at(i, j + 1)) + q * (lattice.at(i, j))) * df
            lattice.push i - 1, j, value
        Next j
    Next i
End Function

Private Function ITreeIterator_getLattice() As Tree
    Set ITreeIterator_getLattice = lattice
End Function

Private Function ITreeIterator_init(ByRef oneFactorPayoff As IOneFactorPayoff, _
ByRef parameters As Scripting.Dictionary)
    '
    Set payoff = oneFactorPayoff
    Set p = parameters
    Set lattice = New Tree: lattice.init p.Item(E_TIME), p.Item(E_PERIODS)
End Function
'
Private Function ITreeIterator_terminalPayoff()
    '
    ' calculate terminal payoffs for a tree at maturity
    Dim j As Long, periods As Long
    periods = lattice.n_periods
    '
    For j = (lattice.n_states(periods)) To 0 Step -1
        '
        Dim terminalValue As Double
        terminalValue = payoff.getPayoff(lattice.at(periods, j))
        lattice.push periods, j, terminalValue
    Next j
End Function
'

Lattice strategy interface. Copy into VBA Class Module (Name = ILatticeStrategy)

Option Explicit
'
Public Function init(ByRef parameters As Scripting.Dictionary)
End Function
'

One possible implementation for Lattice strategy interface. Copy into VBA Class Module (Name = CRRNoDrift)

Option Explicit
'
' this class implements Cox, Ross and Rubinstein model with no drift factor
Implements ILatticeStrategy
'
Private p As Scripting.Dictionary
'
Private Function ILatticeStrategy_init(ByRef parameters As Scripting.IDictionary)
    '
    ' init parameter dictionary
    Set p = parameters
    '
    ' calculate process-related parameters into parameters
    Dim dt As Double: dt = p.Item(E_TIME) / p.Item(E_PERIODS)
    '
    ' calculate up and down factors into parameters
    p.Item(E_UP) = VBA.Exp(p.Item(E_VOLATILITY) * VBA.Sqr(dt))
    p.Item(E_DOWN) = VBA.Exp(-p.Item(E_VOLATILITY) * VBA.Sqr(dt))
    '
    ' calculate risk-neutral probability factor into parameters
    p.Item(E_PROBABILITY) = ((VBA.Exp(p.Item(E_RATE) * dt) - p.Item(E_DOWN)) / (p.Item(E_UP) - p.Item(E_DOWN)))
End Function
'

Payoff interface. Copy into VBA Class Module (Name = IOneFactorPayoff)

Option Explicit
'
Public Function getPayoff(ByVal spot As Double) As Double
End Function
'
Public Function init(ByRef parameters As Scripting.Dictionary)
End Function
'

One possible implementation for payoff interface. Copy into VBA Class Module (Name = VanillaCallPayoff).

Option Explicit
'
' one factor vanilla call option payoff
Implements IOneFactorPayoff
'
Private x As Double
'
Private Function IOneFactorPayoff_getPayoff(ByVal spot As Double) As Double
    IOneFactorPayoff_getPayoff = maxPayoff(0, spot - x)
End Function
'
Private Function IOneFactorPayoff_init(ByRef parameters As Scripting.Dictionary)
    x = parameters.Item(E_STRIKE)
End Function
'
Private Function maxPayoff(ByVal a As Double, ByVal b As Double) As Double
    '
    maxPayoff = b
    If (a > b) Then maxPayoff = a
End Function
'

Binomial method class. Copy into VBA Class Module (Name = BinomialMethod).

Option Explicit
'
Private it As ITreeIterator 
'
Public Function init(ByRef iterator As ITreeIterator)
    '
    ' artificial constructor
    Set it = iterator
End Function
'
Public Function getPrice(ByVal spot As Double) As Double
    '
    ' this function builds tree and iterates it forward and backward to calculate option value
    it.forward spot ' create spot tree
    it.terminalPayoff ' calculate all payoffs at maturity
    it.backward ' calculate option value
    getPrice = it.getLattice.at(0, 0)
End Function
'

Interface for option factory. Copy into VBA Class Module (Name = IOptionFactory).

Option Explicit
'
Public Function createOptionParameters()
End Function
'
Public Function getOptionParameters() As Scripting.Dictionary
End Function
'

One possible implementation of option factory. Copy into VBA Class Module (Name = ExcelFactory).

Option Explicit
'
' class reads parameters data from specific excel worksheet
Implements IOptionFactory
'
Private optionParameters As Scripting.Dictionary ' data structure to hold all needed option parameters
'
Private Function IOptionFactory_createOptionParameters() As Variant
    '
    Set optionParameters = New Scripting.Dictionary
    Dim r As Range: Set r = Sheets("Sheet1").Range("D2:D6")
    '
    optionParameters.Item(E_STRIKE) = VBA.CDbl(r(1, 1))
    optionParameters.Item(E_VOLATILITY) = VBA.CDbl(r(2, 1))
    optionParameters.Item(E_TIME) = VBA.CDbl(r(3, 1))
    optionParameters.Item(E_RATE) = VBA.CDbl(r(4, 1))
    optionParameters.Item(E_PERIODS) = VBA.CLng(r(5, 1))
    '
    Set r = Nothing
    End Function
'
Private Function IOptionFactory_getOptionParameters() As Scripting.IDictionary
    Set IOptionFactory_getOptionParameters = optionParameters
End Function
'

Then we also need that Enumerator for our parameter wrapper. Copy into VBA Standard Module.

Option Explicit
'
Public Enum PRM
    '
    ' process-related parameters (calculated by ILatticeStrategy implementation)
    E_UP = 1
    E_DOWN = 2
    E_PROBABILITY = 3
    '
    ' option-related parameters (created by IOptionFactory implementation)
    E_RATE = 4
    E_STRIKE = 5
    E_VOLATILITY = 6
    E_PERIODS = 7
    E_TIME = 8
    '
End Enum
'

Program example

We create our tester program for vanilla equity call option, which is not paying any cashflows. Set the following data into Excel Worksheet. Make sure, that the range reference in ExcelFactory class is referring to this range in your Excel.

parameter value
strike 2,600
vol 42,9 %
time 0,271
rate 0,3 %
periods 250

Note, that in this design we are giving spot value to BinomialMethod object as an argument in its getPrice method. Below here is the actual tester program. Copy-paste it into VBA Standard Module.

Option Explicit
'
Sub Tester()
    '
    ' create option parameters in option factory
    Dim optionFactory As IOptionFactory: Set optionFactory = New ExcelFactory ' can be switched at runtime!
    optionFactory.createOptionParameters
    '
    ' create option payoff object
    Dim payoff As IOneFactorPayoff: Set payoff = New VanillaCallPayoff ' can be switched at runtime!
    payoff.init optionFactory.getOptionParameters
    '
    ' create process type for creating spot tree
    Dim latticeStrategy As ILatticeStrategy: Set latticeStrategy = New CRRNoDrift ' can be switched at runtime!
    latticeStrategy.init optionFactory.getOptionParameters
    '
    ' create iterator for traversing tree structure
    Dim latticeIterator As ITreeIterator: Set latticeIterator = New EuropeanTreeIterator ' can be switched at runtime!
    latticeIterator.init payoff, optionFactory.getOptionParameters
    '
    ' create solver which uses parameters and process to calculate option value
    Dim binomialSolver As New BinomialMethod
    binomialSolver.init latticeIterator
    Debug.Print binomialSolver.getPrice(2.614)
    '
    ' object releasing tasks
    Set binomialSolver = Nothing
    Set latticeIterator = Nothing
    Set latticeStrategy = Nothing
    Set payoff = Nothing
    Set optionFactory = Nothing
End Sub
'

Some afterthoughts

First of all, I got my valuation for this equity option (NOK1V FH, September 13 Call, 2.6 strike) to be approximately 0.24 today when the spot was on 2.614. I confirmed this valuation to be close enough to the market by using Bloomberg OMON<GO> function.

So, what is so great about this design after all? What is the reason, why we have to have all this complexity? When we investigate that tester program, we can realize, that the following "components" can be switched at will - meaning, that we could create a new implementation for these, without breaking our existing design:

1) the source from which we create option-related parameters (Excel, txt file, database, etc)
2) payoff function (vanilla put, digital call, etc)
3) process for creating binomial tree parameters (CRR, JR, etc)
4) iterator, which calculates payoffs from binomial tree (european, american, etc)


This example program hopefully shows, that it is possible to create very flexible and extendable designs in VBA by using Interface implementation mechanism and a couple of other tricks presented in this post (parameter wrapper). Some of the components employed in this example are also quite generic in nature. We could use Tree data structure and its iterator, when creating a program for building short-term interest rate trees, for example.

My thanks about some of the central ideas presented here belongs to Daniel Duffy for his inspiring C++ design pattern example papers and C++ book chapter on this particular topic.

Well, it is time to say goodbye again. First of all, a big hats off for you, if you really have gone through this posting. Thank You, I hope you could get a bit of some idea from it.
-Mike

Tuesday, June 11, 2013

Using Excel Solver with VBA

Excel Solver XLAM-addin by Frontline is a handy tool for small optimization problems. In the case you did not know yet, it is also possible to use Solver in your VBA program. In this post, I am opening one possible way to implement Solver functionality to be used in VBA program. As a practical example, we will use our Solver program to perform a curve-fitting routine. If you need some refresh in this topic, you can check the following links to get some overview: http://en.wikipedia.org/wiki/Curve_fitting (fitting lines and polynomial curves to data points) and http://en.wikipedia.org/wiki/Least_squares 

Solver functions

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.

maturity rate estimate error   coefficients  
0,08 0,19 0 0,04   a 0,0000
0,25 0,27 0 0,08   b1 0,0000
0,5 0,29 0 0,09   b2 0,0000
1 0,35 0 0,12      
2 0,51 0 0,26   errors 29,6609
5 1,38 0 1,91      
10 2,46 0 6,07      
20 3,17 0 10,07      
30 3,32 0 11,04      

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.

Calculation formulas 

rate estimate r by using 2nd degree polynomial function:
r = a + b1 * (maturity) + b2 * (maturity * maturity) 

estimate error:
(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):

maturity rate estimate error   coefficients  
0,08 0,19 0,166955 0,00   a 0,1446
0,25 0,27 0,211408 0,00   b1 0,2685
0,5 0,29 0,277517 0,00   b2 -0,0055
1 0,35 0,407683 0,00      
2 0,51 0,659804 0,02   errors 0,0904
5 1,38 1,350482 0,00      
10 2,46 2,282667 0,03      
20 3,17 3,325987 0,02      
30 3,32 3,274573 0,00      

Program

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

Option Explicit
'
' interface to be implemented
Public Function solve(ByRef parameters As Scripting.Dictionary)
End Function
'

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).

Option Explicit
'
Implements ISolver
'
' implementation for ISolver interface
' solver for unconstrained optimization problems
Private Function ISolver_solve(ByRef parameters As Scripting.IDictionary)
    '
    Solver.SolverReset
    Solver.SolverOk _
        parameters.Item(PRM.objectiveFunction), _
        parameters.Item(PRM.maxMinVal), _
        parameters.Item(PRM.valueOf), _
        parameters.Item(PRM.changingVariables)
    '
    Solver.SolverOptions AssumeNonNeg:=parameters.Item(PRM.assumeNonNegative)
    Solver.SolverSolve parameters.Item(PRM.userFinish)
    Solver.SolverReset
End Function
'

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.

Option Explicit
'
' enumerator needed for parameter wrapper
Public Enum PRM
    '
    objectiveFunction = 1
    maxMinVal = 2
    valueOf = 3
    changingVariables = 4
    userFinish = 5
    assumeNonNegative = 6
End Enum
'

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.

Option Explicit
'
Public Sub tester()
    '
    ' define Excel ranges for objective function and variables
    Dim objectiveFunctionRange As Range: Set objectiveFunctionRange = Sheets(2).Range("H7")
    Dim changingVariablesRange As Range: Set changingVariablesRange = Sheets(2).Range("H3:H5")
    '
    ' create parameter wrapper and fill it
    Dim parameters As New Scripting.Dictionary
    '
    ' for objective function and variables, address of range is needed
    parameters.Add PRM.objectiveFunction, CStr(objectiveFunctionRange.Address)
    parameters.Add PRM.changingVariables, CStr(changingVariablesRange.Address)
    parameters.Add PRM.maxMinVal, CInt(2)
    parameters.Add PRM.userFinish, CBool(True)
    parameters.Add PRM.assumeNonNegative, CBool(False)
    '
    ' create solver model - in this case we need unconstrained optimization
    Dim xlSolver As ISolver: Set xlSolver = New Optimization_unconstrained
    xlSolver.solve parameters
    '
    ' release objects
    Set xlSolver = Nothing
    Set parameters = Nothing
End Sub
'

Afterthoughts

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.
-Mike