## mpmath for Excel

mpmath has been mentioned in passing here before, but not in any detail.  The mpmath site says:

mpmath is a free (BSD licensed) Python library for real and complex floating-point arithmetic with arbitrary precision. It has been developed by Fredrik Johansson since 2007, with help from many contributors.

The following example computes 50 digits of pi by numerically evaluating the Gaussian integral with mpmath. See 100 mpmath one-liners for pi and the documentation links below for many more examples!

```>>> from mpmath import mp
>>> mp.dps = 50
>>> print(mp.quad(lambda x: mp.exp(-x**2), [-mp.inf, mp.inf]) ** 2)
3.1415926535897932384626433832795028841971693993751
```

I have set up some simple examples illustrating the use of mpmath from Excel, linking with Pyxll (minor changes to the code will provide the same results with xlwings): Full Python code for the functions shown above is:

```from mpmath import mp

@xl_func
@xl_arg('dp', 'int')
def mp_setdp(dp = 15):
mp.dps = dp
return mp.dps

@xl_func
def mp_sin(x):
return str(mp.sin(x))

@xl_func
def mp_cos(x):
return str(mp.cos(x))

@xl_func
@xl_arg('syms', 'str[]')
@xl_arg('vals', 'str[]')
def mp_Eval(func, syms = [], vals = []):
func = exp_py(func)
f = eval('lambda ' + ', '.join(syms) +': ' + func )
if vals != []:
vals =np.asarray(vals, dtype= object)
for i in range(0, vals.shape): vals[i] = mp.mpf(vals[i])
return str(f(*vals))
```

The mp_setdp function sets the number of decimal points to be used in all subsequent calculations, until the function is called again. Entering 25   in cell B3, and recalculating the worksheet changes the returned values as shown below: The mp_sin and mp_cos functions illustrate how any mpmath function may be called from Excel.  Note that:

• Data may be entered as a number or a string.
• Decimal numbers should in general be entered as a string, since floats will be converted to 64bit binary form before being converted to the higher precision mpmath format.
• Results are returned as a string.

As an alternative to creating an interface function for each mpmath function, the function may be entered on the spreadsheet and evaluated to mpmath precision using the mp_Eval function.

The screen-shots above show examples of mp_Eval:

• mp.sin(x)^2 + mp.cos(x)^2 which evaluates to exactly 1.0 for any value of x.
• mp.asin(0.5)*6, which evaluates to exactly mp.pi

The mpmath main page links to a list of 100 mpmath one-line expressions for pi.  To create the screen-shots below I have simply:

• Changed the mpmath import line to:  ‘from mpmath import *’ (to avoid the need to enter mp. before each function).
• Copied and pasted the text from the mpmath link directly into Column I.
• Entered =mp_Eval(I3) in cell J3, and copied down for 100 rows.

As can be seen below, all but 2 of the functions evaluate to pi, to 60 decimal places (click on any image for full-size view).   The two functions that did not evaluate included the statement “if k else 0” in the lambda expressions.   This entry was posted in Excel, Link to Python, Maths, PyXLL, UDFs, VBA, xlwings and tagged , , , , , , , , . Bookmark the permalink.

### 3 Responses to mpmath for Excel

1. Kapt Blasto says:

XNumbers 6.0.5.6M got borked by Win 10 Office 365 upgrade.
Love VBA. Would LOVE to have THIS for xlwings.

Now, what’s the slight modifications for xlwings?

Like

• dougaj4 says:

I just checked my XNumbers and it seems to work OK with Office 365 (64 bit). It is ver 6.05.6M and the xlam file is dated Dec 2013 (downloaded from http://www.thetropicalevents.com/Xnumbers60/).

To adapt the Python code for xlwings:
In Python delete the pyxll import and decorators, and replace them with the xlwings equivalents.
In Excel install the xlwings add-in, and run the code importer. That should generate VBA interface functions for all the Python functions with xlwings decorators.

You should then be able to call the mpmath functions directly from Excel, just like a VBA function.

I’m not up to date with the latest xlwings version, so any problems it would probably be best to raise at the xlwings forum.

Like

• dougaj4 says:

By the way, if you get XNumbers working, could you let us know what the problem was.
Thanks

Like

This site uses Akismet to reduce spam. Learn how your comment data is processed.