181 lines
9.1 KiB
Markdown
181 lines
9.1 KiB
Markdown
# 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
|
|
```bash
|
|
# 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
|
|
```bash
|
|
# 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
|
|
```bash
|
|
# Using the import script
|
|
node backend/scripts/importCSV.js path/to/alertstream.csv
|
|
```
|
|
|