Advanced Modelling in Finance Using Excel and VBA - Mary Jackson

. .

Résumé :

Preface xi

Acknowledgements xii

1 Introduction 1

1.1 Finance insights 1

1.2 Asset price assumptions 2

1.3 Mathematical and statistical problems 2

1.4 Numerical methods 2

1.5 Excel solutions 3

1.6 Topics covered 3

1.7 Related Excel workbooks 5

1.8 Comments and suggestions 5

Part One Advanced Modelling in Excel 7

2 Advanced Excel functions and procedures 9

2.1 Accessing functions in Excel 9

2.2 Mathematical functions 10

2.3 Statistical functions 12

2.3.1 Using the frequency function 12

2.3.2 Using the quartile function 14

2.3.3 Using Excel's normal functions 15

2.4 Lookup functions 16

2.5 Other functions 18

2.6 Auditing tools 19

2.7 Data Tables 20

2.7.1 Setting up Data Tables with one input 20

2.7.2 Setting up Data Tables with two inputs 22

2.8 XY charts 23

2.9 Access to Data Analysis and Solver 26

2.10 Using range names 27

2.11 Regression 28

2.12 Goal Seek 31

2.13 Matrix algebra and related functions 33

2.13.1 Introduction to matrices 33

2.13.2 Transposing a matrix 33

2.13.3 Adding matrices 34

2.13.4 Multiplying matrices 34

2.13.5 Matrix inversion 35

2.13.6 Solving systems of simultaneous linear equations 36

2.13.7 Summary of Excel's matrix functions 37

Summary 37

3 Introduction to VBA 39

3.1 Advantages of mastering VBA 39

3.2 Object-oriented aspects of VBA 40

3.3 Starting to write VBA macros 42

3.3.1 Some simple examples of VBA subroutines 42

3.3.2 MsgBox for interaction 43

3.3.3 The writing environment 44

3.3.4 Entering code and executing macros 44

3.3.5 Recording keystrokes and editing code 45

3.4 Elements of programming 47

3.4.1 Variables and data types 48

3.4.2 VBA array variables 48

3.4.3 Control structures 50

3.4.4 Control of repeating procedures 51

3.4.5 Using Excel functions and VBA functions in code 52

3.4.6 General points on programming 53

3.5 Communicating between macros and the spreadsheet 53

3.6 Subroutine examples 56

3.6.1 Charts 56

3.6.2 Normal probability plot 59

3.6.3 Generating the efficient frontier with Solver 61

Summary 65

References 65

Appendix 3A The Visual Basic Editor 65

Stepping through a macro and using other debug tools 68

Appendix 3B Recording keystrokes in 'relative references' mode 69

4 Writing VBA user-defined functions 73

4.1 A simple sales commission function 73

4.2 Creating Commission(Sales) in the spreadsheet 74

4.3 Two functions with multiple inputs for valuing options 75

4.4 Manipulating arrays in VBA 78

4.5 Expected value and variance functions with array inputs 79

4.6 Portfolio variance function with array inputs 81

4.7 Functions with array output 84

4.8 Using Excel and VBA functions in user-defined functions 85

4.8.1 Using VBA functions in user-defined functions 85

4.8.2 Add-ins 86

4.9 Pros and cons of developing VBA functions 86

Summary 87

Appendix 4A Functions illustrating array handling 88

Appendix 4B Binomial tree option valuation functions 89

Exercises on writing functions 94

Solution notes for exercises on functions 95

Part Two Equities 99

5 Introduction to equities 101

6 Portfolio optimisation 103

6.1 Portfolio mean and variance 103

6.2 Risk-return representation of portfolios 105...

Sommaire:
Mary G. Jackson, MEd, MS, is a Licensed Clinical Professional Counselor (LCPC) in private practice and a graduate of Loyola College of Maryland's School of Pastoral Counseling. She holds two master's degrees, the first studying educational psychology and the second in counseling, followed by advanced study in pastoral counseling. Mary has led an eclectic life spurred by her adventurous curiosity. She is just as much at home in the city of her birth, Washington, D.C., the Maryland farm raising alpacas, or the coastal beach of Delaware. Mary delights in her close and loving family, world travel, and a search for meaning. A life-long seeker, Mary invites you to ponder your spiritual journey and to recognize the Divine in your everyday life....