getTicks (finance)¶
Data Services: Get time series data with additional functionality applied
Finance-specific enhancements to getTicks are documented here.
adjustCorAx Corporate Actions applyCanCor Cancellations and corrections assetClass R Asset class customSymbologyMapping Custom symbology mapping filterRule Condition-code filters idType Identifier type numLevels Number of levels returned by depth showFilter Display matched filter rules symList R List of instruments timezone Timezone doNotValidate Disable Parameter Validation
Parameters¶
List of instruments¶
Key symList required Value current instrument ID as symbol atom or vector
Examples `VOD.L `VOD.L`6758.T
If the underlying asset of an ID was historically mapped to a different ID, the system will automatically query using the correct ID for the query time window.
Identifier type¶
Key idType optional Value ID type as a symbol atom Valid commonCode ISIN MIC SEDOL wertpapier CUSIP PILC
Examples `commonCode `CUSIP
Defines the type of the instrument IDs specified in `symList. The purpose of the argument is to support identifier mapping between input IDs and the underlying identifier persisted with the data. If omitted, the system assumes the ID types match the types of the persisted source data.
Asset class¶
Key assetClass required Value asset class as a symbol atom Valid equity foreignExchange future index fixedIncome moneyMarket listedOptions
Example `foreignExchange
Indicates which asset class to query. The IDs in List of instruments must correspond to the asset class. No data is returned for an ID not in the asset class.
For example, `symList`assetClass!`.FTSE`equity will return no data.
Datatype¶
Valid trade quote taq taqWide depth rawDepth OC openInterest auction canCor
| Subtype | Returns |
|---|---|
| trade | trade data |
| quote | level 1 (top of book) quotes |
| taq | Trades And Quotes i.e. trades aligned with the prevailing quote at the time of execution |
| taqWide | trades And quotes i.e. trades aligned with the prevailing quote at the time of execution, with the addition that it shows all columns of the same name in both tables. E.g. time of both the trade and most recent quote. |
| depth | order-book (level 2) data, created from a history book of trades and the trades of the queried time period. When paired with a temporality of snapshot, images of the book can be returned; see Temporality |
| rawDepth | a view of the raw order book data. Queries with legacy L2 tickers will return empty tables as the orderbook is already reconstructed for legacy L2 tickers. (Does not support applyFilter). See below for column definitions. |
| OC | the open/close table.openInterest is valid only for futurescanCor will return cancel and correction orders |
rawDepth columns:
action A add; M modify; D delete
levelTime similar to exchange time: when the change happened
MMId market maker ID
numOrders how many orders sit at that level
condition a condition field
priority priority
seqNum sequence number
indication 0: apply this update (not the end of an atomic)
1: this is the start of a clear cache message
2: this is the end of an atomic update
3: clear the cache but also this is the end of an update
Symbology changes¶
Key trackSym optional Value boolean atom
Examples 1b
Default 0b
Refinery accepts both current and historical instrument identifiers and can track changes to the symbology over time. This functionality is optional and disabled by default.
Condition code filters¶
Key filterRule optional Value filter rule name as a symbol atom
Example `myFilter
The filter rule must already be defined. Dashboard administrators maintain filter rules.
Refinery takes a new approach to condition code management and data filtering on condition codes, adding a logical abstraction layer that allows you to apply named filters to content and cleanse the data returned as required.
All combinations of condition codes are normalized into a condition code column, upon which additional processing can then take place. Filters are set up using a flag to indicate their inclusion or exclusion from a particular filter. Example:
exch cond default auction
-----------------------------
LSE O|A TRUE TRUE
LSE R|N TRUE FALSE
LSE R|O TRUE FALSE
LSE R|S TRUE FALSE
LSE D|A TRUE FALSE
LSE A|U TRUE TRUE
LSE O|M TRUE FALSE
LSE R|T TRUE FALSE
LSE D|N TRUE FALSE
LSE D|S TRUE FALSE
LSE D|o TRUE FALSE
LSE D|O TRUE FALSE
LSE 8835 TRUE FALSE
LSE D|U TRUE FALSE
LSE A TRUE TRUE
LSE O TRUE TRUE
The top row of the table shows a compound condition code, O|A. There are additional similar entries on the bottom two rows (A and then O for LSE). The system treats these independently and inclusion/exclusion criteria must be established for each. Compound conditions occur when more than one condition is sent for a given event.
When you call getTicks, you can specify a filter e.g. default, which will include only trades based on that filter definition and return the relevant data to the user.
Example applying no filter to the data:
getTicks .[!]flip(
(`symList ; `BARC.L);
(`dataType ; `trade);
(`startDate ; 2016.05.24);
(`endDate ; 2016.05.24);
(`startTime ; 06:00:00.000);
(`endTime ; 09:00:00.000);
(`timeZone ; `Europe/London);
(`assetClass ; `equity);
(`temporality; `slice);
(`filterRule ; `)
)
or simply omit the filterRule parameter for the same behavior:
getTicks .[!]flip(
(`symList ; `BARC.L);
(`dataType ; `trade);
(`startDate ; 2016.05.24);
(`endDate ; 2016.05.24);
(`startTime ; 06:00:00.000);
(`endTime ; 09:00:00.000);
(`timeZone ; `Europe/London);
(`assetClass ; `equity);
(`temporality; `slice)
)
| time | sym | price | volume | cond0 | cond1 |
|---|---|---|---|---|---|
| 2016.05.24D08:15:27.146733000 | BARC.L | 173.55 | 464335 | U | U|||||||||||| |
| 2016.05.24D08:15:27.146914000 | BARC.L | 173.45 | 856 | A | A|||||||||||| |
| 2016.05.24D08:15:27.208393000 | BARC.L | 173.5 | 1121 | A | A|||||||||||| |
| 2016.05.24D08:15:29.077089000 | BARC.L | 173.5 | 12000 | N|1006 | N|||||||1006|||| |
| 2016.05.24D08:15:32.025616000 | BARC.L | 173.2731 | 4500 | N|1006 | N|||||||1006|||| |
| 2016.05.24D08:15:33.992784000 | BARC.L | 173.41 | 1200 | N|1006 | N|||||||1006|||| |
| 2016.05.24D08:15:33.992811000 | BARC.L | 173.41 | 5000 | N|1006 | N|||||||1006|||| |
| 2016.05.24D08:15:36.100356000 | BARC.L | 173.4 | 33 | A | A|||||||||||| |
| 2016.05.24D08:15:36.100411000 | BARC.L | 173.4 | 2604 | A | A|||||||||||| |
| 2016.05.24D08:15:36.100441000 | BARC.L | 173.4 | 26 | A | A|||||||||||| |
| 2016.05.24D08:15:36.125427000 | BARC.L | 173.3 | 1197 | A | A|||||||||||| |
| 2016.05.24D08:15:36.152075000 | BARC.L | 173.4 | 1845 | A | A|||||||||||| |
| 2016.05.24D08:15:36.200572000 | BARC.L | 173.3 | 765 | A | A|||||||||||| |
| 2016.05.24D08:15:37.482013000 | BARC.L | 173.3 | 8574 | A | A|||||||||||| |
Example applying an auction filter to the data:
getTicks .[!]flip(
(`symList ; `BARC.L);
(`dataType ; `trade);
(`startDate ; 2016.05.24);
(`endDate ; 2016.05.24);
(`startTime ; 06:00:00.000);
(`endTime ; 09:00:00.000);
(`timeZone ; `Europe/London);
(`assetClass ; `equity);
(`temporality; `slice);
(`filterRule ; `auction)
)
| time | sym | price | volume | cond0 | cond1 |
|---|---|---|---|---|---|
| 2016.05.24D08:15:27.146914000 | BARC.L | 173.45 | 856 | A | A|||||||||||| |
| 2016.05.24D08:15:27.208393000 | BARC.L | 173.5 | 1121 | A | A|||||||||||| |
| 2016.05.24D08:15:36.100356000 | BARC.L | 173.4 | 33 | A | A|||||||||||| |
| 2016.05.24D08:15:36.100411000 | BARC.L | 173.4 | 2604 | A | A|||||||||||| |
| 2016.05.24D08:15:36.100441000 | BARC.L | 173.4 | 26 | A | A|||||||||||| |
| 2016.05.24D08:15:36.125427000 | BARC.L | 173.3 | 1197 | A | A|||||||||||| |
| 2016.05.24D08:15:36.152075000 | BARC.L | 173.4 | 1845 | A | A|||||||||||| |
| 2016.05.24D08:15:36.200572000 | BARC.L | 173.3 | 765 | A | A|||||||||||| |
| 2016.05.24D08:15:37.482013000 | BARC.L | 173.3 | 8574 | A | A|||||||||||| |
| 2016.05.24D08:15:37.482185000 | BARC.L | 173.3 | 751 | A | A|||||||||||| |
| 2016.05.24D08:15:37.482235000 | BARC.L | 173.3 | 1710 | A | A|||||||||||| |
| 2016.05.24D08:15:37.680736000 | BARC.L | 173.45 | 823 | A | A|||||||||||| |
| 2016.05.24D08:15:41.358512000 | BARC.L | 173.25 | 2608 | A | A|||||||||||| |
| 2016.05.24D08:15:41.379601000 | BARC.L | 173.2 | 11160 | A | A|||||||||||| |
Insert vs exchange timestamps¶
Key time optional Value option as symbol atom Valid insertTime exchangeTime
Valid insertTime exchangeTime Example `exchangeTime
Default `insertTime
For an analytics or data request you can specify whether you want the time queried to be the time published by the exchange, or the time it was captured by Refinery.
Exchange times are published into the market data feed by the exchange. These times may be of variable granularity and time zone and the events in the system may not be sorted by exchange time for varying reasons, e.g. packet-ordering issues on transmission through various networks.
The insert time is the time the Refinery’s feed handler captured and unpacked the message from the market data feed. This is the receive time of Refinery and hence all timestamps here are in sorted order – but events are not necessarily in transmitted order from the exchange.
Querying by insert time is faster than by exchange time because the data is sorted and hence optimal search algorithms can be used. Querying by exchange time will be slower because the entire column has to be scanned as the data is not sorted.
Corporate actions¶
Key adjustCorAx optional Value boolean atom Valid insertTime exchangeTime
Example 1b
Default 0b
Specifies whether or not to adjust the returned data for Corporate Actions. If Corporate Action data (such as stock splits, stock dividends or demergers) has been loaded into the system and this parameter is specified as true (1b), then the returned data is adjusted. If this parameter is not specified, then by default the data is not adjusted. For the dates queried, the data will be adjusted accordingly for all future Corporate Actions that have been loaded into the system.
Corporate Actions are applied only to equities.
The adjustCorAx parameter is not supported where the dataType is
taq, OC, depth, rawDepth, or taqWide.
Examples with optional parameters
Cancellations and corrections¶
Key applyCanCor optional Value boolean atom Valid insertTime exchangeTime
Example 1b
Default 0b
The user has the option to apply cancellations and corrections to the data. If selected, this option causes the results to display the updated values with a cancelled/corrected condition code added in the case of getTicks. CanCor is only relevant to (and supported for) trades.
The cancellation or correction is matched to its corresponding trade using either the trade ID or sequence number. This ensures that the correct trade is amended.
If a cancellation or correction has no matching trade in the tick data requested, then nothing will be applied.
Number of levels returned by depth¶
Key numLevels optional Value int atom >1
Example 5
Default 10
When data type is depth, use this to specify the number of levels of depth to be returned. The number of levels available depends on the source data and may vary by exchange. All available levels, up to the specified value of numLevels, are returned.
Display matched filter rules¶
Key showFilter optional Value boolean atom
Example 1b
Default 0b
Returns any filters that would accept that trade.
Significantly reduces performance. Use only for diagnostic purposes.
Custom symbology mapping¶
Key customSymbologyMapping optional Value boolean atom
Example 1b
Default 0b
Custom symbology mapping lets you query data using alternative symbol IDs to those in the database; for example, query the symbol VOD.L as voda. For this, the reference data must be maintained.
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
Examples with required parameters¶
getTicks .[!]flip(
(`symList ; `);
(`dataType ; `trade);
(`startDate ; 2016.05.24);
(`endDate ; 2016.05.24);
(`timeZone ; `$"Europe/London");
(`assetClass; `equity)
)
Returns equity tick data for VOD.L trades from the 24th of May 2016 in London time.
getTicks .[!]flip(
(`symList ; `VOD.L);
(`dataType ; `quote);
(`startDate ; 2016.05.24);
(`endDate ; 2016.05.24);
(`timeZone ; `$"Europe/London");
(`assetClass; `equity)
)
Returns equity tick data for VOD.L quotes from the 24th of May 2016 in London time.
getTicks .[!]flip(
(`symList ; `1BON6);
(`dataType ; `trade);
(`startDate ; 2016.05.24);
(`endDate ; 2016.05.24);
(`timeZone ; `$"Europe/London");
(`assetClass; `future)
)
Returns futures tick data for 1BON6 trades from the 24th of May 2016 in London time.
getTicks .[!]flip(
(`ymList ; `1BON6);
(`ataType ; `quote);
(`tartDate ; 2016.05.24);
(`ndDate ; 2016.05.24);
(`imeZone ; `$"Europe/London");
(`ssetClass; `future)
)
Returns futures tick data for 1BON6 quotes from the 24th of May 2016 in London time.
getTicks .[!]flip(
(`symList ; `NOK=);
(`dataType ; `quote);
(`startDate ; 2016.05.24);
(`endDate ; 2016.05.24);
(`timeZone ; `$"Europe/London");
(`assetClass; `foreignExchange)
)
Returns FX tick data for NOK/USD quotes from the 24th of May 2016 in London time.
getTicks .[!]flip(
(`symList ; `.IBEX);
(`dataType ; `quote);
(`startDate ; 2016.05.24);
(`endDate ; 2016.05.24);
(`timeZone ; `Europe/London);
(`assetClass; `index)
)
Returns indices tick data for .IBEX quotes from the 24th of May 2016 in London time.
getTicks .[!]flip(
(`symList ; `BARC.L);
(`dataType ; `trade);
(`startDate ; 2016.05.24);
(`endDate ; 2016.05.24);
(`startTime ; 0D06:00:00.000000000);
(`endTime ; 0D09:00:00.000000000);
(`timeZone ; `$"Europe/London");
(`assetClass ; `equity);
(`temporality; `slice);
(`filterRule ; `auction)
)
Returns equity tick data for BARC.L auction trades from the 24th of May 2016, between 06:00 and 09:00 in London time.
getTicks .[!]flip(
(`symList ; `CEY.L);
(`dataType ; `OC);
(`startDate ; 2019.04.15);
(`endDate ; 2019.04.29);
(`startTime ; 16:51:53);
(`endTime ; 13:58:31);
(`timeZone ; `Japan);
(`assetClass; `equity)
)
Returns equity tick data for CEY.L open and close table from the 15th of April 2019 till the 29th of April 2019, between 16:51:53 and 13:58:31 in Japanese time zone.
Examples with optional parameters¶
getTicks .[!]flip(
(`symList ;`BARC.L);
(`dataType ;`quote);
(`startDate ;2016.05.24);
(`endDate ;2016.05.24);
(`startTime ;0D06:00:00.000000000);
(`endTime ;0D09:00:00.000000000);
(`timeZone ;`$"Europe/London");
(`assetClass ;`equity);
(`temporality;`slice);
(`filterRule ;`auction)
)
Returns equity tick data for BARC.L auction quotes from the 24th of May 2016, between 06:00 and 09:00 in London time.
getTicks .[!]flip(
(`symList ;`1BON6);
(`dataType ;`trade);
(`startDate ;2016.05.24);
(`endDate ;2016.05.25);
(`startTime ;09:00:00.000);
(`endTime ;10:00:00.000);
(`timeZone ;`$"Europe/London");
(`assetClass ;`future);
(`temporality;`slice);
(`filterRule ;`)
)
Returns futures tick data for 1BON6 trades from an hour slice (09:00-10:00) on the 24th and 25th of May 2016 in London time.
getTicks .[!]flip(
(`symList ;`1BON6);
(`dataType ;`quote);
(`startDate ;2016.05.24);
(`endDate ;2016.05.25);
(`startTime ;09:00:00.000);
(`endTime ;10:00:00.000);
(`timeZone ;`$"Europe/London");
(`assetClass ;`future);
(`temporality;`slice);
(`filterRule ;`)
)
Returns futures tick data for 1BON6 quotes from an hour slice (09:00-10:00) on the 24th and 25th of May 2016 in London time.
getTicks .[!]flip(
(`symList ;`NOK=);
(`dataType ;`quote);
(`startDate ;2016.05.24);
(`endDate ;2016.05.24);
(`startTime ;06:00:00.000);
(`endTime ;09:00:00.000);
(`timeZone ;`$"Europe/London");
(`assetClass ;`foreignExchange);
(`temporality;`slice);
(`filterRule ;`)
)
Returns FX tick data for NOK/USD quotes between 06:00 and 09:00 on 24th of May 2016 in London time.
getTicks .[!]flip(
(`symList ;`.IBEX);
(`dataType ;`quote);
(`startDate ;2016.05.24);
(`endDate ;2016.05.24);
(`startTime ;00:00:00.000);
(`endTime ;14:00:00.000);
(`timeZone ;`$"Europe/London");
(`assetClass ;`index);
(`temporality;`slice);
(`filterRule ;`)
)
Returns indices tick data for .IBEX quotes between 06:00 and 09:00 on the 24th of May 2016 in London time.
getTicks .[!]flip(
(`symList ;`VOD.L);
(`dataType ;`quote);
(`startDate ;2016.05.24);
(`endDate ;2016.05.25);
(`startTime ;12:00:00.000);
(`endTime ;12:01:00.000);
(`timeZone ;`$"Europe/London");
(`assetClass ;`equity);
(`temporality;`slice);
(`filterRule ;`default)
)
Returns equity tick data for VOD.L quotes from a minute slice (12:00 – 12:01) on the 24th and 25th of May 2016 in London time.
getTicks .[!]flip(
(`symList ;`NOK=);
(`dataType ;`quote);
(`startDate ;2016.05.12);
(`endDate ;2020.03.31);
(`startTime ;08:00:00.000);
(`endTime ;10:00:00.000);
(`inputTimeZone ;`GMT);
(`outputTimeZone;`Hongkong);
(`assetClass ;`foreignExchange);
(`temporality ;`slice);
(`filterRule ;`)
)
Returns FX tick data for NOK/USD quotes from a two-hour slice (08:00 – 10:00) between the 24th of May 2016 and the current date (.z.d) in London time.
getTicks .[!]flip(
(`symList ;`VOD.L);
(`dataType ;`depth);
(`startDate ;2020.03.31);
(`endDate ;2020.03.31);
(`startTime ;08:00:00.000);
(`endTime ;10:00:00.000);
(`inputTimeZone ;`GMT);
(`outputTimeZone;`GMT);
(`assetClass ;`equity);
(`temporality ;`continuous);
(`numLevels ;5)
)
Returns Level-2 equity tick data for VOD.L between 08:00 and 10:00 on today’s date. 5 levels of depth are returned in GMT time zone.
getTicks .[!]flip(
(`symList ;`VOD.L);
(`dataType ;`depth);
(`startDate ;2020.03.31);
(`endDate ;2020.03.31);
(`endTime ;2020.03.31D10:00:00.000000000);
(`inputTimeZone ;`GMT);
(`outputTimeZone;`GMT);
(`assetClass ;`equity);
(`temporality ;`snapshot);
(`numLevels ;5)
)
Returns snapshot of Level 2 equity order book for VOD.L. 5 levels of depth are returned in GMT time zone. The snapshot is taken from just after 10:00 on today’s date.
getTicks .[!]flip(
(`symList ;`VOD.L);
(`dataType ;`depth);
(`startDate ;2020.03.31);
(`endDate ;2020.03.31);
(`endTime ;2020.03.31D11:00:00.000000000);
(`inputTimeZone ;`GMT);
(`outputTimeZone;`GMT);
(`assetClass ;`equity);
(`temporality ;`snapshot);
(`numLevels ;5)
)
Returns snapshots of Level-2 equity order book for VOD.L. 5 levels of depth are returned in GMT time zone. The snapshots are taken from just after 11:00 on today's date.
Two getTicks calls, one with a Select statement, one without:
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)
)
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)
)
Both return the same Level-2 equity order book data between 2019.04.04 and 2019.04.25. However, the order of the data returned may be different for the Select statement function.
getTicks .[!]flip(
(`symList ;`BARC.L);
(`dataType ;`quote);
(`startDate ;2016.05.24);
(`endDate ;2016.05.24);
(`startTime ;0D06:00:00.000000000);
(`endTime ;0D09:00:00.000000000);
(`timeZone ;`$"Europe/London");
(`assetClass ;`equity);
(`temporality ;`slice);
(`filterRule ;`auction);
(`columns ;`askQtyAlias);
(`doNotValidate ;`columns)
)
Returns equity tick data for BARC.L auction quotes, limiting columns to askQtyAlias from the 24th of May 2016, between 06:00 and 09:00 in London time. Column validation disabled, i.e. the GW will send the query straight through to the on-disk processes, assuming askQtyAlias exists on-disk.