Deriscope

The Excel Derivatives Periscope

Excel Manual

The backbone of Deriscope’s Excel user interface consists of 14 spreadsheet functions.
Due to four of these functions being trivial variations of the others, there remains a total of 10 essential functions.
All these 14 functions can be divided into three groups:

Main Group (3 Functions)

These are functions that deal with derivatives pricing and risk valuation.

Utilities Group (6 Functions)

These are functions that play a non-critical, auxiliary role in data manipulation.

Live Feeds Group (5 Functions)

These are functions specializing in the acquisition of live feeds.

Main Group List (3 Functions)


1) =ds ( arg1, arg2, …, arg30 )
2) =dsi ( arg1, arg2, …, arg30 )
3) =dsv ( arg1, arg2, …, arg30 )

Main Group Details (3 Functions)

1) =ds ( arg1, arg2, …, arg30 )
All arguments beyond the first arg1 are optional.
Each input argument can – in principle – be anything: text, number, boolean or range.
Deriscope will attempt to make sense of the given arguments and if it fails, it will create a special object in memory that contains a useful diagnostic message and will return a text label that links to that object. You may then use the returned text label in order to read the linked message.
While there exist specific rules that govern the formatting constraints of the input arguments, the standard approach is to let the wizard generate the appropriate arguments for a chosen task and paste the complete function in the designated spreadsheet range.
The same applies to reading the message associated with the returned text label of an error object.
Although there exists a spreadsheet formula that takes that text label as input and returns the associated message, the standard approach is to let the wizard present the message upon selection of the cell that contains the text label.

2) =dsi ( arg1, arg2, …, arg30 )
dsi is the “ignore” version of ds.
It does not issue an error if some of the input data make no sense to it.
It processes only the portion of data that forms a consistent input set and ignores all the rest.

3) =dsv ( arg1, arg2, …, arg30 )
dsv is the volatile version of ds.
When Excel is in auto calculation mode, it is recalculated every time a spreadsheet element changes, even if the changed element bears no relation to that function.

Utilities Group List (6 Functions)


1) =dsMegreH ( range1, range2 )
2) =dsMegreV ( range1, range2 )
3) =dsPipe ()
4) =dsReplace ( source, fromRow, toRow, fromColumn, toColumn )
5) =dsSort ( source, keysFirstRow, keysLastRow, keysFirstColumn, keysLastColumn, ascending, keyType )
6) =dsSubRange ( source, fromRow, toRow, fromColumn, toColumn )

Utilities Group Details (6 Functions)

1) =dsMegreH ( range1, range2 )
Creates a data array by merging the contents of its two input ranges in Horizontal fashion.
Both ranges must have the same number of rows.
range1: The first range. Its contents will constitute the left part of the output array.
range2: The second range. Its contents will constitute the right part of the output array.

2) =dsMegreV ( range1, range2 )
Creates a data array by merging the contents of its two input ranges in Vertical fashion.
range1: The first range. Its contents will constitute the top part of the output array.
range2: The second range. Its contents will constitute the bottom part of the output array.

3) =dsPipe ()
Returns the input unmodified.
The only side effect is that Deriscope treats the output as being its own and may thus apply flashing if that output changes.

4) =dsReplace ( source, fromRow, toRow, fromColumn, toColumn )
Creates a data array by replacing the contents of the specified subset of the source range with those of the target range.
source: The range supplying the original data before the replacement takes place.
fromRow: The index of the top row of the data within the source range that should be replaced. Starts with 1.
toRow: The index of the bottom row of the data within the source range that should be replaced. Cannot exceed the number of rows of the source range.
fromColumn: The index of the leftmost column of the data within the source range that should be replaced. Starts with 1.
toColumn: The index of the rightmost column of the data within the source range that should be replaced. Cannot exceed the number of columns of the source range.

5) =dsSort ( source, keysFirstRow, keysLastRow, keysFirstColumn, keysLastColumn, ascending, keyType )
Creates a data array by sorting the source range (which is not altered) according to the referenced keys. The latter must be a single column or row inside the source range.
source: The range supplying the original data that need to be sorted. The existing data in that range are not affected.
keysFirstRow: The index of the top row of the data within the source range that constitute the keys with respect to which the source data are sorted. Starts with 1.
keysLastRow: The index of the bottom row of the data within the source range that constitute the keys with respect to which the source data are sorted. Starts with 1.
keysFirstColumn: The index of the leftmost column of the data within the source range that constitute the keys with respect to which the source data are sorted. Starts with 1.
keysLastColumn: The index of the rightmost column of the data within the source range that constitute the keys with respect to which the source data are sorted. Starts with 1.
ascending: Boolean value. Set TRUE to order the referenced keys in increasing fashion. Set FALSE to order them in decreasing fashion.
keyType: Optional integer from 0 to 4. If missing or 0, the type of sorted keys is implied from first key. Otherwise 1 -> text, 2 -> number, 3 -> boolean, 4 -> date

6) =dsSubRange ( source, fromRow, toRow, fromColumn, toColumn )
Creates a data array out of the contents of the specified subset of the source range.
source: The range supplying the original data.
fromRow: The index of the top row of the data within the source range that should be extracted. Starts with 1.
toRow: The index of the bottom row of the data within the source range that should be extracted. Cannot exceed the number of rows of the source range.
fromColumn: The index of the leftmost column of the data within the source range that should be extracted. Starts with 1.
toColumn: The index of the rightmost column of the data within the source range that should be extracted. Cannot exceed the number of columns of the source range.

Live Feeds Group List (5 Functions)


1) =dsLive ( handle )
2) =dsLivev ( handle )
3) =dsLiveGet ( provider, tickers, fields )
4) =dsLiveGetv ( provider, tickers, fields )
5) = dsLiveStartEngine ( provider, takt, tickers, fields )

Live Feeds Group Details (5 Functions)

1) =dsLive ( handle )
Downloads live feeds and/or displays historical data.
Expects as input the handle name of an object, the type of which derives from the Deriscope type Live Data.
Use the DeriLive ribbon tab for demonstration and more information.
handle: Text containing the handle name of an object, the type of which derives from the Deriscope type Live Data.

2) =dsLivev ( handle )
This is the volatile version of dsLive. When Excel is in auto calculation mode, it is recalculated every time a spreadsheet element changes, even if the changed element bears no relation to that function.

3) =dsLiveGet ( provider, tickers, fields )
Displays live quotes.
On the absense of an engine, the feeds are first fetched from the provider's server before they can be displayed.
Technically this is referred as "synchronous feeds acquisition" and the quotes may appear after a noticable delay if the server connection is slow.
On the absense of an active engine, the feeds are sourced from local data that have been previously acquired by the running engine.
Technically this is referred as "asynchronous feeds acquisition" and the quotes appear instantly.
provider: Case insensitive text defining the live feed provider.
Available are "YF", "AV", "WTD", "BC", "IEX", "IEXTops", "TFX".
tickers: Optional. Text or range defining the ticker symbol or symbols.
If several symbols are specified, these must be supplied as a single column of cells.
If an engine exists, this argument may be left empty to indicate all engine-registered symbols.
fields: Optional. Case insensitive text or range defining the type(s) of the requested quote(s).
If several types are specified, these must be supplied as a single row of cells.
If omitted, the provider-dependent text indicating the last traded price is assumed.

4) =dsLiveGetv ( provider, tickers, fields )
This is the volatile version of dsLiveGet.
When Excel is in auto calculation mode, it is recalculated every time a spreadsheet element changes, even if the changed element bears no relation to that function.

5) = dsLiveStartEngine ( provider, takt, tickers, fields )
Launches a live feeds engine that downloads periodically and asynchronously data from a specified provider.
Use the DeriLive ribbon tab for demonstration and more information.
provider: Case insensitive text defining the live feed provider.
Available are "YF", "AV", "WTD", "BC", "IEX", "IEXTops", "TFX".
takt: Specifies the number of seconds between successive feed updates.
Enter any non-positive value to stop the updates all together.
tickers: Text or range defining the ticker symbol or symbols expected by the live feeds provider.
If several tickers are specified, these must be supplied as a single column of cells.
Handle names are also accepted, as long as their associated objects uniquely define a ticker symbol.
fields: Optional. Case insensitive text or range defining the type(s) of the requested quote(s).
If several types are specified, these must be supplied as a single row of cells.
If omitted, the provider-dependent text indicating the last traded price is assumed.