Working with units in Excel – using VBA and Python

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:

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.

 

This entry was posted in Excel, Link to Python, Maths, Newton, NumPy and SciPy, UDFs, VBA, xlwings and tagged , , , , , , , . Bookmark the permalink.

9 Responses to Working with units in Excel – using VBA and Python

  1. Larry says:

    Could you review exactly how configure Excel to recognize the existence of the Python pre-requisites? I have Anaconda3 installed and use it regularly, but I can’t get Excel to find the Python libraries even with the xlwings.xlam loaded.

    Like

  2. Larry says:

    Thanks, that helped immensely, and everything is working. The most salient points are 1) xlwings.xlam must be in the XLSTART directory, not selected as an add-in, and 2) make sure that the Anaconda folder is in the system path.

    Like

  3. Pingback: Units and solvers with Pint and Sympy | Newton Excel Bach, not (just) an Excel Blog

  4. Kurt Clark says:

    Hi Doug,
    Amazing resource and great work.
    I tried using your linked excel, and after much debugging I am still stuck at one point, when I click the import functions button in xlwings i get the following error
    “Import error: cannot import name ‘dimsys_SI””
    I’m unable to find the resolution even on the internet, thought you might have some ideas what’s going wrong here. I have python 3.6.8 and sympy 1.6.2

    Thanks in advance.

    Like

  5. Kurt Clark says:

    Hi Doug,
    Is there any way to implement units that have a slash character in them like “m/s”. The script works great for other units. But fails for any units that have a slash in them like speed or velocity units.

    Thanks in advance.

    Like

    • dougaj4 says:

      Hi Kurt – I’m glad you solved the dimsys_SI problem. Since doing that post I have focussed on using Pint rather than Unyt, but I note that my latest code (not yet published) has a “try … except” around the “from sympy.physics.units.dimensions import dimsys_SI”, which suggests I was also having trouble with that line.

      Regarding the use of the slash character, the download spreadsheet from the next blog post, using Pint, has some units with /, so it looks like the problem is with Unyt. A query on the Unyt site would probably be the best bet.

      You mention you had to do much debugging. Is there anything you can pass on that might be useful to others?

      Like

      • Kurt Clark says:

        Yes, I it took me quite a few hours to get xlwings working.
        xlwings was repeatedly freezing and giving errors like “Cannot activate python COM server”. This used to happen when I would click the “import functions” button on the xlwings tab.
        It turned out that python 3 in my case was installed in c:\program files which has a space in the path which I think was causing the errors. So I wrapped my python exe path with double quotes and then pasted the path into the xlwings “Interpreter” text box
        like so “C:\Program Files\Python36\python.exe”
        after this the functions imported correctly and everything was fine.

        Regarding the slash, the folks at unyt told me that I could convert a string to Unyt objects using some thing like

        from unyt import Unit, unyt_array

        u = Unit(‘m/s’)

        print(3*u)

        # these are synonyms
        print([1, 2, 3]*u)
        print(unyt_array([1, 2, 3], u)

        I guess we may have to detect the “/” in the input and process it with another piece of code.

        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 )

Google photo

You are commenting using your Google 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.