The latest version of the Python continuous beam analysis program pyCBA is 0.7, allowing for specification of trapezoidal distributed loads, and this is now available with pip. I have updated the py-xlCBA spreadsheet and associated code to use the latest version, and the revised files can be downloaded from:
As before the spreadsheet requires Python to be installed, as well as pyCBA (version 0.7 or later), and pyxll to handle the Excel/Python interface. See Python and pyxll for more details of pyxll, and a discount code for those opening a new pyxll account.
The distributed load input now has four columns, allowing for the load/m to be defined at the start and end of the load:
If the fourth column is not selected or left blank the load will be treated as rectangular. To define a triangular load a zero must be entered in the start or end column.
All analysis options are now available with the py_CBAcache function, which returns a single cell cache object, from which results may be extracted with the py_CBARes or py_CBAReact functions:
The Matplotlib graphics generated by pyCBA may be displayed in Excel using the py_CBAcache function (as shown above), or using py_CBA.
The detailed check against Strand7 results has been updated with trapezoidal loads:
The results of the 15 different span arrangements, each with 6 different support conditions can be seen in the file Check py_CBA-4Apr26-2.xlsb included in the download, showing very close agreement in all cases:
This file can be viewed without access to Python or any of the associated packages. Enter 1-15 in the “Span Type” cell (Y2) to view results from Strand7 and pyCBA on any of the 6 worksheets.
Following the previous post, more detailed checking found that the code was returning an error for beams with a support at X = 0. This has now been fixed, and the revised code and spreadsheets can be downloaded from:
I have also added the files used for the detailed check, analysing 15 different span arrangements, each with 6 different support conditions, analysed in the file py_CBA-Check26-4.xlsb, and compared with Strand7 results for the same span arrangements and support conditions (Check Beam-pyCBA-all-Apr26.st7). The spreadsheet and Strand7 results are copied to Check py_CBA-4Apr26.xlsb, which is all numerical data and can be opened in Excel without Python.
The spreadsheet calculation of the 15 beams is shown below, with split-screen view (click image for full-screen view):
The segment definition, with a total beam length of 32 m, and the applied loading are the same for all cases, but the spreadsheet has 15 different ranges defining support locations, and each support has 6 different options for support type, including pinned, fixed, specified deflections and/or rotations or deflection and rotational stiffness. For each of the 6 support conditions the py_CBA function results for the 15 beams were copied to the summary spreadsheet:
The Strand7 results are also copied to the summary spreadsheet, where the plots show near identical results, with both analyses appearing as a single line. The results for each of the 15 different support arrangement can be viewed by entering 1-15 in cell Y2.
The pyCBA code has now been updated to allow for supports with specified displacements (release 0.6 or later). My py_xlCBA spreadsheet and associated functions have been updated to call the pyCBA code for displacement analysis, rather than using my add-on functions. This has the advantages that it now handles rotations as well as vertical displacements, specified displacements may now be called from the py_CBACache function, as well as py_CBA, and the overall code is much simpler. The updated spreadsheet and Python code can be downloaded from:
The function input is as before except that the restraint displacement range has an additional column for specified rotations:
The next release of pyCBA (0.7) will allow for trapezoidal distributed loads, so the distributed load Load/m input has two columns to allow for that. For uniform distributed loads the second column may be left blank.
Results are shown below for a 3 span continuous beam with cantilevers at both ends and specified displacements and rotations at some supports:
Analysis in Strand7 give near exact agreement for all output:
The py_CBAcache function returns the pyCBA results as a cache object, from which the desired results may be xtracted with py_CBARes or py_CBAReact, without running the analysis again. The Python graphical output generated by pyCBA may also be transferred to Excel by setting the Plot_res input to TRUE:
The Strand7 data and results files for the example beam are included in the download file, and typical output is shown below:
I have added code to the 3DFrame-py module to allow simplified input for 2D frame analysis. The revised code and example spreadsheets can be downloaded from:
A quick plot of the function 2^x – 10x shows that there are solutions at x just greater than 0 and just less than 6:
The simplest and easiest way to get a close estimate of the exact solutions is to use the Excel Goal Seek function (although this wasn’t mentioned in any of the replies on Quora!). It is found under What-if Analysis on the Data tab:
The required inputs are an initial guess of the ‘x’ value and the function to be solved. In this example, since we are looking for the x value to make the function zero, I have multiplied it by 1 million for greater precision. Goal Seek adjusts the value in cell D6 to make cell E6 very close to zero:
In this case starting with x = 1 returns a result of 0.107755, and x = 5 returns 5.877011.
The most frequent solution given in the Quora discussion was to use the Newton-Raphson method to find the two solutions by iteration. This can be done in Excel using the py_Brent function (which is a refinement of the Newton-Raphson method), that calls the Scipy brentq function, and is included in the Scipy download linked above. There is also a VBA version that can be downloaded from:
The py_Brent function requires an input lower and upper bound for the solution, which must evaluate to values with different signs, and a text function in Python lambda format. Note that exponentiation must be in Python format (**), rather than ^.
The third alternative is to use the Lambert W function, which is defined by W(z) = w, where we^w = z. The function has an infinite number of solutions with complex numbers, but for real results there are just two, indicated as W(0) and W(-1). See Lambert W function for more details.
The Lambert W function can be used to find the desired results as shown below:
Scipy has a lambertw function, that can be called from Excel using the pyScipy py_CallfuncS function:
The required input is the function name (lambertw), the function input value and output index (0 or -1), entered in a column, and the number of input rows. The returned value is divided by LN(2) to find the required results.
The py_CallfuncS function does not allow for input or output of complex numbers. To allow that, I have added the py_LambertW function to the pyScipy module, included in the download at the top of the post.
For the current example the input Z value is specified in a single cell, but complex numbers may be specified in two adjacent cells, or multiple complex numbers in a 2 column range. The function also allows adjustment of the target tolerance value, and the option to return complex numbers. By default results are returned as real for real input or complex for complex input, but the function result may be complex for real input with K other than 0 or -1, in which case RtnComplex should be set to True.
The final results using the LambertW functions are as for the other options:
The examples shown above can be downloaded from: Lambert.zip
Newton Excel Bach, not (just) an Excel Blog 