Financial Applications using Excel Add-in Development in C/C++, 2/e (Hardcover)

Steve Dalton

  • 出版商: Wiley
  • 出版日期: 2007-09-01
  • 售價: $4,540
  • 貴賓價: 9.5$4,313
  • 語言: 英文
  • 頁數: 584
  • 裝訂: Hardcover
  • ISBN: 0470027975
  • ISBN-13: 9780470027974
  • 相關分類: C++ 程式語言Excel
  • 海外代購書籍(需單獨結帳)

買這商品的人也買了...

商品描述

Description

Financial Applications using Excel Add-in Development in C/C++ is a must-buy book for any serious Excel developer.Excel is the industry standard for financial modelling, providing a number of ways for users to extend the functionality of their own add-ins, including VBA and C/C++. This is the only complete how-to guide and reference book for the creation of high performance add-ins for Excel in C and C++ for users in the finance industry. Steve Dalton explains how to apply Excel add-ins to financial applications with many examples given throughout the book. It also covers the relative strengths and weaknesses of developing add-ins for Excel in VBA versus C/C++, and provides comprehensive code, workbooks and example projects on the accompanying CD-ROM. The impact of Excel 2007’s multi-threaded workbook calculations and large grids on add-in development are fully explored. Financial Applications using Excel Add-in Development in C/C++ features: 
  • Extensive example codes in VBA, C and C++, explaining all the ways in which a developer can achieve their objectives.
  • Example projects that demonstrate, from start to finish, the potential of Excel when powerful add-ins can be easily developed.
  • Develops the readers understanding of the relative strengths and weaknesses of developing add-ins for Excel in VBA versus C/C++.

A CD-ROM with several thousand lines of example code, numerous workbooks, and a number of complete example projects.

Table of Contents

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.

商品描述(中文翻譯)

《使用C/C++開發Excel插件的金融應用》是任何嚴肅的Excel開發人員必讀的書籍。Excel是金融建模的行業標準,為用戶提供了多種擴展其自己插件功能的方式,包括VBA和C/C++。這是唯一一本針對金融行業用戶在C和C++中創建高性能Excel插件的完整指南和參考書。Steve Dalton解釋了如何應用Excel插件於金融應用,並在整本書中提供了許多示例。它還涵蓋了在VBA和C/C++中開發Excel插件的相對優勢和劣勢,並在附帶的CD-ROM中提供了全面的代碼、工作簿和示例項目。全面探討了Excel 2007的多線程工作簿計算和大型網格對插件開發的影響。《使用C/C++開發Excel插件的金融應用》的特點包括:

- 大量的VBA、C和C++示例代碼,解釋開發人員實現目標的各種方式。
- 示範項目從頭到尾展示了Excel在強大插件開發時的潛力。
- 增進讀者對在VBA和C/C++中開發Excel插件的相對優勢和劣勢的理解。

附帶的CD-ROM包含數千行示例代碼、多個工作簿和一些完整的示例項目。

《目錄》

- 第二版前言
- 第一版前言
- 第一版致謝
- 第二版致謝
- 1. 引言
- 1.1 本書使用的排版和代碼慣例
- 1.2 撰寫插件所需的工具和資源
- 1.2.1 VBA宏和插件
- 1.2.2 C/C++ DLL插件
- 1.2.3 可訪問C API和XLL插件的C/C++ DLL
- 1.2.4 C/C++/C#.NET插件
- 1.3 本書適用於哪些版本的Excel
- 1.4 Excel的未來:Excel 2007(版本12)
- 1.4.1 關鍵工作簿變更摘要
- 1.4.2 本書未涵蓋的Excel 2007方面
- 1.4.3 Excel 2007的新功能和限制