Two songs = 2/3 Wikipedia

It is said that a picture is worth a 1000 words.

On the same basis, a poem is worth 1000 pictures, and a song is worth 1000 poems.

So two songs are worth 2 billion words, which is about 2/3 of all the words in Wikipedia.


Another Okenyo song with video directed by my daughter:

… and from Anais Mitchell “Before the Eyes of Storytelling Girls“:

I could tell you stories like the government tells lies
ah, but no one listens anymore
in the rooms, the women come and go
talking on the mobile phones
and the television talks about the war
when i was a baby, there was laughter in my house
my daddy smoked domestic cigarettes
and Thursday nights on the radio
live in concert- live from Cairo:
mother of Egypt!

mama, mama, be with me
with the music in your breast
in your glittering evening dress
and the white flag in your fist trembling

I could tell you stories like the past was dead and
but I know nothing changes in this world
every day the muezzin calls
the sun comes up and Baghdad falls
before the eyes of storytelling girls
she was just a poor man’s daughter
going down into the sultan’s bed
he was desert, she was water
and he remembered every word she said, that she said,

and I say, grandma, grandma, be with me
in your tragic wedding gown
with your long hair hanging down
and the stories tumbling out, tumbling

I could tell you stories like the government tells lies
ah, but no one listens anymore
in the rooms the women come and go
talking on the mobile phones
and the television talks about the war, about the war
the television talks about the war

Posted in Bach | Tagged , , | 3 Comments

New Links – 2018

Some new links to active Excel related blogs:

David Hagar has been active in the Excel community for many years, and has published the Excel For You blog since 2013.  I was led to it by a recent post on a UDF generating a list of antonyms.Excel For You.

Excel Matters is the blog of self-confessed Excel addict Rory Archibald.

My Spreadsheet Lab is run by Kevin Lehrbass and has a wide range of useful articles, plus numerous links valuable links.



Posted in Computing - general, Excel | Tagged , , , , | Leave a comment

Returning Arrays From VBA and Python

Most  of the  user defined functions (UDFs) presented on this blog return a multi-cell array, that must be entered by selecting the output range then pressing Ctrl-shift-enter to display all the results.  This process has several inconveniencies:

  • You need to know the size of the array, or work by trial and error.
  • It is easy to increase the size of the output range, by selecting the new range and re-entering, but there is no easy way to reduce the size.  You need to delete the array and re-enter.
  • If the size of the output array changes it must be re-sized manually, or it will display #N/A results, or only part of the array.

These problems can be resolved, at least partially, using VBA macros, or when using Python and xlwings, using the xlwings dynamic array functions.  Both methods are illustrated below using the Python Numpy spreadsheet from the previous post.

To use the VBA macros, enter the function in the top-left cell of the output range, and enter in the usual way:


Then press Ctrl-Shift-S and all of the UDF results will be displayed.  Note that any existing data within the output range will be deleted.


To re-size the array, select the required range and press Ctrl-Shift-R:


These VBA functions can be added to any Excel file containing UDFs that return array functions.  The code may be copied from the xlw_Numpy spreadsheet:

When using xlwings, dynamic arrays are available.  Simply add an @xw.ret(expand =’table’) decorator to each function where a dynamic array is required:

@xw.arg("x", np.array, ndim = 2, dtype = np.float64)
def xl_isfinite(x):
    return np.isfinite(x)

The function may then be entered in the top left cell of the output range:


and the results will automatically be generated over the full extent of the table:


The results will automatically write over any data within the output range, and also the column to the right and the row below.   The data will automatically resize to show the full extent of the array, but the output range cannot be adjusted to show only part of the array (other than by re-sizing the array returned from Python).

At the time of writing (1st June 2018), the code does not always fully recalculate after a global recalculation of the sheet or workbook:


and if results are deleted or overwritten the arrays do not always recalculate:


These problems are a recognised bug,  which should be fixed in the next release.

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

More Numpy with Excel and xlwings

The spreadsheet presented in the last post on sorting also includes links to many other Numpy functions.  The spreadsheet and associated Python file can be downloaded from:

The Numpy functions require xlwings and Numpy to be installed, which are included in the (free) Anaconda Python default installation.

All the Numpy functions in this spreadsheet return an array.  The whole array may be displayed either by entering as an array function, or using xlwings dynamic array formulas.  The spreadsheet also includes two VBA macros to display the entire array function (press Ctrl-Shift-S), or to re-size the display to the selected range (press Ctrl-Shift-R).  The next post on this topic will give more details.

The Numpy functions included are shown in the screenshots below.  See the Numpy Documentation for more details.:

Polynomial functions:

  • xl_PolyRoots
  • xl_PolyFromRoots
  • xl_PolyVal

xl_PolyFit fits a polynomial to scattered data

  • xl_PolyCompanion
  • xl_PolyDer
  • xl_PolyInt
  • xl_PolyAdd
  • xl_PolySub
  • xl_PolyMul
  • xl_PolyDiv
  • xl_PolyPow

Maths functions operating on arrays:

  • xl_add
  • xl_subtract
  • xl_multiply
  • xl_divide
  • xl_logaddexp
  • xl_logaddexp2
  • xl_true_divide
  • xl_floor_divide
  • xl_negative
  • xl_positive
  • xl_power
  • xl_remainder

  • xl_mod
  • xl_fmod
  • xl_divmod
  • xl_absolute
  • xl_fabs
  • xl_rint
  • xl_sign
  • xl_heaviside
  • xl_conj
  • xl_exp
  • xl_exp2
  • xl_log
  • xl_log2
  • xl_log10
  • xl_expm1
  • xl_log1p

  • xl_sqrt
  • xl_square
  • xl_cbrt
  • xl_reciprocal

Trigonometric Functions:

  • xl_sin
  • xl_cos
  • xl_tan
  • xl_arcsin
  • xl_arccos
  • xl_arctan
  • xl_arctan2
  • xl_hypot

  • xl_sinh
  • xl_cosh
  • xl_tanh
  • xl_arcsinh
  • xl_arccosh
  • xl_arctanh
  • xl_deg2rad
  • xl_rad2deg

Floating Point Functions:

  • xl_isfinite
  • xl_isinf
  • xl_isnan
  • xl_fabs
  • xl_signbit
  • xl_copysign
  • xl_nextafter
  • xl_spacing

  • xl_modf
  • xl_ldexp
  • xl_frexp
  • xl_fmod
  • xl_floor
  • xl_ceil
  • xl_trunc

Numerical Ranges:

  • xl_arange
  • xl_linspace

  • xl_logspace
  • xl_geomspace

  • xl_meshgrid



Posted in Arrays, Excel, Link to Python, Maths, Newton, NumPy and SciPy, UDFs, VBA, xlwings | Tagged , , , , , , , , , , | 1 Comment

More Lowlands

Sometimes YouTube suggestions are hopeless, and sometimes they are spot on.  Here is one of the latter:

It seems that Ali Darragh is yet to be deemed Wiki-worthy, but there is a good article about her here.

That link led me to a more traditional version by The Corries:

… and then back to the Anne Briggs unaccompanied version, which has featured here before:

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

Office Insiders and Power Pivot

I recently discovered another benefit of the Office Insiders program through the ExcelUnplugged blog, which has a feature on the new linked data types in Excel.  More on those another day, but in addition to JavaScript UDFs, the added feature that I found to be of most immediate interest was that after signing up for the program Power Pivot becomes available on Office 365 Home and (I presume) the business plans where it was previously excluded.

See more about Power Pivot.


Posted in Computing - general, Excel, Javascript | Tagged , , , | Leave a comment

Sorting with Python and Numpy

Following the previous post, lets look at sorting functions in the Numpy library.

The spreadsheet xlwNumpy links to a variety of Numpy functions, including the sorting functions described below.  The spreadsheet and associated Python code can be downloaded from:

The Python code requires xlwings and Numpy to be installed, which are included in the (free) Anaconda Python default installation.

The screenshots below  show examples of the sorting functions:

xl_npsort uses the basic Numpy sort function.  The Numpy sort has two significant differences from the standard Python version:

  •  Each column is sorted independently.
  • Numpy arrays convert all elements to the same data type, so if the data contains one or more strings, all the numbers will be converted to strings as well.  This may change the sort results, since in an ascending numerical sort 3 will be sorted before 21, but in an alphabetic sort 21 will come first.

The xl_LexSort function uses Numpy lexsort, which performs an indirect sort, returning a list of index values for the sorted array, using the supplied “keys”.  In the Numpy function the keys are defined as:

keys : (k, N) array or tuple containing k (N,)-shaped sequences.
The k different “columns” to be sorted. The last column (or row if keys is a 2D array) is the primary sort key.

In the Excel version the entire data range is passed as single array, and the columns to be sorted are passed as a row of values.  Note that the primary key is listed first, and the list is base 1, not base 0.  The key list may be any length from 1 up to the number of column.

The xl_Sort_t function sorts a table using the lexsort function, providing similar functionality to the Python sort function, provided that all the data has the same type.

The lexsort results can also be used with the Excel built-in Index function.  This provides the same results as the xl_Sort_t function.  The xl_pysort function calls the standard Python sort, and can therefore handle table with mixed data types.

The xl_Argsort function returns the index values to sort an array in the same way as Numpy sort, i.e. each column is sorted separately.

xl_Msort calls the Numpy msort function, which has similar functionality to sort, except there is no provision for changing the axis of the sort.

xl_Sort_Complex sorts a two column array, treating each row as the real and imaginary parts of a complex number.

xl_Partition and xl_Argpartition split an array into two parts, with values below and above the kth value.


Posted in Arrays, Excel, Link to Python, NumPy and SciPy, UDFs, VBA, xlwings | Tagged , , , , , , , | 1 Comment