{"id":1912,"date":"2025-04-30T14:57:58","date_gmt":"2025-04-30T14:57:58","guid":{"rendered":"https:\/\/www.pyxll.com\/blog\/?p=1912"},"modified":"2026-04-17T07:17:49","modified_gmt":"2026-04-17T07:17:49","slug":"monte-carlo-simulations-in-excel-with-python","status":"publish","type":"post","link":"https:\/\/www.pyxll.com\/blog\/monte-carlo-simulations-in-excel-with-python\/","title":{"rendered":"Monte Carlo Simulations in Excel with Python"},"content":{"rendered":"\n<p>Monte Carlo simulations are essential as an analytical tool in various fields, from finance to project management. Using Python and the PyXLL add-in we&#8217;ll explore how to bring the Monte Carlo method to Excel.<\/p>\n\n\n\n<p>This Monte Carlo statistical technique allows us to understand and quantify uncertainty by performing repeated random sampling to obtain numerical results.<\/p>\n\n\n\n<p>In the context of Excel and Python, Monte Carlo simulations enable users to leverage powerful analysis without leaving their familiar spreadsheet environment.<\/p>\n\n\n\n<p>In this article, we will explore how to implement Monte Carlo simulations in Excel using the PyXLL add-in and Python, providing a step-by-step guide and actionable examples to enhance your analytical capabilities.<\/p><div id=\"ez-toc-container\" class=\"ez-toc-v2_0_82_2 counter-hierarchy ez-toc-counter ez-toc-grey ez-toc-container-direction\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">Table of Contents<\/p>\n<label for=\"ez-toc-cssicon-toggle-item-69e598dfa0282\" class=\"ez-toc-cssicon-toggle-label\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #999;color:#999\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #999;color:#999\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/label><input type=\"checkbox\"  id=\"ez-toc-cssicon-toggle-item-69e598dfa0282\"  aria-label=\"Toggle\" \/><nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/www.pyxll.com\/blog\/monte-carlo-simulations-in-excel-with-python\/#Why_Use_Monte_Carlo_Simulations\" >Why Use Monte Carlo Simulations?<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/www.pyxll.com\/blog\/monte-carlo-simulations-in-excel-with-python\/#A_First_Simple_Monte_Carlo_Simulation\" >A First (Simple) Monte Carlo Simulation<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/www.pyxll.com\/blog\/monte-carlo-simulations-in-excel-with-python\/#Step_1_Defining_the_Simulation_Function\" >Step 1: Defining the Simulation Function<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/www.pyxll.com\/blog\/monte-carlo-simulations-in-excel-with-python\/#Step_2_Running_the_simulation_in_Excel\" >Step 2: Running the simulation in Excel<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/www.pyxll.com\/blog\/monte-carlo-simulations-in-excel-with-python\/#Step_3_Getting_the_standard_deviation_of_our_result\" >Step 3: Getting the standard deviation of our result<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/www.pyxll.com\/blog\/monte-carlo-simulations-in-excel-with-python\/#Step_4_Plotting_the_results_as_a_histogram\" >Step 4: Plotting the results as a histogram<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-7\" href=\"https:\/\/www.pyxll.com\/blog\/monte-carlo-simulations-in-excel-with-python\/#Monte_Carlo_for_Excel_Models\" >Monte Carlo for Excel Models<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-8\" href=\"https:\/\/www.pyxll.com\/blog\/monte-carlo-simulations-in-excel-with-python\/#Tweaking_Excel_Model_Inputs\" >Tweaking Excel Model Inputs<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-9\" href=\"https:\/\/www.pyxll.com\/blog\/monte-carlo-simulations-in-excel-with-python\/#Defining_the_Random_Variables_in_Excel\" >Defining the Random Variables in Excel<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-10\" href=\"https:\/\/www.pyxll.com\/blog\/monte-carlo-simulations-in-excel-with-python\/#Multiple_Input_Variables\" >Multiple Input Variables<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-11\" href=\"https:\/\/www.pyxll.com\/blog\/monte-carlo-simulations-in-excel-with-python\/#Run_Faster\" >Run Faster<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-12\" href=\"https:\/\/www.pyxll.com\/blog\/monte-carlo-simulations-in-excel-with-python\/#Removing_the_last_hard_coded_cell\" >Removing the last hard coded cell<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-13\" href=\"https:\/\/www.pyxll.com\/blog\/monte-carlo-simulations-in-excel-with-python\/#Further_Improvements\" >Further Improvements<\/a><\/li><\/ul><\/li><\/ul><\/nav><\/div>\n\n\n\n\n<p><strong>The contents of this blog post is covered in the following video:<\/strong><\/p>\n\n\n\n<figure class=\"wp-block-embed is-type-video is-provider-youtube wp-block-embed-youtube wp-embed-aspect-16-9 wp-has-aspect-ratio\"><div class=\"wp-block-embed__wrapper\">\n<span class=\"embed-youtube\" style=\"text-align:center; display: block;\"><iframe loading=\"lazy\" class=\"youtube-player\" width=\"640\" height=\"360\" src=\"https:\/\/www.youtube.com\/embed\/Va9ih1DXPDs?version=3&#038;rel=1&#038;showsearch=0&#038;showinfo=1&#038;iv_load_policy=1&#038;fs=1&#038;hl=en-US&#038;autohide=2&#038;wmode=transparent\" allowfullscreen=\"true\" style=\"border:0;\" sandbox=\"allow-scripts allow-same-origin allow-popups allow-presentation allow-popups-to-escape-sandbox\"><\/iframe><\/span>\n<\/div><\/figure>\n\n\n\n<p>The code accompanying this post can be found here <a href=\"https:\/\/github.com\/pyxll\/pyxll-examples\/tree\/master\/montecarlo\" target=\"_blank\" rel=\"noreferrer noopener\">https:\/\/github.com\/pyxll\/pyxll-examples\/blob\/master\/montecarlo<\/a><\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-why-use-monte-carlo-simulations\"><span class=\"ez-toc-section\" id=\"Why_Use_Monte_Carlo_Simulations\"><\/span>Why Use Monte Carlo Simulations?<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>Monte Carlo simulations help model the behavior of complex systems where uncertainty is a factor. Instead of relying solely on deterministic inputs, this approach incorporates randomness into the model. For instance, consider a project cost estimation: rather than specifying fixed costs, Monte Carlo simulations allow us to define a range of possible costs and compute the probability of various total costs.<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Assess Uncertainty<\/strong>: Understand potential variations in project outcomes.<\/li>\n\n\n\n<li><strong>Flexible Models<\/strong>: Incorporate various distributions to reflect real-world scenarios, such as normal, uniform, or triangular distributions.<\/li>\n\n\n\n<li><strong>Informed Decision-Making<\/strong>: Aid in risk assessment and better strategic planning by quantifying the likelihood of different scenarios.<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-a-first-simple-monte-carlo-simulation\"><span class=\"ez-toc-section\" id=\"A_First_Simple_Monte_Carlo_Simulation\"><\/span>A First (Simple) Monte Carlo Simulation<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>PyXLL is an Excel add-in that enables you to write functions and create macros in Python, providing an interface to easily integrate Python functionality into Excel worksheets.<\/p>\n\n\n\n<p>Before diving into the code, ensure you have PyXLL installed and running in your Excel environment. For more information or to get started, visit&nbsp;<a href=\"https:\/\/www.pyxll.com\/\">PyXLL&#8217;s official documentation<\/a>.<\/p>\n\n\n\n<p><em>If you are already familiar with PyXLL and Monte-Carlo already you may prefer to skip ahead to the next section.<\/em><\/p>\n\n\n\n<p>We will create a simple project cost estimation model using Monte Carlo simulations. Let\u2019s represent the cost estimates as random variables and use Python to generate outputs based on these estimates.<\/p>\n\n\n\n<p>Our &#8220;model&#8221; will simply be the sum of the cost estimates, drawn from a normal distribution. We repeat the calculation a number of times and return the mean result.<\/p>\n\n\n\n<p>This is a simple case where we define model in Python. For real world use-cases we would want to be able to construct more complex models using Excel, but we will come on to that later.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-step-1-defining-the-simulation-function\"><span class=\"ez-toc-section\" id=\"Step_1_Defining_the_Simulation_Function\"><\/span>Step 1: Defining the Simulation Function<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Begin by creating a Python function that will run the simulation.<\/li>\n\n\n\n<li>Use the PyXLL &#8220;@xl_func&#8221; decorator to expose this function to Excel.<\/li>\n<\/ol>\n\n\n\n<p>Here\u2019s an example code snippet:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: python; title: ; notranslate\" title=\"\">\nfrom pyxll import xl_func, xl_app\nimport numpy as np\n\n@xl_func(&quot;int n, float&#x5B;] means, float&#x5B;] stddevs: float&quot;)\ndef run_simulation(n, means, stddevs):\n    results = &#x5B;]\n    for _ in range(n):\n        # draw some random samples\n        cost_estimates = np.random.normal(means, stddevs)\n\n        # our &quot;model&quot; is just the sum of the estimates\n        results.append(cost_estimates.sum())\n\n    # return the mean of all the results\n    return sum(results) \/ n\n<\/pre><\/div>\n\n\n<p>The function takes a number of times to run the model (n), a list of the mean for the cost estimates (means) and a list of the standard deviations of the cost estimates (stddevs).<\/p>\n\n\n\n<p>The @xl_func decorator tells the PyXLL add-in to make this function available to us in Excel. We have given it a &#8220;signature string&#8221; to tell it the types of the arguments, and the return type. PyXLL uses this when it exposes the function to Excel, so it can be called correctly.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-step-2-running-the-simulation-in-excel\"><span class=\"ez-toc-section\" id=\"Step_2_Running_the_simulation_in_Excel\"><\/span>Step 2: Running the simulation in Excel<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>To run your Python code in Excel you need to first have the PyXLL add-in installed. You can find instructions for how to do that here <a href=\"https:\/\/www.pyxll.com\/docs\/userguide\/installation\/index.html\">https:\/\/www.pyxll.com\/docs\/userguide\/installation\/index.html<\/a>.<\/p>\n\n\n\n<p>Once you have PyXLL installed and working, you just need to add your Python module to the &#8220;modules&#8221; list in the pyxll.cfg configuration file. You should also add the folder you&#8217;ve saved the module to the &#8220;pythonpath&#8221; setting in the same file, if it&#8217;s not included there already.<\/p>\n\n\n\n<p>After doing that, start Excel (or click &#8220;Reload&#8221; in the PyXLL menu to reload the Python code) and the &#8220;run_simulation&#8221; function we wrote above is now available to be called directly from the worksheet!<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><a href=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2025\/04\/image.png\"><img decoding=\"async\" width=\"463\" height=\"385\" data-attachment-id=\"1913\" data-permalink=\"https:\/\/www.pyxll.com\/blog\/monte-carlo-simulations-in-excel-with-python\/image-24\/\" data-orig-file=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2025\/04\/image.png\" data-orig-size=\"463,385\" data-comments-opened=\"0\" data-image-meta=\"{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;,&quot;orientation&quot;:&quot;0&quot;}\" data-image-title=\"image\" data-image-description=\"\" data-image-caption=\"\" data-large-file=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2025\/04\/image.png\" src=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2025\/04\/image.png\" alt=\"\" class=\"wp-image-1913\" srcset=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2025\/04\/image.png 463w, https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2025\/04\/image-300x249.png 300w\" sizes=\"(max-width: 463px) 100vw, 463px\" \/><\/a><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-step-3-getting-the-standard-deviation-of-our-result\"><span class=\"ez-toc-section\" id=\"Step_3_Getting_the_standard_deviation_of_our_result\"><\/span>Step 3: Getting the standard deviation of our result<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>In steps 1 and 2 we saw how we can write a Python function and call it from Excel, but currently that function only returns us the mean result. To understand how certain that result is we need to also calculate the standard deviation of the result.<\/p>\n\n\n\n<p>Instead of returning just a number, we can return the entire list of results. But, we don&#8217;t want to see those as a massive list in Excel! We might be doing many thousands of samples and there&#8217;s no need for us to see every single one and it would take up too much space in our workbook.<\/p>\n\n\n\n<p>So, we change the return type in the @xl_func decorator to &#8220;object&#8221;. This returns the Python object to Excel as a single value object reference. We can then pass that to other Python functions. We&#8217;ll write two more functions as well, one to calculate the mean and one to calculate the standard deviation:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: python; title: ; notranslate\" title=\"\">\nfrom pyxll import xl_func, xl_app\nimport numpy as np\n\n@xl_func(&quot;int n, float&#x5B;] means, float&#x5B;] stddevs: object&quot;)\ndef run_simulation(n, means, stddevs):\n    results = &#x5B;]\n    for _ in range(n):\n        # draw some random samples\n        cost_estimates = np.random.normal(means, stddevs)\n\n        # our &quot;model&quot; is just the sum of the estimates\n        results.append(cost_estimates.sum())\n\n    # return all the results\n    return results\n\n@xl_func(&quot;object results: float&quot;)\ndef mc_mean(results):\n    &quot;&quot;&quot;Return the mean of a list of results.&quot;&quot;&quot;\n    if not results:\n        return &quot;# No results&quot;\n    return np.mean(results)\n\n\n@xl_func(&quot;object results: float&quot;)\ndef mc_stddev(results):\n    &quot;&quot;&quot;Return the standard deviation of a list of results.&quot;&quot;&quot;\n    if not results:\n        return &quot;# No results&quot;\n    return np.std(results)\n<\/pre><\/div>\n\n\n<p>When the &#8220;run_simulation&#8221; function runs in Excel we get an object handle returned, which we then pass to our new functions &#8220;mc_mean&#8221; and &#8220;mc_stddev&#8221;.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><a href=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2025\/04\/image-1.png\"><img decoding=\"async\" width=\"444\" height=\"427\" data-attachment-id=\"1914\" data-permalink=\"https:\/\/www.pyxll.com\/blog\/monte-carlo-simulations-in-excel-with-python\/image-25\/\" data-orig-file=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2025\/04\/image-1.png\" data-orig-size=\"444,427\" data-comments-opened=\"0\" data-image-meta=\"{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;,&quot;orientation&quot;:&quot;0&quot;}\" data-image-title=\"image\" data-image-description=\"\" data-image-caption=\"\" data-large-file=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2025\/04\/image-1.png\" src=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2025\/04\/image-1.png\" alt=\"\" class=\"wp-image-1914\" srcset=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2025\/04\/image-1.png 444w, https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2025\/04\/image-1-300x289.png 300w\" sizes=\"(max-width: 444px) 100vw, 444px\" \/><\/a><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-step-4-plotting-the-results-as-a-histogram\"><span class=\"ez-toc-section\" id=\"Step_4_Plotting_the_results_as_a_histogram\"><\/span>Step 4: Plotting the results as a histogram<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>Getting the mean and standard deviation is useful, but sometimes it helps to see the data plotted as a histogram. With PyXLL we can use all of the major Python plotting libraries. Seaborn is one of those plotting libraries, and that&#8217;s what we&#8217;ll use to plot the histogram of results.<\/p>\n\n\n\n<p>We&#8217;ll write one more Python function. This one takes the list of results, as the &#8220;mc_mean&#8221; and &#8220;mc_stddev&#8221; functions do, but rather than returning a number, this function will plot a chart using the PyXLL &#8220;plot&#8221; function:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: python; title: ; notranslate\" title=\"\">\nimport matplotlib.pyplot as plt\nimport seaborn as sns\n\n@xl_func(&quot;object results: str&quot;)\ndef mc_plot_sim(results):\n    &quot;&quot;&quot;Plot the results of a simulation run.&quot;&quot;&quot;\n    # create a matplotlib figure and axes to plot to\n    fig, ax = plt.subplots()\n\n    # use Seaborn to plot the results as a histogram\n    sns.histplot(results, ax=ax)\n\n    # Display the figure in Excel\n    plot(fig)\n<\/pre><\/div>\n\n\n<figure class=\"wp-block-image size-full\"><a href=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2025\/04\/image-2.png\"><img decoding=\"async\" width=\"951\" height=\"552\" data-attachment-id=\"1915\" data-permalink=\"https:\/\/www.pyxll.com\/blog\/monte-carlo-simulations-in-excel-with-python\/image-26\/\" data-orig-file=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2025\/04\/image-2.png\" data-orig-size=\"951,552\" data-comments-opened=\"0\" data-image-meta=\"{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;,&quot;orientation&quot;:&quot;0&quot;}\" data-image-title=\"image\" data-image-description=\"\" data-image-caption=\"\" data-large-file=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2025\/04\/image-2.png\" src=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2025\/04\/image-2.png\" alt=\"\" class=\"wp-image-1915\" srcset=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2025\/04\/image-2.png 951w, https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2025\/04\/image-2-300x174.png 300w, https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2025\/04\/image-2-768x446.png 768w\" sizes=\"(max-width: 951px) 100vw, 951px\" \/><\/a><\/figure>\n\n\n\n<p>This is a (very) simple example. In the next section we&#8217;ll get in to how to do apply the same basic technique but where the model itself is built in Excel.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-monte-carlo-for-excel-models\"><span class=\"ez-toc-section\" id=\"Monte_Carlo_for_Excel_Models\"><\/span>Monte Carlo for Excel Models<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>In the sections above we saw how we can write Python functions and call from them from Excel. We wrote a simple function that ran a simple model to generate total cost estimates drawing from a set of normal distributions.<\/p>\n\n\n\n<p>Real world cases are more complex than this and very often we want to build the model part in Excel. For example, if you have a spreadsheet that calculates total cost (or it could be anything) from some input assumptions, you might want to know what the output would look like if those input assumptions were drawn from distributions rather than being fixed numbers.<\/p>\n\n\n\n<p>You might have done something where you know <em>roughly<\/em> what your input numbers are and put those in Excel, but then you think &#8220;what if this number increases by 10%&#8221;, or something like that, and so you do some experiments seeing how different inputs affect the output to get a <em>feel<\/em> for the model. Monte Carlo is like that, but with a little more mathematical rigor behind it.<\/p>\n\n\n\n<p>What we&#8217;ll do in this next section is see how we can use PyXLL and Python to vary inputs to an Excel model, using a Monte Carlo style approach, sample the output value as calculated by Excel, and present the results.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-tweaking-excel-model-inputs\"><span class=\"ez-toc-section\" id=\"Tweaking_Excel_Model_Inputs\"><\/span>Tweaking Excel Model Inputs<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>The basic idea of what we&#8217;re building is that we will have a model in Excel with the output in one cell, and some inputs in some other cells. We will control the inputs using defined distributions. We change the inputs using samples from those distributions and observe the output.<\/p>\n\n\n\n<p>For this we&#8217;ll use an Excel macro (often called &#8220;Sub&#8221; in VBA circle) instead of a worksheet function. The following code sets the value in one cell and samples the output calculated in another.<\/p>\n\n\n\n<p>For a guide to writing Excel macros in Python, see <a href=\"https:\/\/www.pyxll.com\/docs\/userguide\/macros.html\" target=\"_blank\" rel=\"noreferrer noopener\">https:\/\/www.pyxll.com\/docs\/userguide\/macros.html<\/a><\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: python; title: ; notranslate\" title=\"\">\nfrom pyxll import xl_macro, XLCell\nfrom pert import PERT  # requires &quot;pertdist&quot;\nimport numpy as np\n\n@xl_macro\ndef run_simulation():\n    # Create an example distribution and draw some samples from it\n    dist = PERT(3000, 3500, 4000)\n    samples = dist.rvs(100)\n\n    # Get the (single) input and output cells, hardcoded for now\n    input = XLCell.from_range(&quot;C5&quot;)\n    output = XLCell.from_range(&quot;C6&quot;)\n\n    results = &#x5B;]\n    for x in samples:\n        # Update the input cell value\n        input.value = x\n\n        # Collect the output cell value\n        results.append(output.value)\n\n    # Write the results to another cell\n    result_cell = XLCell.from_range(&quot;C7&quot;)\n    result_cell.options(type=&quot;object&quot;).value = results\n<\/pre><\/div>\n\n\n<p>We assign this macro to a button in Excel, and when we press the button, the code runs, updates the input cell, and collects the results.<\/p>\n\n\n\n<p>The macro writes results to cell C7, and we can then pass those results to our &#8220;<code>mc_mean\"<\/code>, &#8220;<code>mc_stddev\"<\/code>, and &#8220;<code>mc_plot_sim\"<\/code> functions just as we did in the section above.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><a href=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2025\/04\/image-3.png\"><img decoding=\"async\" width=\"993\" height=\"586\" data-attachment-id=\"1927\" data-permalink=\"https:\/\/www.pyxll.com\/blog\/monte-carlo-simulations-in-excel-with-python\/image-27\/\" data-orig-file=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2025\/04\/image-3.png\" data-orig-size=\"993,586\" data-comments-opened=\"0\" data-image-meta=\"{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;,&quot;orientation&quot;:&quot;0&quot;}\" data-image-title=\"image\" data-image-description=\"\" data-image-caption=\"\" data-large-file=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2025\/04\/image-3.png\" src=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2025\/04\/image-3.png\" alt=\"\" class=\"wp-image-1927\" srcset=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2025\/04\/image-3.png 993w, https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2025\/04\/image-3-300x177.png 300w, https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2025\/04\/image-3-768x453.png 768w\" sizes=\"(max-width: 993px) 100vw, 993px\" \/><\/a><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-defining-the-random-variables-in-excel\"><span class=\"ez-toc-section\" id=\"Defining_the_Random_Variables_in_Excel\"><\/span>Defining the Random Variables in Excel<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>The above example has several limitations, especially because the code hard-codes the random distribution used for the input. Instead we would like to be able to decide what distribution to use for each variable from Excel.<\/p>\n\n\n\n<p>To do this we will write some new Python functions that return &#8220;RandomVariable&#8221; objects, which can then be used in our simulation. We&#8217;ll write a new RandomVariable class with methods get samples from the random distribution, and we&#8217;ll also give them the ability to update an Excel cell with a sampled value.<\/p>\n\n\n\n<p>As we might want to use various different types of distribution, we&#8217;ll make RandomVariable an <em>abstract base class<\/em>. Later, we can write implementations of this class for any type of distribution we want.<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: python; title: ; notranslate\" title=\"\">\nfrom abc import ABCMeta, abstractmethod\n\nclass RandomVariable(metaclass=ABCMeta):\n    &quot;&quot;&quot;Base class for random variables.&quot;&quot;&quot;\n\n    # Each random variable has a name (which will be useful later for plotting)\n    # and also a target cell. The target cell is the input to the Excel model that\n    # we overwrite with simulated values.\n    def __init__(self, name: str, target: XLCell):\n        self.name = name\n        self.target = target\n\n    @abstractmethod\n    def samples(self, n, seed=None):\n        &quot;&quot;&quot;Return n random samples.&quot;&quot;&quot;\n        pass\n\n    def simulate(self, value):\n        self.target.value = value\n<\/pre><\/div>\n\n\n<p>Above we used the PERT distribution, and we can implement a new class derived from the RandomVariable base class that implements samples using that same distribution:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: python; title: ; notranslate\" title=\"\">\nclass PertRandomVariable(RandomVariable):\n    &quot;&quot;&quot;Random variable using the PERT distribution.&quot;&quot;&quot;\n\n    def __init__(self,\n                 name: str,\n                 target: XLCell,\n                 min_value: float,\n                 ml_value: float,\n                 max_vaue: float,\n                 lamb: float = 4.0):\n        super().__init__(name, target)\n        self.__dist = PERT(min_value, ml_value, max_vaue, lamb)\n\n    def samples(self, n, seed=None):\n        return self.__dist.rvs(size=n, random_state=seed)\n<\/pre><\/div>\n\n\n<p>Finally we need a way to create this &#8220;PertRandomVariable&#8221; object in Excel, which we do via an Excel function using the @xl_func decorator:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: python; title: ; notranslate\" title=\"\">\n@xl_func(&quot;string, xl_cell, float, float, float, float: object&quot;)\ndef mc_pert(name, target, min_value, ml_value, max_vaue, lamb=4.0):\n    &quot;&quot;&quot;Returns a RandomVariable object instance using the PERT distribution.&quot;&quot;&quot;\n    return PertRandomVariable(name, target, min_value, ml_value, max_vaue, lamb)\n<\/pre><\/div>\n\n\n<p>Note the type of the &#8220;target&#8221; argument is &#8220;xl_cell&#8221;. This tells the PyXLL add-in to pass a cell reference object to the function instead of the cell value.<\/p>\n\n\n\n<p>We can add the call to the new &#8220;mc_pert&#8221; to our sheet, using &#8220;C5&#8221; as the target input cell.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><a href=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2025\/04\/image-4.png\"><img decoding=\"async\" width=\"939\" height=\"597\" data-attachment-id=\"1928\" data-permalink=\"https:\/\/www.pyxll.com\/blog\/monte-carlo-simulations-in-excel-with-python\/image-28\/\" data-orig-file=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2025\/04\/image-4.png\" data-orig-size=\"939,597\" data-comments-opened=\"0\" data-image-meta=\"{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;,&quot;orientation&quot;:&quot;0&quot;}\" data-image-title=\"image\" data-image-description=\"\" data-image-caption=\"\" data-large-file=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2025\/04\/image-4.png\" src=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2025\/04\/image-4.png\" alt=\"\" class=\"wp-image-1928\" srcset=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2025\/04\/image-4.png 939w, https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2025\/04\/image-4-300x191.png 300w, https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2025\/04\/image-4-768x488.png 768w\" sizes=\"(max-width: 939px) 100vw, 939px\" \/><\/a><\/figure>\n\n\n\n<p>Our macro needs updating to get the random variable from cell G5, and to use that to get the random samples and set the target cell value:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: python; title: ; notranslate\" title=\"\">\n@xl_macro\ndef run_simulation():\n    # Get the random input variable as a Python object\n    rv_cell = XLCell.from_range(&quot;G5&quot;)\n    rv = rv_cell.options(type=&quot;object&quot;).value\n\n    # and output cell\n    output = XLCell.from_range(&quot;C6&quot;)\n\n    results = &#x5B;]\n    for x in rv.samples(100):\n        # Update the input cell value\n        rv.simulate(x)\n\n        # Collect the output cell value\n        results.append(output.value)\n\n    # Write the results to another cell\n    result_cell = XLCell.from_range(&quot;C7&quot;)\n    result_cell.options(type=&quot;object&quot;).value = results\n<\/pre><\/div>\n\n\n<p>We now have a working Monte Carlo solution in Excel! We can use any Excel formula as the output, and we can customize the parameters of the input distribution.<\/p>\n\n\n\n<p>Although it still has limitations, it provides a good start. The remaining issues we need to resolve are<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>We want to have multiple inputs to our model, not just one<\/li>\n\n\n\n<li>There is no way to enter the number of samples currently<\/li>\n\n\n\n<li>It is quite slow to run the simulation<\/li>\n\n\n\n<li>The final macro should not have cell references hard-coded into it<\/li>\n<\/ul>\n\n\n\n<p>The next sections address these issues.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-multiple-input-variables\"><span class=\"ez-toc-section\" id=\"Multiple_Input_Variables\"><\/span>Multiple Input Variables<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>The current &#8220;run_simulation&#8221; macro takes a single random variable input from a cell reference. Instead, we would like to be able to have multiple inputs to our model controlled by different distributions, without having to update the macro to list all of those inputs.<\/p>\n\n\n\n<p>A good way to deal with these various inputs is to collect them all into a single &#8220;Simulation&#8221; object. We&#8217;ll create a new class for that to hold all of the input random variables, the output cell, and the number of samples we want to run the simulation over.<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: python; title: ; notranslate\" title=\"\">\nclass Simulation:\n    &quot;&quot;&quot;Simulation class for managing setting inputs to random variables\n    and collecting the calculated output.\n    &quot;&quot;&quot;\n\n    def __init__(self,\n                 name: str,\n                 n: int,\n                 output: XLCell,\n                 inputs: list&#x5B;RandomVariable]):\n        self.name = name\n        self.n = n\n        self.output = output\n        self.inputs = inputs\n\n    def run(self, seed=None):\n        results = &#x5B;]\n\n        # Prepare random samples for the inputs\n        samples = &#x5B;input.samples(self.n, seed=seed) for input in self.inputs]\n\n        # Calculate the output value for each set of inputs\n        for i in range(self.n):\n            # Get the sample value for each input and set it\n            for j, input in enumerate(self.inputs):\n                input_samples = samples&#x5B;j]\n                value = input_samples&#x5B;i]\n                input.simulate(value)\n\n            # Collect the output value with the input values set\n            results.append(self.output.value)\n\n        return results\n<\/pre><\/div>\n\n\n<p>As previously, we also now need an Excel function to create this new Simulation object:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: python; title: ; notranslate\" title=\"\">\n@xl_func(&quot;str name, int n, xl_cell output, object *inputs: object&quot;)\ndef mc_simulation(name, n, output, *inputs):\n    &quot;&quot;&quot;Returns a Simulation object for use with the &#039;mc_run_simulation&#039; macro.&quot;&quot;&quot;\n    return Simulation(name, n, output, inputs)\n<\/pre><\/div>\n\n\n<p>This &#8220;mc_simulation&#8221; function takes any number of input variables. Now we update the spreadsheet to add some more variables and add our simulation object:<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><a href=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2025\/04\/image-7.png\"><img decoding=\"async\" width=\"1024\" height=\"470\" data-attachment-id=\"1935\" data-permalink=\"https:\/\/www.pyxll.com\/blog\/monte-carlo-simulations-in-excel-with-python\/image-31\/\" data-orig-file=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2025\/04\/image-7.png\" data-orig-size=\"1111,510\" data-comments-opened=\"0\" data-image-meta=\"{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;,&quot;orientation&quot;:&quot;0&quot;}\" data-image-title=\"image\" data-image-description=\"\" data-image-caption=\"\" data-large-file=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2025\/04\/image-7-1024x470.png\" src=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2025\/04\/image-7-1024x470.png\" alt=\"\" class=\"wp-image-1935\" srcset=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2025\/04\/image-7-1024x470.png 1024w, https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2025\/04\/image-7-300x138.png 300w, https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2025\/04\/image-7-768x353.png 768w, https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2025\/04\/image-7.png 1111w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/a><\/figure>\n\n\n\n<p>The &#8220;run_simulation&#8221; macro needs an update to use the simulation object in cell C10.<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: python; title: ; notranslate\" title=\"\">\n@xl_macro\ndef run_simulation():\n    # Get the simulation object from cell C10\n    cell = XLCell.from_range(&quot;C10&quot;)\n    simulation = cell.options(type=&quot;object&quot;).value\n\n    # Run the simulation\n    results = simulation.run()\n\n    # And write the results to cell below\n    cell.offset(rows=1).options(type=&quot;object&quot;).value = results\n<\/pre><\/div>\n\n\n<p>Our &#8220;run_simulation&#8221; macro is much simpler than before and now only has the cell C10 hard-coded in it (and we will fix that soon&#8230;).<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><a href=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2025\/04\/image-8.png\"><img decoding=\"async\" width=\"1024\" height=\"584\" data-attachment-id=\"1936\" data-permalink=\"https:\/\/www.pyxll.com\/blog\/monte-carlo-simulations-in-excel-with-python\/image-32\/\" data-orig-file=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2025\/04\/image-8.png\" data-orig-size=\"1137,649\" data-comments-opened=\"0\" data-image-meta=\"{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;,&quot;orientation&quot;:&quot;0&quot;}\" data-image-title=\"image\" data-image-description=\"\" data-image-caption=\"\" data-large-file=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2025\/04\/image-8-1024x584.png\" src=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2025\/04\/image-8-1024x584.png\" alt=\"\" class=\"wp-image-1936\" srcset=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2025\/04\/image-8-1024x584.png 1024w, https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2025\/04\/image-8-300x171.png 300w, https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2025\/04\/image-8-768x438.png 768w, https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2025\/04\/image-8.png 1137w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/a><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-run-faster\"><span class=\"ez-toc-section\" id=\"Run_Faster\"><\/span>Run Faster<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>You might have noticed that updating the input cells and recalculating the output takes a little while.<\/p>\n\n\n\n<p>Part of the problem is that whenever any of our input cells change, Excel recalculates the output cell. Now that we\u2019re updating multiple input cells, Excel does too much work &#8211; we only need the output after all input cells are updated.<\/p>\n\n\n\n<p>The other thing that slows things down is Excel is re-drawing the screen every time there is an update. This takes more time than you might expect and causes the whole calculation to be much slower than it needs to be.<\/p>\n\n\n\n<p>PyXLL has a convenient solution for this! The context manager <a href=\"https:\/\/www.pyxll.com\/docs\/api\/macros.html#xl-disable\" target=\"_blank\" rel=\"noreferrer noopener\">pyxll.xl_disable()<\/a> switch Excel to manual calculation mode and disables screen updating! Because calculations will be set to manual when using this, we now need to calculate the output cell manually.<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: python; highlight: [27,36]; title: ; notranslate\" title=\"\">\nfrom pyxll import xl_disable\n\nclass Simulation:\n    &quot;&quot;&quot;Simulation class for managing setting inputs to random variables\n    and collecting the calculated output.\n    &quot;&quot;&quot;\n\n    def __init__(self,\n                 name: str,\n                 n: int,\n                 output: XLCell,\n                 inputs: list&#x5B;RandomVariable]):\n        self.name = name\n        self.n = n\n        self.output = output\n        self.inputs = inputs\n\n    def run(self):\n        results = &#x5B;]\n\n        # Prepare random samples for the inputs\n        samples = &#x5B;input.samples(self.n) for input in self.inputs]\n\n        output_range = self.output.to_range()\n        \n        # Disable automatic calculations and screen updating\n        with xl_disable():\n            for i in range(self.n):\n                # Get the sample value for each input and set it\n                for j, input in enumerate(self.inputs):\n                    input_samples = samples&#x5B;j]\n                    value = input_samples&#x5B;i]\n                    input.simulate(value)\n\n                # Calculate the output range\n                output_range.Calculate()\n                \n                # Collect the output value\n                results.append(self.output.value)\n\n        return results\n<\/pre><\/div>\n\n\n<p>And with that small change our simulation now runs much much faster!<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-removing-the-last-hard-coded-cell\"><span class=\"ez-toc-section\" id=\"Removing_the_last_hard_coded_cell\"><\/span>Removing the last hard coded cell<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>We still have cell C10 hardcoded in our macro. This is not ideal as we might want to use the same macro to run different Monte Carlo simulations, and we don&#8217;t want to rely on the simulation object always being in cell C10!<\/p>\n\n\n\n<p>We can&#8217;t pass arguments or parameters directly to a macro function from a button, but what we can do it get the button from inside the macro. We can add some data to the button and then read it back in the macro. One convenient place to add data like this is in the button&#8217;s Alternative Text field.<\/p>\n\n\n\n<p>Right click the button, go to Format Control, and find the &#8220;Alt Text&#8221; tab. That&#8217;s where we&#8217;ll store the cell reference for the simulation object.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><a href=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2025\/04\/image-9.png\"><img decoding=\"async\" width=\"744\" height=\"702\" data-attachment-id=\"1943\" data-permalink=\"https:\/\/www.pyxll.com\/blog\/monte-carlo-simulations-in-excel-with-python\/image-33\/\" data-orig-file=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2025\/04\/image-9.png\" data-orig-size=\"744,702\" data-comments-opened=\"0\" data-image-meta=\"{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;,&quot;orientation&quot;:&quot;0&quot;}\" data-image-title=\"image\" data-image-description=\"\" data-image-caption=\"\" data-large-file=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2025\/04\/image-9.png\" src=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2025\/04\/image-9.png\" alt=\"\" class=\"wp-image-1943\" srcset=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2025\/04\/image-9.png 744w, https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2025\/04\/image-9-300x283.png 300w\" sizes=\"(max-width: 744px) 100vw, 744px\" \/><\/a><\/figure>\n\n\n\n<p>The only remaining thing to do now is to fetch that in our macro as follows:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: python; title: ; notranslate\" title=\"\">\n@xl_macro\ndef mc_run_simulation():\n    &quot;&quot;&quot;Run a Simulation located at the cell specified in the calling button&#039;s\n    alternative text field.\n    &quot;&quot;&quot;\n    # Get the cell reference from the calling button&#039;s Alternative Text\n    xl = xl_app()\n    caller = xl.Caller\n    button = xl.ActiveSheet.Shapes&#x5B;caller]\n    address = button.AlternativeText\n\n    # Get the simulation object from the cell address\n    cell = XLCell.from_range(address)\n    simulation = cell.options(type=&quot;object&quot;).value\n\n    # Run the simulation\n    results = simulation.run()\n\n    # And write the results to cell below\n    cell.offset(rows=1).options(type=&quot;object&quot;).value = results\n<\/pre><\/div>\n\n\n<h3 class=\"wp-block-heading\" id=\"h-further-improvements\"><span class=\"ez-toc-section\" id=\"Further_Improvements\"><\/span>Further Improvements<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>There are various things you could do to continue to improve on this, but hoepefully the above will get you off to a good start!<\/p>\n\n\n\n<p>You can find a version of this code, along with some more improvements, on GitHub here <a href=\"https:\/\/github.com\/pyxll\/pyxll-examples\/tree\/master\/montecarlo\" target=\"_blank\" rel=\"noreferrer noopener\">https:\/\/github.com\/pyxll\/pyxll-examples\/blob\/master\/montecarlo<\/a><\/p>\n\n\n\n<p>Some of the improvements in the code linked above include<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Progress indicator<\/li>\n\n\n\n<li>Better error reporting<\/li>\n\n\n\n<li>Resetting the inputs after the simulation<\/li>\n\n\n\n<li>Plotting the input distributions<\/li>\n<\/ul>\n\n\n\n<p>See the video <a href=\"https:\/\/youtu.be\/Va9ih1DXPDs\" target=\"_blank\" rel=\"noreferrer noopener\">https:\/\/youtu.be\/Va9ih1DXPDs<\/a> for a walkthrough of all of this code in detail.<\/p>\n\n\n\n<figure class=\"wp-block-embed is-type-video is-provider-youtube wp-block-embed-youtube wp-embed-aspect-16-9 wp-has-aspect-ratio\"><div class=\"wp-block-embed__wrapper\">\n<span class=\"embed-youtube\" style=\"text-align:center; display: block;\"><iframe loading=\"lazy\" class=\"youtube-player\" width=\"640\" height=\"360\" src=\"https:\/\/www.youtube.com\/embed\/Va9ih1DXPDs?version=3&#038;rel=1&#038;showsearch=0&#038;showinfo=1&#038;iv_load_policy=1&#038;fs=1&#038;hl=en-US&#038;autohide=2&#038;wmode=transparent\" allowfullscreen=\"true\" style=\"border:0;\" sandbox=\"allow-scripts allow-same-origin allow-popups allow-presentation allow-popups-to-escape-sandbox\"><\/iframe><\/span>\n<\/div><\/figure>\n\n\n\n<p><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Monte Carlo simulations are essential as an analytical tool in various fields, from finance to project management. Using Python and the PyXLL add-in we&#8217;ll explore how to bring the Monte Carlo method to Excel. This Monte Carlo statistical technique allows us to understand and quantify uncertainty by performing repeated random sampling to obtain numerical results.<\/p>\n","protected":false},"author":1,"featured_media":1951,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[12,2,504,272],"tags":[671,672,665],"class_list":["post-1912","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-python","category-pyxll","category-user-interface","category-visualization","tag-dataviz","tag-montecarlo","tag-plotting"],"acf":[],"jetpack_featured_media_url":"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2025\/04\/thumbnail.png","jetpack_shortlink":"https:\/\/wp.me\/p7l3LP-uQ","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.pyxll.com\/blog\/wp-json\/wp\/v2\/posts\/1912","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.pyxll.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.pyxll.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.pyxll.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.pyxll.com\/blog\/wp-json\/wp\/v2\/comments?post=1912"}],"version-history":[{"count":1,"href":"https:\/\/www.pyxll.com\/blog\/wp-json\/wp\/v2\/posts\/1912\/revisions"}],"predecessor-version":[{"id":2074,"href":"https:\/\/www.pyxll.com\/blog\/wp-json\/wp\/v2\/posts\/1912\/revisions\/2074"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.pyxll.com\/blog\/wp-json\/wp\/v2\/media\/1951"}],"wp:attachment":[{"href":"https:\/\/www.pyxll.com\/blog\/wp-json\/wp\/v2\/media?parent=1912"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.pyxll.com\/blog\/wp-json\/wp\/v2\/categories?post=1912"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.pyxll.com\/blog\/wp-json\/wp\/v2\/tags?post=1912"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}