Following the last post on using the Python version of Alglib from Excel, via xlwings, this post looks in more detail at alternatives for fitting a non-linear function to a set of data, using the Levenberg-Marquardt method.  The spreadsheet with examples and full open-source code may be downloaded from:

xlAlglib.zip

The spreadsheet requires xlwings, Python (including Numpy and Scipy) and Alglib to be installed.  All are available for free download, see Excel to Alglib via xlwings for more details.

In the examples below a non-linear function has been fitted to data generated using the same function plus some random scatter using two different Alglib modules:

• The lsfit module (least-squares fitting)
• The minlm module (Levenberg-Marquardt optimizer)

For each of these modules there is one user defined function (UDF) that uses a function entered as text on the spreadsheet, and another that calls a python function.

The lsfit versions (  xl_NLFitwFunc and  xl_NLFitwText) are described with examples on the Fit NonLin sheet:

The minlm versions (xl_minlm_vb and xl_minlm_vbtext) are found on the LM Solvers sheet:

The example shown below uses all four functions to fit a function of the form:
a * exp(b*x) + c * x + d
to the data shown in the screenshot below:

The function xl_minlm_vb (shown below) has the following arguments:

• x (B42:B45), Initial guess of function parameters, single column range
• xydat (B22:C34), Data to be fitted, two column range
• Func (C37), Fitting function; a function in NLFuncs.py
• bndl (B48:E48), Lower bound values, single row range
• bndu (B49:E49), Upper bound values, single row range
• epsx (B51), Maximum error
• maxits (B52), Maximum iterations
• out (0), Out = 0 returns results, anything else returns termination type code

Results of the four functions are shown below:

Code for the xl_minlm_vb function is described below.  Open source code for all the others is available in the download zip file.

The function xl_minlm_vb (automatically generated by xl wings) calls the Python function of the same name:

@xw.func
@xw.arg('x', ndim=1)
@xw.arg('bndl', ndim=1)
@xw.arg('bndu', ndim=1)
@xw.ret(transpose= True)
def xl_minlm_vb(x, xydat, funcname, bndl, bndu, epsx, maxits, out = 0):
    """
        Minimise a named function with Alglib minlmcreatev  Levenberg–Marquardt algorithm
        Initial guess of function parameters, single column range
        Data to be fitted, two column range
        Fitting function; a function in NLFuncs.py
        Lower bound values, single row range
        Upper bound values, single row range
        Maximum error, 0 = automatic
        Maximum iterations, 0 = unlimited
        out = 0 returns results, anything else returns termination type code
    """
    if type(x) == tuple: x = list(x)
    if type(bndl) == tuple: bndl = list(bndl)
    if type(bndu) == tuple: bndu = list(bndu)
    nx = len(x)
    nf = len(xydat)
    func = getattr(NLFuncs, funcname)
    state = xal.minlmcreatev(nx, nf, x, 0.00001)
    xal.minlmsetbc(state, bndl, bndu)
    xal.minlmsetcond(state, epsx, maxits)
    xal.minlmoptimize_v(state, func, None, xydat)
    x, rep = xal.minlmresults(state)
    if out == 0:
        return x
    else:
        return rep.terminationtype


if type(x) == tuple:  … Arrays are passed from Excel to Python as tuples, but Alglib requires a list.
func = getattr(NLFuncs, funcname): looks in NLFuncs.py for a Python function with the name given by funcname, and assigns this function to “func”.
state = xal.minlmcreatev( … x, rep = xal.minlmresults(state):  calls Alglib minlm functions to adjust the parameters passed to func in the “state”  object to minimize the square of the results.

“func” in this case is xl_expfunc:
def xl_expfunc(x, fi, func_dat):
    nf = len(func_dat)
    func_dat  = np.array(func_dat)
    for i in range(0,nf):
        X = func_dat[i,0]
        fi[i] = x[0]*np.exp(x[1]*X)+x[2]*X+x[3] - func_dat[i,1]
    return

Note that Alglib minimises the square of the array returned by xl_expfunc by adjusting the values in the x array.  To use this for curve fitting purposes we have to subtract the supplied Y value from the exponential function:

fi[i] = x[0]*np.exp(x[1]*X)+x[2]*X+x[3] – func_dat[i,1]
Alglib adjusts the function parameters until the error or maximum iterations criteria are satisfied, then extracts the required results from the “state” object with:

x, rep = xal.minlmresults(state)
The x array is then returned to the spreadsheet, via the VBA function.
Share this:
Facebook
LinkedIn
Tumblr
Twitter
Like this:
Like Loading...


	
Related