Brownian Bridge™

Setup

Why XLLs?

A production quant desk does not price options in a Python notebook. It prices them in Excel workbooks connected to C++ pricing libraries. The XLL (Excel Link Library) is the mechanism: a 64-bit Win32 DLL with a specific entry point that Excel loads at startup, registers C++ functions as native worksheet functions, and exposes them in the function wizard like built-in Excel functions.

The alternatives — VBA UDFs, COM add-ins, RTD servers — are either too slow, too complex to deploy, or limited in the types they can handle. XLLs are the industry standard for high-performance Excel integration. Banks use them to expose Black-Scholes pricers, yield curve bootstrappers, and risk engines directly to structurers and traders working in Excel.

What this module builds:

A minimal XLL from the Excel SDK that exports a scalar function (xMultiply2Numbers) and a range-in/range-out function (xVectorFunction).
The xlOper.h utility layer that converts between Excel's XLOPER12 type and C++ std::vector<double> and std::string.
The registration mechanism in xlAutoOpen.

Prerequisites:

64-bit Microsoft Excel (Office 365 or Excel 2016+).
Visual Studio 2019+ with MSVC (C++17).
The Excel SDK (xlcall.h, xlcall.cpp, framework.h, framework.c) — distributed as part of the Microsoft Excel Developer Kit (XDK), available from the Microsoft website.

Platform note: XLLs are Windows-only (Win32 DLL). The code in this module is intentionally not cross-platform. On a quant desk, the production pricing machine runs Windows. This is not a limitation to work around.

Theory

1. What Is an XLOPER12?

The central type in the Excel SDK is XLOPER12 (defined in xlcall.h). It is a tagged union — a C struct that can hold a number, a string, a range (multi-cell array), an error code, or a boolean:

typedef struct xloper12 {
    union {
        double num;              // xltypeNum
        XLWCHAR* str;            // xltypeStr (counted string: str[0] = length)
        BOOL xbool;              // xltypeBool
        int err;                 // xltypeErr
        short int w;             // xltypeInt
        struct { ... } sref;     // xltypeSRef (reference)
        struct {                 // xltypeMulti (2D array)
            LPXLOPER12 lparray;
            RW rows;
            COL columns;
        } array;
        // ... other variants
    } val;
    DWORD xltype;                // tag: xltypeNum, xltypeStr, xltypeMulti, etc.
} XLOPER12;

When Excel calls your function, it passes arguments as LPXLOPER12 (pointer to XLOPER12). Your function inspects the xltype tag to determine what kind of data arrived, then reads the appropriate val field. Your return value is likewise an LPXLOPER12.

Type string codes in xlfRegister tell Excel how to marshal arguments:

Code	Type
`B`	`double` (scalar, by value)
`Q`	`LPXLOPER12` (any Excel type, passed as pointer)
`J`	`int` (32-bit integer)
`C`	`const char*` (ANSI string)

The first character in the type string is the return type; subsequent characters are argument types. BBB means: returns double, takes two double arguments.

2. Memory Management in the XLL

XLL functions run on Excel's call stack. They must not return a pointer to a local variable (dangling) or new-allocated memory that Excel can't free. The SDK provides a temporary memory pool via GetTempMemory():

LPXLOPER12 result = (LPXLOPER12)GetTempMemory(sizeof(XLOPER12));

Memory allocated through GetTempMemory is valid for the duration of the Excel formula evaluation and is automatically freed by FreeAllTempMemory() at the end of the call. This is the correct pattern for returning strings and arrays from XLL functions.

For arrays returned as xltypeMulti, each element of the lparray must also be individually allocated (or pointed into the temp pool). The helper functions in xlOper.h handle this for you.

3. xlAutoOpen: The Registration Entry Point

When Excel loads the XLL, it calls xlAutoOpen(). This is where you register every function you want to expose as a worksheet function:

Excel12f(xlfRegister, 0, 11, &xDLL,
    TempStr12("xMultiply2Numbers"),  // function name in DLL
    TempStr12("BBB"),                // type string: returns B, takes B, B
    TempStr12("xMultiply2Numbers"),  // name shown in Excel
    TempStr12("x, y"),               // argument names
    TempStr12("1"),                  // function type: 1 = worksheet function
    TempStr12("myOwnCppFunctions"),  // category in function wizard
    TempStr12(""),                   // shortcut text (optional)
    TempStr12(""),                   // help topic (optional)
    TempStr12("Multiplies 2 numbers"), // description
    TempStr12(""));                  // argument 1 description

The 11 is the number of arguments after the xlfRegister constant. Getting this count wrong is a silent bug — Excel will load the function but call it with wrong arguments. Always count them.

Implementation

framework.h / framework.c (SDK boilerplate)

These files come from the Microsoft XDK and must not be modified. They provide:

GetTempMemory(size) — allocates from the per-call temp pool
FreeAllTempMemory() — frees the temp pool (called by Excel internally)
TempStr12(wchar_t*) — allocates a counted Unicode string in the temp pool
Excel12f(fn, ...) — variadic wrapper around the Excel12 API call

xlOper.h — C++ / Excel Type Conversions

This header provides helper functions to convert between the C++ world and the XLOPER12 world:

#pragma once
#include "xlcall.h"
#include "framework.h"
#include <string>
#include <vector>
#include <limits>
using namespace std;

// Additional TempStr12 overload accepting std::string
LPXLOPER12 TempStr12(const string str);

// ── Getters (Excel → C++) ───────────────────────────────────────────────────

// Number of rows in an XLOPER12 range (1 if scalar)
size_t getRows(const LPXLOPER12& oper);

// Number of columns in an XLOPER12 range (1 if scalar)
size_t getCols(const LPXLOPER12& oper);

// Read string at position (i, j); returns "" if not a string
string getString(const LPXLOPER12& oper, size_t i = 0, size_t j = 0);

// Read number at position (i, j); returns infinity if not a number
double getNum(const LPXLOPER12& oper, size_t i = 0, size_t j = 0);

// ── Setters (C++ → Excel) ───────────────────────────────────────────────────

// Resize oper to an (rows x cols) multi-cell array, filling with empty strings
void resize(LPXLOPER12& oper, size_t rows, size_t cols);

// Write string into position (i, j) of a resized multi array
void setString(LPXLOPER12& oper, const string& str, size_t i = 0, size_t j = 0);

// Write number into position (i, j) of a resized multi array
void setNum(LPXLOPER12& oper, double num, size_t i = 0, size_t j = 0);

utils.h / utils.cpp — Vector Conversion

// utils.h
#pragma once
#include "xlcall.h"
#include <vector>

// Convert a 1D or 2D Excel range to a flat std::vector<double>
// Elements that are not xltypeNum are skipped (or treated as NaN).
std::vector<double> xlRangeToVector(XLOPER12* range);

// Convert a flat std::vector<double> to an Excel range of given dimensions.
// Returns a pointer to temp-pool memory.
XLOPER12* vectorToXlRange(const std::vector<double>& vec, int rows, int cols);

// utils.cpp
#include "utils.h"
#include "framework.h"
#include <cmath>

std::vector<double> xlRangeToVector(XLOPER12* range) {
    std::vector<double> result;
    if (!range) return result;

    if (range->xltype == xltypeNum) {
        result.push_back(range->val.num);
        return result;
    }
    if (range->xltype != xltypeMulti) return result;

    int n = range->val.array.rows * range->val.array.columns;
    result.reserve(n);
    for (int i = 0; i < n; ++i) {
        const XLOPER12& elem = range->val.array.lparray[i];
        if (elem.xltype == xltypeNum)
            result.push_back(elem.val.num);
        else
            result.push_back(std::numeric_limits<double>::quiet_NaN());
    }
    return result;
}

XLOPER12* vectorToXlRange(const std::vector<double>& vec, int rows, int cols) {
    LPXLOPER12 result = (LPXLOPER12)GetTempMemory(sizeof(XLOPER12));
    result->xltype = xltypeMulti;
    result->val.array.rows    = rows;
    result->val.array.columns = cols;
    result->val.array.lparray =
        (LPXLOPER12)GetTempMemory(rows * cols * sizeof(XLOPER12));

    for (int i = 0; i < rows * cols; ++i) {
        result->val.array.lparray[i].xltype    = xltypeNum;
        result->val.array.lparray[i].val.num   =
            (i < (int)vec.size()) ? vec[i] : 0.0;
    }
    return result;
}

xlExport.cpp — Exported Functions and Registration

#include <windows.h>
#include "utils.h"
#include "xlcall.h"
#include "framework.h"
#include "xlOper.h"

// ── Exported worksheet functions ─────────────────────────────────────────────

// Type string "BBB": returns double, takes double, double
extern "C" __declspec(dllexport)
double xMultiply2Numbers(double x, double y) {
    return x * y;
}

// Type string "QQ": returns XLOPER12*, takes XLOPER12* (range → range)
extern "C" __declspec(dllexport)
XLOPER12* xVectorFunction(XLOPER12* inputRange) {
    std::vector<double> vec = xlRangeToVector(inputRange);
    for (double& v : vec)
        v *= 2.0;
    return vectorToXlRange(vec,
                           inputRange->val.array.rows,
                           inputRange->val.array.columns);
}

// ── DLL entry point ──────────────────────────────────────────────────────────

extern "C" __declspec(dllexport)
int xlAutoOpen(void) {
    XLOPER12 xDLL;
    Excel12f(xlGetName, &xDLL, 0);  // retrieves the full path of this DLL

    // Register xMultiply2Numbers
    Excel12f(xlfRegister, 0, 11,
        (LPXLOPER12)&xDLL,
        (LPXLOPER12)TempStr12(L"xMultiply2Numbers"),
        (LPXLOPER12)TempStr12(L"BBB"),
        (LPXLOPER12)TempStr12(L"xMultiply2Numbers"),
        (LPXLOPER12)TempStr12(L"x, y"),
        (LPXLOPER12)TempStr12(L"1"),
        (LPXLOPER12)TempStr12(L"myOwnCppFunctions"),
        (LPXLOPER12)TempStr12(L""),
        (LPXLOPER12)TempStr12(L""),
        (LPXLOPER12)TempStr12(L"Multiplies 2 numbers"),
        (LPXLOPER12)TempStr12(L""));

    // Register xVectorFunction
    Excel12f(xlfRegister, 0, 11,
        (LPXLOPER12)&xDLL,
        (LPXLOPER12)TempStr12(L"xVectorFunction"),
        (LPXLOPER12)TempStr12(L"QQ"),
        (LPXLOPER12)TempStr12(L"xVectorFunction"),
        (LPXLOPER12)TempStr12(L"inputRange"),
        (LPXLOPER12)TempStr12(L"1"),
        (LPXLOPER12)TempStr12(L"myOwnCppFunctions"),
        (LPXLOPER12)TempStr12(L""),
        (LPXLOPER12)TempStr12(L""),
        (LPXLOPER12)TempStr12(L"Multiplies each element by 2"),
        (LPXLOPER12)TempStr12(L""));

    Excel12f(xlFree, 0, 1, (LPXLOPER12)&xDLL);
    return 1;
}

Exposing the Black-Scholes Pricer

A realistic production function combines the above patterns with the C++ pricing library:

#include "BlackScholes.h"  // from compute/pricing-lib

// Type string: "BBBBBB" — returns double, takes 5 doubles
// Arguments: spot, strike, rate, time, vol
// Call from Excel: =xBSCall(100, 100, 0.05, 1.0, 0.20)
extern "C" __declspec(dllexport)
double xBSCall(double S, double K, double r, double T, double sigma) {
    if (T <= 0.0 || sigma <= 0.0) return -1.0;  // return error sentinel
    return bs_european(S, K, r, T, sigma, true).price;
}

// Registration in xlAutoOpen:
Excel12f(xlfRegister, 0, 11,
    (LPXLOPER12)&xDLL,
    (LPXLOPER12)TempStr12(L"xBSCall"),
    (LPXLOPER12)TempStr12(L"BBBBBB"),
    (LPXLOPER12)TempStr12(L"xBSCall"),
    (LPXLOPER12)TempStr12(L"Spot, Strike, Rate, Maturity, Vol"),
    (LPXLOPER12)TempStr12(L"1"),
    (LPXLOPER12)TempStr12(L"Pricing"),
    (LPXLOPER12)TempStr12(L""),
    (LPXLOPER12)TempStr12(L""),
    (LPXLOPER12)TempStr12(L"Black-Scholes European call price"),
    (LPXLOPER12)TempStr12(L""));

Build Configuration

The XLL is a 64-bit DLL. In Visual Studio:

Project type: Dynamic-Link Library (DLL)
Platform: x64 (must match Excel's bitness — almost always 64-bit today)
Output extension: rename .dll to .xll as a post-build step, or set the linker output filename directly.
Module definition file (.def): optional but prevents C++ name mangling for the exported entry points. The extern "C" decorator on each exported function achieves the same result.
No CRT: optionally link against the static CRT (/MT) to avoid distributing the Visual C++ redistributable.

Post-build command to rename the output:

copy /Y "$(OutDir)$(TargetName).dll" "$(OutDir)$(TargetName).xll"

Validation

Smoke Test

Load the XLL in Excel via File → Options → Add-ins → Excel Add-ins → Browse. Then in any cell:

=xMultiply2Numbers(3, 7)       → 21
=xVectorFunction({1,2,3,4,5})  → {2,4,6,8,10}  (Ctrl+Shift+Enter)
=xBSCall(100, 100, 0.05, 1, 0.2)  → 10.451

If the function returns #NAME?, the DLL was not loaded. If it returns #VALUE!, the type string mismatch means Excel is passing the wrong type.

Debugging

Attach Visual Studio debugger to EXCEL.EXE (Attach to Process). Set a breakpoint in xMultiply2Numbers. When you call the function from a cell, the breakpoint will hit, and you can inspect the arguments in the call stack.

Limitations

Windows only. The Excel C API and the XLL mechanism are Win32-specific. Cross-platform alternatives (Python xlwings, C# ExcelDNA) exist but run outside the Excel process and incur inter-process communication overhead.

No thread safety by default. Excel evaluates multi-threaded recalculation (MTR) since Excel 2007. An XLL function can be declared thread-safe in xlfRegister (argument 5 = "1" for single-threaded, "2" for multi-threaded). If your function accesses shared mutable state (a global pricing surface cache, a random number generator), it must be protected by a mutex. A common production pattern: declare the function multi-threaded, use thread_local storage for per-thread state.

XLOPER12 memory is your responsibility. Memory allocated with new inside an XLL function and returned via XLOPER12 will leak — Excel does not call delete on it. Always use GetTempMemory for return values. For large surfaces returned from a risk function, this means the temp pool must be large enough; configure the pool size in the SDK's framework.c.

The Excel SDK is unmaintained. Microsoft has not updated the Excel C API since Excel 2007 (which introduced the XLOPER12 64-bit type). The SDK headers are stable but the documentation is sparse. For new development on platforms where you control the environment, ExcelDNA (C# XLL wrapper) or direct COM automation may be more practical.

Interview Angle

Junior (L1): What is an XLL and how does it differ from a VBA macro?

An XLL is a native Win32 DLL that runs in-process with Excel. When Excel calls an XLL function, the execution is a direct function call — no marshalling overhead, no cross-process boundary. VBA UDFs run in the VBA interpreter, which is interpreted (not compiled to native code), single-threaded, and limited to VBA-compatible types. A simple Black-Scholes calculation in VBA takes ~10 microseconds; the same function in an XLL takes ~100 nanoseconds — a 100x difference. On a desk with 10,000 live options in a workbook, this is the difference between a recalculation that takes 0.1 seconds and one that takes 10 seconds.

Senior (L2): Explain the memory management contract between an XLL function and Excel. What happens if you new double[n] inside an XLL function and return a pointer to it wrapped in an XLOPER12?

Excel does not call delete[] on memory it did not allocate — it cannot know how the XLL allocated it. The SDK temp pool (GetTempMemory) is freed by Excel after each function evaluation. If you new a buffer and wrap its pointer in an XLOPER12 array, Excel uses the data during the current evaluation but never frees the buffer. Each function call leaks memory. The correct approach: allocate the return array via GetTempMemory(rows * cols * sizeof(XLOPER12)). For large arrays returned frequently, this means the temp pool must be pre-sized appropriately in framework.c (the default is 64 KB; a 1000×1000 double matrix requires 8 MB).

Researcher (L3): How would you implement a thread-safe in-process cache for a calibrated Heston surface inside an XLL that re-calibrates when market data changes?

Declare the worksheet function as multi-threaded ("1" → "2" in the type string). Cache the HestonParams in a std::atomic<std::shared_ptr<HestonSurface>>. The recalibration function (called by a timer or a dedicated cell) acquires an exclusive lock, recalibrates, atomically replaces the shared pointer. Pricing functions acquire a shared (reader) lock or use the atomic read — std::shared_ptr is not itself atomic, but std::atomic<std::shared_ptr<T>> is in C++20. This gives lock-free reads on the pricing path and a single-writer calibration. The alternative — a reader-writer lock (std::shared_mutex) — is also valid but requires lock_shared() on every pricing call, adding 10–20 ns per call in the uncontested case.