Published on: **Mar 3, 2016**

- 1. Using the NAG Library in Excel Marcin Krzysztofik NAG Ltd.Experts in numerical algorithmsand HPC services
- 2. Agenda NAG Introduction Why do Quants love NAG? NAG from VBA Examples • Least Squares Optimisation • Local Volatility • Nearest Correlation Matrix 2
- 3. The Numerical Algorithms Group NAG provides mathematical and statistical algorithm libraries and compiler widely used in industry and academia Founded in 1970 as a co-operative project in UK Operates as a commercial, not-for-profit organization Worldwide operations • Offices in Oxford & Manchester, Chicago, Tokyo, Taipei • Technical Support Engineer in Germany • Distributors in Brazil, India, Singapore, Mexico… Over 3,000 customer sites worldwide Staff of ~100, ~70 technical, includes ~25 HPC 3
- 4. NAG Portfolio Mathematical, statistical, data analysis components • NAG Numerical libraries • Connectors for Excel, .NET, R, Java NAG Fortran Compiler • Windows GUI - Fortran Builder HPC software engineering services • HECToR support Consultancy work for bespoke application development 4
- 5. NAG is a HPCFinance partner http://www.hpcfinance.eu The network is recruiting for Early Stage Researchers (ESRs ~ PhD Students) Experienced Researchers (ERs ~ Post Docs) 5
- 6. The NAG Library Contents • Dense Linear Algebra• Root Finding • Sparse Linear Algebra• Summation of Series • Correlation & Regression Analysis• Quadrature • Multivariate Methods• Ordinary Differential Equations • Analysis of Variance• Partial Differential Equations • Random Number Generators• Numerical Differentiation • Univariate Estimation• Integral Equations • Nonparametric Statistics• Mesh Generation • Smoothing in Statistics• Interpolation • Contingency Table Analysis• Curve and Surface Fitting • Survival Analysis• Optimisation • Time Series Analysis• Approximations of Special Functions • Operations Research 6
- 7. The NAG Library is now at Mark 23Now available as: The NAG Fortran Library The NAG Library for SMP & Multicore The NAG C Library The NAG Toolbox for MATLAB 7 7
- 8. NAG Library : new Mark 23Mark 23 has new functions in many areas including... Wavelet Transforms • One dimensional continuous transforms Optimisation • Two dimensional discrete single level and • Multi-start optimisation multi-level transforms • Minimization by quadratic approximation ODE’s (BOBYQA) • BVP solution through Chebyshev pseudo- • Stochastic global optimisation using PSO spectral method RNG’s Matrix Operations • Generators of multivariate copulas • Matrix exponentials • Skip-ahead for Mersenne Twister • Functions of real symmetric and Hermitian • L’Ecuyer MRG32K3a generator matricies Statistics • Sparse matrix functions • Quantiles of streamed data, bivariate • LAPACK 3.2 Cholesky solvers and Student’s t, and two probability density factorizations, and many other LAPACK functions driver functions • Nearest correlation matrices Interpolation • Quantile regression • Modified Shepard’s method in 4D/5D • Peirce Outlier detection New vector functions (in G01 and S) • Anderson–Darling goodness-of-fit 8
- 9. NAG Library : new Mark 23Mark 23 has new functions in many areas including... Wavelet Transforms • One dimensional continuous transforms Optimisation • Two dimensional discrete single level and • Multi-start optimisation multi-level transforms • Minimization by quadratic approximation ODE’s (BOBYQA) • BVP solution through Chebyshev pseudo- • Stochastic global optimisation using PSO spectral method RNG’s Matrix Operations • Generators of multivariate copulas • Matrix exponentials • Skip-ahead for Mersenne Twister • Functions of real symmetric and Hermitian • L’Ecuyer MRG32K3a generator matricies Statistics • Sparse matrix functions • Quantiles of streamed data, bivariate • LAPACK 3.2 Cholesky solvers and Student’s t, and two probability density factorizations, and many other LAPACK functions driver functions • Nearest correlation matrices Interpolation • Quantile regression • Modified Shepard’s method in 4D/5D • Peirce Outlier detection New vector functions (in G01 and S) • Anderson–Darling goodness-of-fit 9
- 10. Why do Quants love NAG?Senior quant from Tier 1 Investment Bank“We deploy production code in C++ embedding NAG C Library functionswherever we can, but often prototype new models in MATLAB beforewriting our C++ code. Having the same NAG algorithms in MATLAB via theNAG Toolbox for MATLAB is a real win for us”OK, what more? 10
- 11. Numerical computation – DIY Vs NAG DIY implementations of numerical components have their place, but NOT in production code. • Handwritten code might be easy to implement, but will… NOT be well tested NOT be fast NOT be stable NOT deliver good error handling • NAG implementations in contrast are fast and Accurate Well tested & thoroughly documented Give “qualified error” messages e.g. tolerances of answers (which the user can choose to ignore, but avoids proceeding blindly) 11
- 12. Problem 1: Finance PDE Solvers Major banks PDE solvers tend to be “proprietary” so there is a reluctance to take “end to end” solutions Several different numerical components needed • Sparse Linear Solvers • Functions for finding a range of Eigenvalues (stability checkers) • Special Linear Systems solvers (e.g. Banded Matrices) • .. 12
- 13. Problem 1: Finance PDE Solvers Major banks PDE solvers tend to be “proprietary” so there is a reluctance to take “end to end” solutions NAG to the rescue Several different numerical components needed • Sparse Linear Solvers √ • Functions for finding a range of Eigenvalues (stability checkers) √ • Special Linear Systems solvers (e.g. Banded Matrices) √ • .. √ √ 13
- 14. Problem 2: Calibration Major banks all need to calibrate their models Several different numerical components needed • Optimisation functions (e.g. constrained non-linear optimisers) • Interpolation functions • Spline functions • .. 14
- 15. Problem 2: Calibration Major banks all need to calibrate their models NAG to the rescue Several different numerical components needed • Optimisation functions (e.g. constrained non-linear optimisers) √ • Interpolation functions (used intelligently*) √ • Spline functions √ • .. √ √ *interpolator must be used carefully –must know the properties to pick appropriate method 15
- 16. Problem 3: Closed-Form Option Pricing Formulae Closed form solutions are useful • for validating numerical methods • for some calibration problems as input (objective function) to optimisers Several different numerical components needed • FFTs, Quadrature • Root finders (single variable) • Special functions (Bessel, non-Central Chi, Erf,..) • Probability distributions • .. 16
- 17. Problem 3: Closed-Form Option Pricing Formulae Closed form solutions are useful • for validating numerical methods • for some calibration problems as input (objective function) to optimisers NAG to the rescue Several different numerical components needed • FFTs, Quadrature √ • Root finders (single variable) √ • Special functions (Bessel, non-Central Chi, Erf,..) √ • Probability distributions √ • .. √ √ 17
- 18. Problem 4: Simulation (Monte Carlo) Simulation is important for scenario generation Several different numerical components needed • Random Number Generators • Brownian bridge constructor • Interpolation/Splines • Principal Component Analysis • Cholesky Decomposition • Distributions (uniform, Normal, exponential gamma, Poisson, Student’s t, Weibull,..) • .. 18
- 19. Problem 4: Simulation (Monte Carlo) Simulation is important for scenario generation NAG to the rescue Several different numerical components needed • Random Number Generators √ • Brownian bridge constructor √ • Interpolation/Splines √ • Principal Component Analysis√ • Cholesky Decomposition √ • Distributions (uniform, Normal, exponential gamma, Poisson, Student’s t, Weibull,..)√ • .. √ √ 19
- 20. Problem 5: Risk Matrix VaR VaR is important for identifying correlation of one asset to another (eg Yen Vs USD) Several different numerical components needed • Matrix functions • Correlation and Regressions • • .. 20
- 21. Problem 5: Risk Matrix VaR VaR is important for identifying correlation of one asset to another (eg Yen Vs USD) NAG to the rescue Several different numerical components needed • Matrix functions √ • Correlation and Regressions √ • • .. √ √ 21
- 22. Problem 6: Historical VaR This is another VaR methodology (again important for identifying what variables might impact you most (eg Yen Vs USD)) Several different numerical components needed • Time Series • Correlation and Regressions • Matrix functions • Cholesky Decomp • RNGs .. 22
- 23. Problem 6: Historical VaR This is another VaR methodology (again important for identifying what variables might impact you most (eg Yen Vs USD)) NAG to the rescue Several different numerical components needed • Time Series √ • Correlation and Regressions √ • Matrix functions √ • Cholesky Decomp √ • RNGs .. √ √ 23
- 24. NAG provides the atomic bricks … for the quants to build the walls, houses and fancy castles! Users know NAG Components are here today, tomorrow and beyond • Functions are not removed when new ones added without sensible notice and advice • NAG functions are well documented Lets take a look at Excel & VBA now… 24
- 25. NAG from Excel Intro Why use NAG from Excel How to call NAG from Excel • A simple function • Callbacks • Passing arrays 25
- 26. NAG from Excel Use Excel’s underlying VBA to call external library NAG provides VBA Declaration Statements • Shipped together with the DLL version of NAG Library • Just Ctrl+C Ctrl+V into a VBA module Start creating macros and UDFs • Macros executed for example via a button • User Defined Functions called via Excel Function Wizard 26
- 27. Why NAG & Excel? Microsoft Office Excel is the industry-standard spreadsheet application. Easy to use, intuitive, practical. However it’s not a statistical data analysis package... And that’s where NAG being great at serious numerical calculations comes in! 27
- 28. How to call a simple function? S17AEF – returns value of the Bessel function 𝐽0 (𝑥) Note: • Declaration statements in VB • Calling straight from the spreadsheet • Calling through a wrapper function • Error handling 28
- 29. The IFAIL parameterIFAIL serves two purposes: On input it determines what action the Library routine takes when an error is detected: • -1 (Soft Fail & Noisy Exit) – error message and continue; • 0 (Hard Fail) – error message and stop; • 1 (Soft Fail & Quiet Exit) – no error message and continue. On output it informs what kind of error has occurred, depending on the function in use (see documentation for details). 29
- 30. How to handle callbacks? A callback is a piece of executable code that is passed as an argument to other code. D01BAF – performs numerical integration on a function of one variable • uses Gaussian quadrature rules Note: • Visual Basic AddressOf operator to handle callbacks • Argument Passing by reference ByRef 30
- 31. How to pass arrays as arguments? F07FDF – computes the Cholesky factorization of a real symmetric positive definite matrix. Note: • Reading the array from the worksheet (Range -> Double) • Checking if the matrix is a square matrix • What if the matrix is not symmetric? 32
- 32. Example Least Squares Optimisation 33
- 33. 1st Example: Least Squares Optimisation General formulation of an optimisation problem is min F ( x) subject to constraints on x xR n • Constraints can be bound, linear or fully non-linear • Example: maximise value of portfolio subject to limits on capital, individual positions and overall risk exposure • Have routines in NAG Library to solve these problems Least squares optimisation addresses the following: m min f i 2 ( x) subject to constraints on x xR n i 1 • Most commonly used in fitting data 34
- 34. 1st Example: Least Squares Optimisation Important special case: x m Ax min yi f i ( x) subject to l u 2 xR n i 1 c( x) • Used in calibration and non-linear regression, typically in the form m min yi C ( xi , ) subject to l u 2 R n i 1 • Here 𝐶is a pricing formula (e.g. SABR, Heston, CIR) with parameters 𝛽, and (𝑥 𝑖 , 𝑦 𝑖 ) are 𝑚 observed market prices 35
- 35. 1st Example: Least Squares Optimisation Another important special case: x min x A x b x subject to l T T u xR C x n • Called Quadratic Programming • 𝐴 and 𝐶 and matrices and 𝑏 is a vector • Objective function is quadratic polynomial, constraints are linear • Arises in certain asset allocation problems, interpolation problems, linear programming problems ... • Emphasis on speed and scale: can have 𝑛 ≥ 20,000 and still require a solution in under 0.5s 36
- 36. 1st Example: Mean-Variance Analysis Recall classic Markowitz model n P( x) xi Ri x R subject to n T x 1 i 1 i i 1 • Assume return on each asset 𝑖 is 𝑅 𝑖 ~𝑁1 (𝜇 𝑖 , 𝜎 𝑖2 ) • Assume vector of all 𝑚 returns 𝑅~𝑁 𝑛 (𝜇, A) • Therefore return on portfolio 𝑃 𝑥 ~𝑁1 (𝑥 𝑇 𝜇, 𝑥 𝑇 𝐴𝑥) • Define “Risk” as variance of return on portfolio, i.e. 𝑉𝑎𝑟 𝑃(𝑥) = 𝑥 𝑇 𝐴𝑥 • Problem: for given level of expected return 𝑟, find the portfolio with minimal risk min Var P( x) x A x s.t. T xi 1 xR n EP( x) i xi r 37
- 37. 1st Example: Mean-Variance Analysis Let’s look at some market data • 10 big FTSE100 players • Market data: 20/05/2003 – 19/06/2009 Use NAG to compute covariances, returns and standard deviations of the stock returns Set upper & lower investment constraints Optimize • Return portfolio risk for given return, sensitivities, shadow prices • Plot the Efficient Frontier 38
- 38. Example Local Volatility Model 39
- 39. 2nd Example: Local Volatility Model What’s in a smile? • Black-Scholes model is not perfect. Main drawbacks? • So traders pick different vol for each combination of maturity and strike, in order to get “correct” price Local volatility model tries to express this dSt r (t ) (t ) St dt (t , St ) St dWt • Here 𝜎(𝑡, 𝑥) is a function, to be determined • Since volatility now depends on time and asset level, we could hope that it’s possible to capture smile effect • But what should 𝜎(𝑡, 𝑥) be to achieve this? • Key is Dupire formula 40
- 40. 2nd Example: Local Volatility Model Let 𝐶(𝑇, 𝐾) denote price of European call option in Local Volatility model • By manipulating Kolmogorov forward equation (Fokker- Plank equation) together with 0 r ( s ) ds T C (T , K ) E e maxST K ,0 we can show that CT (T )C (T )C K (T , K ) 2 K 2C KK • Now 𝐶(𝑇, 𝐾) we observe in the market – why? So this gives a formula to translate market call quotes into 𝜎(𝑡, 𝑥) 41
- 41. 2nd Example: Local Volatility Model Let 𝜃(𝑇, 𝐾) be market implied vol for a call option • Then 𝐶 𝑇, 𝐾 = 𝐵𝑆(𝑇, 𝐾, 𝜃(𝑇, 𝐾)) where 𝐵𝑆(. ) is the Black-Scholes pricing formula • Substituting and simplifying, we arrive at the Dupire formula 2T / T 2 K r (T ) (T ) K 2 (T , K ) 1 1 2 K KK d T K 2 d K 2 K T • So given a smile surface 𝜃(𝑇, 𝐾) we can also recover 𝜎(𝑡, 𝑥). This more common way of doing things. 42
- 42. 2nd Example: Local Volatility Model Sweet – no problems!! • Well, no. What’s the problem? 43
- 43. 2nd Example: Local Volatility Model Two problems • 1. Dupire formula assumes infinitely many market quotes. We only have finitely many. We’ll have to interpolate, and this is challenging • 2. Once we’ve obtained 𝜎(𝑡, 𝑥), what then? How do we price options? Two routes: either Monte Carlo, or PDEs. 44
- 44. 2nd Example: Interpolation Interpolation – but that’s just joining the dots! • Interpolated surface must be smooth: at least 𝐶 1,2 Many interpolators can do this, so not such a problem. • Worse: the interpolated surface must be arbitrage free! E.g. suppose had quotes for maturity 𝑇 and 𝐾 = 50,60,70,80,90. Delete quote for 𝐾 = 70 and interpolate over 𝐾 = 50,60,80,90. Interpolated value at 𝐾 = 70 must be consistent with current market prices – no arbitrage Termed shape preserving interpolation – it’s a very hard mathematical problem! • Research is quite young, and not much in financial space 45
- 45. 2nd Example: Interpolation So how is this problem commonly tackled? • As best you can • One approach – fit monotonic (Hermite) interpolator in time, then a cubic spline in space • Make sure 𝜎 𝑡, 𝑥 ≥ 0 Backward heat problems are unstable NAG Library has all these interpolators (and more!) • Currently has no routines for shape constrained interpolation, but watch this space! 46
- 46. 2nd Example: PDE Solution Monte Carlo vs PDEs? • Local vol model is quite amenable to PDE techniques, provided interpolation is good enough • Monte Carlo reserved for heavily path-dependent options The local volatility model PDE for option price 𝑉 is Vt r (t ) (t ) SVS 1 2 2 (t , S ) S 2VSS r (t )V • NAG Library has many PDE solvers, but which kind of solver should we be using? • Decision tree 47
- 47. 2nd Example: NAG PDE Solver Try to use most specific solver, rather than most general solver So seems we should use D03PFF • Provided we can massage our PDE into conservative form • Conservative form is term used predominantly in engineering literature • Related to continuity or conservation laws Conservation of mass Conservation of momentum Conservation of energy Etc. • Many PDEs can be put into conservative form 48
- 48. 2nd Example: NAG PDE Solver Can re-write our PDE as follows Vt r SV V 1 2 2 S 2 VS rV S S 2S 2 1 Vt SV VS 2r V r S 2r S r • Note 𝑟, 𝛾, 𝜎 are still functions. Our boundary conditions are at 𝑡 = 𝑇, not 𝑡 = 0. So set 𝜏 = 𝑇 − 𝑡 to get 1 2S 2 2r V SV VS V r S 2r S r 49
- 49. 2nd Example: NAG PDE Solver So our PDE can be placed in conservative form What about flux? How can we get approximate solutions? • Work it out analytically for your approximate Riemann solver of choice: our flux equation is Vt SV 0 S • Use a NAG routine to approximate it numerically (D03PUF) • Look at Example 2 in D03PFF doc Just so happens to have our exact flux equation, with solution 50
- 50. 2nd Example: NAG Interpolators Requirements for interpolation • 1D monotonic (Hermite) interpolator in time – E01BEF • 1D cubic spline interpolator in space – E01BAF So far so good. How will we get derivatives? • Recall need 𝜃 𝑇 , 𝜃 𝐾 , 𝜃 𝐾𝐾 • Simple approach – use finite differences • Do NOT do this! • NAG interpolation routines can also return exact analytic derivatives – use these 51
- 51. 2nd Example: PDE Solver Before we look at the code: • How will we know whether our model and implementation are correct ... ? Examine the code 52
- 52. Example Nearest Correlation Matrix 53
- 53. 3rd Example: Nearest Correlation Matrix Correlation between assets • We all know what this is ... ? Mathematically, a correlation matrix 𝐶 ∈ ℝ 𝑛×𝑛 is ... 1. Square 2. Symmetric 3. Has ones on diagonal 4. Is positive semi-definite: 𝑥 𝑇 𝐶𝑥 ≥ 0 for all 𝑥 ∈ ℝ 𝑛 54
- 54. 3rd Example: Nearest Correlation Matrix How do we estimate correlations? • Historical data • Parametric methods such as Gaussian Copulas • Try to back it out from options markets Typically 1, 2 and 3 easy enough to ensure • Ensuring positive semi-definite can be tricky 55
- 55. 3rd Example: Nearest Correlation Matrix Historical data • Take time series for several assets and try to estimate correlation • Perhaps time series model with exponential weights? Gaussian copula G (u ) FZ (u ) C 1 (u1 ), , 1 (un ) for u [0,1]n where 𝐹 𝑍 𝑢 = ℙ(𝑍 ≤ 𝑢) and Z~𝑁 𝑛 (0, C). Then set FX ( x) G F1 ( x1 ), , Fn ( xn ) for x R n where 𝐹𝑖 is the marginal distribution of asset 𝑋 𝑖 • Now only need 𝐹𝑖 and 𝐶, but still have to estimate 𝐶 56
- 56. 3rd Example: Nearest Correlation Matrix Infer from options markets • Recent market events yet again highlighted importance of correlation • New developments in market-based approaches • Idea is to combine individual options, options on indexes, and perhaps best-of-two options, to back out correlation structure • Example is Local Correlation Model • Typically have to supply some “primal” input correlation matrix which is then evolved over time and space 57
- 57. 3rd Example: Nearest Correlation Matrix Given the importance of estimating correlations, what happens if estimate not mathematically correct? NAG Library can find the “nearest” correlation matrix to a given square matrix 𝐴 • G02AAF solves the problem min 𝐶 𝐴 − 𝐶 2𝐹 in Frobenius norm • G02ABF incorporates weights min 𝐶 𝑊 1/2 𝐴− 𝐶 𝑊 1/2 2 𝐹 • Weights useful when have more confidence in accuracy of observations for certain assets than for others 58
- 58. 3rd Example: Nearest Correlation Matrix The effect of W:A = 0.4218 0.6557 0.6787 0.6555 0.9157 0.3571 0.7577 0.1712 0.7922 0.8491 0.7431 0.7060 0.9595 0.9340 0.3922 0.0318W = diag([10,10,1,1])W*A*W = Whole rows/cols 42.1761 65.5741 6.7874 6.5548 weighted by 𝑤 𝑖 91.5736 35.7123 7.5774 1.7119 7.9221 8.4913 0.7431 0.7060 Elements weighted by 9.5949 9.3399 0.3922 0.0318 𝑤 𝑖 ∗ 𝑤𝑗 59
- 59. 3rd Example: Nearest Correlation Matrix Can also do dimension reduction (G02AEF) • So-called factor models • Very similar to PCA in regression analysis • Suppose have assets 𝑌1 , ⋯ , 𝑌 𝑛 , an 𝑛 dimensional Brownian motion 𝑊 and an 𝑛 × 𝑛 “correlation” matrix 𝐴 where Yt1 Wt1 Ft A Yt n Wt n • For example, a simple multi-asset model • Assuming one factor (Brownian motion) for each asset, and 𝐴𝐴 𝑇 gives correlation between all factors 60
- 60. 3rd Example: Nearest Correlation Matrix Can use NAG Library to reduce the number of factors • Find a n × 𝑘 matrix D (where 𝑘 < 𝑛) such that Yt1 Wt1 Ft D Yt n Wt k • Crucially, 𝐷𝐷 𝑇 gives a correlation structure as close as possible to the original structure implied by 𝐴 • Can be very useful to reduce complexity and computational cost of some models and applications 61
- 61. 3rd Example: Nearest Correlation Matrix Let’s examine the code 62
- 62. Finally…. Thank you and…… 63
- 63. Finally… Contact support@nag.co.uk to get your free 30-day trial licence key for NAG Library • NAG C Library, Fortran Library, • NAG Toolbox for MATLAB • NAG Library for .NET Visit www.nag.co.uk/numeric/nagandexcel.asp • Many Excel examples available • Tips & tricks, white papers 64