# Candle Data Loaders

## Project Overview
Two applications for loading candlestick (OHLCV) data into a shared MySQL database:

1. **moex.py** — Moscow Exchange (MOEX) ISS API (W1/H1/D1, ~1667 lines)
2. **crypto.py** — Binance public API (W1/H1/D1, ~862 lines)

Both share the same DB schema `{TICKER}_{TF}` and instrument registry `instruments` table.

## Project Structure
```
moex2/
  moex.py          - MOEX data loader
  crypto.py        - Crypto data loader (Binance)
  .opencode/       - OpenCode agent configuration
  opencode.json    - OpenCode project config
```

## Running
```bash
pip install -r requirements.txt
python moex.py TICKER [--username USER] [--password PASS] [--debug]
python crypto.py TICKER [--debug]
```

## Architecture
- **Single-file design**: All logic in `moex.py`
- **MoexClient class**: Handles MOEX ISS API with pagination, retries, and optional authentication
- **Scheduler loop**: `run_scheduler()` polls at :15 past each period boundary
- **MySQL storage**: Tables named `{TICKER}_{TIMEFRAME}` (e.g., `SBER_W1`, `SBER_H1`, `SBER_D1`)
- **Instruments registry**: `instruments` table tracks all active tickers

## Key Constants
- `TIMEFRAMES`: W1 (interval=7), H1 (interval=60), D1 (interval=24)
- `MAX_RECORDS`: 40000 candles for initial load
- `MOEX_PUBLISH_DELAY_MINUTES`: 15 — задержка публикации данных на MOEX
- `REQUEST_RETRIES`: 10 with 30s delay between retries
- `PAGE_DELAY`: 5s pause every 5 pages
- `H1_RECONCILE_RETRY_DELAY`: 120 — retry reconcile каждые 2 мин для H1
- `H1_RECONCILE_MAX_RETRIES`: 15 — до 30 минут ретриев (покрывает задержку MOEX)

## Tbank + MOEX: двухэтапная синхронизация
При наличии `TBANK_TOKEN`:

| Этап | Время | Источник | Действие |
|------|-------|----------|----------|
| 1. Tbank | :03 | Tbank API | Запись в БД с фильтрацией спайков |
| 2. MOEX reconcile | :15 + retry 2 мин | MOEX ISS | Валидация + заполнение пропусков |

### Этап 1: Tbank (:03)
- Записывает свечу в БД **только если она не аномальная** (проходит spike filter)
- Если свеча — спайк → **не записывается**, остаётся пропуск в БД
- Лог успеха: `[TBANK] SBER_H1: записана свеча 2026-07-20 15:00 O=251.36 H=251.47 ...`
- Лог спайка: `[TBANK] SBER_H1: свечи [...] отфильтрованы как спайки — пропуск, будет заполнен MOEX`

### Этап 2: MOEX reconcile (:15 + retry 120с × 15)
- **Валидация**: сравнивает свечи из БД с MOEX, перезаписывает расхождения (`ON DUPLICATE KEY UPDATE`)
- **Заполнение пропусков**: находит MOEX-свечи, которых нет в БД (пропущены Tbank как спайки) → вставляет их через `insert_candles_raw()` (без фильтрации спайков — MOEX-данные уже подтверждены)

### Логирование reconcile
```
⚠️ Свеча 2026-07-20 15:00: O 251.3600→251.4700 | V 3573468→3570000
➕ Свеча 2026-07-20 15:00: отсутствует в БД (Tbank пропустил как спайк), добавляем из MOEX
Сверка SBER_H1: ИСПРАВЛЕНО 1 расхождений
Сверка SBER_H1: ДОБАВЛЕНО 1 пропущенных свечей (Tbank спайки)
```

### Преимущества
- **Аномалии не попадают в БД**: Tbank фильтрует, MOEX не перезаписывает спайки (их просто нет)
- **MOEX заполняет пропуски**: если свеча была аномальной в Tbank, MOEX вставит корректную версию
- **Нет race condition**: Tbank пишет первым (с фильтром), MOEX позже валидирует/дополняет

## Database Schema
### instruments table
- `id` (INT, PK, AUTO_INCREMENT)
- `name` (VARCHAR(50), UNIQUE with type)
- `type` (VARCHAR(20))

### Candle tables ({TICKER}_{TF})
- `timestamp` (BIGINT, PK) - Unix timestamp of candle begin
- `Date` (VARCHAR(10)) - Format: YYYY.MM.DD
- `Time` (VARCHAR(5)) - Format: HH:MM
- `Open`, `High`, `Low`, `Close` (DECIMAL(20,8))
- `Volume` (BIGINT)

## MOEX API
- Base URL: `https://iss.moex.com/iss/engines/stock/markets/shares/boards/TQBR/securities`
- Endpoint: `/{ticker}/candles.json`
- Params: `interval`, `start`, `limit`, `from`, `till`
- Auth: Optional basic auth via `passport.moex.com/authenticate`
- Rate limiting: Built-in page delays and retry logic

## Conventions
- Russian-language comments and log messages
- Timezone: Europe/Moscow (pytz)
- All datetimes are naive (no tzinfo) after stripping from MOEX response
- Candle `begin` times are aligned to period boundaries via `align_begin()`
- Only completed candles (past publish delay) are stored

## Environment Variables
- `MOEX_USERNAME` - MOEX account username (optional, for authenticated access)
- `MOEX_PASSWORD` - MOEX account password (optional)

## Important Notes
- DB credentials are hardcoded in `DB_CONFIG` - consider moving to env vars
- The app runs as a long-lived daemon with an infinite scheduler loop
- 5-min candles are synthesized from 1-min data via `aggregate_1min_to_5min()`
- UPSERT logic: `ON DUPLICATE KEY UPDATE` ensures idempotent inserts

## Commands (MOEX)
- Run for single ticker: `python moex.py SBER`
- Run with debug logging: `python moex.py SBER --debug`
- Run for all registered tickers: `python moex.py`
- Install dependencies: `pip install -r requirements.txt`

---

# Crypto Data Loader (crypto.py)

## Overview
Loads candlestick data from Binance public API for cryptocurrency instruments.
Same DB schema and instrument registry as MOEX.

## Ticker Mapping
- `BITCOIN` → BTCUSDT (Bitcoin/USDT)
- `BITCOINC` → BCHUSDT (Bitcoin Cash/USDT)

## Architecture
Same as moex.py:
- BinanceClient class: REST API with rate limiting and retries
- Scheduler loop: `run_scheduler()` polls per-TF schedule
- Spike detection: 5× thresholds (wider than MOEX's 3×)
- Initial load: 10000 H1 candles, 417 D1, 60 W1

## Schedule (UTC)
- H1: every hour at :05
- D1: daily at 01:00 UTC
- W1: Monday at 02:00 UTC

## Commands
- Run for single ticker: `python crypto.py BITCOIN`
- Run for all crypto tickers: `python crypto.py`
- Debug mode: `python crypto.py --debug`

## Key Differences from moex.py
| Aspect | moex.py | crypto.py |
|--------|---------|-----------|
| Data source | MOEX ISS API | Binance public API |
| Auth | Optional MOEX login | None (public) |
| Timezone | MSK (Europe/Moscow) | UTC |
| Sync offset | :15 after period | :05 after period |
| Spike threshold | 3× | 5× |
| Initial H1 load | 40000 candles | 10000 candles |
| Tbank support | Yes | No |

## Custom Agents

The project includes specialized subagents for specific tasks. Invoke them via `@agent-name` in messages.

### Available Agents

#### @db-review
**Purpose**: Review database operations, SQL queries, and table schema.

**Use when**:
- Need to verify SQL query correctness (especially UPSERT in `insert_candles()`)
- Analyzing table schema or indexes
- Checking connection and transaction handling
- Optimizing database performance
- Reviewing functions: `get_db_connection()`, `ensure_instruments_table()`, `create_table_if_not_exists()`, `insert_candles()`, `get_last_timestamp()`, `get_candles_count_since()`

**Example**: `@db-review Check UPSERT logic in insert_candles() for race conditions`

---

#### @moex-debug
**Purpose**: Debug MOEX ISS API interactions, pagination, and data parsing.

**Use when**:
- Issues with fetching data from MOEX API
- Pagination errors or response parsing issues
- Questions about authentication and cookies
- Problems with timeouts or retry logic
- Understanding MOEX response format (`candles.columns` + `candles.data`)
- Questions about `align_begin()`, `filter_completed_candles()`, timezone handling

**Example**: `@moex-debug Why aren't candles loading for the last week? Check pagination logic`

---

#### @scheduler-review
**Purpose**: Review scheduler logic, sync timing, and candle lifecycle.

**Use when**:
- Issues with sync schedule (`:15` after period close)
- Questions about `get_next_sync_time()` for H1/D1/W1
- Analyzing `run_scheduler()` and `check_new_candles_for_timeframe()`
- Problems with `is_candle_completed()` and publish delay (15 min)
- Reviewing retry logic for failed attempts (`MAX_FAIL_ATTEMPTS = 3`)
- Questions about initial load vs incremental update in `sync_timeframe()`

**Example**: `@scheduler-review Check if next sync time for W1 is calculated correctly`

---

#### @tbank-api
**Purpose**: Design Tbank (T-Bank) API integration for future implementation.

**Use when**:
- Planning project expansion for Tbank Invest API
- Need MOEX ↔ Tbank ticker mapping (FIGI)
- Designing `TbankClient` by analogy with `MoexClient`
- Analyzing Tbank API limitations and caching strategies
- Planning fallback logic (MOEX → Tbank on unavailability)

**Example**: `@tbank-api How to best organize ticker mapping between MOEX and Tbank API?`

---

#### @python-dev
**Purpose**: Write professional Python code for the project.

**Use when**:
- Need to write new functions or classes for `moex.py`
- Refactoring existing code with quality improvements
- Adding type hints and docstrings
- Implementing new functionality (new timeframes, APIs, features)
- Optimizing performance or code readability
- Fixing bugs following best practices

**Features**:
- Follows PEP 8, uses type hints and Google-style docstrings
- Writes comments and log messages in Russian
- Compatible with existing architecture (single-file, MySQL, MOEX API)
- Can edit files (unlike review agents)

**Example**: `@python-dev Write a function to export candles to CSV with proper error handling`

---

### Usage Rules

1. **Operating modes**: Most agents are read-only (analysis and recommendations). The `@python-dev` agent can edit files.

2. **Automatic invocation**: Primary agents (build, plan) can automatically invoke subagents via the Task tool if the task matches their specialization.

3. **Manual invocation**: You can explicitly invoke an agent via `@agent-name` in your message.

4. **Parallel work**: You can run multiple subagents in parallel for different aspects of a task.

5. **Context**: Subagents receive project context from AGENTS.md and have access to all files for reading.

6. **Results**: Subagents return analysis and recommendations (or code changes from @python-dev) back to the main session.

## Skills

Agents can load specialized skills to get additional information. Skills are loaded on demand.

### Available Skills

| Skill | Description | Agents |
|-------|-------------|--------|
| `moex-api-reference` | MOEX ISS API documentation: endpoints, parameters, pagination | @moex-debug, @python-dev |
| `db-schema-reference` | DB schema: tables, SQL patterns, UPSERT | @db-review, @python-dev |
| `scheduler-patterns` | Scheduler: timing, sync logic, retry | @scheduler-review, @python-dev |
| `python-code-standards` | Code standards: PEP 8, type hints, docstrings | @python-dev |
| `tbank-api-reference` | Tbank API: REST/gRPC, FIGI, integration | @tbank-api, @python-dev |
| `error-handling` | Error handling: retry, transactions, logs | All agents |

### Using Skills

Agents automatically load the skills they need during work. You can also explicitly request skill usage:

```
@python-dev Use the db-schema-reference skill to verify the schema
```

Skills contain detailed documentation, code examples, and patterns that help agents provide more accurate recommendations.