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

Hardcover (Print)
Used and New from Other Sellers
Used and New from Other Sellers
from $43.70
Usually ships in 1-2 business days
(Save 67%)
Other sellers (Hardcover)
  • All (14) from $43.70   
  • New (7) from $54.60   
  • Used (7) from $43.70   


"If you're looking to take advantage of the performance benefit from calling C/C++ code from Excel then this book should be the first and last one that you buy. For the second edition Steve has catalogued the changes needed to work with Excel 2007 and given new examples covering the SABR stochastic volatility model and CMS derivative pricing."
—Dr. Mike Staunton, London Business School, Wilmott columnist

"This book is for anyone who wants to do any application development in Excel. Even for an old hand at Excel development such as myself, a brief skim through reveals valuable nuggets of information. Delving deeper into the text, richer veins are easily found. This book is destined to become an essential reference on Excel development."
—Dr. Les Clew low, Principal, Lucama Group Ltd

"Programming Excel add-ins using the C API can be complex and difficult. Steve has done a masterful job of demystifying the process. After reading this book you’ll be creating XLLs for all purposes with complete confidence. Highly recommended."
—Rob Bovey, MCSE, MCSD, Excel MVP, President, Application Professionals

Read More Show Less

Product Details

  • ISBN-13: 9780470027974
  • Publisher: Wiley
  • Publication date: 9/24/2007
  • Series: Wiley Finance Series, #424
  • Edition description: REV
  • Edition number: 2
  • Pages: 584
  • Product dimensions: 6.93 (w) x 9.90 (h) x 1.56 (d)

Meet the Author

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.

Read More Show Less

Table of Contents

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

Read More Show Less

Customer Reviews

Be the first to write a review
( 0 )
Rating Distribution

5 Star


4 Star


3 Star


2 Star


1 Star


Your Rating:

Your Name: Create a Pen Name or

Barnes & Review Rules

Our reader reviews allow you to share your comments on titles you liked, or didn't, with others. By submitting an online review, you are representing to Barnes & that all information contained in your review is original and accurate in all respects, and that the submission of such content by you and the posting of such content by Barnes & does not and will not violate the rights of any third party. Please follow the rules below to help ensure that your review can be posted.

Reviews by Our Customers Under the Age of 13

We highly value and respect everyone's opinion concerning the titles we offer. However, we cannot allow persons under the age of 13 to have accounts at or to post customer reviews. Please see our Terms of Use for more details.

What to exclude from your review:

Please do not write about reviews, commentary, or information posted on the product page. If you see any errors in the information on the product page, please send us an email.

Reviews should not contain any of the following:

  • - HTML tags, profanity, obscenities, vulgarities, or comments that defame anyone
  • - Time-sensitive information such as tour dates, signings, lectures, etc.
  • - Single-word reviews. Other people will read your review to discover why you liked or didn't like the title. Be descriptive.
  • - Comments focusing on the author or that may ruin the ending for others
  • - Phone numbers, addresses, URLs
  • - Pricing and availability information or alternative ordering information
  • - Advertisements or commercial solicitation


  • - By submitting a review, you grant to Barnes & and its sublicensees the royalty-free, perpetual, irrevocable right and license to use the review in accordance with the Barnes & Terms of Use.
  • - Barnes & reserves the right not to post any review -- particularly those that do not follow the terms and conditions of these Rules. Barnes & also reserves the right to remove any review at any time without notice.
  • - See Terms of Use for other conditions and disclaimers.
Search for Products You'd Like to Recommend

Recommend other products that relate to your review. Just search for them below and share!

Create a Pen Name

Your Pen Name is your unique identity on It will appear on the reviews you write and other website activities. Your Pen Name cannot be edited, changed or deleted once submitted.

Your Pen Name can be any combination of alphanumeric characters (plus - and _), and must be at least two characters long.

Continue Anonymously

    If you find inappropriate content, please report it to Barnes & Noble
    Why is this product inappropriate?
    Comments (optional)