Timeseries
TimeSeries is a sequence of data points, each consisting of a date and a value, associated with an entity and stored in the Quintessence database along with related metadata. The TimeSeries() function retrieves these records and their associated information for a specified date range.
See also Introduction to TimeSeries, Entities
Syntax:
Qx.TimeSeries(Entity Code(s), Factor(s), Start Date, End Date, Frequency, Interp method, View, Unit Code, Period, IAAD, Source(s), Factor Attributes, ShowError)- In Excel with Quintessence add-in versions prior to 25.1, use:
TimeSeries() - In the Quintessence Editor (Studio) using Quintessence language, use:
TimeSeries() - In the Quintessence Editor (Studio) using Python language, use:
Qx.TimeSeries()
ⓘ 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. |
| 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, Linear, Backward (not available for extrapolation), 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 TimeSeries. |
| Period (optional) | The period related to the TimeSeries 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. |
| Source(s) (optional) | The source provider of the data. |
| Factor Attributes (optional) | The attribute(s) of the TimeSeries 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:
TD - 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,
Linear:Linear), you can simply passLinear. - 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 TimeSeries data.
Suppose the following price data is stored for the stock StockEntity1 in the Quintessence database.
| Price | Date | Unit Code | Period | Source | Attributes |
| 101 | 2024-06-04 | UnCo1 | D | SourceProv1 | NA |
You can retrieve this data by making the following call:
=Qx.TimeSeries("StockEntity1", "Price", "2024-06-04", "2024-06-04", "P",,, "UnCo1", "D",, "SourceProv1", "NA")| 101 |
> Example 2: Requesting all data for a factor.
When requesting all data for the factor Price on the entity StockEntity1, the parameters Unit Code, Period, Source, and Attributes can be set to *. This indicates that data should be retrieved for all available values of these parameters.
Suppose the following price data for StockEntity1 exists in the Quintessence database:
| Price | Date | Unit Code | Period | Source | Attributes |
| 101 | 2024-06-04 | UnitCo1 | D | SourceProv1 | NA |
| 100 | 2024-06-05 | UnitCo1 | W | SourceProv1 | Revised |
| 99 | 2024-06-06 | UnitCo1 | D | SourceProv1 | NA |
| 108 | 2024-06-06 | UnitCo2 | D | SourceProv2 | NA |
| 105 | 2024-06-30 | UnitCo3 | M | SourceProv2 | Freq=M |
You can retrieve all price data for StockEntity1 by using the following call:
=Qx.TimeSeries("StockEntity1", "Price", "2024-06-01", "2024-06-30", "P",, "dataview(value):column", "*", "*",, "*", "*")| Value |
| 101 |
| 100 |
| 99 |
| 108 |
| 105 |
To retrieve all price data from the provider SourceProv1, modify the call as follows:
=Qx.TimeSeries("StockEntity1", "Price", "2024-06-01", "2024-06-30", "P",, "dataview(value):column", "*", "*",, "SourceProv1", "*")| Value |
| 101 |
| 100 |
| 99 |
> Example 3: Applying interpolation and extrapolation on data.
In this example, green will indicate the interpolated values, while blue will indicate the extrapolated values.
Suppose the following data exists in the Quintessence database for the entity StockEntity2.
| Price | Date | Unit Code | Period | Source | Attributes |
| 10 | 2024-06-03 | NA | NA | SourceProv1 | NA |
| 20 | 2024-06-05 | NA | NA | SourceProv1 | NA |
| 30 | 2024-06-07 | NA | NA | SourceProv1 | NA |
To extrapolate the data from 7 June 2024 onward and apply linear interpolation between dates that contain data, we can use Linear:Forward as the Interp Method parameter. Note that the IAAD value passed in is 1 January 2025, and we want to see the data for each day in our date range; hence, the Frequency parameter is set to D.
=Qx.TimeSeries("StockEntity2", "Price", "2024-06-03", "2024-06-09", "D", "Linear:Forward", "dataview(valuedate, declarationdate, value):column", "NA", "NA", "2025-01-01", "SourceProv1", "NA")| ValueDate | DeclarationDate | Value |
| 2024-06-03 | 2024-06-03 | 10 |
| 2024-06-04 | 2025-01-01 | 15 |
| 2024-06-05 | 2024-06-05 | 20 |
| 2024-06-06 | 2025-01-01 | 25 |
| 2024-06-07 | 2024-06-07 | 30 |
| 2024-06-08 | 2024-06-07 | 30 |
| 2024-06-09 | 2024-06-07 | 30 |
Whenever a request attempts to carry a specific value forward or backward, the DeclarationDate will indicate the ValueDate from which the value was carried.
If a linear approach is applied to the values, the DeclarationDate will reflect the IAAD value, indicating that the received value is linearly calculated as of the supplied date.
To apply backward interpolation and linear extrapolation, use Backward:Linear.
=Qx.TimeSeries("StockEntity2", "Price", "2024-06-01", "2024-06-09", "D", "Backward:Linear", "dataview(valuedate, declarationdate, value):column", "NA", "NA", "2025-01-01", "SourceProv1", "NA")| ValueDate | DeclarationDate | Value |
| 2024-06-01 | 2025-01-01 | 0 |
| 2024-06-02 | 2025-01-01 | 5 |
| 2024-06-03 | 2024-06-03 | 10 |
| 2024-06-04 | 2024-06-05 | 20 |
| 2024-06-05 | 2024-06-05 | 20 |
| 2024-06-06 | 2024-06-07 | 30 |
| 2024-06-07 | 2024-06-07 | 30 |
| 2024-06-08 | 2025-01-01 | 35 |
| 2024-06-09 | 2025-01-01 | 40 |
The interpolation method (Interp Method) can also be applied with respect to different frequencies, except for P.
For example, if we want to forward interpolate the value on the 7th for the next five days – but only on trading days – we can achieve this by passing the following call.
=Qx.TimeSeries("StockEntity2", "Price", "2024-06-07", "2024-06-12", "TD", "None:Forward", "dataview(valuedate, declarationdate, value):column", "NA", "NA", "2025-01-01", "SourceProv1", "NA")| ValueDate | DeclarationDate | Value |
| 2024-06-07 | 2024-06-07 | 30 |
| 2024-06-10 | 2024-06-07 | 30 |
| 2024-06-11 | 2024-06-07 | 30 |
| 2024-06-12 | 2024-06-07 | 30 |
> Example 4: Retrieve the last n number of available data points.
Suppose the following data exist in the Quintessence database for entity StockEntity3.
| Price | Date | Unit Code | Period | Source | Attributes |
| 10 | 2024-06-03 | NA | NA | SourceProv1 | NA |
| 20 | 2024-06-05 | NA | NA | SourceProv1 | NA |
| 30 | 2024-06-07 | NA | NA | SourceProv1 | NA |
To retrieve the latest available price for StockEntity3, we can use an offset of -1 on both the Start Date and End Date.
Assuming today’s date is 1 January 2025, this offset will return the most recent value prior to that date.
=Qx.TimeSeries("StockEntity3", "Price", "2025-01-01:-1", "2025-01-01:-1", "P",, "PivotView({Entity}, {Source, ValueDate}, Value)", "NA", "NA",, "SourceProv1", "NA")| SourceProv1 | |
| 2024-06-07 | |
| StockEntity3 | 30 |
To retrieve the second latest available price, apply an offset of -2 to both the Start Date and End Date.
=Qx.TimeSeries("StockEntity3", "Price", "2025-01-01:-2", "2025-01-01:-2", "P",, "PivotView({Entity}, {Source, ValueDate}, Value)", "NA", "NA",, "SourceProv1", "NA")| SourceProv1 | |
| 2024-06-05 | |
| StockEntity3 | 20 |
To retrieve all available price values, pass an offset of * to the Start Date.
=Qx.TimeSeries("StockEntity3", "Price", "2025-01-01:*", "2025-01-01", "P",, "PivotView({Entity}, {Source, ValueDate}, Value)", "NA", "NA",, "SourceProv1", "NA")| SourceProv1 | SourceProv1 | SourceProv1 | |
| 2024-06-03 | 2024-06-05 | 2024-06-07 | |
| StockEntity3 | 10 | 20 | 30 |
> Example 5: Forecasted values retrieval example
To retrieve forecasted values from the Quintessence database, assume today’s date is 1 January 2025.
Suppose the provider SourceProv1 made a forecast on 3 June 2024, predicting that the Price for StockEntity4 would be 100 on 12 September 2025. In Quintessence, the data will be stored as shown below (assume that the Unit Code, Period and Attributes for this data is NA in the Quintessence database).
| ValueDate | DeclarationDate | Price |
| 2025-09-12 | 2024-06-03 | 100 |
On 5 June 2024, the provider SourceProv1 revised their earlier forecast, updating the expected Price for StockEntity4 on 12 September 2025 from 100 to 101. As a result, the Quintessence database is updated to reflect this new information.
| ValueDate | DeclarationDate | Price |
| 2025-09-12 | 2024-06-03 | 100 |
| 2025-09-12 | 2024-06-05 | 101 |
On 12 June 2024, the provider SourceProv1 revised their forecast again, updating the expected Price for StockEntity4 on 12 September 2025 from 101 to 99. The Quintessence database is updated accordingly to reflect this latest forecast.
| ValueDate | DeclarationDate | Price |
| 2025-09-12 | 2024-06-03 | 100 |
| 2025-09-12 | 2024-06-05 | 101 |
| 2025-09-12 | 2024-06-12 | 99 |
To retrieve the most up-to-date forecasted Price for StockEntity4 as of 1 January 2025, apply an offset of 1 to the End Date. This offset instructs the system to return the first available ValueDate after today’s date, effectively retrieving the next known forecast from the current point in time.
=Qx.TimeSeries("StockEntity4", "Price", "2025-01-01", "2025-01-01:1", "P",,, "na", "NA", "2025-01-01", "SourceProv1", "na")| 99 |
To retrieve the second-to-last most updated forecasted value, apply an offset of -1 to the IAAD parameter. This instructs the system to return the forecast that was declared just before the latest one, allowing access to historical revisions for comparison or audit purposes.
=Qx.TimeSeries("StockEntity4", "Price", "2025-01-01", "2025-01-01:1", "P",,, "na", "NA", "2025-01-01:-1", "SourceProv1", "na")| 101 |