Scipy solver update

Posted on 31/05/2024 by dougaj4

The py_SolveFS function calls the Scipy optimize.root function to solve multi-variable equations. Examples are found on the py_Solvers spreadsheet, see Scipy Functions with Excel and pyxll 4 – Solvers 2.

One problem with using this function is that the input data must be arranged to suit the Scipy root function, as shown on the help screen below:

The root function requires an initial guess for each of the unknown values (x0). Any additional known variables are passed in the args parameter, which is a tuple, that is a list or list of lists. If py_SolveFS is called from an interface function, that function can arrange the input in the required format, but if it is called directly from the spreadsheet, and there are many input values, it is convenient if the data can be arranged in a range, with each column being treated as a 1D array, that will be converted to a Python list.

To allow this to be used in a flexible way I have added an optional TransposeVals argument to the py_SolveFS function, with a default value of True. If input arrays are arranged row-wise on the spreadsheet this should be set to False.

The revised inputs for the function are:

If TransposeVals is omitted or set to False the Values data must be arranged in columns, either as a single column:

Or as a range with two or more columns:

In the example above the py_SolveFS function is called both indirectly with py_CurveatMA, and directly. For the indirect case the Values data is in Columns C and F. For the direct case the data has been copied to adjacent columns (J and K). In both cases the target values are passed as a single column array, which is combined with the Values data in py_SolveFS.

The new version of py_SolveFS is included in the Scipy download file at:

py_Scipy.zip

For information on the pyxll package, required to connect the Python code to Excel, see: https://newtonexcelbach.com/python-and-pyxll/.

Posted in Excel, Link to Python, NumPy and SciPy, PyXLL, UDFs | Tagged , , , , , , , | Leave a comment

Regular Expressions in Excel

Posted on 25/05/2024 by dougaj4

I recently discovered that Microsoft are working on Excel functions to work with Regular Expressions. A beta version is currently available to those on the “insider” program, but this functionality has long been available through VBA, and can also be accessed from Python, using pyxll.

Details of the new Excel functions are at:

https://insider.microsoft365.com/en-us/blog/new-regular-expression-regex-functions-in-excel

I have previously covered the VBA use of regular expressions in detail at:

https://newtonexcelbach.com/2014/11/30/extracting-numbers-from-text-and-regular-expressions/
https://newtonexcelbach.com/2014/12/08/more-on-regular-expressions/
https://newtonexcelbach.com/2017/03/11/extracting-numbers-from-text-update/

Detailed documentation of the Python code for regular expressions is at:

Excel Regex Tutorial (Regular Expressions)

From the Analyst Cave tutorial I was able to very quickly write four Excel user defined functions using pyxll to access the Python code:

import re

@xl_func()
@xl_arg('regexp', 'str')
@xl_arg('txt', 'str')
def RESearch(regexp, txt, rtn = 3):
    """
Returns a Match object if there is a match for regexp anywhere in txt

:param regexp: Regular expression
:param txt: Text to be searched
:param rtn: Value to be returned; 1 = span array for match location, 2 = input txt, 3 = list of matches (default)
    """
    x = re.search(regexp, txt)
    try:
        # span() string group()
        if rtn == 1:
            return x.span()
        elif rtn == 2:
            return x.string
        else:
            return x.group()
    except:
        return "No match"

@xl_func()
@xl_arg('regexp', 'str')
@xl_arg('txt', 'str')
def REFindall(regexp, txt):
    """
Returns a list containing all matches 

:param regexp: Regular expression
:param txt: Text to be searched
    """
    x = re.findall(regexp, txt)
    try:
        return x
    except:
        return "No match"

@xl_func()
@xl_arg('regexp', 'str')
@xl_arg('txt', 'str')
def RESplit(regexp, txt):
    """
Returns a list where the string has been split at all matches 

:param regexp: Regular expression
:param txt: Text to be searched
    """
    x = re.split(regexp, txt)
    try:
        return x
    except:
        return "No match"

@xl_func()
@xl_arg('regexp', 'str')
@xl_arg('replace', 'str')
@xl_arg('txt', 'str')
def RESub(regexp, replace, txt):
    """
Replaces one or many matches with a string 

:param regexp: Regular expression
:param txt: Text to be searched
    """
    x = re.sub(regexp, replace, txt)
    try:
        return x
    except:
        return "No match"

Example results are shown below, using an example from the Microsoft site:

Posted in Excel, Link to Python, PyXLL, UDFs, VBA | Tagged , , , , , , | Leave a comment

Pale Blue Dot

Posted on 24/05/2024 by dougaj4

34 years ago Carl Sagan presented an image of the Earth taken from outer space, known as the pale blue dot.

An event very different from a 13 year old boy singing a song he had written himself, but the message was the same:

Posted in Climate, Newton | Tagged , | Leave a comment

Rory Phillips – The Truth

Posted on 13/05/2024 by dougaj4

“‘The Truth’ is a passionate call to action on climate change from 13 year old Rory Phillips. “I wanted to say something with this song. The lyrics are subtle, not in your face; but the message is clear. We need to do more, from the top down.”

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

Creating sequential lists in Excel

Posted on 08/05/2024 by dougaj4

I recently discovered that Excel has a new SEQUENCE function that will return a sequence of increasing numbers in a column, row, or 2D range. Use of the new function is shown in the screenshot below, with some old and new alternatives.

The formula used for each example is shown above the output, using the FORMULATEXT function.

The function arguments are:

  • Number of rows
  • Number of columns (default = 1)
  • Start value (default = 1)
  • Increment (default = 1)

The start value and increment may be integers or floats, and may be entered as values, cell references, or formulas. The example in Column I illustrates the use of the ROW function to calculate the required number of rows. This has the advantage that if rows are inserted within the output range, the range will expand to include the new number.

An alternative to the new function is using the range fill functionality, or entering a simple addition formula and copying down the required number of rows. Use of the Fill, Series command (Column L) is shown below:

  • Enter the start value in the first cell, and with that cell selected, click the Fill icon on the Editing menu (rectangle with blue down arrow). Select Series to display the dialog as shown above and select “Series in Columns” and enter the step value and stop value.

Ranges can also be filled by entering the required values in the first two rows, selecting those two cells, then dragging down the bottom right hand corner of the selected range:

Finally, since Python has a range function that will generate a sequential list of integers, it was quite simple to call this from Excel, using pyxll:

@xl_func
@xl_arg('start', 'int')
@xl_arg('stop', 'int')
@xl_arg('step', 'int')
@xl_return('numpy_array<int>', ndim=1)
def py_Range(start, stop = '', step = 1):
    if type(stop) == str:
        return np.array(range(0, start))
    else:    
        return np.array(range(start, stop, step))

The last two examples in the first screenshot show the Python function results. Note that:

  • The function will always return a single column.
  • Input is the start and stop values, and optional step, rather than number of values and start value.
  • The default start value is zero, rather than 1.
  • The ‘stop’ value is the lowest value not included in the output.
Posted in Excel, Link to Python, PyXLL, UDFs | Tagged , , , , | Leave a comment