Excel to Alglib via xlwings

Posted on 11/08/2017 by dougaj4

The Alglib library provides many valuable numerical analysis routines, but the VBA version is no longer actively maintained.  The latest versions are available in C++, C#, and Python, of which the Python version (in conjunction with xlwings) provides the easiest interface with Excel.

An Alglib/Python based spreadsheet has previously been presented here.  I have now updated this to the latest versions of xlwings and Alglib, added additional solver and linear algebra functions, and updated the Python code to make better use of the xlwings auto-import functionality, which generates the necessary VBA code at the touch of a button.  The new spreadsheet can be downloaded from:

xlAlglib.zip

As usual, the download files include full open source code.

To install the necessary files:

  1. Install Python, either version 2 or 3.  The Anaconda Python package will also install xlwings, but see below.
  2. Install or update to the latest version of xlwings (0.11.04 at the time of writing).  If the automatic install delivers an earlier version, see here.
  3. Install Alglib.
  4. Copy the contents of the xlAlglib zip file to any convenient location.
  5. Any problems, please let me know.

The screenshots below show the functions available in the new version.  Future posts will look in more detail at specific examples, and procedures for modifying the Python code.

List of functions:

Spline Functions:

Parametric Splines:

Rational Interpolation Function:

2D and 3D Interpolation:

Fit a spline to scattered data:

Polynomial fitting:

Non-Linear Fitting:

Fit Polynomial to scattered data:

Radial Basis Function Interpolation:

Levenberg-Marquardt optimisation:

Integration of Cubic Splines:

Differentiation of Cubic Splines:

Matrix algebra:

Posted in AlgLib, Curve fitting, Excel, Link to Python, Maths, Newton, Numerical integration, UDFs, VBA, xlwings | Tagged , , , , , , , , , | 12 Comments

Installing xlwings 0.11.4

Posted on 30/07/2017 by dougaj4

The 0.11 versions of xlwings have some major changes to the arrangement of the required files, which will make installation and updating much easier, but for some reason Anaconda is very slow to upload the new versions, and the pdf documentation has some omissions, so here is a summary of procedures for downloading and installing the latest version.

Finding the latest version

The xlwings web site shows the latest version.  The source code may be downloaded from the GitHub page, but for easier installation use pip or conda:

pip install xlwings or conda install xlwings

The main conda install is still on version 0.10 however, so for those using the Anaconda Python package, and wanting to stick with conda for updates use:

conda install –c conda–forge xlwings

Install the add-in

  1. If you have the old xlwings addin installed, find the location and remove it or overwrite it with the new version (see next step). If you installed it via the xlwings command line client, you should be able to do: xlwings addin remove.
  2. Close Excel. Run xlwings addin install from a command prompt. Reopen Excel and check if the xlwings Ribbon appears. If not, copy xlwings.xlam (from your xlwings installation folder under addin\xlwings.xlam manually into the XLSTART folder. You can find the location of this folder under Options > Trust Center > Trust Center Settings… > Trusted Locations, under the description Excel default location: User StartUp. Restart Excel and you should see the add-in.

Upgrade existing workbooks

  1. Make a backup of your Excel file
  2. Open the file and go to the VBA Editor (Alt-F11)
  3. Remove the xlwings VBA module
  4. Add a reference to the xlwings addin, see Installation
  5. If you want to use workbook specific settings, add a sheet xlwings.conf, see Workbook Settings

For more details see the xlwings documentation.  For the current version (0.11.4) the pdf documentation has the “Migrate to Version 0.11” and “installation” sections missing.  This will be fixed in future versions, but for now use the on-line docs.

Posted in Excel, Link to Python, VBA, xlwings | Tagged , , | 1 Comment

Using the Frame Analysis Spreadsheets

Posted on 20/07/2017 by dougaj4

Following some recent questions in the comments, here are some answers:

How can nodal moments be created?

The current versions only allow for forces to be applied to beams, but any point load or moment applied at the start or end of a beam will in effect be applied at the node, so set “Position” to zero for any beam starting at the node, or to the beam length for a beam ending at the node.  In Frame 4 (the 2D version) all moments are applied about the Z axis.  In 3DFrame moments are applied about the global axis specified in “Direction XYZ” (Column C).

How can hinge or spherical joints, or bushing joints be created?

Both Frame4 and 3DFrame allow either end of a beam to be given a rotational spring stiffness.  If the stiffness is set to a very low value the beam ends will in effect be free to rotate.  In Frame4 rotations are about the Z axis.  In 3DFrame the rotations are relative to the beam principal axes, which are described here:  3D Frames, axes and stiffness matrices.

3DFrame also allows beams ends to be allocated a translational spring release along any of the three principal axes.

The example frame in each of the download files illustrates the use of the beam end springs.

How can multiple point restraints be applied?

Nodes can in effect be restrained to move together by connecting with very stiff members, with any required end hinge conditions, to provide the required restraint.

 

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

Installing Frame4 and 3DFrame

Posted on 17/07/2017 by dougaj4

Following a recent comment I have added a VBA only version to the download files of the frame analysis spreadsheets:

3DFrame.zip

Frame4.zip

The VBA versions should work on any version of Excel from 2007, with macros enabled, without any further installation.

The standard versions link to compiled versions of the solver code, that provide much better performance with large frames.  The download file includes the required dll files, and the installation process is summarised here:

Installing C# dll files, reminder

Any problems, please leave a message here.

 

Posted in AlgLib, Excel, Frame Analysis, Link to dll, Newton, VBA | Tagged , , , , , , | 1 Comment

Tam Lin

Posted on 11/07/2017 by dougaj4

Tam Lin is a Scottish folk tale and song dating from 1549 or earlier. It is set at Carterhaugh (not Carter Hall), which is a real place.

p1070228

The song was widely re-interpreted in the British folk revival, most notably by Fairport  Convention in their 1969 album Liege and Lief.  The recording below is an excellent live version recorded for the BBC’s Peel Sessions:

The song was also recorded by The Pentangle (for a 1972 film that somehow entirely escaped my attention at the time), but not released until many years later.  The You Tube video has been viewed just 52 times up to today:

Finally a very different version from Steelye Span, which sounds better each time I listen:

Lyrics of the Fairport Convention version:

“I forbid you maidens all that wear gold in your hair
To travel to Carter Hall for young Tam Lin is there

None that go by Carter Hall but they leave him a pledge
Either their mantles of green or else their maidenhead”

Janet tied her kirtle green a bit above her knee
And she’s gone to Carter Hall as fast as go can she

She’d not pulled a double rose, a rose but only two
When up there came young Tam Lin says “Lady, pull no more”

“And why come you to Carter Hall without command from me?”
“I’ll come and go”, young Janet said, “and ask no leave of thee”

Janet tied her kirtle green a bit above her knee
And she’s gone to her father as fast as go can she

Well, up then spoke her father dear and he spoke meek and mild
“Oh, and alas, Janet,” he said, “I think you go with child”

“Well, if that be so,” Janet said, “myself shall bear the blame
There’s not a knight in all your hall shall get the baby’s name

For if my love were an earthly knight as he is an elfin grey
I’d not change my own true love for any knight you have”

Janet tied her kirtle green a bit above her knee
And she’s gone to Carter Hall as fast as go can she

“Oh, tell to me, Tam Lin,” she said, “why came you here to dwell?”
“The Queen of Faeries caught me when from my horse I fell

And at the end of seven years she pays a tithe to hell
I so fair and full of flesh and feared it be myself

But tonight is Hallowe’en and the faery folk ride
Those that would their true love win at Miles Cross they must buy

So first let past the horses black and then let past the brown
Quickly run to the white steed and pull the rider down

For I’ll ride on the white steed, the nearest to the town
For I was an earthly knight, they give me that renown

Oh, they will turn me in your arms to a newt or a snake
But hold me tight and fear not, I am your baby’s father

And they will turn me in your arms into a lion bold
But hold me tight and fear not and you will love your child

And they will turn me in your arms into a naked knight
But cloak me in your mantle and keep me out of sight”

In the middle of the night she heard the bridle ring
She heeded what he did say and young Tam Lin did win

Then up spoke the Faery Queen, an angry queen was she
Woe betide her ?ill-fought? face, an ill death may she die

“Oh, had I known, Tam Lin,” she said, “what this knight I did see
I have looked him in the eyes and turned him to a tree”

Read more: Fairport Convention – Tam Lin Lyrics | MetroLyrics

Posted in Bach | Tagged , , , | 2 Comments