Using with numpy arrays and Pyxll problem

[Doug Jenkins]

I have been experimenting with Pyxll for a few months, and have just discovered ExcelPython, which seems to have some advantages for my purposes. I have successfully created a couple of UDFs, but have encountered a couple of problems:

1) If I have the Pyxll add-in enabled I can't open any file with ExcelPython code; it just crashes. If I disable Pyxll then ExcelPython will open, but it would be convenient to be able to have them both enabled.

2) If I try to use a numpy array I get a Type Mismatch error. I tried copying the data as passed into a numpy array in the python code, but I still get the message. Pyxll allows passing of numpy arrays in both directions, and it would really be useful if that is possible in ExcelPython. If I need to convert to and from numpy in the python code, some advice on that would be useful; i.e. what is the best way to convert a numpy array into a form that can be passed back to Excel?

I know VBA well, but I'm a beginner at Python, so apologies if the questions are a bit basic.

[Doug Jenkins]

For the numpy array question, this seems to work:


def py_ArgSort(InRange):
nprange = np.array(InRange)
sortind = np.argsort(nprange,0)
return sortind.tolist()


Any comments are welcome.

[Eric Reynolds]

Hi Doug,

Thanks for your question, out of curiosity what have you identified as the advantages of ExcelPython over PyXLL? Each of these Excel/Python integration tools seems to occupy a slightly different niche, so feedback on ExcelPython's relative strengths is nice to have.

1) I was not aware of the PyXLL/ExcelPython incompatibility, I will try to look into that when I have a moment.

2) NumPy types are not natively supported by ExcelPython (a) because I don't want to introduce a mandatory dependency on NumPy and (b) because I have no need for it and little time to dedicate to ExcelPython. But it's open source so if anyone wants to try be my guest! Plus, as you point out, it's easy to just perform a conversion in the Python code.

You have identified the correct functions for conversion to/from NumPy, I would just point out that it may be worth specifying the matrix type:

nprange = np.array(InRange, dtype='d')

otherwise NumPy might assume you want an int32 array in some cases.

Regards,

Eric

[Doug Jenkins]

Hi Eric,

Thanks for the reply and for making your work on this available.

The main advantage I see with ExcelPython is that it is free! I'm working on some applications which I may wish to sell in the future. Pyxll is unrestricted and free for non-commercial use, but they require a licenced copy for commercial use. I wouldn't have a problem if it was just the one copy, but any users would also have to buy a licence, so I see that as being a significant drawback. Being open source is also a plus, although whether I will ever have the time and inclination to delve into the source code is another matter!

On the other side, Pyxll is better documented, and provides more facilities for controlling Excel from within Python. The latter isn't so important for me, because I'm quite happy to use VBA to control the Excel side of things, and Python for where it provides a real advantage. That usually involves the use of Numpy and/or SciPy, which is where I see the real benefit of Python over VBA.

I have done a few tests solving large sets of simultaneous equations using SciPy with ExcelPython and Pyxll, inloving arrays up to 2000x2000. Performance-wise they seem to be almost the same. There is a significant delay in transferring the data (it takes about the same time to transfer the data as it does to complete the solution!), but there didn't seem to be a significant difference between the two. In practice this isn't a big issue anyway, because I will transfer a much smaller block of data from Excel, generate and solve the arrays in Python, then the solution array is quite small to pass back.

I will be documenting my work on this on my blog, so you might like a look:
http://newtonexcelbach.wordpress.com/

Thanks again for making all your work on this public.

Doug

[Stefan]

Hi Doug!

I am also trying to link Python to Excel VBA using ExcelPython with the aim of extending the mathematical solver library for my work. The problem is that I am complete newbie to Python. At least I managed to install Anaconda Python and ExcelPython tool to operate.
Now I am trying to get a Gibbs minimisation reactor example given by the Kitchingroup at Carnegie Mellon (http://kitchingroup.cheme.cmu.edu/blog/2013/03/01/Finding-equilibrium-composition-by-direct-minimization-of-Gibbs-free-energy-on-mole-numbers/)
I get the example to work in Python but when trying to pass the temperature and initial composition via Excel VBA I run into a number of problems such as "Type Mismatch" and others...

Trying your proposed py_ArgSort function I could at least get numbers back into Excel but it only seems to be the sort indices of the result area (a vector containing the equilibrium mole flow)...

Do you have any worked example available with ExcelPython and numpy/scipy? I look at your blog (had been there before and am REALLY impressed! Great stuff!).

Thanks in advance for any hints.

Stefan

[Eric Reynolds]

Hi Stefan,
Please post an example of something you are trying to do which is not
working so I or Doug can try to help you!
Rgds,
Eric

[Doug Jenkins]

Stefan - I have just posted on my blog:
http://newtonexcelbach.wordpress.com/2014/05/22/linking-to-python-with-excel-python-and-python-matrix-functions/

I don't go into detail about the code, but you can download a matrix solver file with open source VBA and Python code.

If you are using Numpy/Scipy, one of the things to look out for is that you need to convert the Excel arrays to Numpy arrays, then the results need to be converted back to Python lists of lists to transfer back to Excel.

I won't have a lot of time, but any specific questions, please ask.

[Stefan]

Thanks Doug! That's it! Will have to have a closer look but inspired by your material I got my stuff to work. Will have to check some things and once I'm done I'll try to post little more detailed here what problems I stumbled into and how I resolved it so maybe someone else can have use of it.

A quick question: With the adaption of the python code to make it work with ExcelPython and Excel, does the code still work as it did in pure Python environment? I'm wondering this because I'm currently working in Excel (as this is the interface people are used to) but consider having all the actual coding in Python. The thought is that I might once have to do more sophisticated optimisations that probably are more easily handled directly in Python. It would be good if I wouldn't have to change the python functions back to "native python" then...Comments welcome :)
And a BIG THANKS again!

Stefan

[Eric Reynolds]

Hi Stefan,

One of the nice things (in my opinion, though as its creator I am obviously biased) about ExcelPython is that it enables you to manipulate Python objects directly from the VBA code.

This means that you can use what you call 'native' Python functions, i.e. ones that do not contain wrapping code, performing the conversion in the VBA code.

So for example you can have a 'native' python module MatrixFunctions.py

import numpy as np

def xl_mmult(x, y):
    return x.dot(y)

which can then be called from VBA like so:

Public Function PyXLMult(x As Range, y As Range)

On Error GoTo fail:

    ' Python equivalent: import numpy
    Set numpyArray = PyGet(PyModule("numpy"), "array")

    ' Python equivalent: x_array = numpy.array(x)
    Set x_array = PyCall(numpyArray, , PyTuple(x.Value2))

    ' Python equivalent: y_array = numpy.array(y)
    Set y_array = PyCall(numpyArray, , PyTuple(y.Value2))

    ' Python equivalent: result_array = xl_mmult(x_array, y_array)
    Set result_array = PyCall(PyModule("MatrixFunctions"), "xl_mmult", PyTuple(x_array, y_array))

    ' Python equivalent: result_list = result_array.tolist()
    Set result_list = PyCall(result_array, "tolist")

    PyXLMult = PyVar(result_list, 2)
    Exit Function

fail:
    PyXLMult = Err.Description

End Function

Like I have mentioned before, the philosophy of ExcelPython is not to provide everything ready done for you (e.g. automatic conversion into numpy arrays) but to provide the building blocks to use Python from Excel in whatever way you want to. I realize of course that for Python beginners this means the learning curve is a bit steeper to begin with, compared with PyXLL say.

Note that the previous example can be simplified even further by calling the matrix function directly and doing away with the seperately defined xl_mmult function:

Public Function PyXLMult(x As Range, y As Range)

On Error GoTo fail:

    Set numpyArray = PyGet(PyModule("numpy"), "array")

    Set x_array = PyCall(numpyArray, , PyTuple(x.Value2))
    Set y_array = PyCall(numpyArray, , PyTuple(y.Value2))

    Set result_array = PyCall(x_array, "dot", PyTuple(y_array))

    Set result_list = PyCall(result_array, "tolist")

    PyXLMult = PyVar(result_list, 2)
    Exit Function

fail:
    PyXLMult = Err.Description

End Function

[Stefan]

Hi Eric,

OK, I think I start to get the idea...but you're right, as Python newbie the learning curve is steep ;-)
I now am using your way of converting to numpy arrays prior to calling my python function. But contrary to my 'native' python I still get a dimension mismatch error when calling the function from within Excel....and do not have any idea why. I suspect it is about an array I'm passing....because when replacing a calculation in the code with numerical values the linked code works (X_in is an array I'm passing from Excel to Python containing mole flows)

# the incoming feed was 4 mol H2O and 1 mol ethane
# WHEN USING THESE LINES OF CODE I GET I DIMENSION MISMATCH ERROR IN EXCEL
# O_in =2X_in[3]+X_in[4]+2X_in[5]+X_in[7]
# H_in = 4X_in[0] + 4X_in[1] + 2X_in[2] + 2X_in[6] + 2X_in[7] + 6X_in[8]
# C_in = X_in[0] + 2X_in[1] + 2X_in[2]+ X_in[3] + X_in[4] +2*X_in[8]
# USING NUMERIC VALUES THERE IS NO ERROR
O_in = 4.0
H_in = 14.0
C_in = 2.0

As stated in the commented code, it works when using the numerical values that in the final version should be calculated based on the three equations above.
What is strange is that the results when calling via Excel are somewhat different to when running the code in Python.
I'm attaching a zip with the Excel file and the two python module versions. Any help appreciated in case you have some spare time .... I suppose it's something very basic that I am missing... :/...

Thanks so much in advance!

Stefan

[Eric Reynolds]

Hi Stefan,

I believe your problem is similar to Richard's below. You can make it explicit that you want X_in to be passed as a 1D list rather than a 2D list-of-lists by specifying the dimension:

Set X = PyCall(numpyArray, , PyTuple(PyObj(X_in.Value2, 1)))

It is becoming clear to me that the fact that ExcelPython does not automatically assume that (N,1) and (1,N) ranges are supposed to be 1-dimensional isn't intuitive for some people so in the next release I will probably modify this (or force you to be explicit in certain cases).

Let me know if this fixes the problem.

Eric.

[Stefan]

Hi Eric,

That actually does solve my problem! Thanks for helping a newbie with such basic stuff :).

What I am still confused about is why the results from the Python code run in console mode in Python are different from the ones obtained when calling from Excel even though all numerical values seem to be identical...

Best regards,

Stefan

[Richard Foo]

Hi All,

I have just started experimenting with ExcelPython yesterday and this forum has been most helpful. I have been trying out different things and got them to work, though often in a convoluted fashion. One thing that bugs me is that I have to do some data-cleanup in the Python layer. The present discussion helps to some degree. In the attached spreadsheet, I am trying to interpolate a value from a table of values using the Python code below.

import numpy  as np
from scipy.interpolate import interp1d

def interpolate(xr, yr, x):
  xra = np.array(xr)
  yra = np.array(yr) 
  y_interp = interp1d(xra.flatten(), yra.flatten())
  results = y_interp(x).tolist()  
  return results

I have had to flatten the arrays coming from the VBA layer after some troubleshooting established that the shape of the arrays was somehow (N,1) while the function was expecting (N,). Below is how I tried to hookup the VBA to Python

Function v_interpolate(xr As Range, yr As Range, xval As Double)
    Set methods = PyModule("Methods", AddPath:=ThisWorkbook.Path)    
    Set result = PyCall(methods, "interpolate", PyTuple(xr.Value2, yr.Value2, xval))
    v_interpolate = PyVar(result)
    Exit Function
End Function

I would greatly appreciate it if someone could point out how to move the flatten operation out of Python and if possible, advise on how best to cleanup and validate data on the VBA layer. Thanks very much.

[Eric Reynolds]

Hi Richard,

the reason why the arrays are (N,1) is very simple: ranges are always matrices so ExcelPython does not automatically assume you want a list if you happen to pass an (N,1) matrix.

The PyTuple (and any other ExcelPython function for that matter) implicitly converts the VBA objects into Python objects. You can solve your (N,1) problem by explicitly performing the conversion specifying the dimension like so:

Set result = PyCall(methods, "interpolate", PyTuple(PyObj(xr.Value2, 1), PyObj(yr.Value2, 1), xval))

This works the other way too with the PyVar function.

Hope this helps,

Eric

[Eric Reynolds]

Hello,

I have integrated these discussions into a tutorial, which I have put on the github site, which ExcelPython will be migrating to as it has a few features which makes open-source collaboration easier

https://github.com/ericremoreynolds/excelpython

[Doug Jenkins]

See:
http://newtonexcelbach.wordpress.com/2014/05/28/python-matrix-functions-with-excelpython-2/

For examples of using ExcelPython with Scipy and PySparse matrix solvers and modified code calling the numpy arrays and the .tolist() method from the VBA end.