发帖
雷达卡
Financial Modeling with Excel and VBA.pdf
(8.94 MB, 需要: 10 个论坛币)
Financial Modeling with Excel and VBA (Ben).pdf
(1.02 MB, 需要: 10 个论坛币)
I. GETTING STARTED WITH EXCEL ....................................................................... 4
A. Things You Should Be Familiar With ........................................................................ 5
B. Working with Cells ..................................................................................................... 7
C. Working with Data ...................................................................................................... 9
D. Working with Dates and Times ................................................................................ 10
E. Other Functionality ................................................................................................... 11
F. Getting Price Data from Yahoo! Finance ................................................................. 12
G. LAB 1: Calculating Volatility and Covariance ........................................................ 13
II. Advanced Excel Functionality .................................................................................. 14
A. Goal Seek .................................................................................................................. 14
B. Data Tables ............................................................................................................... 15
C. Pivot Tables .............................................................................................................. 16
D. Histograms ................................................................................................................ 17
E. Calculating Portfolio Volatility................................................................................. 19
F. LAB 2: Calculating Portfolio Metrics ...................................................................... 21
II. VISUAL BASIC FOR APPLICATIONS ................................................................. 22
A. Variable Scope .......................................................................................................... 25
B. Conditional Statements ............................................................................................. 26
D. Loops......................................................................................................................... 27
F. Using Excel Functions in VBA ................................................................................. 28
G. Recording Macros ..................................................................................................... 29
H. LAB 3: Programming VBA ..................................................................................... 30
I. Arrays ........................................................................................................................ 31
J. Calculating Beta ........................................................................................................ 33
K. LAB 4: Beta ............................................................................................................. 34
L. Financial Data ........................................................................................................... 35
M. Correlation and Ranking ........................................................................................ 36
N. Distribution Fitting Example .................................................................................... 38
O. Scaling and Ranking in Practice ............................................................................... 41
P. Double Normalizing.................................................................................................. 42
Q. LAB 5: Scaling and Ranking ................................................................................... 44
III. INTRODUCTION TO SIMULATION ................................................................. 45
A. Uniform Distribution ................................................................................................ 47
III. CONTINUOUS DISTRIBUTIONS ...................................................................... 50
A. Inverse Transform Method ....................................................................................... 50
C. Exponential Distribution ........................................................................................... 51
E. Triangular Distribution ............................................................................................. 53
F. Normal Distribution .................................................................................................. 55
G. Lognormal Distribution ............................................................................................ 59
H. Generalized Inverse Transform Method ................................................................... 60
IV. DISCRETE DISTRIBUTIONS ............................................................................. 61
A. Bernoulli Trials ......................................................................................................... 61
B. Binomial Distribution ............................................................................................... 62
C. Trinomial Distribution .............................................................................................. 63
D. Poisson Distribution .................................................................................................. 64
E. Empirical Distributions ............................................................................................. 65
F. Linear Interpolation .................................................................................................. 66
V. GENERATING CORRELATED RANDOM NUMBERS ...................................... 67
A. Bivariate Normal ....................................................................................................... 68
B. Multivariate Normal.................................................................................................. 69
VI. MODELING REAL TIME FINANCIAL DATA ................................................. 71
A. Financial Market Data ............................................................................................... 74
B. Modeling Tick Data .................................................................................................. 76
C. Modeling Time Series Data ...................................................................................... 84
D. LAB 6: Augmenting the Simple Price Path Simulation .......................................... 86
1. Fat Tails .................................................................................................................... 87
2. Stochastic Volatility Models ..................................................................................... 88
a. ARCH(1) ................................................................................................................... 89
b. GARCH(1,1) ............................................................................................................. 89
b. Estimating Volatility ................................................................................................. 90
VII. MODELING OPTIONS ........................................................................................ 92
A. The Simulation Way ................................................................................................. 93
B. The Cox-Ross-Rubenstein (CRR) or Binomial Way ................................................ 94
C. The Black-Scholes Way ............................................................................................ 96
D. Option Greeks ........................................................................................................... 97
E. Implied Volatility ...................................................................................................... 98
F. American Options ..................................................................................................... 99
VIII. OPTIMIZATION ............................................................................................. 100
A. Linear Optimization ................................................................................................ 101
B. LAB 7: Nonlinear Optimization ............................................................................ 103
C. Efficient Portfolios .................................................................................................. 104
D. Capital Budgeting ................................................................................................... 107
APPENDIX I: MATRIX MATH PRIMER ............................................................... 109
A. Matrix Transpose .................................................................................................... 109
B. Addition and Subtraction ........................................................................................ 109
C. Scalar Multiplication ............................................................................................... 110
D. Matrix Multiplication .............................................................................................. 110
E. Matrix Inversion ...................................................................................................... 110
APPENDIX II: CALCULUS PRIMER ...................................................................... 112
A. Differentiation ......................................................................................................... 112
B. Taylor’s Theorem.................................................................................................... 113
C. Integration ............................................................................................................... 113
D. Fundamental Theorem ............................................................................................ 113
京公网安备 11010802022788号
