1st Edition

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

By Guojun Gan Copyright 2017
    300 Pages
    by Chapman & Hall

    300 Pages
    by Chapman & Hall

    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

    Biography

    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.