Functions Data
Functions Language
Functions Utility
-
ReportAsync
The Reportasync() function returns the output from a Quintessence report. The function provides the required parameter values as set up in the report configuration. Reportasync()
-
ExecuteScheduleAsync
ExecuteScheduleAsync() works the same way as the ExecuteSchedule() function but calculates in the background (asynchronously from third party applications). This is useful for longer Schedules,
-
IsDefined
The IsDefined() function determines whether an input parameter has a value assigned to it or not. Related functions: IsDate, IsElement, IsNull Syntax IsDefined (Element) Element
-
IsElement
The IsElement() function assesses whether an array contains an element. Related functions: IsDate, IsDefined, IsNull Syntax IsElement (Element,Array) Element Any string or numeric value Array
-
SubArray
The SubArray() function is used to create a new array from a partial area of an existing array. Syntax SubArray (Data Range, Rows, Columns) Data
-
GroupBy
The GroupBy() function groups a range of data into sets and calculates values per set using aggregation functions, for example, SUM, AVERAGE, COUNT etc. GroupBy()
-
Join
The Join() function combines rows from two ranges of data into a single range. This function works in a similar way to the join functionality
-
FilterData
The FilterData() function is used to filter and output a range of information according to specified criteria. Syntax FilterData (Range, Criteria, Columns) Range The range
-
AuditLog
The audit log contains data pertaining to the usage which is channeled through Quintessence by users. The auditlog() function retrieves the records and associated information
-
DataView
When using data functions that return data from the Quintessence database, the results can be displayed in various ways using the ‘View’ parameter. Syntax The
-
DateOffset
The DateOffset() function returns a date that is relative to a stipulated base date. A number is used to indicate how many date offsets to
-
DateSeries
The DateSeries() function returns a range of dates. The frequency determines the sequence of dates to return, for example days, month ends, trading days only,
-
ExecuteSchedule
Schedules are automated workflows that perform tasks on behalf of a user. They can be run from the Quintessence application but also initiated from a
-
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.
-
GetTradingDays
GetTradingDays() lists all dates in a specified date range and indicates whether the day was, or is, a trading day, taking into account weekends and
-
Hierarchy
The Hierarchy() function returns the children related to a parent entity on a specific date. A hierarchy in Quintessence consists of the same type of
-
MetaData
The MetaData() function retrieves information about data in the Quintessence database. For example, MetaData() can be used to list all TimeSeries or reports in the
-
Net Present Value/NPV
NPV stands for Net Present Value, and it is a financial metric used in capital budgeting and investment analysis. The Net Present Value represents the
-
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
-
Relationship.Period
The Relationship.Period() function returns the dates when a relationship existed between a parent (for example, an entity such as a fund) and its children (for
-
RelationshipData
The RelationshipData() function returns data associated with a relationship between an entity and its constituents. Related functions: Relationship, Relationship.Period, TimeSeries Syntax RelationshipData (Entity Code(s), Relationship
-
Report
The Report() function returns the output from a Quintessence report. The function provides the required parameter values as set up in the report configuration. ReportAsync()
-
ReverseHierarchy
The ReverseHierarchy() function returns the levels of parents that are linked to a child on a specific date for a relationship that is common to
-
ReverseRelationship
The ReverseRelationship() function returns the primary entities linked to another entity according to a specified relationship type. For example, the function returns the portfolios to
-
Timeseries
A TimeSeries is a series of points consisting of dates and values, associated with an entity, and stored in the Quintessence database along with other
-
TranslateCode
Entity codes have associated code types. The same entity code might be used in two or more different systems, for example StockExchange1 and StockExchange2. TranslateCode()
-
UpdateCodes
The UpdateCodes() function updates the codes for specified entities and code types in the Quintessence database. If the specified code type already exists for the
-
UpdateRelationship
The UpdateRelationship() function updates a relationship in the Quintessence database, typically a relationship between a fund and its constituents. This could involve adding a new
-
Bottom
The Bottom() function returns the last element of an array. Related functions: Top Syntax Bottom (Array, No of rows) Array The input array No of
-
CaseStatement
The Case statement is an extension to the Quintessence language. The Case statement performs the same functionality as an IF statement, but is easier to
-
Cotan
The Cotan() function returns the cotangent of an angle. Syntax Cotan (number) Number Any numerical value Examples Function Output Cotan(60) 3.124606 Cotan(45) 0.61737 Cotan(120) 1.402283
-
DateAdd
The DateAdd() function adds an interval to a part of a date. Related Functions: DatePart Syntax Dateadd (Part, Interval, Date) Part The date part to
-
DatePart
The DatePart() function returns a part of a date. Related functions: DateAdd Syntax Datepart (Part, Date) Part The part of the date to return. Possible
-
EndsWith
The EndsWith() function evaluates whether an input string ends with a specified set of characters. Related functions: StartsWith Syntax EndsWith (SpecifiedElement, Value) SpecifiedElement The input
-
ExcelDate
Excel stores dates as numeric values. The ExcelDate() function converts an Excel numeric date to a standard date format. This is used during data interchanges
-
ExcelMetaData
The ExcelMetaData() function returns an array containing the names of the worksheets in an Excel workbook. Syntax ExcelMetaData (Filepath, Information) Filepath The full file path
-
FileList
The FileList() function returns the list of files in a directory. Related functions: FileName, FilePath Syntax FileList (FilePath, Recursive search) FilePath A valid file directory
-
FileName
The FileName() function returns the file name portion of a directory path. Related functions: FileList, FilePath Syntax FileName (FilePath) FilePath The full directory path and
-
FilePath
The FilePath() function returns the file path portion of a full directory path, without the file name. Related functions: FileList, FileName Syntax FilePath (FilePathName) FilePathName
-
GetValueAt
The GetValueAt() function returns a value from a specified position in an array. Note: GetValueAt() is an old function that is interchangeable with the Excel
-
IndexOf
The IndexOf() function locates one text string within another and returns the position of the located text (the position is zero-based, i.e. the first position
-
IsDate
The IsDate() function assesses whether an element is a date. Related functions: IsDefined, IsElement, IsNull Syntax IsDate (Element) Element Any input value Note: If the
-
IsDefined
The IsDefined() function determines whether an input parameter has a value assigned to it or not. Related functions: IsDate, IsElement, IsNull Syntax IsDefined (Element) Element
-
IsElement
The IsElement() function assesses whether an array contains an element. Related functions: IsDate, IsDefined, IsNull Syntax IsElement (Element,Array) Element Any string or numeric value Array
-
IsNull
The IsNull() function assesses whether an element has no value assigned to it (has a Null value). Related functions: IsDate, IsDefined, IsElement Syntax IsNull (Element)
-
IsNullOrEmpty
-
Language functions
The Quintessence language functions are useful ‘parser-type’ functions that are available in the Quintessence Application. They can also be used within expressions in Excel. Maths
-
PadLeft
The Padleft() function adds specified characters to the front of a string until it reaches a specified length. Related functions: PadRight Syntax Padleft (String, Length,
-
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,
-
ArrayToString
The ArrayToString() function is used to return a string representation of a range. Delimiters are used to represent the start of rows or columns in
-
Colouring
Colouring The Colouring() function returns the colours of an array of highlighted cells. Related functions: Sieve Syntax Colouring (Range) Range The input range of cells
-
CreateMatrix
The CreateMatrix() function generates a random block of test data. Syntax CreateMatrix (Rows, Columns, Data Generation Pattern, Format, Additional) Rows The number of rows to
-
CsvToArray
CsvToArray The CsvToArray() function converts a block of Csv formatted data to an Array. Syntax CsvToArray (Csv Input, Csv Mapping) Csv Input Csv data used
-
Depivot
The Depivot() function turns a block of data with row and column headers into an array of three columns, where the row headers becomes the
-
FilterData
The FilterData() function is used to filter and output a range of information according to specified criteria. Syntax FilterData (Range, Criteria, Columns) Range The range
-
Fx
The Fx() function allows Excel functions to behave like Quintessence functions. When an Excel array function is passed to the Fx() function, it automatically expands
-
GroupBy
The GroupBy() function groups a range of data into sets and calculates values per set using aggregation functions, for example, SUM, AVERAGE, COUNT etc. GroupBy()
-
ImportCSV
The ImportCSV() function retrieves data from an external CSV (comma delimited) text file. The function allows users to import specific columns that satisfy user-defined criteria.
-
ImportExcel
The ImportExcel() function imports data from one Excel file into another. The function allows users to import specific columns that satisfy user-defined criteria. Related functions:
-
ImportMDX
The ImportMDX() function imports data from a Microsoft MDX cube. Related functions: ImportCSV, ImportExcel, ImportText Syntax ImportMDX () Source Query The query to pass to
-
ImportODBC
The ImportODBC() function imports data from a specified ODBC source into Excel. The function allows users to import specific columns that satisfy user-defined criteria. Related
-
ImportText
The ImportText() function imports data from a Text file into Excel. The function allows users to import specific columns that satisfy user-defined criteria. Related functions:
-
Join
The Join() function combines rows from two ranges of data into a single range. This function works in a similar way to the join functionality
-
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
-
Merge
The Merge() function merges one or more ranges of cells. Syntax Merge () Ranges The ranges of cell to merge Note: Input parameters can consist
-
MultiJoin
The MultiJoin() function combines rows from multiple ranges of data into a single range. This function works in a similar way to the join functionality
-
Null
When a cell in Excel has not been populated, it contains no value. The Null() function returns an array with one element representing a missing
-
Pivot
The Pivot() function is used to arrange and aggregate a range of information into a more readable format. This usually involves creating new columns using