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.

Related Articles

Quintessence Innovation

Innovation is our top priority

Innovation is our top priority

ESG and the Rise of Responsible Investing

ESG and the Rise of Responsible Investing

Symbology – the heart of a Consolidated Data View

Symbology – the heart of a Consolidated Data View


Contact us to find out how we can assist you or book a demo to experience Quintessence first-hand.

Copyright © 2021 Quintessence
All trademarks are the property of their respective owners. All rights reserved.
Website developed by: