DateSeries
The DateSeries() function returns a range of dates. The frequency determines the sequence of dates to return, for example days, month ends, trading days only, week starts etc. The trading days returned take into account weekends and the public holidays of the securities exchange or country under consideration.
Syntax
DateSeries (Start Date, End Date, Evaluation Date, Frequency, ShowError)
| Start Date |
The start date delimiting the range
Note: depending on the frequency selected, the start date or end date may not be included in the output, for example, if the chosen frequency is trading days only, the start date or end date may not be a trading day. |
| End Date | The end date delimiting the range |
| Frequency |
The sequence of value dates to return. Possible values: “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 ends),
Any value prefixed by a ‘T’ means trading days only. For example, ‘TWE’ means week ends that are also trading days. |
| ShowError | If ShowError is set to TRUE, an 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.
Examples
Example 1. Return all week starts in April that were trading days
Example 2. Return all trading month ends in 2015
Example 3. Return all week starts for the next 60 days
Note:
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 all week starts in April that were trading days
DateSeries() is used to return all trading week starts in April 2015 that were also trading days. Note that the 7th of April is a Tuesday. This is because the 6th was a holiday (for the default exchange of this implementation) and therefore not a trading day.
| Function |
| =DateSeries(“1 April 2015″,”30 April 2015″,”tws”) |
| Output |
| 2015/04/07 |
| 2015/04/13 |
| 2015/04/20 |
| 2015/04/28 |
Example 2. Return all trading month ends in 2015
DateSeries() is used to return all month ends in 2015
| Function |
| =DateSeries(“1 Jan 2015″,”31 Dec 2015″,”tme”) |
| Output |
| 2015/01/30 |
| 2015/02/27 |
| 2015/03/31 |
| 2015/04/30 |
| 2015/05/29 |
| 2015/06/30 |
| 2015/07/31 |
| 2015/08/31 |
| 2015/09/30 |
| 2015/10/30 |
| 2015/11/30 |
| 2015/12/31 |
Example 3. Return all week starts for the next 60 days
DateSeries() is used to return all week starts for the next 60 days (in this example, DateOffset() is 2 June 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.
| Function |
| =DateSeries(DateOffset(),DateOffset()+60,”ws”) |
| Output |
| 2015/06/08 |
| 2015/06/15 |
| 2015/06/22 |
| 2015/06/29 |
| 2015/07/06 |
| 2015/07/13 |
| 2015/07/20 |
| 2015/07/27 |