Scipy has 3 functions for multiple numerical integration in the scipy.integrate module:
- dblquad: Compute a double integral.
- tplquad: Compute a triple integral’
- nquad: Integration over multiple variables.
I have written six functions to call these functions from Excel, via Pyxll:
Each of the Python functions can be called to evaluate the integrals of either a function entered as a string on the spreadsheet (py_DblQuadS, py_TplQuadS, or py_NQuadS), or a Python function (py_DblQuadF, py_TplQuadF, or py_NQuadF).
The integration limits are passed as either two or three two-column ranges for the dblquad and tplquad functions, or an n-row, two-column range for nquad functions. Note that the order of the limits is different for nquad to the dblquad and tplquad functions:
The two screen shots below show output from dblquad and tplquad on the left, and nquad on the right, with an example of the “S” and “F” function in each case, Click on the images for full-size view:
Other significant differences between the functions are:
- For the dblquad and tplquad functions, the variables must be x, y, and z, which are passed in the order z, y, x. For the nquad functions the variable names are an input argument, and they are passed in the order listed.
- Where one or both limits for the nquad functions is a formula the limits are entered in one cell in the format: [lower limit or function, upper limit or function]. See examples in the screenshots.
A Pyxll based Excel-Scipy application including these functions, and many others, will be published in the near future.
I’m confused, isn’t every single Python function and feature available already? You just need to COM wrapper, see this for an example http://exceldevelopmentplatform.blogspot.com/2018/06/python-vba-curve-building.html
The code I used is “just” a wrapper as well, using the C API in the case of Pyxll.
The advantages of writing separate functions for each Scipy function (or at least the main ones) are:
The functions can be called without any additional coding, just like a built-in function.
You can give all the arguments descriptive names.
You can add argument descriptions, that will appear in the function wizard.
You can set up the data entry in the most convenient way for the particular data types and format required.
You can assign the functions to appropriate categories.
aha. I think you’ve commented on my blog. Speaking as blogger to blogger I think yours is much prettier, I should have picked WordPress.
I’ve been meaning to add your blog to my blog roll.
I’ll do it in the next few days. 🙂
Do you know of a way to step through Python code that is called from Excel?. Is it possible to put breakpoints in the Python code then recalc a sheet and have the code stop on the breakpoint? That would be a great feature.
Both Pyxll and xlwings have this feature.
I have been using Pyxll recently, and I found the code debugging works very well, as described here: https://www.pyxll.com/blog/debugging-your-python-excel-add-in/
I used the Visual Studio Code editor, with the add-in mentioned in the article.
I haven’t done much with xlwings debugging. I found it pretty clunky, but that may well be because of the editor I was using (Pycharm), which I’m not familiar with, and didn’t much like.