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 relationship that repeats from the parent to the child, from the child to the next child etc.
Related functions: ReverseHierarchy, Relationship, ReverseRelationship
Syntax
Hierarchy (Entity Code, Relationship, Levels, IAAD, Projection, Headers, Source)
| Entity Codes |
An entity is any element in the Quintessence database that has data associated with it and is identified by a code, for example, instruments, portfolios, countries or managers.
The Hierarchy() function requires an entity code that is at the root of a hierarchy, in other words, the highest level parent in a hierarchy of the same type of relationship.
|
| Relationship | The relationship between the child and parent entities. For example “constituent”. |
| Levels | Which levels to display (the first level is 0). |
| IAAD | The information ‘as at’ date. Relationships defined after that date will not be shown. |
| Projection | The projection of the hierarchy. Possible options: none, parents, leaves. |
| Headers | Whether to include headers in the output or not. Possible options: none, row, column or both. |
| Source | The source of the data. Examples of sources: MarketDataProvider2, PortfolioAdministrator1, MarketDataProvider1 etc. |
| ShowError | If ShowError is set to TRUE, a standard Excel error message will be displayed if the function fails. |
Note:
Input parameters can consist of Quintessence functions. This includes functions that return ranges in cases where a parameter value can be a range.
Examples
Example 1. Return ABCStockExchange’s 5-tier classification hierarchy
Example 2. Display specific levels of the ABCStockExchange’s 5-tier classification hierarchy
Example 3. Use projection to ensure that the leaf nodes are at the same level
Note:
How your system administrator configured Quintessence determines which function parameters are required. In most implementations, administrators configure functions so that certain parameters default to specific values when left blank.
Example 1. Return ABCStockExchange’s 5-tier classification hierarchy
| Function |
| =Hierarchy(“ABCStockExchange 5 tier classification”,”ICB Classification”) |
| Output | |||||
| ABCStockExchange 5 Tier Classification | Basic Materials | Basic Resources | Forestry & Paper | Forestry | YRK |
| ABCStockExchange 5 Tier Classification | Basic Materials | Basic Resources | Forestry & Paper | Paper | MND |
| ABCStockExchange 5 Tier Classification | Basic Materials | Basic Resources | Industrial Metals | Aluminum | HLM |
| ABCStockExchange 5 Tier Classification | Basic Materials | Basic Resources | Industrial Metals | Steel | BSS |
| ABCStockExchange 5 Tier Classification | Basic Materials | Basic Resources | Industrial Metals | Steel | KIO |
| ABCStockExchange 5 Tier Classification | Basic Materials | Basic Resources | Mining | General Mining | AGL |
| ABCStockExchange 5 Tier Classification | Basic Materials | Basic Resources | Mining | General Mining | ARI |
| ABCStockExchange 5 Tier Classification | Consumer Goods | Food & Beverage | Food Producers | Farming & Fishing | RCL |
| ABCStockExchange 5 Tier Classification | Consumer Goods | Food & Beverage | Food Producers | Food Products | AVI |
Example 2. Display specific levels of the ABCStockExchange’s 5-tier classification hierarchy
| Function |
| =Hierarchy(“ABCStockExchange 5 tier classification”,”ICB Classification”,”1,3,5″) |
| Output | ||
| Basic Materials | Forestry & Paper | YRK |
| Basic Materials | Forestry & Paper | MND |
| Basic Materials | Industrial Metals | HLM |
| Basic Materials | Industrial Metals | BSS |
| Basic Materials | Industrial Metals | KIO |
| Basic Materials | Mining | AGL |
| Basic Materials | Mining | ARI |
| Consumer Goods | Food Producers | RCL |
| Consumer Goods | Food Producers | AVI |
Example 3. Use projection to ensure that the leaf nodes are at the same level
Projection creates balanced output. Consider the following output that has ‘leaf’ nodes at different levels:
| Output | |||
| ABCStockExchange 5 Tier Classification | Forestry & Paper | Forestry | YRK |
| ABCStockExchange 5 Tier Classification | Forestry & Paper | Paper | MND |
| ABCStockExchange 5 Tier Classification | Industrial Metals | HLM | |
| ABCStockExchange 5 Tier Classification | Industrial Metals | Steel | BSS |
| ABCStockExchange 5 Tier Classification | Industrial Metals | KIO | |
Selecting “parents” for projection carries the parent value forward such that the leaf nodes are at the same level:
| Output | |||
| ABCStockExchange 5 Tier Classification | Forestry & Paper | Forestry | YRK |
| ABCStockExchange 5 Tier Classification | Forestry & Paper | Paper | MND |
| ABCStockExchange 5 Tier Classification | Industrial Metals | Industrial Metals | HLM |
| ABCStockExchange 5 Tier Classification | Industrial Metals | Steel | BSS |
| ABCStockExchange 5 Tier Classification | Industrial Metals | Industrial Metals | KIO |
Selecting “leaves” for projection carries the leaf node forward such that the leaf nodes are at the same level:
| Output | |||
| ABCStockExchange 5 Tier Classification | Forestry & Paper | Forestry | YRK |
| ABCStockExchange 5 Tier Classification | Forestry & Paper | Paper | MND |
| ABCStockExchange 5 Tier Classification | Industrial Metals | HLM | HLM |
| ABCStockExchange 5 Tier Classification | Industrial Metals | Steel | BSS |
| ABCStockExchange 5 Tier Classification | Industrial Metals | KIO | KIO |