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[0]): 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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

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