book

Advanced Modelling in Finance Using Excel and VBA

Name: Advanced Modelling in Finance Using Excel and VBA
ISBN: 9780471499220

by Mary Jackson, Mike Staunton

June 2001

Intermediate to advanced

276 pages

8h 1m

English

Wiley

Read now

Unlock full access

Copyright
Preface
Acknowledgements
1. Introduction
1.1. FINANCE INSIGHTS1.2. ASSET PRICE ASSUMPTIONS1.3. MATHEMATICAL AND STATISTICAL PROBLEMS1.4. NUMERICAL METHODS1.5. EXCEL SOLUTIONS1.6. TOPICS COVERED1.7. RELATED EXCEL WORKBOOKS1.8. COMMENTS AND SUGGESTIONS
1. Advanced Modelling in Excel
2. Advanced Excel Functions and Procedures
2.1. ACCESSING FUNCTIONS IN EXCEL2.2. MATHEMATICAL FUNCTIONS2.3. STATISTICAL FUNCTIONS2.3.1. Using the Frequency Function2.3.2. Using the Quartile Function2.3.3. Using Excel's Normal Functions2.4. LOOKUP FUNCTIONS2.5. OTHER FUNCTIONS2.6. AUDITING TOOLS2.7. DATA TABLES2.7.1. Setting Up Data Tables with One Input2.7.2. Setting Up Data Tables with Two Inputs2.8. XY CHARTS2.9. ACCESS TO DATA ANALYSIS AND SOLVER2.10. USING RANGE NAMES2.11. REGRESSION2.12. GOAL SEEK2.13. MATRIX ALGEBRA AND RELATED FUNCTIONS2.13.1. Introduction to Matrices2.13.2. Transposing a Matrix2.13.3. Adding Matrices2.13.4. Multiplying Matrices2.13.5. Matrix Inversion2.13.6. Solving Systems of Simultaneous Linear Equations2.13.7. Summary of Excel's Matrix Functions2.13.8. SUMMARY
3. Introduction to VBA
3.1. ADVANTAGES OF MASTERING VBA3.2. OBJECT-ORIENTED ASPECTS OF VBA3.3. STARTING TO WRITE VBA MACROS3.3.1. Some Simple Examples of VBA Subroutines3.3.2. MsgBox for Interaction3.3.3. The Writing Environment3.3.4. Entering Code and Executing Macros3.3.5. Recording Keystrokes and Editing Code3.4. ELEMENTS OF PROGRAMMING3.4.1. Variables and Data Types3.4.2. VBA Array Variables3.4.3. Control Structures3.4.4. Control of Repeating Procedures3.4.5. Using Excel Functions and VBA Functions in Code3.4.6. General Points on Programming3.5. COMMUNICATING BETWEEN MACROS AND THE SPREADSHEET3.6. SUBROUTINE EXAMPLES3.6.1. Charts3.6.2. Normal Probability Plot3.6.3. Generating the Efficient Frontier with Solver3.6.4. SUMMARY3.6.5. REFERENCES3.6.6. APPENDIX 3A THE VISUAL BASIC EDITOR3.6.6.1. Stepping Through a Macro and Using Other Debug Tools3.6.7. APPENDIX 3B RECORDING KEYSTROKES IN 'RELATIVE REFERENCES' MODE
4. Writing VBA User-defined Functions
4.1. A SIMPLE SALES COMMISSION FUNCTION4.2. CREATING COMMISSION(SALES) IN THE SPREADSHEET4.3. TWO FUNCTIONS WITH MULTIPLE INPUTS FOR VALUING OPTIONS4.4. MANIPULATING ARRAYS IN VBA4.5. EXPECTED VALUE AND VARIANCE FUNCTIONS WITH ARRAY INPUTS4.6. PORTFOLIO VARIANCE FUNCTION WITH ARRAY INPUTS4.7. FUNCTIONS WITH ARRAY OUTPUT4.8. USING EXCEL AND VBA FUNCTIONS IN USER-DEFINED FUNCTIONS4.8.1. Using VBA Functions in User-Defined Functions4.8.2. Add-Ins4.9. PROS AND CONS OF DEVELOPING VBA FUNCTIONS4.9.1. SUMMARY4.9.2. APPENDIX 4A FUNCTIONS ILLUSTRATING ARRAY HANDLING4.9.3. APPENDIX 4B BINOMIAL TREE OPTION VALUATION FUNCTIONS4.9.4. EXERCISES ON WRITING FUNCTIONS4.9.5. SOLUTION NOTES FOR EXERCISES ON FUNCTIONS
2. Advanced Modelling in Equities
5. Introduction to Equities

6. Portfolio Optimisation
6.1. PORTFOLIO MEAN AND VARIANCE6.2. RISK–RETURN REPRESENTATION OF PORTFOLIOS6.3. USING SOLVER TO FIND EFFICIENT POINTS6.4. GENERATING THE EFFICIENT FRONTIER (HUANG AND LITZENBERGER'S APPROACH)6.5. CONSTRAINED FRONTIER PORTFOLIOS6.6. COMBINING RISK-FREE AND RISKY ASSETS6.7. PROBLEM ONE–COMBINING A RISK-FREE ASSET WITH A RISKY ASSET6.8. PROBLEM TWO–COMBINING TWO RISKY ASSETS6.9. PROBLEM THREE—COMBINING A RISK-FREE ASSET WITH A RISKY PORTFOLIO6.10. USER-DEFINED FUNCTIONS IN Module16.11. FUNCTIONS FOR THE THREE GENERIC PORTFOLIO PROBLEMS IN Module16.12. MACROS IN ModuleM6.12.1. SUMMARY6.12.2. REFERENCES
7. Asset Pricing
7.1. THE SINGLE-INDEX MODEL7.2. ESTIMATING BETA COEFFICIENTS7.3. THE CAPITAL ASSET PRICING MODEL7.4. VARIANCE–COVARIANCE MATRICES7.5. VALUE-AT-RISK7.6. HORIZON WEALTH7.7. MOMENTS OF RELATED DISTRIBUTIONS SUCH AS NORMAL AND LOGNORMAL7.8. USER-DEFINED FUNCTIONS IN Module17.8.1. SUMMARY7.8.2. REFERENCES
8. Performance Measurement and Attribution
8.1. CONVENTIONAL PERFORMANCE MEASUREMENT8.2. ACTIVE–PASSIVE MANAGEMENT8.3. INTRODUCTION TO STYLE ANALYSIS8.4. SIMPLE STYLE ANALYSIS8.5. ROLLING-PERIOD STYLE ANALYSIS8.6. CONFIDENCE INTERVALS FOR STYLE WEIGHTS8.7. USER-DEFINED FUNCTIONS IN Module18.8. MACROS IN ModuleM8.8.1. SUMMARY8.8.2. REFERENCES
3. Options on Equities
9. Introduction to Options on Equities
9.1. THE GENESIS OF THE BLACK–SCHOLES FORMULA9.2. THE BLACK–SCHOLES FORMULA9.3. HEDGE PORTFOLIOS9.4. RISK-NEUTRAL VALUATION9.5. A SIMPLE ONE-STEP BINOMIAL TREE WITH RISK-NEUTRAL VALUATION9.6. PUT–CALL PARITY9.7. DIVIDENDS9.8. AMERICAN FEATURES9.9. NUMERICAL METHODS9.10. VOLATILITY AND NON-NORMAL SHARE RETURNS9.10.1. SUMMARY9.10.2. REFERENCES
10. Binomial Trees
10.1. INTRODUCTION TO BINOMIAL TREES10.2. A SIMPLIFIED BINOMIAL TREE10.3. THE JR BINOMIAL TREE10.4. THE CRR TREE10.5. BINOMIAL APPROXIMATIONS AND BLACK–SCHOLES FORMULA10.6. CONVERGENCE OF CRR BINOMIAL TREES10.7. THE LR TREE10.8. COMPARISON OF CRR AND LR TREES10.9. AMERICAN OPTIONS AND THE CRR AMERICAN TREE10.10. USER-DEFINED FUNCTIONS IN Module0 AND Module110.10.1. SUMMARY10.10.2. REFERENCES
11. The Black-Scholes Formula
11.1. THE BLACK–SCHOLES FORMULA11.2. BLACK–SCHOLES FORMULA IN THE SPREADSHEET11.3. OPTIONS ON CURRENCIES AND COMMODITIES11.4. CALCULATING THE OPTION'S 'GREEK' PARAMETERS11.5. HEDGE PORTFOLIOS11.6. FORMAL DERIVATION OF THE BLACK–SCHOLES FORMULA11.7. USER-DEFINED FUNCTIONS IN MODULE111.7.1. SUMMARY11.7.2. REFERENCES
12. Other Numerical Methods for European Options
12.1. INTRODUCTION TO MONTE CARLO SIMULATION12.2. SIMULATION WITH ANTITHETIC VARIABLES12.3. SIMULATION WITH QUASI-RANDOM SAMPLING12.4. COMPARING SIMULATION METHODS12.5. CALCULATING GREEKS IN MONTE CARLO SIMULATION12.6. NUMERICAL INTEGRATION12.7. USER-DEFINED FUNCTIONS IN Module112.7.1. SUMMARY12.7.2. REFERENCES
13. Non-normal Distributions and Implied Volatility
13.1. BLACK–SCHOLES USING ALTERNATIVE DISTRIBUTIONAL ASSUMPTIONS13.2. IMPLIED VOLATILITY13.3. ADAPTING FOR SKEWNESS AND KURTOSIS13.4. THE VOLATILITY SMILE13.5. USER-DEFINED FUNCTIONS IN Module113.5.1. SUMMARY13.5.2. REFERENCES
4. Options on Bonds
14. Introduction to Valuing Options on Bonds
14.1. THE TERM STRUCTURE OF INTEREST RATES14.2. CASH FLOWS FOR COUPON BONDS AND YIELD TO MATURITY14.3. BINOMIAL TREES14.4. BLACK'S BOND OPTION VALUATION FORMULA14.5. DURATION AND CONVEXITY14.6. NOTATION14.6.1. SUMMARY14.6.2. REFERENCES
15. Interest Rate Models
15.1. VASICEK'S TERM STRUCTURE MODEL15.2. VALUING EUROPEAN OPTIONS ON ZERO-COUPON BONDS, VASICEK'S MODEL15.3. VALUING EUROPEAN OPTIONS ON COUPON BONDS, VASICEK'S MODEL15.4. CIR TERM STRUCTURE MODEL15.5. VALUING EUROPEAN OPTIONS ON ZERO-COUPON BONDS, CIR MODEL15.6. VALUING EUROPEAN OPTIONS ON COUPON BONDS, CIR MODEL15.7. USER-DEFINED FUNCTIONS IN Module115.7.1. SUMMARY15.7.2. REFERENCES
16. Matching the Term Structure
16.1. TREES WITH LOGNORMALLY DISTRIBUTED INTEREST RATES16.2. TREES WITH NORMAL INTEREST RATES16.3. THE BDT TREE16.4. VALUING BOND OPTIONS USING BDT TREES16.5. USER-DEFINED FUNCTIONS IN Module116.5.1. SUMMARY16.5.2. REFERENCES
A. Other VBA Functions
A.1.A.1.1. FORECASTINGA.1.2. ARIMA MODELLINGA.1.3. SPLINESA.1.4. EIGENVALUES AND EIGENVECTORSA.1.5. REFERENCES

Overview

This new and unique book demonstrates that Excel and VBA can play an important role in the explanation and implementation of numerical methods across finance. Advanced Modelling in Finance provides a comprehensive look at equities, options on equities and options on bonds from the early 1950s to the late 1990s.

The book adopts a step-by-step approach to understanding the more sophisticated aspects of Excel macros and VBA programming, showing how these programming techniques can be used to model and manipulate financial data, as applied to equities, bonds and options. The book is essential for financial practitioners who need to develop their financial modelling skill sets as there is an increase in the need to analyse and develop ever more complex 'what if' scenarios.

Specifically applies Excel and VBA to the financial markets
Packaged with a CD containing the software from the examples throughout the book

Note: CD-ROM/DVD and other supplementary materials are not included as part of eBook file.

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Hedge Fund Modelling and Analysis Using Excel and VBA

Publisher Resources

ISBN: 9780471499220Purchase book

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills