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 set of constituents for a new relationship date, or removing and adding constituents for an existing relationship date.
Syntax
UpdateRelationship (Entity, Relationship Type, Constituents, Relationship date, Source, Strategy, ShowError)
| Entity |
The entity to update
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 relationship to update for that entity, for example ‘constituent’ |
| Constituents | The constituents to add, remove or update |
| Relationship date | The date the relationship is applicable to |
| Source | The source of the data under consideration, for example, “MarketDataProvider2” or “MarketDataProvider1”. |
| Strategy |
The value of this parameter can be: “Set”, “Add” or “Remove”. Additionally, “Set” values can be combined with “Always” and “OnChange”, for example “Set, Always”. See the detailed explanation below. |
| Show Error | If ShowError is set to TRUE, an error message will be displayed if the function fails. |
Strategy:
Set replaces the set.
Add adds constituents.
Remove removes constituents.
Always means always make the change.
OnChange means only make the change if the set has changed.
If the relationship date exists:
Set, Always: Remove the existing set of constituents for that date and replace it with the new set of constituents.
Set, OnChange: Remove the existing set for that date and replace it with the new set, but only if the new set is different.
Add: Add the constituents to the existing set for that date if they are not already part of the set.
Remove: Remove the constituents from the existing set for that date.
If the relationship date does not exist:
Set, Always: The set of constituents will be added with the new relationship date.
Set, Onchange: The set will be added for the new date, but only if it differs from the set with the most recent date.
Add: The set of constituents will be added with the new relationship date.
Remove: No action.
Examples
Example 1. Replace the set, always, for an existing date
Example 2. Add the set, always, with the new date
Example 3. Replace the set, if it has changed, for an existing date (there are changes)
Example 4. Replace the set, if it has changed, for an existing date (there are no changes)
Example 5. Add the set, if it has changed, with the new date (there are changes)
Example 6. Add the set, if it has changed, with the new date (there are no changes)
Example 7. Add to the existing set (there is a matching date)
Example 8. Add as a new set (there is no matching date)
Example 9. Remove from the set for the matching date
Consider the following set of data:
| Database | ||
| Entity | Constituent | Relationship Date |
| 5010 | XDU | 2 Feb 2015 |
| 5010 | VRR | 2 Feb 2015 |
| 5010 | LEF | 2 Feb 2015 |
Example 1. Replace the set, always, for an existing date
Replace the set always. In this example the relationship date exists for that set of data and the set is replaced.
| Function |
| =UpdateRelationship(“5010”, “constituent”,”XDU, VRR, KVJ”, “2 Feb 2015”, “MarketDataProvider2”, “Set, Always”, True) |
| Updated database | ||
| Entity | Constituent | Relationship Date |
| 5010 | XDU | 2 Feb 2015 |
| 5010 | VRR | 2 Feb 2015 |
| 5010 | KVJ | 2 Feb 2015 |
Example 2. Add the set, always, with the new date
Replace the set always. In this example the relationship date does not exist for that set of data in the database. Therefore the new set is added.
| Function |
| =UpdateRelationship(“5010”, “constituent”,”XDU, VRR, KVJ”, “15 June 2015”, “MarketDataProvider2”, “Set, Always”, True) |
| Updated database | ||
| Entity | Constituent | Relationship Date |
| 5010 | XDU | 2 Feb 2015 |
| 5010 | VRR | 2 Feb 2015 |
| 5010 | LEF | 2 Feb 2015 |
| 5010 | XDU | 15 June 2015 |
| 5010 | VRR | 15 June 2015 |
| 5010 | KVJ | 15 June 2015 |
Example 3. Replace the set, if it has changed, for an existing date (there are changes)
Replace the set if it has changed. In this example the relationship date exists for that set of data in the database. The set has changed; therefore the set is replaced.
| Function |
| =UpdateRelationship(“5010”, “constituent”,”XDU, THT, GTE”, “2 Feb 2015”, “MarketDataProvider2”, “Set, OnChange”, True) |
| Updated database | ||
| Entity | Constituent | Relationship Date |
| 5010 | XDU | 2 Feb 2015 |
| 5010 | THT | 2 Feb 2015 |
| 5010 | GTE | 2 Feb 2015 |
Example 4. Replace the set, if it has changed, for an existing date (there are no changes)
Replace the set if it has changed. In this example the relationship date exists for that set of data in the database. The set has not changed; therefore no change is made.
| Function |
| =UpdateRelationship(“5010”, “constituent”,”XDU, VRR, LEF”, “2 Feb 2015”, “MarketDataProvider2”, “Set, OnChange”, True) |
| Updated database | ||
| Entity | Constituent | Relationship Date |
| 5010 | XDU | 2 Feb 2015 |
| 5010 | VRR | 2 Feb 2015 |
| 5010 | LEF | 2 Feb 2015 |
Example 5. Add the set, if it has changed, with the new date (there are changes)
Replace the set if it has changed. In this example the relationship date does not exist for that set of data in the database. The set has changed; therefore the new set is added with the new relationship date.
| Function |
| =UpdateRelationship(“5010”, “constituent”,”XDU, THT, GTE”, “15 June 2015”, “MarketDataProvider2”, “Set, OnChange”, True) |
| Updated database | ||
| Entity | Constituent | Relationship Date |
| 5010 | XDU | 2 Feb 2015 |
| 5010 | VRR | 2 Feb 2015 |
| 5010 | LEF | 2 Feb 2015 |
| 5010 | XDU | 15 June 2015 |
| 5010 | THT | 15 June 2015 |
| 5010 | GTE | 15 June 2015 |
Example 6. Add the set, if it has changed, with the new date (there are no changes)
Replace the set if it has changed. In this example the relationship date does not exist for that set of data in the database. The set has not changed since the last existing set for the most recent relationship date; therefore the new set is not added.
| Function |
| =UpdateRelationship(“5010”, “constituent”,”XDU, VRR, LEF”, “15 June 2015”, “MarketDataProvider2”, “Set, OnChange”, True) |
| Database | ||
| Entity | Constituent | Relationship Date |
| 5010 | XDU | 2 Feb 2015 |
| 5010 | VRR | 2 Feb 2015 |
| 5010 | LEF | 2 Feb 2015 |
Example 7. Add to the existing set (there is a matching date)
Add the constituents to the set with the matching date.
| Function |
| =UpdateRelationship(“5010”, “constituent”,”QIF, ZIK”, “2 Feb 2015”, “MarketDataProvider2”, “Add”, True) |
| Database | ||
| Entity | Constituent | Relationship Date |
| 5010 | XDU | 2 Feb 2015 |
| 5010 | VRR | 2 Feb 2015 |
| 5010 | LEF | 2 Feb 2015 |
| 5010 | QIF | 2 Feb 2015 |
| 5010 | ZIK | 2 Feb 2015 |
Example 8. Add as a new set (there is no matching date)
Add the constituents with the new date.
| Function |
| =UpdateRelationship(“5010”, “constituent”,”QIF, ZIK”, “15 June 2015”, “MarketDataProvider2”, “Add”, True) |
| Database | ||
| Entity | Constituent | Relationship Date |
| 5010 | XDU | 2 Feb 2015 |
| 5010 | VRR | 2 Feb 2015 |
| 5010 | LEF | 2 Feb 2015 |
| 5010 | QIF | 15 June 2015 |
| 5010 | ZIK | 15 June 2015 |
Example 9. Remove from the set for the matching date
Remove the constituents from the set with the matching date.
| Function |
| =UpdateRelationship(“5010”, “constituent”,”VRR”, “2 Feb 2015”, “MarketDataProvider2”, “Remove”, True) |
| Database | ||
| Entity | Constituent | Relationship Date |
| 5010 | XDU | 2 Feb 2015 |
| 5010 | LEF | 2 Feb 2015 |