As stated in the previous post, this post will cover the calculation of beam actions and displacements when one or more supports have a defined vertical displacement, using the pyCBA beam analysis package. The Python code has been updated since the previous post, so the revised code and associated spreadsheets should be downloaded from:
The procedure used for analysis with specified support deflections in the latest update is:
Find results with no displacement at all fixed supports. This is easily done using the original input data ranges, but select only the first three columns of the Supports data.
At the first support with a specified displacement, set the support translation restraint to 0 and apply unit upward force.
Calculate the force required for the specified displacement: Specified displacement/displacement due to unit force
Apply this force at the support, and add the resulting actions and displacements to the results from the first stage.
Repeat for each support with a specified displacement.
This procedure is shown step by step in the screenshots below and is included on the DefStages sheet of the py_CBA-def-stages.xlsb file. The final results are compared with the results of a Strand7 analysis, with near exact agreement.
Analyse with no deflection at fixed supports:
At the first fixed support, set translation restraint to zero, and apply unit upward force:
Apply force required for the specified displacement, and add results to the first stage results:
Repeat for Support 2:
and Support 3:
Final results are in near exact agreement with Strand7:
Following the previous post the py_xlCBA spreadsheet and associated code have been updated. The updated files, including open-source code, can be downloaded from:
Note that the link in the previous post was downloading the wrong file, and has just been corrected.
The main updates are:
The py_CBA function now returns an array of beam or reaction results, rather than the py_CBA cache object.
The cache_object is now generated using the py_CBAcache function.
Beam or reaction results can now be extracted from the cache object using the pyCBARes or pyCBAReact functions.
The beam results now include beam slopes as well as deflections.
The reaction results include the beam slope and deflection at each support, as well as reaction forces and bending moments.
Supports may now be allocated a specific vertical deflection, as well as the displacement and rotation fixed or spring restraints. Note that if a non-zero displacement is specified any spring stiffness for deflections at that support is ignored, but spring or fixed rotation restraints are still applied.
The new input and output is shown below:
The supports input may now be three columns as before, or four columns if specified support displacements are required. Note that there must be at least one support with zero (or blank) displacement.
The main beam results array is now generated using the py_CBA function.
The results cache object is returned as BeamResults@x (lower left), using the py_CBAcache function.
As before, the CBA generated Python graphs may be returned immediately below the cache object.
Results may be extracted from the cache using the py_CBARes or py_CBAReact functions.
As before, the latest functions have been checked against Strand7 analyses of 15 different span arrangements, each with 6 different support conditions. The py_CBA results were generated in the py_CBA-Check26-2.xlsb spreadsheet, and all results are compared with Strand7 in Check py_CBA-16Jan26.xlsb. The results for the different support conditions are copied to six different sheets, and the results for any span type can be displayed by entering the Span Type number in cell Y2. The results are very close, except that currently where a specified deflection analysis is carried out, and the first support is at X = 0, the shear force at that support is shown as zero, as shown below:
The results have also been compared against the py_Conbeam function on the py_CBA-Check v Conbeam-support def.xlsb spreadsheet, with near exact agreement. Results are shown below for a 3 spam beam with two cantilevers, and 3 supports with specified deflections:
The next post will look at the procedure used to calculate the beam actions and deflections with specified deflections at one or more supports.
PyCBA is for fast linear elastic analysis of general beam configurations. It uses the matrix stiffness method to determine the displacements at each node. These are then used to determine the member end forces. Exact expressions are then used to determine the distribution of shear, moment, and rotation along each member. Cumulative trapezoidal integration is then used to determine the rotations and deflections along each member. The program features:
Multiple load types: point load; uniformly distributed load; patch load, and; moment load;
Spring supports, both vertical and rotational, enabling it to be used as part of a subframe analysis;
Results are output at 100 (user can change) positions along each span, enable accurate deflection estimation.
One of the main functions of PyCBA is that the basic analysis engine forms the basis for higher-level analysis. Current PyCBA includes modules for:
Influence line generation
Moving load analysis for bridges, targeted at bridge access assessments
I have set up a spreadsheet based on py_Conbeam that converts the py_Conbeam input to PyCBA format, then runs the PyCBA analysis and returns the results to the spreadsheet. The spreadsheet and associated files can be downloaded from:
Edit 18 Jan 2026: This link was previously downloading the wrong file. The link has now been corrected.
The spreadsheet requires the following programs in addition to Excel:
Python, Numpy, Scipy, and Matplotlib
PyCBA, which can be installed with pip. See the documentation page for details.
pyxll to transfer data between Excel and Python, including generation of dynamic Matplotlib graphics in Excel.
The spreadsheet uses 4 new Python functions, which have been added to the Beam_Act_2.py module, which also includes the py_Conbeam function and related functions.
In addition to the Python code the download file includes:
py_xlCBA.xlsb: The new spreadsheet.
py_CBA-Check v Conbeam.xlsb: Check of py_CBA results against py_Conbeam.
py_CBA-Check26-1.xlsb: Check of py_CBA results against Strand7 results for multiple different span and support conditions
Check py_CBA-2Jan26.xlsb: Summary of the Strand7 check results
py_xlCBA-Strand7.zip: Strand7 data file and results
The bulk of the work is done by the new py_CBA function which converts the input data from py_Conbeam format, creates a BeamAnalysis object, and runs the analyze method. The resulting beam_results object is returned to Excel as a cache_object, from which other pxll based Excel functions can extract and display the required results values.
Optionally, the function will also return graphs of the beam actions and deflections:
A table of beam shear forces, bending moments, and deflections at specified locations along the beam is returned by the pyCBARes function, with the results cache object and a list of output points as input:
Support reactions are returned by the pyCBAReact function. Note that currently reactions are only returned for supports that are fixed against deflection and/or rotation. For supports with spring restraints the associated reactions are returned as zero.
The py_CBAA function calls the py_CBA function and returns results as numeric arrays, rather than a cache object. The values returned are determined by the specified “out” index. Currently the available results are limited to the input files generated by py_CBA. See the “functions” sheet of the spreadsheet for a list of the available options.
As for the py_Conbeam spreadsheet, the check against Strand7 showed near exact agreement for all cases:
Note that currently it is not possible to specify support deflections in PyCBA, so the check runs are limited to spans type 1 to 4, which have either fixed or spring restraints at each support.
Recent work with the py_ConBeamU spreadsheet found that it was returning incorrect results when the last support had a rotation spring restraint. In spite of the checks against Strand7 having 90 different support conditions, none of them included this one, so I have modified the check files to include it. The modified Python code (version 1.05), and the new check runs, can be downloaded from:
The VBA version did not have this problem, but I have updated the check runs to be consistent with the Python version, and these can be downloaded from:
Following a lengthy discussion at Eng-Tips I have developed several functions to generate animations of the effects of applied moving loads. The Python code and examples have been added to the py_ConBeamU.zip file which can be downloaded from:
Starting with a short VBA sub-routine, using the Conbeam spreadsheet. The code simply generates a sequence of index numbers in a specified range, which are written to the spreadsheet, and the associated position of the applied point load is generated on the spreadsheet and passed to the ConBeam function that generates the required output, in this case the beam vertical deflections.
Sub Animate()
Dim Start As Long, Stp As Long, Target As Variant, EndPause As Double
Start = Range("U25").Value
Stp = Range("V25").Value
Set Target = Range("S25")
ActiveSheet.ChartObjects("Chart 1").Activate
' Enable screen updates for real-time visualization
Application.ScreenUpdating = True
For i = Start To Stp
Target.Value = i
ActiveChart.Refresh
EndPause = Timer + 0.05
Do While Timer < EndPause
ActiveSheet.Calculate
DoEvents
Loop
Next
End Sub
The animation displays in Excel as an Excel chart object. To display as a GIF:
Copy the active animation using a screen capture programme such as Snagit.
The first Python function generates a similar graph (deflections due to a moving point moment), using the py_ConBeam function, but with the following differences:
The code is written as an Excel User Defined Function, using pyxll, with all input data included in the function input.
The deflections are calculated using the py_ConBeam function, called from the Python Code.
The animation is generated within the function using Matplotlib, and then written to the spreadsheet as a graphics object.
Optionally, the animation can be written to a GIF file, which can then be used in any program accepting the GIF format, including the examples below.
Python code for the moving point load animation:
@xl_func
@xl_arg('Segments', 'numpy_array', ndim = 2)
@xl_arg('Supports', 'numpy_array', ndim = 2)
@xl_arg('DLoads', 'numpy_array', ndim = 2)
@xl_arg('PLoads', 'numpy_array', ndim = 2)
@xl_arg('replot', 'bool')
@xl_arg('miny', 'float')
@xl_arg('maxy', 'float')
@xl_arg('savegif', 'bool')
def plot_momdef(Segments, Supports, DLoads, PLoads, replot = False, miny=-4, maxy=4, savegif = False):
Supports0 = np.copy(Supports)
if replot:
OutPoints=np.zeros((101,1))
endx = Segments[-1,0]
OutPoints[:,0] = np.linspace(0, endx, 101)
# Create the matplotlib Figure object, axes and a line
fig = plt.figure(facecolor='white')
ax = plt.axes(xlim=(0, endx), ylim=(miny, maxy ))
plt.grid(True)
line, = ax.plot([], [], lw=3)
point, = ax.plot([], [], 'ro', markersize=8)
# The init function is called at the start of the animation
def init():
line.set_data([], [])
point.set_data([], [])
return line, point,
i = 1
# The animate function is called for each frame of the animation
def animate(i):
x = np.linspace(0, endx, 101)
PLoads[0,0] = OutPoints[i]
Supports = np.copy(Supports0)
res = py_ConBeam(Segments, OutPoints, Supports, DLoads, PLoads,1,True)
# convert y to mm
y = res[:,4]*1000
line.set_data(x, y)
x2 = np.array([OutPoints[i]])
y2 = np.array([y[i]])
point.set_data(x2, y2)
return line, point,
# Construct the Animation object
anim = FuncAnimation(fig,
animate,
init_func=init,
frames=100,
interval=50,
blit=True)
# Call pyxll.plot with the Animation object to render the animation
# and display it in Excel.
plot(anim, allow_resize=False)
# Set savegif to True to save anaimation as a gif file
if savegif: anim.save("pointmomdef.gif", writer=PillowWriter(fps=30))
return 'Deflections for moving point moment'
Animation generated by the Python code above:
For this example 2 m long cantilevers were added at each end of the beam, and multi-span continuous beams are also possible.
The examples above generate animations for a single applied point moment, but the py_ConBeam spreadsheet includes a moving load function which allows a vehicle with any number of axles to be generated and applied to a continuous beam with any number of spans. The animations below have been generated using this function with a three span beam with short link slab spans at each support, and the M1600 vehicle from the Australian Bridge Design Code (AS 5100.2).
The beam segment lengths and cross section properties, and support locations and properties are defined in the usual way. Output points should be generated at equal spacing, and are defined by the number of sections per span:
The Vehicle Definition, Load Factors, and Output Units are defined as in the py_MovLoad function. The vehicle positions are defined with the starting and end point of the first axle, together with the number of positions. The graph options include:
The action to be plotted; one of Shear, Moment, Slope or Deflection
Option to re-plot the animation.
Time interval between vehicle positions (milliseconds)
Option to save the pot to a gif file.
Note that the plot generation process is quite slow, so the re-plot option should be turned off (0) except when the input has been changed.
The moving load animation is generated immediately under the cell where the plot_MovingLoad function is entered:
Newton Excel Bach, not (just) an Excel Blog 