RelationshipData
The RelationshipData()
function returns data associated with a relationship between an entity and its constituents.
Related functions: Relationship, Relationship.Period, TimeSeries
Suntax:
Qx.RelationshipData(Entity Code(s), Relationship Type, Factor(s), Start Date, End Date, Frequency, Interp Method, View, Unit Code, Period, IAAD, Factor Source(s), Relationship Source(s), Factor Attributes, ShowError)
- In Excel with Quintessence add-in versions prior to 25.1, use:
RelationshipData()
- In the Quintessence Editor (Studio) using Quintessence language, use:
RelationshipData()
- In the Quintessence Editor (Studio) using Python language, use:
Qx.RelationshipData()
ⓘ See parameter descriptions
Entity Code(s) | An entity in the Quintessence database is any element with associated data that is identified by a code, such as an instrument, portfolio, country, or manager. | |
Relationship Type | The relationship between the child and parent entities. | |
Factor(s) | The type of TimeSeries values requested. | |
Start Date (optional) | The date delimiting the start of the range of value dates under consideration. | |
End Date (optional) | The date delimiting the end of the range of value dates under consideration. | |
Frequency (optional) | Depending on the frequency selected, a date in the range may or may not be included in the result. Possible values: • P – Points• 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 endsAny value prefixed with T means trading days only.For example, TWE means week ends that are also trading days. | |
Interp method (optional) | Includes both interpolation and extrapolation. Interpolation estimates a value that lies between two known values in a sequence, while extrapolation estimates a value by extending a known sequence of values or facts beyond its established range. The valid options are None and Forward . | |
View (optional) | Defines how the data should be displayed. Options include: • none return default columns without headers• column return default columns with headers• DataView():header returns columns with or without headers.See more on DataView. • PivotView():header returns pivoted view with or without headers.See more on PivotView. | |
Unit Code (optional) | The code of the unit type associated with the relationship data. | |
Period (optional) | The period related to the values under consideration. Possible values: • NA – Not Applicable• D – Day• W – Week• M – Month• B – Bi-Monthly• Q – Quarter• S – Semi-Annual• Y – Year | |
IAAD (optional) | The as at date specifies the point in time for the information. Any data declared after the IAAD is ignored. | |
Factor Source(s) (optional) | The source provider of the data. | |
Relationship Source(s) (optional) | The source(s) of the relationship under consideration. | |
Factor Attributes (optional) | The attribute(s) of the values under consideration. | |
ShowError (optional) | If TRUE, an error message is displayed when the function fails. |
Remarks:
- Unless changed by an administrator, the default parameters are:
- Start Date, End Date and IAAD: today
- Frequency:
P
- Interp method:
None:None
- View:
none
- Unit Code, Period, Source and Factor Attributes: determined by implicit or explicit defaults, if available.
- It is always safer to specify the Unit Code, Period, Source and Factor Attributes parameters rather than relying on the default. Leaving this blank not only depends on explicit defaults set by the administrator, but also on data being loaded, which may cause conflicts with implicit defaults.
- In Quintessence versions 23.1 and later, if the interpolation and extrapolation options are the same (for example,
Forward:Forward
), you can simply passForward
. - In the examples below we will refer to Factor Attributes as Attributes.
Examples:
The examples provided below are based on fictitious data for illustrative purposes only.
> Example 1: Retrieve RelationshipData
Suppose the following market value data from SourceProv1 is stored for the portfolio PortEnt1 in the Quintessence database (with Unit Code, Period, and Attributes set to NA).
Parent | Child | Date | Market Value |
PortEnt1 | StEnt1 | 2025-09-01 | 1545 |
PortEnt1 | StEnt2 | 2025-09-01 | 1005 |
PortEnt1 | StEnt3 | 2025-09-01 | 1150 |
PortEnt1 | StEnt1 | 2025-09-03 | 1500 |
PortEnt1 | StEnt2 | 2025-09-03 | 1000 |
PortEnt1 | StEnt3 | 2025-09-03 | 1250 |
This data can be retrieved with the following data call:
=Qx.RelationshipData("PortEnt1", "Constituent", "Market Value", "2025-09-1", "2025-09-05",,, "DataView(parent, child, valuedate, value)", "na", "NA",, "SourceProv1", "SourceProv1", "na")
> Example 2: Retrieving the value of a particular child in the relationship data
Suppose the following market value data from SourceProv1 is stored for the portfolio PortEnt1 in the Quintessence database (with Unit Code, Period, and Attributes set to NA).
Parent | Child | Date | Market Value |
PortEnt1 | StEnt1 | 2025-09-01 | 1545 |
PortEnt1 | StEnt2 | 2025-09-01 | 1005 |
PortEnt1 | StEnt3 | 2025-09-01 | 1150 |
PortEnt1 | StEnt1 | 2025-09-03 | 1500 |
PortEnt1 | StEnt2 | 2025-09-03 | 1000 |
PortEnt1 | StEnt3 | 2025-09-03 | 1250 |
We would like to retrieve the value for the child entity StEnt1 only. One way to do this is to first retrieve the data for all constituents using RelationshipData()
, and then apply FilterData() to the result. However, by using the ->
notation, we can retrieve the data directly from RelationshipData()
by passing PortEnt1 -> StEnt1
as the entity.
=Qx.RelationshipData("PortEnt1 -> StEnt1", "Constituent", "Market Value", "2025-09-1", "2025-09-05",,, "DataView(valuedate, value)", "na", "NA",, "SourceProv1", "SourceProv1", "na")
The results obtained are:
2025-09-01 | 1545 |
2025-09-03 | 1500 |
> Example 3: Retrieving both RelationshipData and TimeSeries data
Suppose the following market value data from SourceProv1 is stored for the portfolio PortEnt1 in the Quintessence database (with Unit Code, Period, and Attributes set to NA).
Parent | Child | Date | Market Value |
PortEnt1 | StEnt1 | 2025-09-01 | 1545 |
PortEnt1 | StEnt2 | 2025-09-01 | 1005 |
PortEnt1 | StEnt3 | 2025-09-01 | 1150 |
For some of these child entities, the following price data from SourceProv1 is also available (with Unit Code, Period, and Attributes set to NA):
Entity | Date | Price |
StEnt1 | 2025-09-01 | 10 |
StEnt3 | 2025-09-01 | 20 |
For the parent entity, the portfolio name from SourceProv1 is also available (with Unit Code, Period, and Attributes set to NA):
Entity | Date | Name |
PortEnt1 | 2025-09-01 | PortfolioEntity1 |
We can retrieve all this data using the RelationshipData()
function by requesting the factor in a specific way:
- To retrieve the market value data, pass the factor
Market Value
. - To retrieve the price data, pass
TS:Price
(TS stands for TimeSeries). - To retrieve the portfolio name, pass
PTS:Name
(PTS stands for Parent TimeSeries).
The example below shows how to request this data using PivotView().
=Qx.RelationshipData("PortEnt1", "Constituent", "Market Value, TS:Price, PTS:Name", "2025-09-01", "2025-09-01", "P",, "PivotView({parent, child}, {factor}, value)", "na", "NA",, "SourceProv1", "SourceProv1", "na")
The results obtained are:
Market Value | Name | Price | ||
StEnt1 | 10 | |||
StEnt3 | 20 | |||
PortEnt1 | PortfolioEntity1 | |||
PortEnt1 | StEnt1 | 1545 | ||
PortEnt1 | StEnt2 | 1005 | ||
PortEnt1 | StEnt3 | 1150 |