def test_calculation(self):
Range('A1').value = 2
Range('B1').formula = '=A1 * 2'
app = Application(wkb=self.wb)
app.calculation = Calculation.xlCalculationManual
Range('A1').value = 4
assert_equal(Range('B1').value, 4)
app.calculation = Calculation.xlCalculationAutomatic
app.calculate() # This is needed on Mac Excel 2016 but not on Mac Excel 2011 (changed behaviour)
assert_equal(Range('B1').value, 8)
Range('A1').value = 2
assert_equal(Range('B1').value, 4)
def main():
wb = Workbook.caller()
# User Inputs
num_simulations = Range('E3').options(numbers=int).value
time = Range('E4').value
num_timesteps = Range('E5').options(numbers=int).value
dt = time/num_timesteps # Length of time period
vol = Range('E7').value
mu = np.log(1 + Range('E6').value) # Drift
starting_price = Range('E8').value
perc_selection = [5, 50, 95] # percentiles (hardcoded for now)
# Animation
animate = Range('E9').value.lower() == 'yes'
# Excel: clear output, write out initial values of percentiles/sample path and set chart source
# and x-axis values
Range('O2').table.clear_contents()
Range('P2').value = [starting_price, starting_price, starting_price, starting_price]
Chart('Chart 5').set_source_data(Range((1, 15),(num_timesteps + 2, 19)))
Range('O2').value = np.round(np.linspace(0, time, num_timesteps + 1).reshape(-1,1), 2)
# Preallocation
price = np.zeros((num_timesteps + 1, num_simulations))
percentiles = np.zeros((num_timesteps + 1, 3))
# Set initial values
price[0,:] = starting_price
percentiles[0,:] = starting_price
# Simulation at each time step
for t in range(1, num_timesteps + 1):
rand_nums = np.random.randn(num_simulations)
price[t,:] = price[t-1,:] * np.exp((mu - 0.5 * vol**2) * dt + vol * rand_nums * np.sqrt(dt))
percentiles[t, :] = np.percentile(price[t, :], perc_selection)
if animate:
Range((t+2, 16)).value = percentiles[t, :]
Range((t+2, 19)).value = price[t, 0] # Sample path
if sys.platform.startswith('win'):
Application(wb).screen_updating = True
if not animate:
Range('P2').value = percentiles
Range('S2').value = price[:, :1] # Sample path
def test_calculation(self):
Range('A1').value = 2
Range('B1').formula = '=A1 * 2'
app = Application(wkb=self.wb)
app.calculation = Calculation.xlCalculationManual
Range('A1').value = 4
assert_equal(Range('B1').value, 4)
app.calculation = Calculation.xlCalculationAutomatic
app.calculate() # This is needed on Mac Excel 2016 but not on Mac Excel 2011 (changed behaviour)
assert_equal(Range('B1').value, 8)
Range('A1').value = 2
assert_equal(Range('B1').value, 4)
def test_screen_updating(self):
Application(wkb=self.wb).screen_updating = False
assert_equal(Application(wkb=self.wb).screen_updating, False)
Application(wkb=self.wb).screen_updating = True
assert_equal(Application(wkb=self.wb).screen_updating, True)
def test_version(self):
app = Application(wkb=self.wb)
assert_true(int(app.version.split('.')[0]) > 0)
def class_teardown(wb):
wb.close()
if sys.platform.startswith('win'):
Application(wb).quit()
