Newton Excel Bach, not (just) an Excel Blog

My 3DFrame spreadsheet has now been converted to Python code and can be downloaded from:

3DFrame-py.zip

The frame analysis uses the Scipy linear algebra functions called from pyLinalgfuncs3, which must be available on the Python path:

pyLinAlgfuncs3.zip

The 3DFrame-py download file includes:

• 3DFrame-py.xlsb: The basic spreadsheet, including data for a small example frame
• 3DFrame-py Strand7 check.xlsb: Results for the small frame compared with Strand7 results.
• 3DFrame-py-vvbig.xlsb: Spreadsheet with input and results for a much larger frame
• Load3DFrame.py: Python code to load the 3DFrame-py code at startup of the associated spreadsheet
• py_3DFrame0_1.py: The 3DFrame-py code, using the Numba just-in-time compiler.
• py_3DFrame0_1-nojit.py: The same code but without any calls to Numba jit compilation.

In addition to the download files, the following modules are used:
Required:

• pyxll
• Numpy and Scipy
• ctypes

Optional:

• Numba
• pyPardiso (can now be installed with pip)

The spreadsheet has VBA code to automatically load the Python frame analysis code when the file is opened:

Public Sub Workbook_Open()
Dim iErr As Long

Application.EnableEvents = True
iErr = Run("Load3DFrame")
End Sub

The Load3DFrame module should either be added to the list of files opened at startup, in the pyxll.cfg file, or the code below should be added to an existing startup file:

import pyxll
from pyxll import xl_menu, xl_macro, xl_app, xlcAlert
import importlib as imp

@xl_macro
@xl_menu("Frame Analysis", menu="Load")
def Load3DFrame():
    xl = xl_app()
    usejit = xl.Range("Usejit").Value
    try:
        if usejit == 0:
            imp.import_module('py_3DFrame0_1-nojit')
        else:
            imp.import_module('py_3DFrame0_1')
        pyxll.rebind()
        xlcAlert('Frame Analysis module import completed')
    except:
        xlcAlert('Module not imported')
    return 0

The Python code is a work in progress, and the functionality of the spreadsheet is largely the same as the VBA version. The major differences are that the links to compiled solver routines have been replaced with links to the Scipy and pyPardiso solvers, and there is now an option to use code calling the Numba just-in-time compiler:

For large problems using the Numba module greatly reduces the time to complete an analysis, but the first time the solver is used the compiled code takes about 20 seconds to generate, so for small frames (or if the Numba module is not available), it is better to leave it disabled.

The spreadsheet has been saved with data for a small frame, and the results are compared with the Strand7 FEA results, showing near exact agreement:

The spreadsheet is able to handle much larger frames, and the download file has an example with nearly 10,000 nodes and over 27,000 beam members:

Note that for models of this size the Numba code should be enabled, and the solver should be set to one of the 2 iterative options, or to Scipy sparse, if the pyPardiso module is installed. The banded solver is very slow for models of this size, and may have memory overrun problems.

Background information on the methods used to generate and solve the frame model can be found by selecting “Frame Analysis” from the Categories drop down list at the top-right of the blog Home tab.