In my Introduction to Deriscope – Part 3 I showed you the most basic features of the Deriscope wizard by using it to create the pricing formula of a Stock Option.
Now, I show you several advanced Deriscope features that help you build efficient Excel spreadsheets.
Table of Contents
Deriscope Formula Syntax Rules
Syntax Rule: Several input ranges
How to Display any Object Contents in the Spreadsheet
Use dsMergeH and dsMergeV to merge Input Ranges
Deriscope Formula Syntax Rules
The main spreadsheet formula that Deriscope exposes to Excel is ds(r1, r2, …) where r1, r2, … are ranges containing primarily key-value pairs.
Also available are the dsi and dsv variations.
dsi (stands for dsIgnore) does not return error if non-expected keys are found in the input data.
dsv (stand for dsVolatile) is registered as volatile with Excel, meaning it is recalculated by Excel even if none of the inputs has changed. In that regard it behaves like the built-in Excel function NOW().
Deriscope allows you to compose the input ranges in many different ways, as explained below.
You may paste the complete list by clicking on the Syntax ribbon button:
Color Rules
Before listing the spreadsheet syntax rules, it is important to know the meaning of the colors with which the wizard paints the cells where the generated formulas are pasted according to their content:
Red cells contain Deriscope formulas, i.e. formulas that start with ds, dsi or dsv.
Green cells contain simple links to other cells or ranges.
Blue cells contain user input that are not keys. In other words, they contain the value parts of the key-value pairs.
Black color cells contain either keys or labels that are not part of the input to any function.
Syntax Rule: One input range
The easiest way to build the ds formula is by supplying all input data in one single range, to the extent that this is possible.
This is also the method employed by the wizard when it creates and pastes formulas in the spreadsheet.
Two variations exist:
A) Keys aligned vertically on the left column
B) Keys aligned horizontally on the top row
Syntax Rule: Several input ranges
If part of the input data is shared by many formulas, then you should split the input into several ranges.
The next picture demonstrates how this is done if several Payoff objects that differ on their strike value need to be created.
Type and Function
The ds formula must always specify the evoked Deriscope Function and the Deriscope Type to which the Function belongs.
This can be done by supplying as input a range that contains – among else - the special keys Type= and Function=, as the following example of the creation of an object of type Payoff indicates:
It is possible though to specify the Type and Function directly without their accompanying keys, using the convention that the first argument specifies the Function and the second the Type, as follows:
Lastly, Deriscope allows you to concatenate Type and Function together into one text argument using the format T::F, where T is the Type and F is the Function (syntax taken from C++), as shown below:
Use Clone instead of Create
The previous example can be also implemented by applying the Clone function, which creates a replica object, certain values of which may be optionally altered.
If you don't remember the syntax, you may ask the wizard to generate the formula for you as the demo below shows:
This browser does not support the video element.
How to Display any Object Contents in the Spreadsheet
You will often need to place in the spreadsheet one or more elements of some object.
Deriscope supplies a function called Show that returns a custom defined portion of any given object.
You don't need to remember this function's syntax because the wizard can generate the right formula for you as this video demonstrates:
This browser does not support the video element.
Let me nevertheless quickly explain how the Show function works.
The screenshot below shows the red-colored output of the array formula {=ds("ObjectTools::Show","Reference=",$A$1)} over the range A8:B10.
The shown formula appears in curly brackets because it is pasted as an array formula. If your Excel supports dynamic arrays, the same formula could appear without the curly brackets.
The first argument is the text "ObjectTools::Show" that specifies the evoked type T = ObjectTools and function F = Show through the format T::F, as explained above.
The next two arguments "Reference=" and $A$1 supply the key "Reference=" required by the Show function and its associated value, in this case the value held in cell $A$1. The latter is the handle name &Payoff_A1:1.1 of the object created in cell A1 by the Deriscope formula in that cell.
The output shown with red color over the range A8:B10 consists of the full contents of the &Payoff_A1:1.1 object.
The Show function accepts additional optional arguments that instruct it to return only a specified sub-portion of the referenced object's contents.
For example, the numerical strike value of 0.95 can be returned in isolation by adding the two extra arguments "Key=" and "Strike", as shown below:
It is even possible to do so recursively if it happens that the value associated with the given key is itself an object.
As an example, let us create an object that contains another object, like the Stock Option object &RDSB.LOpt_G1:1.2 created in cell G1 using as input the existing Payoff object &Payoff_D1:1.1 as shown below:
Now, I would like to set up my Show function so that it returns the strike value of 0.95, while I feed as Reference= input only the Stock Option object &RDSB.LOpt_G1:1.2.
This is achieved by supplying the values "Payoff" and "Strike" that specify the path of keys that lead to the final target that needs to be returned:
If the value associated with the referenced key is not a single element or object, but rather an array of other values, the Show function returns the whole array.
As an example, I create an object of type Table2D in cell J1:
The contents of the object &Tbl2DNum_J1:1.1 are shown in the wizard because the cell J1 is selected.
I may now display these contents – which form an array of values – in the spreadsheet by using the array formula {=ds("ObjectTools::Show","Reference=",$J$1)} as shown below:
It is further possible to return a sub-section of that array by specifying the top/bottom rows and left/right columns of the desired subsection.
For example, the array formula {=ds("ObjectTools::Show","Reference=",$J$1,"Key=","2,3,2,4")} returns the sub-section consisting of the rows from 2 to 3 and the columns from 2 to 4:
A single element, such as the number 3.2, can be returned as follows:
Use dsMergeH and dsMergeV to merge Input Ranges
Sometimes the input range expected by ds cannot be split in separate ranges. It must be supplied as one connected range.
Take as example the DividendCurve::Create function that creates an object of type Dividend Curve. This function in the case of discrete dividends expects a table of dividend payments that must be supplied as a single range of 2 columns and one or more rows, as the following image indicates:
As you see, the third input argument is the range A9:B11 that contains two columns containing the dividend payment dates and amounts respectively.
But what can you do if the ranges containing the payment dates and amounts are disconnected?
In such a case, you can use the special Deriscope function dsMergeH – where H stands for Horizontal – that all it does is to merge two given ranges into one single connected range by placing them in a horizontal arrangement, one to the right of the other.
The following image shows how this is done in practice:
Similar and symmetrical to dsMergeH is the function dsMergeV – V for Vertical – that merges two given ranges by using a vertical arrangement, one below the other.
Click on IntroductionToDeriscope-4-Spreadsheet.xlsx to download the spreadsheet produced with the above steps.
Feel free to contact me if you want to share any thoughts with regard to this product or if you want to request any particular features. Contact info and social media links are available at my web site https://www.deriscope.com