Python for VBA users – 1

Posted on 24/09/2013 by dougaj4

Following my recent posts on some preliminary experimentation with linking Excel to Python, this will be the first of a more structured series, mainly as a reminder to myself of lessons learnt on the way, but also I hope of use to others starting out with Python.  The focus will be on connecting to Python from within Excel, using the PyXll add-in, combined with the numpy and scipy maths and scientific libraries.  See the link above for suggestions for installation of Python and PyXll.

This post will look at the procedure for transferring a VBA User Defined Function (UDF) to Python.  The basic procedure for creating a Python UDF in PyXll is as follows:

  • Create a Python module to hold the Python code.  A Python module is analogous to a code module in the Visual Basic Editor, but consists of a text file with the extension .py.
  • Add the Python code for the required function.
  • Add an “xl_func” decorator at the top of the Python function code, to expose the function to Excel.
  • Add the name of the Python module to the modules list in the file pyxll.cfg in the main PyXll folder.
  • The Python function will now be available from any Excel file, so long as the PyXll add-in is active.

For the purposes of illustration of this procedure I have chosen to translate VBA functions to solve Quadratic and Cubic equations to Python.  In fact functions to solve polynomial equations can be found in the numpy library, but translating the code is a good exercise, and provides a function that can be easily checked.

The procedure I used to translate the VBA code to Python was:

  • Ensure that the VBA code is properly indented.  In VBA indentation is solely for ease of reading, but in Python the indentation is essential to the logic of the code.  I use the excellent and free Smart Indenter, for automatic indentation of my VBA code.
  • Copy and paste the VBA code to your text editor of choice.  I am currently using PyScripter, a free and open-source Integrated Development Environment.
  • Edit the VBA code to follow Python requirements:
    • Remove all Dim statements.
    • Replace all VBA comment markers (‘) with the Python equivalent (#).
    • Create any global variables, lists or arrays required, and ensure that bracketed index values to arrays are in the correct format (more details in a later post).
    • Modify all If, Do, and similar loops to Python format.  Note that Select Case loops must be replaced with a series of if … elif statements.  Also note that Python is case sensitive, and that VBA statements in Proper Case must be converted to all lower case.
    • Where the VB editor has added a # to integer values this must be removed, since it is the comment character in Python.
    • Where integer fractions are required to be treated as floating point values (e.g. 1/3), at least one of the values must be entered as a decimal: 1.0/3.
    • Replace any VBA exponentiation symbols (^) with the Python equivalent (**).
    • Check the correct spelling for any built-in functions used, and remember to change initial upper case letters to lower case.
    • Comparison operators: <, <=, >, and >= may remain unchanged, but = must be replaced with == (when used for comparison, rather than assignment), and != is preferred to <>.
    • Revise any code segments where the methods used in VBA will not work in Python.
    • Create a return value or array in a format suitable for transfer to Excel.
    • Replace the VBA style return value statement:
      FunctionName  = x
      with the Python:
      return x
      and delete the End Function statement.
    • At the first line, replace VBA:
      Function FunctionName(Parameter as Type, …) as Type
      with Python:
      def FunctionName(Parameter):
    • Add the xl_func “decorator”:
      @xl_func(“type Parameter, … : return type”)

The two screen-shots below show a section of code as pasted from the VBA editor, and as translated to VBA format.  In the next post in this series I will look at this process in more detail, including some of the catches for the unwary.

VBA code pasted to Python editor (Dim statements deleted)

VBA code pasted to Python editor (Dim statements deleted)

VBA2Python1-2

Final Python Code

Posted in Excel, Link to Python, UDFs, VBA | Tagged , , , , , | 2 Comments

Sorting with Python

Posted on 19/09/2013 by dougaj4

A previous post provided a VBA Sort function that could be used as a User Defined Function (UDF) to provide a sorted list on the spreadsheet, or called from another VBA routine to sort an array.

I have just written a Python/PyXll routine to provide the same functionality, including sorting on any specified column, and sorting in ascending or descending order.  Here is the code in full:

from operator import itemgetter
@xl_func("var srange, int sortcol, bool rev : var")
def py_Sort(srange, sortcol, rev):
    return sorted(srange, key = itemgetter(sortcol), reverse = rev)

The py_Sort function requires Python and PyXll to be installed, but otherwise works the same as the VBA function, and a quick test indicates that it is about twice as fast.  The screen-shot below shows output from the VBA and Python function:

VBA and Python Sort Functions

VBA and Python Sort Functions

Posted in Arrays, Excel, Link to Python, UDFs | Tagged , , , , , | 5 Comments

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