deepkoluguri
/
institutional-trader
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
institutional-trader/DATA_FLOW_DOCUMENTATION.md

9.1 KiB
Raw Permalink Blame History

Data Flow into Database Tables

This document explains how data flows into three key database tables: prices_intraday_1m, prices_daily, and AlertStream_monthly.

1. prices_intraday_1m Table

Real-Time Data Source (PostgreSQL)

Service: backend/src/services/bookmapWebSocketService.js

  • Source: Bookmap trading platform add-on
  • Method: WebSocket server (port 3001 by default)
  • Process:
    1. Receives real-time trade data from Bookmap add-on via WebSocket
    2. Aggregates trades into 1-minute OHLCV bars in memory
    3. Flushes completed bars (at least 1 minute old) to PostgreSQL every 30 seconds
    4. Uses INSERT ... ON CONFLICT to handle duplicates
  • Activation: Enabled when ENABLE_BOOKMAP !== 'false' in environment
  • Direct Insert: Writes directly to PostgreSQL prices_intraday_1m table

Historical/Batch Data Source (SQLite)

Script: yahhooscript.py

  • Source: Yahoo Finance via yfinance Python library
  • Method: Batch downloads with configurable intervals
  • Process:
    1. Fetches 1-minute intraday data for configured symbol universe (S&P 500, S&P 400, Nasdaq-100, plus custom groups)
    2. Processes data in batches (default: 110 symbols per batch)
    3. Writes to SQLite database at C:\Users\srk47\Desktop\options_flow.db
    4. Uses INSERT OR REPLACE for upserts
  • Note: This writes to SQLite, not directly to PostgreSQL. A separate sync mechanism would be needed to transfer to PostgreSQL.

2. prices_daily Table

Script: yahhooscript.py

  • Source: Yahoo Finance via yfinance Python library
  • Method: Incremental daily updates
  • Process:
    1. For each symbol, checks existing data count
    2. If < 10 records exist, performs full download (default: 2 years)
    3. Otherwise, performs incremental update from last date minus 5 days
    4. Writes to SQLite prices_daily table
    5. Uses INSERT ... ON CONFLICT for upserts
  • Scheduling: Runs in loop mode with configurable interval (DAILY_INTERVAL_MIN)
  • Note: This writes to SQLite, not directly to PostgreSQL. A separate sync mechanism would be needed to transfer to PostgreSQL.

3. AlertStream_monthly Table

CSV Import Method

Script: backend/scripts/importCSV.js

  • Source: CSV files
  • Method: Manual/scripted CSV import
  • Process:
    1. Parses CSV file with AlertStream data
    2. Maps columns to both CamelCase and lowercase versions for compatibility
    3. Batch inserts (100 records per batch) into PostgreSQL AlertStream_monthly table
    4. Handles duplicate columns (Date/date, Ticker/ticker, etc.)

BlackBox API Method

Script: watch_and_upload_blackbox_postgres.py

  • Source: BlackBox Stocks API (https://api.blackboxstocks.com/api/v2/options/getFlowMobile)
  • Method: API fetch and sync
  • Process:
    1. Fetches alert/flow data from BlackBox API (requires BLACKBOX_API_TOKEN)
    2. Maps API response to database schema (snake_case for AlertStream_monthly)
    3. Normalizes and prepares data for PostgreSQL
    4. Uses chunked upserts (default: 3000 records per chunk) with row_hash for deduplication
    5. Writes directly to PostgreSQL AlertStream_monthly table
  • Usage: Run with --table AlertStream_monthly parameter
  • Default: Script defaults to OptionsFlow_monthly table, but can target AlertStream_monthly

Data Flow Diagram

┌─────────────────────────────────────────────────────────────┐
│                    prices_intraday_1m                        │
├─────────────────────────────────────────────────────────────┤
│                                                               │
│  Real-Time Path:                                             │
│  Bookmap Add-on → WebSocket → bookmapWebSocketService.js    │
│                    → PostgreSQL (direct insert)              │
│                                                               │
│  Historical Path:                                            │
│  Yahoo Finance → yahhooscript.py → SQLite                    │
│  (Note: Requires separate sync to PostgreSQL)                │
│                                                               │
└─────────────────────────────────────────────────────────────┘

┌─────────────────────────────────────────────────────────────┐
│                      prices_daily                            │
├─────────────────────────────────────────────────────────────┤
│                                                               │
│  Yahoo Finance → yahhooscript.py → SQLite                     │
│  (Note: Requires separate sync to PostgreSQL)                │
│                                                               │
└─────────────────────────────────────────────────────────────┘

┌─────────────────────────────────────────────────────────────┐
│                  AlertStream_monthly                         │
├─────────────────────────────────────────────────────────────┤
│                                                               │
│  Method 1: CSV Import                                        │
│  CSV File → importCSV.js → PostgreSQL                        │
│                                                               │
│  Method 2: API Sync                                          │
│  BlackBox API → watch_and_upload_blackbox_postgres.py       │
│                → PostgreSQL                                  │
│                                                               │
└─────────────────────────────────────────────────────────────┘

Key Files

  • Real-time intraday prices: backend/src/services/bookmapWebSocketService.js (lines 146-213)
  • Historical price data: yahhooscript.py (lines 317-454)
  • CSV import: backend/scripts/importCSV.js (lines 371-526)
  • API sync: watch_and_upload_blackbox_postgres.py (lines 637-733)

Important Notes

  1. SQLite vs PostgreSQL: The yahhooscript.py script writes to SQLite, not directly to PostgreSQL. If you need this data in PostgreSQL, you'll need a separate sync mechanism.

  2. Bookmap Service: The Bookmap WebSocket service writes directly to PostgreSQL and is the primary real-time source for prices_intraday_1m.

  3. AlertStream Sources: AlertStream_monthly can be populated from either CSV files or the BlackBox API, depending on your data source preference.

Configuration

Bookmap WebSocket Service

  • Environment Variable: ENABLE_BOOKMAP (default: enabled if not set to 'false')
  • Port: BOOKMAP_WS_PORT (default: 3001)
  • Flush Interval: Every 30 seconds (hardcoded in bookmapWebSocketService.js)

Yahoo Finance Script

  • Database Path: DB_PATH in yahhooscript.py (default: C:\Users\srk47\Desktop\options_flow.db)
  • Symbol Universe: Configured via SEGMENTS, EXTRA_SYMBOLS, and EXTRA_GROUPS in yahhooscript.py
  • Batch Size: BATCH_SIZE (default: 110)
  • Loop Mode: LOOP (default: True)

BlackBox API Sync

  • Environment Variable: BLACKBOX_API_TOKEN (required)
  • PostgreSQL Connection: Configured via environment variables:
    • POSTGRES_HOST (default: localhost)
    • POSTGRES_PORT (default: 5432)
    • POSTGRES_DB (default: institutional_trader)
    • POSTGRES_USER (default: postgres)
    • POSTGRES_PASSWORD (default: postgres)
  • Upsert Chunk Size: UPSERT_CHUNK (default: 3000)

Usage Examples

Running Yahoo Finance Script

# One-time run (no loop)
python yahhooscript.py

# Single symbol daily data
python yahhooscript.py --only AAPL --daily

# Single symbol intraday data
python yahhooscript.py --only AAPL --intraday

Running BlackBox API Sync for AlertStream

# Sync AlertStream for today
python watch_and_upload_blackbox_postgres.py --table AlertStream_monthly

# Sync for specific date range
python watch_and_upload_blackbox_postgres.py --table AlertStream_monthly --start-date 2024-01-01 --end-date 2024-01-31

Importing CSV for AlertStream

# Using the import script
node backend/scripts/importCSV.js path/to/alertstream.csv