Essentials of Excel VBA, Python, and R: Volume II: Financial Derivatives, Risk Management and Machine Learning, 2nd edition

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