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.
Newton Excel Bach, not (just) an Excel Blog 
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?
LikeLike
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.
LikeLike
By the way, if you get XNumbers working, could you let us know what the problem was.
Thanks
LikeLike
Pingback: Pint, MPmath and implied units, working with Excel | Newton Excel Bach, not (just) an Excel Blog
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?
LikeLike
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.
LikeLike
Thank you, I figure it out. Yes it needs to be string so always:
return str(z)
LikeLike