In this article I show you how to manipulate the formulas created by the wizard in order to build functional Excel spreadsheets.
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 button
and selecting Paste Demo Formulas as shown here
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.
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:
Use Show to display in the spreadsheet any object part
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:
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.
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