Skip to content

getTicks

The getTicks API is used for raw data extraction and preprocessing. It is intended to run on a Data Access Process and retrieve records from a configured database table using a specified dictionary of arguments.

The gw variable below is defined as an IPC connection to the Service Gateway. For example `:insights-qe-gateway:5050 would connect to the query environment gateway within an insights namespace.

Gateway URL

The gw variable below is defined as an IPC connection to the Service Gateway. For example `:insights-qe-gateway:5050 would connect to the query environment gateway within an insights namespace.

// API arguments dictionary.

args: (!) . flip (
    (`table;            `Trade);
    (`startTS;          2023.07.21D00:00:00);
    (`endTS;            2023.07.22D00:00:00)
    );

opts:()!()
last gw(`getTicks;args;`callback;opts);
eventTimestamp                instrumentID exchTime price  volume conditions normalisedCondition tickDirection tradedExchange sequenceNumber accVol tickCount executionID srcSys
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2023.07.21D00:00:00.000000000 AMD                   113.66 43895  ""                                                          1297                            ""                
2023.07.21D00:05:00.000000000 AMD                   112.48 77130  ""                                                          1298                            ""                
2023.07.21D00:10:00.000000000 AMD                   113.72 9567   ""                                                          1299                            ""                
2023.07.21D00:15:00.000000000 AMD                   111.55 39436  ""                                                          1300                            ""                
2023.07.21D00:20:00.000000000 AMD                   118.87 3135   ""                                                          1301                            ""                     
..

Gateway URL

The $INSIGHTS_URL, $INSIGHTS_CLIENT_ID, $INSIGHTS_CLIENT_SECRET variables should point at your kdb Insights install.

INSIGHTS_TOKEN=`curl -s --header "Content-Type: application/x-www-form-urlencoded" \
    -d "grant_type=client_credentials&client_id=$INSIGHTS_CLIENT_ID&client_secret=$INSIGHTS_CLIENT_SECRET" \
    "${INSIGHTS_URL}/auth/realms/insights/protocol/openid-connect/token" | jq -cr ".access_token"`

DATA=`curl -s -X POST "${INSIGHTS_URL}/servicegateway/fsi/getTicks" \
    -H "Content-Type: application/json" \
    -H "Accept: application/json" \
    -H "Authorization: Bearer $INSIGHTS_TOKEN" \
    -d "$(jq -n \
        '{
            table           : "Trade",
            startTS         : "2023.07.21D00:00:00",
            endTS           : "2023.07.22D00:00:00"
        }' | jq -cr .)"`


echo $DATA | jq -cr '.payload'

When issuing an IPC request, synchronous requests return a tuple where the first element is the response payload and the second is the response data. See the asynchronous requests section for how to use the callback argument to get an asynchronous response. See the extended options section for how to use the options like timeouts for your IPC call.

Arguments

name required type default example description
table yes symbol N/A `Trade Name of table to retrieve data from.
startTS yes timestamp -0Wp .z.p-1D Inclusive start time of period of interest. It is recommended that this field is always used to limit scope of data in the response and to maximize query efficiency.
endTS yes timestamp 0Wp .z.p Exclusive end time of period of interest. It is recommended that this field is always used to limit scope of data in the response and to maximize query efficiency.
columns no symbol[] ` `instrumentID`price Specify columns to return.
idList no symbol[] ` `A`B`C List of identifiers to select.
idCol no symbol configurable `instrumentID The identifier column to apply idList to
filter no list () ("<";`price;111) See filter section for more info.
fill no symbol () `forward Will fill the outbound result after aggregation. Using any fill will "rack" the data, giving rows for windows that did not happen. Options are `null`zero`forward`linear`linearStep
temporality no symbol `continuous `slice Sets the range of data in view for each day within the query. Support two types of temporality: `continuous (default) which takes a continuous range of the data, and `slice which returns data within the startTS and endTS dates that is between the times defined in the slice argument.
slice no symbol () (0D12:00:00;0D13:00:00) Sets the time range to grab between each date in the startTS and endTS range when using a temporality of slice.
sortCols no symbol[] () (`desc;`instrumentID) Columns to sort (ascending) result data on.
applyCanCor no boolean 0b 1b Apply cancellations and corrections data to table.
inputTZ no symbol UTC `America/New_York Timezone of startTS and endTS.
outputTZ no symbol UTC `America/New_York Timezone of output timestamp columns.
idMapping no boolean 0b 1b Apply identifier mapping
filterRule no symbol () `myFilter Name of the configured filter to apply to conditions column
timeCol no symbol () `exchTime Use a time column that is not the default parition column.
orderbook no symbol () `plotOrderbookSnapshots Orderbook specific functionality. More info in the Orderbook section of this doc
decPlaces no integer () 2 Decimal places to round float columns to in results. For example, 3 will round to three decimal places.
ref no symbol[] () `Instrument Join the results to a reference data table(s). The reference table(s) must have a foreign key relationship with the queried table, which should be defined in the schema yaml for the queried table.
refFilter no dict/string () "(enlist `Instrument)!enlist enlist[(>;`maturityDate;2050.01.01)]" If joining to reference data, filters to apply on columns in the reference table(s). Must be a dictionary, or a string containing a dictionary with the reference tables as keys and filter triplets for the relevant table as values.

columns

args: (!) . flip (
    (`table;            `Trade);
    (`startTS;          2023.07.21D00:00:00);
    (`endTS;            2023.07.22D00:00:00);
    (`columns;          `instrumentID`price`volume)
    )

last  gw(`getTicks;args;`callback;()!())
eventTimestamp                instrumentID price  volume
--------------------------------------------------------
2023.07.21D00:00:00.000000000 AMD          113.66 43895 
2023.07.21D00:05:00.000000000 AMD          112.48 77130 
2023.07.21D00:10:00.000000000 AMD          113.72 9567  
2023.07.21D00:15:00.000000000 AMD          111.55 39436 
2023.07.21D00:20:00.000000000 AMD          118.87 3135  
..
INSIGHTS_TOKEN=`curl -s --header "Content-Type: application/x-www-form-urlencoded" \
    -d "grant_type=client_credentials&client_id=$INSIGHTS_CLIENT_ID&client_secret=$INSIGHTS_CLIENT_SECRET" \
    "${INSIGHTS_URL}/auth/realms/insights/protocol/openid-connect/token" | jq -cr ".access_token"`

DATA=`curl -s -X POST "${INSIGHTS_URL}/servicegateway/fsi/getTicks" \
    -H "Content-Type: application/json" \
    -H "Accept: application/json" \
    -H "Authorization: Bearer $INSIGHTS_TOKEN" \
    -d "$(jq -n \
        '{
            table           : "Trade",
            startTS         : "2023.07.21D00:00:00",
            endTS           : "2023.07.22D00:00:00",
            columns         : ["instrumentID","price","volume"]
        }' | jq -cr .)"`


echo $DATA | jq -cr '.payload'

idList

List of identifiers to select.

args: (!) . flip (
    (`table;            `Trade);
    (`startTS;          2023.07.21D00:00:00);
    (`endTS;            2023.07.22D00:00:00);
    (`columns;          `instrumentID`price`volume);
    (`idList;           `AMD)
    )

last  gw(`getTicks;args;`callback;()!())
eventTimestamp                instrumentID price  volume
--------------------------------------------------------
2023.07.21D00:00:00.000000000 AMD          113.66 43895 
2023.07.21D00:05:00.000000000 AMD          112.48 77130 
2023.07.21D00:10:00.000000000 AMD          113.72 9567  
2023.07.21D00:15:00.000000000 AMD          111.55 39436 
2023.07.21D00:20:00.000000000 AMD          118.87 3135  
..
INSIGHTS_TOKEN=`curl -s --header "Content-Type: application/x-www-form-urlencoded" \
    -d "grant_type=client_credentials&client_id=$INSIGHTS_CLIENT_ID&client_secret=$INSIGHTS_CLIENT_SECRET" \
    "${INSIGHTS_URL}/auth/realms/insights/protocol/openid-connect/token" | jq -cr ".access_token"`

DATA=`curl -s -X POST "${INSIGHTS_URL}/servicegateway/fsi/getTicks" \
    -H "Content-Type: application/json" \
    -H "Accept: application/json" \
    -H "Authorization: Bearer $INSIGHTS_TOKEN" \
    -d "$(jq -n \
        '{
            table           : "Trade",
            startTS         : "2023.07.21D00:00:00",
            endTS           : "2023.07.22D00:00:00",
            columns         : ["instrumentID","price","volume"],
            idList          : "AMD"
        }' | jq -cr .)"`


echo $DATA | jq -cr '.payload'

idCol

By specifying a different idCol, the idList applies to a different column

args: (!) . flip (
    (`table;            `Trade);
    (`startTS;          2023.07.21D00:00:00);
    (`endTS;            2023.07.22D00:00:00);
    (`columns;          `tradedExchange`instrumentID`price`volume);
    (`idList;           `LSE);
    (`idCol;            `tradedExchange`)
    )

last  gw(`getTicks;args;`callback;()!())
eventTimestamp                tradedExchange instrumentID price  volume
-----------------------------------------------------------------------
2023.07.21D00:00:00.000000000  LSE           AMD          113.66 43895 
2023.07.21D00:05:00.000000000  LSE           AMD          112.48 77130 
2023.07.21D00:07:00.000000000  LSE           VOD          174.72 2512  
2023.07.21D00:10:00.000000000  LSE           AMD          111.55 39436 
2023.07.21D00:11:00.000000000  LSE           VOD          174.87 6132  
..
INSIGHTS_TOKEN=`curl -s --header "Content-Type: application/x-www-form-urlencoded" \
    -d "grant_type=client_credentials&client_id=$INSIGHTS_CLIENT_ID&client_secret=$INSIGHTS_CLIENT_SECRET" \
    "${INSIGHTS_URL}/auth/realms/insights/protocol/openid-connect/token" | jq -cr ".access_token"`

DATA=`curl -s -X POST "${INSIGHTS_URL}/servicegateway/fsi/getTicks" \
    -H "Content-Type: application/json" \
    -H "Accept: application/json" \
    -H "Authorization: Bearer $INSIGHTS_TOKEN" \
    -d "$(jq -n \
        '{
            table           : "Trade",
            startTS         : "2023.07.21D00:00:00",
            endTS           : "2023.07.22D00:00:00",
            columns         : ["tradedExhange","instrumentID","price","volume"],
            idList          : "LSE",
            idCol           : "tradedExchange"
        }' | jq -cr .)"`


echo $DATA | jq -cr '.payload'

filter

The filter argument is used for applying custom filtering to the query. The filters are applied in the order they are defined when selecting from the table. Filters can be specified as symbols, strings or a combination of both.

function parameters example
in Filters data that is not in the list of possible alternatives ("in";"sym";("AAPL";"MSFT") keeps symbols that are either AAPL or MSFT
within Keeps numeric data that is within the bounds of a range using inclusive limits. ("within";"price";(100;200)) keeps prices that are greater than or equal to 100 but less than or equal to 200
< Keeps numeric data that is less than a threshold ("<";"price";100) keeps data that is less than 100
> Keeps numeric data that is greater than a threshold (">";"price";100) keeps data that is greater than 100
<= Keeps numeric data that is less than or equal to a threshold ("<=";"price";100) keeps data that is less than or equal to 100
>= Keeps numeric data that is greater than or equal to a threshold (">=";"price";100) keeps data that is greater than or equal to 100
= Keeps data equal to another value ("=";"sym";"AAPL") keeps only AAPL data
<> Keeps data that is not equal to a value ("<>";"sym";"AAPL") keeps all data that is not AAPL
like Filters string data that matches a simple expression ("like";"sym";"A*") matches any symbols that start with an A

Some examples using filter are shown below;

args: (!) . flip (
    (`table;            `Trade);
    (`startTS;          2023.07.21D00:00:00);
    (`endTS;            2023.07.22D00:00:00);
    (`columns;          `instrumentID`price`volume);
    (`filter;           ("<";`price;111))
    )

last  gw(`getTicks;args;`callback;()!())
eventTimestamp                instrumentID price  volume
--------------------------------------------------------
2023.07.21D02:45:00.000000000 AMD          110.94 51852 
2023.07.21D03:10:00.000000000 AMD          110.54 43580 
2023.07.21D03:35:00.000000000 AMD          110.45 44657 
2023.07.21D03:55:00.000000000 AMD          110.66 60049 
2023.07.21D04:05:00.000000000 AMD          110.19 25382      
..
INSIGHTS_TOKEN=`curl -s --header "Content-Type: application/x-www-form-urlencoded" \
    -d "grant_type=client_credentials&client_id=$INSIGHTS_CLIENT_ID&client_secret=$INSIGHTS_CLIENT_SECRET" \
    "${INSIGHTS_URL}/auth/realms/insights/protocol/openid-connect/token" | jq -cr ".access_token"`

DATA=`curl -s -X POST "${INSIGHTS_URL}/servicegateway/fsi/getTicks" \
    -H "Content-Type: application/json" \
    -H "Accept: application/json" \
    -H "Authorization: Bearer $INSIGHTS_TOKEN" \
    -d "$(jq -n \
        '{
            table           : "Trade",
            startTS         : "2023.07.21D00:00:00",
            endTS           : "2023.07.22D00:00:00",
            columns         : ["instrumentID","price","volume"],
            filter          : ["<","price","111"]
        }' | jq -cr .)"`


echo $DATA | jq -cr '.payload'    

And using within;

args: (!) . flip (
    (`table;            `Trade);
    (`startTS;          2023.07.21D00:00:00);
    (`endTS;            2023.07.22D00:00:00);
    (`columns;          `instrumentID`price`volume);
    (`filter;           (`$"within";`price;(114;115)))
    )

last  gw(`getTicks;args;`callback;()!())
eventTimestamp                instrumentID price  volume
--------------------------------------------------------
2023.07.21D01:50:00.000000000 AMD          114.8  56236 
2023.07.21D01:55:00.000000000 AMD          114.68 30011 
2023.07.21D02:00:00.000000000 AMD          114.94 73717 
2023.07.21D02:25:00.000000000 AMD          114.38 95394 
2023.07.21D03:40:00.000000000 AMD          114.23 93447 
..
INSIGHTS_TOKEN=`curl -s --header "Content-Type: application/x-www-form-urlencoded" \
    -d "grant_type=client_credentials&client_id=$INSIGHTS_CLIENT_ID&client_secret=$INSIGHTS_CLIENT_SECRET" \
    "${INSIGHTS_URL}/auth/realms/insights/protocol/openid-connect/token" | jq -cr ".access_token"`

DATA=`curl -s -X POST "${INSIGHTS_URL}/servicegateway/fsi/getTicks" \
    -H "Content-Type: application/json" \
    -H "Accept: application/json" \
    -H "Authorization: Bearer $INSIGHTS_TOKEN" \
    -d "$(jq -n \
        '{
            table           : "Trade",
            startTS         : "2023.07.21D00:00:00",
            endTS           : "2023.07.22D00:00:00",
            columns         : ["instrumentID","price","volume"],
            filter          : ["within","price",["114","115"]]
        }' | jq -cr .)"`


echo $DATA | jq -cr '.payload'

Alernative Filter Syntax

Filters can also be specified in a string format using the following syntax:

Operator Description Example
= equal instrumentID=BP.B

product=Option
<> not equal eventType<>cancelled
* column value wildcard instrumentID=BP.*
\| or traderID=traderID1\|traderID2

product=Forward\|Future\|Option

broker<>bkrA\|brkB
; and assetClass=EQ;product=Future\|Share

sourceData=ABC*;sourceData<>ABCD
> greater than price>0
< less than quantity<100000
>= greater than price>=0
<= less than quantity<=100000
[blank column value] null traderID<>;side<>
Name Column Filter Key Condition Column Filter Value
Example assetClass = EQ
Description Filter shall be on column "assetClass". Column shall contain values equal (=) to the specified values. Data shall be filtered on where assetClass is equal to "EQ". It will filter out rows where this condition is not true

Some examples using filter in the string format are shown below;

args: (!) . flip (
    (`table;            `Trade);
    (`startTS;          2023.07.21D00:00:00);
    (`endTS;            2023.07.22D00:00:00);
    (`columns;          `instrumentID`price`volume);
    (`filter;           "price<111")
    )

last  gw(`getTicks;args;`callback;()!())
eventTimestamp                instrumentID price  volume
--------------------------------------------------------
2023.07.21D02:45:00.000000000 AMD          110.94 51852 
2023.07.21D03:10:00.000000000 AMD          110.54 43580 
2023.07.21D03:35:00.000000000 AMD          110.45 44657 
2023.07.21D03:55:00.000000000 AMD          110.66 60049 
2023.07.21D04:05:00.000000000 AMD          110.19 25382      
..
INSIGHTS_TOKEN=`curl -s --header "Content-Type: application/x-www-form-urlencoded" \
    -d "grant_type=client_credentials&client_id=$INSIGHTS_CLIENT_ID&client_secret=$INSIGHTS_CLIENT_SECRET" \
    "${INSIGHTS_URL}/auth/realms/insights/protocol/openid-connect/token" | jq -cr ".access_token"`

DATA=`curl -s -X POST "${INSIGHTS_URL}/servicegateway/fsi/getTicks" \
    -H "Content-Type: application/json" \
    -H "Accept: application/json" \
    -H "Authorization: Bearer $INSIGHTS_TOKEN" \
    -d "$(jq -n \
        '{
            table           : "Trade",
            startTS         : "2023.07.21D00:00:00",
            endTS           : "2023.07.22D00:00:00",
            columns         : ["instrumentID","price","volume"],
            filter          : "price<111"
        }' | jq -cr .)"`


echo $DATA | jq -cr '.payload'    

Can replicate within functionality using >= and <=;

args: (!) . flip (
    (`table;            `Trade);
    (`startTS;          2023.07.21D00:00:00);
    (`endTS;            2023.07.22D00:00:00);
    (`columns;          `instrumentID`price`volume);
    (`filter;           "price>=114;price<=115")
    )

last  gw(`getTicks;args;`callback;()!())
eventTimestamp                instrumentID price  volume
--------------------------------------------------------
2023.07.21D01:50:00.000000000 AMD          114.8  56236 
2023.07.21D01:55:00.000000000 AMD          114.68 30011 
2023.07.21D02:00:00.000000000 AMD          114.94 73717 
2023.07.21D02:25:00.000000000 AMD          114.38 95394 
2023.07.21D03:40:00.000000000 AMD          114.23 93447 
..
INSIGHTS_TOKEN=`curl -s --header "Content-Type: application/x-www-form-urlencoded" \
    -d "grant_type=client_credentials&client_id=$INSIGHTS_CLIENT_ID&client_secret=$INSIGHTS_CLIENT_SECRET" \
    "${INSIGHTS_URL}/auth/realms/insights/protocol/openid-connect/token" | jq -cr ".access_token"`

DATA=`curl -s -X POST "${INSIGHTS_URL}/servicegateway/fsi/getTicks" \
    -H "Content-Type: application/json" \
    -H "Accept: application/json" \
    -H "Authorization: Bearer $INSIGHTS_TOKEN" \
    -d "$(jq -n \
        '{
            table           : "Trade",
            startTS         : "2023.07.21D00:00:00",
            endTS           : "2023.07.22D00:00:00",
            columns         : ["instrumentID","price","volume"],
            filter          : "price>=114;price<=115"
        }' | jq -cr .)"`


echo $DATA | jq -cr '.payload'

fill

The fill parameter can be used to specify how the to handle null values. It takes a single symbol which can be one of `null`zero`forward`linear`linearStep.

  • null - Replace empty values with nulls.
  • zero - Replace null values with a 0 value of the appropriate type.
  • forward - Replace null values with the previous non-null value.
  • linear - Linear interpolation according to time column of table.
  • linearStep - Linear interpolation according to row index.

The below example highlights the difference between how linear and linearStep fills work.

time price linear linearStep
0 0 0 0
5 2.5 3.33
10 5 6.66
20 10 10 10

Example call using zero;

args: (!) . flip (
    (`table;            `Trade);
    (`startTS;          2023.07.21D00:00:00);
    (`endTS;            2023.07.22D00:00:00);
    (`columns;          `instrumentID`price`volume`accVol);
    (`fill;             `zero)
    )

last  gw(`getTicks;args;`callback;()!())
eventTimestamp                instrumentID price  volume accVol
---------------------------------------------------------------
2023.07.21D00:00:00.000000000 AMD          113.66 43895  0     
2023.07.21D00:05:00.000000000 AMD          112.48 77130  0     
2023.07.21D00:10:00.000000000 AMD          113.72 9567   0     
2023.07.21D00:15:00.000000000 AMD          111.55 39436  0     
2023.07.21D00:20:00.000000000 AMD          118.87 3135   0                        
..
INSIGHTS_TOKEN=`curl -s --header "Content-Type: application/x-www-form-urlencoded" \
    -d "grant_type=client_credentials&client_id=$INSIGHTS_CLIENT_ID&client_secret=$INSIGHTS_CLIENT_SECRET" \
    "${INSIGHTS_URL}/auth/realms/insights/protocol/openid-connect/token" | jq -cr ".access_token"`

DATA=`curl -s -X POST "${INSIGHTS_URL}/servicegateway/fsi/getTicks" \
    -H "Content-Type: application/json" \
    -H "Accept: application/json" \
    -H "Authorization: Bearer $INSIGHTS_TOKEN" \
    -d "$(jq -n \
        '{
            table           : "Trade",
            startTS         : "2023.07.21D00:00:00",
            endTS           : "2023.07.22D00:00:00",
            columns         : ["instrumentID","price","volume","accVol"],
            fill            : "zero"
        }' | jq -cr .)"`


echo $DATA | jq -cr '.payload'    

temporality/slice

There are two types of temporality currently supported, and each determines the way the startTS and endTS arguments are interpreted. The default value is continuous and when specified getTicks return a continuous chunk of data between the specified startTS and endTS arguments.

When a slice temporality is specified, the API returns data between the values specified in the slice argument, for each date within the startTS and endTS timestamp.

For example, if you request data between 02:00 and 04:00 for a three day period using a time slice, then the following data would be returned:

time day 1 day 2 day3
00:00:00 1.10 2.20 3.30
01:00:00 1.11 2.21 3.31
02:00:00 1.12 2.22 3.32
03:00:00 1.13 2.23 3.33
04:00:00 1.14 2.24 3.34
args: (!) . flip (
    (`table;            `Trade);
    (`startTS;          2023.07.21D00:00:00);
    (`endTS;            2023.07.22D00:00:00);
    (`columns;          `instrumentID`price`volume);
    (`temporality;      `slice);
    (`slice;            (0D12:00:00;0D13:00:00))
    )

last  gw(`getTicks;args;`callback;()!())
eventTimestamp                instrumentID price  volume
--------------------------------------------------------
2023.07.21D12:00:00.000000000 AMD          115.46 18847 
2023.07.21D12:00:00.000000001 AMD          114.39 75208 
2023.07.21D12:00:00.000000002 AMD          119.67 22109 
2023.07.21D12:05:00.000000000 AMD          117.62 46386 
2023.07.21D12:10:00.000000000 AMD          118.12 40625   
..
INSIGHTS_TOKEN=`curl -s --header "Content-Type: application/x-www-form-urlencoded" \
    -d "grant_type=client_credentials&client_id=$INSIGHTS_CLIENT_ID&client_secret=$INSIGHTS_CLIENT_SECRET" \
    "${INSIGHTS_URL}/auth/realms/insights/protocol/openid-connect/token" | jq -cr ".access_token"`

DATA=`curl -s -X POST "${INSIGHTS_URL}/servicegateway/fsi/getTicks" \
    -H "Content-Type: application/json" \
    -H "Accept: application/json" \
    -H "Authorization: Bearer $INSIGHTS_TOKEN" \
    -d "$(jq -n \
        '{
            table           : "Trade",
            startTS         : "2023.07.21D00:00:00",
            endTS           : "2023.07.22D00:00:00",
            columns         : ["instrumentID","price","volume"],
            temporality     : "slice",
            slice           : ["0D12:00:00","0D13:00:00"]
        }' | jq -cr .)"`


echo $DATA | jq -cr '.payload'    

sortCols

Specifies the columns to sort before returning the response. You can specify either ascending (asc) or descending (desc) order.

args: (!) . flip (
    (`table;            `Trade);
    (`startTS;          2023.07.21D00:00:00);
    (`endTS;            2023.07.22D00:00:00);
    (`columns;          `instrumentID`price`volume);
    (`sortCols;         (`desc;`price))
    )

last  gw(`getTicks;args;`callback;()!())
eventTimestamp                instrumentID price  volume
--------------------------------------------------------
2023.07.21D09:35:00.000000000 AMD          119.99 77410 
2023.07.21D09:35:00.000000000 AMD          119.99 77410 
2023.07.21D08:10:00.000000000 AMD          119.98 35635 
2023.07.21D08:10:00.000000000 AMD          119.98 35635 
2023.07.21D12:59:59.999999998 AMD          119.96 46373 
..
INSIGHTS_TOKEN=`curl -s --header "Content-Type: application/x-www-form-urlencoded" \
    -d "grant_type=client_credentials&client_id=$INSIGHTS_CLIENT_ID&client_secret=$INSIGHTS_CLIENT_SECRET" \
    "${INSIGHTS_URL}/auth/realms/insights/protocol/openid-connect/token" | jq -cr ".access_token"`

DATA=`curl -s -X POST "${INSIGHTS_URL}/servicegateway/fsi/getTicks" \
    -H "Content-Type: application/json" \
    -H "Accept: application/json" \
    -H "Authorization: Bearer $INSIGHTS_TOKEN" \
    -d "$(jq -n \
        '{
            table           : "Trade",
            startTS         : "2023.07.21D00:00:00",
            endTS           : "2023.07.22D00:00:00",
            columns         : ["instrumentID","price","volume"],
            sortCols        : ["desc","price"]
        }' | jq -cr .)"`


echo $DATA | jq -cr '.payload'

applyCanCor

args: (!) . flip (
    (`table;            `Trade);
    (`startTS;          2023.07.21D00:00:00);
    (`endTS;            2023.07.22D00:00:00);
    (`columns;          `instrumentID`price`volume);
    (`applyCanCor;      1b)
    )

last  gw(`getTicks;args;`callback;()!())
eventTimestamp                instrumentID price  volume
--------------------------------------------------------
2023.07.21D00:00:00.000000000 AMD          117.42 26845 
2023.07.21D00:05:00.000000000 AMD          114.42 63689 
2023.07.21D00:10:00.000000000 AMD          110.43 34964 
2023.07.21D00:15:00.000000000 AMD          111.55 39436 
2023.07.21D00:20:00.000000000 AMD          118.87 3135  
..
INSIGHTS_TOKEN=`curl -s --header "Content-Type: application/x-www-form-urlencoded" \
    -d "grant_type=client_credentials&client_id=$INSIGHTS_CLIENT_ID&client_secret=$INSIGHTS_CLIENT_SECRET" \
    "${INSIGHTS_URL}/auth/realms/insights/protocol/openid-connect/token" | jq -cr ".access_token"`

DATA=`curl -s -X POST "${INSIGHTS_URL}/servicegateway/fsi/getTicks" \
    -H "Content-Type: application/json" \
    -H "Accept: application/json" \
    -H "Authorization: Bearer $INSIGHTS_TOKEN" \
    -d "$(jq -n \
        '{
            table           : "Trade",
            startTS         : "2023.07.21D00:00:00",
            endTS           : "2023.07.2D00:00:00",
            columns         : ["instrumentID","price","volume"],
            applyCanCor     : "1b"
        }' | jq -cr .)"`


echo $DATA | jq -cr '.payload'

inputTZ/outputTZ

Timezone of input (startTS and endTS) and the output.

args: (!) . flip (
    (`table;            `Trade);
    (`startTS;          2023.07.21D00:00:00);
    (`endTS;            2023.07.22D00:00:00);
    (`columns;          `instrumentID`price`volume);
    (`inputTZ;          `$"America/New_York");
    (`outputTZ;         `$"America/New_York")
    )

last  gw(`getTicks;args;`callback;()!())
eventTimestamp                instrumentID price  volume
--------------------------------------------------------
2023.07.21D00:00:00.000000000 AMD          115.22 65481 
2023.07.21D00:05:00.000000000 AMD          110.19 25382 
2023.07.21D00:10:00.000000000 AMD          111.51 64714 
2023.07.21D00:15:00.000000000 AMD          115.71 60447 
2023.07.21D00:20:00.000000000 AMD          110.72 24078          
..
INSIGHTS_TOKEN=`curl -s --header "Content-Type: application/x-www-form-urlencoded" \
    -d "grant_type=client_credentials&client_id=$INSIGHTS_CLIENT_ID&client_secret=$INSIGHTS_CLIENT_SECRET" \
    "${INSIGHTS_URL}/auth/realms/insights/protocol/openid-connect/token" | jq -cr ".access_token"`

DATA=`curl -s -X POST "${INSIGHTS_URL}/servicegateway/fsi/getTicks" \
    -H "Content-Type: application/json" \
    -H "Accept: application/json" \
    -H "Authorization: Bearer $INSIGHTS_TOKEN" \
    -d "$(jq -n \
        '{
            table           : "Trade",
            startTS         : "2023.07.21D00:00:00",
            endTS           : "2023.07.22D00:00:00",
            columns         : ["instrumentID","price","volume"],
            inputTZ         : "AmericaNew_York",
            outputTZ        : "AmericaNew_York"
        }' | jq -cr .)"`


echo $DATA | jq -cr '.payload'    

Note

When using an inputTZ timezone that observes daylight savings time, the system cannot robustly handle attempts to select data specifically within the non-existant or repeated local hour.

For example, on the night in March in New York where the clock skips forward from 1:59 to 03:00, attempting to select a data bucket that begins or ends within those time values may result in unexpected data returning. Our tests confirm that a work-around is using a startTS/endTS value that wholely surrounds the problematic local time range, that is a range at least as wide as startTS=01:59 to endTS=03:00.

Similarly for example, on the night in November in New York where the clock repeats an hour going from 1:59 back to 01:00, attempting to select a data bucket that begins or ends within those time values may result in unexpected data returning. Our tests confirm that a work-around is using a startTS/endTS value that wholely surrounds the problematic local time range, that is a range at least as wide as startTS=00:59 to endTS=02:00.

filterRule

If filter rules are configured, then the argument can be used to filter data based upon condition codes. This enables repeatable filters without extremely complex `filter` arguments.

args: (!) . flip (
    (`table;            `Trade);
    (`startTS;          2023.07.21D00:00:00);
    (`endTS;            2023.07.22D00:00:00);
    (`columns;          `instrumentID`price`volume`condition);
    (`idList;           `AMD);
    (`filterRule;       `myFilter)
    )

last  gw(`getTicks;args;`callback;()!())
eventTimestamp                instrumentID price  volume  condition
-------------------------------------------------------------------
2023.07.21D00:00:00.000000000 AMD          113.66 43895   "A||N||"
2023.07.21D00:05:00.000000000 AMD          112.48 77130   "A||||"
2023.07.21D00:20:00.000000000 AMD          118.87 3135    "A||||"
..
INSIGHTS_TOKEN=`curl -s --header "Content-Type: application/x-www-form-urlencoded" \
    -d "grant_type=client_credentials&client_id=$INSIGHTS_CLIENT_ID&client_secret=$INSIGHTS_CLIENT_SECRET" \
    "${INSIGHTS_URL}/auth/realms/insights/protocol/openid-connect/token" | jq -cr ".access_token"`

DATA=`curl -s -X POST "${INSIGHTS_URL}/servicegateway/fsi/getTicks" \
    -H "Content-Type: application/json" \
    -H "Accept: application/json" \
    -H "Authorization: Bearer $INSIGHTS_TOKEN" \
    -d "$(jq -n \
        '{
            table           : "Trade",
            startTS         : "2023.07.21D00:00:00",
            endTS           : "2023.07.22D00:00:00",
            columns         : ["instrumentID","price","volume"],
            idList          : "AMD",
            filterRule      : "myFilter"
        }' | jq -cr .)"`


echo $DATA | jq -cr '.payload'

idMapping

If an IDMap is configured, then the idMapping parameter can be used to query by custom symbology.

In the example below, the IDMap has been configured to map a to AMD

args: (!) . flip (
    (`table;            `Trade);
    (`startTS;          2023.07.21D00:00:00);
    (`endTS;            2023.07.22D00:00:00);
    (`columns;          `instrumentID`price`volume`condition);
    (`idList;           `a);
    (`idMapping;        1b)
    )

last  gw(`getTicks;args;`callback;()!())
eventTimestamp                instrumentID price  volume
--------------------------------------------------------
2023.07.21D00:00:00.000000000 AMD          113.66 43895 
2023.07.21D00:05:00.000000000 AMD          112.48 77130 
2023.07.21D00:10:00.000000000 AMD          113.72 9567  
2023.07.21D00:15:00.000000000 AMD          111.55 39436 
2023.07.21D00:20:00.000000000 AMD          118.87 3135  
..
INSIGHTS_TOKEN=`curl -s --header "Content-Type: application/x-www-form-urlencoded" \
    -d "grant_type=client_credentials&client_id=$INSIGHTS_CLIENT_ID&client_secret=$INSIGHTS_CLIENT_SECRET" \
    "${INSIGHTS_URL}/auth/realms/insights/protocol/openid-connect/token" | jq -cr ".access_token"`

DATA=`curl -s -X POST "${INSIGHTS_URL}/servicegateway/fsi/getTicks" \
    -H "Content-Type: application/json" \
    -H "Accept: application/json" \
    -H "Authorization: Bearer $INSIGHTS_TOKEN" \
    -d "$(jq -n \
        '{
            table           : "Trade",
            startTS         : "2023.07.21D00:00:00",
            endTS           : "2023.07.22D00:00:00",
            columns         : ["instrumentID","price","volume"],
            idList          : "a",
            idMapping       : "1b"
        }' | jq -cr .)"`


echo $DATA | jq -cr '.payload'

timeCol

Use a time column that is not the default partition column.

non partition column time query performance

Note that more partitions may need to be scanned to satisfy the query, so a time buffer is added either side of the window provided. This can be configured (or turned off) to improve performance of alternative timeCol queries, see timeCol time windowing

args: (!) . flip (
    (`table;            `Trade);
    (`startTS;          2023.08.15D00:00:00);
    (`endTS;            2023.08.16D00:00:00);
    (`idList;           `A);
    (`columns;          `exchTime`price`volume);
    (`timeCol;          `exchTime)
    )

eventTimestamp                exchTime                      price    volume
---------------------------------------------------------------------------
2023.08.19D00:00:00.000000000 2023.08.15D00:00:00.000000000 10.29076 1500  
2023.08.19D03:00:00.000000000 2023.08.15D03:00:00.000000000 10.42448 1000  
2023.08.19D06:00:00.000000000 2023.08.15D06:00:00.000000000 10.20321 1700  
2023.08.19D09:00:00.000000000 2023.08.15D09:00:00.000000000 10.01974 1100  
2023.08.19D12:00:00.000000000 2023.08.15D12:00:00.000000000 10.01221 1000  
2023.08.19D15:00:00.000000000 2023.08.15D15:00:00.000000000 10.27259 1400  
2023.08.19D18:00:00.000000000 2023.08.15D18:00:00.000000000 10.60147 1400  
2023.08.19D21:00:00.000000000 2023.08.15D21:00:00.000000000 10.59386 1700
INSIGHTS_TOKEN=`curl -s --header "Content-Type: application/x-www-form-urlencoded" \
    -d "grant_type=client_credentials&client_id=$INSIGHTS_CLIENT_ID&client_secret=$INSIGHTS_CLIENT_SECRET" \
    "${INSIGHTS_URL}/auth/realms/insights/protocol/openid-connect/token" | jq -cr ".access_token"`

DATA=`curl -s -X POST "${INSIGHTS_URL}/servicegateway/fsi/getTicks" \
    -H "Content-Type: application/json" \
    -H "Accept: application/json" \
    -H "Authorization: Bearer $INSIGHTS_TOKEN" \
    -d "$(jq -n \
        '{
            table           : "Trade",
            startTS         : "2023.08.15D00:00:00",
            endTS           : "2023.08.16D00:00:00",
            idList          : "A",
            columns         : ["exchTime","price","volume"],
            timeCol         : "exchTime"
        }' | jq -cr .)"`

echo $DATA | jq -cr '.payload'
[{"eventTimestamp":"2023-08-19T00:00:00.000000000","exchTime":"2023-08-15T00:00:00.000000000","price":10.29076,"volume":1500},

Orderbook

The `orderbook argument in getTicks is primarily used for orderbook-specific logic and is used to present data in the Realtime-Orderbook View

Accepted inputs are `plotOrderbookSnapshots, `instrumentNames and `orderbookUpdatesRefData.

`plotOrderbookSnapshots is used to more easily view OrderbookSnapshot data on a dashboard: - Filters the OrderbookSnapshots table for the max row within the specified time range. - Using that snapshotd data, the following values are calculated: - Calculate values: - bestAsk = min askPrice - bestBid = max bidPrice - maxSize = max (bidSize;askSize) - spread = bestAsk - bestBid - midPoint = (bestBid + bestAsk) / 2

args: (!) . flip (
    (`table;            `OrderbookSnapshots);
    (`startTS;          2024.01.17D12:30:00);
    (`endTS;            2024.01.17D12:40:00);
    (`idList;           `$"F:TFM\\G24");
    (`orderbook;        `plotOrderbookSnapshots)
    );

`instrumentNames is used to enhance an instrument ID dropdown with a more readable instrument name.

args: (!) . flip (
    (`table;            `OrderbookUpdates);
    (`startTS;          2024.01.17D12:30:00);
    (`endTS;            2024.01.17D12:40:00);
    (`idList;           `$"F:TFM\\G24");
    (`scope;            enlist[`assembly]!enlist[`$"fsi-app-ice-orderbook"]);
    (`ref;              `Instrument);
    (`orderbook;        `instrumentNames)
    );

`orderbookUpdatesRefData is used to enhance OrderbookUpdates data with a more readable instrument name instead of instrumentID

args: (!) . flip (
    (`table;            `OrderbookUpdates);
    (`startTS;          2024.01.17D12:30:00);
    (`endTS;            2024.01.17D12:40:00);
    (`idList;           `$"F:TFM\\G24");
    (`scope;            enlist[`assembly]!enlist[`$"fsi-app-ice-orderbook"]);
    (`ref;              `Instrument);
    (`orderbook;        `orderbookUpdatesRefData)
    );

Note

When choosing instrumentNames or orderbookUpdatesRefData the ref argument must be used with a value of Instrument to be able to query the correct reference data.

Relevant COREREF and CROSSREF reference data files must also be ingested in order for this to work correctly. If no reference data is found the original instrumentID field will be displayed as the instrument name