UpdateTimeSeries
The UpdateTimeSeries() function updates TimeSeries data in the Quintessence database.
Syntax
UpdateTimeSeries (Data, Entity, Factor, Unit, Period, IAAD, Attributes, Source, Options, ShowError)
| Data |
The range of cells containing the new data. Note: This could also be an array of data. There are two formats: Date, Value (the declaration date defaults to today’s date) Declaration Date, Date, Value |
| 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.
|
| Factor | The factor to update, for example “Close Price” or “Market Value” |
| Unit | The unit associated with the factor, usually a currency, for example “ZAR”. |
| Period | The period related to the values under consideration. For example, “d” (daily) or “w” (weekly). |
| IAAD | The Information As At Date. Anything declared after this date is not updated. |
| Attributes | The Attributes of the values, for example “Historical”, “Forecast” or “Interim”. |
| Source | The source of the data under consideration, for example, “MarketDataProvider2” or “MarketDataProvider1”. |
| Options |
The options determine the insertion strategy. The options “OnChange” and “Always” are mutually exclusive. Both OnChange and Always can be combined with “Snapshot”, for example, “Always, Snapshot”. See the detailed explanation below. |
| ShowError | If ShowError is set to TRUE, an error message will be displayed if the function fails. |
Options
Always: Always make the change.
OnChange: Only make the change if the values have changed.
Snapshot: The TimeSeries is a snapshot TimeSeries. Snapshot can be combined with Always and OnChange.
Note:
TimeSeries Value Definition (TSVD) = Factor, Unit, Period, Attributes and Source.
Always and OnChange (without Snapshot)
| If a set of data matching the entity and TSVD exists for the declaration date (IAAD): | |
| Always | Update the values for the matching value dates, adding any value dates that don’t exist |
| OnChange | Only make the update for values that have changed, adding any value dates that don’t exist |
| If a set of data matching the entity and TSVD does not exist for the declaration date (IAAD): | |
| Always | Add the set of data with the new declaration date (or a default of today) |
| OnChange | Add the data with the new declaration date (or a default of today), but only add value date / value pairs that have changed since the most recent declaration date or don’t already exist for that date |
Always and OnChange with Snapshot
| If a set of data matching the entity and TSVD exists for the declaration date (IAAD): | |
| Always, Snapshot | Always replace the snapshot |
| OnChange, Snapshot | Replace only values that have changed |
| If a set of data matching the entity and TSVD does not exist for the declaration date (IAAD): | |
| Always, Snapshot | Always add a new snapshot, with the new declaration date or a default of today |
| OnChange, Snapshot | Only add the new snapshot, with the new declaration date or a default of today, if the existing data set has changed since the most recent declaration date OR the new set contains a different number of values |
Examples
Example 1. Always: There are matching declaration dates
Example 2. OnChange: There are matching declaration dates
Example 3. Always: There are no matching declaration dates
Example 4. OnChange: There are no matching declaration dates
Example 5. Always, Snapshot: There are matching declaration dates
Example 6. OnChange, Snapshot: There are matching declaration dates
Example 7. Always, Snapshot: There are no matching declaration dates
Example 8. OnChange, Snapshot: There are no matching declaration dates
Example 1. Always: There are matching declaration dates
| Database | ||||||||
| Entity | Factor | Unit | Period | Attributes | Source | Declaration Date | Value Date | Value |
| XDU | Close Price | ZAR | D | Final | MarketDataProvider1 | 1 Feb 2015 | 1 Feb 2015 | 100 |
| XDU | Close Price | ZAR | D | Final | MarketDataProvider1 | 2 Feb 2015 | 2 Feb 2015 | 150 |
| XDU | Close Price | ZAR | D | Final | MarketDataProvider1 | 3 Feb 2015 | 3 Feb 2015 | 125 |
The range to use as update values:
| Excel cells | ||
| Declaration Date | Value Date | Value |
| 1 Feb 2015 | 1 Feb 2015 | 100 |
| 2 Feb 2015 | 2 Feb 2015 | 149 |
| 3 Feb 2015 | 3 Feb 2015 | 124 |
| Function |
| =UpdateTimeSeries(A3:B5, “XDU”, “Close Price”, “ZAR”, “D”,, “Final”, “MarketDataProvider1”, “Always”, True) |
The entire set of data is replaced, regardless of whether there are any changes.
| Database | ||||||||
| Entity | Factor | Unit | Period | Attributes | Source | Declaration Date | Value Date | Value |
| XDU | Close Price | ZAR | D | Final | MarketDataProvider1 | 1 Feb 2015 | 1 Feb 2015 | 100 |
| XDU | Close Price | ZAR | D | Final | MarketDataProvider1 | 2 Feb 2015 | 2 Feb 2015 | 149 |
| XDU | Close Price | ZAR | D | Final | MarketDataProvider1 | 3 Feb 2015 | 3 Feb 2015 | 124 |
Example 2. OnChange: There are matching declaration dates
| Database | ||||||||
| Entity | Factor | Unit | Period | Attributes | Source | Declaration Date | Value Date | Value |
| XDU | Close Price | ZAR | D | Final | MarketDataProvider1 | 1 Feb 2015 | 1 Feb 2015 | 100 |
| XDU | Close Price | ZAR | D | Final | MarketDataProvider1 | 2 Feb 2015 | 2 Feb 2015 | 150 |
| XDU | Close Price | ZAR | D | Final | MarketDataProvider1 | 3 Feb 2015 | 3 Feb 2015 | 125 |
The range to use as update values:
| Excel cells | ||
| Declaration Date | Value Date | Value |
| 1 Feb 2015 | 1 Feb 2015 | 100 |
| 2 Feb 2015 | 2 Feb 2015 | 151 |
| 3 Feb 2015 | 3 Feb 2015 | 126 |
| Function |
| =UpdateTimeSeries(A3:B5, “XDU”, “Close Price”, “ZAR”, “D”,, “Final”, “MarketDataProvider1”, “OnChange”, True) |
Only values that have changed are replaced.
| Database | ||||||||
| Entity | Factor | Unit | Period | Attributes | Source | Declaration Date | Value Date | Value |
| XDU | Close Price | ZAR | D | Final | MarketDataProvider1 | 1 Feb 2015 | 1 Feb 2015 | 100 |
| XDU | Close Price | ZAR | D | Final | MarketDataProvider1 | 2 Feb 2015 | 2 Feb 2015 | 151 |
| XDU | Close Price | ZAR | D | Final | MarketDataProvider1 | 3 Feb 2015 | 3 Feb 2015 | 126 |
Example 3. Always: There are no matching declaration dates
| Database | ||||||||
| Entity | Factor | Unit | Period | Attributes | Source | Declaration Date | Value Date | Value |
| XDU | Close Price | ZAR | D | Final | MarketDataProvider1 | 1 Feb 2015 | 1 Feb 2015 | 100 |
| XDU | Close Price | ZAR | D | Final | MarketDataProvider1 | 2 Feb 2015 | 2 Feb 2015 | 150 |
| XDU | Close Price | ZAR | D | Final | MarketDataProvider1 | 3 Feb 2015 | 3 Feb 2015 | 125 |
The range to use as update values. Note the declaration date defaults to today (in this example, 1 August 2015).
| Excel cells | |
| Value Date | Value |
| 1 Feb 2015 | 100 |
| 2 Feb 2015 | 155 |
| 3 Feb 2015 | 123 |
| Function |
| =UpdateTimeSeries(A3:B5, “XDU”, “Close Price”, “ZAR”, “D”, , “Final”, “MarketDataProvider1”, “Always”, True) |
| Database | ||||||||
| Entity | Factor | Unit | Period | Attributes | Source | Declaration Date | Value Date | Value |
| XDU | Close Price | ZAR | D | Final | MarketDataProvider1 | 1 Feb 2015 | 1 Feb 2015 | 100 |
| XDU | Close Price | ZAR | D | Final | MarketDataProvider1 | 2 Feb 2015 | 2 Feb 2015 | 150 |
| XDU | Close Price | ZAR | D | Final | MarketDataProvider1 | 3 Feb 2015 | 3 Feb 2015 | 125 |
| XDU | Close Price | ZAR | D | Final | MarketDataProvider1 | 1 Aug 2015 | 1 Feb 2015 | 100 |
| XDU | Close Price | ZAR | D | Final | MarketDataProvider1 | 1 Aug 2015 | 2 Feb 2015 | 155 |
| XDU | Close Price | ZAR | D | Final | MarketDataProvider1 | 1 Aug 2015 | 3 Feb 2015 | 123 |
Example 4. OnChange: There are no matching declaration dates
| Database | ||||||||
| Entity | Factor | Unit | Period | Attributes | Source | Declaration Date | Value Date | Value |
| XDU | Close Price | ZAR | D | Final | MarketDataProvider1 | 1 Feb 2015 | 1 Feb 2015 | 100 |
| XDU | Close Price | ZAR | D | Final | MarketDataProvider1 | 2 Feb 2015 | 2 Feb 2015 | 150 |
| XDU | Close Price | ZAR | D | Final | MarketDataProvider1 | 3 Feb 2015 | 3 Feb 2015 | 125 |
The range to use as update values. Note the declaration date defaults to today (in this example, 1 August 2015).
| Excel cells | |
| Value Date | Value |
| 1 Feb 2015 | 100 |
| 2 Feb 2015 | 153 |
| 3 Feb 2015 | 146 |
| Function |
| =UpdateTimeSeries(A3:B5, “XDU”, “Close Price”, “ZAR”, “D”, , “Final”, “MarketDataProvider1”, “OnChange”, True) |
Only values that have changed since the last declaration date are added.
| Database | ||||||||
| Entity | Factor | Unit | Period | Attributes | Source | Declaration Date | Value Date | Value |
| XDU | Close Price | ZAR | D | Final | MarketDataProvider1 | 1 Feb 2015 | 1 Feb 2015 | 100 |
| XDU | Close Price | ZAR | D | Final | MarketDataProvider1 | 2 Feb 2015 | 2 Feb 2015 | 150 |
| XDU | Close Price | ZAR | D | Final | MarketDataProvider1 | 3 Feb 2015 | 3 Feb 2015 | 125 |
| XDU | Close Price | ZAR | D | Final | MarketDataProvider1 | 1 Aug 2015 | 2 Feb 2015 | 153 |
| XDU | Close Price | ZAR | D | Final | MarketDataProvider1 | 1 Aug 2015 | 3 Feb 2015 | 146 |
Example 5. Always, Snapshot: There are matching declaration dates
| Database | ||||||||
| Entity | Factor | Unit | Period | Attributes | Source | Declaration Date | Value Date | Value |
| JWQ | EPS | ZAR | Y | Forecast | MarketDataProvider1 | 18 Oct 2014 | 31 Dec 2016 | 2670 |
| JWQ | EPS | ZAR | Y | Forecast | MarketDataProvider1 | 18 Oct 2014 | 31 Dec 2015 | 2495 |
| JWQ | EPS | ZAR | Y | Forecast | MarketDataProvider1 | 18 Oct 2014 | 31 Dec 2014 | 1870 |
The range to use as update values:
| Excel cells | ||
| Declaration Date | Value Date | Value |
| 18 Oct 2014 | 31 Dec 2016 | 2670 |
| 18 Oct 2014 | 31 Dec 2015 | 2496 |
| 18 Oct 2014 | 31 Dec 2014 | 1875 |
| Function |
| =UpdateTimeSeries(A3:B5, “JWQ”, “EPS”, “ZAR”, “Y”, , “Forecast”, “MarketDataProvider1”, “Always, Snapshot”, True) |
The entire set of data is replaced, regardless of whether there are any changes or not.
| Database | ||||||||
| Entity | Factor | Unit | Period | Attributes | Source | Declaration Date | Value Date | Value |
| JWQ | EPS | ZAR | Y | Forecast | MarketDataProvider1 | 18 Oct 2014 | 31 Dec 2016 | 2670 |
| JWQ | EPS | ZAR | Y | Forecast | MarketDataProvider1 | 18 Oct 2014 | 31 Dec 2015 | 2496 |
| JWQ | EPS | ZAR | Y | Forecast | MarketDataProvider1 | 18 Oct 2014 | 31 Dec 2014 | 1875 |
Example 6. OnChange, Snapshot: There are matching declaration dates
| Database | ||||||||
| Entity | Factor | Unit | Period | Attributes | Source | Declaration Date | Value Date | Value |
| JWQ | EPS | ZAR | Y | Forecast | MarketDataProvider1 | 18 Oct 2014 | 31 Dec 2016 | 2670 |
| JWQ | EPS | ZAR | Y | Forecast | MarketDataProvider1 | 18 Oct 2014 | 31 Dec 2015 | 2495 |
| JWQ | EPS | ZAR | Y | Forecast | MarketDataProvider1 | 18 Oct 2014 | 31 Dec 2014 | 1870 |
The range to use as update values:
| Excel cells | ||
| Declaration Date | Value Date | Value |
| 18 Oct 2014 | 31 Dec 2016 | 2670 |
| 18 Oct 2014 | 31 Dec 2015 | 2496 |
| 18 Oct 2014 | 31 Dec 2014 | 1875 |
| Function |
| =UpdateTimeSeries(A3:B5, “JWQ”, “EPS”, “ZAR”, “Y”, , “Forecast”, “MarketDataProvider1”, “OnChange, Snapshot”, True) |
Only the values that have changed are updated.
| Database | ||||||||
| Entity | Factor | Unit | Period | Attributes | Source | Declaration Date | Value Date | Value |
| JWQ | EPS | ZAR | Y | Forecast | MarketDataProvider1 | 18 Oct 2014 | 31 Dec 2016 | 2670 |
| JWQ | EPS | ZAR | Y | Forecast | MarketDataProvider1 | 18 Oct 2014 | 31 Dec 2015 | 2496 |
| JWQ | EPS | ZAR | Y | Forecast | MarketDataProvider1 | 18 Oct 2014 | 31 Dec 2014 | 1875 |
Example 7. Always, Snapshot: There are no matching declaration dates
| Database | ||||||||
| Entity | Factor | Unit | Period | Attributes | Source | Declaration Date | Value Date | Value |
| JWQ | EPS | ZAR | Y | Forecast | MarketDataProvider1 | 18 Oct 2014 | 31 Dec 2016 | 2670 |
| JWQ | EPS | ZAR | Y | Forecast | MarketDataProvider1 | 18 Oct 2014 | 31 Dec 2015 | 2495 |
| JWQ | EPS | ZAR | Y | Forecast | MarketDataProvider1 | 18 Oct 2014 | 31 Dec 2014 | 1870 |
The range to use as update values:
| Excel cells | ||
| Declaration Date | Value Date | Value |
| 19 Oct 2014 | 31 Dec 2016 | 2670 |
| 19 Oct 2014 | 31 Dec 2015 | 2495 |
| 19 Oct 2014 | 31 Dec 2014 | 1870 |
| Function |
| =UpdateTimeSeries(A3:B5,”JWQ”, “EPS”, “ZAR”, “Y”, , “Forecast”, “MarketDataProvider1”, “Always, Snapshot”, True) |
Always add the new set of data, regardless of whether there any values have changed since the last declaration date or not.
| Database | ||||||||
| Entity | Factor | Unit | Period | Attributes | Source | Declaration Date | Value Date | Value |
| JWQ | EPS | ZAR | Y | Forecast | MarketDataProvider1 | 18 Oct 2014 | 31 Dec 2016 | 2670 |
| JWQ | EPS | ZAR | Y | Forecast | MarketDataProvider1 | 18 Oct 2014 | 31 Dec 2015 | 2495 |
| JWQ | EPS | ZAR | Y | Forecast | MarketDataProvider1 | 18 Oct 2014 | 31 Dec 2014 | 1870 |
| JWQ | EPS | ZAR | Y | Forecast | MarketDataProvider1 | 19 Oct 2014 | 31 Dec 2016 | 2670 |
| JWQ | EPS | ZAR | Y | Forecast | MarketDataProvider1 | 19 Oct 2014 | 31 Dec 2015 | 2495 |
| JWQ | EPS | ZAR | Y | Forecast | MarketDataProvider1 | 19 Oct 2014 | 31 Dec 2014 | 1870 |
Example 8. OnChange, Snapshot: There are no matching declaration dates
| Database | ||||||||
| Entity | Factor | Unit | Period | Attributes | Source | Declaration Date | Value Date | Value |
| JWQ | EPS | ZAR | Y | Forecast | MarketDataProvider1 | 18 Oct 2014 | 31 Dec 2016 | 2670 |
| JWQ | EPS | ZAR | Y | Forecast | MarketDataProvider1 | 18 Oct 2014 | 31 Dec 2015 | 2495 |
| JWQ | EPS | ZAR | Y | Forecast | MarketDataProvider1 | 18 Oct 2014 | 31 Dec 2014 | 1870 |
The range to use as update values:
| Excel cells | ||
| Declaration Date | Value Date | Value |
| 19 Oct 2014 | 31 Dec 2016 | 2670 |
| 19 Oct 2014 | 31 Dec 2015 | 2495 |
| Function |
| =UpdateTimeSeries(A3:B5, “JWQ”, “EPS”, “ZAR”, “Y”, , “Forecast”, “MarketDataProvider1”, “OnChange, Snapshot”, True) |
New items are added if they have changed since the last declaration date OR if the number of values is different. In this example there have been no changes, but the new set contains fewer items than the set most recently declared; therefore it constitutes a new snapshot.
| Database | ||||||||
| Entity | Factor | Unit | Period | Attributes | Source | Declaration Date | Value Date | Value |
| JWQ | EPS | ZAR | Y | Forecast | MarketDataProvider1 | 18 Oct 2014 | 31 Dec 2016 | 2670 |
| JWQ | EPS | ZAR | Y | Forecast | MarketDataProvider1 | 18 Oct 2014 | 31 Dec 2015 | 2495 |
| JWQ | EPS | ZAR | Y | Forecast | MarketDataProvider1 | 18 Oct 2014 | 31 Dec 2014 | 1870 |
| JWQ | EPS | ZAR | Y | Forecast | MarketDataProvider1 | 19 Oct 2014 | 31 Dec 2016 | 2670 |
| JWQ | EPS | ZAR | Y | Forecast | MarketDataProvider1 | 19 Oct 2014 | 31 Dec 2015 | 2495 |