Intermezzo: how to escape the DP limitations?

In general, it is at least a very complicated task to prove that an algorithm is working as intended over the whole range of possible input parameters. When dealing with mathematical problems, it seems to be a good idea to check whether results obtained using a higher level of accuracy agree with those obtained using the standard level. Fortunately, for all major programming languages there are multi-precision libraries as well as automated conversion utilities that transform the source code automatically. The situation seems different in case of spreadsheets, though imho they are used for relatively complicated calculations as well nowadays. Unfortunately, there is much mindless advice on the web like “check the accuracy using addition theorems” or so. However, in case you hopefully read the previous posts on DP numbers (or even more professional matter), you know that you cannot test the accuracy of a numerical expression like 1 – x^2 in DP by adding x^2 and checking whether the result equals 1 in DP, e.g. Thus, there definitely is need of multiple-precision calculations in spreadsheets.

I use three spreadsheet environments, two of which are fundamentally different: two combinations of Microsoft®™ (MS) Excel®™ (XL) under MS Windows®™ (Win) as well as gnumeric (GNM) under Ubuntu Linux (UBL). Fortunately, multi-precision extensions are available more or less easily relying on free and open source software (FOSS) only. In case of XL, the software of my choice is a Visual Basic®™ for Applications (VBA) XL add-in called xNumbers (XN) (old version). XL is shipped with an Integrated Development Environment (IDE) for VBA. In case of GNM, I have decided to use its built-in Python plug-in and write a function suite myself that utilises the multi-precision capabilities of the FOSS PY module mpmath. I have not decided on an IDE for big python projects so far; the proof of concept function library provided here is so simple that I could write it using an editor capable of syntax highlighting. I wanted to figure out whether it is feasible to provide PY based cell formulae to GNM, which offer exactly the same functionality as their XN pendants in XL and thus do not require any change whatsoever in the XL file when it is opened with GNM in UBL. Of course, this GNM extension is totally independent of XL and works with the standard GNM file format as well.

The GNM PY plug-in under UBL requires two files to be located in a (subdirectory of a) specific folder, i.e. “/usr/lib/gnumeric/1.10.1/plugins” in case of the current GNM version 1.10.1x, in order to fully specify a user-defined function (UDF). Unfortunately, this folder belongs to the user “root” in UBL. However, very surprisingly there is no user “root” in a fresh UBL installation.

!!! DANGER – DO NOT TRY THIS AT HOME !!!
If this warning failed to prevent you from reading on, be warned: “root” can be medicine and poison as well … At least, if you are not a subject matter expert in computer security and secure internet router configuration, at first do all the necessary downloads and installations, then plug off your network cable or switch off your mobile network equipment, and only after that try to become the “root” user of UBL.

A search on the web immediately yielded the cure for the UBL-“root” problem: type >>sudo passwd<< in a terminal window, then twice enter an identical and very secure password, and voila, the user “root” is automatically created. Now exit the terminal, re-start and then login as “other”–>”root”. Now you can add a subfolder to the “plugins” directory, which in my case is called “fn-drgst” (GNM will automatically check all subfolders in the “plugins” folder). In this folder, at least two files have to be created: the module file that contains the executable PY code, which in my case is “drgsts_pyfuncs.py”, and an xml file having the mandatory name “plugin.xml” that describes the PY file. All the details of the naming conventions are explained in the section writing your own python xxx on the GNM website. Any PY UDF is referred to three times: firstly in the actual function definition line in PY file, secondly in a line belonging to a named function group in the PY file describing the kinds and numbers of parameters that all functions take, and thirdly in a line in the XML file.

For example, the multi-precision function which performs the addition of two real numbers in xN is named xADD and is invoked by typing “=xADD(C5;C6;30)”, e.g., into an XL cell. In this case, the result in XL will be the sum of the two cells C5 and C6 with an accuracy of 30 decimal digits. Of course, it cannot be an XL number but in fact is a string. Typically, C5 and C6 will therefore contain strings like >>’1.23456789012345678901234567891<<, too. My PY code defines exactly such a function and reads:

def xADD(myStr1,myStr2,myStr3):
# sum of the numbers contained in myStr1 and myStr2,
# i.e. myStr1 + myStr2, with an accuracy of myStr3 decimal places
from mpmath import mp
dps_int = int(myStr3);mp.dps = dps_int;mp.pretty = True
return str(mp.mpf(myStr1) + mp.mpf(myStr2))

Fortunately, it turned out that this function might be called using cell references instead of number strings as well. In the function-group statement, the corresponding line reads:

drgstspyfuncs_functions = {

'xADD': ('sss', 'myStr1,myStr2,myStr3', xADD),

}

It tells to the system that the function “xADD” takes three strings as parameters (‘sss’), how these parameters are named (‘myStr1,myStr2,myStr3’), and provides the internal (xADD) as well as external (‘xADD’) names of the function. In the XML file, the line

<plugin id="Gnumeric_drgstspyfuncs">

introduces the function group defined in the PY file, then the line

<attribute name="module_name" value="drgsts_pyfuncs" />

states the name of the PY module file that contains the aforementioned function group, and finally the line

<function name="xADD" />

tells to the system the external name of any function that is to appear in the GNM function GUI. Unfortunately, a bug in the GNM PY plug-in prevents the formal description of a function from appearing in the GUI (this is why I have omitted such a formal description like a PY “docstring” in the code). The number and kind of parameters are stated correctly in the GNM GUI, though.

As a proof of concept I have created 30 PY functions which correspond to those 28 functions in xN that are available in two or even three different kinds of accuracy, i.e. DP, quadruple precision, and multiple precision, and the two constants pi and e as well. You may download the code of the two defining *.py as well as *.xml files found as text in a PDF file and a spreadsheet in XL 97-2003 file format demonstrating all the functions and providing their results obtained with all my different spreadsheet environments. All software written by myself is licensed under the GPL v3 that is found in both files, too.

Advertisements

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 )

Google+ photo

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

Connecting to %s