Introduction to Deriscope – Part 1: Why Deriscope?
Deriscope is described by … Wikipedia at https://en.wikipedia.org/wiki/fascinatingnewproducts/bestever/deriscope as a "wizard-guided Excel Add-In dedicated to the pricing of Financial Derivatives".
Well, it is a bit early for such a Wikipedia entry given Deriscope's recent birth date of August 2017. But the above description still applies and makes Deriscope's significance dependent on the extent by which Excel is used for pricing Financial Derivatives.
So before answering the "Why Deriscope" question, I should first address the "Why Excel" question.
One may define Financial Derivatives as legal contracts that – perhaps conditionally - bind the signing parties into exchanging traded assets among themselves in the future, whereby the conditions – if any – that trigger the various binding rights and obligations as well as the exact timing of the anticipated exchange(s) are coded in terms of prices of traded assets.
Examples of traded assets are currencies, equity shares, bonds etc.
The word Derivatives means that these contracts somehow derive from the traded assets used in their definition.
Starting with the seminal article of Fischer Black from the University of Chicago and Myron Scholes from M.I.T. in 1973 we now know that not only the derivative contracts derive from the referenced traded assets but also their respective prices do so. In other words the fair (theoretical) price of a derivative contract is a function of the prices of the traded assets referenced inside that contract.
This price dependency represents a strict, mathematically proven fact that has forced all major financial institutions to invest heavily in recruiting high caliber physicists, mathematicians and engineers – known as quants – for the purpose of developing mathematical models that are capable of accurately pricing derivative products.
Table of Contents
Reason 1: Great product scope and analytics reliability at an affordable price
Reason 2: Auto-generation of spreadsheet formulas
Reason 3: Context-based, dynamically generated Documentation
The status today
There exist hundreds – perhaps even thousands – of different types of Financial Derivatives and thousands of different mathematical treatments.
Although many mathematical models are in the public domain, most ready-to-implement blueprints are developed and stay under the watchful eye of the front offices of a few big global banks. Second tier banks, insurances, government institutions and even the middle and back offices of the big global banks have to rely on less accurate mathematical models that are either developed in house or copied over from the public domain.
In all cases, it is desirable that any used mathematical models are integrated with the IT infrastructure of the hosting institution so that the various processes involving prices of derivatives are fully automated.
Due to the inherent difficulty of coding each model directly in the in-house IT system, it is often easier to establish a one-time in-house IT - Excel link and then simply code all pricing models in Excel, because it is much simpler to implement and test a model in Excel than in some in-house IT system.
But even if a model is already implemented in the in-house system, most sophisticated users - such as traders, analysts, risk controllers – often need to run stress tests or benchmark comparisons against other models. Such tasks can be carried out in Excel much faster and more thoroughly than anywhere else. For example, creating tables in Excel by pasting initial formulae over large ranges is a much easier operation than coding a for-loop and the associated reporting logic in some programming language.
A non-Excel environment suffices for repetitive operations such as bookkeeping of standardized derivatives transactions. All other activities almost require a spreadsheet environment, such as Excel.
Now I come to the Why Deriscope question.
The following four arguments apply:
Reason 1: Great product scope and analytics reliability at an affordable price
Doing a Google search for "Derivatives pricing in Excel" will produce several results, ranging from free individual projects to subscription-based solutions delivered by big risk management software providers.
Invariably most free products provide only a few basic Black Scholes type of formulas incapable of pricing anything beyond simple stock options with perhaps a few model switches.
Deriscope instead can handle hundreds of products and models. Comparable or greater product/model scope is available only from big established providers at prices that range into the thousands per year and license.
Deriscope's affordable price is not a sign of weakness. It is afforded by its reliance on the free, open source analytics library QuantLib, which has earned the respect of derivatives professionals through its two decades of existence. Deriscope's numerical results can be trusted exactly because they are produced by QuantLib rather than by some unknown company or individual. They can be also trusted because they have been extensively used in production and thus tested by several Deriscope's clients in front office, trading and risk management desks.
Reason 2: Auto-generation of spreadsheet formulas
Imagine you start with a blank Excel sheet and you want to type in some cell the appropriate formula that calculates the dirty price of a zero bond that has notional 1,000,000 €, maturity 22 July 2018 and yield of 4%.
Your usual course of action would be to click on the Insert Function button of the FORMULAS ribbon tab and search in the long list of functions - shipped with the special financial Add-In you have installed for that purpose – for a name resembling the task you want to achieve, for example something like CalculateZeroBondDirtyPrice. If you cannot remember the exact name, you may have trouble locating the desired item, especially if the Add-In contains hundreds or thousands of functions.
The brief video below shows what the user of the latest QuantLibXL version 1.11 – the official Excel interface of QuantLib – faces in this case.
But even if you locate the wanted item, it is quite likely that the selected function requires a few inputs to be objects referred by text handles that should be produced by special formulas somewhere else in the spreadsheet before they can be used as input to the selected function.
For example, QuantLibXL supplies the function qlBondDirtyPriceFromYield which takes 5 arguments, the first of which is the id (handle name) of an existing bond object. This is how this function looks in Excel's native function wizard:
As you see, you need to provide a valid ObjectId as the first input parameter, an impossible task if you do not have such an id already!
I refer to the problem of putting together all spreadsheet formulas required for producing a certain result as the Syntax problem, because of the following linguistic analogue between formulae and sentences:
Single formula <--> Partial sentence
Input parameters <--> Words
Group of linked formulae <--> Full sentence
Deriscope solves the Syntax problem through its wizard, which is a dedicated Excel taskpane where you can select your choices and order the generation of all respective spreadsheet formulae.
Below you see the wizard where the various selections define a function called Dirty Price applying on an object of type Zero Bond and a Yield of 4%.
Note cell B2 is selected. Clicking on the Go button will generate and paste two formulae in the spreadsheet as shown below:
Cell B2 in particular contains the formula that produces the final result.
Reason 3: Context-based, dynamically generated Documentation
Most Excel financial packages are shipped without any Excel-integrated documentation. If you are not sure about the meaning of some input parameter, you will have to visit the provider's site and search for the respective entry in some big pdf document. Apart from the fact that the found definition cannot reflect the actual context in which the referred input parameter is used, chances are high that it is not 100% at sync with what you see on the spreadsheet.
Deriscope has no documentation pdf documents residing in some server. Instead informational text is dynamically generated within Excel as soon as you make a cell or taskpane selection. Due to its dynamic generation nature, the text is guaranteed to be at perfect sync with the remaining user interface environment.
The next picture shows how the definition of the Yield parameter is displayed at the bottom of the Deriscope taskpane when cell B6 is selected.
Reason 4: User friendly Diagnostics
Even after you have overcome the initial hurdle of building a spreadsheet with all necessary formulae, the devil still lurks around the corner ready to devour your precious time by producing all sorts of errors or unreasonable numerical results. As a matter of fact, the "nasty party" really begins when you start changing the input data in order to analyze and understand the produced results.
It is quite human to slip your finger on the keyboard and enter 32 July 2018 in the cell containing the bond's maturity. If you do so, your financial Add-In will invariably fail to calculate a price, but this does not mean it will also let you know of the reason for its failure. In many cases it will produce a #NUM!, which will be of no help as to what went wrong. You may feel grateful that at least your Excel did not crash (!), but wouldn't it be infinitely better if you were told that the input "32 July 2018" is not a proper date?
Deriscope does exactly that by returning an error text that you can read in the taskpane as soon as you select the cell where the failure occurs.
The next picture shows the informative error message being displayed in the wizard when the error-containing cell B8 is selected.
You may also want to watch the following video tutorial with voice narration that deals with the subject of the current article:
Click on ExcelDerivatives-Part1.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
