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
Table of Contents:
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
About the Author :
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.
