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.
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
outermost dictionary of the JSON data structure has its upper left cell at B9
root) with the definition of the Reference Date followed by links to the nested data structures for YieldCurves, Instruments,
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
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.
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
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
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
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
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.