Advanced Financial Modeling
Macros

© EduPristine – www.edupristine.com/ca

Agenda
 Introduction to Macros & Advanced Application Building in Excel • Introduction and context  Key Concepts in Macros  Macros as recorded Robots!  Designing application to gather user data • Implementing user forms in Excel Macros • Linking forms to excel cells • Building flexible Charts in Excel  Understanding VBA • Understanding the relevant language constructs • Example: Write your First Macro  Debugging VBA code • Errors in VBA • Step into VBA  Writing more VBA Codes • Random Number Generator: Use of Loop, Excel Functions • Message Box, Input Box  Advanced Financial Applications • Breaking circular loops • Monte Carlo Simulation
© EduPristine Macros 2

Agenda
 Introduction to Macros & Advanced Application Building in Excel • Introduction and context  Key Concepts in Macros  Macros as recorded Robots!  Designing application to gather user data • Implementing user forms in Excel Macros • Linking forms to excel cells • Building flexible Charts in Excel  Understanding VBA • Understanding the relevant language constructs • Example: Write your First Macro  Debugging VBA code • Errors in VBA • Step into VBA  Writing more VBA Codes • Random Number Generator: Use of Loop, Excel Functions • Message Box, Input Box  Advanced Financial Applications • Breaking circular loops • Monte Carlo Simulation
© EduPristine Macros 3

Building user applications in excel requires knowledge of advanced functions
 Excel is one of the most widely used tools in financial industry
• • • • • • Easy to use High reach & access to software across geographies Flexibility Robustness Inbuilt features (Most people would not even be using 95% of the features) & Extendibility Modular and Object Oriented Architecture

 Applications, typically requiring back end calculations can be built using simple Excel
• Poor user interaction • Not that flexible • Cannot run for “multiple instances”

 Applications building in Excel require understanding of advanced features and functions
• • • • To collect user inputs in a nice fashion, Graphical User Interface (GUI) can be built To perform any repeated task excel provides Macro Recording To generate complex functions, you can use excel functions To perform complex calculations and operations, Excel VBA (Macros) can be used

© EduPristine

Macros

4

Useful, if we are trying to build a complex user application …

Great User Interface

Advanced Calculations

© EduPristine

Macros

5

Applications with good user control require advanced features

Controlling User Inputs

 Excel sheets and applications requiring inputs from users need to have a good user interface  Excel provides form controls and active x controls to help create a GUI

© EduPristine

Macros

6

Showing results to users again require creation of dashboards and charts. Building flexibility requires advanced excel features
Flexibility in showing results

Dashboards

 Macros can also be used to build flexibility in the output format as well

© EduPristine

Macros

7

Automation of repetitive tasks in applications

Using a Robot Training a Robot

 Most applications would require the task to be done for multiple iterations
• For example formatting charts, etc. • For simple routine tasks, Macros can be recorded and used

© EduPristine

Macros

8

… Automation of repetitive tasks in applications

Programmatically performing routine tasks in applications

 Most applications would require the task to be done for multiple iterations
• VBE can be used to programmatically perform routine tasks repetitively for complex calculations

© EduPristine

Macros

9

Agenda
 Introduction to Macros & Advanced Application Building in Excel • Introduction and context  Key Concepts in Macros  Macros as recorded Robots!  Designing application to gather user data • Implementing user forms in Excel Macros • Linking forms to excel cells • Building flexible Charts in Excel  Understanding VBA • Understanding the relevant language constructs • Example: Write your First Macro  Debugging VBA code • Errors in VBA • Step into VBA  Writing more VBA Codes • Random Number Generator: Use of Loop, Excel Functions • Message Box, Input Box  Advanced Financial Applications • Breaking circular loops • Monte Carlo Simulation
© EduPristine Macros 10

What are Macros?

Macros are like Robots!!

Create Graphical User Interface (GUI)

Automate Routine Tasks by Recording

Automate Routine Tasks by Programming

 A macro is a set of computer instructions
• A mini-computer program • Activated by an event • That tells the computer what to do

© EduPristine

Macros

11

Steps in Using Macros

Train the Robot

Use the Robot

Retrain

Reuse

The Robot is as good as you have trained it!

 Typically the first level of Macro can be created by Recording the macro  A little bit of understanding of VB can help improve the usability multi-fold

© EduPristine

Macros

12

The VB Editor
 Right click on any existing ribbon tab and choose customize the ribbon. Check the developer tab box and click OK.
Right click on existing tab and choose customize the ribbon

1

Check the developer tab box.

2

4

3
© EduPristine Macros

Click OK.

Now developer tab is in the menu bar.
13

The VB Editor
 Click on the developer tab on the ribbon and then Go to Visual Basic.

You can select the Visual Basic option under the developer tab or by using shortcut Alt+F11.

© EduPristine

Macros

14

The VB Editor
 The Visual Basic window is open.

© EduPristine

Macros

15

Switching From the VB Editor to Excel
 Click on the first button on the VB editor toolbar. It takes back to the excel. Switching between VB Editor and Excel is also done by using keyboard shortcuts Alt+F11.

Click on the button for switching back to Excel from VB Editor toolbar.

© EduPristine

Macros

16

Introducing the VBA Environment
Code Window

Run and Control Commands Project Explorer

Use Alt + F11 to get the window

© EduPristine

Macros

17

A Tour of the VB Editor Screen
 For Project Explorer window, go to View>Project Explorer or use shortcut Ctrl+R.  For Properties Window, go to View>Properties window or use shortcut F4.

1

2

Go to Project explorer under the View tab for open the Project Explorer Window or use Ctrl+R shortcut.
© EduPristine Macros

Go to Properties window under the View tab for open the Project Properties Window or use F4 shortcut.
18

Moving the VB Editor Windows
 The Side panel is moving to any other place by simply drag it from original place and drop it down to new place.

Move the panel from left side to right side

© EduPristine

Macros

19

Creating Modules
 There are different ways to create a module. One is right click on any where inside the Project window>Go to Insert>Module.  The Module is inserted under the Module Folder.

Right click on Modules

1 4
Go to Insert

2 3
Select Module A new Module is added under Modules Folder

© EduPristine

Macros

20

Creating Modules
 The Second way is go to insert in the menu bar and click on the Module.

1
The Another way is Go to Insert Tab

2 4

Select Module

A new Module is added under Modules Folder

© EduPristine

Macros

21

Removing Modules
 For deleting the Module, Right click on Module and click on Remove Module. Click on ‘NO’. The module is deleted.

1
Right click on Module3

3

2

Click “NO”

Select “Remove Module3”

4
Module3 is deleted

© EduPristine

Macros

22

Renaming Modules
 To rename the modules, first select the modules then go to Properties Window and rename it.

2
Select the Module which you want to rename. Go to Properties window, and rename it

Name of the module is changed

1
 The name of modules follow some rules that you can’t use spaces between the name of the modules. You can use the underscore character in place of space.
Space between the name is not allowed

2
Error showing that module name is not legal

1
© EduPristine Macros

23

Setting Up the VB Editor Fonts
 For editing the setting of VB Editor Fonts, Go to Tools Tab in menu bar and then click on Options.
For editing the setting, Go to Tools menu Go to Editor Format tab. Now you can change the setting of VB Editor.

2 1

2

Select Options

© EduPristine

Macros

24

Setting the Right Macro Security Levels

Macro security can be changed by “Macro Security” option under the developer menu tab or “Options” under the File Menu.

 Macros are dangerous snippets of code and can be built with malicious intent  Macros need to be enabled for the application to run properly
© EduPristine Macros 25

Changing Macro Security Levels

Go to Trust Center

Select Trust Center Settings

1 1 2 2

© EduPristine

Macros

26

Changing Macro Security Levels

1

Select the Macro Settings

2

After Changing the setting, Click OK

© EduPristine

Macros

27

Saving Workbook Containing Code

1
File Name

2

Save your file as Excel Macro-Enabled Workbook format
© EduPristine Macros 28

Agenda
 Introduction to Macros & Advanced Application Building in Excel • Introduction and context  Key Concepts in Macros  Macros as recorded Robots!  Designing application to gather user data • Implementing user forms in Excel Macros • Linking forms to excel cells • Building flexible Charts in Excel  Understanding VBA • Understanding the relevant language constructs • Example: Write your First Macro  Debugging VBA code • Errors in VBA • Step into VBA  Writing more VBA Codes • Random Number Generator: Use of Loop, Excel Functions • Message Box, Input Box  Advanced Financial Applications • Breaking circular loops • Monte Carlo Simulation
© EduPristine Macros 29

Training the Robot – Recording a Macro

 Name the macro  Shortcut key - fill in a letter in the available space
• Allows you to run the macro by holding down the CTRL key and pressing the chosen letter on the keyboard

 Description
• Description of the macro Remember to Stop the recording!

© EduPristine

Macros

30

Using the Robot – Using Macros

 Use the Run Macro or shortcut key assigned to run the macro
Use Par_01_CopyMe-MacroRecording

© EduPristine

Macros

31

Your first Recorded Macro
1 6

2

5

4 3

© EduPristine

Macros

32

So what’s inside the Macro?

Use Alt + F11 to see what Excel writes inside

© EduPristine

Macros

33

If we had used relative references?
1
Relative Referencing works like cell referencing without “$”

5 2

4 3

© EduPristine

Macros

34

So what’s in the code now?

Internally uses the offset function

© EduPristine

Macros

35

Simple Example - Automatically format charts in Excel

 Unformatted “default” chart in Excel
• Format it once and record for further use

Use Par_02_Chart-Recording-Macro

© EduPristine

Macros

36

Create a New Macro – Format Chart

 Create a new macro
• Record the steps performed to update the chart as per the standards required

© EduPristine

Macros

37

Record all steps in the macro
1 2

3

 Record all steps performed to change the formatting and stop the recording

© EduPristine

Macros

38

Select the new chart to be formatted and run

 Select the new chart that has to be formatted
• Run the macro

© EduPristine

Macros

39

The macro would automatically make the formatting consistent

 With a single keystroke you can format your charts
• For example B, CTRL + q is the key

© EduPristine

Macros

40

Automatically generated Macros are not very robust
 Even small changes in the expected step would cause the macro to throw errors
• Need to understand the code • Slight modifications in the code can make the macros much more robust

© EduPristine

Macros

41

Agenda
 Introduction to Macros & Advanced Application Building in Excel • Introduction and context  Key Concepts in Macros  Macros as recorded Robots!  Designing application to gather user data • Implementing user forms in Excel Macros • Linking forms to excel cells • Building flexible Charts in Excel  Understanding VBA • Understanding the relevant language constructs • Example: Write your First Macro  Debugging VBA code • Errors in VBA • Step into VBA  Writing more VBA Codes • Random Number Generator: Use of Loop, Excel Functions • Message Box, Input Box  Advanced Financial Applications • Breaking circular loops • Monte Carlo Simulation
© EduPristine Macros 42

Form Controls and Active X controls
ActiveX Controls Control Toolbox In the code module for the Sheet Corresponds to the control name (e.g., CommandButton1_Click) UserForm controls Form Controls Forms In any standard VBA module

Toolbar Macro code storage

Macro name

Any name you specify.

Correspond to... Customization Respond to events Inserting Assigning Macros

Dialog Sheet controls

Extensive, using the Properties Minimal box Yes Design Mode No Arbitrary Macro Can be assigned Click or Change events only Normal Macros can be assigned

© EduPristine

Macros

43

Creating a Model for CAPM
1 2
Using Form Controls

To Select: Right Click! Limitation: A +ve #

3 5 4
To Link to a cell

To Covert to %age

6
Use Par_03_CAPM-GUI
© EduPristine Macros 44

Playing with the changes

A Linear Transformation to generate desired output

Custom Format
Different Controls can be used for different user inputs

Link to the cell

© EduPristine

Macros

45

Formatting the Scroll Bars

Select all scroll bars by click and ctrl key

© EduPristine

Macros

46

Linking the Model

Based on the User Inputs, Generate the return Expectation

© EduPristine

Macros

47

Creating Flexible Charts

Create the Chart Based on the Data

Use Par_04_FlexiChart

© EduPristine

Macros

48

Create Decision controls and link to cells

1

4

3 2

© EduPristine

Macros

49

Linking Data to decision

If the appropriate series is selected, show the ratio, else hide

Change linkage to new block

© EduPristine

Macros

50

Agenda
 Introduction to Macros & Advanced Application Building in Excel • Introduction and context  Key Concepts in Macros  Macros as recorded Robots!  Designing application to gather user data • Implementing user forms in Excel Macros • Linking forms to excel cells • Building flexible Charts in Excel  Understanding VBA • Understanding the relevant language constructs • Example: Write your First Macro  Debugging VBA code • Errors in VBA • Step into VBA  Writing more VBA Codes • Random Number Generator: Use of Loop, Excel Functions • Message Box, Input Box  Advanced Financial Applications • Breaking circular loops • Monte Carlo Simulation
© EduPristine Macros 51

Basics of VBA – Introduction to Sub and Function
 VBA procedure
• Set of instructions given to the “Robot” to execute • Perform tasks such as calculating equations, analyzing data, communicating with excel/ databases, etc. • Block structured as a Sub or Function

 Constructed from statements
• Declaration (Robot’s memory) • Assignment statements (What is put in that memory) • Execution (What actions to perform)

 Procedures are typed and stored in a Module/ worksheet  Functions/ Sub are run line by line from beginning to end  VBA has two types of procedures
• Sub (A subroutine, which performs a small task for you!) • Function procedures (It performs some tasks and returns a value to you)

© EduPristine

Macros

52

Basics of VBA – Introduction to Variables
 Variables are like buckets
• Hold content for you • The contents of the variables can change • Actually variables are areas allocated by the computer memory to hold data • Each variable has a
 Name  Type

 In VBA to Declare a new variable (Dim is the keyword)
• Dim myPassword As String • Dim firstNum As Integer • Dim aLargeNumber As Double

 Variables can be used to store data
• myPassword = “whatever” • firstNum = 10 • etc

© EduPristine

Macros

53

Example: Write your First Macro
Write a macro which will add a new worksheet, then it will populate the content as shown in below figure:

Use Par_05_MyFirstMacro
© EduPristine Macros 54

Creating a new Module

Right click on Modules

1

Go to Insert

2 3

4

A new Module is added under Modules Folder Select Module

© EduPristine

Macros

55

Laying Out Your Code Neatly

Neat Code

© EduPristine

Macros

56

Code for the example
Beginning a new subroutine Basic syntax of VBA instructions Comments Assigning a value to a cell Text as input Using Functions to calculate values Selecting a cell

Changing Font Color

Changing Background color, to select a color: write rgb and press Ctrl + Spacebar, it will give you many options to choose

© EduPristine

Macros

57

Running Code from the VB Editor

Run the Code

© EduPristine

Macros

58

Running Code from the VB Editor

Data in Excel Sheet after running the code

© EduPristine

Macros

59

Running Code from the VB Editor
Go to Macros under the developer tab Run the Macro

1

2

© EduPristine

Macros

60

Running Code from the VB Editor

After running the code, a new sheet is created containing the data.

© EduPristine

Macros

61

Agenda
 Introduction to Macros & Advanced Application Building in Excel • Introduction and context  Key Concepts in Macros  Macros as recorded Robots!  Designing application to gather user data • Implementing user forms in Excel Macros • Linking forms to excel cells • Building flexible Charts in Excel  Understanding VBA • Understanding the relevant language constructs • Example: Write your First Macro  Debugging VBA code • Errors in VBA • Step into VBA  Writing more VBA Codes • Random Number Generator: Use of Loop, Excel Functions • Message Box, Input Box  Advanced Financial Applications • Breaking circular loops • Monte Carlo Simulation
© EduPristine Macros 62

Syntax Errors

It show the place where error occur

The dialog box show that what type of error occur

1

2 3

Red color show that there is some error in this line

If there is any mistake in the syntax , then a dialog box is pop up showing that there is error in code
© EduPristine Macros 63

Syntax Errors
The error is removed by using the “)”

© EduPristine

Macros

64

Turning Off Syntax Error Messages
 Whenever we have any syntax error, a dialog box is Pop Up to show that there is error in line.

© EduPristine

Macros

65

Turning Off Syntax Error Messages
To turn off the syntax error message, Go to Tool in menu bar Uncheck The Auto syntax Check.

1

3

2

Select Options

4

Click OK

© EduPristine

Macros

66

Turning Off Syntax Error Messages
 Now if you have any syntax error in line, then dialog box will not Pop Up. Only the Color of the line will change to red to show that there is error in line.

© EduPristine

Macros

67

Compile Errors
3

Run the Code

But it doesn’t show error

1

2

In this line word “Range” is not spell correctly

© EduPristine

Macros

68

Compile Errors

Error Message showing that there is error in code

2 1
It show that this word have error

3

The dialog box show that the function is not defined

© EduPristine

Macros

69

Compile Errors
 When we click on OK and fix the code, you will notice that first line of subroutine is highlighted in yellow indicated that the subroutine is in break mode.

© EduPristine

Macros

70

Compile Errors
 The best policy to run the subroutine again is to reset it.
Go to Run

1 2
Select Reset

© EduPristine

Macros

71

Compile Errors
 Lets assume that we spell “Date” and “rgbPaleTurquoise” incorrectly. Then What happened?

Compile the code

Spell Incorrectly

© EduPristine

Macros

72

Compile Errors
 It doesn’t show any error message at the time of compile. And also run the code without showing error.

© EduPristine

Macros

73

Compile Errors

Date is not shown

The Background color change to black

1

2

Output of the Code

© EduPristine

Macros

74

Option Explicit

Option explicit is designed for working with variables

© EduPristine

Macros

75

Compile Errors

Compile it again

© EduPristine

Macros

76

Compile Errors

Show the error in “Date”

Show the error in “color”
© EduPristine Macros 77

Compile Errors
For adding Option Explicit automatically, select Tools Check the “Require Variable Declaration” box

1 3 2

Select Options

4
Click Ok

© EduPristine

Macros

78

Compile Errors
 Now whenever you create a new module, the Option Explicit is added automatically as first line of your subroutine.

© EduPristine

Macros

79

Runtime Errors

2
Compile the code. It doesn’t show error message.

Change the Range from B1 to ZZZ1

1

© EduPristine

Macros

80

Runtime Errors

But at runtime, error message is shown

Click on Debug

The highlighted show that this line contain error

© EduPristine

Macros

81

Runtime Errors

The cell B1, B2, B3 is empty. Because the code could not run due to error

1 2
Output show that code is running just before the error

© EduPristine

Macros

82

Stepping Through a Subroutine

1

Go to Debug Select Step Into

2
This will execute the code step by step

© EduPristine

Macros

83

Stepping Through a Subroutine

Now press F8 to execute the code step by step This highlighting show that this line is not executed yet

© EduPristine

Macros

84

Setting Breakpoints

Breakpoint

We can set it at any line by pressing F9.

© EduPristine

Macros

85

Setting Breakpoints

The code is executed till that point

The rest of the lines will executed by pressing F8 key

© EduPristine

Macros

86

Agenda
 Introduction to Macros & Advanced Application Building in Excel • Introduction and context  Key Concepts in Macros  Macros as recorded Robots!  Designing application to gather user data • Implementing user forms in Excel Macros • Linking forms to excel cells • Building flexible Charts in Excel  Understanding VBA • Understanding the relevant language constructs • Example: Write your First Macro  Debugging VBA code • Errors in VBA • Step into VBA  Writing more VBA Codes • Random Number Generator: Use of Loop, Excel Functions • Message Box, Input Box  Advanced Financial Applications • Breaking circular loops • Monte Carlo Simulation
© EduPristine Macros 87

Random Number Generator – Looping functions in VBA
 For repetitive work, VBA has looping functions
• Much more powerful than direct formulas • For-Next loop

Use Par_06_Function-Randbetween

© EduPristine

Macros

88

Calling Excel Functions in VBA
 Syntax
• Application.worksheetfunction.Whatever function

© EduPristine

Macros

89

Message Box

© EduPristine

Macros

90

Message Box – Result
1 2 3

6

5

4

7

© EduPristine

Macros

91

Message Box – Storing the value of the Message Box

© EduPristine

Macros

92

Message Box – Storing the value of the Message Box – Result

If Yes

If No

© EduPristine

Macros

93

Input Box

© EduPristine

Macros

94

Agenda
 Introduction to Macros & Advanced Application Building in Excel • Introduction and context  Key Concepts in Macros  Macros as recorded Robots!  Designing application to gather user data • Implementing user forms in Excel Macros • Linking forms to excel cells • Building flexible Charts in Excel  Understanding VBA • Understanding the relevant language constructs • Example: Write your First Macro  Debugging VBA code • Errors in VBA • Step into VBA  Writing more VBA Codes • Random Number Generator: Use of Loop, Excel Functions • Message Box, Input Box  Advanced Financial Applications • Breaking circular loops • Monte Carlo Simulation
© EduPristine Macros 95

IDC: Breaking Circular Loops

Because of circular dependencies, the sheet can enter into inconsistent state

Use Par_08_IDC

© EduPristine

Macros

96

IDC: Basic Algorithm

The Copy & Paste Special has to be repeated again and again. This can be achieved by Recording

© EduPristine

Macros

97

IDC: Recording the Macro

Has to be done “Intelligently” to be used later

© EduPristine

Macros

98

IDC: Macro by Recording

The Macro is able to copy and paste special the values once

© EduPristine

Macros

99

IDC: Editing (with for loop) for the desired results

For Loop to run it multiple times

© EduPristine

Macros

100

IDC: Writing the code instead of Recording with Name Ranges

Name Ranges

© EduPristine

Macros

101

Monte Carlo Simulation (MCS)
 There is a fair amount of uncertainty and risk involved with estimating the future value of figures or amounts due to the wide variety of potential outcomes.
• Monte Carlo simulation (MCS) is one technique that helps to reduce the uncertainty involved in estimating future outcomes.

 MCS can be applied to complex, non-linear models or used to evaluate the accuracy and performance of other models.
• • • • • • risk management, portfolio management, pricing derivatives, strategic planning, project planning, cost modeling and other fields.

Technique that converts uncertainties in input variables of a model into probability distributions Combining the distributions and randomly selecting values from them, it recalculates the simulated model many times and brings out the probability of the output.

© EduPristine

Macros

102

MCS: Frequently Used Distributions for MC
 Normal/Gaussian Distribution
• where the mean and the standard deviation are given and the mean represents the most probable value of the variable. It is symmetrical around the mean and is not bounded

 Lognormal Distribution
• Appropriate for a variable ranging from zero to infinity, with positive skewness and with normally distributed natural logarithm

 Triangular Distribution
• With fixed minimum and maximum values. It is bounded by the minimum and maximum values and can be either symmetrical (the most probable value = mean = median) or asymmetrical.

 Uniform Distribution
• Contrast to the triangular distribution, the likelihood of occurrence of the values between the minimum and maximum is the same.

 Exponential Distribution
• Used to illustrate the time between independent occurrences, provided the rate of occurrences is known.

© EduPristine

Macros

103

MCS: Probabilistic Growth in Price and Volume
 Assumptions
• Initial Price and Volume of Material Sold • Mean Growth and the Std Deviations

 Generating the Probability Distribution of Price and Volume Growth
• Rand() Generates Uniform Random • Normsinv() to generate in normal form

 Use the probabilistic price and volume to project the revenues
Use Par_09_MCS

© EduPristine

Macros

104

MCS: Implementing Monte Carlo Simulation
 The process of projecting revenues is to be repeated “n” number of times. This can be achieved with the help of a Macro.

1.

Variable Declaration

2.

Emptying the Vars & Cells

3.

For Loop to run the simulation ‘n’ times

4.

Calculating Average

© EduPristine

Macros

105

MCS: Interpreting the Output

The Progress Bar Implemented with “Rept()” formula

© EduPristine

Macros

106

Thank you!

© EduPristine – www.edupristine.com/ca
© EduPristine Macros