Microsoft, Windows, Excel, and Visual Basic are trademarks or registered trademarks of Microsoft Corporation.


The upload tool is an Excel spreadsheet with Visual Basic macros which produces a text file in JSON format from data defined in the spreadsheet. The resulting text file can be uploaded to the service as described here.

JSON is a lightweight and human-readable data format which can easily be generated and parsed. It consists of a dictionary of key-value pairs where key is a string and value can be either a string, number, boolean, a list of values, or another dictionary of key-value pairs. This allows for nested data structures.

Data in Excel on the other hand is stored in flat, table-like ranges, and in order to produce nested data structures we need commands which control how the data ranges are assembled into a JSON structure. The control commands start with a keyword followed by the worksheet name followed by the address of the upper left cell of the data range (in A1 notation) with | as separator, for instance !ToListOfDicts|QCSetup|B20 tells the upload tool that the data range with upper left cell B20 in worksheet QCSetup should be transformed into a list of dictionaries. The allowed keywords are:

In order to determine the size of a data range when the upper left cell is given the upload tool follows this procedure: starting from the upper left cell, the upload tool crawls in the same row to the right until an empty cell is found. The cell immediately before that empty cell is the upper right cell and its coordinate determines the number of columns in the data range. After that the program starts to scan row-by-row the content between the upper left cell and the upper right cell until a row is found with all the cell content empty. The row immediately before that defines the lower left and lower right cells of the data range.

Please be aware that there are no safeguards implemented in the upload tool which prevent you from circular references or from creating absurdly deep nested data structures.

Description of the Worksheets


This is the control panel of the upload tool. It contains the button which triggers the JSON file generation, as well as the location of the file output in cell B2 (named range FileLocation) which you have to adapt to your installation, and the number of simulation scenarios in cell B4 (named range nbrSims). The outermost dictionary of the JSON data structure has its upper left cell at B9 (named range root) with the definition of the Reference Date followed by links to the nested data structures for YieldCurves, Instruments, TimeSeries etc.

No worries if the locale you are using in Excel does not comply with the ISO date format or the point as decimal separator which are mandatory in the JSON data structure: the upload tool will take care of this. The only exception is the par trade convention for instrument coupons (or spreads or strikes), for instance @par+0.001, where you really have to use the point as decimal separator independent of your locale.


You can define the content of the built-in yield curves here. Since the term structures are stored as zero rates you have to provide a daycount convention and a compounding method, as well as an interpolation method for dates between grid points. For the upload you can either specify zero rates or discount factors (using a list of lists data range with key ZeroRates or DiscountFactors, respectively). The data ranges with the term structures contain tenors in the first column and the zero rates (or discount factors) in the second. Those yield curves for which you do not specify data here, will not be affected when you upload the JSON file to the service.


You can specify the content of the built-in time series here - either by referring with key Data to a list of lists data range containing the historical observations or by a default value with key DefaultValue which is used whenever a historical reset/fixing is needed.

Instrument Types

The following worksheets are for creating instruments of different type. Please refer to the example instruments for valid key-value pair settings, and to this page for an explanation of the fields.


You can define portfolios and their content here - either by referring with key AliasList to a list data range containing the aliases of the instruments in the portfolio or by a Regular Expression search pattern with key SearchPattern.


You can define Stress Scenarios here. The yield curves and FX rates to be shifted are contained in list of dicts data ranges which are referred to by keys YieldCurves and FXRefIndices, respectively. Both keys are mandatory, that is you have to provide an empty list of dicts data range even if you do not want to shift any FX rates or yield curves at all.

For each yield curve or FX rate to be shifted you have to specify the spread type described here. While for FX rates it is sufficient to define the spread by a single number, you have to specify an interpolation type and a term structure of spreads for yield curves. This is achieved by referring to a dict data range for each yield curve to be shifted under key Spread. Please note that in this dictionary the index of the grid point where the spread is to be applied has to be given as string: 0 is the first grid point, -1 the last one.


This worksheet shows how you can produce Simulation Scenarios for the service with a Monte-Carlo engine. Please keep in mind that this is an example with highly simplifying assumptions for illustrative purposes only.

For the scenario generation for yield curves we assume three driving factors per currency, represented by the three Brownian motions dWshort for the short end of the curve, dWlong for the long end, and dWspread driving the spreads within that currency. While dWlong and dWshort are correlated with given correlation coefficient ρ, dWspread is uncorrelated to the other two Brownian motions. For each yield curve i within one currency we then assume the spread on the short end (applied to the first grid point) dSishort and the spread on the long end (applied to the last grid point) of the yield curve dSilong to be given by

dSishort = σshort dWshort + σispread dWspread

dSilong = σlong dWlong + σispread dWspread


E[dWshortdWlong] = ρdt, E[dWshortdWspread] = E[dWlongdWspread] = 0

E[dW2short] = E[dW2long] = E[dW2spread] = dt

The normal yield volatilities for the short and long end, σshort and σlong, the normal spread volatilities for each curve σispread, and the correlation coefficient are taken from the spreadsheet for each currency. The field Timescale in years defines the liquidation horizon of the simulation: for a horizon of five business days we have 5/250 = 0.02. You can switch on and off the scenario generation for each currency with the field Include.

If you switch on FX scenario generation, an uncorrelated geometric Brownian motion is assumed to drive the exchange rate for each currency pair. The corresponding log-normal volatilities are taken from the spreadsheet.

Upload Tool
Worksheet QCSetup in the upload tool

Upload Tool YieldCurves
Worksheet YieldCurves in the upload tool

Upload Tool TimeSeries
Worksheet TimeSeries in the upload tool

Upload Tool Portfolios
Worksheet Portfolios in the upload tool

Upload Tool Scenarios
Worksheet Scenarios in the upload tool

Upload Tool SimScenarios
Worksheet SimScenarios in the upload tool