.fsi.getTicks API¶
This page provides an overview of the .fsi.getTicks API.
Note
The .fsi prefix refers to the namespace for the FSI Accelerator Library. All the available FSI APIs must be called using this namespaced syntax.
The .fsi.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(`.fsi.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.
Refer to the asynchronous requests section for how to use the callback argument to get an asynchronous response.
Refer to 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 | Fills the outbound result after aggregation. Using any fill "racks" 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 partition 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 rounds 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. |
| idTS | no | timestamp | N/A | .z.p | The effective timestamp used to resolve the underlying exchange symbol. Setting this argument applies symbology mapping. |
| idSymbology | no | symbol | N/A | `FGC | Specifies the symbology used for idList. If you omit this argument, the function queries the underlying ticker in the table. |
| applyCorpActions | no | symbol[]/boolean | 0b | `cashDividend | Specifies the corporate action adjustments to apply to the results. Supported values are `cashDividend`split`spinoff`stockDividend`securitySplice. Boolean true applies the entire list. |
| adjustTime | no | timestamp | endTS | .z.p | The time as of which the values are adjusted. The function applies the specified corporate actions that occur between each tick's timestamp and the adjustment time. |
| adjustSize | no | symbol[]/boolean | 1b | `volume | Column names of size type to be adjusted for corporate actions. Boolean true will apply to all columns with "size" in the column name. |
| adjustPrice | no | symbol[]/boolean | 1b | `ask | Column names of price type to be adjusted for corporate actions. Boolean true will apply to all columns with "price" in the column name. |
columns¶
Click the corresponding tab to select between q and REST.
args: (!) . flip (
(`table; `Trade);
(`startTS; 2023.07.21D00:00:00);
(`endTS; 2023.07.22D00:00:00);
(`columns; `instrumentID`price`volume)
)
last gw(`.fsi.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. Click the corresponding tab to select between q and REST.
args: (!) . flip (
(`table; `Trade);
(`startTS; 2023.07.21D00:00:00);
(`endTS; 2023.07.22D00:00:00);
(`columns; `instrumentID`price`volume);
(`idList; `AMD)
)
last gw(`.fsi.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(`.fsi.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 : ["tradedExchange","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(`.fsi.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(`.fsi.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'
Alternative filter syntax¶
Filters can also be specified in a string format using the following syntax:
| Operator | Description | Example |
|---|---|---|
= |
equal | instrumentID=BP.Bproduct=Option |
<> |
not equal | eventType<>cancelled |
* |
column value wildcard | instrumentID=BP.* |
\| |
or | traderID=traderID1\|traderID2product=Forward\|Future\|Optionbroker<>bkrA\|brkB |
; |
and | assetClass=EQ;product=Future\|SharesourceData=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 filters 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(`.fsi.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(`.fsi.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(`.fsi.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, .fsi.getTicks returns 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 | Day 3 |
|---|---|---|---|
| 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(`.fsi.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(`.fsi.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(`.fsi.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.22D00:00:00",
columns : ["instrumentID","price","volume"],
sortCols : ["desc","price"]
}' | 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(`.fsi.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-existent 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 wholly 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 wholly 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(`.fsi.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(`.fsi.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. Refer to timeCol time windowing for more details.
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 .fsi.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 is displayed as the instrument name.
idTS/idSymbology¶
Specify the idTS argument to apply symbology resolution and continuity to the queried data.
To use this functionality, add the required reference data to the Symbology table. For the required format and examples, see Symbology reference data.
You must also use the FSI_DATA_SYMBOLOGY environment variable to configure the symbology of the underlying ticker in the queried database. For configuration instructions, see Symbology resolution and continuity.
The idTS argument identifies the effective timestamp to use for resolving the underlying exchange symbol. Set it to a time at which the symbols in idList are valid.
The idSymbology argument specifies the symbology being used for idList. Not specifying this argument means the query will use the underlying ticker on the queried table.
In the following example, idSymbology is set to FGC. This value indicates that the identifiers in idList use FGC (Composite Financial Instrument Global Identifier) symbology.
args: (!) . flip (
(`table; `Trade);
(`startTS; 2017.10.08D03:00:00.000);
(`endTS; 2017.12.01D22:00:00.000);
(`idList; `BBG002CV4Q74`BBG006L2XW01);
(`idTS; .z.p);
(`idSymbology; `FGC)
)
eventTimestamp instrumentID price sourceId
---------------------------------------------------------
2017.10.08D05:00:00.000000000 BBG002CV4Q74 15 CRCO
2017.10.08D06:00:00.000000000 BBG006L2XW01 20 PTCO
2017.10.09D01:00:00.000000000 BBG006L2XW01 22 PTCO
2017.10.09D02:30:00.000000000 BBG002CV4Q74 23.5 CRCO
2017.10.10D00:30:00.000000000 BBG006L2XW01 23.8 PTCOD
2017.10.10D01:10:00.000000000 BBG002CV4Q74 24 CRCO
2017.10.10D01:30:00.000000000 BBG002CV4Q74 24.2 CRCO
2017.10.10D02:10:00.000000000 BBG006L2XW01 24.5 PTCOD
2017.11.05D07:00:00.000000000 BBG002CV4Q74 25 CRCO
2017.11.06D05:00:00.000000000 BBG006L2XW01 26 PTCOD
2017.11.06D14:00:00.000000000 BBG002CV4Q74 28 CRCO
2017.11.06D15:00:00.000000000 BBG006L2XW01 28.7 PTCOD
2017.11.25D10:00:00.000000000 BBG002CV4Q74 29 BTOP
2017.11.26D10:30:00.000000000 BBG006L2XW01 31 PTCO
2017.11.26D17:30:00.000000000 BBG006L2XW01 47 PTCO
2017.11.26D19:20:00.000000000 BBG002CV4Q74 48 BTOP
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 : ["BBG002CV4Q74","BBG006L2XW01"],
idTS : "2026.09.09D00:00:00",
idSymbology : "FGC"
}' | jq -cr .)"`
echo $DATA | jq -cr '.payload'
applyCorpActions/adjustTime/adjustSize/adjustPrice¶
Specify the applyCorpActions argument to apply corporate action adjustments to the queried data.
To use this functionality, add the required reference data to the CorpActions table. For the required format and examples, see Corporate action reference data.
Use the following arguments to configure the adjustments:
-
applyCorpActionsspecifies the types of corporate action adjustments to apply. You can specify one or more of the following values:cashDividend,split,spinoff,stockDividend, andsecuritySplice. A boolean value of true applies the entire list. -
adjustTimespecifies the cutoff time for corporate action adjustments. The function applies corporate actions of the specified types that occur between each tick's timestamp andadjustTime. If you omit this argument, the function uses the query'sendTSvalue. -
adjustSizespecifies the size columns to adjust. Provide a list of column names, set the value totrueto adjust every column withsizein its name, or set it tofalseto disable size adjustments. The default istrue. -
adjustPricespecifies the columns to be adjusted for price according to the specified corporate actions. Provide a list of column names, set the value totrueto adjust every column withpricein its name, or set it tofalseto disable price adjustments. The default istrue.
You can configure the corporate action types that applyCorpActions supports and set the default adjustSize and adjustPrice values for each table. For more information, see Corporate actions.
args: (!) . flip (
(`table; `Trade);
(`startTS; 2017.10.08D03:00:00.000);
(`endTS; 2017.10.08D03:00:05.000);
(`idList; `AWRRF);
(`applyCorpActions; `split`cashDividend);
(`adjustTime; 2017.12.01D00:00:00.000);
(`adjustSize; `volume);
(`adjustPrice; enlist `price)
)
eventTimestamp instrumentID price volume
----------------------------------------------------------
2017.10.08D03:00:01.000000000 AWRRF 4.014105 1038.362
2017.10.08D03:00:01.500000000 AWRRF 8.829383 1557.542
2017.10.08D03:00:02.000000000 AWRRF 18.89392 5136.843
2017.10.08D03:00:02.700000000 AWRRF 20.84064 6164.212
2017.10.08D03:00:04.000000000 AWRRF 23.99687 7154.63
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 : "2017.10.08D03:00:00",
endTS : "2017.10.08D03:00:05",
idList : "AWRRF",
applyCorpActions: ["split","cashDividend"],
adjustTime : "2017.12.01D00:00:00",
adjustSize : "volume",
adjustPrice : "price"
}' | jq -cr .)"`
echo $DATA | jq -cr '.payload'