Quant Analytics — Vectorized Bitcoin Research Engine

A quantitative research pipeline for Bitcoin/USD daily OHLCV data, combining MySQL-based data engineering and validation with a vectorized Python analytics engine.

The project is designed as a Phase 1 quantitative research foundation: first establish reliable market data, mathematically correct transformations, deterministic processing, and clear analytical visualizations before building higher-level trading systems.

---

Overview

This project processes historical Bitcoin daily OHLCV data through two primary layers:

Raw Bitcoin Data
       │
       ▼
┌──────────────────────┐
│      MySQL / SQL     │
│                      │
│ Import               │
│ Schema               │
│ Validation           │
│ Quality Checks       │
│ Extraction           │
└──────────┬───────────┘
           │
           ▼
┌──────────────────────┐
│   Python Analytics   │
│                      │
│ Returns              │
│ Log Returns          │
│ Drawdowns            │
│ Volatility           │
│ Direction            │
└──────────┬───────────┘
           │
           ▼
┌──────────────────────┐
│    Visualization     │
│                      │
│ Candlestick          │
│ Price Series         │
│ Returns              │
│ Log Returns          │
│ Drawdowns            │
│ Volatility           │
└──────────────────────┘

The core design principle is:

Validate the data before analyzing it, and verify the mathematics before trusting the output.

---

Project Status

Phase: 1 — Quantitative Analytics Foundation

Asset: Bitcoin / USD

Frequency: Daily

Market: 24/7 cryptocurrency market

Annualization convention: 365 observations/year

Primary technologies: MySQL, Python, NumPy, Pandas, Matplotlib

---

Objectives

Phase 1 focuses on establishing a reliable analytical foundation.

Primary objectives

• Import historical Bitcoin OHLCV data into MySQL.
• Validate the structure and integrity of the dataset.
• Extract clean market data into Python.
• Calculate simple and logarithmic returns.
• Calculate cumulative returns.
• Calculate historical drawdowns.
• Calculate rolling annualized volatility.
• Generate directional features.
• Produce analytical visualizations.
• Keep the numerical computation primarily vectorized.
• Separate data engineering from analytical computation.
• Create a foundation for future quantitative research.

This project is infrastructure and research tooling.

It is not yet a trading strategy or production trading system.

---

Tech Stack

Layer	Technology
Database	MySQL
SQL	MySQL SQL / CTEs
Programming	Python
Numerical Computing	NumPy
Data Processing	Pandas
Visualization	Matplotlib
Database Connection	MySQL Connector
Configuration	.env
Path Handling	pathlib

---

Repository Structure

Phase 1 Remastered/
│
├── .vscode/
│
├── docs/
│
├── Python/
│   │
│   ├── data/
│   │   └── Gemini_BTCUSD_1d.csv
│   │    
│   │       
│   │
│   ├── src/
│   │   ├── analytics_core/
│   │   │   ├── __init__.py
│   │   │   ├── features.py
│   │   │   ├── drawdown.py
│   │   │   ├── get_data.py
│   │   │   ├── plotting.py
│   │   │   ├── returns.py
│   │   │   └── volatility.py
│   │   │
│   │   └── demo_workflow.py
│   │
│   ├── tests/
│   ├── .env
│   ├── README.md
│   └── run_current.py
│
├── SQL_market_data_build/
│   │
│   ├── docs/
│   ├── notebook/
│   ├── samples/
│   │
│   ├── SQL/
│   │   ├── 00_table_creation.sql
│   │   ├── 01_inspect_source_table.sql
│   │   ├── 02_validate_join_keys.sql
│   │   ├── 03_base_ohlcv_extract.sql
│   │   ├── 04_date_filtered_extract.sql
│   │   ├── 05_quality_checks.sql
│   │   ├── 06_final_export_query.sql
│   │   └── data_import.sql
│   │
│   └── README.md
│
├── .gitignore
├── Project Overview.md
└── README.md

---

Data

The primary dataset used in this project is:

Gemini_BTCUSD_1d.csv

The dataset contains daily Bitcoin/USD OHLCV observations.

The main market variables are:

Variable	Description
Open	Opening price
High	Highest price during the period
Low	Lowest price during the period
Close	Closing price
Volume	Traded volume
Timestamp	Observation time

The Python data layer retrieves the validated dataset from MySQL, converts the required fields to numerical types, and ensures the resulting time series is chronologically ordered.

---

SQL Data Pipeline

The SQL layer is organized into a sequential data-engineering pipeline.

00_table_creation.sql
        │
        ▼
01_inspect_source_table.sql
        │
        ▼
02_validate_join_keys.sql
        │
        ▼
03_base_ohlcv_extract.sql
        │
        ▼
04_date_filtered_extract.sql
        │
        ▼
05_quality_checks.sql
        │
        ▼
06_final_export_query.sql

The raw dataset is imported through:

data_import.sql

Each stage has a specific responsibility rather than combining the entire database workflow into one script.

---

Important Setup: CSV Path

Before running:

data_import.sql

you must replace the existing CSV path with the full absolute path to your local Bitcoin dataset.

The path must point to:

Gemini_BTCUSD_1d.csv

located in:

Python/data/

For example:

LOAD DATA LOCAL INFILE
'C:/Users/YourName/Projects/Phase 1 Remastered/Python/data/Gemini_BTCUSD_1d.csv'

Use the actual path on your machine.

Do not rely on:

Gemini_BTCUSD_1d.csv

alone if MySQL requires a full filesystem path.

Required setup

data_import.sql
      │
      ▼
Replace existing CSV path
      │
      ▼
Full absolute filesystem path
      │
      ▼
Gemini_BTCUSD_1d.csv
      │
      ▼
Run SQL import

If MySQL has local file loading disabled, the appropriate LOCAL INFILE configuration must also be enabled.

---

Data Integrity

The SQL layer is responsible for validating the market data before it reaches the Python analytics engine.

For an OHLC observation:

$$ H_t \ge \max(O_t, C_t) $$

$$ L_t \le \min(O_t, C_t) $$

and therefore:

$$ H_t \ge L_t $$

where:

• $O_t$ = Open
• $H_t$ = High
• $L_t$ = Low
• $C_t$ = Close

The pipeline also checks for issues such as:

• Invalid OHLC relationships.
• Invalid timestamps.
• Duplicate observations.
• Invalid join keys.
• Missing required values.
• Chronological inconsistencies.
• Invalid price or volume observations.

The goal is to prevent structurally invalid market data from contaminating downstream calculations.

---

Python Analytics Core

The Python numerical engine is located at:

Python/src/analytics_core/

The core modules are:

analytics_core/
├── features.py
├── drawdown.py
├── get_data.py
├── plotting.py
├── returns.py
└── volatility.py

Module responsibilities

Module	Responsibility
features.py	Market feature generation
drawdown.py	Drawdown calculations
get_data.py	Database access and data preparation
plotting.py	Analytical visualizations
returns.py	Simple/log/cumulative return calculations
volatility.py	Rolling volatility calculations

---

Mathematical Framework

1. Simple Returns

For closing prices $P_t$, the simple return is:

$$R_t = \frac{P_t - P_{t-1}}{P_{t-1}}$$

which is equivalent to:

$$R_t = \frac{P_t}{P_{t-1}}-1$$

A return of:

$$R_t=0.05$$

represents a 5% increase relative to the previous observation.

---

2. Log Returns

The logarithmic return is:

$$r_t = \ln\left( \frac{P_t}{P_{t-1}} \right)$$

Log returns have the important additive property:

$$\sum_{t=1}^{T}r_t=\ln\left(\frac{P_T}{P_0}\right)$$

This makes log returns particularly useful for:

• Volatility estimation.
• Time-series analysis.
• Cumulative log-growth calculations.
• Statistical modeling.

---

3. Cumulative Simple Returns

Simple returns compound multiplicatively.

Given:

$$R_1,R_2,\ldots,R_T$$

the cumulative simple return is:

$$R_{\mathrm{cum},T}=\prod_{t=1}^{T}(1+R_t)-1$$

For example, a +10% return followed by a -10% return gives:

$$(1.10)(0.90)-1=-0.01$$

Therefore the total compounded return is:

$$-1%$$

rather than 0%.

---

4. Cumulative Log Returns

The cumulative log-return series is:

$$L_t =\sum_{i=1}^{t}r_i$$

Since:

$$\sum_{i=1}^{t}r_i=\ln\left(\frac{P_t}{P_0}\right)$$

the cumulative log-return value represents logarithmic growth.

To convert it into a conventional compounded return:

$$R_{\mathrm{cum},t}=e^{L_t}-1$$

The distinction matters:

Cumulative log return:
    Σ log returns

Cumulative compounded return:
    exp(Σ log returns) - 1

The analytical engine keeps these concepts distinct.

---

5. Drawdown

Let the running historical maximum be:

$$M_t = \max(P_1,P_2,\ldots,P_t)$$

The drawdown at time (t) is:

$$D_t = \frac{P_t}{M_t}-1$$

Because:

$$P_t \leq M_t$$

we have:

$$D_t \leq 0$$

A drawdown of:

$$D_t=-0.40$$

means Bitcoin is currently 40% below its previous running peak.

Maximum drawdown is:

$$MDD = \min_t(D_t)$$

For example:

Maximum Drawdown = -0.73

corresponds to a 73% peak-to-trough decline.

The drawdown calculation uses cumulative maximum operations rather than unnecessary Python-level state iteration.

---

6. Rolling Volatility

The volatility engine calculates rolling standard deviation from log returns.

For a rolling window of (N) observations:

$$\sigma_t = \text{std}\left(r_{t-N+1}, \ldots, r_t\right)$$

The rolling statistic is based on the standard deviation of the observed log-return window.

A shorter window reacts more quickly to changes in market conditions but produces noisier estimates.

A longer window produces smoother estimates but reacts more slowly.

---

7. Bitcoin Annualized Volatility

Bitcoin trades continuously:

24 hours/day
7 days/week
365 days/year

Therefore the project uses:

$$N_{\text{year}}=365$$

for annualization.

For daily volatility:

$$\sigma_{\mathrm{annual}}=\sigma_{\mathrm{daily}}\sqrt{365}$$

The project therefore uses:

$$\boxed{\sigma_{\mathrm{annual}}=\sigma_{\mathrm{daily}}\sqrt{365}}$$

rather than the traditional equity-market convention:

$$\sqrt{252}$$

The distinction is intentional.

For Bitcoin daily data, the annualization factor is:

365

---

8. Features

The features.py module generates features from the market data.

These features provide a compact representation of price direction that can later be used by:

• Signal research.
• Feature engineering.
• Backtesting.
• Statistical analysis.

The exact classification behavior is defined by the implementation in:

Python/src/analytics_core/features.py

rather than by an assumed universal definition of bullish or bearish behavior.

---

Vectorized Computation

The analytical engine is designed around vectorized NumPy/Pandas operations.

For example:

log_returns = np.log(close / close.shift(1))

and:

running_peak = close.cummax()
drawdown = close / running_peak - 1

These operations allow calculations to be performed over entire arrays/Series rather than manually iterating through every observation.

The goal is not to eliminate every loop regardless of context.

The goal is:

Use vectorized operations when the mathematical transformation naturally maps to array operations.

This keeps the analytical layer concise and allows numerical operations to be handled by optimized underlying implementations.

---

Data Access

The Python data layer retrieves database credentials through environment variables and establishes the MySQL connection using the configured settings.

After retrieving the data, the pipeline:

1. Loads the SQL query.
2. Executes the database query.
3. Converts required fields into numerical types.
4. Structures the time series.
5. Sets the observation time as the temporal index.
6. Verifies chronological ordering.

Chronological ordering is critical because returns, rolling volatility, and drawdowns are all time-dependent calculations.

A correctly calculated return on incorrectly ordered data is still a meaningless result.

---

Visualization

The project includes six primary visualization functions:

plot_candlestick_chart(df)

plot_price_series(df)

plot_returns(df)

plot_log_returns(df)

plot_drawdowns(df)

plot_volatility(df, 30, 365)

Together, these plots provide a visual diagnostic layer for the quantitative analytics engine.

---

1. Bitcoin Candlestick Chart

Function:

plot_candlestick_chart(df)

The candlestick chart displays Bitcoin's daily OHLC structure:

• Open
• High
• Low
• Close

It is useful for visually inspecting daily price ranges and historical price behavior.

Candlestick Chart

---

2. Bitcoin Price Series

Function:

plot_price_series(df)

This plot displays the Bitcoin price series across the available historical dataset.

It provides the most direct representation of the evolution of BTC/USD price.

Price Series

---

3. Returns

Function:

plot_returns(df)

This visualization displays the return behavior of Bitcoin over time.

Simple returns are defined as:

$$R_t = \frac{P_t}{P_{t-1}}-1$$

The plot can be used to inspect:

• Positive and negative return events.
• Return clustering.
• Extreme daily movements.
• Changes in market activity.

Returns Plot

---

4. Log Returns

Function:

plot_log_returns(df)

This visualization displays Bitcoin's logarithmic return series:

$$r_t =\ln\left(\frac{P_t}{P_{t-1}}\right)$$

Log returns are particularly useful for volatility and statistical analysis because they are additive through time.

Log Returns Plot

---

5. Historical Drawdowns

Function:

plot_drawdowns(df)

The drawdown plot measures the decline from Bitcoin's previous running peak.

$$D_t = \frac{P_t}{M_t}-1$$

where:

$$M_t = \max(P_1,\ldots,P_t)$$

This visualization makes major historical declines immediately visible.

Historical Drawdowns

---

6. Rolling Annualized Volatility

Function:

plot_volatility(df, 30, 365)

The volatility plot uses:

Rolling window = 30 days
Annualization factor = 365

The rolling volatility is based on log returns and is annualized using:

$$\sigma_{\mathrm{annual}}=\sigma_{\mathrm{daily}}\sqrt{365}$$

This provides a view of how realized Bitcoin volatility changes through time.

Rolling Volatility

---

Complete Visualization Overview

The six visualizations provide different perspectives on the same underlying market data.

Function	Purpose
plot_candlestick_chart(df)	Daily OHLC structure
plot_price_series(df)	Bitcoin price history
plot_returns(df)	Return behavior
plot_log_returns(df)	Log-return behavior
plot_drawdowns(df)	Historical peak-to-trough losses
plot_volatility(df, 30, 365)	30-day annualized volatility

The relationship can be summarized as:

                     BTC/USD OHLCV
                           │
            ┌──────────────┴──────────────┐
            │                             │
            ▼                             ▼
      Price Structure                Price Changes
            │                             │
       ┌────┴────┐                   ┌────┴────┐
       ▼         ▼                   ▼         ▼
 Candlestick   Price              Returns   Log Returns
       │         │                   │         │
       └─────────┴───────────────────┴─────────┘
                           │
                           ▼
                    Risk Analytics
                           │
                    ┌──────┴──────┐
                    ▼             ▼
                Drawdowns     Volatility

---

Installation

Requirements

Recommended environment:

Python 3.10+
MySQL 8.0+

Install the Python dependencies:

pip install -r requirements.txt

The project uses packages including:

numpy
pandas
matplotlib
mysql-connector-python
python-dotenv

Exact dependency versions should eventually be pinned for fully reproducible environments.

---

Environment Configuration

Database credentials should not be hard-coded into source code.

Create a .env file containing the required database configuration.

Example:

DB_HOST=localhost
DB_PORT=3306
DB_NAME=your_database
DB_USER=your_username
DB_PASSWORD=your_password

Do not commit real credentials to Git.

The .env file should remain excluded through .gitignore.

---

Database Setup

Navigate to:

SQL_market_data_build/SQL/

The database construction begins with:

00_table_creation.sql

The general pipeline is:

Create Tables
      │
      ▼
Inspect Source
      │
      ▼
Validate Join Keys
      │
      ▼
Build Base OHLCV Extract
      │
      ▼
Apply Date Filter
      │
      ▼
Run Quality Checks
      │
      ▼
Final Export

---

Importing the Bitcoin Dataset

Before executing:

data_import.sql

replace the existing CSV path with the full absolute path to:

Python/data/Gemini_BTCUSD_1d.csv

Example:

LOAD DATA LOCAL INFILE
'C:/Users/YourName/Projects/Phase 1 Remastered/Python/data/Gemini_BTCUSD_1d.csv'

Use the actual path on your computer.

Do not assume that the current terminal directory is the same as the location of the CSV.

---

Running the Python Pipeline

From the Python/ directory:

python src/demo_workflow.py

The primary workflow entry point is:

Python/src/demo_workflow.py

The analytics engine is located at:

Python/src/analytics_core/

---

Reproducibility

The project is designed to keep data processing deterministic.

For reproducible results:

0. Clone this repository:

git clone https://github.com/Jad-srifi/vectorized-quant-analytics.git

1. Use the same source dataset.
2. Use the same database contents.
3. Use the same SQL transformations.
4. Use the same environment configuration.
5. Use the same Python dependencies.
6. Keep analytical parameters fixed.
7. Avoid modifying the raw dataset between runs.

The Python data layer also verifies chronological ordering before performing temporal calculations.

---

Validation Checklist

Before trusting the output, verify the following.

Database

• Database tables were created successfully.
• Bitcoin CSV imported successfully.
• Full absolute CSV path is configured in data_import.sql.
• Source data was inspected.
• Join keys are valid.
• Duplicate observations were checked.
• OHLC relationships were checked.
• Timestamp validity was checked.
• SQL quality checks passed.

Data

• Required columns exist.
• Numerical types are correct.
• Prices are valid.
• Volumes are valid.
• Timestamps are ordered chronologically.
• Missing values are understood.

Mathematics

• Simple returns match:

[ \frac{P_t}{P_{t-1}}-1 ]

• Log returns match:

[ \ln\left(\frac{P_t}{P_{t-1}}\right) ]

• Simple returns compound multiplicatively.
• Cumulative log returns are interpreted correctly.
• Drawdowns are calculated from running peaks.
• Maximum drawdown equals the minimum drawdown.
• Rolling volatility uses the intended window.
• Bitcoin volatility uses (\sqrt{365}) for annualization.

Engineering

• .env is not committed.
• Database credentials are externalized.
• Python modules import correctly.
• Tests pass.
• The workflow executes successfully.
• Identical inputs produce consistent outputs.

---

What This Project Is

This repository is:

• A Bitcoin market-data engineering pipeline.
• A MySQL data-validation layer.
• A vectorized quantitative analytics engine.
• A return and risk-analysis framework.
• A visualization and diagnostic system.
• A foundation for future quantitative research.

---

What This Project Is Not

This repository is not yet:

• A profitable trading strategy.
• A live trading system.
• A production execution engine.
• A complete backtesting framework.
• A portfolio optimizer.
• A market-making system.
• A market-microstructure engine.
• Evidence of predictive alpha.
• Evidence of out-of-sample profitability.

Correct mathematics does not imply predictive power.

A correctly calculated historical volatility series does not predict future volatility by itself.

A correctly calculated historical signal does not constitute alpha.

A fast vectorized implementation does not make an invalid research methodology valid.

The purpose of Phase 1 is to establish the infrastructure required to conduct that research correctly.

---

Limitations

The current system is based on daily OHLCV data.

This means it does not contain complete information about:

• Order-book depth.
• Bid/ask spreads.
• Individual trades.
• Trade direction.
• Queue position.
• Order-flow imbalance.
• Market impact.
• Execution latency.
• Slippage.
• Real transaction costs.

Therefore, this system should not yet be used for serious execution or market-microstructure research.

OHLCV-based research can also suffer from:

• Look-ahead bias.
• Data leakage.
• Overfitting.
• Parameter selection bias.
• Unrealistic execution assumptions.
• Transaction-cost neglect.
• Regime dependence.

These problems must be explicitly addressed before a strategy derived from this infrastructure can be considered statistically credible.

---

Phase 1 Roadmap

Completed

[✓] Bitcoin daily OHLCV ingestion
[✓] MySQL schema construction
[✓] Source-data inspection
[✓] Join-key validation
[✓] OHLCV extraction
[✓] Date filtering
[✓] SQL quality checks
[✓] Python database access
[✓] Chronological validation
[✓] Simple returns
[✓] Log returns
[✓] Cumulative return calculations
[✓] Rolling volatility
[✓] Bitcoin-specific √365 annualization
[✓] Historical drawdown analysis
[✓] Directional features
[✓] Candlestick visualization
[✓] Price-series visualization
[✓] Returns visualization
[✓] Log-return visualization
[✓] Drawdown visualization
[✓] Volatility visualization

Next Research Layers

[ ] Expand automated test coverage
[ ] Benchmark vectorized operations
[ ] Formalize feature specifications
[ ] Transaction-cost model
[ ] Slippage model
[ ] Backtesting engine
[ ] Strategy interface
[ ] Position sizing
[ ] Portfolio construction
[ ] Sharpe ratio
[ ] Sortino ratio
[ ] Calmar ratio
[ ] Walk-forward validation
[ ] Out-of-sample testing
[ ] Regime analysis
[ ] Factor research
[ ] Higher-frequency market data
[ ] Order-book research
[ ] Execution simulation
[ ] Live market-data ingestion

---

Research Pipeline

The intended progression of the project is:

                    RAW DATA
                        │
                        ▼
                DATA VALIDATION
                        │
                        ▼
             MATHEMATICAL VALIDATION
                        │
                        ▼
               IMPLEMENTATION TESTS
                        │
                        ▼
                FEATURE ENGINEERING
                        │
                        ▼
               RESEARCH HYPOTHESIS
                        │
                        ▼
                    BACKTEST
                        │
                        ▼
             OUT-OF-SAMPLE TEST
                        │
                        ▼
             REALISTIC EXECUTION
                        │
                        ▼
                  RISK ANALYSIS
                        │
                        ▼
              PRODUCTION RESEARCH

The order matters.

A strategy should not be trusted because a chart looks convincing.

The data must be correct.

The mathematics must be correct.

The implementation must be tested.

The research methodology must avoid leakage and overfitting.

Only then does performance become meaningful.

---

Design Principle

The central engineering philosophy of the project is:

[ \boxed{ \text{Data Integrity} \rightarrow \text{Mathematical Correctness} \rightarrow \text{Reproducibility} \rightarrow \text{Statistical Validation} \rightarrow \text{Trading Research} } ]

The goal of Phase 1 is to make the first three stages reliable enough that future research is built on a trustworthy foundation.