DEV Community: DolphinDB

We Rebuilt a Securities Firm's FICC Data Stack — Queries Got 10x Faster

DolphinDB — Thu, 16 Jul 2026 07:21:39 +0000

The investment research data team at a leading publicly listed securities firm had long struggled with fragmented data, inconsistent data standards, and insufficient real-time computing capabilities across its FICC operations. The journey from strategy development to production deployment often took several months.

To overcome these challenges, the firm partnered with DolphinDB to build a unified FICC multi-asset research data platform based on an integrated stream-batch architecture, achieving:

More than a 10× improvement in historical market data query performance
Millisecond-level latency for intraday market data
A reduction in the strategy development-to-production cycle from three months to just two weeks
An overall efficiency improvement of more than 85%

The platform eliminated data silos and fragmented system architectures, providing a solid technical foundation for FICC quantitative trading and market-making.

FICC Quantitative Trading: Speed Creates Alpha

FICC markets have long been dominated by over-the-counter (OTC) trading, where research and trading workflows depend heavily on manual data handling. Quantitative teams spend significant time integrating and cleaning data before pricing, backtesting, and risk analysis. The result is slower research, less accurate pricing, and delayed hedging.

FICC markets are becoming increasingly data-driven. Growing market complexity and stronger links between exchange-traded and OTC markets are driving demand for unified data management, real-time analytics, and faster strategy execution.

As a result, low-latency processing of high-frequency market data and unified data governance have become essential capabilities for modern FICC quantitative platforms, serving as the technological foundation for high-precision pricing, algorithmic hedging, and automated market making at leading financial institutions.

The Challenge: Data Fragmentation Was Slowing Down the Business

As business requirements evolved, the firm's traditional FICC data architecture could no longer support the growing demands of quantitative research and trading. Four major challenges emerged.

1. Inconsistent Data Across Sources
FICC research data came from multiple heterogeneous sources, including CTP market data feeds, third-party vendors, and locally maintained Excel spreadsheets. Each used different schemas, field definitions, and transmission formats. The lack of standardized frequently resulted in typo records, missing sequences, and other data quality issues, reducing backtesting accuracy and reinforcing the classic “Garbage In, Garbage Out” (GIGO) cycle.

2. Fragmented Architecture and Isolated Data Components
Years of incremental IT expansion had left the same data duplicated across multiple systems. Researchers had to retrieve, reconcile, and integrate data across systems before analysis, while the siloed architecture increased infrastructure costs through duplicated hardware, redundant storage, and higher maintenance overhead.

3. Batch-Oriented Processing with Limited Real-Time Computing
The legacy architecture relied on end-of-day batch processing, with analytical results available only on T+1. High-frequency strategies simply weren't possible without live market data.

4. High Technical Barriers for Researchers
The fragmented architecture placed a heavy technical burden on researchers. Instead of focusing on strategy development, they spent much of their time writing data extraction scripts and glue code to integrate data from multiple sources. As a result, development cycles became longer and production deployment was significantly delayed.

The Solution: An FICC Research Data Platform Powered by DolphinDB

The firm partnered with DolphinDB to build a unified, multi-asset FICC research data platform on a distributed time-series database, combining streaming and batch processing in a single framework.

Consolidating Historical Data

The team migrated nearly five years of cash bond market data from Hive into DolphinDB, along with historical futures and options data. With DolphinDB's multi-model storage, the platform optimizes storage for different data types while balancing storage efficiency and query performance.

Multi-source data is cleansed and normalized into a unified trade and quote data model. A closed-loop validation process continuously checks for missing or abnormal records, enabling centralized management of historical data across all asset classes while significantly reducing system complexity and maintenance costs.

Building a Real-Time Market Data Hub

The system uses DolphinDB's Kafka plugin to build a low-latency data ingestion layer, enabling real-time access to market data from major exchanges and brokers through a unified data pipeline. Combined with DolphinDB's streaming engines, the system enables real-time calculation of derived indicators. As new data arrives, incremental computations are triggered automatically and results are generated with minimal latency.

For FICC-specific needs, DolphinDB also provides a real-time one-minute candlestick generation module for futures and options. The module addresses common industry challenges such as inconsistent exchange timestamps and disconnected workflows between historical and real-time data processing. Manual and error-prone alignment processes are now automated within the system.The module now serves as a core infrastructure component, feeding low-latency, high-availability minute-level data to both production trading systems and quantitative strategies.

The Results

The unified platform now connects research, trading, and risk management on a single data foundation.

The project transformed fragmented, post-market data workflows into a unified real-time data foundation. By connecting research, trading, and risk management teams on a single data platform, it enables faster decision-making, accelerates quantitative research, and strengthens the firm's competitiveness in FICC market making.

What's Next: AI-Powered Research and High-Fidelity Backtesting

For both the firm and DolphinDB, this project marks a major milestone in modernizing FICC data infrastructure—but it is only the beginning.

Looking ahead, we plan to further evolve the platform in two strategic directions.

The first is deeper AI integration. DolphinDB will build a high-performance feature store designed for AI workloads, enabling factor selection, model training, and online inference directly within the data platform to accelerate the deployment of AI-driven investment research.

The second is the development of a high-fidelity backtesting platform capable of dynamically simulating intraday margin requirements and real-time risk exposure for futures and options. By more accurately replicating live trading conditions, the platform will provide a more realistic environment for strategy validation and improve the reliability of quantitative research before production deployment.

As financial markets continue to evolve, DolphinDB will keep innovating at speed—empowering intelligent finance with higher performance, greater reliability, and technology built for the future.

What’s New in DolphinDB：Introducing DolphinX, an Enterprise-Grade Platform for Agent Development and Governance

DolphinDB — Wed, 15 Jul 2026 08:33:01 +0000

_This release is a Compatibility Level 2 update. For details, please refer to the V3.00.6 Release Notes.

Features marked [V3.0] are available exclusively in DolphinDB 3.00.6._

As enterprises race to turn AI from pilot projects into production systems, the real bottleneck is often integration — connecting data, compute, and workflows into something secure and reusable. With DolphinDB V3.00.6 & V2.00.19, we’re tackling this head-on.

Central to this release is DolphinX, an enterprise-grade platform that deeply integrates DolphinDB’s native capabilities with an Agent platform — turning data, computing, and business logic into secure, reusable, and governable AI agent capabilities with minimal effort.

This release also strengthens core capabilities across FeatureDB, FICC, JIT compilation, dynamic script optimization, high-availability stream computing, and system operations — spanning the full workflow from low-latency feature storage and complex derivatives pricing to high-performance script execution and enterprise-grade governance.

1. DolphinX: An Enterprise-Grade AI Development and Governance Platform

Available in V3.0

As demand grows for natural language data querying, intelligent analytics, and automated workflows, the traditional “database + external AI framework” approach is increasingly challenged by concerns around security, compatibility, operational complexity, and enterprise governance. DolphinX, an enterprise-grade platform for AI agent development and governance, enables organizations to transform their data, computing, and business capabilities into secure, governable, and reusable AI agent services with a single integration, empowering faster business innovation and accelerating AI deployment.

Natural Language-Driven Data Workflows
With the built-in Coding Agent, users can interact with DolphinDB’s core capabilities using natural language, eliminating the need to learn Dlang. From script generation and debugging to SQL query analysis, data import/export, stream processing, and real-time engine development, Coding Agent makes advanced data capabilities accessible to a much broader range of business users.

Out-of-the-Box AI Toolkit with Flexible Extensibility
DolphinX works out of the box with zero configuration required. Just log in through the web interface to get started. It also includes a web-based management console for configuring prompts, models, and MCP servers, and attaching Skills to build dedicated AI agents.

DolphinX comes with a rich set of built-in Skills covering coding standards, data import, financial services, stream processing, operations and maintenance, machine learning, data visualization, testing, and more (see the table below).

For organizations requiring deeper customization, DolphinX exposes APIs that enable seamless integration of its AI capabilities into existing enterprise systems.

Eliminate External Dependencies and Build an Enterprise-Grade Intelligent Closed Loop
AI agent capabilities are built directly into the DolphinDB Server, eliminating the need for third-party orchestration frameworks. This enables a complete, end-to-end workflow — from understanding and analysis to execution — entirely within a single system. For scripts generated by AI models but not yet reviewed by users, DolphinX parses and validates them before execution, blocking high-risk operations such as deletion and cleanup. When invoking DolphinDB capabilities, AI agents inherit the current user’s permissions, eliminating the need for a separate access control system. This significantly reduces the security risks, compatibility issues, and operational complexity associated with external dependencies, making enterprise AI applications more secure, reliable, and efficient.

To further enhance AI’s understanding of database environments, DolphinDB has also expanded its semantic metadata framework for database objects. Building on existing support for descriptions of tables, table columns, and user-defined functions, this release introduces description support for both the Catalog and Schema levels. In addition, the maximum length of table column descriptions has been increased from 256 bytes to 1,024 bytes. By providing richer representations of database hierarchy and business semantics, AI agents can more accurately understand data assets, generate scripts, and execute analytical tasks, resulting in a more reliable enterprise-grade intelligent closed loop.

Enterprise-Grade Agent Development and Governance Foundation
DolphinX provides a common capability layer for building and governing Agents, including session management, Memory, Skills, context assembly, LLM provider management, and permission auditing. This helps enterprises avoid rebuilding these capabilities from scratch for every Agent, while enabling unified governance and operations.It supports both short-term and long-term memory, and provides a built-in RAG knowledge system based on DolphinDB’s TextDB and VectorDB. It also supports unified integration with multiple LLM providers, including fallback switching, token budget management, and local or private cloud deployment, balancing flexible extensibility, data security, and autonomous control.

Designed for scenarios such as natural language interaction, intelligent analytics, and automated processing, DolphinX deeply integrates AI capabilities with DolphinDB to provide an integrated product ecosystem that combines out-of-the-box usability with enterprise-level extensibility. It enables organizations to build intelligent data applications with lower barriers to entry and reduced system integration costs.

2. FeatureDB: A Low-Latency Feature Storage Engine for AI and Machine Learning Applications

Available in V3.0

In AI and machine learning training and online inference scenarios, feature data access follows different patterns than general analytical queries with high-frequency reads by column, window, and timestamp. Traditional online feature stores, which are primarily designed for row-based access, focus more on full-row read/write operations and key-value retrieval. As a result, they are not fully optimized for these access patterns and can become performance bottlenecks in scenarios involving wide feature tables, frequent reads, and low-latency services.

FeatureDB is purpose-built to address exactly this gap. Running within the DolphinDB environment, it offers a lightweight, high-performance foundation for feature serving pipelines in AI and machine learning applications.

Note: The FeatureDB introduced in this release currently supports in-memory mode only. Persistent storage will be available in future releases.

Microsecond-Level Random Reads to Alleviate Feature Access Bottlenecks
FeatureDB delivers microsecond-level random reads, with response times typically ranging from a few micro-seconds to tens of microseconds. This significantly reduces the impact of feature access latency on both training throughput and inference speed, especially in large-scale, high-frequency access scenarios.

Native Support for Machine Learning Data Types and Flexible Wide Table Design
FeatureDB natively supports commonly used floating-point data types in machine learning, including FLOAT8, FLOAT16, FLOAT32, and FLOAT64, covering different tradeoffs between precision and storage efficiency. It also supports wide tables containing hundreds of feature columns and provides simple syntax for quickly defining continuous feature columns, reducing the management complexity of large-scale feature engineering.

Seamless Python Ecosystem Integration, Simplifying Workflows from Import to Retrieval
On the upstream side, FeatureDB integrates with feature computation tools such as Spark and PyArrow through the standard Parquet format, enabling one-click batch import. On the downstream side, its Python SDK directly outputs data that can be consumed by mainstream ecosystem tools such as NumPy and PyTorch, covering the complete pipeline from storage to model training and inference. By leveraging industry-standard tools and formats throughout the workflow, FeatureDB effectively reduces system integration and maintenance costs.

The introduction of FeatureDB marks an extension of DolphinDB’s capabilities in AI and machine learning scenarios, providing users with a new infrastructure option for building high-performance feature data services.

3. Building a Complete Pricing Loop: Comprehensive FICC Capability Enhancements

Available in V3.0

The new release continues to enhance DolphinDB’s FICC pricing capability framework by introducing a wide range of pricing functions across fixed income, interest rates, credit, foreign exchange, and equity OTC derivatives. New support covers agreements, government bond futures, FRAs, Caps/Floors, Swaptions, CDS, NDFs, digital options, and range accrual options.

In addition, the release introduces new foundational tools such as Copula functions, credit curves, volatility surfaces/cubes, date generation, and schedule construction. These enhancements further strengthen DolphinDB’s cross-asset pricing and risk analysis capabilities, enabling a more complete and integrated pricing workflow.

4. Enhanced JIT Capabilities with Broader Language Support and Greater Stability

Available in V3.0

As enterprises increasingly demand real-time execution of complex business logic, high-performance script acceleration, and event-driven processing, Just-In-Time (JIT) compilation has become a critical foundation for executing core computational workloads. In production environments, users expect more than successful compilation — they also require comprehensive support for complex language features, rich object and container operations, and consistent performance under long-running workloads, complex event streams, and high-concurrency scenarios.

DolphinDB has comprehensively upgraded its JIT engine in the new release, expanding language support while significantly improving runtime stability. These enhancements allow users to migrate more business logic into the high-performance execution path with minimal code refactoring.

Broader Language Support for Complex Scripts
The new release adds JIT support for a wider range of language features, including partial application, anonymous functions, lambda expressions, closures, default parameters, and keyword arguments. As a result, many scripting patterns that previously relied on the interpreter can now be compiled directly without rewriting code specifically for JIT compatibility. This significantly improves scripting flexibility for scenarios involving reusable abstractions, complex computational workflows, and sophisticated data structures.

Enhanced Type System and Container Support
This release introduces explicit type annotations as a major enhancement to the JIT type system. Developers can declare member types directly within class definitions using the following syntax:
class MyClass { field1 :: STRING field2 :: INT field3 :: DOUBLE VECTOR field4 :: DICTIONARY[STRING, STRING] }
Type annotations support scalar types, user-defined classes, vectors, ANY, and recursively nested dictionaries. The JIT compiler generates type-specialized machine code directly from these declarations, eliminating runtime type inference and significantly improving performance when accessing complex objects and nested data structures.

Container operations have also been substantially expanded. The JIT runtime now supports reading, assignment, deletion, clearing, indexing, and concurrent access across these data types. In addition, Matrix support and non-scalar indexing have been introduced, while numerous container-related crashes, memory issues, and incorrect computation results have been resolved.

Improved Compatibility for CEP
JIT support for classes has been further enhanced to better serve Complex Event Processing (CEP) applications. The new release supports event classes, monitor classes, and helper classes used in CEP, while improving advanced class features such as dictionary/vector member access, nested classes, inter-method calls, and callback function passing. These enhancements improve both execution efficiency and runtime stability in CEP-related workloads.

Greater Stability and Production Readiness
Beyond language enhancements, this release significantly improves the engineering robustness of the JIT engine. Numerous stability issues involving complex data processing, nested assignments, loop execution, classes, and tuples have been resolved, along with multiple memory leaks, persistent memory growth, and abnormal resource utilization issues. Together, these improvements reduce runtime failures and make JIT considerably more reliable for complex business logic running under production-scale data workloads.

5. Upgraded Dynamic Script Optimization for Faster Nested Functions and Loop Execution

In quantitative analytics, real-time risk management, and low-latency trading, users frequently write complex scripts containing loops, conditional branches, and function calls. Because these workloads are often difficult to fully vectorize, interpreter overhead can become a significant performance bottleneck.
DolphinDB provides Dynamic Script Optimization, which automatically optimizes interpreted scripts at runtime without requiring any code modifications. Once enabled, it significantly accelerates scripts containing loops, conditional branches, and function calls. In function-intensive workloads, performance improvements of several times have been observed.

In the new release, this capability has been further refined by changing the runtime optimization scope from process-level to session-level. Configuration parameters continue to define the default behavior, while runtime APIs now affect only the current session without impacting other sessions within the same process. This improvement provides much greater flexibility in multi-user and multi-task environments. For example, users can enable optimization in one session for performance testing while keeping another session unchanged for functional validation, making the feature more practical for production deployment.

Dynamic Script Optimization is particularly effective for the following scenarios:

Deeply nested function calls, such as strategy logic decomposed into multiple reusable functions with frequent invocation.
Complex iterative computations, including tick-by-tick or bar-by-bar processing, element-wise window calculations, and recursive state updates.
High-frequency conditional branching, such as signal generation, chained risk control rules, and sequential trading condition filtering.
Business logic that cannot be easily vectorized, including state-dependent, path-dependent, or multi-stage decision workflows.

For these workloads, Dynamic Script Optimization significantly reduces interpreter overhead and minimizes the performance cost of loops and function calls. Benchmark results show performance improvements of 63.9%–88.8% for function-intensive scripts, up to 8.93× acceleration for deeply nested function calls, and more than 30% performance gains for representative financial factor calculations such as VWAP.

6. Enhanced High Availability for Streaming Data

The new release further strengthens the high availability (HA) capabilities for streaming data, covering both HA stream tables and HA MVCC tables to improve production reliability.

For stream tables, data subscription and delivery are now more stable, subscription management is more reliable, and services recover faster after node status changes. These enhancements also extend to the ORCA streaming platform, improving stream graph continuity and overall data reliability.
For MVCC tables, HaMvccTable further enhances the stability of data reading, writing, and synchronization, improves resource management, and ensures data consistency across multiple nodes. With continued optimization, HaMvccTable is now more production-ready and provides stronger protection for mission-critical data.

7. Improvements to Core Capabilities

This release also delivers enhancements across the database kernel and data analytics engine.

Database
Core database capabilities have been improved across distributed scheduling, storage integration, data lifecycle management, and write control. The new release delivers better task scheduling balance in multi-replica clusters, simplified Amazon S3 integration through the AWS SDK default credential chain, enhanced monthly partition retention and cold storage migration, and more precise write controls to prevent unintended NULL values during complex update operations.

Analytical Functions
The function library has been expanded for quantitative research and real-time analytics. New capabilities include time-based weighted calculations, linear filtering, variable-length sliding windows, and rolling drawdown calculations. Custom aggregation and regression options have also been extended, while text import and JSON serialization have been improved for better interoperability with external systems.

Core Language
The core language continues to evolve with improvements to type definitions, class support, control flow, metaprogramming, SQL semantics, and function metadata. New SQL features include union join (uj), ROLLUP, group-level pagination with context by, enhanced type conversion, and improved identifier compatibility. Function annotations have also been enriched with @desc, parameter types, and return type declarations.

Stream Processing
The streaming framework has been further strengthened with more flexible trigger mechanisms in the time-series engine, refined snapshot generation across multiple time ranges, access control identifiers for stream table queries, and improved management of dynamic metrics in ORCA stream graphs, delivering greater stability for complex streaming workloads.

Plugins & API
Plugin installation and Python data upload have been streamlined, improving plugin deployment in restricted network environments. Python integration with the Apache Arrow ecosystem has also been enhanced, enabling smoother interoperability with tools such as Polars.

8. Enhanced System Operations

The new release strengthens observability, resource management, and operational efficiency for production environments.

Better Observability
System monitoring has been improved with richer metadata and status visibility. Administrators can now query historical background tasks based on permissions, while stream tables, functions, shared variables, and in-memory tables expose additional status information. Automatic DDL generation through getDatabaseDDL and getDBTableDDL also simplifies database migration and schema replication.

Stronger Security and Resource Control
AES-256-CBC encryption has been added for secure string encryption and decryption. License management now supports hardware dongle verification and real-time license status monitoring. User-level task concurrency limits help prevent resource contention, while Catalog and Schema objects now support descriptions for easier management in multi-tenant environments. (Some features are available only in V3.00 and later.)

9. Performance and User Experience Improvements

This release delivers comprehensive improvements in data processing performance, system stability, and usability. Enhancements to query parsing, error reporting, storage scheduling, cache warm-up, transaction management, and Leader stability improve performance, observability, and reliability while reducing resource consumption in storage-compute separation, hot/cold tiering, and large-scale data access scenarios.

At the storage layer, TSDB introduces the new volumeType configuration for storage volumes. With SSD optimization enabled, level file access paths have been optimized to reduce storage latency and improve throughput for high-frequency read workloads. Benchmark results show an average throughput improvement of approximately 13% for multi-level group aggregation, with gains of up to 35% in specific scenarios. CPU-intensive read workloads achieve throughput improvements of up to 109%.

Usability has also been enhanced with improvements to object display, timestamp handling, dynamic metric management, field parsing, license queries, matrix indexing, and other commonly used data processing capabilities, providing greater flexibility, ease of use, and compatibility.

10. Roadmap

Storage & Data Ingestion

Introduce persistent mode for FeatureDB to improve the reliability and reusability of feature data.
Enhance the read performance of external tables (e.g., Amazon S3) to improve data ingestion efficiency in lakehouse architectures.
Financial Derivatives Pricing
Expand support for pricing complex financial derivatives across three major asset classes:
Fixed Income: Embedded-option bonds
Credit: CLN, CRMW, and CRMA
FX: Asian, Barrier, Touch, and Quanto options
Further enhance core pricing models — including LV, SV, SLV, PDE, and Monte Carlo methods, and optimize pricing engine compatibility to provide a unified infrastructure for multi-asset derivatives pricing.

AI Agent Ecosystem
Launch a suite of domain-specific AI agents for different use cases:

Database Operations Agent
Plugin Development Agent
Machine Learning & Data Analytics Agent
Quantitative Research Agent
Dlang Programming Assistant with long-context conversation support.
Release an AI agent evaluation framework X-Lab for measuring and validating agent capabilities.

Query Engine Optimization
Continue enhancing the SQL engine by improving standards compliance while significantly boosting execution performance for complex multi-table join workloads.

Download DolphinDB V3.00.6 & V2.00.19 now.

Thanks for reading! Stay connected with DolphinDB by following us on X (@DolphinDB_Inc) and LinkedIn for the latest updates, technical articles, and product news. For more information, visit our website.

How Crypto Pin Bars Form and How to Detect Them: Lessons from a $19B Liquidation

DolphinDB — Wed, 08 Jul 2026 09:02:42 +0000

One Candlestick, $19 Billion Wiped Out Overnight

On October 10, 2025, the U.S. abruptly announced a 100% tariff on all Chinese imports and introduced new export restrictions on critical software. The news shattered an otherwise quiet Friday evening. Bitcoin plunged more than 14% from near its all-time high, while several Altcoins fell by as much as 70% before rebounding [1]. Within just a few hours between October 10 and 11, more than $19 billion in leveraged positions were liquidated, making it one of the largest single-day liquidation events in crypto market history.

Left behind on the charts: a series of long lower wicks that traders know all too well as pin bars.

What Is a Pin Bar?

A pin bar is an extreme candlestick pattern characterized by a very long wick and a relatively small body. The price briefly breaks through a key support or resistance level before rapidly reversing, leaving behind a distinctive "needle" on the chart. It serves as the market's fingerprint of a brief loss of equilibrium.

The concept originates from classical candlestick analysis and can be traced back to Steve Nison's pioneering work on candlestick techniques [2]. Traditional definitions, however, are largely qualitative. To provide a more objective definition, we quantify a pin bar using the following criteria:

The wick is significantly longer than the candlestick body.
The wick accounts for a large proportion of the candlestick's total price range.
The overall price range exceeds a minimum absolute or relative threshold to filter out normal bid-ask fluctuations.

Why Do Pin Bars Occur?

Market microstructure offers several classic explanations.

Informed Trading

Large institutions or traders with superior information execute sizable orders during periods of limited liquidity, causing significant price impact.

Insufficient Liquidity

Smaller cryptocurrencies often have shallow order books, allowing a single large order to move the market by several percentage points.

Market Manipulation

Some traders intentionally trigger clusters of stop-loss or liquidation orders by forcing prices through key levels before buying back at lower prices.

Leverage and Cascading Liquidations

This is the most common and most destructive mechanism. Highly leveraged long positions tend to concentrate their liquidation levels just below important support prices. Once the market reaches those levels, systems automatically execute market sell orders. The additional selling pressure pushes prices even lower, triggering more liquidations and creating a vicious cycle:

Pin Bar → Liquidations → Deeper Pin Bar

The October 10 crash provides a textbook example of this mechanism. Bitcoin was trading near its historical highs, with the market heavily positioned on the long side using excessive leverage. Meanwhile, liquidity had thinned during Friday evening as institutional market makers reduced activity. Under such conditions, a single geopolitical shock was enough to ignite the cascade.

Reconstructing October 10: What the Data Saw

This analysis is based on real market data collected from Binance and OKX throughout October 2025. Three types of datasets are used:

Minute-level candlestick (K-line) data for identifying pin bar patterns.
Aggregated trade data for calculating VPIN (Volume-Synchronized Probability of Informed Trading).
Order book depth snapshots for measuring order book imbalance.

All timestamps are converted to UTC and stored in the DolphinDB distributed database for analysis.

We begin by reconstructing the event using the three most direct indicators—pin bar patterns, liquidation records, and trading volume—to understand exactly what unfolded on that day.

Identifying Pin Bars

We scanned the 5-minute BTCUSDT candlestick data from Binance to identify pin bars using the following criteria:

Wick length > 5 × body length
Wick length > 50% of the total candlestick range
Candlestick range > 0.5% of the average price

The core implementation is shown below:

update dataKline set body = abs(close - open)
update dataKline set amplitude  = (high - low)
update dataKline set upper = (high - max(open,close))
update dataKline set lower = (min(open,close) - low)
update dataKline set avgPrice = (open + close)/2
//Detection criteria (adjust as needed)
a,b,c = 5.0,0.5,0.5
pinbar = select * from dataKline where lower > body * a and lower > amplitude * b
pinbar_upper = select * from dataKline where upper > body * a and upper > amplitude * b 
pinbar.append!(pinbar_upper)
pinbar = select * from pinbar where amplitude > avgPrice*c*0.01 order by eventTime

Using the 5-minute BTCUSDT candlestick data from Binance as an example, the detection results are shown below:

Example of detected pin bars

Candlesticks identified as pin bars are typically accompanied by unusually high trading volume compared with the day's average. As shown in the figure, at 2025-10-10 21:20:00 UTC, the price briefly fell to 101,516 USDT, dropping by more than 7,000 USDT from the opening price of 108,888 USDT. It then rebounded rapidly and closed at 107,747 USDT. Trading volume during this interval was approximately an order of magnitude higher than during other periods of the day.

So, who was selling during this period, and how much was actually sold?

Liquidation Data: Reconstructing the Cascade of Forced Liquidations

To answer this question, we aggregated minute-level liquidation data from Binance and OKX for the entire month of October. The data were then summarized by liquidation direction (long vs. short) to reveal the overall liquidation dynamics.

liqDataForSym = select * from loadTable("dfs://CryptocurrencyDay","liquidation")
update liqDataForSym set eventTime = temporalAdd(eventTime, -8, "H")  //Convert to UTC
liqDataForSymDay = select sum(quantity),side, date,symbolSource from liqDataForSym 
    group by symbolSource,side, date(eventTime) as date
res1 = select * from liqDataForSymDay order by date
liqDataForOneDay = select sum(quantity), side,eventTime,symbolSource from liqDataForSym 
    where date(eventTime) = 2025.10.10 group by symbolSource,side, bar(eventTime,1m) as eventTime
res2 =select * from liqDataForOneDay order by sum_quantity desc

The results are striking. The total liquidation volume on October 10 far exceeded that of any other day in the month. In terms of liquidation direction, long liquidations (forced sell orders) overwhelmingly outnumbered short liquidations, providing strong evidence of a cascading long squeeze. Intraday, liquidation activity peaked between 21:10 and 22:00, closely matching the period in which pin bars were most frequently detected.

Using XRPUSDT as an example, the figure below illustrates the relationship between minute-level liquidation volume and price movements.

Liquidation Data vs. Price Movement

Trading Volume Reveals the Sentiment Turning Point

We further analyzed the intraday trading volume of XRPUSDT and compared it with the corresponding price movement. It is important to note that the aggregated trade data used here includes only regular market orders and excludes liquidation-related forced orders. As a result, it provides a clearer view of genuine market sentiment rather than passive trading triggered by forced liquidations.

XRPUSDT tradeQty vs Volatility

The results show that trading volume surged sharply after 20:50, indicating that the shift in market sentiment occurred before the largest price decline. A wave of aggressive sell orders entered the market first, disrupting the supply-demand balance. These active sell orders were then amplified by forced liquidation orders, together driving larger price swings and ultimately producing the sharp wick observed around 21:20.

The Signals That Were Already There

The previous analysis reconstructed the full sequence of events during the price wick. For traders, however, stop-loss orders being triggered, positions being liquidated, or even accounts being wiped out often happen within minutes—or even seconds. Post-event analysis offers little practical value. The more important question is whether these events can be anticipated before the wick forms.

To answer this, we evaluated two market microstructure indicators—VPIN and Order Book Imbalance—using historical data to determine whether the market had already begun signaling elevated risk before the extreme price movement occurred.

VPIN: Tracking the Evolution of Order Flow Toxicity

VPIN (Volume-Synchronized Probability of Informed Trading) was introduced by Easley et al. in 2011 [3]. Its key idea is **to measure market activity based on trading volume rather than clock time. **Specifically, the order flow is divided into equal-volume buckets, and the imbalance between aggressive buy orders and aggressive sell orders is calculated for each bucket. A larger imbalance indicates that one side is dominating the market, implying higher order flow toxicity and a greater likelihood of extreme price movements.

We calculated the VPIN indicator using aggregated trade data to track the dynamics of order flow toxicity across multiple cryptocurrencies throughout October.

Parameter settings: The fixed bucket size (V) was determined by dividing the average daily trading volume over the previous 20 trading days by 50 buckets per day. A rolling window of 10 buckets was used to calculate VPIN. The indicator was computed in parallel for BTC, ETH, XRP, and ADA.

symList = [`BTCUSDT,`ETHUSDT,`XRPUSDT,`ADAUSDT] 
targetSource = "Binance-Futures"
dataType = "trade"  //"trade" or "minKLine"
d,N,n = 20,50,10  //Use the first d days, divide each day into N buckets to calculate V, with a window size of n
//Parallel computation across multiple trading days
Date = 2025.10.01..2025.10.30   //UTC time
result = peach(calcVPINForAllSym{,symList,targetSource,dataType,d, N,n}, Date)
res = select * from unionAll(result) order by symbol, eventTime
// Calculate volatility and correlation simultaneously
result = calcVPINVolCor(res)

The figure below compares VPIN with price volatility.

From the result we can see:

ADA and ETH: VPIN and price volatility were highly synchronized around October 10. In many cases, sharp increases in VPIN either preceded or coincided with significant price swings, suggesting that VPIN exhibits meaningful early warning characteristics.
BTC: The relationship between VPIN and price volatility was noticeably weaker, which is consistent with Bitcoin's larger market size and stronger liquidity absorption capacity.
Cross-asset pattern: The lower the liquidity of an asset, the more sensitive VPIN is as an early warning indicator for extreme market events.

The imbalance in order flow is already evident—but what about order book liquidity? Was market depth deteriorating at the same time?

Order Book Imbalance: Detecting the Early Signs of Liquidity Exhaustion

Another indicator that directly reflects market conditions is the Order Book Depth Ratio. It is calculated as the ratio of the cumulative bid size from Level 1 to Level N to the cumulative ask size from Level 1 to Level N.

When this ratio drops sharply, it indicates that buy-side liquidity is rapidly disappearing. Under such conditions, even modest selling pressure can trigger a cascade of price declines [4].

To validate this hypothesis, we analyzed order book depth snapshots from October 10, 2025. To avoid signal dilution caused by averaging multiple snapshots, we retained only the last snapshot of each minute. We then calculated a normalized order book imbalance metric and compared it with subsequent price volatility.

The analysis consisted of the following steps:

Retrieve order book depth data and calculate the imbalance metric.
Retain the last order book snapshot of each minute to preserve signal strength.
Calculate future returns and price volatility over different forward-looking windows.
Identify depth imbalance spikes using the 90th percentile of depthRatioQtyDiff for each asset as the threshold.
Compute ampRatio = future volatility following a Spike / future volatility without a Spike to evaluate the predictive power of the indicator.

use CalcDepthRatioModule
symList = [`BTCUSDT,`XRPUSDT,`ETHUSDT,`ADAUSDT]
targetSource = "Binance-Futures"
targetDate = 2025.10.10  //supports date range input
futureStep = 5  //number of future bars for amplitude window
// Call main function depthRatioAnalysis to execute
Data, spikeComparisonForSym, corrForSym = depthRatioAnalysis(symList,targetSource,targetDate,futureStep)

Using ADAUSDT as an example, the figure below compares the order book imbalance indicator with price movement.

When the order book imbalance exceeded the 90th percentile, creating an extreme spike, the average price range over the following five minutes increased significantly across all assets. This suggests that severe order book imbalances are indeed associated with elevated short-term volatility and can serve as an effective early warning signal for flash wicks.

The order book data used in this case includes the top 20 price levels from Binance and the top 5 price levels from OKX. Since this does not represent the exchanges' complete order books, we also used the difference between the best bid and best ask quantities (bidQty[0] - askQty[0]) as a real-time proxy for market liquidity:

//...
data = select * from loadTable("dfs://CryptocurrencyTick","depth") 
    where symbol = "BTCUSDT" and symbolSource = sySource and eventTime between startTargetTime:endTargetTime
update data set eventTime = temporalAdd(eventTime, -8, "H") //Convert to UTC
plotData = select eventTime,symbolSource,symbol,(bidQty[0]-askQty[0]) as diffQty from data
// Difference between cumulative bid and ask quantities across multiple order book levels
// plotData = select eventTime,symbolSource,symbol,each(sum,bidQty-askQty) as diffQty from data
plot(plotData[`diffQty],plotData.eventTime.minute(),"bid-ask spread",extras={multiYAxes: true})

As shown in the figure above, the order book exhibited several episodes of significant imbalance throughout the day, with the most pronounced fluctuations occurring between 21:00 and 22:10. This period coincided with the sharpest price movements, indicating severe order flow imbalance and a substantially higher likelihood of flash wick events.

Building a Real-Time Risk Monitoring System with VPIN

Identifying the signal is only the first step. During extreme market conditions, traders cannot afford to wait until offline analysis is complete before taking action. To address this, we integrated these indicators into a real-time stream processing pipeline and built a VPIN-based monitoring system that automatically generates alerts whenever abnormal conditions are detected, helping traders reduce exposure or exit positions before market conditions deteriorate further.

The workflow consists of four steps:

1. Continuously ingest aggregated trade data and divide the order flow into equal-volume buckets (bucket size = average daily trading volume over the previous 20 trading days ÷ 50 buckets per day).

2. Calculate the buy-sell imbalance (diffQty) for each bucket in real time.

3. Compute VPIN using a rolling window of 5 buckets. Trigger an alert whenever VPIN exceeds a predefined threshold (e.g., 0.2).

4. Push real-time notifications through channels such as WeCom or email to assist traders in making timely decisions.

We replayed historical market data from October 10, 2025 to simulate the real-time monitoring process. Using ADAUSDT as an example:

The results show that VPIN for ADAUSDT rose sharply after 21:00, closely matching the period of extreme price volatility. This demonstrates that the proposed monitoring framework is capable of providing meaningful early warning signals before flash wick events unfold.

Conclusion

Pin bars are not random market noise. From surging trading volume and rising VPIN to the depletion of order book liquidity, every stage leaves measurable traces in the data. The real danger is not the wick itself, but the liquidity vacuum and leveraged pressure that quietly accumulate beforehand—and those warning signs are already embedded in the market data long before the event occurs.

All analyses in this article were implemented using DolphinDB. From batch historical replay across multiple exchanges to real-time stream processing of VPIN and order book imbalance with automated alerts, the entire workflow runs within a unified framework. The goal is simple: surface critical signals when they matter—not after the market has already moved.

Reference

[1] Jung Hua Liu.(2025). Crypto “Black Swan” Crash: An Academic Analysis: ++The October 11, 2025 Crypto “Black Swan” Crash: An Academic Analysis | by Jung-Hua Liu | Medium++.

[2] Nison, S. (1991). Japanese Candlestick Charting Techniques. New York: New York Institute of Finance.

[3] Torben G. Andersen,Oleg Bondarenko (2014). VPIN and the Flash Crash: Journal of Financial Markets

[4] How to Read and Interpret Liquidity and Order Book Depth: ++How to Read and Interpret Liquidity and Order Book Depth++.

Introduction to backtesting strategy: Historical data replay in DolphinDB

DolphinDB — Thu, 29 Aug 2024 03:08:33 +0000

In DolphinDB, we can import historical data into a stream table in chronological order as “real-time data” so that the same script can be used both for backtesting and real-time trading. Regarding streaming in DolphinDB please refer to DolphinDB Streaming Tutorial .

This article introduces functions replay and replayDS and then demonstrates the process of data replaying.

1. Functions

replay

replay(inputTables, outputTables, [dateColumn], [timeColumn], [replayRate], [absoluteRate=true], [parallelLevel=1])

Function replay injects data from specified tables or data sources into stream tables.

‘inputTables’ is a table or a tuple. Each element of the tuple is an unpartitioned table or a data source generated by function replayDS.
‘outputTables’ is a table or a tuple of tables, or a string or a string vector. The number of elements of outputTables must be the same as the number of elements of inputTables. If it is a vector, it is a list of the names of the shared stream tables where the replayed data of the corresponding tables of inputTables are saved. If it is a tuple, each element is a shared stream table where the replayed data of the corresponding table in inputTables are saved. The schema of each table in outputTables must be identical as the schema of the corresponding table in inputTables.
‘dateColumn’ and ‘timeColumn’ are strings indicating the date column and time column in inputTables. If neither is specified, the first column of the table is chosen as ‘dateColumn’. If there is a ‘dateColumn’, it must be one of the partitioning columns. If only ‘timeColumn’ is specified, it must be one of the partitioning columns. If information about date and time comes from the same column (e.g., DATETIME, TIMESTAMP), use the same column for both ‘dateColumn’ and ‘timeColumn’. Data are replayed in batches determined by the smallest unit of time in ‘timeColumn’ or ‘dateColumn’ if ‘timeColumn’ is not specified. For examples, if the smallest unit of time in ‘timeColumn’ is second then all data in the same second are replayed in the same batch; if ‘timeColumn’ is not specified, then all data in the same day are replayed in the same batch.
‘replayRate’ is a nonnegative integer indicating the number of rows to be replayed per second. If it is not specified, it means data are replayed at the maximum speed.
‘replayRate’ is an integer.
‘absoluteRate’ is a Boolean value. The default value is true.

Regarding ‘replayRate’ and ‘absoluteRate’:

(1) If ‘replayRate’ is a positive integer and absoluteRate=true, replay at the speed of ‘replayRate’ rows per second.

(2) If ‘replayRate’ is a positive integer and absoluteRate=false, replay at ‘replayRate’ times the original speed of the data. For example, if the difference between the maximum and the minimum values of ‘dateColumn’ or ‘timeColumn’ is n seconds, then it takes n/replayRate seconds to finish the replay.

(3) If ‘replayRate’ is unspecified or negative, replay at the maximum speed.

‘parallelLevel’ is a positive integer. When the size of individual partitions in the data sources is too large relative to memory size, we need to use function replayDS to further divide individual partitions into smaller data sources. 'parallelLevel' indicates the number of threads loading data into memory from these smaller data sources simultaneously. The default value is 1. If 'inputTables' is a table or a tuple of tables, the effective 'parallelLevel' is always 1.

replayDS

replayDS(sqlObj, [dateColumn], [timeColumn], [timeRepartitionSchema])

Function replayDS generates a group of data sources to be used as the inputs of function replay. It splits a SQL query into multiple subqueries based on 'timeRepartitionSchema' with 'timeColumn' within each 'dateColumn' partition.

‘sqlObj’ is a table or metacode with SQL statements (such as ) indicating the data to be replayed. The table object of “select from” must use a DATE type column as one of the partitioning columns.
‘dateColumn’ and ‘timeColumn’ are strings indicating the date column and time column. If neither is specified, the first column of the table is chosen as ‘dateColumn’. If there is a ‘dateColumn’, it must be one of the partitioning columns. If only ‘timeColumn’ is specified, it must be one of the partitioning columns. If information about date and time comes from the same column (e.g., DATETIME, TIMESTAMP), use the same column for both ‘dateColumn’ and ‘timeColumn’. Function replayDS and the corresponding function replay must use the same set of 'dateColumn' and 'timeColumn'.
‘timeRepartitionSchema’ is a TIME or NANOTIME type vector. ‘timeRepartitionSchema’ deliminates multiple data sources on the dimension of ‘timeColumn’ within each ‘dateColumn’ partition. For example, if timeRepartitionSchema=[t1, t2, t3], then there are 4 data sources within a day: [00:00:00.000,t1), [t1,t2), [t2,t3) and [t3,23:59:59.999).

Replay a single in-memory table

replay(inputTable, outputTable, `date, `time, 10)

Replay a single table using data sources

To replay a single table with a large number of rows, we can use function replayDS together with function replay. Function replayDSdeliminates multiple data sources on the dimension of 'timeColumn' within each 'dateColumn' partition. Parameter 'parallelLevel' of functionreplay` specifies the number of threads loading data into memory from these smaller data sources simultaneously. In this example, 'parallelLevel' is set to 2.

Replay multiple tables simultaneously using data sources

To replay multiple tables simultaneously, assign a tuple of these table names to parameter ‘inputTables’ of function replay and specify the output tables. Each of the output tables corresponds to an input table and should have the same schema as the corresponding input table. All input tables should have identical 'dateColumn' and 'timeColumn'.

Cancel replay

If function replay was called with submitJob, we can use getRecentJobs to get jobId, then cancel the replay with command cancelJob.

If function replay was called directly, we can use getConsoleJobs in another GUI session to get jobId, then cancel the replay use command cancelConsoleJob.

2. How to use replayed data

Replayed data are streaming data. We can subscribe to and process the replayed data in the following 3 ways:

Subscribe in DolphinDB. Write user-defined functions in DolphinDB to process streaming data.
Subscribe in DolphinDB. To conduct real-time calculations with streaming data, use DolphinDB’s built-in streaming aggregators such as time-series aggregator, cross-sectional aggregator and anomaly detection engine. They are very easy to use and have excellent performance. In section 3.2, we use a cross-sectional aggregator to calculate the intrinsic value of an ETF.
With third-party client through DolphinDB’s streaming API.

3. Examples

Replay level 1 stock quotes to calculate ETF intrinsic value

In this example, we replay the level 1 stock quotes in US stock markets on 2007/08/17, and calculate the intrinsic value of an ETF with the built-in cross-sectional aggregator in DolphinDB. The following are the schema of the input table ‘quotes’ and a preview of the data.

(1) To replay a large amount of data, if we load all data into memory first, we may have an out-of-memory problem. We can first use function replayDS and specify parameter 'timeRepartitionSchema' to divide the data into 60 parts based on the column 'time'.

(2) Define the output stream table ‘outQuotes’.

(3) Define a dictionary for the ETF components weights and function etfVal to calculate ETF intrinsic value. For simplicity we use an ETF with only 6 component stocks.

(4) Define a streaming aggregator to subscribe to the output stream table ‘outQuotes’. We specify a filtering condition for the subscription that only data with stock symbols of AAPL, IBM, MSFT, NTES, AMZN or GOOG are published to the aggregator. This significantly reduces unnecessary network overhead and data transfer.

(5) Start to replay data at the specified speed of 100,000 rows per second. The streaming aggregator conducts real-time calculation with the replayed data.

(6) Check ETF intrinsic values

4. Performance testing

We tested data replaying in DolphinDB on a server with the following configuration:

Server: DELL PowerEdge R730xd
CPU: Intel Xeon(R) CPU E5–2650 v4(24cores, 48 threads, 2.20GHz)
RAM: 512 GB (32GB × 16, 2666 MHz)
Harddisk: 17T HDD (1.7T × 10, read speed 222 MB/s, write speed 210 MB/s)
Network: 10 Gigabit Ethernet

DolphinDB script:

When replaying at maximum speed (parameter ‘replayRate’ is not specified) and the output table is not subscribed, it only takes about 100 seconds to replay 336,305,414 rows of data.

https://github.com/dolphindb/Tutorials_EN/blob/master/historical_data_replay.md?source=post_page-----497e24af596d--------------------------------

Helpful Tools for Quant丨Efficiently Calculate Transaction Costs from Tick Data

DolphinDB — Wed, 28 Aug 2024 01:48:54 +0000

The calculation of transaction costs from tick data often involves two tables: trade and nbbo. As the timestamps of both tables are at nanosecond level, there are virtually no exact match between the timestamps of the two tables.

Therefore, in order to calculate transaction costs, we need to locate the most recent quote before each trade (of the same stock). We may also need to calculate the average quotes within a specific window relative to each trade. These non-exact joins are frequently used in quant finance, but they are not supported in most databases.

This time, DolphinDB provides you with asof join and window join for these scenarios.

Take a look at the following case!

The data used in this example is the high-frequency data from the New York Stock Exchange, consisting of two tables: trade and nbbo, respectively containing 27 million and 78 million records. The DolphinDB script is as follows:

trade = loadTable("dfs://EQY", "trade")
select count(*) from trade

nbbo = loadTable("dfs://EQY", "nbbo")
select count(*) from nbbo

// asof join
timer TC1 = select sum(Trade_Volume*abs(Trade_Price-(Bid_Price+Offer_Price)/2))/sum(Trade_Volume*Trade_Price)*10000 as TC_aj from aj(trade,nbbo,`Symbol`Time) where Time between 09:30:00.000000000 : 15:59:59.999999999 group by symbol

// window join
timer TC2 = select sum(Trade_Volume*abs(Trade_Price-(Bid_Price+Offer_Price)/2))/sum(Trade_Volume*Trade_Price)*10000 as TC_wj from pwj(trade,nbbo,-100000000:0,<[avg(Offer_Price) as Offer_Price, avg(Bid_Price) as Bid_Price]>,`Symbol`Time) where Time between 09:30:00.000000000 : 15:59:59.999999999 group by symbol

select * from ej(TC1,TC2,`symbol) where symbol in `AAPL`MS`EBAY

Just one line of script can implement complex calculation logic.

// asof join
timer TC1 = select sum(Trade_Volume*abs(Trade_Price-(Bid_Price+Offer_Price)/2))/sum(Trade_Volume*Trade_Price)*10000 as TC_aj from aj(trade,nbbo,`Symbol`Time) where Time between 09:30:00.000000000 : 15:59:59.999999999 group by symbol

It takes 339 milliseconds to complete the calculation of transaction costs with asof join, which is more than 100 times faster than the equivalent calculation in Python pandas, and the script to calculate with window join takes 402 milliseconds.

While calculating transaction costs, DolphinDB shows an excellent performance with concise code. For a visual representation of the operation covered in this article, you can take one minute watching this demo!

https://youtu.be/2HfTRLDYUrY

Mastering Pairwise Correlations Calculation of Securities Through Coding

DolphinDB — Tue, 27 Aug 2024 07:23:22 +0000

Calculating pairwise correlations of securities sheds light on the relationship between different securities, assisting in investment decision-making and risk management. In this article, you’re going to learn how to calculate the pairwise correlations of multiple securities using high frequency data.

The tool we use is DolphinDB and the data we use is the high-frequency quote data of US stocks on August 1, 2007. The raw data is 16.1 GB with 380 million records. We will calculate the pairwise correlations of the most actively traded 500 stocks in that day.

Let’s delve into the code.

select count(*) from loadTable("dfs://TAQ", "quotes") where date= 2007.08.01

def getStockCorrelation(dateValue, num){
    quotes = loadTable("dfs://TAQ", "quotes")

    syms = (exec count(*) from quotes where date = dateValue, time between 09:30:00 : 15:59:59, 0<bid, bid<ofr, ofr<bid*1.1 group by Symbol order by count desc).Symbol[0:num]
    priceMatrix = exec avg(bid + ofr)/2.0 as price from quotes where date = dateValue, Symbol in syms, 0<bid, bid<ofr, ofr<bid*1.1, time between 09:30:00 : 15:59:59 pivot by time.minute() as minute, Symbol
    retMatrix = each(def(x):ratios(x)-1, priceMatrix)
    correlationMatrix = corrMatrix(retMatrix[1:,].ffill())

    mostCorrelated = select * from table(correlationMatrix.columnNames() as sym, correlationMatrix).unpivot(`sym, syms).rename!(`sym`corrSym`corr) context by sym having rank(corr,false) between 1:10
    return mostCorrelated
}

dateValue = 2007.08.01
num = 500
timer mostCorrelated = defStockCorrelation(dateValue,num)

First, we generate a vector of 500 stock tickers with the largest number of quote records.

syms = (exec count(*) from quotes where date = dateValue, time between 09:30:00 : 15:59:59, 0<bid, bid<ofr, ofr<bid*1.1 group by Symbol order by count desc).Symbol[0:num]

Why we use “exec” statement?

The syntax of the “exec” statement is the same as the “select” statement except that “select” always returns a table **whereas “exec*” can return a **scalar, vector, matrix or table*, which is more convenient for certain operations.

Next, we select the records for these 500 stocks and filter out dirty data, then generate a minute-level mid-price matrix with stock tickers as column labels and minutes as row labels.

priceMatrix = exec avg(bid + ofr)/2.0 as price from quotes where date = dateValue, Symbol in syms, 0<bid, bid<ofr, ofr<bid*1.1, time between 09:30:00 : 15:59:59 pivot by time.minute() as minute, Symbol

Then convert the minute-level mid-price matrix into a stock return matrix.

retMatrix = each(def(x):ratios(x)-1, priceMatrix)

Based on the stock return matrix, we calculate the correlation between every two columns to get a 500 by 500 pairwise correlation matrix.

correlationMatrix = corrMatrix(retMatrix[1:,].ffill())

So actually, it only takes 4 lines of code to get the pairwise correlation matrix from the high frequency data in DolphinDB.

In addition, running the script only takes 2.5 seconds.

To verify the accuracy, we get the 3 stocks with the highest correlation with Lehman Brothers from the pairwise correlation matrix. The outputs are Morgan Stanley, Goldman Sachs, and Merrill Lynch, which are all investment bank stocks that are supposed to be highly correlated with Lehman Brothers.

In this example, DolphinDB demonstrates excellent performance in data analysis of large amounts of data with elegant code and minimal coding efforts. The calculation process can be encapsulated into a function to be used in script as well as through various APIs such as Python, C++, Java, C#, Go, etc.

addFunctionView(getStockCorrelation)

Follow the video below to try the efficient calculation 👇
https://youtu.be/kmz5OsoDBs0

Accelerating Data Analysis: Embracing the Efficient Query and Aggregation Calculations

DolphinDB — Mon, 26 Aug 2024 08:18:06 +0000

Efficient query and calculation capabilities play a pivotal role in handling large-scale datasets, and ensuring timely and accurate data analysis. In this article, we will explore how DolphinDB’s query and advanced calculation features significantly enhance data processing and analysis workflows through practical code examples👇

In this case, we choose TSDB engine as the DolphinDB storage engine. We use a server with 16 CPU cores, 512 GB of memory, and 4 SSDs. The read speed of each disk is about 400 MB/s.

The data we use is the US Stock Exchange level 1 quotes of 4 years. The original data size is around 12.75 TB. After compression, the disk space occupied by DolphinDB is 2.12 TB.

All these data are stored in one table in DolphinDB, and there are almost 270 billion records.

login(`admin, `123456)
pnodeRun(clearAllCache)
quotes = loadTable("dfs://TAQ", "quotes")

First, let’s do the query.

We can get the data of Lehman Brothers at 3:59:59 PM, August 21, 2007 through the code below. It turns out that DolphinDB takes only 7.9 ms to extract one second’s data of a stock from 270 billion (12.75 TB) records.

select count(*) from quotes 

timer x=select symbol, time, bid, ofr from quotes where symbol='LEH', date=2007.08.21 and time=15:59:59

The query result is assigned to the variable x, and the content of x can be shown in the data browser.

Next, let’s calculate the average bid-ask spread of Lehman Brothers in each minute on Aug 31, 2007.

The bid-ask spread is defined as the ask price minus the bid price, and then divided by the average ask price and the bid price, and we also add some filtering conditions to ensure the validity of the data.

timer avgSpread = select avg((ofr-bid)/(ofr+bid)*2) as avgSpread from quotes where date=2007.08.31, symbol=`LEH, time between 09:30:00 : 15:59:59, ofr>bid, ofr>0, bid>0, ofr/bid<1.2 group by minute(time) as minute
plot(avgSpread.avgSpread, avgSpread.minute, "Average bid-ask spread per minute")

Run the script, and we can see that execution takes 89 milliseconds.

The calculation result is plotted in this graph.

Then we’ll increase the data volume, remove the stock code limitation, and calculate the bid-and-ask price difference per minute.

timer avgSpread = select avg((ofr-bid)/(ofr+bid)*2) as avgSpread from quotes where date=2007.09.24, time between 09:30:00 : 15:59:59, ofr>bid, bid>0, ofr/bid<1.2 group by minute(time) as minute

The data of all stocks in the whole market for a whole day is about 10 GB. Run the script, and it takes 2.1 seconds.

For a well-designed system, the time it takes to finish a query should be linearly related to the amount of days the query consumes.

So this time, we performs the same calculation on 3 days.

timer avgSpread = select avg((ofr-bid)/(ofr+bid)*2) as avgSpread from quotes where date between 2007.09.25 : 2007.09.27, time between 09:30:00 : 15:59:59, ofr>bid, bid>0, ofr/bid<1.2 group by minute(time) as minute
plot(avgSpread.avgSpread, avgSpread.minute, "Average bid-ask spread per minute")

It takes 5.47 seconds, about 3 times as long as the previous query, which means that the time consumption is directly proportional to the amount of data.

In a word, performance of DolphinDB is pretty good for both querying a small amount of data and aggregating calculations with large amounts of data.

The complete script is displayed as follows 👇👀

login(`admin, `123456)
pnodeRun(clearAllCache)
quotes = loadTable("dfs://TAQ", "quotes")

select count(*) from quotes 

timer x=select symbol, time, bid, ofr from quotes where symbol='LEH', date=2007.08.21 and time=15:59:59

timer avgSpread = select avg((ofr-bid)/(ofr+bid)*2) as avgSpread from quotes where date=2007.08.31, symbol=`LEH, time between 09:30:00 : 15:59:59, ofr>bid, ofr>0, bid>0, ofr/bid<1.2 group by minute(time) as minute
plot(avgSpread.avgSpread, avgSpread.minute, "Average bid-ask spread per minute")

timer avgSpread = select avg((ofr-bid)/(ofr+bid)*2) as avgSpread from quotes where date=2007.09.24, time between 09:30:00 : 15:59:59, ofr>bid, bid>0, ofr/bid<1.2 group by minute(time) as minute
plot(avgSpread.avgSpread, avgSpread.minute, "Average bid-ask spread per minute")

timer avgSpread = select avg((ofr-bid)/(ofr+bid)*2) as avgSpread from quotes where date between 2007.09.25 : 2007.09.27, time between 09:30:00 : 15:59:59, ofr>bid, bid>0, ofr/bid<1.2 group by minute(time) as minute
plot(avgSpread.avgSpread, avgSpread.minute, "Average bid-ask spread per minute")

And you can follow the video below, experiencing the efficient query and aggregation calculation.

https://youtu.be/A_XoHP9NboE

High Frequency Data Analysis: Converting High-frequency Signals to Discrete Buy/Sell Signals

DolphinDB — Fri, 23 Aug 2024 03:01:17 +0000

In high-frequency trading, we generate high-frequency signals from trade and quote tick data and analyze these signals to identify trading opportunities. This tutorial demonstrates how to convert high-frequency signals into discrete buy/sell/hold signals. Essentially, the problem is to convert an array of floating-point values into an array of only 3 integers: +1 (buy), 0 (hold) and -1 (sell).

The conversion rules can be quite simple. For example:

+1 if signal > t1
-1 if signal < t2
0 otherwise

Such conversion rules can be easily implemented in DolphinDB with function iif:

iif(signal > t1, 1, iif(signal <t2, -1, 0))

However, to avoid too frequent reversals of trading direction, we usually adopt a more complex set of rules: if a signal is above the t1 threshold, it’s a buy signal (+1) and the subsequent signals remain buy signals until one falls below the t10 threshold. Similarly, if a signal is below the t2 threshold, it is a sell signal (-1) and the subsequent signals remain sell signals until a signal exceeds the t20 threshold. The relationship between the thresholds is as follows:

t1 > t10 > t20 > t2

With the above rules, the value of a trade signal is determined not only by the value of the current signal but also by the state of the previous signal. This is a typical example of path dependence, which is commonly considered unsuitable or difficult to be handled by vector operations and thus very slow in scripting languages including DolphinDB.

In some cases, however, a path dependence problem can be solved with vector operations. The problem above is one such example. The next section describes how to solve it with vector operations.

First, find out the signals that fall in the ranges of a determined state:

if signal > t1, state=1
if signal < t2, state=-1
if t20<= signal <= t10, state=0

The states of the signals in the ranges of [t2,t20] and [t10,t1] are determined by the states of the signals preceding these ranges.

The DolphinDB script for implementing the above rules:

direction = (iif(signal>t1, 1, iif(signal<t2, -1, iif(signal between t20:t10, 0, NULL)))).ffill().nullFill(0)

Let’s run a simple test:

t1= 10
t10 = 6
t20 = -6
t2 = -10
signal = 20.12 8.78 4.39 -20.68 -8.49 -6.98 0.7 2.08 8.97 12.41
direction = (iif(signal>t1, 1, iif(signal<t2, -1, iif(signal between t20:t10, 0, NULL)))).ffill().nullFill(0)

The script would be like this if we use pandas:

t1=60
t10=50
t20=30
t2=20
signal=pd.Series([20.12,8.78,4.39,-20.68,-8.49,-6.98,0.7,2.08,8.97,12.41])
start=time.time()
direction1=(signal.apply(lambda signal: 1 if signal > t1 else(-1 if signal<t2 else(0 if t20 < signal < t10 else np.nan)))).ffill().fillna(0)

The test below generates a random array of 10 million signals between 0 and 100 to test the performance of DolphinDB and pandas. The test environment setup is as follows:

CPU: Intel(R) Core(TM) i7–7700 CPU @3.60GHz 3.60 GHz

Memory: 16 GB

OS: Windows 10

The executions take 171.73ms (DolphinDB) and 3.28 seconds (pandas), respectively.

DolphinDB script:

t1= 10
t10 = 6
t20 = -6
t2 = -10
signal = rand(100.0, 10000000)
direction = (iif(signal>t1, 1, iif(signal<t2, -1, iif(signal between t20:t10, 0, NULL)))).ffill().nullFill(0)

pandas script:

import time
t1= 60
t10= 50
t20= 30
t2= 20
signal= pd.Series(np.random.random(10000000) * 100)
start= time.time()
direction1=(signal.apply(lambda signal: 1 if signal > t1 else(-1 if signal<t2 else(0 if t20 < signal < t10 else np.nan)))).ffill().fillna(0)
end= time.time()
print(end- start)

How to Downsample Your Data Efficiently

DolphinDB — Thu, 22 Aug 2024 03:33:09 +0000

Tired of spending countless mintues downsampling your data? Look no further!

In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million minute-level records in only 41 seconds in DolphinDB.

The basic configuration of the DolphinDB server is:

16 CPU cores
256 GB memory
4 SSDs

A DolphinDB cluster with 4 data nodes is deployed, and each node uses a SSD.

The data we use is:

the level 1 quotes on August, 2007 from New York Stock Exchange
around 272 GB, with 6.48 billion records

Downsampling can be performed with a SQL statement in DolphinDB.

As the SQL query may involve multiple partitions, DolphinDB breaks down the job into several tasks and assigns the tasks to the corresponding data nodes for parallel execution. When all the tasks are completed, the system merges the intermediate results from the nodes to return the final result.

The script is as follows:

db = database("dfs://TAQ")
quotes = db.loadTable("quotes")
select count(*)  from quotes where date between 2007.08.01 : 2007.08.31

model=select  top 1 symbol,date, minute(time) as minute, bid, ofr from quotes where date=2007.08.01,symbol=`EBAY
if(existsTable("dfs://TAQ", "quotes_minute_sql"))
 db.dropTable("quotes_minute_sql")
db.createPartitionedTable(model, "quotes_minute_sql", `date`symbol)

timer{
 minuteQuotes=select avg(bid) as bid, avg(ofr) as ofr from quotes where data between 2007.08.01 : 2007.08.31 group by symbol,date,bar(time, 60) as minute
 loadTable("dfs://TAQ", "quotes_minute_sql").append!(minuteQuotes)
}

select count(*)  from loadTable("dfs://TAQ", "quotes_minute")

The frequency can be adjusted as needed just by modifying bar(time, 60). Here 60 means the data is downsampled to 1-minute interval as the timestamp values have seconds precision.

The table “quotes_minute_sql“ is created with createPartitionedTable and the downsampled result can be appended to this table.

We can execute the script and visit the web-based user interface to check the resource usage. It’s shown that all CPU cores have participated in the downsampling. On each data node, 15 tasks are running concurrently as data is being read from disk.

When we come back to VScode and check the execution status, we find that it only takes 41 seconds to complete the data downsampling, which generates 61 million minute-level records.

DolphinDB exhibits outstanding performance in data downsampling due to the following reasons:

Jobs are executed distributedly and resources of different nodes can be utilized at the same time;
Compression reduces the disk I/O;
Columnar storage and vectorized computation improve the efficiency of aggregation.

To learn detailed operations of data downsampling, take a look at this demo!
https://youtu.be/0vRuiz1Lf6Y

A Simpler Way to Calculate WorldQuant 101 Alphas

DolphinDB — Wed, 21 Aug 2024 02:31:10 +0000

The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation.

Take the calculation formula of Alpha#98 for example, and the data we use is the daily data from the New York Stock Exchange.

Kakushadze et al 2015 Alpha #98: 
(rank(decay_linear(correlation(vwap, sum(adv5, 26.4719), 4.58418), 7.18088)) -
rank(decay_linear(Ts_Rank(Ts_ArgMin(correlation(rank(open), rank(adv15), 20.8187), 8.62571), 6.95668), 8.07206)))

This formula involves both cross-sectional data and time series data, and the calculation uses nested functions with up to 6 levels, which is extremely difficult to implement in most systems.

We can significantly reduce the development cost of complex calculations such as Alpha#98 by using DolphinDB’s built-in functions with panel data (in matrix form).

Why is DolphinDB’s code so concise and elegant? 😲

On the one hand, using panel data provided by DolphinDB to implement the Alpha#98 factor simplifies the calculation logic and makes the code very precise. Panel data is a matrix that combines cross-sectional data and time-series data.

On the other hand, DolphinDB has more than 1,500 built-in functions and many of them are optimized. You can implement all 101 alphas with DolphinDB built-in functions.

Let’s take a look at DolphinDB script, which is very similar to the original formulas.

def alpha98Panel(vwap, open, vol){
    return tsRank(mavg(mcorr(vwap, msum(mavg(vol, 5), 26), 5), 1..7)) - tsRank(mavg(mrank(9 - mimin(mcorr(tsRank(open), 
    tsRank(mavg(vol, 15)), 21), 9), true, 7), 1..8))
}

Run the script, and it only takes 986ms to generate a matrix with 7,162 columns and 252 rows and calculate Alpha#98 with the matrix.

Furthermore, DolphinDB supports unified stream and batch processing. It provides the streamEngineParser function to automatically form a pipeline of stream engines to carry out the specified metrics calculation. You can directly use the Alpha#98 function as the metrics, with no need to modify the script for metrics calculation.

Click the demo below to get more info! 👇
https://youtu.be/B0lYBAI_FEc

DolphinDB to Offer Access to ICE Consolidated Feed and Historical Tick Data

DolphinDB — Tue, 20 Aug 2024 07:30:41 +0000

In March 2023, DolphinDB, Inc., a leading provider of time-series databases, and Intercontinental Exchange (NYSE: ICE), agreed to offer access to the ICE Consolidated Feed and its historical tick data services on DolphinDB’s high-performance time series database. This new offering provides a more efficient and reliable workflow for DolphinDB clients to access ICE’s market data for faster deployment of trading strategy research, testing and executions.

DolphinDB is a high-performance distributed time-series database, which has been widely adopted by brokerage firms, mutual funds, hedge funds, banks, and exchanges. Through this agreement, DolphinDB users can easily access and analyze a diverse range of market data from ICE for crucial insights regarding investment decisions and risk management.

The ICE Consolidated Feed provides a broad range of real-time and intra-day data and analytics from over 600 markets and third-party data sources. Global organizations including major banks, brokerage firms, and investment management firms utilize the ICE Consolidated Feed to power their pricing and analytics data requirements. Together, ICE and DolphinDB can offer clients an efficient way to manage, process and analyze massive amounts of market data.

“We are excited to work with ICE to bring a comprehensive and efficient suite of data solutions to our clients,” said Dr. Davis Zhou, CEO and founder of DolphinDB. “This integration brings together the products and resources of both parties, which transforms and streamlines data acquisition, processing, and analysis throughout the investment research process. We look forward to a collaboration where we bring out the best in each other to accelerate the development in fintech.”

“We are pleased to work with DolphinDB, and offer more clients access to ICE’s suite of market data offerings, by leveraging a streamlined integration for fast deployment and reliability,” said Magnus Cattan, Head of ICE Fixed Income and Data Services in APAC. “Our data provides a competitive option to DolphinDB’s clients for accessing in-depth cross-asset global market data.”

Both DolphinDB and ICE are committed to delivering the highest quality solutions for quantitative investment research, and this collaboration further solidifies their shared vision of advancing the industry through collaboration and innovation. This collaboration will leverage DolphinDB’s data storage and analytics capabilities and ICE’s global market data reach & expertise to deliver comprehensive, reliable and timely financial data solutions to global market participants.

About DolphinDB

DolphinDB offers a unified platform for data warehouse, analytics and streaming workloads. At its core it is a high-performance distributed time-series database. With a fully featured programming language, over 1500 built-in functions and a suite of stream computing engines, DolphinDB enables rapid development of high performance applications for mission critical tasks in global trading.

About Intercontinental Exchange

Intercontinental Exchange, Inc. (NYSE: ICE) is a Fortune 500 company that designs, builds and operates digital networks to connect people to opportunity. We provide financial technology and data services across major asset classes that offer our customers access to mission-critical workflow tools that increase transparency and operational efficiencies. We operate exchanges, including the New York Stock Exchange, and clearing houses that help people invest, raise capital and manage risk across multiple asset classes. Our comprehensive fixed income data services and execution capabilities provide information, analytics and platforms that help our customers capitalize on opportunities and operate more efficiently. At ICE Mortgage Technology, we are transforming and digitizing the U.S. residential mortgage process, from consumer engagement through loan registration. Together, we transform, streamline and automate industries to connect our customers to opportunity.

Integrating Crypto Market Data From Multiple Sources

DolphinDB — Tue, 20 Aug 2024 07:06:39 +0000

Cryptocurrency trading professionals typically use Python, Java, Rust, or C++ for exchange API access. Single-source data access presents inherent limitations, such as data loss from server downtime, delayed updates, and limited data quality and refresh rates, which can potentially impact trading decisions.

This article introduces a DolphinDB solution to access market streams (with Binance Exchange as an example) through both WebSocket and REST APIs. Performance test shows that this integrated approach enhances continuous data access and comprehensive coverage and improves data quality by providing more timely and frequent market updates. By implementing higher frequency data refreshes within shorter intervals, traders can achieve superior market responsiveness and make more informed decisions.

1. Background

Binance offers two ways for accessing the market streams:

REST API: Suitable for retrieving static data but requires polling for real-time updates.
WebSocket API: Designed for accessing real-time market streams. By maintaining a persistent connection, it enables low-latency retrieval of the latest data.

Binance provides multiple base endpoints, each with distinct performance and stability characteristics. We recommend that users test the performance and stability of each base endpoints on the designated server in their specific business context and determine the optimal number of data sources and retrieval methods based on their needs. The proposed approach combines the strengths of both methods to enhance performance and stability by subscribing to data on different base URLs using the same account and setting different processing frequencies.

2. Implementation Overview

The integrated market data solution comprises the following components.

Accessing Market Streams from Binance Exchange

For each cryptocurrency pair, market data is accessed from Binance via both WebSocket API (with subscription frequency at 100 ms) and REST API (with polling interval of 50 ms).

Processing Data from WebSocket API

Subscribing to the data from WebSocket API, and writing it to a shared stream table.
Persisting the stream table to a DFS partitioned table.

Processing Data from REST API

Setting up HTTP requests to fetch data from REST API.
Writing the data to a shared stream table.
Persisting the stream table to a DFS partitioned table.

Cleaning and Integrating Received Data

Creating a shared dictionary to maintain the latest timestamp for each trading pair.
Defining rules for filtering data with latest timestamps, and integrating selected streams into a shared stream table.
Upon receiving new data, comparing timestamps of incoming data with those in table to ensure only the latest data is written.
Persisting the stream table to a DFS partitioned table.

3. Accessing Market Streams

This chapter outlines the steps of accessing market streams from Binance’s WebSocket and REST APIs using DolphinDB WebSocket and HttpClient Plugins.

3.1 Accessing Data From WebSocket API

We establish a WebSocket subscription task using the DolphinDB WebSocket plugin to access real-time market depth data from Binance WebSocket API.

Step 1: Install and load the DolphinDB WebSocket plugin. This step can be skipped if the plugin is already loaded.

installPlugin("WebSocket")
loadPlugin("WebSocket")

Step 2: Create a shared stream table wssDepthST and persist it to disk.

colNames = `type`eventTime`transactionTime`code`firstId`lastId`finalId`bidPrice`bidQty`askPrice`askQty`currentTime
colTypes = [SYMBOL, TIMESTAMP, TIMESTAMP, SYMBOL, LONG, LONG, LONG, DOUBLE[], DOUBLE[], DOUBLE[], DOUBLE[], TIMESTAMP]
enableTableShareAndPersistence(table=streamTable(10000:0, colNames, colTypes), tableName=`wssDepthST, cacheSize=12000)

Step 3: Define functions for handling market data.

The market depth data accessed through WebSocket API is formatted as follows:

{"e": "depthUpdate",  "E": 1571889248277, "T": 1571889248276, "s": "BTCUSDT", "U": 390497796, "u": 390497878, "pu": 390497794,
 "b": [["7403.89", "0.002"], ["7403.90", "3.906"], ["7404.00", "1.428"] ,["7404.85", "5.239"], ["7405.43","2.562"]],
 "a": [["7405.96","3.340" ], ["7406.63", "4.525"], ["7407.08", "2.475"], ["7407.15", "4.800"], ["7407.20","0.175"]]}

The following script defines functions for processing the incoming data in DolphinDB.

// Define parseDepth for parsing market depth data
def parseDepth(rawData){
    rawDataDict = parseExpr(rawData).eval().data
    if (rawDataDict["e"]=="depthUpdate"){
        // Process bid data
        bTransposed = rawDataDict.b.double().matrix(b).transpose()
        bidPrice = bTransposed[0].enlist()
        bidQty = bTransposed[1].enlist()
        // Process ask data
        aTransposed = rawDataDict.a.double().matrix(a).transpose()
        askPrice = aTransposed[0].enlist()
        askQty = aTransposed[1].enlist()
        // Extract other relevant fields
        type = rawDataDict["e"]
        eventTime = timestamp(rawDataDict["E"])
        transactionTime = timestamp(rawDataDict["T"])
        code = rawDataDict["s"]
        firstId = rawDataDict["U"]
        lastId = rawDataDict["u"]
        finalId = rawDataDict["pu"]
        currentTime = gmtime(now())
        // Construct a table
        return table(typ, eventTime, transactionTime, code, firstId, 
          lastId, finalId, bidPrice, bidQty, askPrice, askQty, currentTime)
    }
}
// WebSocket event handlers
def onOpen(ws){
    writeLog("WebSocket opened to receive data")
}

def onMessage(mutable streamTable, ws, dataTable){
    for (data in dataTable[`msg]){
        res = parseDepth(data)
        streamTable.append!(res)
    }
}

def onError(ws, error){
    writeLog("WebSocket failed to receive data: " + error.string())
}

def onClose(ws, statusCode, msg){
    writeLog("Connection is closed, status code: " + statusCode.string() + ", " + 
      msg.string())
}

Step 4: Create a WebSocket subscription task.

// Connect to Binance WebSocket API
url = "wss://fstream.binance.com/stream?streams=btcusdt@depth20@100ms"
config = dict(STRING, ANY)
// Create a WebSocket subscription task
ws = WebSocket::createSubJob(url, onOpen, onMessage{streamTable=wssDepthST}, onError, onClose, "wssDepth", config)

Step 5: Manage the subscription.

After establishing the subscription, use getSubJobStat to view the subscription status:

// Check subscription status
WebSocket::getSubJobStat()

To cancel the subscription, use cancelSubJob:

// Cancel subscription
WebSocket::cancelSubJob("wssDepth")

For the complete script for Binance WebSocket data access, refer to Appendix binanceDepthWS.

3.2 Accessing Data From REST API

We create a task to send periodic HTTP requests through the HttpClient Plugin for accessing the latest market depth data from Binance REST API.

Step 1: Install and load the DolphinDB HttpClient plugin. This step can be skipped if the plugin is already loaded.

installPlugin("httpClient")
loadPlugin("httpClient")

Step 2: Create a shared stream table restDepthST and persist it to disk.

colNames = `eventTime`transactionTime`code`lastUpdateId`bidPrice`bidQty`askPrice`askQty`currentTime
colTypes = [TIMESTAMP, TIMESTAMP, SYMBOL, LONG, DOUBLE[], DOUBLE[], DOUBLE[], DOUBLE[], TIMESTAMP]
enableTableShareAndPersistence(table=streamTable(100000:0, colNames, colTypes), tableName=`restDepthST, cacheSize=12000)

Step 3: Define functions for handling market data.

The market depth data accessed through REST API is formatted as follows:

{"lastUpdateId": 1027024,"E": 1589436922972,"T": 1589436922959,
 "bids": [["4.00000000", "431.00000000"]],
 "asks": [["4.00000200", "12.00000000"]]}

Define a getDepth function that sends HTTP requests at regular intervals (every 50 ms) to continuously fetch the latest market data. The retrieved data is parsed and ingested into a stream table.

// Define getDepth to fetch market data
def getDepth(mutable restDepthST, code, baseUrl){
    do{
        try{
        param = dict(string,string)
        param['symbol'] = code;
        res = httpClient::httpGet(baseUrl,param,10000)

        rawDataDict = parseExpr(res.text).eval()
        b = double(rawDataDict.bids)
        bTransposed = matrix(b).transpose()
        bidPrice = bTransposed[0]
        bidQty = bTransposed[1]
        a = double(rawDataDict.asks)
        aTransposed = matrix(a).transpose()
        askPrice = aTransposed[0]
        askQty = aTransposed[1]

        eventTime = timestamp(rawDataDict["E"])
        transactionTime = timestamp(rawDataDict["T"])
        lastUpdateId = long(rawDataDict["lastUpdateId"])
        currentTime = gmtime(now())

        resTable = table(eventTime as eventTime, transactionTime as transactionTime, 
            code as code, lastUpdateId as lastUpdateId, [bidPrice] as bidPrice, 
            [bidQty] as bidQty, [askPrice] as askPrice, [askQty] as askQty, currentTime as currentTime)
        restDepthST.append!(resTable)
        sleep(50) 
        }
        catch(ex){
            print(ex)
            continue
        }
    }while(true)
}

Step 4: Set the base endpoint and submit the job with the name getDepth_BTC.

baseUrl = "https://fapi.binance.com/fapi/v1/depth"
submitJob("getDepth_BTC","getDepth_BTC", getDepth, restDepthST, "btcusdt", baseUrl)

Step 5: Manage submitted jobs.

// View recent jobs
getRecentJobs()
// View intermediate job information
getJobMessage("getDepth_BTC")
// Cancel the job
cancelJob('getDepth_BTC')

For the complete script for Binance REST data access, refer to Appendix binanceDepthREST.

4. Integrating Multi-Source Data

The stream tables wssDepthST and restDepthST receive market data from Binance WebSocket and REST APIs. Due to the differences in subscription methods and delays, rules for aligning and cleaning such multi-source market data are required for later data integration. These rules are designed to compare the timestamp of the incoming data with that in table to determine if the data provides the most recent information.

Specifically, we implement data integration by creating a shared dictionary to store the latest timestamps for each cryptocurrency pair and using a stream table latestDepthST to store the integrated market data. The table subscribes to wssDepthST and restDepthST with handling rules to update records with latest timestamps.

Step 1: Create a shared dictionary for maintaining the latest timestamps for each trading pair to track the most recent update time.

latestTs=syncDict(SYMBOL,TIMESTAMP)
latestTs['btcusdt'] = 2024.05.16 06:39:17.513

Step 2: Set rules for filtering and ingesting data from restDepthST and wssDepthST into table latestDepthST. By extracting data and event times, the rules check if the record has the latest timestamp. If true, the shared dictionary is updated and new records are appended to latestDepthST.

def toMerged(mutable latestTs, routeName, msg){ 
    symbolID = exec code from msg limit 1
    Timets = exec eventTime from msg limit 1
    lts = latestTs[symbolID]
    if(Timets > lts){
        latestTs[symbolID] = Timets
        appendTestTb = select code, eventTime, lastUpdateId, bidPrice, bidQty, askPrice, askQty, currentTime, routeName as dataroute from msg
        objByName("latestDepthST").append!(appendTestTb)
    }
}

Step 3: Subscribe to wssDepthST and restDepthST and specify the handler with filtering rules.

subscribeTable(tableName="restDepthST", actionName="restTolive", offset=-1, handler=toMerged{latestTs, 'restapi'}, msgAsTable=true, batchSize=1, throttle=0.001)
subscribeTable(tableName="wssDepthST", actionName="wssTolive", offset=-1, handler=toMerged{latestTs, 'wssapi'}, msgAsTable=true, batchSize=1, throttle=0.001)

This method allows subscriptions to multiple sources to be integrated into one table. Rules should be specified based on the format of the obtained market data.

5. Performance Test

We test the data coverage of using WebSocket and REST APIs separately and together by evaluating the data volume received within 24 hours. Larger data volume indicates higher refresh rates on average.

In our test environment, we use two methods to obtain BTCUSDT spot depth data: WebSocket with a 100-ms frequency (maximum available speed) and REST polling with a 50-ms interval. We then filter and insert the latest market data into a table. While the 50-ms update speed is ideal for balancing update frequency and market data acquisition across multiple currency pairs given bandwidth limitations, it’s important to note that the REST polling method actually yields higher data volumes due to its faster 50 ms interval.

After 24 hours of data acquisition:

REST polling: 1,383,480
WebSocket subscription: 644,808
Duplicate records: 10,020

The latest market table latestDepthST receives 1,753,067 new records, with the specific composition as follows:

From the results, we can see that the number of new records added to latestDepthST is greater than the number of records obtained separately through WebSocket and REST. Moreover, a higher proportion of REST data in the table indicates that this multi-source integration approach can increase the frequency and timeliness of data acquisition compared to a single access method. Additionally, since the data obtained from WebSocket and REST APIs are pushed from different servers, this method helps avoid problems caused by a single server failure. This further demonstrates that multi-source market data access can obtain market streams more quickly, reducing the risks associated with network fluctuations and exchange service failures.

6. Conclusion

This article introduces a DolphinDB-based solution in multi-source market stream access and integration of cryptocurrency market. By integrating multiple data sources and implementing efficient data cleansing and storage, DolphinDB not only improves the refresh frequency and timeliness of data acquisition but also ensures data continuity and reliability. The test results show that this approach significantly increases the amount of market data obtained and improves the data refresh interval, providing traders with more reliable and timely market data support.

In summary, DolphinDB’s multi-source market data integration solution significantly enhances the cryptocurrency market data acquisition process, providing traders with a superior framework for strategic trading decisions and activities.

DEV Community: DolphinDB

We Rebuilt a Securities Firm's FICC Data Stack — Queries Got 10x Faster

FICC Quantitative Trading: Speed Creates Alpha

The Challenge: Data Fragmentation Was Slowing Down the Business

The Solution: An FICC Research Data Platform Powered by DolphinDB

Consolidating Historical Data

Building a Real-Time Market Data Hub

The Results

What's Next: AI-Powered Research and High-Fidelity Backtesting

What’s New in DolphinDB：Introducing DolphinX, an Enterprise-Grade Platform for Agent Development and Governance

1. DolphinX: An Enterprise-Grade AI Development and Governance Platform

2. FeatureDB: A Low-Latency Feature Storage Engine for AI and Machine Learning Applications

3. Building a Complete Pricing Loop: Comprehensive FICC Capability Enhancements

4. Enhanced JIT Capabilities with Broader Language Support and Greater Stability

5. Upgraded Dynamic Script Optimization for Faster Nested Functions and Loop Execution

6. Enhanced High Availability for Streaming Data

7. Improvements to Core Capabilities

8. Enhanced System Operations

9. Performance and User Experience Improvements

10. Roadmap

How Crypto Pin Bars Form and How to Detect Them: Lessons from a $19B Liquidation

What Is a Pin Bar?

Why Do Pin Bars Occur?

Reconstructing October 10: What the Data Saw

Identifying Pin Bars

Liquidation Data: Reconstructing the Cascade of Forced Liquidations

Trading Volume Reveals the Sentiment Turning Point

The Signals That Were Already There

VPIN: Tracking the Evolution of Order Flow Toxicity

Order Book Imbalance: Detecting the Early Signs of Liquidity Exhaustion

Building a Real-Time Risk Monitoring System with VPIN

Conclusion

Reference

Introduction to backtesting strategy: Historical data replay in DolphinDB

1. Functions

2. How to use replayed data

3. Examples

4. Performance testing

Helpful Tools for Quant丨Efficiently Calculate Transaction Costs from Tick Data

Mastering Pairwise Correlations Calculation of Securities Through Coding

Accelerating Data Analysis: Embracing the Efficient Query and Aggregation Calculations

High Frequency Data Analysis: Converting High-frequency Signals to Discrete Buy/Sell Signals

How to Downsample Your Data Efficiently

A Simpler Way to Calculate WorldQuant 101 Alphas

DolphinDB to Offer Access to ICE Consolidated Feed and Historical Tick Data

Integrating Crypto Market Data From Multiple Sources

1. Background

2. Implementation Overview

3. Accessing Market Streams

3.1 Accessing Data From WebSocket API

3.2 Accessing Data From REST API

4. Integrating Multi-Source Data

5. Performance Test

6. Conclusion

7. Appendix