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
Newton Excel Bach, not (just) an Excel Blog 
Engineers are stubborn. 🙂
Why cross the street to turn the corner? You can call C (and Fortran) from the Microsoft C SDK. That is what Microsoft designed it for. My library makes this easy: https://xll.codeplex.com.
Taking a detour through Python or VBA can only hurt performance. And you don’t have access to the _FP data type that hands you a pointer to an array of doubles.
See https://xlllapack.codeplex.com/SourceControl/latest#trunk/potrf.cpp for an example of how to call the Cholesky decomposition. As a bonus, you can automatically generate help files for your code that are natively integrated with Excel.
I hope one day you will actually try this.
LikeLiked by 1 person
Hi Keith, I’m glad you are still visiting, in spite of your frustration with the contents 🙂
If I was a full-time programmer and my aim was 100% best performance then there is no question, getting into compiled code as soon as possible would be the way to go, but if that was my aim I probably wouldn’t be using Excel as a front end anyway.
In fact I’m a full-time engineer who does a bit of programming when I get the chance, and my aim is to get maximum functionality with the minimum of coding, and importantly minimum use of different tools that I have to learn and remember (my last programming class was probably in 1970 and was mainly spent learning the correct way to fill in paper forms to get our Fortran punch cards punched correctly). For that aim it seems to me that the Excel-Python route has a lot to recommend it, not least of which is that there are many other engineers and scientists working with it who do the hard work of generating the Fortran and C code, and providing convenient links to it.
LikeLiked by 2 people
Doug, I agree as an engineer who does a bit of programming occasionally, the Excel (ubiquitous) and the Python route has a lot to offer to make things fast and easy (relative) with little extra effort.
I was wondering if you had come across an interface (Python/Excel) that can handle the works, numpy, scipy, pandas and matplotlib altogether. You seem to like ExcelPython. I am about to start on this journey. I run Anaconda but they seem to suggest XLWings. I would like to see some comparison study.
I being a newbie, your suggestions would be appreciated. We had met many years ago in OZ at Main Roads, Queensland.
LikeLike
Hi Vasantha, good to hear from you.
I have only had a quick look at XL Wings, so I can’t comment at the moment, but I believe that the guy who wrote ExcelPython is collaborating with the XL Wings people, so I do plan to have a closer look when time permits.
I have just posted a continuous beam spreadsheet using ExcelPython which you might like a look at. If you have Anaconda Python installed it should work just by unzipping the download file to any convenient directory.
LikeLike
Thanks and good to have reconnected over the years. Both seem to have similar computing interests. I will try your module.
I am in Canada now.
Cheers
Vasantha
LikeLike
Pingback: Running Fortran from Python with F2PY | Newton Excel Bach, not (just) an Excel Blog