top of page

Collecting and Analyzing Cryptocurrency Trading Data with Machbase Neo

4 days ago
5 min read

Characteristics of Cryptocurrency Trading Data

Cryptocurrency trading data has distinctive characteristics as a source for analysis. Exchanges operate around the clock, and popular trading pairs can see dozens or hundreds of trades per second. Collecting trades and order book updates for the top 50 coins by trading value on Binance generates 5,000–7,000 rows per second, or roughly 17 GB per day.

Yet the data is easy to obtain. Real-time stock prices usually require a brokerage account or a paid feed, but cryptocurrency exchanges stream trades and order book updates through public WebSocket connections. You can receive this data without an account or an API key.

The challenge, then, is capturing this continuous stream without missing data, storing it without excessive disk usage, and quickly retrieving the time ranges you need for analysis. This article shows how Machbase Neo addresses these challenges, using the neo-pkg-coin-collector package as an example.

Why Choose the Machbase Time Series Database?

Trade data is continually appended by trading pair and time, with little need for updates. Queries also focus on time ranges, such as “the last hour for this trading pair” or “hourly candles for the past week.” This makes a time series database a better fit than a general-purpose relational database. Machbase meets these needs in three ways:

  • Fast ingestion: The Append API loads thousands of rows per second or more directly, without constructing SQL statements.

  • Compressed storage: TAG tables store data in compressed form, while retention policies let the database automatically remove old raw data.

  • Fast queries: Tag names and timestamps identify the required time range, while rollups precompute summaries at minute and hour intervals.

The package stores all trades in a single TAG table, CC_TICK. The tag name (NAME) identifies the market and trading pair: BINANCE.BTCUSDT for BTC spot trading and BINANCE_F.BTCUSDT for BTC futures. This lets you query every trading pair with the same SQL without creating a separate table for each one.

CREATE TAG TABLE CC_TICK (
    NAME     VARCHAR(40) PRIMARY KEY,  -- Exchange.TradingPair
    TIME     DATETIME BASETIME,        -- Trade timestamp
    VALUE    DOUBLE SUMMARIZED,        -- Trade price
    QTY      DOUBLE,                   -- Trade quantity (buy +, sell -)
    AMT      DOUBLE,                   -- Traded value
    SAMT     DOUBLE,                   -- Net buy value
    TRADE_ID LONG
) WITH ROLLUP (MIN) EXTENSION;

Easy Data Collection

There is no need to set up a separate collection environment. With Machbase Neo 8.7.0 or later, place the package ZIP file in the public/ directory of your installation without extracting it, then install it from the App Store in the web UI. The tables, rollups, and retention policies are created together, and collection starts immediately for the top 50 coins by trading value. The collector runs as a service in Neo’s built-in JavaScript runtime (JSH), so you do not need a separate server or Node.js runtime. Installation and collection requirements

Choose the trading pairs in the settings screen, then specify whether to collect spot and futures trades and order book data for each pair. When you save the settings, the collector detects the changes within two seconds and reopens the relevant connections without restarting the service.

The collector normalizes incoming messages into a form suitable for analysis before storing them:

  • It stores the exchange’s trade timestamp, rather than the time the server receives the message, so network latency does not distort the timing.

  • It records buyer-initiated trades with positive quantities and seller-initiated trades with negative quantities to distinguish trade direction.

  • For futures contracts such as 1000PEPEUSDT, it converts prices and quantities to a per-coin basis so they can be compared with spot data on the same terms.

Records are accumulated in an in-memory buffer and loaded through Append once per second. The core code is concise:

const writer = conn.append('CC_TICK');

for (const row of trades) {
    writer.append(
        row.name, new Date(row.time), row.price, row.qty,
        row.price * Math.abs(row.qty),
        row.price * row.qty, row.tradeId
    );
}
writer.flush();

If a connection receives no messages for 30 seconds, the collector reconnects. If the database encounters a temporary problem, records remain buffered and are loaded again once it recovers. Collector implementation

Creating Minute and Hourly Candles Easily

Aggregating raw data every time you need minute or hourly candles becomes slower as the dataset grows. At 100 trades per second for a single trading pair, a day produces 8.64 million raw records. Summarizing them into one-minute intervals reduces this to just 1,440 intervals per day.

Machbase rollups let the database automatically precompute these summaries. CC_TICK is created with WITH ROLLUP (MIN) EXTENSION, which retains the first and last values as well as the minimum and maximum for minute and hour intervals. This lets you obtain open, high, low, and close prices with a single SQL query.

-- One-minute candles for BTC futures over the past hour
SELECT ROLLUP('min', 1, TIME) AS candle_time,
       FIRST(TIME, VALUE) AS open, MAX(VALUE) AS high,
       MIN(VALUE) AS low, LAST(TIME, VALUE) AS close
FROM CC_TICK
WHERE NAME = 'BINANCE_F.BTCUSDT' AND TIME >= NOW - 1h
GROUP BY candle_time
ORDER BY candle_time;

For five-minute candles, simply use ROLLUP('min', 5, TIME); for hourly candles, use ROLLUP('hour', 1, TIME). Minute and hour rollups are also configured for traded value (AMT) and net buy value (SAMT), so each candle’s buy value, (AMT + SAMT) / 2, and sell value, (AMT - SAMT) / 2, can be calculated as well. Candlestick queries

Rollups also reduce the need to retain raw data for long periods. By default, the package keeps raw data for only one day while retaining rollups indefinitely. You can inspect individual trades for the most recent day and use minute or hourly candles for earlier periods. Raw data retention can be set to 1, 7, or 30 days, or unlimited. Shorter retention greatly reduces disk usage while preserving long-term trend analysis. Retention policies

Analysis Using Neo’s Built-in Features

Machbase Neo combines a database, JavaScript runtime, HTTP API, and web interface, so collected data can feed directly into analytical features. The package includes the following examples:

  • Candlestick charts: The candle interval is selected to suit the query period. Intervals of one minute or longer use rollups; shorter intervals aggregate raw data.

  • Real-time dashboard: The collection service uses incoming trades to calculate each trading pair’s latest price, traded value over the past minute, and buy/sell trade strength, then sends the results to the screen.

  • Whale detection: Trades in the same direction for the same trading pair are aggregated over one second. Flows that are at least 30 times the usual trade size and at least 50,000 USDT are recorded as events.

  • Raw data vs. rollup comparison: The same query is run against raw data and rollups, with the SQL and execution times displayed side by side.

All these features are built entirely within Neo using JavaScript and SQL. In the same way, you can add analyses such as spot–futures price differences or changes in trading volume by time of day with a few lines of SQL and a simple script. To collect and analyze cryptocurrency trading data yourself, start by installing neo-pkg-coin-collector.

 
 
call
english
bottom of page