ConBeamU 4.13

The continuous beam analysis spreadsheet, ConBeamU, is now up to version 4.13 with some minor bug-fixes, and the addition of a non-linear option for the BeamAct functions.

See ConBeamU for more details of the functions available in the spreadsheet.

Download the latest version from

The BeamAct2D and BeamAct3D user defined functions (UDFs) return beam actions and deflections for any beam subject to specified loads and end deflections.  The beam may have any number of segments with different section properties, and may be specified in either global or local coordinate systems.  The new non-linear option allows non-linear geometric effects to be taken into account.

Most of the function input is unchanged from previous versions:


The optional non-linear arguments specify if a non-linear analysis is required, the target relative error in maximum deflection, and the maximum number of iterations.  The support stiffness (translational and rotational) may also be supplied.  The default is fixed at both ends for both translation and rotation:


The output is before, with output at any number of specified locations:


Results are shown below for a 3D analysis of a 10 m long beam under combined axial and transverse load.  The blue and green lines are the results ignoring geometric non-linear effects, and the grey and red are the output from both the spreadsheet and a non-linear analysis in Strand7, with the beam divided into 16 segments:


Due to differing approximations in the two programs there are small differences in the shear results:


Bending moments are in good agreement.


The 2D function gives similar results:

conbeamu6-8Note that these functions are still under development.  All results must be independently verified using other software.

Posted in Beam Bending, Excel, Frame Analysis, Newton, UDFs, VBA | Tagged , , , , , , | Leave a comment

February Links

Two new links with valuable free resources:

.net and office automation follies : including many advanced VBA resources.  Thanks to Alfred Vachris for the link.

AET VBE Tools : Tools for the Visual Basic editor from Andrew Engwirda (via Daily Dose of Excel).

Posted in Excel, Link to dll, VBA | Tagged , , , | Leave a comment

ACO, Pekka Kuusisto, Sam Amidon, Pentangle and Wedding Dress

Murder and Redemption, the current ACO concert series, led by guest musical director Pekka Kuusisto and folk musician Sam Amidon, combines American and British folk songs with the works of Janáček and John Adams.

Here Pekka Kuusisto talks of performing the traditional song Wedding Dress:

and Sam Amidon performs it (from a previous concert, without ACO):

and finally a much earlier recording, from Pentangle in 1972, recorded for French TV:

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

Christy Moore sings Beeswing

Back in 2011 I posted a link to Richard Thompson’s song Beeswing, sung by The Little Unsaid.   That post still brings a trickle of visitors to his site, which led me to this version by Christy Moore:

and Christy Moore’s version of the lyrics:


Richard Thompson
I was 18 when I came to town they called it the summer of love
Burning babies burning flags the hawks against the doves
I took a job at the steaming way down on Caltrim St,
Fell in love with a laundry girl that was workin next to me.

Brown hair zig zagged across her face and a look of half surprise,
Like a fox caught in the headlights there was animal in her eyes,
She said to me can’t you see I’m not the factory kind,
If you don’t take me out of here I’ll surely lose my mind

She was a rare thing fine as a bee’s wing
So fine a breath of wind might blow her away
She was a lost child, she was runnin’ wild (she said)
So long as theres no price on love I’ll stay
You wouldn’t want me any other way.

We busked around the market towns fruit pickin down in kent
We could tinker pots and pans or knives wherever we went.
We were campin down the Gower one time, the work was mighty good.
She wouldn’t wait for the harvest, I thought we should.

I said to her we’ll settle down, get a few acres dug,
A fire burning in the hearth and babbies on the rug.
She said Oh man you foolish man that surely sounds like hell,
You might be lord of half the world,You’ll not own me as well


We were drinking more in those days our tempers reached a pitch
Like a fool I let her run away when she took the rambling itch.
Last I heard she was living rough back on the Derby beat
A bottle of White Horse in her pocket, a Wolfhound at her feet

They say that she got married once to a man called Romany Brown
Even a gypsy caravan was too much like settlin’ down
They say her rose has faded, rough weather and hard booze,
Maybe thats the price you pay for the chains that you refuse

She was a rare thing, fine as a bee’s wing
I miss her more than ever words can say
If I could just taste all of her wildness now
If I could hold her in my arms today…..
I wouldn’t want her any other way

Followed by this comment (which I thought was a pretty good answer to the incessant YouTube arguments about whose version of any song is the best):

It is never easy writing these words out. First there is the problem of the lyric as written. Invariably I need to turn these songs into my own dialect, into the english as I sing it. Sometimes I am unable to resist slipping back into the writers idiom when seduced by the beauty of the sound of a particular word. Then there is the bloody grammar and punctuation which can get in the way of writng a song as she should be sung (as distinct from the way Fr. Clandillon would have me write it!)

This song is, for me, a modern classic in the old style. Up there with Musgrave and Baker, Raggle and Yellow Bittern, it will survive the ages that are left and will shine brightly when us lads are long forgotton.

Its a beauty to sing, it is usually good but every now and then a version emerges that stills my night and leaves me totally satisfied at the last chord not caring about audience or next song or The Gig or anything, just to bathe in the luxury of a beautiful song shared and sung to a receptive kipful of listeners.


Posted in Bach | Tagged , , | Leave a comment

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

Least squares linear regression in Excel is easy.  That’s what the Linest and Trend functions do.  That is, they find the coefficients of a straight line (or higher dimension shape) so that the sum of the squares of the distances of each data point from the line is a minimum.  However, if we want to use weighted data (give the values at some points more importance than others), there are no built in functions to do the job.  This post looks at various options, including using Linest with modified input, VBA user defined functions (UDFS) and UDFs using the Alglib and Python Scipy libraries.  All the code used is free and open source, and may be downloaded from:

WeightLSq.xlsb (VBA and Linest versions) (Alglib version) (Python Scipy version)

The first spreadsheet uses VBA only.  The other two both use Python and require Python and xlwings to be installed.  The Alglib and Python downloads also include  wide variety of other functions.

The screen shot below shows some sample data with two known variables (X1 and X2) and an associated value Y.  We wish to find a linear equation of the form:
Y = A + B.X1 + C.X2
that fits the given Y values as closely as possible.


The first two results use the Linest function with no weighting.  In the first formula, the optional Const value is omitted, so Const is set to True, and the equation constant value (A) is calculated.  For this case the X range only requires the X1 and X2 values (columns B and C).  In the second formula Const is entered as False, so an additional column is required with X = 1.

Note that Linest returns the coefficients in reverse order, so our equation is given by:
Y = 22.12 + 0.0137X1  – 1.032X2

Linest can be used with weighted data by applying the weights to both the X and Y data, but to return the correct results the following points are important:

  • The weighted error values for each point are squared, so the weights are also squared, but the standard definition for weighted least squares applies the weight to the squared errors (see weighted linear least squares).  The Linest function should therefore be passed the square root of the weights.
  • The weights must also be applied to the intercept data (the column of ones), so the Linest Const value must be set to False, and the intercept column included in the X data, as for the second unweighted example.

The table of weight square roots may either be generated on the spreadsheet (Weighted Linest 1 above), or the square root can be applied within the Linest formula (Weighted Linest 2).

Results of VBA functions performing the least squares calculations (unweighted and weighted) are shown below:


Full open source code is included in the download file.  Note that the results are identical to those found by Linest, but returned in the reverse order.  For the weighted analysis the column of full weights is used as the input data.

The two screenshots below show an on-sheet calculation, using the same method as used in the VBA functions.  These are also included in the download file.




The Python Scipy library includes a least squares function, which is included in the xlw-SciPy spreadsheet.  A weighted version has now been added:


The Alglib library also has a least squares function, including both unweighted and weighted versions:


In the Alglib weighted function the weights are squared, as well as the error values (see Note 4 here).  The square root of the weights should therefore be used for consistency with the other functions.

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

Debugging with xlwings and PyCharm

The xlwings documentation covers de-bugging, but it is very brief and it took me some time to get everything working, so this article covers the process in a bit more detail.  The details are affected by the editor being used, and the process described here applies to PyCharm, Community Edition 2016.3.2.

The first example uses the xlwings Fibonnaci sample (download  The Python code must be amended as shown below, with the code for the standalone version commented out (or deleted), and code added to set up a “dummy caller”:

#if __name__ == "__main__":
#  Used for frozen executable
#    xl_fibonacci()

if __name__ == '__main__':
# Expects the Excel file next to this source file, adjust accordingly.

Note that:

  • The code must be modified for each routine to be de-bugged, with the name of the Excel file calling the Python routine, and the name of the Python function.
  • The current xlwings documentation, at the time of writing, has a typo.  The code uses the file name ‘myfile.xlsxm’, rather than ‘myfile.xlsm’, which causes a file not found error if copy and pasted. Update 30 Jan 2017:  Now fixed in the docs for Version 0.10.3.

For a new installation of PyCharm the Python Interpreter must first be configured.  When a new py file is opened the editor displays a “Configure Python Interpreter” link in the top-right corner, where you can connect to your Python executable, as shown in the screen-shot below (click on any image for a full-size view):


The chosen file can be set as the default with the File-Default Settings-Project Interpreter menu.
Having set the interpreter a de-bug session can be initiated by:

  • Insert a break-point at the desired location in the code (click in the left hand margin).
  • Use the Run-Debug menu to start the chosen routine:


It is then possible to step through the code using either the Run menu, the associated function keys, or the de-bug step icons under the main edit window.

The value of active variables is shown both adjacent to the code, and in the Variables window.

After the first de-bug run, Run and De-bug icons become active in the top right hand corner, and these can be used to start a new run.

De-bugging a user-defined function (UDF) has a couple of differences.  The  if __name__ == ‘__main__’ statement must be changed as shown below:

if __name__ == '__main__':

In the VBA code, the xlwings function Settings is amended with:

  •     UDF_DEBUG_SERVER = True

The de-bug process is now initiated in the same way as before.  PyCharm will display:

Connected to pydev debugger (build 163.10154.50)
xlwings server running, clsid={506E67C3-55B5-48C3-A035-EED5DEEA7D6D}

To step through the chosen UDF, go to Excel, select any copy of the function in a worksheet, press F2 and then Enter (or Ctrl-Shift-Enter for an array function), as for a VBA function.  PyCharm should then run to the first break point:


Stepping through the code then has the same options as when running a subroutine:



Posted in Excel, Link to Python, UDFs, VBA | Tagged , , , , , , | Leave a comment

Some thoughts on Magic …

… from Alana Hicks, Leonard Cohen, and Buffy St Marie.

Alana Hicks is a friend of my daughter’s.  This is her story:


… which reminded me of a song I heard once on the radio over 40 years ago, and I have always remembered. Words by Leonard Cohen, sung by Buffy St Marie:


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