Manipulate your data using Quintessence utility functions
As I have mentioned before, one can use the Quintessence data functions to access all data in an organisation from within Microsoft Excel®. These data functions return blocks of data applicable to the investment and research process, be it the historical and forecast HEPS series of a company or the constituents and weights of a managed portfolio.
Additionally, within Excel, one can also utilise the Quintessence utility functions. These functions operate on and return blocks of data, allowing users to further manipulate data in Excel. The key attribute that differentiates the Quintessence utility functions from the Quintessence data functions, is that the utility functions operate on data, as opposed to returning data from a consolidated source.
We believe these functions should be available in Excel
In fact the Quintessence utility functions are functions that we believe should be available as part of Excel’s function set. A few examples will reinforce this point. In the example below, Column A and Column C contain lists of company codes. As you can imagine, automatically calculating which companies are in both lists would be quite difficult using the standard functions in Excel. Fortunately, Quintessence provides a SetIntersect() function that returns the companies common to both:
Consider the following example, where we have the constituent weights of two portfolios on a specific date. One can imagine a scenario where you would like to see the constituents of both portfolios aligned so as to compare the relative weights.
As you can see, the Join() function allows a side-by-side comparison of these weights.
These are standard SQL functions
Those with some SQL experience will recognise the Join() function. In fact, the Quintessence utility functions encompass all constructs defined by IBM in 1990 for the ANSI SQL standard. What’s more, the utility functions output dynamic blocks of data, as is the case with the data functions. Together with the data functions, they exponentially enhance Excel’s functionality to implement algorithms.
The union of the:
- Quintessence utility functions
- Quintessence data functions
- Standard Excel functions
comprises a powerful functional language that allows users to implement any algorithm in Excel.
Feel free to contact us for more information and to learn about additional Quintessence features.
