Newton Excel Bach, not (just) an Excel Blog

One of the most frequent reasons given for not using Excel for engineering and scientific applications is its very limited  facilities for dealing with calculations involving units.  It is possible to overcome this deficiency with VBA (see  Daily Download 24: Units for Excel and Evaluating text with units and finding the neutral axis), but linking to unit related libraries in Python has several potential advantages:

• Using existing libraries greatly reduces the coding required.
• Python libraries such as Sympy have sophisticated methods for dealing with parsing of non-standard units, not available in VBA.
• Linking to other Python packages such as Numpy and Scipy offers potential performance advantages.
• The method used to evaluate functions passed as strings in VBA is limited to 255 characters.  Linking to Python does not have this restriction.

There are many unit related Python libraries, apparently offering similar functionality.  A good review of three of the most popular is given at: Quantities and Units in Python.  This suggests that the Pint package would be most suitable, however a recent Stackoverflow reply links to a paper reviewing Unyt, which offers a convenient interface to the unit handling facilities in the Sympy package.  Unyt docs.

I have set up  a small spreadsheet using xlwings to link to both Unyt and directly to the units facilities in Sympy.  Download, including open source Python and VBA code from:

xlw_Sympy-Unyt.zip

As well as Excel the spreadsheet requires Python, xlwings, Numpy, Sympy and Unyt.

I usually recommend the Anaconda package to install the required libraries, but in this case I found that it installed an old version of Sympy, and did not include Unyt.  After using Anaconda to install Python, xlwings and Numpy, the final two packages can be installed from the command line with pip:

• pip install sympy
• pip install unyt

I found that this installed the required packages with no problems.

The download zip file also includes a file “unit_symbols.py” that should be copied to the Unyt folder  (..\Anaconda3\Lib\site-packages\unyt\).

In the spreadsheet I have created three user defined functions:

• Planet_year1 and Planet_year2 find the orbital period  of a planet around a sun, given the radius of the planet orbit and the mass of the sun.  This is based on an example in the Unyt documentation.
• Evalu evaluates any function entered as text on the spreadsheet:

The Planet_year1 function calls the Sympy unit handling and function solving routines:

@xlw.func
def Planet_year1(rad, runit, smass, sunit):
    T = symbols("T")
    a = su.Quantity("planet_a")
    a.set_dimension(su.length, "SI")
    lunit = getattr(su, runit)
    a.set_scale_factor(rad*lunit, "SI")
    
    M = su.Quantity("solar_mass")
    M.set_dimension(su.mass, "SI")
    munit = getattr(su, sunit)
    M.set_scale_factor(smass*munit,"SI")
    
    eq = Eq(T, 2*pi*(a**3/(su.G*M))**0.5)
    q =solve(eq, T)[0]
    pdays = su.convert_to(q, su.day).n()
    return float(pdays.args[0]),str(pdays.args[1])

The return unit in this function is hard-coded todays.

The Unyt code performs the same calculation, but allows the return unit to be specified as a function argument:

@xlw.func
def Planet_year2(rad, runit, smass, sunit, rtnunit):
    lunit = getattr(un, runit)
    semimajor_axis = rad*lunit
    
    munit = getattr(un, sunit)
    smass = smass*munit
    
    period = 2*np.pi*(semimajor_axis**3/(un.G* smass))**0.5
    period = period.to(rtnunit)
    return float(period.value), str(period.units)

The Unyt code is considerably simpler:

• The input strings passed as the arguments “runit” and “sunit” are converted to unit objects using getattr().
• The float values are assigned units by multiplying with the associated unit.
• The resulting unit values can be combined with Numpy and Unyt constants (np.pi and un.G) and evaluated as usual.
• The return unit is specified as a text string: period = period.to(rtnunit)
• For return to Excel, the result value are extracted as a separate float and string

The Evalu function evaluates a function passed as text from the spreadsheet:

@xlw.func
@xlw.arg('syms', ndim=1)
@xlw.arg('fvals', ndim=1)
@xlw.arg('funits', ndim=1)
def Evalu(func, syms, fvals, funits, rtnunit):
    # Convert ^ to ** and remove leading =
    func = exp_py(func)
    i=0
    for funit in funits:
        if type(funit) == str and funit != '':
            funit = getattr(un, funit)
            fvals[i] = fvals[i]*funit
        i=i+1
    f = eval('lambda ' + ', '.join(syms) +': ' + func )
    
    rtn = f(*fvals)    
    rtn = rtn.to(rtnunit)
    return float(rtn.value), str(rtn.units)  

• The input arrays may be a single cell, so the xlwings arg decorator is used to specify that they should always be treated as a 1D array.
• Any Excel/VBA exponent operators (^) are converted to **
• The function is converted from a string to a lambda function.
• The return unit is applied, and the return value and units are extracted as in the Planet_year2 function.

The second Evalu example illustrates the procedure for dealing with functions with incompatible units.  The tensile strength of concrete is specified in design codes as 0.6 times the square root of the compressive strength in MPa, and therefore has units of square root MPa.  If the input value is multiplied by MPa units: (fc*un.MPa) it will give the result the correct stress dimensions, so the input value can be specified with any valid stress units.