Tick by Tick Stock Data Collection from Interactive Brokers IB Gateway to TimescaleDB for Further Analysis

Photo by benjamin lehman on Unsplash

Quantitative analysis article after a long time.

Create TSDB cluster and table

Go here timescale.com, create Postgres for time-series cluster. No idea how it will work for you, but this database is one of the cheapest solutions. For this test free version will way enough.

With psql create a ticks table:

CREATE TABLE ticks (
  time TIMESTAMPTZ NOT NULL,
  symbol TEXT NOT NULL,
  bid DOUBLE PRECISION NOT NULL,
  ask DOUBLE PRECISION NOT NULL
);
SELECT create_hypertable('ticks', 'time');

Download IB Gateway

Download, install, configure any API port. Here I’ll use 7496.

Start the script

import { Client, Contract } from 'ib-tws-api'
import { Connection } from 'postgresql-client'
const connection = new Connection('<timescale url>')
await connection.connect()

function query ({ table, cols, data }) {
  return `INSERT INTO ${table} (time,symbol,bid, ask) VALUES (${data});`
}

const api = new Client({
  host: '127.0.0.1',
  port: 7496
})

const contract = Contract.stock('SPY')
const events1 = await api.streamMarketData({ contract })

events1.on('tick', (t) => {
  const { ticker } = t
  const { lastTimestamp, bid, ask } = ticker
  if (!bid || !ask) return
  const data = `to_timestamp(${lastTimestamp}),'SPY',${bid},${ask}`
  await connection.query(query({ table: 'ticks', data }))
})

Possible improvements that can be done:

• ib-tws-api has outdated API version, it needs to be upped, still works,
• there may be a lot of ticks if using multiple equities, so it is better to write them via the buffered queue.

Enjoy.