Timeseries
A TimeSeries is a series of points consisting of dates and values, associated with an entity, and stored in the Quintessence database along with other related information. The TimeSeries() function retrieves the records and associated information for a specified range of dates.
See also Introduction to TimeSeries, Entities
Syntax
TimeSeries (Entity Codes, Factors, Start Date, End Date, Frequency, Interp method, View, Unit Code, Period, IAAD, Sources, Factor Attributes, ShowError)
| Parameter | Description | Examples |
| 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, instruments, portfolios, countries or managers.
|
Examples: “ABC” “VRR” “ABC, VRR, GMP” |
| Factor(s) | The type of TimeSeries values requested. |
Examples: “Close Price” “Current Assets” “Market Value, Export Sales” |
| Start Date | The date delimiting the start of the range of value dates under consideration. |
Any valid date, for example: “1 January 2015” “2015/03/15” “2015-04-04” |
| End Date | The date delimiting the end of the range of value dates under consideration. | Any valid date, as for Start Date. |
| Frequency |
Note: depending on the frequency selected, the start date or end date may not be included in the TimeSeries results, for example, if the chosen frequency is trading days only, the start date or end date may not be a trading day. |
The sequence of value dates to return. Possible values: “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 |
Interpolation is an estimation of a value within two known values in a sequence of values. Interpolation can be “none”, “linear” or “forward”.
Extrapolation is an estimation of a value based on extending a known sequence of values or facts beyond what is known. Extrapolation can be “none” or “forward”. |
This parameter is formatted as “interpolation:extrapolation”, for example: “linear:none” “none:forward”. |
| 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”
|
Examples: “Dataview()” “Dataview(*)” “Dataview(*):column” “Dataview(Entity, ValueDate, Value):none” “Dataview(Entity:StockExchange1 Code, Factor:MarketDataProvider2 code,ValueDate, Value):none” |
| Unit Code | The code of the unit type associated with the TimeSeries, for example currency codes or country codes. |
Examples: “GBP” “USD” “ENG” Using “*” as a parameter value returns records for all unit types. |
| Period | The period related to the TimeSeries values under consideration. For example, ‘Close Price’ is daily and ‘EPS’ is annual. | Possible values: “D” (day), “W” (week), “M” (month), “Y” (year), null or NA (not applicable). |
| 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. | Any valid date. Using “*” as a parameter value returns records for all declaration dates – this is useful when looking at movements between forecast figures, for example. |
| 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. |
Examples: “MarketDataProvider2” “PortfolioAdministrator1” “MarketDataProvider1” |
| Factor Attributes | The attribute(s) of the TimeSeries values under consideration. |
Examples: “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.
An Illustration
The TimeSeries() function returns the requested values from underlying data. For example, given a set of stored data, the TimeSeries() function is used to return the Daily High Price for instrument ABC. Final ZAR values are requested, occurring between 3 January 2015 and 10 January 2015, on trading days only and sourced from Maitland. Any values declared after 1 March 2015 are ignored.
=TimeSeries(“ABC”, “High Price”, “3 Jan 2015”, “10 Jan 2015”, “TD”, “none:none”, “Dataview(*):column”, “ZAR”, “D”, “1 Mar 2015”, “Maitland”, “Final”)
In this illustration, the underlying data is filtered from left to right according to the specified parameter values until the final set of output data remains (outlined in red).
Examples
Example 1. Return a single attribute, such as the name of an instrument
Example 2. Return a series of values and show all columns, with headings
Example 3. Return a series of values and show specific columns, without headings
Example 4. Return a series of values between two dates relative to today
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 a single attribute, such as the name of an instrument
This example returns the name of an instrument on a specific date.
| Retrieve the name of Instrument VWE on 18 February 2011 |
| =TimeSeries(“VWE”,”Name”,”18 Feb 2011″,”18 Feb 2011″,”D”,”forward:forward”) |
| Output |
| Violet West Equities |
Example 2. Return a series of values and show all columns, with headings
This example returns the same series of values returned in Example 2 above, but all columns are displayed, along with columns headings, as per the parameter value DataView(*):column.
| Retrieve the MarketDataProvider2 Close Prices for instrument VWE between 1 May 2015 and 10 May 2015, as at 15 May 2015 |
| =TimeSeries(“VWE”,”Close Price”,”1 May 2015″,”10 May 2015″,”TD”,”none:none”,”Dataview(*):column”,”ZAR”,”D”,”15 May 2015″,”MarketDataProvider2″,”NA”,TRUE) |
| Output | ||||||||
| Entity | Factor | Unit | Period | Attributes | Source | ValueDate | DeclarationDate | Value |
| VWE | Close Price | ZAR | D | NA | MarketDataProvider2 | 2015/05/04 | 2015/05/09 | 256.56 |
| VWE | Close Price | ZAR | D | NA | MarketDataProvider2 | 2015/05/05 | 2015/05/10 | 264 |
| VWE | Close Price | ZAR | D | NA | MarketDataProvider2 | 2015/05/06 | 2015/05/10 | 260.75 |
| VWE | Close Price | ZAR | D | NA | MarketDataProvider2 | 2015/05/07 | 2015/05/10 | 250.5 |
| VWE | Close Price | ZAR | D | NA | MarketDataProvider2 | 2015/05/08 | 2015/05/10 | 253.25 |
Example 3. Return a series of values and show specific columns, without headings
This example returns the same series of values returned in Example 2 and 3 above, but specific columns are displayed, without column headings, as per the parameter value Dataview(Entity,ValueDate,Value):none.
| Retrieve the same TimeSeries as in example 1 above, but only return specific columns and don’t show the column headers |
| =TimeSeries(“VWE”, “Close Price”, “1 May 2015”, “10 May 2015”, “TD”, “none:none”, “Dataview(Entity,ValueDate,Value):none”, “ZAR”, “D”, “15 May 2015”, “MarketDataProvider2”, “NA”, TRUE) |
| Output | ||
| VWE | 2015/05/04 | 256.56 |
| VWE | 2015/05/05 | 264 |
| VWE | 2015/05/06 | 260.75 |
| VWE | 2015/05/07 | 250.5 |
| VWE | 2015/05/08 | 253.25 |
Example 4. Return a series of values between two dates relative to today
In this example, DateOffset() is used to calculate the first and fifth trading days of the current year (in this example 2015).
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.
| Retrieve the PortfolioAdministrator1 close prices for instrument CFH for the first five trading days of the year |
| =TimeSeries(“CFH”,”close price”,DateOffset(1,CONCATENATE(“1 January “,YEAR(DateOffset())),”td”),DateOffset(5,CONCATENATE(“1 January “,YEAR(DateOffset())),”td”),”td”,”none:none”,”dataview(*):column”,”ZAR”,”D”,DateOffset(),”PortfolioAdministrator1″,”NA”) |
| Output | ||||||||
| Entity | Factor | Unit | Period | Attributes | Source | ValueDate | DeclarationDate | Value |
| CFH | Close Price | ZAR | D | NA | PortfolioAdministrator1 | 2015/01/02 | 2015/01/02 | 247.7 |
| CFH | Close Price | ZAR | D | NA | PortfolioAdministrator1 | 2015/01/05 | 2015/01/05 | 236.5 |
| CFH | Close Price | ZAR | D | NA | PortfolioAdministrator1 | 2015/01/06 | 2015/01/06 | 240.59 |
| CFH | Close Price | ZAR | D | NA | PortfolioAdministrator1 | 2015/01/07 | 2015/01/07 | 239.56 |
| CFH | Close Price | ZAR | D | NA | PortfolioAdministrator1 | 2015/01/08 | 2015/01/08 | 240.05 |
Example 5. Return a series of values and pivot the results
The Pivot() function is used to lay out the TimeSeries results in a different format to make them easier to read.
| Return current assets and current liabilities for two instruments |
| =TimeSeries(“XDU,ABA”, “Current Assets,Current Liabilities”,”2014-12-01″,”2014-12-31″,”p”,”none:none”,”Dataview(*)”,”ZAR”,”NA”,”2015-01-01″,”MarketDataProvider2″,”*”,TRUE) |
| Output | ||||||||
| XDU | Current Assets | ZAR | NA | F | MarketDataProvider2 | 2014/12/31 | 2014/12/31 | 143.08 |
| XDU | Current Liabilities | ZAR | NA | F | MarketDataProvider2 | 2014/12/31 | 2014/12/31 | 67.27 |
| ABA | Current Assets | ZAR | NA | I | MarketDataProvider2 | 2014/12/31 | 2014/02/17 | 189.01 |
| ABA | Current Liabilities | ZAR | NA | I | MarketDataProvider2 | 2014/12/31 | 2014/02/17 | 133.5 |
| Pivot the TimeSeries results into a more readable format |
| =Pivot(TimeSeries(“XDU,ABA”, “Current Assets,Current Liabilities”,”2014-12-01″,”2014-12-31″,”p”,”none:none”,”Dataview(*)”,”ZAR”,”NA”,”2015-01-01″,”MarketDataProvider2″,”*”,TRUE), “C1,C7,C5”, “C2″,”C9”) |
| Output | ||||
| Current Assets | Current Liabilities | |||
| ABA | 2014/12/31 | I | 189.01 | 133.5 |
| XDU | 2014/12/31 | F | 143.08 | 67.27 |
Heya i am for the first time here. I came across this board and I to find It truly helpful & it helped me out a lot. I hope to present one thing again and aid others such as you helped me.
I don’tknow iff it’s just me or if perhaps everyone else experiencing issues with your blog.
It appears as if some of the text in your content are running off the screen. Can somebody else please comment and
let me know if this is happening to them as well?This might be a issue with my internet browser because I’ve had this happen before.
Cheers https://menbehealth.Wordpress.com/
I really love yoir website.. Very nice colors & theme. Did
you make this amazing site yourself? Please reply back
as I’m trying to create my own blog and would love to know where you ggot this from or exactly what the theme is named.
Many thanks! https://Jobspage.ca/employer/psychology-paper-expert/