Financial Applications Using Excel Add–in Development in C/C++ 2e +CD

STEVE DALTON is currently head of interest rate options at GFI in London. Steve has a degree in mathematics from Queen Mary College, University of London, and over 20 years experience in interest rate derivatives and IT. He pioneered the use of real-time spreadsheets in the mid 1980s for arbitrage and derivatives pricing. He founded Eigensys Ltd in the late 1980s, which specialises in software and consultancy in this field and in the use of Excel for demanding real-time applications.

Preface to Second Edition xvii Preface to First Edition xix Acknowledgements for the First Edition xxi Acknowledgements for the Second Edition xxiii 1 Introduction 1 1.1 Typographical and code conventions used in this book 1 1.2 What tools and resources are required to write add-ins 2 1.3 To which versions of Excel does this book apply? 5 1.4 The future of Excel: Excel 2007 (Version 12) 5 1.5 About add-ins 7 1.6 Why is this book needed? 8 1.7 How this book is organised 9 1.8 Scope and limitations 10 2 Excel Functionality 11 2.1 Overview of Excel data organisation 11 2.2 A1 versus R1C1 cell references 12 2.3 Cell contents 13 2.4 Worksheet data types and limits 13 2.5 Excel input evaluation 15 2.6 Data type conversion 16 2.7 Strings 23 2.8 Excel Terminology: Active and current 27 2.9 Commands versus functions in Excel 28 2.10 Types of worksheet function 29 2.11 Complex functions and commands 31 2.12 Excel recalculation logic 33 2.13 The Add-in Manager 46 2.14 Loading and unloading add-ins 46 2.15 Paste function dialog 47 2.16 Good spreadsheet design and practice 49 2.17 Problems with very large spreadsheets 54 2.18 Conclusion 54 3 UsingVBA 55 3.1 Opening the VB editor 55 3.2 Using VBA to create new commands 56 3.3 Assigning VBA command macros to control objects in a worksheet 58 3.4 Using VBA to trap Excel events 59 3.5 Using VBA to create new functions 61 3.6 Using VBA as an interface to external DLL add-ins 62 3.7 Excel ranges, VB arrays, SafeArrays, array Variants 80 3.8 Commands versus functions in VBA 86 3.9 Creating VB add-ins (XLA files) 87 3.10 VBA versus C/C++: some basic questions 88 4 Creating a 32-bit Windows (Win32) DLL Using Visual C++ 6.0 or Visual Studio .NET 89 4.1 Windows library basics 89 4.2 DLL basics 89 4.3 DLL memory and multiple DLL instances 90 4.4 Multi-threading 90 4.5 Compiled function names 91 4.6 Function calling conventions: --cdecl,--stdcall,--fastcall 93 4.7 Exporting DLL function names 94 4.8 What you need to start developing add-ins in C/C++ 97 4.9 Creating a DLL using Visual C++ 6.0 98 4.10 Creating a DLL using Visual C++ .NET 2003 103 4.11 Accessing DLL functions from VB 108 4.12 Accessing DLL functions from excel 110 5 Turning DLLs into XLLs: The Add-in Manager Interface 111 5.1 The xlcall32 library and the C API functions 111 5.2 What does the Add-in manager do? 114 5.3 Creating an XLL: The xlAuto interface functions 115 5.4 When and in what order does Excel call the XLL interface functions? 116 5.5 XLL functions called by the Add-in Manager and Excel 117 6 Passing Data Between Excel and the DLL 127 6.1 Handling Excel s internal data structures: C or C++? 127 6.2 How Excel exchanges worksheet data with DLL add-in functions 128 6.3 Defining constant xlopers/xloper12s 144 6.4 A C++ class wrapper for the xloper/xloper12 - cpp-xloper 146 6.5 Converting between xloper/xloper12s and C/C++ data types 154 6.6 Converting between xloper/xloper12 types 154 6.7 Converting between xlopers and variants 155 6.8 Converting between xlopers and xloper12s 159 6.9 Detailed Discussion of xloper types 163 6.10 Initialising xloper/xloper12s 198 6.11 Missing arguments 201 7 Memory Management 203 7.1 Excel stack space limitations 203 7.2 Static add-in memory and multiple Excel instances 204 7.3 Getting Excel to free memory allocated by Excel 205 7.4 Getting Excel to call back the DLL to free DLL-allocated memory 208 7.5 Returning data by modifying arguments in place 211 7.6 Making add-in functions thread safe 212 8 Accessing Excel Functionality using the C API 223 8.1 The Excel 4 macro language (XLM) 223 8.2 The Excel4(), Excel12() C API functions 226 8.3 The Excel4v()/Excel12v() C API functions 233 8.4 What C API functions can the DLL call and when? 236 8.5 Wrapping the C API 238 8.6 Registering and un-registering DLL (XLL) functions 244 8.7 Registering and un-registering DLL (XLL) commands 271 8.8 Functions defined for the C API only 274 8.9 Working with binary names 285 8.10 Workspace information commands and functions 289 8.11 Working with Excel names 316 8.12 Working with Excel menus 326 8.13 Working with toolbars 344 8.14 Working with custom dialog boxes 351 8.15 Trapping events with the C API 356 8.16 Miscellaneous commands and functions 361 8.17 The XLCallVer() C API function 364 9 Miscellaneous Topics 365 9.1 Timing function execution in VBA and C/C++ 365 9.2 Relative performance of VBA, C/C++: Tests and results 369 9.3 Relative performance of C API versus VBA calling from a worksheet cell 372 9.4 Detecting when a worksheet function is called from an Excel dialog 373 9.5 Accessing Excel functionality using COM/OLE automation using C++ 376 9.6 Maintaining large data structures within the DLL 385 9.7 A C++ Excel name class example, xlName 387 9.8 Keeping track of the calling cell of a DLL function 389 9.9 Passing references to Excel worksheet functions 398 9.10 Multi-tasking, Multi-threading and asynchronous calls in DLLs 401 9.11 A background task management class and strategy 406 9.12 How to crash Excel 417 9.13 Add-in Design 419 9.14 Optimisation 433 10 Example Add-ins and Financial Applications 451 10.1 String functions 451 10.2 Statistical functions 463 10.3 Matrix functions eigenvalues and eigenvectors 474 10.4 Interpolation 477 10.5 Lookup and search functions 485 10.6 Financial markets date functions 493 10.7 Building and reading discount curves 502 10.8 Building trees and lattices 505 10.9 Monte Carlo simulation 506 10.10 Calibration 511 10.11 CMS derivative pricing 513 10.12 The SABR stochastic volatility model 519 10.13 Optimising the SABR implementation for CMS derivatives 528 Appendix 1 Contents of the CD ROM 531 Related reading 535 Web Links and Other Resources 537 Index 539