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.

 

Posted in Excel, Link to Python, Maths, Newton, NumPy and SciPy, UDFs, VBA, xlwings | Tagged , , , , , , , | Leave a comment

Scipy Statistics Functions – coding and getting help

The Python Scipy library currently has 84 statistics functions.  I have now updated the xlwSciPy3 spreadsheet to access all of them directly from Excel.  The new version can be downloaded from:

xlwSciPy3.zip

As usual, the download includes full open-source code.  As well as Excel, the spreadsheet requires Python, Scipy, Numpy and xlwings to be installed.  The Anaconda Python installation includes all the required files.

The Scipy statistics functions  have a variable number of required and optional arguments.  To allow all the functions to be called from a single interface function the following procedure is used:

  1. Both required and optional arguments are passed to VBA using the ParamArray argument, which will accept any number of separate arguments.  Optional arguments are passed as a pair of separate arguments; the name followed by the value, which may be a single cell or a range.
  2. The VBA xl_Stats function reads the number of required arguments, and converts the required number to a single variant array.  The remaining pairs of arguments are converted to another variant array, and the two arrays are passed, together with the function name, to Python, via the VBA function “xl_callfuncSt0”.
  3. In Python the optional argument array is converted to a dictionary, and together with the required argument array passed to the required stats function.

For the functions to work correctly from Excel it is essential that all the required arguments are provided, and that any optional arguments are passed as a name/value pair.  To help identifying the correct input two VBA functions provide help:

The Get_Args function lists all argument names, together with default values for optional arguments:

These arguments can then be used in the xl_Stats function.  The examples below call the binned_statistic function, using the function name in cell K11.  The first  example passes only the two required arguments: x (K19:O19) and values(K20:O20).  In the second the optional “bins” argument is set to 2, and in the third both optional arguments are provided:

The full help documentation can be called from Excel using the Get_Doc function, as shown below.  The output range for this function can be re-sized by selecting the top-left corner (cell U7) and  pressing Ctrl-Alt-S:

Finally all 84 statistics functions are listed on the spreadsheet, with a brief description of the function output:

The xlw_SciPy3 spreadsheet also links to a wide range of other Scipy functions, as listed at: xlwSciPy update for Python 3.

 

 

Posted in Excel, Link to Python, Maths, Newton, NumPy and SciPy, UDFs, VBA, xlwings | Tagged , , , , , , , | Leave a comment

Using Hyperlinks

Hyperlinks are useful to link to other locations in a spreadsheet, or any location with an accessible address, but they can do more than that.

A recent post at Daily Dose of Excel looks at using hyperlinks as a general purpose user interface elements.  As always, both the article and the comments are full of useful content.  Well worth a look.

Posted in Excel | Tagged , , | Leave a comment

On Rythym

“Foli” is the word used for rhythm by the Malinke tribe in West Africa. But Foli is not only found in Malinke music, but in all parts of their daily lives. Directed by Thomas Roebers, this short film portrays the people of Baro, a small town in eastern-central Guinea, and gives you a glimpse inside their culture of rhythm. As the Malinke man says, “Tous les choses, c’est du rythme.” (“Everything is rhythm.”) What makes this film even more beautiful is the fact that it was edited so as to reflect Malinke rhythms.

From Open Culture.

Posted in Bach | Tagged , , , | Leave a comment

Some multi-cultural songs

The latest concert of the excellent Willoughby Symphony Choir featured a collection of popular and folk songs, rather than their usual classical performances.  I was struck by the mixture of cultural influences in many of the songs, and have collected  a few of  my favourite performances with multi-cultural connections.

The first is a song usually associated with the African-American Roberta Flack, but written (I recently discovered) by the English folk singer, of Scottish ancestry, Ewan McColl.  Here it is performed by the Irish folk musician Christy Moore, performed in Scotland:

The next is an English song of life at sea, “A Sailor’s Life”, performed by Sheila Chandra with a unique mixture of Indian and English musical influences:

Next up, the Arabic song “El Helwa Di”, sung by American Anais Mitchell:

Finally, the last performance from today’s concert was the Scottish Song “Blooming Heather”, jointly performed by choir and audience. Here is a performance from Yorkshire lass Kate Rushby, assisted by a (rather larger) audience at the 2007 Cambridge Folk festival:

Posted in Bach | Tagged , , , , , | Leave a comment

Using Section Properties- Group

Following a recent comment I have updated the documentation for finding the section properties of groups of shapes in the Section-Properties-Group spreadsheet.  The revised file can be downloaded from:

Section Properties07-Group.zip

The screenshots below illustrate use of the Group function to find the composite section properties for a circular concrete section with steel reinforcement, taking account of the relative stiffness of the two materials.

First define the concrete shape on the DefShapes sheet:

Note that X and Y axes are tangential to the circle.  To simplify the generation of the reinforcement we want the circle centre at the origin, so in the groups table on the same sheet enter an offset of -R (450mm) for X and Y.  Enter 1 for the elastic modulus, then click the “Create new group” button:

The area should now be the same as in the section property results, but the centroid is moved to the origin, and the associated properties about the X and Y axes will be different.

The reinforcement diameter should now be defined in the section properties input:

The details of the reinforcing bars are now added in the group table.  Note that:

  • The bar data overwrites the data for the concrete.
  • The X and Y offsets are defined so that the centre of all the bars is on the Y axis at the required level (in this case 450 mm – 50 mm cover -24 mm bar diameter).
  • The individual bars are defined by a rotation angle about the origin.
  • The elastic modulus is defined as the modulus for steel/concrete (200/30) -1, because the steel is displacing concrete.

After entering the required details click the “Add shapes to group” button.  The Group Properties table will update:

The combined shape and group properties can now be seen on the “Coords_Group” sheet; click the “Recalc Group Properties” button, then the “Plot Group” button:

Details of each shape can also be viewed on the “Group data” sheet.  This table should always be checked to ensure that the correct number of elements are included.  Note that if shapes are accidentally added twice at the same location, this will not be obvious on the plot, but they will appear twice in the group data table.

The shape can also be modified by adding additional groups, or subtracting shapes using a negative elastic modulus.  In the example below the section properties are adjusted for the bottom region of the concrete being in tension, and therefore deducted from the concrete area.  The tension region is defined as a circular segment with a chord angle of +- 60degrees from the X axis:

This is then entered in the group table, rotated  through -90 degrees, so that the chord is parallel to the X axis:

Finally the three  reinforcement bars now in the tension zone are no longer displacing active concrete, so the area of these bars should be  added back, with an elastic modulus of 1:

The revised cross-section and section properties may now be plotted on the Coords_Group sheet:

 

 

Posted in Coordinate Geometry, Excel, Maths, Newton, Numerical integration, UDFs, VBA | Tagged , , , , , | Leave a comment

Passing variable argument lists from Excel to Python via xlwings

When calling a Python function it is sometimes necessary to pass a variable number of arguments.  Examples from the xlw_Scipy spreadsheet include:

  • Curve fitting functions, for which the functions to be fitted may have any number of arguments.
  • The Scipy statistics and distance functions have different optional arguments, depending on the statistic or distance metric being used.

Options to provide the required data are:

  • Pass a single range to Python, and extract the required arguments in Python code.
  • Pass a single range to VBA, and extract the required arguments in VBA code, passing  to Python as a single array.
  • Use a VBA ParamArray argument to combine any number of VBA arguments into a single array which can be passed to Python.
  • Use a single range or ParamArray to pass pairs of argument names and values to Python, where it can be easily converted to a dictionary.
  • Use the xlwings Dictionary converter, that turns a 2 column range into Python dictionary format.

The advantages and disadvantages of these methods are discussed below, with examples in the spreadsheet PassParamarray.xlsb and associated Python code, which can be downloaded from PassParamarray.zip.

In the examples below data is passed to Python functions in various formats.  The functions return two columns with the value of each argument (or the value of the first element in the case of an array), and the argument data type.

In the first three examples the data is passed to VBA as a single column or row.  In the first two cases this array is passed directly to the Python function.  In the third any text defining a spreadsheet range is converted to a variant array, before being passed to Python.

The VBA code for the xl_Range1 function is:

Function xl_Range1(DRange As Variant)
Dim NRows As Long, i As Long, RangeA() As Variant

DRange = DRange.Value2
NRows = UBound(DRange)
ReDim RangeA(1 To NRows)
For i = 1 To NRows
On Error Resume Next
RangeA(i) = Range(DRange(i, 1)).Value2
If IsArray(RangeA(i)) = False Then
If RangeA(i) = 0 Then RangeA(i) = DRange(i, 1)
RangeA(i) = Array(RangeA(i))
End If
Next i
xl_Range1 = RtnRange1(RangeA)
End Function

xl_Range1 calls the auto-generated VBA function RtnRange1, which passes the data to the Python function:

@xw.func
@xw.arg('rng', ndim = 1)    
def RtnRange1(rng): 
    """
    Return a value and  data type from each value in rng
    rng contains arrays or values 
    """
    rtn = []
    i =0
    for val in rng:
        if type(val) is tuple:
            if type(val[0]) is tuple:
                rtn.append([val[0][0], str(type(val))])
            else:
                rtn.append([val[0], str(type(val))])
        else:
            rtn.append([val, str(type(val))])
    return rtn

Passing a single range, the arguments must be either single values, or range addresses or names that can be converted to arrays in the VBA code. An alternative is to use the VBA ParamArray object, that allows any number of values or ranges to be passed to VBA, as shown in the three examples below:

The xlwParamA function (first two examples above) converts each element of the ParamArray to an array of values.

Function xlwParamA(ParamArray x() As Variant)
Dim Res As Variant, i As Long
   On Error GoTo rtnerr:
   
    For i = 0 To UBound(x)
        If TypeName(x(i)) = "Range" Then x(i) = x(i).Value2
        If IsArray(x(i)) = False Then x(i) = Array(x(i))
    Next i
    
    Res = Py.CallUDF("PassParamarray", "RtnArray", Array(x), ThisWorkbook, Application.Caller) 'RtnArray(x)

    xlwParamA = Res
    Exit Function
rtnerr:
    xlwParamA = Err.Description
    
End Function

The ParamArray object is then passed to the Python RtnArray function:

@xw.func
def RtnArray(x): 
    """
    Return a value and  data type from each value in rng
    """
    rtn = []
    for val in x:
        if type(val) is tuple:
            if type(val[0]) is tuple:
                rtn.append([val[0][0], str(type(val))])
            else:
                rtn.append([val[0], str(type(val))])
        else:
            rtn.append([val, str(type(val))])
    return rtn

In the third example the data is passed to Python as a list of arguments, using the Python *args notation:

@xw.func
def RtnRange3(*args): 
    """
    Return a value and  data type from each value in rng
    rng contains arrays or values 
    """
    rtn = []
    i =0
    for val in args:
        if type(val) is list:
            if type(val[0]) is list:
                rtn.append([val[0][0], str(type(val))])
            else:
                rtn.append([val[0], str(type(val))])
        else:
            rtn.append([val, str(type(val))])
    return rtn

xlwings recognises Python *args as being equivalent to a VBA ParamArray, and automatically generates appropriate code for the UDF:

Function RtnRange3(ParamArray args())
        If TypeOf Application.Caller Is Range Then On Error GoTo failed
        ReDim argsArray(1 To UBound(args) - LBound(args) + 1)
        For k = LBound(args) To UBound(args)
        argsArray(1 + k - LBound(args)) = args(k)
        Next k
        RtnRange3 = Py.CallUDF("PassParamarray", "RtnRange3", argsArray, ThisWorkbook, Application.Caller)
        Exit Function
failed:
        RtnRange3 = Err.Description
End Function

The final set of examples transfer pairs of values, or values and ranges, in a format that can easily be converted to a Python dictionary and used as keyword arguments for Python functions. In these examples the Python function returns a three column range, with the dictionary keys, as well as the value and data type for each item:

The first three examples above are similar to the earlier single column examples, except with the addition of data for the dictionary keys (see the download file for VBA and Python code).

The next two examples make use of the VBA ParamArray object to pass an array of values or arrays. As before, the version using the Python *args argument allows the VBA code to be generated automatically, and is recommended:

@xw.func
def RangeDict3(*args): 
    """
    Convert paramarray to dictionary and return index, value and types 
    """
    kwargs = dict(args[i:i+2] for i in range(0, len(args), 2))
    rtn = []
    for indx in kwargs:
        rtnv = kwargs[indx]
        if type(rtnv)is list: rtnv = rtnv[0]
        if type(rtnv)is list: rtnv = rtnv[0]
        rtn.append([indx, rtnv, str(type(kwargs[indx]))])
    
    return rtn

Using the xlwings add-in Import Functions automatically generates the VBA code for the UDF:

Function RangeDict3(ParamArray args())
        If TypeOf Application.Caller Is Range Then On Error GoTo failed
        ReDim argsArray(1 To UBound(args) - LBound(args) + 1)
        For k = LBound(args) To UBound(args)
        argsArray(1 + k - LBound(args)) = args(k)
        Next k
        RangeDict3 = Py.CallUDF("PassParamarray", "RangeDict3", argsArray, ThisWorkbook, Application.Caller)
        Exit Function
failed:
        RangeDict3 = Err.Description
End Function

Finally the xlwings Dictionary converter allows a two column range to be passed as keys and values of a Python dictionary, without additional coding:

@xw.func
@xw.arg('args', dict)
def RangeDict(args): 
    """
    Return a specified item from a dictionary of range addresses
    """
    rtn = []
    
    for indx in args:
        rtnv = args[indx]
        rtn.append([indx, rtnv, str(type(rtnv))])
    
    return rtn
Posted in Arrays, Excel, Link to Python, NumPy and SciPy, UDFs, VBA, xlwings | Tagged , , , , , , , , | Leave a comment