Binary functions, combining text, and finding unique columns

I recently had an e-mail request asking for a method of finding unique columns in an Excel table consisting of either blank cells or an entry of 1.  An easy way to do this would be to convert the columns to a binary value, then use the Countif function to count how many copies there were of each column.  Excel does not provide a convenient way to combine the contents of many cells into a single value, but a simple User Defined Function (UDF) to perform this task can be downloaded from Binary Functions.xls.

The Binary Functions spreadsheet uses code from: Visual Basic Code Examples , which provides code to convert binary to and from decimal, octal and hex values.  I have added code to create a binary value or text string from an Excel column (or row) of values.  Output from this spreadsheet is shown in the screen shot below:



Note that:

  • Excel has functions to convert between binary and decimal, but these are limited to a very small maximum value.
  • The BinRangeToString function assigns each cell a value of 0 for cells containing 0 or empty cells, or 1 for anything else.
  • The BinRangeToDec function returns a floating point decimal value, and will not be reliable for columns with more that 48 rows.
  • The TextString function returns a concatenated string of the contents of each cell.

The TextString function was previously presented at: Stringing more than two words together.  The TextString function has now been modified to display values formatted as “general” correctly, and to optionally allow blank cells to either be ignored, or included in the returned string.  The revised file may be downloaded from TextString.xls

Posted in Excel, UDFs, VBA | Tagged , , , , | Leave a comment

Pictures of Sicily

Trapani and Erice

This slideshow requires JavaScript.

Segesta, and Mozia

This slideshow requires JavaScript.


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

Another WW1 story

From the Youtube link:

I don’t mean to infringe on copyrights of artists, especially not respected ones like the Incredible String band, but I felt I should publish this track because of the strange tale attached to its origins. A tale that can be traced back to Rotterdam, my hometown. ‘Darling Belle’ is the final track of the Incredible String Band’s album ‘Liquid Acrobat As Regards The Air’ (1971). It was written by Robin Williamson, who said about this song: “I wrote ‘Darling Belle’ very quickly; or rather, wrote it down very quickly. I was lying in a hotel room in Rotterdam just before I fell asleep, and I began to hear these voices outside my head, and they were telling the story of Belle and James. Two voices, a man and a woman, and of what they said I jotted down fragments and the following morning I wrote parts of the song; about four months later I wrote the rest. It only lasted a few moments.

Papa would take me to the park to see the swans
By hansom cab trotting so high
Holding his hand to see the swans
Hissing louder than rustling dresses of gracious ladies bustling by

See swan ships come sailing in
White as the clouds on a windy day

James I suppose would be in school
James I suppose would be in school

I was I was learning to spell laughing at loud smells
Avoiding the rod of the cod faced master
Was it your absence made me quiet at noon?
Playing British Bulldogs on the gravel
Was it your presence colored my dream?

I burrowed in cupboards like a mole all Saturday
Under old chairs and old ladies knees
I framed your half remembered face
With frail white embroideries

Calling for you down the mousy garden
Calling for you down the mousy garden

O did you meet him at the ball? Eighteen years on
Tall soldier now and you full grown
Belle did you meet him at the ball?
Belle did you meet him at the ball?

O do you remember me? Thin girl with cold hands
You in your scarlet and you knew my name
Step to the veranda under the wisteria in the mysterious November

Dancing as if with death or fate to the moon black ballroom
Of the silk skinned lake
Kissing me you lifted my skirt under the willow trees

Keep the home fires burning though your heart is yearning
Though the boys are far away they dream of home
There’s a silver lining in the dark clouds shining
Turn that lining inside out till the boys come home

Did I see you march to the train? Did I cry was my nose red?
My two day bride can you feel me in your memory?
I will be the redness in your iron fire
How could I write? My words would seem sad or gay

We regret to inform you
We regret to inform you
We regret to inform you

Meet me by gaslight in the dark dawn
On waterloo bridge we will walk arm in arm
Hearing the leaves fall with whisper into the foggy dew
When we are dead, when we are dead

Now she sits in her brother’s widow’s house
Her skin like a lizard her aura like a daffodil
Sits like a sign in the children’s chair
Migrant guest from relative to in-law
She stares into the embers

Details of my daughter’s short film “Long Lost”, and to support crowd funding see:

Long Lost, a WW1 short film – crowd funding site


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

More on Long Lost

"Long Lost" extras

“Long Lost” extras

This weekend was shooting for my daughter’s short film “Long Lost” at Callan Park in Sydney. The film is based on the story of a French soldier, but coincidentally there was an Australian soldier who went through the same ordeal of losing all memory of his earlier life, and this man lived for many years at the same Callan Park, which was at the time a psychiatric hospital:

The history of forgetting, from shell shock to PTSD

For more details of the film, and to support crowd funding see:

Long Lost, a WW1 short film – crowd funding site

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

Run Fortran from Excel with Python and F2Py

I have previously posted on the use of the Silverfrost Fortran compiler with Excel (e.g. Linking Excel to Fortran).  More recently I have been concentrating on accessing high performance compiled routines via Python and the Python maths and science libraries Numpy and Scipy (and also the Alglib library, as featured in the previous post).  Numpy also includes a built-in library to link to Fortran code (F2Py), allowing the vast range of Fortran public domain software to be accessed from Python, and hence from Excel.

In this post I will describe linking to a simple function to generate Fibonacci Numbers, and a routine to generate the stiffness matrix for 3D frame analysis.  Later posts will look at incorporating the stiffness matrix routine into a complete Excel based 3D frame analysis program.

The first requirement is to install Python, including Numpy.  As mentioned in the previous post, I can recommend the Anaconda package, but any other package including Numpy should work equally well.

It is also necessary to install a Fortran compiler.  After some trial and error I found that the MinGW (Minimalist GNU for Windows) package worked well.  See Getting Started for installation instructions.

Having installed Python and a Fortran compiler, look for the F2PY folder, which on my system was at:  c:\Users\Doug\Anaconda\Lib\site-packages\numpy\f2py\.  I found the F2py User Guide invaluable for getting started.  If not included in the F2py docs it can be found at: F2Py pdf User Guide.

Having installed the necessary packages, the first sample program in the user guide generates a series of Fibonacci Numbers.  See the user guide for the Fortran code, and the procedure for generating a compiled .pyd file that can be called from Python.  Having generated a module called fib.pyd, containing a single function, fib, the function may be called with the following Python code:

import fib
def pyfib(n):
    return fib.fib(n)

Linking to this from Excel, using ExcelPython:

Function xl_Fib(n)
Dim res As Variant
    On Error GoTo rtnres
    Set res = Py.Call(Py.Module("framep44_2"), "pyfib", Py.Tuple(n))
    xl_Fib = Py.Var(res, 2)
    Exit Function
    xl_Fib = Err.Description
End Function

… which generates the results below:


The procedure for the stiffness matrix routine is similar. The Fortran source code was based on a function listed in Programming The Finite Element Method.  Having generated the compiled module, frame_for2.pyd, with F2Py, this can be called from Python with the code below:

import frame_for2 as ff2
def getkm3UDF_F2(propm, coord, gamrad):
    propm = propm[0]
    coord = np.array(coord)
    km = ff2.getkm3_f(propm, coord, gamrad)
    return km.tolist()

As before, Linking to this from Excel, using ExcelPython:

Function xl_km3(PropA As Variant, Coord As Variant, Gamrad As Double) As Variant

On Error GoTo RtnErr
PropA = PropA.Value2
Coord = Coord.Value2

Set args = Py.Tuple(PropA, Coord, Gamrad)
xl_km3 = Py.Var(Py.Call(Py.Module("framep44_2"), "getkm3UDF_F2", args))
   Exit Function
    xl_km3 = Err.Description

End Function

… which generates the 12 x 12 stiffness matrix


Posted in Excel, Finite Element Analysis, Fortran, Frame Analysis, Link to Python, Maths, Newton | Tagged , , , , , , , , | 2 Comments