Raising the bar

Creating frequency table and histogram and using Excel macros.

Most of Exercise 2.3 you have to do by hand. I use this opportunity to show how to use Excel macros to automate repetitive tasks.

Simulation steps

1. Simulate the coin 10 times
2. The sum of the simulated values gives the binomial variable with 10 repetitions.
3. We need to record 20 observations on the binomial. For this, we put references to the cell, containing the binomial, to 20 cells.
4. Next we want to convert references to numbers. Doing this by hand would be tedious, so I show how to use macros to repeat the same operation 20 times.

There are two ways to create macros.

One is by telling Excel to record your actions on the screen; then it remains to execute the created macro the necessary number of times. Anybody can do this.

Another, more complex and versatile, way is by programming in Visual Basic. This second way is so powerful that financial analysts use it for creating automated trading strategies. I learnt everything I know about Visual Basic from Office Help. Unfortunately, Microsoft decided to move the most part of Office Help, including all examples, to the Office site. One of their arguments is that chm files used for Office Help are not secure. However, you can still find and download them to use offline. For example, the help file for Excel is "Excel 2013 Developer Documentation.chm"

Creating frequency table and histogram - click to view video