More circle fitting options with Scipy

A comment on the first post on this topic found that the method used did not work well for short arcs of large radius circles.  An example of the problem can be seen in the screen shots below:

The function has returned an arc with a radius of only 85 m (rather than 6000), and the plots below show that the generated arc is a very poor fit to the data:

The comment pointed to the following page at the SciPy CookBook:

Least squares circle

which provides Python code for 5 alternative fitting methods:

  1. Solve linear system with linalg.solve
  2. Basic usage of optimize.leastsq
  3. Advanced usage of optimize.leastsq, with jacobian
  4. Basic usage of orthogonal distance regression (ODR) with an implicit function definition
  5. Advanced usage of ODR, with jacobian

To accommodate these alternatives in the Fit_Circ3D function I have added an optional “method” argument to the function.  Method 0 calls the original routine.  Methods 1-4 call the SciPy methods listed above (default method = 3 if the argument is omitted).  I have not been able to get method 5 to work properly from my code, so at the moment this option is not available.

The results with method 3 of the new code are shown below, showing a good fit to the scattered data, even with a short large radius arc  (click on the image for full-size view):

The code for the new methods (adapted from the original so that it can be called from Excel, via xlwings) can be found in  This file, the spreadsheet IP2_py.xlsb, and the associated Python file are all available for free download from:

In addition to Excel, the following (free) software is required by the spreadsheet:

  • Python
  • Numpy and Scipy
  • xlwings

All are included in the default Anaconda Python installation.


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

Seven -Up

A very nice version of John Renbourn’s “Seven Up” recently discovered on You Tube:


… which led me to another piece by the same artist:

… and a live version of Sweet Potato by Renbourn himself.


Posted in Bach | Tagged , , , | Leave a comment

Year 10 Report

Last years’ statistics for this blog are now uploaded to Onedrive, and since this is now 10 years since the blog started, I have also included statistics since the start. The link to each post is preserved in the spreadsheet, so it makes a convenient index to what has been posted over the year, and what people are looking at from previous years. You should be able to access the links in the window below, or open the file in your browser or Excel, or download it.

Of the 2017 posts, the most popular overall was:

Weighted Least Squares Regression, using Excel, VBA, Alglib and Python

The most popular in the Newton category was:

The Conjugate Beam Method

and the most popular in the Bach category was:

Three tributes to John Clarke

From the “deserving but sadly neglected category” I have chosen (and they are all worth a look/listen):


Brent’s Method; Update and Examples


Setting up UDF Applications


Tam Lin

Over the 10 years of the blog I have selected the most popular post, and two runners up in each category:


Using LINEST for non-linear curve fitting

Units for Excel

Solving higher order polynomials

In the Newton category:

Frame Analysis with Excel – 4, 2D frame analysis

Elegant solutions, Column buckling, and the hole through the middle of the Earth

Reinforced Concrete – ULS capacity under combined axial load and biaxial bending

and in the Bach category:

George Gently, Matty Groves, and Ebony Buckle


Fine as a Bee’s wing

Posted in Bach, Excel, Newton | Tagged , , | 1 Comment

Elegant proofs – more Pythagoras

Browsing the Plus Magazine site recently, I was struck the proof of Pythagoras’ Theorem shown in the animation below:

Not just because it is an elegant proof, but also because of the author, the 20th President of the United States, James Garfield.

Posted in Maths, Newton | Tagged , , | Leave a comment

More Python rotation functions

The code described in the previous post includes a Python function that rotates 3D coordinates by an angle defined with two vectors, using Rodrigue’s Rotation.  I have now added this function to the IP2_py spreadsheet, where it is used in the original application (fitting a circular arc to 3D data points), and also in the PView user defined function (UDF), to generate a perspective projection of any 3D framework.  The new spreadsheet and associated Python code may be downloaded from:

As well as the new functions, the update includes:

  • Updated Python and VBA code for the latest version of xlwings.
  • Updates to the Python GlobLoc3 and Gamma3 functions.
  • Python code re-named

Examples of the new PView and Fit_Circ3D functions are shown in the screenshots below:

As for the previous application, to use the spreadsheet:

  • If necessary, install Python, Numpy, and xlwings.  All the necessary packages are included in the default Anaconda Python installation.
  • Copy IP2_py.xlsb and to any convenient directory.
  • Run IP2_py.xlsb, and if necessary enable macros.


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

Fitting 3D circles to scattered points

Following a comment at update-to-glob_to_loc3-and-loc_to_glob3-functions, I have modified the Python code at the linked site, so it can be run from Excel, via xlwings.  The spreadsheet described below, and the associated Python code, can be download from:

The original code and background information can be found at: Fitting a Circle to Cluster of 3D Points.  The code performs the following functions:

  • Generates points along a circular arc, then applies a random 3D offset to these points, to generate a cloud of points close to the original curve.
  • Finds the best fit circle passing through these points.
  • Generates graphs of the points, and the best fit circles, using Matplotlib.

To simplify the process as far as possible, I have converted the code to two user-defined functions (UDFs) that can be called from Excel, using xlwings, to generate the cloud of points, and to return the coordinates of a series of points along the best fit circle, or along an arc extending over the range of the data.  This data is then plotted in Excel, using xy charts.

Typical spreadsheet input and output are shown in the screen shots below:

The Points_by_ang function generates points along an arc of the specified circle, with random 3D offsets of magnitude determined by the k factor.  The input data in the example is the same as is hard coded in the original Python code.  The Fit_circ3D functions returns 3D coordinates along the best fit arc or circle (or alternatively other results, as defined by the “out” value).  Note that if the number of generated points is changed from 100, the range must be adjusted in the fit_Circ3D function, so that all of the input data range contains real numbers, not #N/A# or blank cells.

The best fit circles, projected to the XY, XZ and YZ planes, are shown below:

The Matplotlib results from the original code are very similar:

Changing the input data, the graphs automatically re-draw to show the new results:

To use different input points (either generated from another source, or real survey data) simply paste the data anywhere in the spreadsheet, and adjust the “3DPoints” range in the Fit_circ3D function, and the chart data ranges for the input data.

To use the spreadsheet:

  • If necessary, install Python, Numpy, and xlwings.  All the necessary packages are included in the default Anaconda Python installation.
  • Copy 3DCirc.xlsm and to any convenient directory.
  • Run 3DCirc.xlsm, and if necessary enable macros.


Posted in Coordinate Geometry, Excel, Link to Python, Maths, Newton, NumPy and SciPy, UDFs, VBA, xlwings | Tagged , , , , , , , | 5 Comments

Cubic splines with jagged data

A cubic spline provides a good approximation to a smooth curve, and alternative versions are available for free download (see Daily Download 22: Splines and Curves, Update to AL-Spline-Matrix, and xlwSciPy 1.09 – update for xlwings 0.10 and Scipy 0.18.1), but if a curve has a sharp change of direction a single cubic spline will deviate significantly from the required values near the change.  An example of curves where this is a problem is a reinforced concrete moment-curvature diagram, which has sharp changes of slope at the cracking moment, and also at the reinforcement yield point.

To deal with curves of this sort, I have added an MSplineA user defined function (UDF) to the CSpline2 spreadsheet, which may be downloaded (including open-source code) from:

This function allows a curve to be divided into any number of segments, each of which may be either linear or cubic splines.  Input and output details, and a typical example are shown in the screen shots below:

Required input ranges are the X and Y values of the input data, a list of spline segments, listing the last node number of each segment, the spline type to be applied (1 = linear or 3 = cubic), and end curve or slope details for cubic splines, and the X values where interpolation is required:

Output for this example is shown below:

Looking more closely at the region near the cracking moments it can be seen that the MSplineA function has given a good approximation to the input values, whereas a single cubic spline deviates significantly:

Posted in Arrays, Beam Bending, Concrete, Curve fitting, Excel, Newton, UDFs, VBA | Tagged , , , , , , | 2 Comments