Quintessence Help Centre
-
Quickly access documentation, guides, and reference material for Quintessence features and functionality.
Functions & Calculations:
Utility Functions:
Functions used to manipulate and process data, such as transforming, formatting, or filtering values.
Data Functions:
Functions that retrieve or reference data from available data sources for use in calculations or analysis.
Language Functions:
Functions used to control logic and expressions within the editor when creating formulas or calculations.
Guides & Platform Resources:
Extensibility & Integrations:
Overview of how Quintessence integrates with external applications for seamless data exchange, interaction, and automation.
Product Interfaces:
Overview of Quintessence interfaces and tools for managing projects, data, and other artifacts.
Knowledge Base:
A collection of articles and reference materials providing additional guidance, troubleshooting, and best practices.
ⓘ See all available functions:
Functions Data
Functions Language
Functions Utility
-
ReportAsync
ReportAsync() performs calculations in the background (asynchronously from Excel). This is useful for larger reports, allowing you to continue working in Excel while the report is being generated. Once the data is ready, it is displayed in Excel. Syntax: Remarks: Examples: The examples provided below are based on fictitious data for illustrative purposes only.
-
ExecuteScheduleAsync
ExecuteScheduleAsync() works the same way as the ExecuteSchedule() function, but runs in the background (asynchronously when called from third-party applications). This is useful for longer-running Schedules, as other work can continue while the Schedule executes. Once processing is complete, the resulting data is returned to the third-party application. Syntax: Remarks: Examples:
-
IsDefined
The IsDefined() function determines whether an input parameter has a value assigned to it. Related functions: IsDate, IsElement, IsNull, IsNullOrEmpty Syntax: Examples: The examples provided below are based on fictitious data for illustrative purposes only.
-
IsElement
The IsElement() function checks whether a specified value exists within an array. Related functions: IsDate, IsDefined, IsNull, IsNullOrEmpty Syntax: Examples: The examples provided below are based on fictitious data for illustrative purposes only.
-
SubArray
The SubArray() function is used to create a new array from a selected portion of an existing array. Syntax: Remarks: Examples: The examples provided below are based on fictitious data for illustrative purposes only.
-
GroupBy
The GroupBy() function groups a range of data into sets and calculates values for each set using aggregation functions such as SUM, AVERAGE, and COUNT. Related functions: Pivot Syntax: Remarks: Examples: The examples provided below are based on fictitious data for illustrative purposes only.
-
Join
The Join() function combines rows from two ranges of data into a single range. It works similarly to the join functionality in SQL (Structured Query Language used for querying databases). Related functions: MultiJoin Syntax: Remarks: Examples: The examples provided below are based on fictitious data for illustrative purposes only.
-
FilterData
The FilterData() function filters a specified range of data based on given criteria and returns the matching results. Syntax: Remarks: Examples: The examples provided below are based on fictitious data for illustrative purposes only.
-
AuditLog
The audit log captures data related to usage activity routed through Quintessence by users. The AuditLog() function retrieves records and relevant details for all requests made to Quintessence within a specified date range. Syntax: Remarks: Examples: The examples provided below are based on fictitious data for illustrative purposes only.
-
DateOffset
The DateOffset() function returns a date (or dates) relative to a specified base date. Syntax: Remarks: Examples: The examples provided below are based on fictitious data for illustrative purposes only.
-
DateSeries
The DateSeries() function returns a sequence of dates within a specified range. If trading days are specified, weekends and public holidays (according to the relevant securities exchange or country) are excluded. Syntax: Remarks: Examples:
-
ExecuteSchedule
Schedules are automated workflows that perform tasks on behalf of a user. They can be run directly from the Quintessence application or triggered programmatically using the ExecuteSchedule() function. By providing the required parameters and the name of the Schedule, the process is executed when the function is called. The ExecuteScheduleAsync() function works similarly to ExecuteSchedule(),…
-
FiscalValue
Note: This is a legacy function and will be deprecated soon. Please use TimeSeries() instead.
-
Functions
The Quintessence data functions are available in both the Quintessence Excel® Addin and Quintessence Application. These functions retrieve or upload data from the Quintessence Database. DateOffset DateSeries GetTradingDays Hierarchy MetaData Relationship Relationship.Period RelationshipData Report ReverseHierarchy ReverseRelationship TimeSeries TranslateCode UpdateCodes UpdateRelationship UpdateRelationshipData UpdateTimeSeries UpdateTradingDays UploadData User
-
GetTradingDays
GetTradingDays() returns all dates within a specified range and indicates whether each date is, or was, a trading day, accounting for weekends and public holidays of the relevant securities exchange or country. Syntax: Remarks: Examples: The examples provided below are based on fictitious data for illustrative purposes only.
-
GlobalVariable
GlobalVariable() allows users to access variables configured by the administrator on a global level. These variables can be accessed from the Quintessence editor (from artifacts like Reports, Custom Sources, Custom Functions, Data Feeds, Virtual Feeds and Schedules) and other third-party applications such as Excel. Syntax: Examples:
-
Hierarchy
The Hierarchy() function returns the children associated with a parent entity on a specific date. In Quintessence, a hierarchy consists of the same type of relationship repeated from the parent to the child, from that child to the next, and so on. Related functions: ReverseHierarchy(), Relationship(), ReverseRelationship() Syntax: Remarks: Examples: The examples provided below are based on fictitious…
-
MetaData
The MetaData() function retrieves information about data stored in the Quintessence database. Syntax: Remarks: Examples: The examples provided below are based on fictitious data for illustrative purposes only.
-
PointValue
Note: This is a legacy function and will be deprecated soon. Please use TimeSeries() instead.
-
Relationship
Relationships exist between entities. For example, a portfolio has constituents (a set of instruments). The portfolio is said to be related to these instruments through the relationship type. The Relationship() function returns the entities linked to a primary entity according to a specified relationship type. Related functions: RelationshipData(), Relationship.Period(), Hierarchy(), ReverseHierarchy(), ReverseRelationship(). Syntax: Remarks: Examples: The examples provided below are based on…
-
RelationshipData
The RelationshipData() function returns data associated with a relationship between an entity and its constituents. Related functions: Relationship, Relationship.Period, TimeSeries Suntax: Remarks: Examples: The examples provided below are based on fictitious data for illustrative purposes only.
-
RelationshipPeriod
Relationships exist between entities. For example, a portfolio has constituents (a set of instruments). The portfolio is said to be related to these instruments through the relationship type “Constituent”. The RelationshipPeriod() function returns the relationships that were active between a parent entity (e.g., a fund) and its child entities (e.g., its constituents) for the dates…
-
Report
Report() returns output from a Quintessence report. ReportAsync() runs the report in the background, which is especially useful for larger reports since Excel stays responsive until the data is ready. Remarks: Examples: The examples provided below are based on fictitious data for illustrative purposes only.
-
ReverseHierarchy
The ReverseHierarchy() function returns the parent levels linked to a specific child on a given date, for a relationship that is consistent across all levels. In Quintessence, a hierarchy consists of the same type of relationship repeating from parent to child, from that child to the next child, and so on. Related functions: Hierarchy(), Relationship(), ReverseRelationship(). Syntax: Remarks:…
-
ReverseRelationship
The ReverseRelationship() function returns the primary entities that are linked to a specified entity based on a given relationship type. For example, it can return the portfolios to which a specified constituent is linked. Related functions: RelationshipData(), Relationship.Period(), Hierarchy(), ReverseHierarchy(), Relationship(). Syntax: Remarks: Examples: The examples provided below are based on fictitious data for illustrative purposes only.
-
Timeseries
TimeSeries is a sequence of data points, each consisting of a date and a value, associated with an entity and stored in the Quintessence database along with related metadata. The TimeSeries() function retrieves these records and their associated information for a specified date range. See also Introduction to TimeSeries, Entities Syntax: Remarks: Examples: The examples provided below…
-
TranslateCode
Entity codes are associated with specific code types. The same entity code may be used in multiple systems, such as StockExchange1 and StockExchange2. The TranslateCode() function converts an entity code to its equivalent in another system. Syntax: Remarks: Examples: The examples provided below are based on fictitious data for illustrative purposes only.
-
UpdateCodes
The UpdateCodes() function updates the codes for specified entities and code types in the Quintessence database. Related functions: UpdateRelationship, UpdateRelationshipData, UpdateTimeseries, UpdateTradingDays Syntax: Remarks: Examples: The examples provided below are based on fictitious data for illustrative purposes only.
-
Bottom
The Bottom() function returns the last element of an array, or the last N elements when specified. Related functions: Top Syntax: Remarks: Examples: The examples provided below are based on fictitious data for illustrative purposes only.
-
CaseStatement
The Case statement performs logic similar to an IF or Switch statement but is typically easier to read and maintain. Depending on the value of an element or expression, control is transferred to the first matching when clause. Syntax: Remarks: Examples: The examples provided below are based on fictitious data for illustrative purposes only.
-
Cotan
The Cotan() function returns the cotangent of a given angle. Syntax: Examples: The examples provided below are based on fictitious data for illustrative purposes only.
-
DateAdd
The DateAdd() function adds a specified interval to a part of a given date. Related functions: DatePart Syntax: Examples: The examples provided below are based on fictitious data for illustrative purposes only.
-
DatePart
The DatePart() function returns a specific part of a date, such as the day, month, or year. Related functions: DateAdd Syntax: Examples: The examples provided below are based on fictitious data for illustrative purposes only.
-
Endswith
The Endswith() function evaluates whether an input string ends with a specified sequence of characters. Related functions: Startswith Syntax: Examples: The examples provided below are based on fictitious data for illustrative purposes only.
-
ExcelDate
Excel stores dates as numeric serial values. The ExcelDate() function converts an Excel numeric date into a standard date format. This function is primarily used during data interchange between Quintessence and Excel, ensuring that dates transferred between the two systems are interpreted as dates rather than numeric values. Related functions: TypeCast Syntax: Remarks: Examples: The…
-
Excelmetadata
The Excelmetadata() function returns metadata from an Excel workbook. Currently, it can return an array containing the names of all worksheets in the file. Syntax: Examples: The examples provided below are based on fictitious data for illustrative purposes only.
-
ExportCSV
The ExportCSV() function takes a data block and exports it into a CSV file. This function is only available in the Quintessence Language (for example, Reports). Syntax: Remarks: Examples: The examples provided below are based on fictitious data for illustrative purposes only.
-
ExportExcel
The ExportExcel() function takes a data block and exports it into an Excel workbook. The function can also append worksheets to existing Excel workbooks. This function is only available in the Quintessence Language (for example, Reports). Syntax: Remarks: Examples: The examples provided below are based on fictitious data for illustrative purposes only.
-
FileList
The FileList() function returns a list of files contained in a specified directory. Related functions: FileName, FilePath Syntax: Remarks: Examples: The examples provided below are based on fictitious data for illustrative purposes only.
-
FileName
The FileName() function returns the file name portion of a full directory path. Related functions: FileList, FilePath Syntax: Examples: The examples provided below are based on fictitious data for illustrative purposes only.
-
FilePath
The FilePath() function returns the directory path portion of a full file path, excluding the file name. Related functions: FileList, FileName Syntax: Examples: The examples provided below are based on fictitious data for illustrative purposes only.
-
GetValueAt
The GetValueAt() function returns the value located at a specified row and column position in an array. Syntax: Remarks: Examples: The examples provided below are based on fictitious data for illustrative purposes only.
-
IndexOf
The IndexOf() function locates one text string within another and returns the zero based position of the located text (zero based means the first character of the string is in position 0). Syntax: Remarks: Examples: The examples provided below are based on fictitious data for illustrative purposes only.
-
IsDate
The IsDate() function determines whether a given element represents a valid date. Related functions: IsElement, IsNull, IsDefined, IsNullOrEmpty Syntax: Examples: The examples provided below are based on fictitious data for illustrative purposes only.
-
IsDefined
The IsDefined() function determines whether an input parameter has a value assigned to it. Related functions: IsDate, IsElement, IsNull, IsNullOrEmpty Syntax: Examples: The examples provided below are based on fictitious data for illustrative purposes only.
-
IsElement
The IsElement() function checks whether a specified value exists within an array. Related functions: IsDate, IsDefined, IsNull, IsNullOrEmpty Syntax: Examples: The examples provided below are based on fictitious data for illustrative purposes only.
-
IsNull
The IsNull() function checks whether an element has no value assigned (i.e., it is null). Related functions: IsElement, IsDate, IsDefined, IsNullOrEmpty Syntax: Remarks: Examples: The examples provided below are based on fictitious data for illustrative purposes only.
-
IsNullOrEmpty
The IsNullOrEmpty() function checks whether an element has no value assigned (i.e., it is null) or is an empty value. Unlike IsNull(), this function returns a single TRUE or FALSE value.To evaluate each element in an array, use IsNullOrEmpty() together with Transform(). Related functions: IsElement, IsDate, IsDefined, IsNull Syntax: Examples: The examples provided below are based on…
-
AlignToAxes
AlignToAxes() can be used to transpose only the source data that matches the specified target data. The target data does not have to be contiguous but can contain spaces between rows and columns that do not get overwritten. Syntax: Remarks: Examples: The examples provided below are based on fictitious data for illustrative purposes only.
-
ArrayToString
The ArrayToString() function returns a string representation of a range. Delimiters are used to indicate the start of rows or columns in the output string. Each delimiter can consist of one or more characters. Related functions: StringToArray, SubstituteArray, TransposeArray Syntax: Remarks: Examples: The examples provided below are based on fictitious data for illustrative purposes only.
-
Colouring
Note: This function has been deprecated in version 25.1 and later. Please use formatted reports instead.
-
CreateMatrix
The CreateMatrix() function generates a random block of test data. Syntax: Remarks: Examples: The examples provided below are based on fictitious data for illustrative purposes only.
-
CsvToArray
The CsvToArray() function converts a block of CSV-formatted data into an array. Columns are delimited by commas, and rows are delimited by newline characters. Syntax: Examples: The examples provided below are based on fictitious data for illustrative purposes only.
-
Depivot
The Depivot() function converts a block of data with row and column headers into an array of columns. In the output, the columns are ordered as follows: column headers first, followed by row headers, and then the value columns. This format is useful for performing calculations on the data. Related functions: Pivot Syntax: Remarks: Examples:…
-
FilterData
The FilterData() function filters a specified range of data based on given criteria and returns the matching results. Syntax: Remarks: Examples: The examples provided below are based on fictitious data for illustrative purposes only.
-
Fx
Note: This function will be deprecated after version 25.1. Please use Sparse instead. Legacy documentation is provided below for reference.
-
GroupBy
The GroupBy() function groups a range of data into sets and calculates values for each set using aggregation functions such as SUM, AVERAGE, and COUNT. Related functions: Pivot Syntax: Remarks: Examples: The examples provided below are based on fictitious data for illustrative purposes only.
-
ImportCSV
The ImportCSV() function retrieves data from an external CSV (comma-delimited) file. It allows users to import specific columns that meet user-defined criteria. Related functions: ImportExcel, ImportODBC, ImportText Syntax: Remarks: Examples: The examples provided below are based on fictitious data for illustrative purposes only.
-
ImportCsvText
Similar to CsvToArray, the ImportCsvText() function converts a block of CSV-formatted data into an array. However, this function also allows you to apply filter expressions and specify which columns should be returned. In the source data, columns are delimited by commas, and rows are delimited by newline characters. Syntax: Remarks: Examples: The examples provided below…
-
ImportExcel
The ImportExcel() function imports data from an Excel file. It allows users to import specific columns that meet user-defined criteria. Related functions: ImportCSV, ImportODBC, ImportText Syntax: Remarks: Examples: The examples provided below are based on fictitious data for illustrative purposes only.
-
ImportMDX
Note: This is a legacy function and will be deprecated soon. Please use ImportODBC() instead.
-
ImportODBC
The ImportODBC() function retrieves data from a specified ODBC source. It allows users to request specific columns that meet user-defined criteria. Related functions: ImportCSV, ImportExcel, ImportText Syntax: Remarks: Examples: The examples provided below are based on fictitious data for illustrative purposes only.
-
ImportText
The ImportText() function retrieves data from an external text file. It allows users to import specific columns that meet user-defined criteria. Related functions: ImportCSV, ImportExcel, ImportODBC Syntax: Remarks: Examples: The examples provided below are based on fictitious data for illustrative purposes only.
-
Join
The Join() function combines rows from two ranges of data into a single range. It works similarly to the join functionality in SQL (Structured Query Language used for querying databases). Related functions: MultiJoin Syntax: Remarks: Examples: The examples provided below are based on fictitious data for illustrative purposes only.
-
JsonToArray
JSON (JavaScript object notation) is a syntax for storing and exchanging data. Since the JSON format is text only, it can easily be sent to and from a server and used as a data format by any programming language. The JsonToArray() function converts a block of JSON formatted data to an array. Syntax JsonToArray(JsonInput, JsonMapping)…
-
Merge
The Merge() function combines one or more ranges of cells. This function is only available for use in Excel. Syntax: Remarks: Examples: The examples provided below are based on fictitious data for illustrative purposes only.
-
MultiJoin
The MultiJoin() function combines rows from multiple data ranges into a single range. It works similarly to the JOIN functionality in SQL (Structured Query Language), which is used for querying databases. Related function: Join Syntax: Examples: The examples provided below are based on fictitious data for illustrative purposes only.
-
Null
The Null() function returns an array with one element representing a missing value. It is commonly used to: Syntax: Examples: The examples provided below are based on fictitious data for illustrative purposes only.