This advanced textbook for business statistics teaches, statistical analyses and research methods utilizing business case studies and financial data with the applications of Excel VBA, Python and R. Each chapter engages the reader with sample data drawn from individual stocks, stock indices, options, and futures. Now in its second edition, it has been expanded into two volumes, each of which is devoted to specific parts of the business analytics curriculum. To reflect the current age of Data Science and Machine Learning, the used applications have been updated from Minitab and SAS to Python and R, so that readers will be better prepared for the current industry.
This second volume is designed for advanced courses in financial derivatives, risk management, and Machine Learning and financial management. In this volume we extensively use Excel, Python, and R to analyze the above-mentioned topics. It is also a comprehensive reference for active statistical finance scholars and business analysts who are looking to upgrade their toolkits. Readers can look to the first volume for dedicated content on financial statistics, and portfolio analysis.
Part I of this volume discusses advanced applications of Microsoft Excel Programs. Chapter 2 introduces Excel programming, Chap. 3 introduces VBA programming, and Chap. 4 discusses professional techniques used in Excel and Excel VBA techniques.
There are six chapters in Part II. Chapter 5 discusses the decision tree approach for the binomial option pricing model, Chap. 6 discusses the Microsoft Excel approach to estimating alternative option pricing models, Chap. 7 discusses how to use Excel to estimate implied variance, Chap. 8 discusses Greek letters and portfolio insurance, Chap. 9 discusses portfolio analysis and option strategies, and Chap. 10 discusses simulation and its application.
There are six chapters in Part III, which describe applications of Python, Machine Learning for financial analysis, and risk management. These six chapters are Linear Models for Regression (Chap. 11), Kernel Linear Model (Chap. 12), Neural Networks and Deep Learning (Chap. 13), Applications of Alternative Machine Learning Methods for Credit Card Default Forecasting (Chap. 14), An Application of Deep Neural Networks for Predicting Credit Card Delinquencies (Chap. 15), and Binomial/Trinomial Tree Option Pricing Using Python (Chap. 16). Part IV shows how Excel can be used to perform financial management. Chapter 17 shows ow Excel can be used to perform financial ratio analysis, Chap. 18 shows how Excel can be used to perform time value money analysis, Chap. 19 shows how Excel can be used to perform capital budgeting under certainty and uncertainty, and Chap. 20 shows how Excel can be used for financial planning and forecasting. Finally, Part V discusses applications of R programs for financial analysis and derivatives. Chapter 21 discusses the theory and application of hedge ratios. In this chapter, we show how the R program can be used for hedge ratios in terms of three econometric methods. Chapter 22 discusses applications of a simultaneous equation in finance research in terms of the R program. Finally, Chap. 23 discusses how to use the R program to estimate the binomial option pricing model and the Black and Scholes option pricing model.
In this volume, Chap. 14 was contributed by Huei-Wen Teng and Michael Lee. Chapter 15 was contributed by Ting Sun, and Chap. 22 was contributed by Fu-Lai Lin.
There are two possible applications of this volume:
A. to supplement financial derivative and risk management courses.
B. to teach students how to use Excel VBA, Python, and R to analyze financial derivatives and perform risk management.
Author(s): John Lee, Jow-Ran Chang, Lie-Jane Kao
Edition: 2
Publisher: Springer
Year: 2023
Language: english
Pages: 521
Preface
Contents
1 Introduction
1.1 Introduction
1.2 Brief Description of Chap. 1 of Volume 1
1.3 Structure of This Volume
1.3.1 Excel VBA
1.3.2 Financial Derivatives
1.3.3 Applications of Python, Machine Learning for Financial Derivatives, and Risk Management
1.3.4 Financial Management
1.3.5 Applications of R Programs for Financial Analysis and Derivatives
1.4 Summary
Excel VBA
2 Introduction to Excel Programming and Excel 365 Only Features
2.1 Introduction
2.2 Excel’s Macro Recorder
2.3 Excel’s Visual Basic Editor
2.4 Running an Excel Macro
2.5 Adding Macro Code to a Workbook
2.6 Macro Button
2.7 Sub Procedures
2.8 Message Box and Programming Help
2.9 Excel 365 Only Features
2.9.1 Dynamic Arrays
2.9.1.1 Year to Date Performance of S&P 500 Components
2.9.1.2 SORT Function
2.9.1.3 FILTER Function
2.9.2 Rich Data Types
2.9.2.1 Stocks Data Type
2.9.2.1.1 Stock
2.9.2.1.2 Instrument Types
2.9.3 STOCKHISTORY Function
2.10 Summary
References
3 Introduction to VBA Programming
3.1 Introduction
3.2 Excel’s Object Model
3.3 Intellisense Menu
3.4 Object Browser
3.5 Variables
3.6 Option Explicit
3.7 Object Variables
3.8 Functions
3.9 Adding a Function Description
3.10 Specifying a Function Category
3.11 Conditional Programming with the IF Statement
3.12 For Loop
3.13 While Loop
3.14 Arrays
3.15 Option Base 1
3.16 Collections
3.17 Summary
References
4 Professional Techniques Used in Excel and VBA
4.1 Introduction
4.2 Finding the Range of a Table: CurrentRegion Property
4.3 Offset Property of the Range Object
4.4 Resize Property of the Range Object
4.5 UsedRange Property of the Range Object
4.6 Go to Special Dialog Box of Excel
4.7 Importing Column Data into Arrays
4.8 Importing Row Data into an Array
4.9 Transferring Data from an Array to a Range
4.10 Workbook Names
4.11 Dynamic Range Names
4.12 Global Versus Local Workbook Names
4.13 List of All Files in a Directory
4.14 Summary
References
Financial Derivatives
5 Binomial Option Pricing Model Decision Tree Approach
5.1 Introduction
5.2 Call and Put Options
5.3 Option Pricing—One Period
5.4 Put Option Pricing—One Period
5.5 Option Pricing―Two Period
5.6 Option Pricing—Four Period
5.7 Using Microsoft Excel to Create the Binomial Option Call Trees
5.8 American Options
5.9 Alternative Tree Methods
5.9.1 Cox, Ross, and Rubinstein
5.9.2 Trinomial Tree
5.9.3 Compare the Option Price Efficiency
5.10 Retrieving Option Prices from Yahoo Finance
5.11 Summary
Appendix 5.1: EXCEL CODE—Binomial Option Pricing Model
References
6 Microsoft Excel Approach to Estimating Alternative Option Pricing Models
6.1 Introduction
6.2 Option Pricing Model for Individual Stock
6.3 Option Pricing Model for Stock Indices
6.4 Option Pricing Model for Currencies
6.5 Futures Options
6.6 Using Bivariate Normal Distribution Approach to Calculate American Call Options
6.7 Black’s Approximation Method for American Option with One Dividend Payment
6.8 American Call Option When Dividend Yield is Known
6.8.1 Theory and Method
6.8.2 VBA Program for Calculating American Option When Dividend Yield is Known
6.9 Summary
Appendix 6.1: Bivariate Normal Distribution
Appendix 6.2: Excel Program to Calculate the American Call Option When Dividend Payments are Known
References
7 Alternative Methods to Estimate Implied Variance
7.1 Introduction
7.2 Excel Program to Estimate Implied Variance with Black–Scholes Option Pricing Model
7.2.1 Black, Scholes, and Merton Model
7.2.2 Approximating Linear Function for Implied Volatility
7.2.3 Nonlinear Method for Implied Volatility
7.2.3.1 Newton–Raphson Method
7.2.3.2 Bisection Method
7.2.3.3 Compare Newton–Raphson Method and Bisection Method
7.3 Volatility Smile
7.4 Excel Program to Estimate Implied Variance with CEV Model
7.5 WEBSERVICE Function
7.6 Retrieving a Stock Price for a Specific Date
7.7 Calculated Holiday List
7.8 Calculating Historical Volatility
7.9 Summary
Appendix 7.1: Application of CEV Model to Forecasting Implied Volatilities for Options on Index Futures
References
8 Greek Letters and Portfolio Insurance
8.1 Introduction
8.2 Delta
8.2.1 Formula of Delta for Different Kinds of Stock Options
8.2.2 Excel Function of Delta for European Call Options
8.2.3 Application of Delta
8.3 Theta
8.3.1 Formula of Theta for Different Kinds of Stock Options
8.3.2 Excel Function of Theta of the European Call Option
8.3.3 Application of Theta
8.4 Gamma
8.4.1 Formula of Gamma for Different Kinds of Stock Options
8.4.2 Excel Function of Gamma for European Call Options
8.4.3 Application of Gamma
8.5 Vega
8.5.1 Formula of Vega for Different Kinds of Stock Options
8.5.2 Excel Function of Vega for European Call Options
8.5.3 Application of Vega
8.6 Rho
8.6.1 Formula of Rho for Different Kinds of Stock Options
8.6.2 Excel Function of Rho for European Call Options
8.6.3 Application of Rho
8.7 Formula of Sensitivity for Stock Options with Respect to Exercise Price
8.8 Relationship Between Delta, Theta, and Gamma
8.9 Portfolio Insurance
8.10 Summary
References
9 Portfolio Analysis and Option Strategies
9.1 Introduction
9.2 Three Alternative Methods to Solve the Simultaneous Equation
9.2.1 Substitution Method (Reference: Wikipedia)
9.2.2 Cramer’s Rule
9.2.3 Matrix Method
9.2.4 Excel Matrix Inversion and Multiplication
9.3 Markowitz Model for Portfolio Selection
9.4 Option Strategies
9.4.1 Long Straddle
9.4.2 Short Straddle
9.4.3 Long Vertical Spread
9.4.4 Short Vertical Spread
9.4.5 Protective Put
9.4.6 Covered Call
9.4.7 Collar
9.5 Summary
Appendix 9.1: Monthly Rates of Returns for S&P500, IBM, and MSFT
Appendix 9.2: Options Data for IBM (Stock Price = 141.34) on July 23, 2021
References
10 Simulation and Its Application
10.1 Introduction
10.2 Monte Carlo Simulation
10.3 Antithetic Variables
10.4 Quasi-Monte Carlo Simulation
10.5 Application
10.6 Summary
Appendix 10.1: EXCEL CODE—Share Price Paths
References
On the Web
Applications of Python, Machine Learning for Financial Derivatives and Risk Management
11 Linear Models for Regression
11.1 Introduction
11.2 Loss Functions and Least Squares
11.3 Regularized Least Squares—Ridge and Lasso Regression
11.4 Logistic Regression for Classification: A Discriminative Model
11.5 K-fold Cross-Validation
11.6 Types of Basis Function
11.7 Accuracy Measures in Classification
11.8 Python Programming Example
Questions and Problems for Coding
References
12 Kernel Linear Model
12.1 Introduction
12.2 Constructing Kernels
12.3 Kernel Regression (Nadaraya–Watson Model)
12.4 Relevance Vector Machines
12.5 Gaussian Process for Regression
12.6 Support Vector Machines
12.7 Python Programming
12.8 Kernel Linear Model and Support Vector Machines
References
13 Neural Networks and Deep Learning Algorithm
13.1 Introduction
13.2 Feedforward Network Functions
13.3 Network Training: Error Backpropagation
13.4 Gradient Descent Optimization
13.5 Regularization in Neural Networks and Early Stopping
13.6 Deep Feedforward Network Versus Deep Convolutional Neural Networks
13.7 Python Programing
References
14 Alternative Machine Learning Methods for Credit Card Default Forecasting*
14.1 Introduction
14.2 Literature Review
14.3 Description of the Data
14.4 Alternative Machine Learning Methods
14.4.1 k-Nearest Neighbors
14.4.2 Decision Trees
14.4.3 Boosting
14.4.4 Support Vector Machines
14.4.5 Neural Networks
14.5 Study Plan
14.5.1 Data Preprocessing and Python Programming
14.5.2 Tuning Optimal Parameters
14.5.3 Learning Curves
14.6 Summary and Concluding Remarks
Appendix 14.1: Python Codes
References
15 Deep Learning and Its Application to Credit Card Delinquency Forecasting
15.1 Introduction
15.2 Literature Review
15.3 The Methodology
15.3.1 Deep Learning in a Nutshell
15.3.2 Deep Learning Versus Conventional Machine Learning Approaches
15.3.3 The Structure of a DNN and the Hyper-Parameters
15.4 Data
15.5 Experimental Analysis
15.5.1 Splitting the Data
15.5.2 Tuning the Hyper-Parameters
15.5.3 Techniques of Handling Data Imbalance
15.6 Results
15.6.1 The Predictor Importance
15.6.2 The Predictive Result for Cross-Validation Sets
15.6.3 Prediction on Test Set
15.7 Conclusion
Appendix 15.1: Variable Definition
References
16 Binomial/Trinomial Tree Option Pricing Using Python
16.1 Introduction
16.2 European Option Pricing Using Binomial Tree Model
16.2.1 European Option Pricing—Two Period
16.2.2 European Option Pricing—N Periods
16.3 American Option Pricing Using Binomial Tree Model
16.4 Alternative Tree Models
16.4.1 Cox, Ross, and Rubinstein Model
16.4.2 Trinomial Tree
16.5 Summary
Appendix 16.1: Python Programming Code for Binomial Tree Option Pricing
Appendix 16.2: Python Programming Code for Trinomial Tree Option Pricing
References
Financial Management
17 Financial Ratio Analysis and Its Applications
17.1 Introduction
17.2 Financial Statements: A Brief Review
17.2.1 Balance Sheet
17.2.2 Statement of Earnings
17.2.3 Statement of Equity
17.2.4 Statement of Cash Flows
17.2.5 Interrelationship Among Four Financial Statements
17.2.6 Annual Versus Quarterly Financial Data
17.3 Static Ratio Analysis
17.3.1 Static Determination of Financial Ratios
17.4 Two Possible Methods to Estimate the Sustainable Growth Rate
17.5 DFL, DOL, and DCL
17.5.1 Degree of Financial Leverage
17.5.2 Operating Leverage and the Combined Effect
17.6 Summary
Appendix 17.1: Calculate 26 Financial Ratios with Excel
Appendix 17.2: Using Excel to Calculate Sustainable Growth Rate
Appendix 17.3: How to Compute DOL, DFL, and DCL with Excel
References
18 Time Value of Money Determinations and Their Applications
18.1 Introduction
18.2 Basic Concepts of Present Values
18.3 Foundation of Net Present Value Rules
18.4 Compounding and Discounting Processes
18.4.1 Single Payment Case—Future Values
18.4.2 Continuous Compounding
18.4.3 Single Payment Case—Present Values
18.4.4 Annuity Case—Present Values
18.4.5 Annuity Case—Future Values
18.4.6 Annual Percentage Rate
18.5 Present and Future Value Tables
18.5.1 Future Value of a Dollar at the End of t Periods
18.5.2 Future Value of a Dollar Continuously Compounded
18.5.3 Present Value of a Dollar Received t Periods in the Future
18.5.4 Present Value of an Annuity of a Dollar Per Period
18.6 Why Present Values Are Basic Tools for Financial Management Decisions
18.6.1 Managing in the Stockholders’ Interest
18.6.2 Productive Investments
18.7 Net Present Value and Internal Rate of Return
18.8 Summary
Appendix 18A
Appendix 18B
Appendix 18C
Continuous Compounding
Continuous Discounting
Appendix 18D: Applications of Excel for Calculating Time Value of Money
Future Value of a Single Amount
Present Value of a Single Amount
Future Value of an Ordinary Annuity
Present Value of an Ordinary Annuity
Appendix 18E: Tables of Time Value of Money
References
19 Capital Budgeting Method Under Certainty and Uncertainty
19.1 Introduction
19.2 The Capital Budgeting Process
19.2.1 Identification Phase
19.2.2 Development Phase
19.2.3 Selection Phase
19.2.4 Control Phase
19.3 Cash-Flow Evaluation of Alternative Investment Projects
19.4 Alternative Capital-Budgeting Methods
19.4.1 Accounting Rate-of-Return
19.4.2 Internal Rate-of-Return Method
19.4.3 Payback Method
19.4.4 Net Present Value Method
19.4.5 Profitability Index
19.5 Capital-Rationing Decision
19.5.1 Basic Concepts of Linear Programming
19.5.2 Capital Rationing
19.6 The Statistical Distribution Method
19.6.1 Statistical Distribution of Cash Flow
19.7 Simulation Methods
19.7.1 Simulation Analysis and Capital Budgeting
19.8 Summary
Appendix 19.1: Solving the Linear Program Model for Capital Rationing
Example 19.3
Appendix 19.3: Hillier’s Statistical Distribution Method for Capital Budgeting Under Uncertainty
References
20 Financial Analysis, Planning, and Forecasting
20.1 Introduction
20.2 Procedures for Financial Planning and Analysis
20.3 The Algebraic Simultaneous Equations Approach to Financial Planning and Analysis
20.4 The Linear Programming Approach to Financial Planning and Analysis
20.4.1 Profit Maximization
20.4.2 Linear Programming and Capital Rationing
20.4.3 Linear Programming Approach to Financial Planning
20.5 The Econometric Approach to Financial Planning and Analysis
20.5.1 A Dynamic Adjustment of the Capital Budgeting Model
20.5.2 Simplified Spies Model
20.6 Sensitivity Analysis
20.7 Summary
Appendix 20.1: The Simplex Algorithm for Capital Rationing
Appendix 20.2: Description of Parameter Inputs Used to Forecast Johnson & Johnson’s Financial Statements and Share Price
Appendix 20.3: Procedure of Using Excel to Implement the FinPlan Program
References
Applications of R Programs for Financial Analysis and Derivatives
21 Hedge Ratio Estimation Methods and Their Applications
21.1 Introduction
21.2 Alternative Theories for Deriving the Optimal Hedge Ratio
21.2.1 Static Case
21.2.1.1 Minimum-Variance Hedge Ratio
21.2.1.2 Optimum Mean–Variance Hedge Ratio
21.2.1.3 Sharpe Hedge Ratio
21.2.1.4 Maximum Expected Utility Hedge Ratio
21.2.1.5 Minimum Mean Extended-Gini Coefficient Hedge Ratio
21.2.1.6 Optimum Mean-MEG Hedge Ratio
21.2.1.7 Minimum Generalized Semivariance Hedge Ratio
21.2.1.8 Optimum Mean-Generalized Semivariance Hedge Ratio
21.2.1.9 Minimum Value-at-Risk Hedge Ratio
21.2.2 Dynamic Case
21.2.3 Case with Production and Alternative Investment Opportunities
21.3 Alternative Methods for Estimating the Optimal Hedge Ratio
21.3.1 Estimation of the Minimum-Variance (MV) Hedge Ratio
21.3.1.1 OLS Method
21.3.1.2 Multivariate Skew-Normal Distribution Method
21.3.1.3 ARCH and GARCH Methods
21.3.1.4 Regime-Switching GARCH Model
21.3.1.5 Random Coefficient Method
21.3.1.6 Cointegration and Error Correction Method
21.3.2 Estimation of the Optimum Mean–Variance and Sharpe Hedge Ratios
21.3.3 Estimation of the Maximum Expected Utility Hedge Ratio
21.3.4 Estimation of Mean Extended-Gini (MEG) Coefficient Based Hedge Ratios
21.3.5 Estimation of Generalized Semivariance (GSV) Based Hedge Ratios
21.4 Applications of OLS, GARCH, and CECM Models to Estimate Optimal Hedge Ratio
21.5 Hedging Horizon, Maturity of Futures Contract, Data Frequency, and Hedging Effectiveness
21.6 Summary and Conclusions
Appendix 21.1: Theoretical Models
Appendix 21.2: Empirical Models
Appendix 21.3: Monthly Data of S&P500 Index and Its Futures (January 2005–August 2020)
Appendix 21.4: Applications of R Language in Estimating the Optimal Hedge Ratio
References
22 Application of Simultaneous Equation in Finance Research: Methods and Empirical Results
22.1 Introduction
22.2 Literature Review
22.3 Methodology
22.3.1 Application of GMM Estimation in the Linear Regression Model
22.3.2 Applications of GMM Estimation in the Simultaneous Equations Model
22.3.3 Weak Instruments
22.4 Applications in Investment, Financing, and Dividend Policy
22.4.1 Model and Data
22.4.2 Results of Weak Instruments
22.4.3 Empirical Results
22.5 Conclusion
Appendix 22.1: Data for Johnson & Johnson and IBM
ch22Sec13
1.2 IBM Data
Appendix 22.2: Applications of R Language in Estimating the Parameters of a System of Simultaneous Equations
References
23 Three Alternative Programs to Estimate Binomial Option Pricing Model and Black and Scholes Option Pricing Model
23.1 Introduction
23.2 Microsoft Excel Program for the Binomial Tree Option Pricing Model
23.3 Black and Scholes Option Pricing Model for Individual Stock
23.4 Black and Scholes Option Pricing Model for Stock Indices
23.5 Black and Scholes Option Pricing Model for Currencies
23.6 R Codes to Implement the Binomial Trees Option Pricing Model
23.7 R Codes to Compute Option Prices by Black and Scholes Model
23.8 Summary
Appendix 23.1: SAS Programming to Implement the Binomial Option Trees
Appendix 23.2: SAS Programming to Compute Option Prices Using Black and Scholes Model
References
