I have now transferred the Ultimate Limit State design functions from the VBA RC Design Functions spreadsheet to Python format. The new spreadsheet and Python code can be downloaded from:
The code also includes the OptShearCap3600 function, described in the previous post.
The python code requires pyxll to connect to Excel. If this is installed, and “RC_UMom” is added to the list of modules to load at start-up in the pyxll.cfg file, the new functions should be available from any spreadsheet.
The included functions are:
py_UMom: ULS design of rectangular sections with two layers of reinforcement under combined bending, axial load, shear and torsion, to Australian and international codes. (Provision for prestressing will be added in the near future):
py_UmomPF: As above, but for Eurocode and British codes using a partial factor approach:
MaxAx: Maximum axial load for short or slender columns:
Devlength: Reinforcement development length to Australian, Eurocode, or British codes:
ShearCap3600: Shear capacity to current AS 3600 and 5100.5:
OptShearCap3600: Optimise shear capacity for given moment/shear ratio by adjusting the compression strut angle:
Extracts of the OptShearCap3600 function code are shown below. The scipy.optimize.brentq function is used to adjust the applied loads to be equal to the design section capacity:
# create list of arguments for the py_Umom function, called by the Scipy brentq function
args = [InCells, Puin, 13, 1, Muin, [] , ShearReo, Code, VTuin]
# Use scipy.optimize.brentq to adjust the input shear force to be equal to the design capacity
try:
res = sopt.brentq(call_Umom, mincap, maxcap, args = args, maxiter = 100)
except:
outA[0,0] = 1
return outA
# The call_Umom function adjusts the input loads in proportion to the shear force
# passed by brentq, then calls py_Umom and returns the difference between the
# applied shear and shear capacity
def call_Umom(Vstar, args):
args[8][0,0] = Vstar
args[4][0] = Vstar * MoV
if ToV != 0: args[8][0,1] = Vstar * ToV
if PoV != 0: args[1][0] = Vstar * PoV
res = py_Umom(*args )
return res[1]
Similar procedures are used to adjust the applied moment when this is critical, or the compression strut angle in the intermediate range, so that both shear and moment capacities are equal to the applied actions.
Further to the last post on this subject I have been looking at procedures to speed up design for shear to AS 3600 when the “refined” analysis procedure is used. The issues that need to be addressed are:
The design shear capacity reduces with increasing tensile strain at the mid-depth of the section, and the tensile strain increases with increasing applied moment and shear force. To find an accurate upper limit to the shear capacity it is therefore necessary to carry out an iterative analysis to match the applied shear force, and associated bending moment, with the shear capacity of the section. This is particularly important for the assessment of existing structures, where it is a requirement to find the actual maximum capacity of the structure, rather than designing for predefined maximum loads.
The maximum shear capacity may be controlled by the shear failure or by the combined effect of bending, shear and torsion on the longitudinal force on the tensile reinforcement.
As discussed previously, where the section capacity is controlled by the longitudinal tension force the design capacity can be increased by increasing the angle of the shear compression strut. This also requires an iterative process.
I have now written a Python function to carry out the iterations using the Scipy brentq function with the procedure outlined below:
From the input data calculate the ratios: moment/shear (M/V) and torsion/shear (T/V)
Adjust the shear force and associated actions so that the applied shear force equals the design shear capacity.
If the design bending capacity, reduced for the longitudinal force due to shear and torsion, is greater than the applied moment then return the results, else:
Set the shear compression strut angle to the maximum value allowed by the code (50 degrees) and adjust the shear force and associated actions so that the applied moment is equal to the reduced design bending capacity.
If the design shear capacity is now greater than the applied shear force then return the results, else:
Adjust the shear compression strut angle so the ratios Applied Shear/Design Shear Capacity and Applied Moment/Design Moment Capacity are equal.
Adjust the shear force and associated actions so that the applied shear force equals the design shear capacity.
Repeat 6 and 7 until the ratio of applied actions/design capacity equals 1 for both shear and bending.
Typical function output is shown in the screenshot below:
In this example the section has been analysed for a constant shear force with an increasing M/V ratio. The function output (Columns K to O) is:
Design shear capacity, and the associated bending moment for the input M/V ratio.
The “excess capacity” for shear and bending. Note that this has three zones where the capacity is initially controlled by shear only, then both shear and moment are at full capacity, then the adjusted bending capacity controls.
The compression strut angle (Theta). The strain values in Column P are calculated with a simple on-sheet formula.
These results are plotted below with typical shear and bending loads, taken from a heavy road vehicle travelling over an 8 metre simply supported span, with the loads plotted at 0.4 and 1.0 metres from the support. The capacity is also plotted with no adjustment of the compression strut angle, showing a significantly reduced capacity.
Plotting the section capacity as a moment-shear interaction diagram allows the loads from all sections with identical reinforcement to be plotted on the same section, and load cases where the applied loads exceed the section capacity are immediately visible.
This is a work in progress, but a spreadsheet with all necessary Python files will be available for download shortly.
There have been many posts here looking at alternative ways of working with functions entered as text on a spreadsheet, and working with units, most recently here.
One drawback with this approach is that text in an Excel cell must be ASCII or Unicode, which has limited capabilities for presenting maths text in a readable form. Excel does allow elaborate maths equations to be generated and displayed, but as far as I know there is no way to interact with these equations from VBA, so they can neither be generated by code, or evaluated by code.
Python on the other hand has libraries that will convert plain text equations to Latex (and other formats), and display Latex code. I have now written a Python function “plot_math” using a combination of Sympy, Pint, and Matplotlib to:
Read any maths function from the spreadsheet
Convert the text to Latex format
Return a graphic image of the function
Optionally evaluate the function using a table of values for each parameter
Optionally adjust for the units of the input and output values.
Startup_min.py includes the plot_math function, and should be added to the list of files to open at start-up in pyxll.cfg.
Note that this is a work in progress. If you have any problems with installation or running the programs, please let me know.
Some examples of the new function in action are shown below:
The text to be displayed (and optionally evaluated) should be entered as plain text at any chosen location. In the example below the plot_math function is then entered in the cell immediately above (C12).
When the plot_math function is entered the image will display immediately below:
The image can then be dragged to the desired location; in this case so that the original text and the function return value (0) are hidden. In this example the numerical value of the function for selected input values is displayed using the py_Evalu function.
Alternatively the plot_math function will return the value of the function if suitable input data is selected:
In this case a three column data range has been selected, and also an output unit, so the calculation will be unit aware:
As before, the image is displayed immediately below the plot_math input cell, but can be dragged to any desired location:
Some other features to note:
If the data range has only two columns (variable name and value) the calculation will have no units.
If the output unit is omitted, and the data range has three columns including units, the output value will be in base SI units.
Names of Greek letters (e.g. alpha) will generate the Greek character
In some case Greek letter names will be the name of a standard maths function (e.g. Gamma), in which case the text should be followed by an underscore (gamma_)
Some English letters also represent standard maths values (e.g. e and i). If an upper-case E or I is required, these should be entered followed by an underscore, and will then be displayed in upper-case, without the underscore.
The exponentiation symbol may be entered in either Excel (^) or Python (**) format. In either case the exponent will display as a superscript, and the calculation will be correct
The new version includes a number of corrections to the calculation of beam shear and torsion capacity to AS 3600 and AS 5100.5:
Shear capacity under negative bending moments has been corrected.
The sign of reported shear capacity is now the same as the input shear force (previously any input with negative moment returned a negative shear capacity).
The contribution of any negative torsion to longitudinal tension forces, to AS 3600, was taken as zero. This is actually in accordance with equation 8.2.7(3) of the code (since Amendment 2), but clearly the absolute value of the torsion should be used.
In addition, the new version allows the minimum angle of the concrete compression strut to be specified, between 29 and 50 degrees. The specified angle is used to calculate a minimum value for the mid-depth strain, which is also used in the calculation of the kv factor. Increase in the mid-depth strain value is allowed under Cl. 8.2.4.2 in both AS 3600 and AS 5100.5.
Use of the compression strut angle adjustment is shown in the screen-shots below:
A beam has design loads of 400 kNm bending moment, and 275 kN shear force. With 10 mm shear reinforcement the shear capacity is just adequate, but taking account of the longitudinal forces due to shear the bending capacity is reduced to 371 kNm:
Increasing the shear reinforcement to 14 mm diameter increases the shear capacity, but using the code calculated compression strut angle to AS 3600 the reduced bending capacity is unchanged. (Note that AS 5100.5, and earlier versions of AS 3600, have a substantial increase in the bending capacity when the shear reinforcement is increased. This is discussed further below):
Entering a compression strut angle of 49.6 degrees (with the “Use simplified” option set to “False”) reduces the shear capacity back down to the required value (275 kN), but the moment capacity is now increased to 416 kNm. Note that any further increase in the shear reinforcement would have negligible effect (to AS 3600) because the compression strut angle is already very close to the maximum value of 50 degrees:
The effect of increasing the shear reinforcement on the adjusted bending capacity with different codes and approaches is shown in the graph below:
The lines are:
1) AS 3600, Amendment 2 or 3, default compression strut angle.
2) AS 3600 with compression strut adjusted to maintain constant shear capacity.
3) AS 5100.5, default compression strut angle.
4) AS 5100.5 with adjusted compression strut angle.
5) As 3), but shear reinforcement force limited in accordance with the Canadian Bridge Code.
So what is going on here?
In AS 5100.5 (and AS 3600 up to Amendment 1) the longitudinal force due to shear is defined as:
Vus is proportional to the area of shear steel, and is not limited, so increasing the shear steel area allows the value of DeltaFtd to be reduced to zero, even though in reality the actual force in the steel cannot be greater than the applied shear force (V*) minus the concrete shear force. The large increase in bending capacity with increased shear steel shown by line 3) is therefore not realistic.
Increasing the compression strut angle (Thetav) with the AS5100.5 equation reduces cot(Thetav), which reduces Vus, but also reduces DeltaFtd. The resulting calculated bending capacity is still unrealistic for large areas of shear reinforcement.
In AS 3600 Amendment 2 the equation for longitudinal force due to shear was revised to:
If the shear capacity of the section is exactly equal to V* then this equation is equivalent to the AS5100.5 version, but increasing the area of the shear reinforcement has no effect on Vuc, so there is no reduction in DeltaFtd, and the bending capacity remains constant, as seen in line 1.
In this case however increasing Thetav, which reduces cot(Thetav) reduces both Vuc and the resulting value of DeltaFtd. Increasing the shear reinforcement area therefore allows Thetav to be increased (so that the shear capacity remains equal to the design shear force), which reduces DeltaFtd, with the nett result that the bending capacity is very close to the values found from the AS 5100.5 equation, until Thetav reaches the upper limit of 50 degrees, after which the bending capacity remains constant (line 2).
Finally the Canadian Code requires the value of Phi.Vus to be limited to V*. In this case this restriction is more conservative than the approach taken in AS 3600. Bending capacities are similar to the other results for small shear reinforcement areas, but the maximum bending capacity is significantly lower than that found with AS 3600 with adjustment of the compression block angle.
In summary:
The AS 3600 equation with default compression block angle is conservative, but shows no benefit from increased shear reinforcement area.
Applying adjustments to the compression block angle, in accordance with the code, the AS 3600 equation gives results very close to those from AS 5100.5, up to a reasonable limit (i.e. maximum angle of 50 degrees, equivalent to a maximum mid-depth strain of 0.003).
If the shear reinforcement area is increased well above the area required for the design shear force the AS 5100.5 equation gives bending capacity results that are highly unconservative.
If design is required to follow the current AS 5100.5 (Amendment 1), it is recommended that the Canadian code limit (Phi.Vus < V*) be applied.
Newton Excel Bach, not (just) an Excel Blog 