Financial Applications using Excel Add-in Development in C/C++

Preface to Second Edition. Preface to First Edition. Acknowledgements for the First Edition. Acknowledgements for the Second Edition. 1 Introduction. 1.1 Typographical and code conventions used in this book. 1.2 What tools and resources are required to write add-ins. 1.2.1 VBA macros and add-ins. 1.2.2 C/C++ DLL add-ins. 1.2.3 C/C++ DLLs that can access the C API and XLL add-ins. 1.2.4 C/C++/C#.NET add-ins. 1.3 To which versions of Excel does this book apply? 1.4 The future of Excel: Excel 2007 (Version 12). 1.4.1 Summary of key workbook changes. 1.4.2 Aspects of Excel 2007 not covered in this book. 1.4.3 Excel 2007 file formats. 1.4.4 Compatibility between Excel 2007 and earlier versions. 1.5 About add-ins. 1.6 Why is this book needed? 1.7 How this book is organised. 1.8 Scope and limitations. 2 Excel Functionality. 2.1 Overview of Excel data organisation. 2.2 A1 versus R1C1 cell references. 2.3 Cell contents. 2.4 Worksheet data types and limits. 2.5 Excel input evaluation. 2.6 Data type conversion. 2.6.1 The unary = operator. 2.6.2 The unary - operator (negation). 2.6.3 Number-arithmetic binary operators: + - /^. 2.6.4 Percentage operator:  . 2.6.5 String concatenation operator: &. 2.6.6 Boolean binary operators: =,<
 , >
,<
 =, >
=,<
 >
. 2.6.7 Conversion of single-cell references. 2.6.8 Conversion of multi-cell range references. 2.6.9 Conversion of defined range names. 2.6.10 Explicit type conversion functions: N(), T(), TEXT(), VALUE(). 2.6.11 Worksheet function argument type conversion. 2.6.12 Operator evaluation precedence. 2.7 Strings. 2.7.1 Length-prepended versus null-terminated strings. 2.7.2 Byte strings versus Unicode strings. 2.7.3 Unmanaged versus managed strings. 2.7.4 Summary of string types used in Excel. 2.7.5 Converting one string type to another. 2.7.6 Hybrid length-counted null-terminated strings. 2.8 Excel Terminology: Active and Current. 2.9 Commands versus functions in Excel. 2.10 Types of worksheet function. 2.10.1 Function purpose and return type. 2.10.2 Array formulae - The Ctrl-Shift-Enter keystroke. 2.10.3 Required, optional and missing arguments and variable argument lists. 2.11 Complex functions and commands. 2.11.1 Data Tables. 2.11.2 Goal Seek and Solver Add-in. 2.12 Excel recalculation logic. 2.12.1 Marking dependents for recalculation. 2.12.2 Triggering functions to be called by Excel - the trigger argument. 2.12.3 Volatile functions. 2.12.4 Cross-worksheet dependencies - Excel 97/2000 versus 2002 and later versions. 2.12.5 User-defined functions (VB Macros) and add-in functions. 2.12.6 Data Table recalculation. 2.12.7 Conditional formatting. 2.12.8 Argument evaluation: IF(), OR(), AND(), CHOOSE()... 2.12.9 Controlling Excel recalculation programmatically. 2.12.10 Forcing Excel to recalculate a workbook or other object. 2.12.11 Using functions in name definitions. 2.12.12 Multi-threaded recalculation. 2.13 The Add-in Manager. 2.14 Loading and unloading add-ins. 2.14.1 Add-in information. 2.15 Paste function dialog. 2.15.1 Function category. 2.15.2 Function name, argument list and description. 2.15.3 Argument construction dialog. 2.16 Good spreadsheet design and practice. 2.16.1 Filename, sheet title and name, version and revision history. 2.16.2 Magic numbers. 2.16.3 Data organisation and design guidelines. 2.16.4 Formula repetition. 2.16.5 Efficient lookups: MATCH(), INDEX() and OFFSET() versus VLOOKUP(). 2.17 Problems with very large spreadsheets. 2.18 Conclusion. 3 UsingVBA. 3.1 Opening the VB editor. 3.2 Using VBA to create new commands. 3.2.1 Recording VBA macro commands. 3.3 Assigning VBA command macros to control objects in a worksheet. 3.4 Using VBA to trap Excel events. 3.5 Using VBA to create new functions. 3.5.1 Function scope. 3.5.2 Declaring VBA functions as volatile. 3.6 Using VBA as an interface to external DLL add-ins. 3.6.1 Declaring DLL functions in VB. 3.6.2 Call-by-reference versus call-by-value. 3.6.3 Converting argument and return data types between VBA and C/C++. 3.6.4 VBA data types and limits. 3.6.5 VB/OLE Currency type. 3.6.6 VB/OLE Bstr Strings. 3.6.7 Passing strings to C/C++ functions from VBA. 3.6.8 Returning strings to VBA from a DLL. 3.6.9 Variant data type. 3.6.10 Variant types supported by VBA. 3.6.11 Variant types that Excel can pass to VBA functions. 3.6.12 User-defined data types in VB. 3.6.13 VB object data type. 3.6.14 Calling XLM functions and commands from VBA: Application.ExecuteExcel4Macro(). 3.6.15 Calling user-defined functions and commands from VBA: Application.Run(). 3.7 Excel ranges, VB arrays, SafeArrays, array Variants. 3.7.1 Declaring VB arrays and passing them back to Excel. 3.7.2 Passing arrays and ranges from Excel to VBA to C/C++. 3.7.3 Converting array Variants to and from C/C++ types. 3.7.4 Passing VB arrays to and from C/C++. 3.8 Commands versus functions in VBA. 3.9 Creating VB add-ins (XLA files). 3.10 VBA versus C/C++: some basic questions. 4 Creating a 32-bit Windows (Win32) DLL Using Visual C++ 6.0 or Visual Studio.NET. 4.1 Windows library basics. 4.2 DLL basics. 4.3 DLL memory and multiple DLL instances. 4.4 Multi-threading. 4.5 Compiled function names. 4.5.1 Name decoration. 4.5.2 The extern "C" declaration. 4.6 Function calling conventions: -cdecl, -stdcall, -fastcall. 4.7 Exporting DLL function names. 4.7.1 The -declspec(dllexport) keyword. 4.7.2 Definition (.DEF) files. 4.7.3 Using a preprocessor linker directive. 4.8 What you need to start developing add-ins in C/C++. 4.9 Creating a DLL using Visual C++ 6.0. 4.9.1 Creating the empty DLL project. 4.9.2 Adding code to the project. 4.9.3 Compiling and debugging the DLL. 4.10 Creating a DLL using Visual C++.NET 2003. 4.10.1 Creating the empty DLL project. 4.10.2 Adding code to the project. 4.10.3 Compiling and debugging the DLL. 4.11 Accessing DLL functions from VB. 4.12 Accessing DLL functions from excel. 5 Turning DLLs into XLLs: The Add-in Manager Interface. 5.1 The Xlcall32 library and the C API functions. 5.2 What does the Add-in manager do? 5.2.1 Loading and unloading installed add-ins. 5.2.2 Active and inactive add-ins. 5.2.3 Deleted add-ins and loading of inactivate add-ins. 5.3 Creating an XLL: The xlAuto interface functions. 5.4 When and in what order does Excel call the XLL interface functions? 5.5 XLL functions called by the Add-in Manager and Excel. 5.5.1 xlAutoOpen. 5.5.2 xlAutoClose. 5.5.3 xlAutoAdd. 5.5.4 xlAutoRemove. 5.5.5 xlAddInManagerInfo (xlAddInManagerInfo12). 5.5.6 xlAutoRegister (xlAutoRegister12). 5.5.7 xlAutoFree (xlAutoFree12). 6 Passing Data Between Excel and the DLL. 6.1 Handling Excel's internal data structures: C OR C++? 6.2 How Excel exchanges worksheet data with DLL add-in functions. 6.2.1 Native C/C++ data types. 6.2.2 Excel floating-point array structures: xl4-array, xl12-array. 6.2.3 The xloper/xloper12 structures. 6.2.4 The xlref/xlref12 structures. 6.2.5 The xlmref/xlmref12 structures. 6.2.6 The oper/oper12 structures. 6.3 Defining constant xlopers/xloper12s. 6.4 A C++ class wrapper for the xloper/xloper12 - cpp-xloper. 6.5 Converting between xloper/xloper12s and C/C++ data types. 6.6 Converting between xloper/xloper12 types. 6.7 Converting between xlopers and variants. 6.8 Converting between xlopers and xloper12s. 6.9 Detailed Discussion of xloper types. 6.9.1 Freeing xloper memory. 6.9.2 Worksheet (floating point) number: xltypeNum. 6.9.3 Length-counted string: xltypeStr. 6.9.4 Excel Boolean: xltypeBool. 6.9.5 Worksheet error value: xltypeErr. 6.9.6 Excel internal integer: xltypeInt. 6.9.7 Array (mixed type): xltypeMulti. 6.9.8 Worksheet cell/range reference: xltypeRef and xltypeSRef. 6.9.9 Empty worksheet cell: xltypeNil. 6.9.10 Worksheet binary name: xltypeBigData. 6.10 Initialising xloper/xloper12s. 6.11 Missing arguments. 7 Memory Management. 7.1 Excel stack space limitations. 7.2 Static add-in memory and multiple Excel instances. 7.3 Getting Excel to free memory allocated by Excel. 7.3.1 Freeing xloper memory within the DLL call. 7.3.2 Freeing Excel-allocated xloper memory returned by the DLL function. 7.3.3 Hiding xloper memory management within a C++ class. 7.4 Getting Excel to call back the DLL to free DLL-allocated memory. 7.5 Returning data by modifying arguments in place. 7.6 Making add-in functions thread safe. 7.6.1 Multi-threaded recalculations (MTR) in Excel 2007 (version 12). 7.6.2 Which of Excel's built-in functions are thread-safe. 7.6.3 Allocating thread-local memory. 7.6.4 Excel's sequencing of calls to xlAutoFree in a multi-threaded system. 7.6.5 Using critical sections with memory shared between threads. 8 Accessing Excel Functionality using the C API. 8.1 The Excel 4 macro language (XLM). 8.1.1 Commands, worksheet functions and macro sheet functions. 8.1.2 Commands that optionally display dialogs - the xlPrompt bit. 8.1.3 Accessing XLM functions from the worksheet using defined names. 8.2 The Excel4(),Excel12() C API functions. 8.2.1 Introduction. 8.2.2 Excel4(), Excel12() return values. 8.2.3 Calling Excel worksheet functions in the DLL using Excel4(), Excel12(). 8.2.4 Calling macro sheet functions from the DLL using Excel4(), Excel12(). 8.2.5 Calling macro sheet commands from the DLL using Excel4()/Excel12(). 8.3 The Excel4v()/Excel12v() C API functions. 8.4 What C API functions can the DLL call and when? 8.5 Wrapping the C API. 8.6 Registering and un-registering DLL (XLL) functions. 8.6.1 The xlfRegister function. 8.6.2 Specifying which category the function should be listed under. 8.6.3 Specifying argument and return types. 8.6.4 Giving functions macro sheet function permissions. 8.6.5 Specifying functions as volatile. 8.6.6 Specifying functions as thread-safe (Excel 2007 only). 8.6.7 Returning values by modifying arguments in place. 8.6.8 The Paste Function dialog (Function Wizard). 8.6.9 Function help parameter to xlfRegister. 8.6.10 Argument help parameters to xlfRegister. 8.6.11 Managing the data needed to register exported functions. 8.6.12 Registering functions with dual interfaces for Excel 2007 and earlier versions. 8.6.13 A class based approach to managing registration data. 8.6.14 Getting and using the function's register ID. 8.6.15 Un-registering a DLL function. 8.7 Registering and un-registering DLL (XLL) commands. 8.7.1 Accessing XLL commands. 8.7.2 Breaking execution of an XLL command. 8.8 Functions defined for the C API only. 8.8.1 Freeing Excel-allocated memory within the DLL: xlFree. 8.8.2 Getting the available stack space: xlStack. 8.8.3 Converting one xloper/xloper12 type to another: xlCoerce. 8.8.4 Setting cell values from a command: xlSet. 8.8.5 Getting the internal ID of a named sheet: xlSheetId. 8.8.6 Getting a sheet name from its internal ID: xlSheetNm. 8.8.7 Yielding processor time and checking for user breaks: xlAbort. 8.8.8 Getting Excel's instance handle: xlGetInst. 8.8.9 Getting the handle of the top-level Excel window: xlGetHwnd. 8.8.10 Getting the path and file name of the DLL: xlGetName. 8.9 Working with binary names. 8.9.1 The xltypeBigData xloper. 8.9.2 Basic operations with binary names. 8.9.3 Creating, deleting and overwriting binary names. 8.9.4 Retrieving binary name data. 8.9.5 Example worksheet functions. 8.10 Workspace information commands and functions. 8.10.1 Setting the application title: xlfAppTitle. 8.10.2 Setting the document window title: xlfWindowTitle. 8.10.3 Getting a reference to the active cell: xlfActiveCell. 8.10.4 Getting a list of all open Excel documents: xlfDocuments. 8.10.5 Information about a cell or a range of cells: xlfGetCell. 8.10.6 Sheet or workbook information: xlfGetDocument. 8.10.7 Getting the formula of a cell: xlfGetFormula. 8.10.8 Getting a cell's comment: xlfGetNote. 8.10.9 Information about a window: xlfGetWindow. 8.10.10 Information about a workbook: xlfGetWorkbook. 8.10.11 Information about the workspace: xlfGetWorkspace. 8.10.12 Information about the selected range or object: xlfSelection. 8.10.13 Getting names of open Excel windows: xlfWindows. 8.10.14 Converting a range reference: xlfFormulaConvert. 8.10.15 Converting text to a reference: xlfTextref. 8.10.16 Converting a reference to text: xlfReftext. 8.10.17 Information about the calling cell or object: xlfCaller. 8.10.18 Information about the calling function type. 8.11 Working with Excel names. 8.11.1 Specifying worksheet names and name scope. 8.11.2 Basic operations with Excel names. 8.11.3 Defining a name on a worksheet: xlcDefineName. 8.11.4 Defining and deleting a name in the DLL: xlfSetName. 8.11.5 Deleting a worksheet name: xlcDeleteName. 8.11.6 Getting the definition of a named range: xlfGetName. 8.11.7 Getting the defined name of a range of cells: xlfGetDef. 8.11.8 Getting a list of named ranges: xlfNames. 8.12 Working with Excel menus. 8.12.1 Menu bars and ID numbers and menu and command specifiers. 8.12.2 Short-cut (context) menu groups. 8.12.3 Getting information about a menu bar: xlfGetBar. 8.12.4 Creating a new menu bar or restoring a default bar: xlfAddBar. 8.12.5 Adding a menu or sub-menu: xlfAddMenu. 8.12.6 Adding a command to a menu: xlfAddCommand. 8.12.7 Displaying a custom menu bar: xlfShowBar. 8.12.8 Adding/removing a check mark on a menu command: xlfCheckCommand. 8.12.9 Enabling/disabling a custom command or menu: xlfEnableCommand. 8.12.10 Changing a menu command name: xlfRenameCommand. 8.12.11 Deleting a command from a menu: xlfDeleteCommand. 8.12.12 Deleting a custom menu: xlfDeleteMenu. 8.12.13 Deleting a custom menu bar: xlfDeleteBar. 8.13 Working with toolbars. 8.13.1 Getting information about a toolbar: xlfGetToolbar. 8.13.2 Getting information about a tool button on a toolbar: xlfGetTool. 8.13.3 Creating a new toolbar: xlfAddToolbar. 8.13.4 Adding buttons to a toolbar: xlcAddTool. 8.13.5 Assigning/removing a command on a tool: xlcAssignToTool. 8.13.6 Enabling/disabling a button on a toolbar: xlfEnableTool. 8.13.7 Moving/copying a command between toolbars: xlcMoveTool. 8.13.8 Showing a toolbar button as pressed: xlfPressTool. 8.13.9 Displaying or hiding a toolbar: xlcShowToolbar. 8.13.10 Resetting a built-in toolbar: xlfResetToolbar. 8.13.11 Deleting a button from a toolbar: xlcDeleteTool. 8.13.12 Deleting a custom toolbar: xlfDeleteToolbar. 8.14 Working with custom dialog boxes. 8.14.1 Displaying an alert dialog box: xlcAlert. 8.14.2 Displaying a custom dialog box: xlfDialogBox. 8.14.3 Restricting user input to dialog boxes: xlcDisableInput. 8.15 Trapping events with the C API. 8.15.1 Trapping a DDE data update event: xlcOnData. 8.15.2 Trapping a double-click event: xlcOnDoubleclick. 8.15.3 Trapping a worksheet data entry event: xlcOnEntry. 8.15.4 Trapping a keyboard event: xlcOnKey. 8.15.5 Trapping a recalculation event: xlcOnRecalc. 8.15.6 Trapping a window selection event: xlcOnWindow. 8.15.7 Trapping a system clock event: xlcOnTime. 8.16 Miscellaneous commands and functions. 8.16.1 Disabling screen updating during command execution: xlcEcho. 8.16.2 Displaying text in the status bar: xlcMessage. 8.16.3 Evaluating a cell formula: xlfEvaluate. 8.16.4 Calling user-defined functions from an XLL or DLL: xlUDF. 8.16.5 Calling user-defined commands from an XLL or DLL: xlcRun. 8.17 The XLCallVer() C API function. 9 Miscellaneous Topics. 9.1 Timing function execution in VBA and C/C++. 9.2 Relative performance of VBA, C/C++: Tests and results. 9.2.1 Conclusion of test results. 9.3 Relative performance of C API versus VBA calling from a worksheet cell. 9.4 Detecting when a worksheet function is called from an Excel dialog. 9.4.1 Detecting when a worksheet function is called from the Paste Function dialog (Function Wizard). 9.4.2 Detecting when a worksheet function is called from the Search and Replace dialog. 9.4.3 Detecting when a worksheet function is called from either the Search and Replace or Paste Function dialogs. 9.5 Accessing Excel functionality using COM/OLE automation using C++. 9.5.1 Initialising and un-initialising COM. 9.5.2 Getting Excel to recalculate worksheets using COM. 9.5.3 Calling user-defined commands using COM. 9.5.4 Calling user-defined functions using COM. 9.5.5 Calling XLM functions using COM. 9.5.6 Calling worksheet functions using COM. 9.6 Maintaining large data structures within the DLL. 9.7 A C++ Excel name class example, xlName. 9.8 Keeping track of the calling cell of a DLL function. 9.8.1 Generating a unique name. 9.8.2 Obtaining the internal name of the calling cell. 9.8.3 Naming the calling cell. 9.8.4 Internal XLL name housekeeping. 9.9 Passing references to Excel worksheet functions. 9.9.1 Data references. 9.9.2 Function references. 9.10 Multi-tasking, Multi-threading and asynchronous calls in DLLS. 9.10.1 Setting up timed calls to DLL commands: xlcOnTime. 9.10.2 Starting and stopping threads from within a DLL. 9.10.3 Calling the C API from a DLL-created thread. 9.11 A background task management class and strategy. 9.11.1 Requirements. 9.11.2 Communication between Excel and a background thread. 9.11.3 The software components needed. 9.11.4 Imposing restrictions on the worksheet function. 9.11.5 Organising the task list. 9.11.6 Creating, deleting, suspending, resuming the thread. 9.11.7 The task processing loop. 9.11.8 The task interface and main functions. 9.11.9 The polling command. 9.11.10 Configuring and controlling the background thread. 9.11.11 Other possible background thread applications and strategies. 9.12 How to crash Excel. 9.13 Add-in Design. 9.13.1 Separating interface code from core function code. 9.13.2 Controlling error propagation. 9.13.3 Making add-in behaviour Excel version-sensitive and backwards-compatible. 9.13.4 Version-dependent workbook recalculation results. 9.14 Optimisation. 9.14.1 Low level code optimisation. 9.14.2 VBA code optimisation. 9.14.3 Excel calculation optimisation. 10 Example Add-ins and Financial Applications. 10.1 String functions. 10.2 Statistical functions. 10.2.1 Pseudo-random number generation. 10.2.2 Generating random samples from the normal distribution. 10.2.3 Generating correlated random samples. 10.2.4 Quasi-random number sequences. 10.2.5 The normal distribution. 10.3 Matrix functions - eigenvalues and eigenvectors. 10.4 Interpolation. 10.4.1 Linear interpolation. 10.4.2 Bilinear interpolation. 10.4.3 Cubic splines. 10.5 Lookup and search functions. 10.6 Financial markets date functions. 10.7 Building and reading discount curves. 10.8 Building trees and lattices. 10.9 Monte carlo simulation. 10.9.1 Using Excel and VBA only. 10.9.2 Using Excel and C/C++ only. 10.9.3 Using worksheet functions only. 10.10 Calibration. 10.11 CMS derivative pricing. 10.12 The SABR stochastic volatility model. 10.13 Optimising the SABR implementation for CMS derivatives. Appendix 1. Contents of the CD ROM. Related Reading. Web Links and Other Resources. Index.