Python matrix functions in Excel, using Pyxll

Posted on 10/09/2013 by dougaj4

Following my previous Python post I have now successfully created a number of matrix arithmetic User Defined Functions (UDFs), using Python, SciPy and the PyXll add-in:

Function list

Function list

The spreadsheet and associated Python modules may be downloaded from Matrixpyxll.zip

See the previous post for details of downloading and installing the necessary Python and PyXll files.

Writing the python modules proved to be very simple.  Two examples are shown below, and the rest are included in the download files:


@xl_func("numpy_array x: numpy_array")
def py_inv(x):
"""
Return the inverse of a square matrix.
x: square matrix
"""
return inv(x)
@xl_func("numpy_array x: numpy_array")
def py_trans(x):
"""
Return the transpose of a matrix.
x: matrix
"""
return transpose(x)

Note that there is no need to explicitly convert the spreadsheet range to a different data type, or re-size input and output arrays.  Also the comments under the function definition are automatically copied to the Excel function wizard, providing very simple documentation.

I was also pleasantly surprised by the performance of the functions on large data sets:

  • Invert a 1000×1000 dense matrix of random numbers: 0.45 sec (more than 70 x faster than the built-in MInverse() function!)
  • Invert a 2000×2000 dense matrix of random numbers: 2.2 sec

Examples of the use of the functions is shown in the screen-shots below.  For details of use of array functions see: Using Array Formulas.

Matrix inversion functions

Matrix inversion functions

2

Eigen value and Eigen vector functions

Eigen value and Eigen vector functions

3

py_Solve and py_SpSolvecoo

py_Solve and py_SpSolvecoo

Posted in Excel | 2 Comments

Martin Simpson and Danny Thompson (1/47 from Graham Bell’s Danny Thompson collection)

Posted on 09/09/2013 by dougaj4

A variant of the traditional song Matty Groves, Little Musgrave tells the story of a young man paying the price for a brief tryst with a noble woman.

This version recorded live at the Oxford Folk Festival of 2008 features some amazing guitar work from Martin Simpson, enhanced (in my opinion) by the long reverberation time of the venue (but it does make the words hard to follow, so I have posted them below).  In the You Tube link this video is followed by 46 more featuring bassist Danny Thompson with a variety of artists.

As it fell out upon a day
As many in the year
Musgrave to the church did go
To see fair ladies there

And some came down in red velvet
And some came down in pall
And the last to come down was the lady barnard
The fairest of them all

She’s cast a look on the little musgrave
As bright as the summer sun
And then bethought this little musgrave
This lady’s love I’ve won

Good day good day you handsome youth
God make you safe and free
What would you give this day musgrave
To lie one night with me

I dare not for my lands, lady
I dare not for my life
For the ring on your white finger shows
You are lord barnard’s wife

Lord barnard’s to the hunting gone
And I hope he’ll never return
And you shall slip into his bed
And keep his lady warm

There’s nothing for to fear musgrave
You nothing have to fear
I’ll set a page outside the gate
To watch til morning clear

And woe be to the little footpage
And an ill death may he die
For he’s away to the green wood
As fast as he could fly

And when he came to the wide water
He fell on his belly and swam
And when he came to the other side
He took to his heels and ran

And when he came to the green wood
’twas dark as dark can be
And he found lord barnard and his men
Asleep beneath the trees

Rise up rise up master he said
Rise up and speak to me
Your wife’s in bed with little musgrave
Rise up right speedily

If this be truth you tell to me
Then gold shall be your fee
And if it be false you tell to me
Then hanged you shall be

Go saddle me the black he said
Go saddle me the grey
And sound you not the horn said he
Lest our coming it would betray

Now there was a man in lord barnard’s train
Who loved the little musgrave
And he blew his horn both loud and shrill
Away musgrave away

I think I hear the morning cock
I think I hear the jay
I think I hear lord barnard’s horn
Away musgrave away

Lie still, lie still, you little musgrave
And keep me from the cold
It’s nothing but a shepherd boy
Driving his flock to the fold

Is not your hawk upon it’s perch
Your steed is eating hay
And you a gay lady in your arms
And yet you would away

So he’s turned him right and round about
And he fell fast asleep
And when he woke lord barnard’s men
Were standing at his feet

And how do you like my bed musgrave
And how do you like my sheets
And how do you like my fair lady
That lies in your arms asleep

It’s well I like your bed he said
And well I like your sheets
But better I like your fair lady
That lies in my arms asleep

Get up, get up young man he said
Get up as swift as you can
For it never will be said in my country
I slew an unarmed man

I have two swords in one scabbard
Full dear they cost my purse
And you shall have the best of them
I shall have the worst

So slowly, so slowly he rose up
And slowly he put on
And slowly down the stairs he goes
Thinking to be slain

And the first stroke little musgrave took
It was both deep and sore
And down he fell at barnard’s feet
And word he never spoke more

And how do you like his cheeks, lady
And how do you like his chin
And how do you like his fair body
Now there’s no life within

It’s well I like his cheeks she said
And well I like his chin
And better I like his fair body
Than all your kith and kin

And he’s taken up his long long sword
To strike a mortal blow
And through and through the lady’s heart
The cold steel it did go

As it fell out upon a day
As many in the year
Musgrave to the church did go
To see fair ladies there

Posted in Bach | Tagged , , , , | 4 Comments

Installing Python, Scipy and Pyxll

Posted on 09/09/2013 by dougaj4

I have recently been experimenting with Pyxll, which after a few initial hiccoughs is proving to be an easy and efficient way to connect Excel to Python based scientific and maths applications, such as linear algebra routines and ODE solvers.  An overview of what the program will do is given in the Youtube link below:

The main thing to watch out for when installing is to make sure that all the software packages required are compatible with your version of Excel, and with each other.  Note that to run Pyxll the version is controlled by the version of Excel (32 bit or 64 bit), regardless of the number of bits of your Windows version.

Finding the version information used to be easy; it was always under Help-About, but Microsoft has apparently decided that this information might frighten the uninformed, so they have decided to hide it:

  • In Excel 2010 it is under File-Help
  • In Excel 2013 it is under File-Account-About Excel

The second choice is the version of Python to install, either the latest 3.x or 2.7.  After a few false starts I ended up installing 2.7, which seems to give a better chance of compatibility with the various add-on packages.

Finally there are numerous different options for the source of the maths and science routines.  I ended up installing Anaconda (one of the packages recommended at the SciPy site); since it seemed the best option for including everything I needed, without being excessively complex.

Having downloaded and installed the Windows 32 bit, Release 2.7 version of Anaconda and Pyxll the next steps were:

  • Add the PyXll path to the system search path; using (in Windows 7) Control Panel- System and Security- System- Advanced system settings- Environment Variables- System variables, then (finally) find and Edit the “Path” variable.
  • Install pyxll as an Excel add-in using File- Options-  Add-ins, select Excel Add-ins in the Manage drop-down box, click Go…, then Browse and find the PyXll add-in file

Having done that you should get a PyXll message when you start Excel.  I then suggest looking at the Examples.xls file in the PyXll/Examples folder.  Have a look at the introduction video linked above, and the Pyxll documentation.

Posted in Excel, Link to dll, Link to Python | Tagged , , , , | 15 Comments

Arrays vs Collections vs Dictionary Objects (and Dictionary help)

Posted on 29/08/2013 by dougaj4

A recent comment by Lori Miller at Daily Dose of Excel suggested that (for the particular application being discussed) the VBA Collection object was quicker than using scripting dictionaries.  Since I hardly ever use collections (other than the built-in ones), and recently I have made quite extensive use of dictionaries I thought I would investigate further. In the process I found a comprehensive article on the scripting dictionary, which I recommend to Microsoft to see how to write truly helpful help articles:

Using the Dictionary Class in VBA

Commenting on the differences between dictionaries and collections, this article says:

For relatively simple needs, such as identifying only the distinct items in a list, there is no advantage to using a Dictionary from a feature functionality perspective.  However, if you must:

  • Retrieve keys as well as the items associated with those keys;
  • Handle case-sensitive keys; and/or
  • Be able to accommodate changes in items and/or keys

then using a Dictionary object offers a compelling alternative to a Collection.
However, even for relatively simple needs, a Dictionary may offer a significant performance advantage, as suggested by the analysis at the end of this article.

So which is it? Are collections faster than dictionaries, or are dictionaries faster than collections?

The linked article finishes up with a downloadable benchmark spreadsheet addressing this very question, giving the following results:


suggesting a substantial speed advantage for dictionaries. But that example was using a data set of 10000 rows x 50 columns.  Reducing the number of rows to 10 (and increasing iterations from 20 to 2000) I get:

Collection vs Dictionary (small data set)

Collection vs Dictionary (small data set)

In this case the collection is more than twice as fast as the dictionary, so it seems that it all depends on the size of the data (but for a small data set it probably doesn’t matter anyway, unless you have a huge number of repetitions).

As for arrays, they don’t have the lookup speed of collections and dictionaries, but they do have a number of advantages:

  • They are easy to create in VBA with a single statement:
    (Arrayname = Range(“rangename”).Value2)
  • 2D arrays are in the format required for matrix arithmetic, and are convenient for other arithmetical operations.
  • They are easily transferable to other programming languages.

Update 3rd September 2013

Two links provided by Lori in the comments are well worth a look, including more detailed benchmarking results.

Charles Williams: VBA UDF shootout between Linear Search, Binary Search, Collection and Dictionary

Colin Legg: Count Distinct Or Unique Values – VBA UDF

I think the main conclusions are:

  • Overall the scripting dictionary has the best balance of speed and convenience.
  • For very large data sets collections can have a speed advantage
  • For the best performance with large data sets consider using a C++ xll function.
Posted in Arrays, Excel, VBA | Tagged , , , , | 7 Comments

Running VBA routines from a new workbook

Posted on 21/08/2013 by dougaj4

I have had a couple of queries recently which seem to be resulting from trying to run User Defined Functions (UDFs) from a new workbook, so this post will look at the options for using the VBA routines provided here (or anywhere else) with new data.

VBA routines come in two basic types:

  • UDFs, which work in a similar way to the built-in Excel functions.  The function name is entered along with the required arguments, and it displays the results, either a single value or a range of values.
  • Macros, which perform a series of operations, either predefined, or with interaction from the user.  Macros are initiated either by clicking on a button (or other object), or through a custom menu or icon on  a tool bar or ribbon, or by selecting from a list (press Alt-F8, or click Macros on the Developer Ribbon).

In both cases all the VBA code defining the routines is stored with the workbook they were created in.  Once the workbook is closed, the routines are no longer available.  To use the routines with the new data there are several options:

The quickest and easiest is to simply save the original workbook with a new name, and enter (or copy and paste) in the new data.  UDFs may be used anywhere, just select the required data ranges as you would for a built-in function.  Macros often use named ranges, and data should be entered in the ranges used in the examples provided.

It is possible to use UDFs in a new workbook, providing the original file is kept open.  The screenshot below shows the UDF Cubic() being used in a macro-free workbook, by calling it from the Polynomial workbook:

Calling Cubic from the open file, Polynomial.xlsb

Calling Cubic from the open file, Polynomial.xlsb

The “Insert Function” dialog is called by clicking the fx icon immediately to the left of the edit bar, then select “User Defined” in the “select a category”.  This will list all UDFs in all open workbooks; for those not in the current active workbook the function name will be preceded by the workbook name.  After selecting the desired function, click OK, and the function arguments may be selected as usual:

UDF-Cubic2

Selecting the data range for the Cubic function

The third option is to save the workbook containing the UDF as an add-in, and install it as described here: Creating An XLA Add-In For Excel and  Installing An Add-In

After installing and opening the add-in file all the functions it contains will be listed in the Insert Function dialog, but will also appear as a tool-tip, as you type the function name:

UDF-Cubic3

Posted in Excel, UDFs, VBA | Tagged , , , | 1 Comment