Calling PyCBA from Excel

Posted on 04/01/2026 by dougaj4

PyCBA is a Python based continuous beam analysis program. From the on-line docs:

PyCBA is a python implementation of the Continuous Beam Analysis program, originally coded in Matlab and subsequently ported to C++ (by Pierrot).

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:

py_xlCBAzip

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.

Posted in Beam Bending, Excel, Frame Analysis, Link to Python, Newton, NumPy and SciPy, PyXLL, Strand7, UDFs | Tagged , , , , , , , , , | Leave a comment

py_ConBeamU and Strand7 check updates-2

Posted on 30/12/2025 by dougaj4

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:

py_ConBeamU.zip

See py_ConBeamU and Strand7 check updates for more details of the download files.

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:

ConBeamU.zip

The results from the previous version of py_ConbeamU and the latest version are shown below for the restraint case causing the problem:

The updated file now agrees almost exactly with the Strand7 results for this load case (Span Type 14 on “Sheet 6”):

Posted in Beam Bending, Excel, Link to Python, Newton, PyXLL, Strand7, UDFs | Tagged , , , , , , , | Leave a comment

Continuous beam animations with VBA and Python

Posted on 20/12/2025 by dougaj4

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:

py_ConBeamU.zip

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.
  • Save as an MP4 file
  • Convert to GIF with an on-line app, such as: MP4 to GIF Converter

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:

As mentioned above, the output options include:

Bending moment:

Shear force:

Slope:

and deflection:

Posted in Animation, Beam Bending, Charts, Excel, Frame Analysis, Link to Python, Newton, PyXLL, UDFs, VBA | Tagged , , , , , , , , , , | Leave a comment

py_ConBeamU and Strand7 check updates

Posted on 11/12/2025 by dougaj4

Following the previous post, I have updated the py_ConBeamU spreadsheet, and the associated check against Strand7 results.

The new files can be downloaded from:

py_ConBeamU.zip

The download file now includes:

  • py_ConBeamU.xlsb: The complete spreadsheet, including examples of all functions
  • py_ConBeamU-Check25-2.xlsb: As above, also including the input for all the Strand7 checks
  • Check py_ConBeamU-4Dec25.xlsb: Summary of check results, including ConBeam and Strand7 results and graphs for all 90 cases.
  • py_ConBeamU-check BeamAct-Dec25.xlsb: Check of BeamAct function against Strand7 results
  • Beam_Act2.py: The main Python code for the beam analysis functions.
  • pyNumpy.py, py_Units4Excel.py, Glob_Loc.py: Required associated Python code
  • ConBeam-S7.zip: Strand7 data file and results

For more details of the spreadsheet and included functions see py_ConBeamU and the following associated posts.

Typical results of Strand7 check:

Posted in Beam Bending, Excel, Frame Analysis, Link to Python, Newton, PyXLL, UDFs | Tagged , , , , , , , | 1 Comment

ConBeamU and Strand7 check update

Posted on 05/12/2025 by dougaj4

Results of the continuous beam spreadsheet were last checked against Strand7 results 10 years ago. I have now updated this check with the latest version (4.19), which can be downloaded from:

ConBeamU.zip

The download file now includes:

  • ConBeamU.xlsb: The complete spreadsheet, including examples of all functions
  • ConBeamU-CheckDec25.xlsb: As above, also including the input for all the Strand7 checks
  • Check conbeamU-Dec25.xlsb: Summary of check results, including ConBeam and Strand7 results and graphs for all 90 cases.
  • ConBeamU-Template.xlsb: Spreadsheet including all VBA code and required units data, but with all examples removed.

The Strand7 check has 15 different span arrangements, each with 1 of 6 different support conditions:

The span arrangements and support conditions are listed on Sheet1 of the summary spreadsheet:

On the ConBeamU-CheckDec25 spreadsheet the Conbeam Check tab is set up with all 15 span arrangements, and the support restraint type can be selected by entering 1-6 in the “Run Type” cell (F2):

The results for the 6 different support conditions are copied to Sheets 1-6 of the Check conbeamU-Dec25 spreadsheet, together with the equivalent Strand7 results:

Both Conbeam and Strand7 results for shear, moment, slope and deflection are plotted for any selected “Span Type” (1-15):

The main ConBeamU spreadsheet has help on each of the available functions on the Functions tab:

The Strand7 checks used the ConBeam function, which requires consistent units. The ConBeamU function allows a wide range of different units to be used:

See the “Ext Unit List” tab for a list of all recognised units, and see Using ConbeamU for more information on adding to the units list, and more details on using the available functions.

Posted in Beam Bending, Excel, Frame Analysis, Newton, UDFs, VBA | Tagged , , , , , , , , , | 1 Comment