Skip to content

Using symbology and corporate actions on the DB Service

This page explains how to load the Symbology and CorpActions reference data on a single-node DB Service deployment, so that fsi-lib can apply symbology and corporate actions to your queries.

Prerequisites

Before you begin, ensure that you have the following:

  • DB Service with fsi-lib: The DB Service is running with fsi-lib loaded, as described in Get started with fsi-lib on the DB Service.
  • Reference data: corpactions.csv and symbology.csv CSV files.
  • curl and jq: Used to call the DB Service REST API.

Table names

The market data tables, such as Trade and Quote, can have any name. Use the name in the table argument of each fsi-lib API call. Examples can be found in DB service with fsi-lib quickstart.

The reference tables must be named exactly Symbology and CorpActions. fsi-lib reads these two tables by name.

Create the reference tables

Note

Creating tables, as shown below, is how tables are set up on a single-node DB Service. In a clustered deployment the tables are defined upfront in the assembly yaml, so skip the create step and load the data as shown in Load the data.

Create the two tables. The columns are described in symbology reference data and corporate action reference data:

curl -s -X POST http://localhost:8080/api/v0/tables/CorpActions -H 'Content-Type: application/json' -d '{
  "type": "basic",
  "description": "Corporate actions reference data",
  "columns": [
    {"name": "oid", "type": "int"},
    {"name": "exchange", "type": "symbol"},
    {"name": "time", "type": "timestamp"},
    {"name": "mltp_adj", "type": "float"},
    {"name": "add_adj", "type": "float"},
    {"name": "adj_type", "type": "symbol"}
  ]
}'

curl -s -X POST http://localhost:8080/api/v0/tables/Symbology -H 'Content-Type: application/json' -d '{
  "type": "basic",
  "description": "Symbology reference data",
  "columns": [
    {"name": "oid", "type": "int"},
    {"name": "source_exchange", "type": "symbol"},
    {"name": "start", "type": "timestamp"},
    {"name": "end", "type": "timestamp"},
    {"name": "mapped_symbol", "type": "symbol"},
    {"name": "dest_exchange", "type": "symbol"},
    {"name": "symbology", "type": "symbol"}
  ]
}'

Confirm that the tables exist:

curl -s http://localhost:8080/api/v0/tables

["Trade","Quote","CorpActions","Symbology"]

Load the data

  1. Copy the CSV files into the data/imports/ folder of the DB Service:

    cp corpactions.csv symbology.csv /path/to/db-service/data/imports/

  2. Start an import job for each file. The path is relative to the data/imports/ folder:

    curl -s -X POST http://localhost:8080/api/v0/imports/files -H 'Content-Type: application/json' -d '{"table": "CorpActions", "path": "corpactions.csv"}'
curl -s -X POST http://localhost:8080/api/v0/imports/files -H 'Content-Type: application/json' -d '{"table": "Symbology", "path": "symbology.csv"}'

    Each reply contains a jobId.

  3. Check the status of each job until it is completed:

    curl -s http://localhost:8080/api/v0/imports/<jobId> | jq '{jobId,status,error}'

    {
  "jobId": "1fcace89-39c9-728a-53af-7e3634b3c787",
  "status": "completed",
  "error": ""
}

  4. Check the number of rows in each table:

    curl -s -X POST http://localhost:8080/api/v0/query/sql -H 'Content-Type: application/json' -H 'Accept: application/json' -d '{"query": "SELECT count(*) AS n FROM CorpActions"}' | jq -c .payload

    [{"n":2287967}]

    The count should match the number of rows in the imported CSV file.

Confirm the data

Connect with the q client, as shown in the DB service with fsi-lib quickstart, and look at the first rows of each table:

session.querySimple`table`limit!(`CorpActions;3)
session.querySimple`table`limit!(`Symbology;3)

oid exchange time                          mltp_adj add_adj adj_type
-------------------------------------------------------------------------
6            1977.06.20D04:00:00.000000000 0        0.05    CASH_DIVIDEND
6            1977.09.19D04:00:00.000000000 0        0.05    CASH_DIVIDEND
9            1991.03.25D05:00:00.000000000 0        0.1     CASH_DIVIDEND

oid source_exchange start                         end                           mapped_symbol   dest_exchange symbology
-----------------------------------------------------------------------------------------------------------------------
10                  2017.01.10D05:00:00.000000000 2024.10.18D04:00:00.000000000 AWRRF US Equity               BSYM
14                  2018.12.20D05:00:00.000000000 2200.01.01D00:00:00.000000000 AQBN US Equity                BSYM
21                  2017.01.10D05:00:00.000000000 2017.10.12D04:00:00.000000000 ANTQ US Equity                BSYM

Using the reference data

With the reference data loaded, add idSymbology or applyCorpActions to your fsi-lib API calls.

The fsi-lib functions are served by the DB Service gateway on port 5040:

h:hopen `:localhost:5040

Example: corporate actions with getStats

Apple split 4-for-1 on 31 August 2020. Use .fsi.getStats to get the daily open, close, volume and number of trades. Query without applying corporate actions:

args:(!) . flip (
    (`table;`Trade);
    (`idList;enlist `AAPL);
    (`startTS;2020.08.27D);
    (`endTS;2020.09.02D);
    (`groupBy;enlist `instrumentID);
    (`agg;`open`close`volume`trades!((`first;`price);(`last;`price);(`sum;`volume);(`count;`price)));
    (`granularity;1);
    (`granularityUnit;`day)
    );

last h(`.fsi.getStats;args;`;()!())

instrumentID eventTimestamp                open   close    volume   trades
--------------------------------------------------------------------------
AAPL         2020.08.27D00:00:00.000000000 508.5  509.72   1343969  11445
AAPL         2020.08.28D00:00:00.000000000 504.1  503.3458 1308543  12608
AAPL         2020.08.31D00:00:00.000000000 127.62 127.575  14518333 69239
AAPL         2020.09.01D00:00:00.000000000 132.79 131.69   10326057 57302

The price decreases from approximately 500 to 127 because of the split. Add applyCorpActions to adjust the earlier prices and sizes. The adjustSize argument specifies the column that contains the trade size:

args:(!) . flip (
    (`table;`Trade);
    (`idList;enlist `AAPL);
    (`startTS;2020.08.27D);
    (`endTS;2020.09.02D);
    (`groupBy;enlist `instrumentID);
    (`agg;`open`close`volume`trades!((`first;`price);(`last;`price);(`sum;`volume);(`count;`price)));
    (`granularity;1);
    (`granularityUnit;`day);
    (`applyCorpActions;enlist `split);
    (`adjustSize;`volume)
    );

last h(`.fsi.getStats;args;`;()!())

instrumentID eventTimestamp                open    close    volume   trades
---------------------------------------------------------------------------
AAPL         2020.08.27D00:00:00.000000000 127.125 127.43   5375876f 11445
AAPL         2020.08.28D00:00:00.000000000 126.025 125.8364 5234172f 12608
AAPL         2020.08.31D00:00:00.000000000 127.62  127.575  14518333 69239
AAPL         2020.09.01D00:00:00.000000000 132.79  131.69   10326057 57302

Prices before 31 August are divided by 4 and sizes multiplied by 4, so the whole period is on the same scale. Prices and sizes on and after 31 August are unchanged.

For .fsi.getTicks examples with symbology and corporate actions, refer to:

Additional documentation

Back to top