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 Type, Factor(s), StartDate, EndDate, Frequency, Interp Method, View, Unit Code, Period, IAAD, Source(s), Relationship Source(s), Factor Attribute(s), ShowError)
| Entity Code(s) |
An entity is any element in the Quintessence database that has data associated with it and is identified by a code, for example, an instrument, portfolio, country or manager. Multiple entity codes are separated by commas.
|
| Relationship Type | The type of connection between the primary entity and other entities, for example, “Constituent”, “Country”, “Region”, “Broker”. |
| Factor(s) | The type of values under consideration, for example “Holding”, “Market Value”, “Accrued Income”, “Profit and Loss” etc. Multiple factors are separated by commas. |
| Start Date |
The start date delimiting the range of value dates to return.
Note: depending on the frequency selected, the start date or end date may not be included in the results, for example, if the chosen frequency is trading days only, the start date or end date may not be a trading day. |
| End Date | The end date delimiting the range of value dates to return. |
| Frequency |
The sequence of value dates to return. Possible values: “D” (days), “WS” (week starts), “WE” (week ends), “MS” (month starts), “ME” (month ends), “QS” (quarter starts), “QE” (quarter ends), “YS” (year starts), “YE” (year ends), “P” (any point with values).
Any value prefixed by a ‘T’ means trading days only. For example, ‘TWE’ means week ends that are also trading days. |
| Interp Method |
This takes the form “interpolation:extrapolation”. Interpolation is an estimation of a value within two known values in a sequence of values. Extrapolation is an estimation of a value based on extending a known sequence of values or facts beyond what is known. The interpolation value can either be “Linear”, “Forward” or “None”. The extrapolation can either be “Forward” or “None”. |
| View |
This parameter takes the form ‘DataView(*):Headers’. To return specific columns, reference them by name, for example, “DataView(Entity,ValueDate)”. Using (*) returns all columns.
The value for Headers can be:
For example, “DataView(*):column” or “DataView():none”
|
| Unit Code | The unit type associated with the relationship data, for example, a currency, such as “GBP” or a language, such as “ENG”. |
| Period | The period related to the values under consideration. For example, for the factor ‘High Price’, the period can be “d” (day), “w” (week), “m” (month) or “y” (year), in other words the high price for the day, week, month or year respectively. |
| IAAD | IAAD is the ‘Information As At Date’. Any data declared after the IAAD is ignored. The IAAD allows the simulation of what information would have been available at that point in time. |
| Source(s) |
The source(s) of the data under consideration. Examples of sources: MarketDataProvider2, PortfolioAdministrator1, MarketDataProvider1 etc.
Note: If only one source is specified, it applies to all the factors. When multiple sources are specified, they must correlate with the list of factors specified on a one-to-one basis. |
| Relationship Source(s) | The source(s) of the relationship under consideration. |
| Factor Attributes | The attribute(s) of the values under consideration, for example, “F” (final), “I” (interim), “NA” (not applicable). |
| 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 the market values of a portfolio’s holdings
Example 2. Return the effective exposure of a portfolio’s holdings
Example 3. Return both RelationshipData (holdings) and constituent TimeSeries data (close price)
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 the market values of a portfolio’s holdings
This example retrieves the market values of a portfolio’s holdings between specified dates and for specified parameters.
| Function |
| =RelationshipData(“5000″,”constituent”,”Market Value”,”2012-10-01″,”2012-10-02″,”P”,”none:none”,”Dataview(*):column”,”ZAR”,”D”,DateOffset(),”StockExchange1″,”StockExchange1″,”NA”,TRUE) |
Note:
Today() is a ‘volatile’ function, meaning it automatically recalculates itself. This can make larger spreadsheets very slow. In these cases, rather use DateOffset(). DateOffset() returns today’s date, but does not recalculate itself every time a spreadsheet is opened.
| Output | |||||||||
| Parent | Child | Factor | Unit | Period | Attributes | Source | ValueDate | DeclarationDate | Value |
| 5000 | CEB | Market Value | ZAR | D | NA | StockExchange1 | 2012/10/01 | 2012/10/01 | 26401 |
| 5000 | MBD | Market Value | ZAR | D | NA | StockExchange1 | 2012/10/01 | 2012/10/01 | 9189 |
| 5000 | KVJ | Market Value | ZAR | D | NA | StockExchange1 | 2012/10/01 | 2012/10/01 | 8217 |
| 5000 | CEB | Market Value | ZAR | D | NA | StockExchange1 | 2012/10/02 | 2012/10/02 | 27060 |
| 5000 | MBD | Market Value | ZAR | D | NA | StockExchange1 | 2012/10/02 | 2012/10/02 | 9160 |
| 5000 | KVJ | Market Value | ZAR | D | NA | StockExchange1 | 2012/10/02 | 2012/10/02 | 8182 |
Example 2. Return the effective exposure of a portfolio’s holdings
This example retrieves the effective exposure of a fund’s constituents between specified dates and for specified parameters.
| Function |
| =RelationshipData(“5090″,”constituent”,”Effective Exposure”,”2015-02-02″,”2015-02-03″,”P”,”none:none”,”Dataview(*):column”,”ZAR”,”D”,DateOffset(),”PortfolioAdministrator1″,”PortfolioAdministrator1″,”NA”,TRUE) |
| Output | |||||||||
| Parent | Child | Factor | Unit | Period | Attributes | Source | ValueDate | DeclarationDate | Value |
| 5090 | CEB | Effective Exposure | ZAR | D | NA | StockExchange1 | 2015/02/02 | 2015/02/02 | 26401 |
| 5090 | MBD | Effective Exposure | ZAR | D | NA | StockExchange1 | 2015/02/02 | 2015/02/02 | 9189 |
| 5090 | KVJ | Effective Exposure | ZAR | D | NA | StockExchange1 | 2015/02/02 | 2015/02/02 | 8217 |
| 5090 | CEB | Effective Exposure | ZAR | D | NA | StockExchange1 | 2015/02/03 | 2015/02/03 | 27060 |
| 5090 | MBD | Effective Exposure | ZAR | D | NA | StockExchange1 | 2015/02/03 | 2015/02/03 | 9160 |
| 5090 | KVJ | Effective Exposure | ZAR | D | NA | StockExchange1 | 2015/02/03 | 2015/02/03 | 8182 |
Example 3. Return both RelationshipData (holdings) and constituent TimeSeries data (close price)
This example retrieves a fund’s constituent holdings and close prices of those consitutents between specified dates and for specified parameters. Note that Close Price does not have a value for parent, as it is independent of fund 5080 and applies only to the constituents.
| Function |
| =RelationshipData(“5080″,”Constituent”,”Holding, Close Price”,”2015-01-01″,”2015-01-05″,”TD”,”none:none”,”Dataview(*):column”,”ZAR”,”D”,DateOffset(),”MarketDataProvider2″,”MarketDataProvider2″,”NA”,TRUE) |
| Output | |||||||||
| Parent | Child | Factor | Unit | Period | Attributes | Source | ValueDate | DeclarationDate | Value |
| 5080 | ZIK | Holding | ZAR | D | NA | MarketDataProvider2 | 2015/01/02 | 2015/01/02 | 534400000 |
| 5080 | DNM | Holding | ZAR | D | NA | MarketDataProvider2 | 2015/01/02 | 2015/01/02 | 918955584 |
| 5080 | GPS | Holding | ZAR | D | NA | MarketDataProvider2 | 2015/01/02 | 2015/01/02 | 101888899 |
| ZIK | Close Price | ZAR | D | NA | MarketDataProvider2 | 2015/01/02 | 2015/01/02 | 24.5 | |
| ZIK | Close Price | ZAR | D | NA | MarketDataProvider2 | 2015/01/05 | 2015/01/05 | 23.81 | |
| DNM | Close Price | ZAR | D | NA | MarketDataProvider2 | 2015/01/02 | 2015/01/02 | 34 | |
| DNM | Close Price | ZAR | D | NA | MarketDataProvider2 | 2015/01/05 | 2015/01/05 | 33.8 | |
| GPS | Close Price | ZAR | D | NA | MarketDataProvider2 | 2015/01/02 | 2015/01/02 | 57 | |
| GPS | Close Price | ZAR | D | NA | MarketDataProvider2 | 2015/01/05 | 2015/01/05 | 56.7 | |