How to use the Quintessence Excel Addin
Examples:
Use cases:
- How to calculate cumulative total returns using the Quintessence Excel® Addin functions
- How to build formulas using the Quintessence Excel® Addin functions
- How to create a graphical return comparison using the Quintessence Excel® Addin functions
Introduction
The Quintessence Excel® Addin allows the use of Quintessence functions in Excel. These functions share certain behaviour and properties, including how they output dynamic arrays and how the functions interact with Excel functions.
Excel array functions treat blocks of data as single cells. Parts of the array cannot be edited or moved. The exact size of an array needs to be known for the Excel array functions work. This behaviour is not viable in the Quintessence use cases. When the constituents of a portfolio are requested in Quintessence, it is not known up front how many constituents there are. Additionally, if the portfolio code changes, the number of constituents returned might be different. Therefore, the Quintessence Addin changes the way Excel outputs dynamic arrays of data.
Examples
Example 1. Moving a dynamic array
Consider the following TimeSeries() function in Excel. The function takes the parameter values in Column B, and returns a set of close prices for the specified company (AGL) between two dates:
Note that when an input parameter changes, the size of the returned array can change. Changing the end date from 10 October 2012 to 5 October 2012, for example, returns fewer items:
The array can be manipulated as a block of data, as opposed to a single cell. For example, dragging and dropping the function cell moves the entire array. To drag a cell in Excel, select the cell, hover over the cell until the cursor changes to a symbol with four arrows and then drag the cell to a new location:
Drag the item to a new cell. Note that the entire block moves and the function remains intact:
Example 2. Calculations based on a dynamic array
Consider the Excel Transpose() function. In the following example, the transpose function is given a three-item array as input. The desired outcome is that the input column values are displayed as a row. Using Transpose() with the column array as input only transposes the first cell, which is not the intention:
To make the Transpose function work as desired, the number of items in the input array needs to be known (in this example there are three items), and the following steps need to be performed:
- Type in the Transpose function as shown above
- Including the first cell that contains the function, select a row of cells that number the same as the items in the array:
- Select the function parameter reference “A1:A3”
- Click CTRL + SHIFT + ENTER
The function is now enclosed in curly brackets and the result is as desired:
The same result can be achieved using a Quintessence function, in this case TransposeArray(), without needing to use CTRL + SHIFT + ENTER.
Another way to enable the Excel function Transpose() to behave as desired is to enclose it in the Quintessence Fx() function. The Fx() function causes the Transpose() function to treat its input as an array:
