# Introduction to Deriscope – Part 4: Spreadsheet

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 **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:

###
Use the function *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:

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