IP2_py Update for Python3

The spreadsheet IP2_py provides a large number of geometric functions, including intersection points of lines, circles and planes, interpolation, rotations, and converting between coordinate systems.  Many of the functions are available in VBA and Python versions.  The Python code has now been updated to work with Python 3.  The updated files can be downloaded from:


Examples of the included functions are shown in the following screenshots:

IP() and IP_4 find the intersection points of 2 lines or polylines:

The Circle Functions sheet contains functions to find the intersection points or lines, circles and spheres, and the 3D centre of circles or arcs:

The PDist function returns the perpendicular distance from point to a polyline:

MaxDist returns the maximum distance between any two points in a list:

RtoP and PtoR convert coordinates between rectangular and polar systems.  Rotate returns coordinates of one or more points, rotated about a specified axis:

IP_3D finds the intersection point of two 3D lines:

The 3DPlaneIP sheet contains functions to find the intersection of planes and related functions:

The PView function returns a perspective projection of coordinates viewed from a specified angle and distance.

The Interp functions provide linear, logarithmic and quadratic interpolation in 1 or 2 dimensions:

The Glob_to_Loc and Loc_to_Glob transform vectors between different coordinate systems:

Glob_to_Loc3 rotates plate or 3 node beam  deflections or forces between global and local axis systems:

Fit_Circ3D returns a least-squares fit of a 3D circle or arc to a set of points:

The Python based functions require Python, Numpy and xlwings. These are installed by default in the Anaconda Python installation.

Posted in Coordinate Geometry, Excel, Link to Python, Maths, Newton, NumPy and SciPy, UDFs, VBA, xlwings | Tagged , , , , , , , , , , | Leave a comment

Beam geometric non-linear effects

A quick and convenient way to allow for the effect of deformations on the behaviour of beams and columns under axial load is to adjust the beam stiffness matrix, so that the calculated deflections are in equilibrium with the applied loads, including the eccentricity of the axial load due to the transverse deflections.  Further background to this approach is presented in the book linked in the previous post:  Matrix Structural Analysis.

The geometric stiffness matrix for a 1D beam (from the link above) is:

I have incorporated this approach into a spreadsheet that will calculate deflections, shear forces and bending moments for a beam subject to combined axial load and a uniform transverse load.  The spreadsheet, including full open source code, may be downloaded from:


Note that the spreadsheet uses the Strand7 axes convention, which changes the sign of some of the matrix values:

Input and output for the Axbeam function is shown below.  The beam may be subdivided into any number of segments, each of which may have different materials properties. Deflections or shear forces and bending moments are output at the end of each segment.

Typical input and output is shown below for a beam with 16 segments, and 2 different material properties.  Results are shown for an axial load of 1000 kN, and also 1800 kN, which is close to the buckling load:

Output deflections are shown below for the two load cases, compared with results for the same beam in Strand7.  For the lower load the results are almost identical.  For the higher load the iterative procedure used in Strand7 has resulted in significantly smaller deflections:

A similar trend is seen for bending moments and shear forces:


The MaxDefAx function returns the maximum absolute deflection for a series of axial loads. In the example below the beam has the same EI value over the full length.  It can be seen that the predicted buckling load matches the Euler buckling value very closely:

The beam end conditions may be adjusted.  In the example below the moment fixity has been removed at both ends.  The spreadsheet buckling load again matched the Euler value almost exactly (the maximum value in the table has been reduced below the buckling load, to show the shape of the deflection curve more clearly).  When one or more end fixities are released, end loads may be applied to that freedom.

Posted in Beam Bending, Excel, Finite Element Analysis, Frame Analysis, Newton, UDFs, VBA | Tagged , , , , , , | 2 Comments

Frame Analysis Resources

I recently discovered:

Matrix Structural Analysis, 2nd Edition, by McGuire, Gallagher, and Ziemian.

which is available for free download:


The aims of the first edition of Matrix Structural Analysis were to place proper emphasis on the methods of matrix structural analysis used in practice and to lay the groundwork for more advanced subject matter. This extensively revised Second Edition accounts for changes in practice that have taken place in the intervening twenty years. It incorporates advances in the science and art of analysis that are suitable for application now, and will be of increasing importance in the years ahead. It is written to meet the needs of both the present and the coming generation of structural engineers.

The book includes a link to a free Matlab based frame analysis program:


For those without Matlab, there is a compiled version (also free).

Posted in Finite Element Analysis, Frame Analysis, Newton | Tagged , , | 1 Comment

xlwSciPy update for Python 3

The xlwSciPy spreadsheet allows a wide variety of the science and maths functions in the Python Numpy and Scipy libraries to be called directly from Excel.  The spreadsheet has now been updated for use with Python 3.  The spreadsheet and all associated Python code can be downloaded from:


The spreadsheet requires installed copies of Python, including Numpy and Scipy, and xlwings.  The easiest way to install the required packages is to install the Anaconda Python library.  When the required Python packages are installed simply copy the contents of the xlwSciPy3.zip file to any convenient folder.

The functions included in the spreadsheet are indexed below:


Posted in Coordinate Geometry, Differential Equations, Excel, Link to Python, Maths, Newton, Numerical integration, NumPy and SciPy, Python Pandas, UDFs, VBA, xlwings | Tagged , , , , , , | Leave a comment

Jørn Utzon and David Moore

Tomorrow (9th April 2018) is the centenary of the birth of Jørn Utzon, the architect of the Sydney Opera House, which is celebrated in an article at the Sydney-Morning Herald.

The article quotes a grand-son of Utson, Mika Utzon Popov, on the role of David Moore in documenting the construction work and the finished structure:

But he does know that his grandfather wanted the world to view the Opera House through the eyes of David Moore, the photographer who documented progress at Bennelong Point in the 14 years it took to build. His grandfather wrote to Moore saying he hoped his photos would be a lasting legacy for the nearly 10,000 construction workers who built it.

“I hope your photos can be seen in Australian magazines because the Sydney Opera House needs to be seen with a great artist’s eye to make people understand its poetic qualities,” he wrote.

See more of the work of David Moore at: 100 photographs portfolio

Posted in Arch structures, Bach, Newton | Tagged , , , | Leave a comment

Two sources of perpetual motion

The buttered cat phenomenon:

The pulling-yourself-up-by-your-boot-straps technique:

Posted in Bach | Tagged , , | Leave a comment

Plotting Charts With Gaps …

“How do you get VB6 to initialize doubles with +infinity, -infinity and NaN”… and various sorts of Not a Number.

Back in 2012 (When is an xy chart not an xy chart) I concluded that it was not possible to return an array with empty rows from a user defined function (UDF), and as a result it was not possible to generate a series with gaps in an x-y chart, using a UDF or an array function.

Recently Lori Miller has provided two solutions to this problem (see comments at the link above).  First the easy way:

For users of Excel 365 there’s now an option to “show #N/A as an empty cell” under hidden and empty cell settings dialog.  Right-click on a chart and select “select data”:

Click “Hidden and Empty Cells” and select “Show #N/A as an empty cell:

The XY chart will now display a break between the two values either side of the #N/A, and will retain all the features of an XY or scatter chart.

For those not using Office 365 it’s not so easy , but the spreadsheet SpecialNum.xlsb, using code from the Stackoverflow discussion at “How do you get VB6 to initialize doubles with +infinity, -infinity and NaN” (link also provided by Lori Miller) has a solution.  This spreadsheet has a function returning various types of “not a number”, or NaN.  Nan index 3 returns a value that is treated as a blank cell in XY charts (cell C11 below):

This value is the only one that produces the desired result.  Index values 0 to 2 and 4 to 5 turn the chart into two vertical lines either side of the NaN:

Index 6 retains the rest of the chart, but has two vertical lines either side of the gap going up (presumably to infinity):

Index 7 is similar, but with the lines going downwards.

Index values 8 to 10 plot as 0:

For those interested in the details, the download file has open-source code for generating the different NaN values, and also a table listing the numbers and their features:

Posted in Arrays, Charts, Charts, Excel, UDFs, VBA | Tagged , , , | 5 Comments