Scarborough Fair is a very old song, according to Wikipedia dating back at least to 1670, and probably much earlier. Here are three versions from the mid-sixties.
The first from Martin Carthy was released in 1965, on his first album:
The second,from Marianne Faithfull, was released in 1966 on the album North Country Maid:
The third, from Simon and Garfunkel, was released later the same year, and of the three is by far the best known:
I have uploaded version 2.18 of the IP2 spreadsheet to:
The new version includes updates to the IP, IP_4, and ArcSpline functions, as described below. The download is free and includes full open-source code.
The IP and IP_4 functions find all intersections of a pair of linear splines. The previous version allowed for only up to one intersection point per segment. This has now been modified to allow for the maximum number of possible intersections, as shown in the screen-shot below:
Other changes are:
The ArcSpline code has been modified so that it is no longer necessary to enter a sign for the radius of each arc. All arcs may be input as positive, and the function determines the placement of the arc centre from the directions of the two tangent lines:
The example above shows the final point defined without a radius, and the CloseArc optional argument set to False.
The ArcSpline function (and many other functions in this spreadsheet) returns an array of variable size, depending on the number of arcs listed, and the number of segments for each arc. The output listing may now be either automatically extended to show the complete array, or shrunk to a smaller range.
To reduce the size of the output range, select the desired range and press Ctrl-Shift-R:
To display the full results array, select the top-left hand cell, then press Ctrl-Shift-S.
The screenshot below shows a split screen after the output has been restored to its full extent, then the last point of the input was deleted, and the CloseArc argument was deleted. This results in the last line of the output displaying as #N/A, since the output array length is now one line less.
Press Ctrl-Shift-S again, with the top left cell selected, and now only the extent of the output data is displayed:
The xlwings Scipy spreadsheet has been updated with a new example of the xl_SolveF function, that uses the Scipy Optimize root function. The new spreadsheet can be downloaded from:
The new example uses a python function ic_calc (included in the download file) to find the ultimate reaction forces and moment for a group of bolts with specified load eccentricity and angle, and yield displacement and force. The calculation procedure is detailed at:
CE591eccentric_shear_F13.pdf
The shear force on each bolt is non-linear, depending on the bolt strength and shear displacement, and the displacement in each bolt depends on the position of the centre of rotation. The Scipy root function adjusts the centre of rotation coordinates and the maximum bolt displacement so that the reaction forces and moments are equal to the applied forces and moment:
Results can be compared with a VBA spreadsheet doing the same calculation but using the Excel Solver, from Yakpol’s Spreadsheet Solutions for Structural Engineering.
Note that to generate the same result as my spreadsheet
The xl_SolveF function passes data to the function to solved as two arguments:
An interface function is therefore used to extract the input parameters for the ic_calc function in the correct format, as shown below:
def ic_check(IC, vals):
xloc = vals[1][0]
yloc = vals[1][1]
vals2 =vals[1][2]
res1 = xlic_calc(IC,xloc, yloc, vals2)
totX = res1[0]
totY = res1[1]
totM = res1[2]
Pu =IC[2]
Pux = res1[3]
Puy = res1[4]
Mu = res1[5]
return [totX-Pux, totY-Puy, totM-Mu]
@xw.func
@xw.ret(transpose = True)
def xlic_calc(IC, xloc, yloc, vals):
xloc = np.array(xloc)
yloc = np.array(yloc)
ecc =vals[0]
theta = np.radians(vals[1])
deltamax = vals[2]
Rult = vals[3]
num_bolts = len(xloc)
ICx = IC[0]
ICy = IC[1]
Pu = IC[2]
Pux = Pu * np.sin(theta)
Puy = Pu * np.cos(theta)
Mu = Pu * (ecc-ICx*np.cos(theta)-ICy*np.sin(theta))
xIC = xloc - ICx
yIC = yloc - ICy
di = np.sqrt((xIC*xIC)+(yIC*yIC))
dmax = max(di)
deltai = di/dmax * deltamax
ri = Rult * (1-np.e**(-10.0*deltai))**0.55
fx = yIC * ri / di
fy = xIC * ri / di
moment = ri * di
totX = sum(fx)
totY = sum(fy)
totM = sum(moment)
return [totX, totY, totM, Pux, Puy, Mu]
I don’t know how I missed this, but I just discovered yesterday:
More about the album: The Very Thought of You
On her second solo album the Grande Dame of British folk is presenting us with a surprising, inspired selection of songs. Most of the songs on this album come in pairs, and each has a story attached. Stories referring to Norma’s childhood memories or commenting contemporary events, most of them connected to famous personalities in the world of music; people as different and remarkable as Nick Drake, Judy Garland and Freddie Mercury. The mood is quiet yet lit by Norma’s powerful and passionately smooth voice, revealing new dimensions to well-known songs. Featuring Richard Thompson, Danny Thompson, Martin Carthy and Eliza Carthy.
I have recently returned to using Pyxll to link Excel to Python (of which more later), which required the installation of a 32 bit version of Python 3.7. First trials after installation returned the message: “AttributeError: ‘module’ object has no attribute ‘CLSIDToPackageMap’“.
Fortunately someone else had had the same problem, raised a question on Stackoverflow, then answered his own question about 12 hours later:
After deleting C:\Temp\gen_py, the code above works again. Hope it can save trouble!
The same trick also worked for me, and is continuing to work with no further problems.
My gen_py folder was located at c:\Users\douga\AppData\Local\Temp\gen_py\, even though my Python installation was on my D: drive, and just in case anyone might be concerned about deleting a system folder, it contains the message: “# Generated file – this directory may be deleted to reset the COM cache…”
Newton Excel Bach, not (just) an Excel Blog 