I have now converted the VBA code in the ConBeamU spreadsheet to Python, connected to Excel via pyxll. The new spreadsheet and associated Python code can be downloaded from:
In addition to the provided files, the pyxll application is required to link to Excel. See Python and pyxll for details, including a 10% discount code for new users. The following Python libraries are also required:
- Numpy
- Scipy
- Sympy
- Pint
- MPMath
Note that this is a work and progress, and some of the functionality of the VBA code is not fully operational. In particular, options for input of units are limited, and all combined unit names must be separated with the appropriate maths operator, e.g. kN.m^2 for a section bending stiffness (EI) unit.
As always, results must be independently checked if they are to be used in a real application.
The spreadsheet provides a number of functions for analysing the effect of loads on straight beams using Macaulay’s Method. For background information on Macaulay see: Beam actions and deflections by Macaulay’s Method. The next few posts will look at each of the functions in more detail.
The py_FEA and py_REA functions return the end actions of beams subject to specified loading, with fully fixed (py_FEA) or partially restrained end conditions (py_REA). Both these functions (and the other functions provided) also come in unit aware versions, py_FEAU and py_REAU.
End conditions for py_FEA are fully fixed at both ends by default, or with End Restraints specified as 11. The moment restraint may be fully released at the right, left, or both ends by entering End Restraints as 10, 1, or 0 respectively. For the py_REA function the translational and rotational restraint must be specified at both ends, with fully fixed restraint indicated with -1.
The beam may be divided into any number of segments with different section properties, and if the shear stiffness value is non-zero shear deflections will be included. For the examples in the spreadsheet two segments with equal properties have been specified, so that the py_FEA results can be compared with standard formula results for uniform beams. The effect of changing the shear stiffness of one segment is shown below.
See the spreadsheet for a range of other examples with different load conditions, with results compared with standard formulas.
The py_REAct3D function provides similar functionality to py_REA for 3 dimensional loading.
For section properties, the shear areas are optional, and all others are required. Output actions are given in the beam principal axis system.
The py_BeamAct3D function returns beam actions and deflections at specified sections for input actions at the beam ends, and along the length.
The input includes an option to include geometric non-linear effects in the analysis (default is not included). Output provides 6 beam actions and deflections at each section.
There is also a py_BeamAct2D function providing similar functionality in two dimensions.
Input:
Output:
The 2D function has the option to return the errors in the calculated end actions and deflections, compared with input values, as shown above. This option is currently not working in the 3D version, but will be added in the near future.
Newton Excel Bach, not (just) an Excel Blog 