getTicks¶
Data Services: Get time series data with additional functionality applied
Syntax: getTicks parms
Where parms is a dictionary of parameters, returns trade data or quote data and conjoined views thereof. You can request data for one or many instruments over a period of your choosing. If a parameter is required (R), omitting it generates an error.
applyFilter Custom filtering columns Specific columns dataType R Data type endDate R End date endTime End time fill Rack and Fill nulls idCol Change identifier column idList R List of identifiers idMapping Map identifier column inputTimeZone Input timezone outputTimeZone Output timezone pivot Pivot the result set sortCols Column sorting startDate R Start date startTime Start time temporality Temporality (Windowing) timezone Timezone doNotValidate Disable Parameter Validation limit Limit
Row order¶
When using a select statement with getTicks, the correct data will be returned but not necessarily in the order received from the feed. This is because rows get sorted by date/time. When there is more than one row with an identical date/time, the order of the rows with the same date/time is indeterminate.
Example: getTicks without select
getTicks .[!]flip(
(`symList ; `7203.T);
(`dataType ; `rawDepth);
(`startDate ; 2019.04.04);
(`endDate ; 2019.04.25);
(`startTime ; 09:36:20);
(`endTime ; 14:59:59);
(`timeZone ; `Japan);
(`time ; `exchangeTime);
(`assetClass; `equity);
(`columns ; `price)
)
The order of the rows in the returned data matches the order of the data from the feed and in the table.
Example: getTicks with select
select exchTime,sym,price from getTicks .[!]flip(
(`symList ; `7203.T);
(`dataType ; `rawDepth);
(`startDate ; 2019.04.04);
(`endDate ; 2019.04.25);
(`startTime ; 09:36:20);
(`endTime ; 14:59:59);
(`timeZone ; `Japan);
(`time ; `exchangeTime);
(`assetClass; `equity);
(`columns ; `price)
)
The results of these examples are tabulated below, with the select results on the left. Grayed rows have matching timestamps; the middle column shows where the order of the data is different
Parameters¶
List of identifiers¶
Key idList required Value current instrument ID as symbol atom or vector
Examples `VOD.L `VOD.L`6758.T
Data type¶
Key dataType required Value data subtype of the asset class as a symbol atom
Example `taqWide
Start date¶
Key startDate required Value start date of query time period as date atom Valid YYYY.MM.DD
Example 2020.03.29
Used in combination with startTime to specify the period of the query.
End date¶
Key endDate required Value end date of query time period as date atom Valid YYYY.MM.DD
Example 2020.03.29
Used in combination with endTime to specify the period of the query.
Start time¶
Key startTime optional Value start time of query time period as temporal atom Valid HH.MM[:SS][.sssssssss]
Examples 12:34 12:34:56 12:34:56.777777777 Default 00:00:00.000000000
Refinery uses all the tick data from the start to end dates and times (inclusive). This means that pre/after-market hours data will be included, unless specifically filtered out, when a query period falls outside regular session hours.
This is now
The q timespan shortcut .z.n can be used to refer to the current time (with nanoseconds) in GMT.
The syntax .z.nhh:[mm[:ss]] can be used for the current time less the specified offset.
End time¶
For queries other than depth snapshots:
Key endTime optional Value end time of query time period as temporal atom Valid HH.MM[:SS][.sssssssss]
Examples 12:34 12:34:56 12:34:56.777777777 Default 23:59:59.999999999
Refinery uses all the tick data from the start to end dates and times (inclusive). This means that pre/after-market hours data will be included, unless specifically filtered out, when a query period falls outside regular session hours.
Note
`startTime != `endTime where temporality is slice or unspecified.
Time zone management¶
You can specify a time zone to qualify the period of a query or adjust its results.
Some time zones:
Asia/Tokyo Japan Standard Time GMT+9:00
ECT European Central Time GMT+1:00
EET Eastern European Time GMT+2:00
GMT Greenwich Mean Time GMT
UTC Universal Coordinated Time GMT
Time zone/s may be specified as either
- a single time zone
- input and output time zones
Input and output time zones¶
Key inputTimeZone optional outputTimeZone Values timezones as symbol atoms
Default none
Specify these parameters as a pair, e.g.
getTicks .[!]flip(
(`symList ; `SKT.NZ);
(`dataType ; `trade);
(`startDate ; 2020.03.31);
(`endDate ; 2020.03.31);
(`inputTimeZone ; `NZ);
(`outputTimeZone; `EST);
(`assetClass ; `equity);
(`temporality ; `continuous);
(`filterRule ; `)
)
Time zone¶
Key timeZone optional Value timezone as a symbol atom
Example `$"Europe/London"
Default none
Data are returned aligned with the specified time zone. It is feasible to send date-time parameters, inform the system these are US/Eastern time, and request the data returned in Europe/London (London local) time. This same operation can also be performed using time zones.
When querying in a q console, cast the time zone name from a string.
Example using time zone:
getTicks .[!]flip(
(`symList ; `VOD.L);
(`dataType ; `trade);
(`assetClass; `equity);
(`startDate ; 2016.05.19);
(`endDate ; 2016.05.19);
(`timeZone ; `$"Europe/London")
)
Example using input/output time zones:
getTicks .[!]flip(
(`symList ; `AZN.L);
(`dataType ; `trade);
(`assetClass ; `equity);
(`startDate ; 2016.05.20);
(`endDate ; 2016.05.20);
(`inputTimeZone ; `$"US/Eastern");
(`outputTimeZone; `$"Europe/London")
)
Temporality (windowing)¶
Key temporality optional Value temporality as a symbol atom Valid continuous slice snapshot
Example `snapshot
Default `slice
The system can return data either using continuous or windowed (slice) time periods. The following diagrams illustrate the difference. If you request data between 02:00 and 04:00 for a three-day period using a time slice, then the following data within the box below would be returned.
If you specify the same time periods and request a continuous time period, the following would be returned.
Snapshot temporality may only be passed with the depth data type. This returns the image of the order book at the given times. When setting a start time, the following quote snapshot will be returned. When setting an end time, the preceding snapshot will be returned. An array of timestamps may be handed when doing so.
Slice temporality is incompatible with the depth data type. When querying with depth data type, temporality will default to continuous.
Racking and filling¶
| Argument ID: | `fill |
|---|---|
| Argument Type: | Optional |
| Format: | `linear <atom> |
| Datatype: | <symbol> |
| Valid Inputs: | ``zero | `forward | `linear |
| Default | ` |
The fill argument allows you to define how you would like the system to manage ticks with no data in certain fields. If no input is provided for this parameter or if you specify (`), the resulting data will be only that which has values, demonstrated below.
| time | a | b | c |
|---|---|---|---|
| 12:01:00 | 245 | ||
| 12:01:01 | 3.8 | ||
| 12:01:03 | 3.9 | ||
| 12:02:00 | 257 | ||
| 12:02:01 | 4.3 | ||
| 12:03:00 | 187 | ||
| 12:03:30 | 0.2 |
If you specify `zero, the data will be returned zero filled, as can be seen below.
| time | a | b | c |
|---|---|---|---|
| 12:01:00 | 245 | 0 | 0 |
| 12:01:01 | 0 | 3.8 | 0 |
| 12:01:03 | 0 | 3.9 | 0 |
| 12:02:00 | 257 | 0 | 0 |
| 12:02:01 | 0 | 4.3 | 0 |
| 12:03:00 | 187 | 0 | 0 |
| 12:03:30 | 0 | 0 | 0.2 |
If you specify `forward, the data will be returned forward filled, as can be seen below.
| time | a | b | c |
|---|---|---|---|
| 12:01:00 | 245 | ||
| 12:01:01 | 245 | 3.8 | |
| 12:01:03 | 245 | 3.9 | |
| 12:02:00 | 257 | 3.9 | |
| 12:02:01 | 257 | 4.3 | |
| 12:03:00 | 187 | 4.3 | |
| 12:03:30 | 187 | 4.3 | 0.2 |
If you specify `linear, the data will be linearly interpolated, as can be seen below.
| time | a | b | c |
|---|---|---|---|
| 12:01:00 | 245 | 3.75 | 0.2 |
| 12:01:01 | 245.2 | 3.8 | 0.2 |
| 12:01:03 | 245.6 | 3.9 | 0.2 |
| 12:02:00 | 257 | 4.29 | 0.2 |
| 12:02:01 | 255.8 | 4.3 | 0.2 |
| 12:03:00 | 187 | 4.71 | 0.2 |
| 12:03:30 | 152 | 4.91 | 0.2 |
This functionality is particularly useful when pivoting data sets in time-channel-value format. See Argument Quick Reference for examples of getStats.
Identifier altercations¶
Identifier column¶
Key idCol optional Value column name as symbol atom
Examples `col1
Default symCol specified in table YAML
Parameter to specify a column alternative to the pre-configured idCol for the idList parameter to operate on.
Identifier mapping¶
Key idMapping optional Value boolean
Example 1b
Default none
Parameter that allows for symbol column values (singular or multiple) to be mapped to a different symbol(IDs).
Custom filtering¶
Key applyFilter optional Value list of operator/column/value tuples: operator: <> < > = >= <= within in and or ~ not column: column name as a symbol value: numerical, symbol or char atom or vector
Example ((<;`col1;42);(within;`col2;50 100))
Default no filtering
The parameter applies custom filtering to getTicks, getStats and getRef.
getStats
For getStats this is applied to the data pre-aggregation, i.e, using the avgPrice analytic with the applyFilter parameter (>;`price;100) will return the average price of all trades that have a price greater than 100.
If incorrect syntax is provided, an error will be signalled.
Info
Complex applyFilter parameters are fully supported as of version 5.11.0.
Not supported for datatypes: taq, taqWide, depth, and rawDepth.
Column sorting¶
Key sortCols optional Value sort direction and column name as 2-item symbol vector
Example `desc`col1
Default ascending time
Sort direction is either asc or desc. Results can be returned sorted on any column; by default sorted ascending on time. When used with getStats the value can be the name of a sorting function as a symbol atom.
Specific columns¶
Key columns optional Value column name/s as symbol atom or vector
Examples `col1 `col1`col2
Default all columns
Not supported for datatypes: taq, taqWide and depth.
Pivoting¶
| Argument ID: | `pivot |
|---|---|
| Argument Type: | Optional |
| Format: | 1b <atom> |
| Datatype: | <boolean> |
| Valid Inputs: | 1b | 0b |
Behavior
Pivots a table of the format
| time | sym | val |
|---|---|---|
| 12:01:00 | a | 245 |
| 12:01:01 | b | 3.8 |
| 12:01:03 | b | 3.9 |
| 12:02:00 | a | 257 |
| 12:02:01 | b | 4.3 |
| 12:03:00 | a | 187 |
| 12:03:30 | c | 0.2 |
To the format
| time | a | b | c |
|---|---|---|---|
| 12:01:00 | 245 | ||
| 12:01:01 | 3.8 | ||
| 12:01:03 | 3.9 | ||
| 12:02:00 | 257 | ||
| 12:02:01 | 4.3 | ||
| 12:03:00 | 187 | ||
| 12:03:30 | 0.2 |
This functionality can be combined with the `fill parameter to re-sample and interpolate channel-time-value format data.
Note
To use this functionality, the table must be configured in .daas.cfg.pivotSettings, which an administrator can set up through the Refinery CLI.
Do not validate¶
Key doNotValidate optional Value parameter symbol vector
Example `columns
Default none
Disables pre-processing parameter validation checks for the specified parameters. For supported parameters, see Parameter Validation
Limit¶
Key limit optional Value limit as atom and vector(max of 2 non negative numbers)Examples 5 2 5
Default none
The limit parameter is used to restrict the number of records returned in the output. It can be defined in two ways: by specifying a single numeric value to retrieve a fixed number of records from the table, or by using a vector with a maximum of two values. In the vector form, the first value represents the starting point (offset), and the second value defines how many records to return from that point.
KDB foreign keys¶
As of Refinery 5.7.0, KDB native foreign keys are now accepted within getTicks and getStats API calls. Anywhere a column can be entered within an API call, a foreign key column may be used as long as a foreign key link is created first.