NAG and Microsoft Excel - Expand your capabilities
Learn how to maximize Excel's potential with the NAG Library.
One of the major benefits of the NAG Library is its inherent flexibility; it can be used by programmers developing in traditional languages, or by users of modern software packages and programming environments, like Microsoft Excel.
To make using the NAG Library easy, we provide the Library as a Dynamic Link Library (DLL). Despite the relatively simple methods of linking to the Library from Excel, it's important to note that to gain benefit from NAG routines in this manner you'll need to be capable of programming in either Microsoft Visual Basic for Applications (VBA) or Visual Studio for Office (VSTO).
The 'NAG and Microsoft Excel' area of the NAG website has been created as a central repository for those wishing to either:
- Learn about linking NAG functionality to Excel via the NAG Dynamic Link Libraries
- Open, view and experiment with NAG routine examples from within Excel
- Using the NAG Library for .NET in Excel
- Read white papers and technical reports on using NAG in Excel
- Leave feedback for NAG's technical team on your experiences on using NAG in Excel and / or suggest future development ideas
Interactive demonstrations showing the use of NAG routines in Excel
These demos highlight the ease with which Excel-based applications that utilize routines from the NAG Library may be developed. Examples are presented for beginner programmers, and for those that are more experienced.
Basic examples:
- How to compute the Cholesky factorization of a real symmetric positive definite matrix
- Using the NAG Fortran Library (NEW EXAMPLE)
- How to reduce a real m by n matrix to bidiagonal form by an orthogonal transformation
- Using the NAG Fortran Library (NEW EXAMPLE)
- How to compute the singular value decomposition of a real upper or lower bidiagonal matrix
- Using the NAG Fortran Library (NEW EXAMPLE)
- How to compute the maximum likelihood estimates of the parameters of a factor analysis model
- Using the NAG Fortran Library (NEW EXAMPLE)
- How to calculate multivariate probability
- How to find a zero of a function using a reverse communication interface
- How to compute the option price given by the Black-Scholes-Merton formula together with its Greeks
- How to compute the option price given by Heston's stochastic volatility model together with its Greeks (NEW AT MARK 23)
- How to solve a Markowitz Modern Portfolio optimization problem
- How to compute the 2D discrete wavelet transform (NEW AT MARK 23)
- How to perform quantile linear regression (NEW AT MARK 23)
Advanced examples:
- How to compute the Nearest Correlation Matrix (NCM), in the Frobenius norm, or weighted Frobenius norm, and optionally with bounds on the eigenvalues, or with k-factor structure, and how to find eigenvalues for both symmetric and asymmetric matrices (NEW AT MARK 23)
- How to find the global optimum of a multivariate function using multi-level coordinate search algorithm
- How to solve a nonlinear least-squares problem using the sequential quadratic programming (SQP) method
- How to obtain Kaplan-Meier estimates for censored data
- How to perform GARCH time series identification, estimation and forecasting
- How to perform ARIMA time series identification, estimation and forecasting
- How to implement Local Volatility model using Dupire formula and a collection of NAG interpolation routines and a PDE solver (NEW EXAMPLE)
NAG Statistical Add-Ins for Excel
The NAG Statistical Add-Ins contain a considerable number of statistical routines grouped together in one spreadsheet. These include functions for:
- Simple statistics
- Regression analysis
- Time series modelling
- GLMs
- PCA
- Factor, Cluster, and Discriminant Analyses
- Non parametric tests
- And others…
All functions can be called via Excel's function wizard and the spreadsheet is supplied with a NAG menu with utilities for expanding the results and extracting formatted data.
The spreadsheet works with the 32-bit NAG Fortran Library FLDLL234ML with Excel 2003, 2007, and 2010 (32-bit).
The zip file contains the Excel spreadsheet, a Readme file and Windows help file.
If you wish to use the Add-Ins in 64-bit Excel 2010 please contact sales@nag.co.uk.
NAG Survival Analysis for Excel
The NAG Survival Analysis spreadsheet contains functions to:
- Compute Kaplan-Meier estimates of survival probabilities (with plots)
- Compute rank statistics for comparing survival curves
- Fit Cox's proportional hazard model
All functions can be called via Excel's function wizard and the spreadsheet is supplied with a NAG menu with utilities for expanding the results and extracting formatted data.
The spreadsheet works with the 32-bit NAG Fortran Library FLDLL234ML with Excel 2003, 2007, and 2010 (32-bit).
The zip file contains the Excel spreadsheet and a Readme file.
If you wish to use the Add-Ins in 64-bit Excel 2010 please contact sales@nag.co.uk.
If you wish to use any of these examples you need to have the appropriate NAG Library installed. All examples are 32/64 bit compatible. The table below shows NAG Product codes depending on which version of Excel and which type of NAG Library you use.
| NAG Fortran Library for Windows | NAG C Library for Windows | |
| 32-bit Excel | FLDLL234ML | CLW3223DAL |
| 64-bit Excel | FLW6I23DCL | CLW6I23DAL |
To access a trial of these libraries please go to our download page. To run the demonstrations effectively you must have macros enabled in Excel. We suggest you save the file (using right click 'Save As') and then open from the saved location. Before you run a demonstration please refer to the Help and Information buttons within the Excel workbook, where available.The examples are supplied with the appropriate VB headers. If you would like to call other routines from the NAG Library, please download the headers via the hyperlinks listed at the bottom of this page (where applicable, otherwise please contact support).
Additional examples of calling NAG routines from Excel are available for most of the above libraries. For the C Library, these examples are available for download for both CLW3223DAL and CLW6I23DAL. For FLDLL234ML and FLW6I23DCL, once the library has been downloaded and installed, the examples can be found in the Samples/Excel_Examples subdirectory of the library installation directory.
Recorded web demonstrations highlighting NAG in Excel functions show the ease of application development
- Portfolio Optimization Example (using the NAG library from Excel)
- Option Pricing Example (using the NAG library from Excel)
Please click here to view recorded webinar clips highlighting NAG in Excel functions.
Using the NAG Library for .NET in Excel (NEW EXAMPLE)
This archive contains a demo which uses methods from the NAG Library for .NET in an Excel spreadsheet to fit a bicubic spline surface to a set of points in 3D space. More information about the demo can be found in this README (a copy of which is also contained in the archive).
White Paper/Technical Reports
NAG experts and collaborators working at other organisations and learning institutions have written papers and reports about using NAG routines in Excel. Links to various reports are featured below.
- Using the NAG Library to calculate financial option prices in Excel
- Sample Microsoft Excel Projects Illustrating How to Call the NAG C Library
- NAG DLLs and Microsoft Excel and Microsoft Visual Basic for Applications
- How to call NAG functions with Microsoft Access and Excel data
- A step by step guide to using the NAG Fortran Library with Microsoft Excel 2003
- Tech Tip: Calling NAG Routines from Excel
- Tech Tip: Using NAG with Microsoft Excel (32-bit or 64-bit)?
Header files for the NAG Libraries
Visual Basic NAG header files (for use in VBA)
- Header files for 32-bit NAG Fortran Library FLDLL234ML
- Header files for 64-bit NAG Fortran Library FLW6I23DCL
- Header files for 32-bit NAG C Library CLW3223DAL
- Header files for 64-bit NAG C Library CLW6I23DAL
Visual Basic .NET NAG header files (for use in VSTO)
- Header files for 32-bit NAG Fortran Library FLDLL234ML
- Header files for 64-bit NAG Fortran Library FLW6I23DCL
- Header files for 32-bit NAG C Library CLW3223DAL
- Header files for 64-bit NAG C Library CLW6I23DAL
Not found what you're looking for?
If you'd like to talk to us about your requirements for mathematical and statistical functionality to enhance your use of programming using Excel either email us at nagmarketing@nag.co.uk or call us directly.