<jupyter_start><jupyter_text>Running Monte Carlo Simulations on an Excel ModelHere I will demonstrate using `xlwings` to drive Excel to run Monte Carlo Simulations. For this exercise, please download the Excel retirement model "Excel with Salary.xlsx" from Canvas. Then open the workbook and keep it open. Make sure you have no other workbooks open. Switch to the "Wealth" tab if it is not open.<jupyter_code>import xlwings as xw<jupyter_output><empty_output><jupyter_text>The Monte Carlo SetupFirst let's get a connection to our Excel sheet.<jupyter_code>import xlwings as xw

book = xw.Book('Dynamic Salary Retirement Model.xlsx')
sht = book.sheets['Inputs and Outputs']<jupyter_output><empty_output><jupyter_text>Running a Single SimulationWe want to evaluate how interest rate affects years until retirement. Let's first just try changing the interest rate and getting the years to retirement as the output.<jupyter_code>sht.range('B10').value = 0.08<jupyter_output><empty_output><jupyter_text>Now we can see that the interest rate has changed to 8% in Excel and that the years to retirement has changed to 24. But we want to get that output out of Excel as well.<jupyter_code>years_to_retirement = sht.range('B18').value
years_to_retirement<jupyter_output><empty_output><jupyter_text>Now that we have the value in Python we can analyze it in Python. Or if we want to analyze the results in Excel, we can output it back to the Excel workbook as a hard-coded value in a different cell, so that it will still be saved when the inputs change.<jupyter_code>sht.range('E2').value = years_to_retirement<jupyter_output><empty_output><jupyter_text>Now we can see the value is in Excel in the cell `E2`.  Running Multiple Simulations Just as we have done with pure Python Monte Carlo simulations, now we want to run this process many times. We'll use a loop over the number of iterations to do this. We will collect the results in Python and then output to Excel at the end. First we need to be getting the interest rate randomly from a normal distribution:<jupyter_code>import random

interest_mean = 0.05
interest_std = 0.03

interest_rate = random.normalvariate(interest_mean, interest_std)
interest_rate
num_iter = 10

all_retirement_years = []
for i in range(num_iter):
    interest_rate = random.normalvariate(interest_mean, interest_std)
    sht.range('B10').value = interest_rate
    years_to_retirement = sht.range('B18').value
    all_retirement_years.append(years_to_retirement)
all_retirement_years<jupyter_output><empty_output><jupyter_text>Now output back to Excel. We want them in a column so we will do the list comprehension trick.<jupyter_code>vertical_retirement_years = [[ret_year] for ret_year in all_retirement_years]
sht.range('E2').value = vertical_retirement_years<jupyter_output><empty_output><jupyter_text>Now wrap this all up in a function.<jupyter_code>def retirement_simulations(num_iter, interest_mean, interest_std):
    all_retirement_years = []
    for i in range(num_iter):
        interest_rate = random.normalvariate(interest_mean, interest_std)
        sht.range('B10').value = interest_rate
        years_to_retirement = sht.range('B18').value
        all_retirement_years.append(years_to_retirement)
    
    vertical_retirement_years = [[ret_year] for ret_year in all_retirement_years]
    sht.range('E2').value = vertical_retirement_years
    
    return all_retirement_years  # return it so we will also have it in Python in addition to Excel

results = retirement_simulations(1000, 0.1, 0.05)
results[:10]<jupyter_output><empty_output>
StarcoderdataJupyterStructuredCleanDedup