Tables

• binance_trades_trx_btc
• binance_trades_adx_btc
• binance_trades_eth_btc
• binance_trades_xlm_btc
• binance_trades_xrp_btc
• binance_trades_btc_usdt

Schemas

btc_usd

• btc
• usd
• trade_time

x_btc

• x
• btc
• usd (blank; needs to be filled)
• trade_time

Setup

CREATE INDEX ON binance_trades_trx_btc  (trade_time);
CREATE INDEX ON binance_trades_adx_btc  (trade_time);
CREATE INDEX ON binance_trades_eth_btc  (trade_time);
CREATE INDEX ON binance_trades_xlm_btc  (trade_time);
CREATE INDEX ON binance_trades_xrp_btc  (trade_time);
CREATE INDEX ON binance_trades_btc_usdt (trade_time);

ALTER TABLE binance_trades_trx_btc ADD COLUMN btc_usdt numeric;
ALTER TABLE binance_trades_adx_btc ADD COLUMN btc_usdt numeric;
ALTER TABLE binance_trades_eth_btc ADD COLUMN btc_usdt numeric;
ALTER TABLE binance_trades_xlm_btc ADD COLUMN btc_usdt numeric;
ALTER TABLE binance_trades_xrp_btc ADD COLUMN btc_usdt numeric;

VACUUM ANALYZE VERBOSE;

Test

SELECT 'binance_trades_trx_btc', count(*) FROM binance_trades_trx_btc;
SELECT 'binance_trades_adx_btc', count(*) FROM binance_trades_adx_btc;
SELECT 'binance_trades_eth_btc', count(*) FROM binance_trades_eth_btc;
SELECT 'binance_trades_xlm_btc', count(*) FROM binance_trades_xlm_btc;
SELECT 'binance_trades_xrp_btc', count(*) FROM binance_trades_xrp_btc;

Query

UPDATE binance_trades_eth_btc AS x_btc_outer
SET btc_usdt = converted.USD
FROM (
    SELECT
      x_btc.binance_trade_id,
      x_btc.price x,
      btc_usd.price btc_USD,
      x_btc.price * btc_usd.price USD,
      x_btc.trade_time - btc_usd.trade_time AS time_diff
    FROM binance_trades_eth_btc AS x_btc
    JOIN LATERAL (
      SELECT price, trade_time
      FROM binance_trades_btc_usdt AS btc_usd_inner
      WHERE x_btc.trade_time > btc_usd_inner.trade_time
      ORDER BY btc_usd_inner.trade_time DESC
      LIMIT 1
    ) AS btc_usd ON true
) AS converted
WHERE converted.binance_trade_id = x_btc_outer.binance_trade_id
;

Debug

• Kill long-running query

    SELECT
    	pid,
    	now() - pg_stat_activity.query_start AS duration,
    	query,
    	state
    FROM pg_stat_activity
    WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes';
  
    SELECT pg_cancel_backend(__pid__);
  

Data Scanner <--> Data Importer interaction

Test 1

• with binance_trades_btc_usd table that has sequential data
• set last_sequential_trade_id = 0
• start sockets and importer for btc_usdt
• re-run test but first delete a few rows here and there

result: dataScanner should make importer jump ahead to later id until it finds gaps. The gaps should be filled and scanner will start again until data importer is caught up to live trades. Data Importer will turn itself off and will then assume sockets have taken over. On app restart the importer starts again to close gaps.

Test 2

• same table set last_sequential_id = 0 or a number that isn't close to live trades
• start the system
• before the scanner starts delete a row that the importer has saved

result: the data scanner should make the importer 'back up' and fill the gap it left behind

Objects

LiveTrader

Dependencies: Exchange API, recently history from DB for RecentHistory

Output: Trade events

Backtester

Same interface as LiveTrader.

Dependencies: History from DB

Output: Trade events

TradeHandler

Controller that coordinates what happens based on incoming trade data.

• Listens for trade events (emitted from the websocket listener or backtester)
• Performs a delta threshold filter to ignore minor changes in price since the last decision algorithm run

TradeExecutor

Communicate with API (or fake for backtesting)

• Executes trades
• Updates trade position

Position

Tracks our current position in memory. Tracks:

• Holdings in each currency
• Buy price (at which you previously bought in)
• Stop/limit orders
• Pending market trades

DecisionAlgorithm

Do the magic based on our neural network, etc.

Input: RecentHistory, Position

Output: Buy/Sell/Hold

TradeHistory

Stores an in-memory rolling window of transaction history. Stores enough

Input: recent history, either from backtesting or the most recent live data