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
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.