PART I : SPREADSHEETS
KEY FUNCTIONS FOR MODEL BUILDING
- Financial, Logic, Date and Lookup functions
- Finding appropriate functions and avoiding potential pitfalls
Case studies:
- Calculation of NPV of irregular cashflows from zero curve.
- Lookup and interpolation of zero coupon rates
INCREASING EFFICIENCY
- Using keyboard shortcuts to speed up standard tasks
- Tricks for efficient navigation of spreadsheets
- Changing Excel's default options
SENSITIVITY ANALYSIS
- Data Tables
- Multi- dimensional scenario analysis
- Using XY graphs efficiently
Case studies:
- Using data tables to create a spot-vol matrix for an option portfolio
- Creating complex scenario analysis to stress test portfolio
DYNAMIC DATA
- Using Offset, Indirect to create dynamic references
- Advanced use of names
- Arrays
Case studies:
- Calculate historical vol, using a parameterized window length
STATISTICS
- Regression, Volatility, Correlation and Percentile functions
- Data Analysis Toolpak
OPTIMIZATION
- Goal Seek
- Solver
Case studies:
- Creating zero coupon curves from portfolio of swaps
PART II: VBA
FUNCTIONS
- Writing a Function( ) macro
- Difference between VBA and Worksheet functions
- Finding your way around VBA Help
Case studies:
- Create a Black ‘76 option pricing function
VARIABLES AND CONSTANTS
- Variable types, Dim statement, Scope of variables
- Establishing good practice
- Constants
- Arrays, Dynamic Arrays
CONTROL STRUCTURES
- Branching: If...then and Select Case
- Looping: For…Next, Do While etc…
- Breaking out of loops, jumping to another part of the code, terminating the program
Case studies:
- Use looping to calculate implied vols
OBJECTS, METHODS & PROPERTIES
- Understanding the Excel Object Model: Properties, Methods, Collections
- Range Object, Cells, Offset, Resize etc…
- Workbooks and Worksheet Objects
- Using the Macro record facility
Case studies:
- Automating repetitive tasks taking advantage of the object methods
SUB-ROUTINES
- Writing Sub-routines
- Getting data in and out of the program
- Using InputBox, MessageBox, StatusBar
- Random numbers
- Using Solver in VBA
Case studies:
- Build a Monte Carlo model to price options
- Use Solver in a loop to create an Efficient Frontier
DEBUGGING
- Breakpoints, Watch and Local windows
- Debug, Immediate