Table of Contents

Excel

nanoql's Excel add-in puts the library in worksheet formulas: =nqlFlatForward(A1, 0.03, "Actual365Fixed") builds a curve, and =nqlYieldTermStructureDiscount(A2, DATE(2030,1,1)) reads it. It is an Excel-DNA 1.9 add-in on .NET 10 (net10.0-windows), for 64-bit Excel for Microsoft 365, whose dynamic arrays it spills into. It comes in two builds:

  • nanoql64.xll, the stable API;
  • nanoql-experimental64.xll, the stable API and the experimental one (bindings.md's opt-in: loading it is the consent).

Both carry the same names, so a workbook built on the stable build opens unchanged in the experimental one. Load one of them per Excel process.

The add-in is generated by nanoql-bind from the surface the bindings cross (bindings.md), for the types a desk's tasks need, listed by task. This page gives its rules, and how to install, build, debug and check it.

Installing

A release's nanoql-excel-<version>-win-x64.zip holds the two add-ins, nanoql's LICENSE, NOTICE and THIRD_PARTY_NOTICES.md (with Excel-DNA's and .NET's notices, whose code each .xll carries), a README, and the example workbooks with their conventions file under examples/ (Example workbooks, below).

  • What it needs: 64-bit Excel for Microsoft 365 on Windows x64, and the .NET 10 Desktop Runtime, x64. Without the runtime, the add-in says so when it loads.
  • Unblocking: Excel blocks an add-in downloaded from the internet, signed or not. Tick Unblock in the .xll's Properties, or put its folder in one of Excel's trusted locations (Trust Center > Trusted Locations).
  • Loading: File > Options > Add-ins, Manage: Excel Add-ins, Go, Browse to the .xll; Excel then loads it at each start. Opening the .xll loads it for the session alone.
  • Unsigned add-ins: the add-ins are unsigned. Excel set to "Require Application Add-ins to be signed by Trusted Publisher", which group policy often sets, disables an unsigned .xll without a prompt; a firm that enforces it signs the .xll with its own code-signing certificate.

Example workbooks

Five workbooks in bindings/excel/examples/, and in the release's zip under examples/, show the add-in at work, each on one sheet, its inputs in cells and its results named:

  • curve.xlsx: a Euribor 6M curve bootstrapped from deposits and swaps, read as discounts, zero rates and forwards;
  • swap.xlsx: Euribor and €STR swaps on that curve;
  • bond.xlsx: a five-year fixed-rate bond's NPV, prices and yield;
  • option.xlsx: a European call and put by Black-Scholes, with their Greeks;
  • sofr.xlsx: the USD SOFR swaps of the SOFR example, at QuantLib's MakeOIS defaults and at the market's convention, which its cells spell out on OISRateHelper and MakeOIS, New York's calendar read from the conventions file market.xml beside it.

They call the stable add-in's functions alone. The curve, the swaps and the SOFR trades are the docs' own examples, and give their numbers. Load the add-in, open a workbook and recalculate it (Ctrl+Alt+F9). sofr.xlsx reads market.xml from its own folder, so open it from a folder holding both: one opened straight from a zip is a copy with nothing beside it.

Other .NET add-ins

Excel holds one .NET runtime per process, and the add-ins run on .NET 10, so an add-in on another .NET version fails silently in the same Excel, its functions or nanoql's #NAME?:

Loaded first Then Result
nanoql an add-in on .NET 8 the other add-in's functions #NAME?
an add-in on .NET 8 nanoql nanoql's functions #NAME?
nanoql an add-in on .NET 10 both work
nanoql an add-in compiled to native code (Native AOT) both work

The tool: nanoql-bind excel

In the repository, dotnet run --project src/NanoQL.Bind -c Release -- excel <command> runs it; outside it, the tool installed from NuGet runs generate and build from the defaults it carries, and refuses update, check, package and --test-exports, which work on the repository's own files (bindings.md, the tool):

Command What it does
update writes bindings/excel/functions.json, the experimental build's surface: each function's Excel name, C name, arguments and their kinds, result, category, experimental mark and loop mask; the add-in's own functions; and the functions the completeness check withholds
check regenerates the surface into memory and compares its bytes with the checked-in file; checks its names are distinct regardless of case, none a cell reference, none the add-in's own, none over 255 characters
generate --out <folder> writes a build's generated C#, its surface and its repro templates alone
build --out <folder> generates a build into an Excel-DNA project in <folder>, builds it and packs its .xll there
package --out <folder> builds nanoql's two add-ins and writes the release's zip, nanoql-excel-<version>-win-x64.zip, with the example workbooks, and its symbols zip there

The others take:

  • --experimental: the experimental API beside the stable;
  • --assembly <prefix>=<path>: nanoql's two assemblies under nql by default; see Users' assemblies below;
  • --list <prefix>=<file>|none: see The list below;
  • --roles and --shipped, as the tool's other commands do.

build also takes:

  • --runtime-source <folder>: a local feed holding the runtime package, which a user's build otherwise restores from NuGet;
  • --no-pack: compile only, with no .xll made.

package takes --runtime-source alone: it packs nanoql's release.

In the repository the runtime comes from its own pack:

dotnet pack src/NanoQL.Excel -c Release -o <feed>
dotnet run --project src/NanoQL.Bind -c Release -- excel build --runtime-source <feed> --out <folder> [--experimental]

The build's folder then holds the add-in, nanoql64.xll or nanoql-experimental64.xll, and the notices. The project the tool writes, NanoQL.Excel.Functions, holds only the generated functions and references the runtime package, NanoQL.Excel, at the tool's version. Its .dna lists both assemblies, so the runtime's own functions register beside the generated ones, and the .xll packs nanoql's assemblies inside it.

Names

  • The prefix and the words: nql, then the words run together, QuantLibXL's form:

    • a constructor is the type's name: nqlFlatForward;
    • any other member is the type's and the member's: nqlYieldTermStructureDiscount, nqlInstrumentResultsNPV;
    • a getter drops its get_, an init-only property's with_ is With (nqlEvaluationContextWithFixings), and an operator drops its op_ (nqlDateAddition);
    • a static or extension member goes under its declaring type, a <Header>Functions class keeping its name (nqlBlackFormulaFunctionsBlackFormula).

  • Overloads and collisions: names that meet regardless of case form a group, numbered in a stated order: the first plain, the rest 2, 3, and on. nqlCalendarAdvance is Advance(Date, int, TimeUnit, …), and nqlCalendarAdvance2 is Advance(Date, Period, …). Within a group the order is:

    • the function's own type, by full name;
    • its own members before inherited ones;
    • the declaring type's full name, then the member's metadata token (QuantLib's declaration order, which the port keeps);
    • binds by their engine input set, after the members;
    • draws last.

    A numbered name doesn't say which overload it is: the Function Wizard's argument names and descriptions tell them apart, and functions.json gives each one's arguments.

  • One numbering for every build: names are numbered over the whole surface, the experimental API included, before the stable build and the list apply. A function has one name in both builds and in any user's build, and a type added to the list renumbers nothing.

  • Case: Excel matches names without regard to case and shows the registered case.

  • Optional arguments: trailing optional arguments may be left out (see Values).

  • Categories and help: a function's category is nanoql.<namespace>. Its description and each argument's come from nanoql's XML documentation, with the argument's form ("The calendar (a Calendar handle or its name)"), each cut at a word to 253 characters. IntelliSense in cells isn't packaged; Excel-DNA's standalone IntelliSense add-in reads the registered descriptions.

  • Frozen from 2.0: each release writes bindings/excel/functions.shipped.json (nanoql-bind freeze), every released name, the experimental build's included, with its C name or its family's key.

    • The tool keeps each released name. A new function takes its group's lowest unused number, so adding an overload never renumbers a released one.
    • A removed function's name stays in the file, retired, and is never given to another function, so a workbook calling it gets #NAME?, never another function's result.
    • A released stable name changes only through deprecation (two releases and a year); an experimental function may change or go in any release, its name retired.
    • Before 2.0 nothing is frozen.

The add-in's own functions take a prefix of their own, nqlxl; the tool refuses a generated name equal to one of them, regardless of case:

Function What it gives
nqlxlError(cell) the message of the error a cell's call returned, while the cell shows the error (Errors, below)
nqlxlRepro(cell, format) the calls behind a cell: a vector, or the C# or the Python that makes them (Repro export, below)
nqlxlRecipe(handle) the call that made a handle: its worksheet function and its arguments
nqlxlVersion() nanoql's version and the .NET runtime the add-in runs on
nqlxlDrawsValues(draws) a batch of draws' values: a column, or a draw per row for sequences
nqlxlDrawsWeights(draws) a batch of draws' weights, a column
nqlxlDrawsNext(draws) the generator's snapshot after the batch, from which the next batch draws

The list: what Excel holds

Excel holds the functions of the types on bindings/excel/list.json, each entry a type's full name and its task (a generic type by its definition, for its family). The tool carries it for nanoql's builds. It holds 309 types in 11 tasks:

Task Types Some of them
dates and calendars 16 Date, Period, Calendar, JointCalendar, DayCounter, MakeSchedule, IMM, ECB
conventions and context 6 EvaluationContext, ConventionSet, ConventionsXml, ConventionSnapshot, Currency, IndexFixings
quotes 6 ForwardSwapQuote, ImpliedStdDevQuote, LastFixingQuote
indexes 9 IborIndex, OvernightIndex, SwapIndex, BMAIndex, the inflation indexes
curves 30 FlatForward, DiscountCurve, PiecewiseYieldCurve, the rate helpers, the default and inflation curves
cash flows 23 CashFlows, the legs and coupons, the coupon pricers
instruments 84 swaps, bonds, options, caps and floors, swaptions, credit default swaps and their builders
engines 53 the pricing engines, and every results record a listed function returns
volatility 37 BlackConstantVol, BlackVarianceSurface, the swaption and optionlet volatilities, SABR
models and calibration 19 HullWhite, G2, HestonModel, the calibration helpers, the optimizers
math 26 the interpolations, the normal distribution, the sequence generators
  • What an entry brings:
    • the type's own functions: its constructors, members, statics, binds and draws;
    • the instance members it inherits, each once from its nearest declaration (its own, then its base classes', an interface's only where no class declares it), constructors and statics excepted: a swap brings its legs and fixed rate, a model its parameters;
    • the extension methods on it, such as a model's Calibrate.
  • Excel's filter still applies inside a listed type. It drops:
    • equality and increment operators, and a results record's constructors and with;
    • setters, and members that return nothing but change an object other cells hold (a validation's check is kept);
    • members writing files or reading the clock (ConventionsXml.WriteDefaults, Date.TodaysDate: Excel's own TODAY() serves);
    • an engine's hooks, and members taking a writable span or an in-out argument.
  • The completeness check: a listed function is withheld, to a fixpoint, when no listed function makes a required input of it, an object or an engine, and no lookup by name builds one. functions.json's "withheld" lists each, with the types it lacks. 19 on the list: the bond yields taking a solver, the short-rate models' trees, the swaps a listed engine prices but the list doesn't hold, among them.
  • A family whole: a listed generic type keeps all its constructions. Where the check withholds one, the family's text differs from the tests' build's, and the build's tests fail naming the family and the type its construction lacks: a gap for the list to close.
  • A type made by a lookup: where no listed function makes a type, the convention set's lookup by its record's name may: =nqlConventionSetInterestRateIndex(, "BMAIndex", curve) makes the BMA index.
  • Asking for a type: open an issue naming the type and the task it serves. An entry added is a change that breaks no workbook. An entry removed after a release is a removed function: a stable one under deprecation (two releases and a year), an experimental one in any release, its name retired.
  • A user's own list: --list nql=<file> widens or narrows nanoql's list for a user's build, in the same format; --list nql=none takes nanoql's whole surface, 10,226 functions with the experimental API and 8,008 without. A wider build names every function as nanoql's builds do.
  • The budget: Excel-DNA's loader registers at most 10,000 functions, and one past them is silently missing. The tool counts a build's functions, the generated and the add-in's own, and refuses to pack one over 10,000, naming the count and the largest namespaces. The builds on the list hold 2,652 functions with the experimental API (328 experimental) and 2,324 without, leaving slots for a user's assemblies.

Values

Kind In a cell or an argument Out
date Excel's serial number, which is QuantLib's; a time of day refused a serial number
period text: "6M", "1Y6M"; days, weeks, months and years only text: "6M"
enum its member's name, any case: "ModifiedFollowing", "Call" the name
boolean TRUE or FALSE (1 or 0) TRUE or FALSE
whole number a number with no fraction a number
number, text as such as such; a non-finite number #NUM!
object its handle (Handles, below); a calendar or a day counter also by name a handle
results record a handle a handle, with a function per field: nqlInstrumentResultsNPV(h)
array a row, a column or an array constant, read by rows a spill down a column; objects as handles
matrix a 2-D range, by rows a spill by rows
array of optional elements a range, its shape the array's a spill, none as #N/A
map a range of two columns: keys, values two columns
jagged array a range, a row per inner array a spill, short rows padded with #N/A
tuple a row or a column of its items across a row
array of tuples a range, a row per element and a column per item a row per element
complex number Excel's complex text, "3+4i", as COMPLEX writes it the same, with round-trip digits
loss distribution (not taken) six columns under a header row: x, dx, density, cumulative, excess, average
generator a snapshot's handle a snapshot's handle
draws (not taken) a handle, read through nqlxlDrawsValues, nqlxlDrawsWeights and nqlxlDrawsNext
  • A 1904 workbook's serial numbers are converted at the boundary, 1,462 days, the date system read per call.
  • An argument left out, or an empty cell, takes the parameter's default: none where the default is none, otherwise the default value. So no cell passes none where C# defaults to something else, such as the mixed interpolations' leftConditionValue = 0.
  • A range's empty cells: trailing ones are trimmed, and an empty cell inside a range is refused, so two parallel ranges can't fall out of step. An array of optional elements is the exception: its range is the array, an empty cell none and nothing trimmed. A map's and an array of tuples' trailing empty rows are trimmed.
  • What is refused: a map's duplicate key, and an array of tuples' range of another width, its message naming the form ("a range, a row per element: Key, Curve"); each with #VALUE!.
  • A family's type arguments: a generic type's constructions fold into one function per family, its type arguments first, by name, in any case: =nqlPiecewiseYieldCurve("Discount", "LogLinear", "IterativeBootstrap", date, helpers, dayCounter, ctx). A nested generic's arguments join with underscores. A combination no construction has is refused, its message listing the constructions. In the stable build a family holds its stable constructions alone, and an experimental one is refused, its message naming the experimental build.
  • What feeds back: an Excel error inside a range is refused, as an error argument passes through whole. So a returned none (#N/A), or a jagged spill's padding, fed back into another function's range refuses. A formula can't return an empty cell, so a computed range can't say none.
  • A period in hours or minutes, which a few results carry, shows as "1h": text the period's parser, which reads days, weeks, months and years, doesn't take back.
  • A file's path in a cell must be absolute (nqlConventionsXmlRead's).

Looping over a range

A range where a function takes one value is looped over: the function is called once per element, and the results spill in the range's shape, as Excel lifts its own functions.

=nqlYieldTermStructureDiscount(curve, A3:A12)                   ten discount factors, down a column
=nqlYieldTermStructureForwardRate2(curve, A3:A5, {"1M","3M","6M"}, dayCounter, "Compounded", "Annual")
                                                                a 3 x 3 grid of forward rates' handles
=nqlDepositRateHelper(B1:B3, "3M", 2, "TARGET", "ModifiedFollowing", FALSE, "Actual360", ctx)
                                                                three deposits, one per rate
  • Which arguments loop: every argument of a kind one cell holds: a number, a whole number, a boolean, text, a date, a period, an enum, a complex number, a handle, a snapshot, a bound engine, and their optional forms. An array, matrix, vector, jagged or map argument keeps its range. A family's type arguments don't loop, and a function whose result is itself a range doesn't loop, since Excel spills no array of arrays. A 1 × 1 range in a looped place is its value. functions.json gives each function's mask, a 1 per looping argument.
  • Several ranges pair by Excel's rule: a single row or column repeats, and a position past a shorter range's end is #N/A.
  • Each element is the single call, keyed as it is: three deposits from one formula are the three cells' deposits, and a curve built from them is the same curve.
  • A failed element shows its error in its place, the others their values.
  • Messages by cell:
    • the formula's cell gets the first failed element's message, with its place ("row 2, column 1"), whether the looped call is the formula's value or nested in it (=SUMPRODUCT(nqlYieldTermStructureDiscount(curve, A3:A12), B3:B12));
    • nqlxlError on a spilled cell gives its element's message when the spill shows the looped call element by element, and nothing for an element showing a value;
    • a spill that reorders the elements (INDEX over a reversed SEQUENCE) gives the formula's message;
    • a cell outside the spill keeps its own call's message.
  • Release: each looped object is held by its cell. Clearing the cell releases them all, shrinking its range releases the elements it leaves, and an element whose input changes releases its old object.

Names for calendars and day counters

Text where a Calendar or a DayCounter goes names it in the current convention set, as an empty set argument reads it (The context and the conventions, below):

=nqlDepositRateHelper(0.03, "6M", 2, "TARGET", "ModifiedFollowing", FALSE, "Actual360", ctx)
=nqlJointCalendar("TARGET", "UnitedStates.Settlement")
  • The names: the set's own and its aliases, FpML's codes (EUTA is TARGET, ACT/360 is Actual360). A name is as the set spells it: "target" is #VALUE!.
  • What isn't a name:
    • a name the set doesn't know is #VALUE!, nqlxlError naming it;
    • a handle's text stays a handle, #REF! when this session doesn't hold it;
    • a name where another type goes stays a missing handle, #REF!.
  • Under a desk's conventions (the settings file), a name is the desk's calendar.
  • An array of calendars takes names too.
  • Indexes stay handles: the set's index needs a curve: =nqlConventionSetIborIndex2(set, "Euribor", "6M", curve).

Handles

An object is held by the add-in and shown in its cell as a handle, <Type>#<16 hexadecimal digits>, such as FlatForward#B4D02F06C4DB1663; any cell passes the handle to another function.

  • The key: the call itself:

    • the function's name;
    • each argument's value (numbers by their bits, text, booleans, a missing argument, a range's shape and cells), each handle argument by its own key;
    • the workbook's date system;
    • the convention provider in effect.

    Equal calls give one object, in any cell, sheet or workbook. The 16 digits are the key's SHA-256; an object whose 16 digits meet another's shows 32.

  • A call that reads files (nqlConventionsXmlRead, nqlConventionsXmlRead2) keys their content beside their paths. An edited file gives a new object when its cell recalculates: F2 and Enter on the cell, or Ctrl+Alt+F9 for the whole workbook. Neither function is volatile, so a plain recalculation doesn't reread the file. One content read from two paths is two objects, each reporting its own path.

  • Lifetime: an object lives while some cell holds its handle, through Excel's RTD topics, and is released when the last one goes. An object inside a returned array (a leg's coupons) is keyed by its parent call and its position, and goes with its parent's cell.

  • Saving and reopening: a saved workbook's handle cells recalculate when it opens. Its handles are rebuilt, the same texts as saved, and every later edit finds them.

  • Threads: every generated function is thread-safe, objects are built once per key, and recalculation runs on all of Excel's threads.

  • Across workbooks: a handle referenced from another workbook works while some cell holds it.

Errors

Excel shows When
#VALUE! the arguments are refused: a wrong value or type, an unknown name, an unreadable or malformed file, a feature not supported, a bound engine refused, the add-in's conventions failing to load
#NUM! QuantLib's own checks and failed calculations, anything else, and a non-finite result
#REF! a handle this Excel session doesn't hold
#N/A no value: a null result, an empty collection

  • Excel's own errors pass through: an error from Excel in an argument returns as itself, as Excel's own functions pass it.

  • The message: =nqlxlError(cell) gives the failed call's message, with the parameter it names and the exception's kind, while the cell shows the error:

    =nqlFlatForward(A1, 0.03, A14)         #VALUE!, A14 holding a calendar
=nqlxlError(B5)                        nqlFlatForward: ArgumentException: dayCounter: expected a DayCounter, got a …

  • The codes: each exception takes the C interface's error code first (bindings.md), then the code its Excel error, so the add-in and the bindings classify a failure alike.

The context and the conventions

  • The evaluation context is a handle: =nqlEvaluationContext(date), and a With<Field> per field (nqlEvaluationContextWithFixings), passed to every function that takes one. There is no today default.

  • Conventions through a set:

    • nqlConventionSetQuantLib(), QuantLib's own;
    • nqlConventionsXmlRead(path) and nqlConventionSetWith(set, snapshot), a conventions file's (conventions.md);
    • the presets by name: =nqlConventionSetIborIndex2(set, "Euribor", "6M", curve).

  • An empty set argument reads the current conventions: the add-in's default, or QuantLib's.

  • The settings file: each build reads its own, <add-in name>.settings.json beside the .xll (nanoql64.settings.json, nanoql-experimental64.settings.json), when Excel loads the add-in:

    { "conventions": "desk-conventions.xml", "recordCalls": true }
    • "conventions": a conventions file, relative to the settings file's folder, loaded as the default conventions.
    • "recordCalls": whether each call is recorded for export, on by default.
    • The environment variable NANOQL_CONVENTIONS overrides "conventions", a relative path resolving against the .xll's folder.
    • No worksheet function sets the default conventions: a setting written by one cell and read by others would make a result depend on Excel's calculation order.

  • A failed load never falls back to QuantLib's conventions: a missing or malformed conventions file, an unknown key (a key in another case included) or a value of the wrong type each refuse every lookup on the default conventions. Every function reading them returns #VALUE!, nqlxlError giving the load's error, while a function given an explicit set still works.

  • The provider in effect enters every handle's key: QuantLib's, the loaded file's content, or the failed load.

Bound engines

An engine is bound in a cell with its inputs and given where a member takes one, as the bindings do (bindings.md):

=nqlDiscountingSwapEngineBind(curve)                    a bound engine, every input optional
=nqlMakeVanillaSwapWithPricingEngine(builder, B1)       the builder solves its fixed rate through it

The member's own arguments are placed by role, and the bound inputs fill the rest. An engine of the wrong shape, an input both bound and supplied, or an engine without the member's calculation is refused, #VALUE!. Functions of the caller's own (solvers, integrators, quotes computed by a formula) stay out of 2.0, as in every binding.

Users' assemblies

A user's own assembly built on nanoql gets its worksheet functions in the same add-in as nanoql's, so its objects and nanoql's meet:

nanoql-bind excel build --assembly nql=NanoQL.dll --assembly nql=NanoQL.Extensions.dll --assembly acme=Acme.dll --out x
  • Names: each assembly's functions take its prefix: =acmeExponentialCurve(A1, 0.03, 0.001, A3) builds the user's curve, and nanoql's =nqlYieldTermStructureDiscount2(A4, 5) prices it. The same naming, freezing and budget rules apply as for nanoql's own.
  • The build: the stable API and the user's assemblies, unless --experimental is given. nanoql's list applies to nanoql's assemblies; a user's assembly is generated whole unless --list <prefix>=<file> gives it a list.
  • What the check withholds: a user's function whose required input only a type off nanoql's list makes is withheld as nanoql's are. The tool prints each one with the types it lacks and the remedy: --list nql=<file>, with the type added.
  • The runtime package: a user's build restores the runtime, NanoQL.Excel, from NuGet at the tool's version.
  • One add-in per Excel process: each Excel-DNA add-in loads its assemblies into a context of its own, so two add-ins' objects are different types and can't meet. A desk's add-in holds every assembly its workbooks price with.

Repro export

=nqlxlRepro(cell, format) gives the nanoql calls behind a cell, a line per row:

  • "vector", the default: a test vector in the vector suite's envelope. It holds each call's worksheet function and the Excel values it was given: a range with its shape and its empty cells, a handle as a reference to the call that made it, an object inside a returned array as its parent call's item. Beside them: the provider, the calls' date system, and the conventions file and every file a call read, embedded. The expected outcome is the cell's own: a spill's whole value, a handle's text (its type alone where a file was read), throws for a failed call, null for no value, a non-finite number's #NUM!.
  • "csharp": a C# program making the same calls through nanoql alone. Compiled against NanoQL.dll and NanoQL.Extensions.dll, it prints the cell's outcome as a grid of cells.
  • "python": the same over nanoql's Python package and NumPy.

A looped call's C# and Python loop over its ranges, each element guarded as Excel shows a failed element; a name where a calendar or a day counter goes is the convention set's lookup. The date system is the calls' own: a 1904 workbook's cell exported from a 1900 workbook gives a 1904 vector.

The export refuses, #VALUE! with nqlxlError giving why:

  • a cell whose value isn't its call's outcome: a scaled cell, or a cell edited to a constant;
  • a call given an Excel error: export the cell that gave it;
  • an object inside a returned map or tuple;
  • calls made in workbooks of both date systems;
  • a call reporting a read file's path (nqlConventionSnapshotSources, nqlConventionSetProvenance), which a replay reading the file from a temporary path can't reproduce.

With "recordCalls": false in the settings, a value cell can't export; a handle cell still does, from its handle's recipe.

The replayer runs a folder of vectors through a tests' build, each on its own (a fresh store, the vector's conventions, date system and files), every number within the vector suite's tolerance:

dotnet run --project bindings/excel/replay -c Release -- vectors <folder> <the experimental tests' build> [--reverse]

An export as a test: commit the vector as bindings/excel/tests/repro/<name>.json and the workbook it was exported from as bindings/excel/tests/repro/workbooks/<name>.xlsx, its read files under workbooks/files/. A workbook holds each call in a cell of its sheet Calls and the export in its sheet Repro; a read file's path is built from the workbook's own folder (its name Here), so it reads its files wherever the checkout is. The read files are kept as written, their line endings included (-text in .gitattributes), since the vector embeds their bytes. CI replays every committed vector; the Excel tests make each one's calls again and export them, and render it as C# and run it; the suite inside Excel exports it again from its workbook. Each comparison leaves out the generator's version and the checkout's own path.

Debugging

nanoql's symbols ship apart from the add-ins, in nanoql-excel-<version>-win-x64-symbols.zip: NanoQL.pdb and NanoQL.Extensions.pdb, with nanoql's source embedded. To stop in nanoql's source from a cell in Visual Studio:

  1. Give it the symbols' folder once: Tools > Options > Debugging > Symbols.
  2. Turn on Suppress JIT optimization on module load and turn off Enable Just My Code (Tools > Options > Debugging > General). The release build's code is optimized: without the first, a breakpoint may bind and never stop.
  3. Attach to Excel before the add-in loads (Debug > Attach to Process, Managed (.NET Core) code), or start Excel from Visual Studio with the .xll as its argument.

In the repository, python -I bindings/excel/xltests/stepin.py [--netcoredbg <netcoredbg.exe>] checks the way in on nanoql's Debug build: it builds an add-in over it, nanoql's PDB left apart, attaches netcoredbg 3.2.0 to an Excel of its own, and stops in FlatForward's discount from a cell's call, the generated worksheet function a frame above.

Checking the add-in

Check Command Where
The surface dotnet run --project src/NanoQL.Bind -c Release -- excel check CI
The back end dotnet test --project bindings/NanoQL.Bind.Tests -c Release: names, the list, the fold, the forms' code, the builds CI
The runtime dotnet test --project bindings/excel/NanoQL.Excel.Tests -c Release: the conversions and forms, keys, errors, settings, looping and messages, through a test host standing in for Excel, on the tests' builds; and the example workbooks' formulas, every function they call listed and stable CI
The replay dotnet run --project bindings/excel/replay -c Release -- <trace> <build folder> --floor bindings/excel/replay-floor.json CI
The repro vectors dotnet run --project bindings/excel/replay -c Release -- vectors bindings/excel/tests/repro <build folder>, then with --reverse CI
The package dotnet run --project src/NanoQL.Bind -c Release -- excel package --runtime-source <feed> --out <folder> CI
Inside Excel python -I bindings/excel/xltests/run.py [--python <interpreter>] a machine with Excel
Stepping into nanoql python -I bindings/excel/xltests/stepin.py [--netcoredbg <netcoredbg.exe>] a machine with Excel

  • The tests' builds hold nanoql's whole surface with the list off, compiled with no .xll: --list nql=none --no-pack, with --experimental for one of them. Every listed function is generated in them byte for byte, so what they pass the shipped builds pass, and the kinds the list doesn't reach today stay tested.

  • The replay: the vector suite's recorded trace (bindings.md) through the experimental tests' build's worksheet functions, outside Excel, each result against C#'s, bit for bit. A fact stops at an input no cell gives: NaN, a whole number past 2^53, none where the default isn't none, a period in hours, an empty matrix, an enum value no member has.

  • The suite inside Excel (bindings/excel/xltests/, ExcelDna.Testing on xUnit 2, with its own global.json) runs on the shipped builds, a test build and a user's build, in Excel instances of its own that load the add-ins for their session alone. It checks:

    • loading, each build's registered functions its surface's;
    • a desk's tasks to their bits; bound engines in cells; handles across saving and reopening, and in both date systems; edited conventions files and the settings' failed loads;
    • errors and their messages; 2,000 curves on all of Excel's threads; the forms in real ranges; a user's assembly;
    • looping and its messages by cell, names for calendars and day counters, and objects released with their cells;
    • repro export: a desk's swap exported in each format, the vector replayed, the C# compiled and run, the Python run (with --python, an interpreter with nanoql's Python package and NumPy), each to the cell's bits; every committed vector the build holds exported again from its workbook; the export's refusals;
    • the package's stable add-in, from its zip, priced;
    • the example workbooks, each opened on the stable add-in and recalculated whole, its named results C#'s bits.

    Its wrapper builds the add-ins first. It checks that the run leaves the machine's other Excel processes and Excel's settings as it found them; it puts back Excel's window position, which Excel rewrites when a test closes a second workbook, and says so.