The pyxll blog has a post from last year on Monte Carlo Simulations in Excel with Python, with examples and full code going from the simplest implementation to more refined code allowing all the calculations to be set up on the spreadsheet, with the simulation just requiring a single call of the Python code.
The calculation modelled in the pyxll blog example is simply the addition of 3 variables. In this post I have used the same code to do a more complex engineering-based analysis, the evaluation of the ultimate bending capacity of a reinforced concrete section subject to combined bending and axial load. This uses the Py_UMom function, last presented at: py_UMom 1-05, and available for download at:
The rest of the required code, and the example spreadsheet, can be downloaded from:
Most of the code is copied directly from the pyxll post, with the addition of a link to the py_UMom code and a new function calling the Scipy Norm function to generate the samples, rather than the PERT function. Note that the examples are purely to demonstrate the process. The values I used to define the range of the distributions were based purely on what “seemed about right” and have no significance beyond that.
The first example below has minimal change to the layout used in the pyxll blog:
The py_UMom function input includes an array of 12 cells defining the cross section dimensions and material properties. Of these, 3 are adjusted in the Monte Carlo simulation, with the rest remaining constant. In the example above the parameters defining the range of the sample distribution are listed adjacent to the input value to be adjusted, followed by the PertRandomVariable function.
In the next example the random variables are listed separately, with the output cell defined in the PertRandomVariable functions input:
This generates similar output to the first example, with the small change in the results being due to the Monte Carlo process:
The final example uses the same analysis process, but uses the Scipy Norm function to generate the input random distributions:
class NormRandomVariable(RandomVar):
"""Random variable using the normal distribution."""
def __init__(self,
name: str,
target: XLCell,
mean_value: float,
std_dev: float):
super().__init__(name, target)
self.__dist = norm(loc=mean_value, scale=std_dev)
def samples(self, n, seed=None):
return self.__dist.rvs(size=n, random_state=seed)
The range of the variable distributions is now defined with two values, the mean and the standard deviation. The results are very similar to the PERT distribution:
Newton Excel Bach, not (just) an Excel Blog 