Business Writing, Excel Templates

How to Create Monte Carlo Simulations for Excel using DeepSeek

Posted on by Anthony James
28
Jan

Even though I’m not that technical, I’ve been keen to see if it was possible to use Large Language Models (LLMs) for practical, everyday tasks. One area that piqued my interest was Excel formula generation.

I wondered if an LLM, such as Claude, could actually understand the nuances of spreadsheet calculations and translate natural language descriptions into functional Excel formulas. Could it handle the complexity and precision required for tasks like financial modeling or statistical analysis? 

I wanted to see if I could describe a calculation in plain English – the way I think about it – and have the LLM spit out the correct Excel syntax. This seemed especially promising for tackling intricate formulas, the kind that can take hours to build and debug manually. Imagine just telling the computer what you want to calculate and having it do the heavy lifting! The potential time savings and reduction in errors seemed enormous.

To get started, I had the idea of using an LLM to help build a Monte Carlo simulation in Excel. Monte Carlo simulations can be notoriously tricky to set up. They often involve a lot of complex formulas for random number generation, probability distributions, and statistical analysis. I thought this would be the perfect test case for an LLM. Could it handle the complexities of a Monte Carlo simulation and generate all the necessary Excel formulas?

So, I experimented with Google Gemini, Claude, and DeepSeek to see which of them could create the charts for me. Let me show you the results, then share a few things I learnt along the way.  

Creating Macros in Excel for Monte Carlo Simulation

As mentioned, creating macros in Microsoft Excel can significantly automate repetitive tasks and complex calculations, such as running a Monte Carlo simulation. Below, I’ll guide you through the process of creating a macro for a Monte Carlo simulation in Excel.

What is a Monte Carlo Simulation?

A Monte Carlo simulation is a computational technique that uses random sampling to estimate the outcomes of complex systems or processes. It’s often used in finance, engineering, and other fields to model uncertainty and risk.

Steps to Create a Monte Carlo Simulation Macro in Excel

Follow these steps:

1. Enable the Developer Tab

  • In Excl, go to File > Options > Customize Ribbon.
  • Under the Main Tabs, check the Developer option and click OK.

2. Set Up Your Spreadsheet

  • Inputs: Define the inputs for your simulation (e.g., mean, standard deviation, number of iterations).
  • Outputs: Define where the results will be displayed (e.g., a range of cells for the simulated values).

Example:

  • Cell A1: “Mean”
  • Cell B1: 100 (mean value)
  • Cell A2: “Standard Deviation”
  • Cell B2: 15 (standard deviation)
  • Cell A3: “Number of Iterations”
  • Cell B3: 1000 (number of simulations)

3. Open the VBA Editor

  • Go to the Developer tab and click on Visual Basic (or press Alt + F11).

4. Insert a New Module

  • In the VBA editor, go to Insert > Module to create a new module.

5. Write the Macro Code

In DeepSeek, I asked it to create code that I could insert into Excel. Here is the code it suuplied.

Here is the prompt I used: 

As a spreadsheet expert, show me how to create macros that I can use in MS Excel. For instance, create a macro that will show me a Monte Carlo simulation.

Copy and paste the following VBA code into the module:


Sub MonteCarloSimulation()
Dim mean As Double
Dim stdDev As Double
Dim iterations As Long
Dim i As Long
Dim result As Double

' Read inputs from the spreadsheet
mean = Range("B1").Value
stdDev = Range("B2").Value
iterations = Range("B3").Value

' Clear previous results
Range("C1:C" & iterations).ClearContents

' Run the Monte Carlo simulation
For i = 1 To iterations
result = Application.WorksheetFunction.NormInv(Rnd(), mean, stdDev)
Range("C" & i).Value = result
Next i

MsgBox "Monte Carlo simulation completed!", vbInformation
End Sub

Explanation of the Code:

  • mean, stdDev, and iterations are variables that store the input values from the spreadsheet.
  • The For loop runs the simulation for the specified number of iterations.
  • Application.WorksheetFunction.NormInv(Rnd(), mean, stdDev) generates a random value from a normal distribution with the given mean and standard deviation.
  • The results are written to column C.

6. Run the Macro

  • Close the VBA editor and return to Excel.
  • Go to the Developer tab and click on Macros.
  • Select MonteCarloSimulation and click Run.

7. View the Results

  • The simulated values will appear in column C of your spreadsheet.

8. Optional: Add a Button to Run the Macro

  • Go to the Developer tab and click on Insert > Button (Form Control).
  • Draw a button on the sheet, and when prompted, assign the MonteCarloSimulation macro to it.
  • Now, you can run the simulation by clicking the button.

Example Spreadsheet Layout

A B C
Mean 100  
Standard Deviation 15  
Number of Iterations 1000  
    Simulated Value 1
    Simulated Value 2
    …
    Simulated Value 1000

Steps to Represent the Data as a Chart

The next stage was to get it to display the chart. You can do this manually but I wanted it to do so using the script. 

Here is the prompt I entered into DeepSeek: 

The code you gave me for Steps to Create a Monte Carlo Simulation Macro in Excel works. How do I represent the data it shows as a chart

1. Ensure the Data is Ready

  • After running the macro, the simulated values should be in column C (or whichever column you specified in the macro).
  • Ensure there are no empty cells in the range of simulated values.

2. Select the Data

  • Highlight the range of simulated values in column C (e.g., C1:C1000).

3. Insert a Chart

  • Go to the Insert tab on the Excel ribbon.
  • Choose the type of chart you want to create. For Monte Carlo simulations, a histogram or scatter plot is commonly used:
    • Histogram: Shows the distribution of the simulated values.
    • Scatter Plot: Shows the individual data points.

To Insert a Histogram:

  1. Go to Insert > Charts > Histogram.
  2. Excel will automatically create a histogram based on the selected data.

To Insert a Scatter Plot:

  1. Go to Insert > Charts > Scatter (or X Y Scatter).
  2. Choose the scatter plot style you prefer.

4. Customize the Chart

  • Once the chart is created, you can customize it:
    • Add a title: Click on the chart title and edit it (e.g., “Monte Carlo Simulation Results”).
    • Adjust the axis labels: Right-click on the axes and select Format Axis to customize the scale, labels, etc.
    • Add gridlines or data labels if needed.

5. Automate Chart Creation with VBA (Optional)

If you want to automate the chart creation process using VBA, you can add the following code to your macro. This will create a histogram chart automatically after the simulation runs:


Sub MonteCarloSimulationWithChart()
Dim mean As Double
Dim stdDev As Double
Dim iterations As Long
Dim i As Long
Dim result As Double
Dim probability As Double
Dim chartObj As ChartObject

' Read inputs from the spreadsheet
mean = Range("B1").Value
stdDev = Range("B2").Value
iterations = Range("B3").Value

' Validate inputs
If iterations <= 0 Or iterations > 1048576 Then
MsgBox "Invalid number of iterations. Please enter a value between 1 and 1,048,576.", vbExclamation
Exit Sub
End If

' Clear previous results
Range("C1:C" & iterations).ClearContents

' Run the Monte Carlo simulation
For i = 1 To iterations
' Ensure probability is within valid range for NormInv
Do
probability = Rnd()
Loop While probability <= 0 Or probability >= 1

' Generate random value from normal distribution
result = Application.WorksheetFunction.NormInv(probability, mean, stdDev)
Range("C" & i).Value = result
Next i

' Create a chart
Set chartObj = ActiveSheet.ChartObjects.Add(Left:=100, Width:=375, Top:=50, Height:=225)
With chartObj.Chart
.SetSourceData Source:=Range("C1:C" & iterations)
.ChartType = xlColumnClustered ' Use xlXYScatter for a scatter plot
.HasTitle = True
.ChartTitle.Text = "Monte Carlo Simulation Results"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Text = "Simulated Values"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Text = "Frequency"
End With

MsgBox "Monte Carlo simulation and chart creation completed!", vbInformation
End Sub

Explanation of the Chart Code:

  • Set chartObj = ActiveSheet.ChartObjects.Add(...): Adds a new chart object to the active sheet.
  • .SetSourceData Source:=Range("C1:C" & iterations): Sets the data source for the chart.
  • .ChartType = xlColumnClustered: Specifies the chart type (use xlXYScatter for a scatter plot).
  • .ChartTitle.Text: Adds a title to the chart.
  • .Axes(...).AxisTitle.Text: Adds titles to the axes.

6. Run the Updated Macro

  • Replace your existing macro with the updated code above.
  • Run the macro, and it will generate both the simulated data and a chart automatically.

Example Output

  • Histogram: Shows the frequency distribution of the simulated values.
  • Scatter Plot: Shows individual data points, which can help visualize trends or patterns.

Tips for Better Visualization

  • Use a histogram to analyze the distribution of the simulated values.
  • Use a scatter plot if you want to see the raw data points.
  • Add descriptive titles and axis labels to make the chart easier to understand.

Lessons Learned

I tried this Excel experiment with different LLMs. I got a frustrated with CHATPGT. It simply told me how to create the Monte Carlo chart in Excel but didn’t give me an actual script. I finally got a script from it but it didn’t work even after a lot of troubleshooting. It seemed to get in a loop, so I moved to Claude. 

Claude was better in that it did provide a script but it failed to render correctly. It would show 10 rows only of the 1000 it was supposed to show. 

Finally, I tried DeepSeek and it worked first time round. Impressive. 

I now have a macro that automates the process of running a Monte Carlo simulation in Excel. I can customize the inputs, such as the mean, standard deviation, and number of iterations, to suit my specific needs. This is just a basic example. In the coming weeks, I’ll continue to experiment with different LLMs to see how they can help automate spreadsheet tasks. 

If you have any questions about how I did this, you can contact me on LinkedIn.

 

Anthony James