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.

7 Responses to mpmath for Excel

  1. Kapt Blasto says:

    Hi, I just came across your site, and found this page.
    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

  2. Pingback: Pint, MPmath and implied units, working with Excel | Newton Excel Bach, not (just) an Excel Blog

  3. skip says:

    Hi, I am trying to achieve better precession with Python or excel however results are not so impressive. I am trying UDF in xlwings.
    1)

    @xw.func
    def fce_2(x, y):
    import decimal
    from decimal import Decimal
    decimal.getcontext().prec = 100
    z = Decimal(x)** Decimal(y)

    return z

    this returns 1,4142


    @xw.func
    def fce_1(x, y):
    from mpmath import mp
    mp.dps = 100
    z = mp.mpf(x)** mp.mpf(y)

    return z

    Returns 1,414213562 None of them returns the required number of decimal places which should be 100.

    I am also trying to use VBA.


    Function testmocnina(A As Variant, B As Variant) As Variant

    testmocnina = CStr(CDec(A) ^ CDec(B))

    End Function

    Which returns 1,4142135623731 however this is the same result as excel formula =B1^C1 -> 1,414213562373100000000000.
    I dont know what I am doing wrong. Would you please be so kind have a look at the issue from your perspective?

    Like

    • dougaj4 says:

      The problem is that Excel has no way of working with numbers with 100 decimal places. Regardless of the precision you use in Python, if you return it to Excel as a number it will be converted to a 64 bit float, which has about 16 significant figures. Excel does have a “Decimal” data type (see https://newtonexcelbach.com/2015/10/26/the-vba-decimal-data-type/) but that only has 28 significant figures. If you want to work with more than that you have to do all the maths in Python, then return the result as a string. Also if you want to pass precise values from Excel to Python you need to pass it as a string and convert it to a number in Python.
      Have a look at the code for the examples used in the article.

      Like

  4. skip says:

    Thank you, I figure it out. Yes it needs to be string so always:
    return str(z)

    Like

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 )

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.