Excel VBA for Finance

• Excel VBA Programming
• Financial Modeling and Applications, UE 111 is covered in this course.
• The course covers:
  • Introduction
  • Excel object model
  • The structure of Visual Basic projects
  • Visual Basic language
  • Miscellaneous

Course Objectives

• Learn how to use Excel + VBA to solve problems in finance, covering:
  • General principles of VBA language (object-oriented)
  • Excel object model
  • Fundamentals of VBA language (variables, loops, tests)
  • Standard problems in finance (diversification, optimal portfolios, option pricing)

Teaching Method

• Course: Consists of 21 hours, broken into 7 sessions lasting 3 hours each
• Tutorial sessions (TD): 15 hours in total, split into 10 sessions of 1.5 hours each

Grading

• Tutorial sessions account for 15% of the grade
• The Midterm exam (MCQ + short answers) accounts for 35% of the grade
• The Final exam accounts for 50% of the grade

Course Outline

• Part I: Introduction to Visual Basic for Applications (VBA) in Excel
  • Chapter 1: Excel object model
  • Chapter 2: Structure of Visual Basic projects
  • Chapter 3: Visual Basic language
  • Chapter 4: Miscellaneous
• Part II: Financial applications
  • Chapter 1: Properties of stock returns
  • Chapter 2: Diversification / efficient frontier
  • Chapter 3: Option pricing

Excel for Financial Modeling

• Excel is useful for financial modeling as it has:
  • Statistical functions like probability distributions, regression tools, and data analysis tools
  • Mathematical functions like matrix operations and numerical optimization procedures

Excel VBA

• From version 5.0 onwards, Excel features a user-friendly IDE and VBA as a tool
• The IDE includes a code editor and debug functions
• VBA, a programming language (loops, conditional statements, worksheet management tools)
• Programming can extend Excel's capabilities by automating repetitive tasks and building additional functions

Chapter 1: Excel object model

• VBA is an object-oriented language
• An object is any element of the Excel application you can interact with, like:
  • workbook
  • worksheet
  • range
  • cell
  • menu

VBA Objective

• Manipulating objects through computer code rather than manual interaction
• Cons: Less intuitive, requires knowledge of objects and VBA language
• Pros: Automation, increased computation capacities

Section 1 Excel objects: Properties and Methods

• Excel object behavior is described by:
  • Properties
  • Methods

Properties

• Properties refer to an object's characteristics
• Cell A1 has the properties:
  • Background color
  • Height and width
  • Font
  • Style and size of characters
  • Coordinates (row and column number)

Modifying Properties

• To write the number 150 in Cell A1:
  • Use the object Range("A1")
  • Access the Value Property to set content
  • Range("A1").Value = 150
• In general, to modify a property: <Object>.<Property> = <value>

Retrieving Values

• To retrieve the content of Cell A1 and store it in a variable:
  • Assign the cell's Value property to the variable
  • Content_A1 = Range("A1").Value
• In general, <Variable> = <Object>.<Property>

Object Methods

• Methods are actions attached to an object
• Actions translate to method names that are verbs
• Methods attached to Cell A1 include:
  • Select
  • Cut
  • Paste
  • Drag and drop

Modifying methods

• To cut the content of Cell A1 and put it into cell A2 (drag and drop):
  • Cut the content using method Cut
  • Assign the destination of the Cut to Cell A2
  • Range("A1").Cut Destination:= Range("A2")
• In general, <Object>.<Method>

Section 2 Organisation of Excel Objects

• Rules organize the huge number of Excel objects, including:
  • Storing objects within collections
  • Hierarchical structure

Objects and Collections

• A collection is a set of objects that shares characteristics
• Collection names contain an 's' (plural), such as Workbooks, Worksheets, or Cells
• When a property value changes, or a method is applied to the collection level, it applies to each object within the collection

Excel Object Hierarchy

• The hierarchy is structured as follows:
  • Application: the Excel application
  • Application is parent of the collection Workbooks
  • Each workbook is parent of both the Worksheets and the Charts collections
  • Each worksheet is parent of:
  • The Names collection (named fields)
  • Several Range objects (ranges of cells)

Accessing Excel Objects

• The hierarchy determines how access is achieved (top to bottom)
• The dot symbol (.) separates objects and collections
• To change the background color of cell B2 in worksheet Sheet3 of workbook UE111 to red, enter:
  • Workbooks("UE111.xlsx").Worksheets("Sheet3").Range("B2").Interior.Color = vbRed
• To shorten the previous line:
  • Work with a single workbook (UE111)
    • Worksheets("Sheet3").Range("B2").Interior.Color = vbRed
  • Make the Workbook UE111 and Worksheet Sheet3 active:
    • To activate Workbooks("UE111.xlsx").Worksheets("Sheet3").Activate:
    • Range("B2").Interior.Color = vbRed
  • Use the index number of worksheets and workbooks:
    • Workbooks(1).Worksheets(3).Range...
  • In case several operations must be performed within a given workbook/worksheet:
    • With Workbooks("UE111.xlsx").Worksheets("Sheet3")
      • .Range("B2").Interior.Color = vbred
    • End With

Section 3 Range objects

• Range objects are defined based on the worksheet collection properties
• These objects allow defining individual cells, ranges of cells and groups of cells

Properties

• Range: Single cell, range of cells, group of (non contiguous) cells, group of ranges, etc.
• Cells: Cells collection of a Worksheet object (set of all cells in the worksheet) or single cell
• Rows: Rows collection of a Worksheet object (set of all rows), single row or range of (contiguous) rows
• Columns: Same as Rows but for columns
• ActiveCell: Active cell in a worksheet
• Selection: Last selected range object
• Offset: Offset (shifted) object wrt initial range object
• Resize: Resized range object wrt initial range object
• EntireRow: Returns a Range object that represents the entire row (or rows) containing the initial range object
• EntireColumn: Returns a Range object that represents the entire column (or columns) containing the initial range object
• End(<xlDirection>) : Range obtained, starting from an initial range object, by pressing the End key followed by ↓, ↑, → or ←:
  • xlDown
  • xlUp
  • xlToRight
  • xlToLeft

Selecting ranges and cells

• Select a range object.
• Activate a cell
• Select the selected cell to equal an active cell as a single cell
• The cell in the upper left corner of the range is activated within the range of cells

Selection by direct referencing

• Using the Range property
  • Range("A1").Select
  • Range("A1:C3").Select
  • Range("A1:C2,D4:E5,F6").Select
  • Range("A:C,E:E").Select
  • Range("1:3,5:5").Select
  • Range("A1:C3,D4,E:G,I:I,7:9,11:11").Select
• Using the Cells property
  • Cells.Select
  • Cells(16).Select
    • 16 384 columns
    • 1 048 576 rows
  • Cells(2,3).Select
  • Range(Cells(1,1),Cells(3,3)).Select
  • Range (Cells(i,j),Cells(k,l)).Select
• Using the Rows and Columns properties
  • Rows.Select
  • Rows(1).Select
• Rows(i).Select
• Rows("1:3").Select
• Columns.Select
• Columns("A").Select
• Columns(1).Select
• Columns(i).Select
• Columns("A:C").Select

Indirect Referencing Examples

• Range("A1").Offset(2,3).Select
• Range("A1:C3").Offset(2,3).Select
• Range("A1").Activate
  • ActiveCell.Offset(1,1).Select
• Range("A1").Activate
  • ActiveCell.Offset(1,1).Activate
  • ActiveCell.Offset(1,1).Select
• Range("D2:G7").Resize(2,3).Select
• Range("A1:A4").EntireRow.Select
• Range("A1").End(xlDown).Select

Invoking a range object from a range object

• Creates a range object using one of the properties of the Worksheets collection = global Range
• A range object can be created from a parent range object:
  • Amounts to restricting the worksheet to the parent range object
  • References are relative to the upper left corner of the first range object (change in coordinates)
• Examples
• Range("B2:F6").Cells.Select
• Range("B2:F6").Range("A1").Select
• Range("B2:F6").Rows(2).Select

Properties

• Row: returns the row index (1 for row 1, 2 for row 2) of the cell to which Row is applied
  • Ex: Range("A1").Row
• Column: returns the column index (1 for A, 2 for B) of the cell to which Column is applied
• Count: returns the number of elements in a collection
  • Ex: Range("A1:C3").Cells.Count
• Name: assigns a name to a range
  • Ex: Range("A1:C3").Name = "MyRange"
  • Range("MyRange").Select

Within ranges and cells

• Storing values:
  • General syntax: <Range object>.Value = <Expression>
  • If the range object contains more than one cell, the expression will be stored in each of the cells
  • Ex: Range("B2:F6").Value = 2024
• Storing formulas:
  • Formula Property
  • FormulaR1C1 property

The Formula Property

• Exhibits the same behavior as formulas written in interactive mode
• Relative reference to a row/column is derived by writing the row number/column letter(s) of the corresponding row/column
• Absolute reference to a row/column is derived by inputting a $ symbol before the row number/column letter(s)

The FormulaR1C1 Property

• R1C1 is the notation
• R to create a reference to a Row
• C to create a reference to a column

The formula dependent on the referencing

• R[i] (C[i]) where i is a non-zero relative integer
• i>0: Generates a relative reference to the row (column) located i rows below (i columns to the right) with respect to the current row (column)
• i<0: Generates a relative reference to the row (column) located i rows above (i columns to the left) with respect to the current row (column)

Chapter 2: The structure of Visual Basic projects

• Applications developed in VBA are structured in a project
• A project is made of:
  • Worksheets
  • Modules
  • UserForms
• The various elements contained in a project interact with one another to achieve the objective of the VBA programmer

Section 1: modules

• Two types of modules:
  • Class modules
  • Code modules, those with VBA code

Two types of modules:

• Class modules: definition of new objects (for advanced users)
• Code modules: which contains the VBA commands

Module features:

• Methods: Created by the user
• Single property = Name (name of the module)
  • By default, Modulei (i = order of creation of the module)
  • The name can be modified in the Properties panel of the IDE Declarations: Top of a module (see Listbox in upper right corner)

Section 2: UserForms

• Objects corresponding to user-defined interactive interfaces

Section 3: Visual Basic procedures

• Procedure: A set of instructions aimed at performing a given task
• Sub procedures
• Function procedures or functions
• Property procedures (within class modules -> for advanced users)

Sub procedures

• Perform a task but returns no result
• General Rule for Sub Procedures
  • Max of 255 characters allowed for procedure name
  • Some characters and names are forbidden (reserved names)
• Syntax:
  • Sub <procedure name> ([arg1], [arg2],...)
  • Instructions
  • End Sub
• Behavior:
  • A Sub executes the instructions one after another
  • The execution stops at the End Sub line
  • A main procedure can call secondary procedures
  • Calling a secondary procedure: Call [<module name>].<procedure name>([arg1], [arg2], ...)
  • Private:Private scope procedures are recognized only within the module to which they belong
  • Public (default option): Public procedures are recognized by all procedures within the project
• Static
  • Determines whether variables remain in existence as long as Visual Basic is running
  • Static: Variables are not reset
  • The alternative defaults to resetting variables

Function procedures or functions

• MUST return a result
• Syntax:
  • [Private|Public] [Static] Function <name> ([arg1],[arg2],...)
  • Instructions
  • <name> = <Expression>
  • End Function It is important to note that:
    • A Sub procedure can call another Sub procedure
  • A Sub procedure can call a Function
    • A Function procedure can call another Function procedure
  • A Function procedure can call itself = Recursion
    • A Function procedure cannot call a Sub procedure

Chapter 3: Visual Basic language

• Covers variables and control statements

Section 1: Variables in VB

• Boolean type: boolean or logical variables
  • 2 possible values including:
    • True
    • False
  • Default value: False

Numerical variables

Byte type: integer value within the interval [0;255] • Integer type: integer value within the interval [-32,768 ; 32,767] • Long type: integer value within the interval [- 2,147,483,648; 2,147,483,647] • Single type: 45 digit precision • Double type: 324 digit precision

String type

• String of characters • Up to 2 billions of characters • Default value: "" (empty string) - & is the concatenation operator

Object variables

• Variables aimed at establishing a reference to an object • As many types as the number of object types (Range, Workbook, Worksheet) • The assignment of an object to a variable involves using the Set instruction:

• Set <variable> = <object> • The default value of an object variable: - Nothing It is recommended to release the memory allocated to an object variable once This variable is no longer useful using Set <variable> = Nothing

Variant type

• Accepts all other types • The default type assigned to a variable if this variable is not declared • Most memory- and time-consuming variables • The use of Variant type should be restricted to variables whose type can change over time (in case of conversion) or to array variables whose cells are used to Store information of different nature (e.g. numbers and characters) • Default value: Empty

Dim Instruction / Variables Declaration

• Declaring means assigning a type to a variable

• Syntax: Dim <variable> as <type>
  • Ex: Dim MyRange as Range

Declaration of array variables

• An array is a structure (vector, matrix) of cells
• Cells all contain information of the same data type
• Each element can be accessed through coordinates

Numbering of elements in every dimension:

• 0 by default or if Option Base 0 has been put in the declaration section of the module • 1 if Option Base 1 has been put in the declaration section of the module.

Data type

• Procedure Scope
  • The variable is accessible from only within the procedure in which it is declared Private Module Scope
  • The variable is accessible from only within the module in which it is declared • Declaration: Private <name> as <Type> in declaration section of the module

Dynamic variables

• ReDim (dim1,dim2) ' to declare dynamic variables

Notes for Dynamic Variables

• Variables whose dimension (number of elements in any of the dimensions of the variable) can change over time • Syntax: ReDim <name> (<Nb'1>, A good practice is to first declare a dynamic variable • ReDim is mostly useful to declare a variable of which the number of elements is given by the content of another variable Ex: ReDim MyArray<Variable>

Section 2: Control statements

• Instructions to modify program behaviour
• Allow to redirect the execution of a program towards a given group of instructions
• Dependency on a variable's values or a condition being either True or False

Control structures

• The general layout for the IF..THEN..ELSE structure

Operators

• Operators consist of:
  • Arithmetic
• Comparison
• Logical
• For Next Loop · For Each Next Loop