There have been several posts here about generating animations of the Mandelbrot set, most recently at Display Matplotlib animations in Excel, but using standard 64 bit floats you rapidly run out of precision. However, using arbitrary precision arithmetic, and with sufficient memory, processing power, and time, you can go much further, as displayed here (and accompanied by Beethoven):
For those who want to do their own investigation of this region of the Mandelbrot Set, it is defined by:
Coordinates : Re : -1.74995768370609350360221450607069970727110579726252077930242837820286008082972804887218672784431700831100544507655659531379747541999999995 Im : 0.00000000000000000278793706563379402178294753790944364927085054500163081379043930650189386849765202169477470552201325772332454726999999995
Following the previous post, I have added a further new function, and in the process discovered that the py_Estress function was not handling tensile axial loads correctly. That has now been fixed, and the updated spreadsheet and Python files can be downloaded from:
The new function calculates creep and shrinkage effects on section curvature for a rectangular reinforced or prestressed concrete section, under any specified bending moment and axial load.
See the download file for full details of the required input and an example:
As before, for details of the pyxll app required to connect the Python code to Excel see Python and pyxll.
Following the previous post I have updated the py_RC Elastic spreadsheet and code to work with the new solver function, and also add new functionality. The updated files can be downloaded from:
For details of the pyxll app required to conect the Python code to Excel see Python and pyxll.
As reported in the previous post, the py_CurveatMA function returns the curvature of a reinforced concrete section for a specified axial force and bending moment, with non-linear steel and concrete properties (in spite of the spreadsheet name!):
The py_CurveatMA function calls the functions py_SectForceMV, Stressatx and py_EC2ForceM, which can also be called directly from the spreadsheet:
New functions will calculate the development of shrinkage and creep over time, according to the AS 3600, AS 5100.5, Eurocode 2, or fib model code provisions:
Also “basic drying shrinkage” (to AS 3600) can be calculated from test results:
Finally the EC2Props function returns 13 concrete properties defined in Eurocode 2, for a specified compressive strength:
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:
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.
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:
Newton Excel Bach, not (just) an Excel Blog 