An Introduction to Excel VBA Programming
with Applications in Finance and Insurance

Excel Visual Basic for Applications (VBA) can be used to automate operations in Excel and is one of the most frequently used software programs for manipulating data and building models in banks and insurance companies. An Introduction to Excel VBA Programming: with Applications in Finance and Insurance introduces readers to the basic fundamentals of VBA Programming while demonstrating applications of VBA to solve real-world problems in finance and insurance. Assuming no prior programming experience and with reproducible examples using code and data, this text is suitable for advanced undergraduate students, graduate students, actuaries, and financial analysts who wish to learn VBA.

Features:

• Presents the theory behind the algorithms in detail
• Includes more than 100 exercises with selected solutions
• Provides VBA code in Excel files and data to reproduce the results in the book
• Offers a solutions manual for qualified instructors

I VBA Preliminaries

Introduction to VBA

Getting Started

Modules

VBA Classes

The ExcelMacro Recorder

Excel Objects

The Excel ObjectModel

The Application Object

TheWorkbook Objects

TheWorksheet Objects

TheWorksheetFunction Object

Variables, Data Types, and Scopes

Variable Declaration and Data Types

Arrays

Constants

Strings

Dates

Scopes

Operators and Control Structures

Operators

Flow Control

Loops

Functions, Events and File IO

User-Defined Functions

Events

File IO

Error Handling and Debugging 109

Error Handling

Debugging VBA Code

Best Practices of VBA Coding

II Applications

Generating Payment Schedules

Introduction

Public Holidays in the United States

The Julian and Gregorian Calendars

Day Count Conventions

Business Day Conventions

Implementation

TheMDateModule

TheMHolidayModule

TheMScheduleModule

TheMInterfaceModule

Bootstrapping Yield Curves

Introduction

Interpolation

Bootstrapping Yield Curves

Finding Roots of an Equation

Implementation

TheMCurveModule

TheMInterfaceModule

Generating Risk-Neutral Scenarios

Introduction

The Black-ScholesModel

Generating Random Normal Numbers

Implementation

TheMGeneratorModule

TheMInterfaceModule

Valuing a GMDB

Introduction

Life Table Construction

GMDB Valuation

Greek Calculation

Implementation

TheMLifeTableModule

TheMGMDBModule

TheMInterfaceModule

Connecting to Databases

ActiveX Data Objects

SQL

Implementation

TheMDatabaseModule

TheMInterfaceModule

Object-Oriented Programming 233

Introduction

Objects

Implementation

The CZeroCurveModule

The CSwapModule

The CBootstrapperModule

An OO Bootstrapping Tool

Solutions to Selected Exercises

Guojun Gan is an Assistant Professor in the Department of Mathematics at the University of Connecticut, where he has been since August 2014. Prior to that, he worked at Manulife Financial, a large life insurance company in Toronto, Canada, for six years. His research focuses on data mining and actuarial science. He has published four books and several papers related to data mining, actuarial science, and mathematical finance.