Newton Excel Bach, not (just) an Excel Blog

I have previously posted on the use of the Silverfrost Fortran compiler with Excel (e.g. Linking Excel to Fortran).  More recently I have been concentrating on accessing high performance compiled routines via Python and the Python maths and science libraries Numpy and Scipy (and also the Alglib library, as featured in the previous post).  Numpy also includes a built-in library to link to Fortran code (F2Py), allowing the vast range of Fortran public domain software to be accessed from Python, and hence from Excel.

In this post I will describe linking to a simple function to generate Fibonacci Numbers, and a routine to generate the stiffness matrix for 3D frame analysis.  Later posts will look at incorporating the stiffness matrix routine into a complete Excel based 3D frame analysis program.

The first requirement is to install Python, including Numpy.  As mentioned in the previous post, I can recommend the Anaconda package, but any other package including Numpy should work equally well.

It is also necessary to install a Fortran compiler.  After some trial and error I found that the MinGW (Minimalist GNU for Windows) package worked well.  See Getting Started for installation instructions.

Having installed Python and a Fortran compiler, look for the F2PY folder, which on my system was at:  c:\Users\Doug\Anaconda\Lib\site-packages\numpy\f2py\.  I found the F2py User Guide invaluable for getting started.  If not included in the F2py docs it can be found at: F2Py pdf User Guide.

Having installed the necessary packages, the first sample program in the user guide generates a series of Fibonacci Numbers.  See the user guide for the Fortran code, and the procedure for generating a compiled .pyd file that can be called from Python.  Having generated a module called fib.pyd, containing a single function, fib, the function may be called with the following Python code:

import fib
def pyfib(n):
    return fib.fib(n)

Linking to this from Excel, using ExcelPython:

Function xl_Fib(n)
Dim res As Variant
    On Error GoTo rtnres
    Set res = Py.Call(Py.Module("framep44_2"), "pyfib", Py.Tuple(n))
    xl_Fib = Py.Var(res, 2)
    Exit Function
rtnres:
    xl_Fib = Err.Description
End Function

… which generates the results below:

The procedure for the stiffness matrix routine is similar. The Fortran source code was based on a function listed in Programming The Finite Element Method.  Having generated the compiled module, frame_for2.pyd, with F2Py, this can be called from Python with the code below:

import frame_for2 as ff2
def getkm3UDF_F2(propm, coord, gamrad):
    propm = propm[0]
    coord = np.array(coord)
    km = ff2.getkm3_f(propm, coord, gamrad)
    return km.tolist()

As before, Linking to this from Excel, using ExcelPython:

Function xl_km3(PropA As Variant, Coord As Variant, Gamrad As Double) As Variant

On Error GoTo RtnErr
PropA = PropA.Value2
Coord = Coord.Value2

Set args = Py.Tuple(PropA, Coord, Gamrad)
xl_km3 = Py.Var(Py.Call(Py.Module("framep44_2"), "getkm3UDF_F2", args))
   Exit Function
RtnErr:
    xl_km3 = Err.Description

End Function

… which generates the 12 x 12 stiffness matrix