ljwx-stock/HISTORICAL_DATA_LOADER_GUIDE.md

Historical Data Loader Guide

📋 Overview

Complete historical data loading system for:

• A-Shares: ~5000 stocks, 1 year history
• US Stocks: Selected 100+ stocks, 1 year history
• HK Stocks: Selected 50+ stocks, 1 year history
• Forex: 10+ currency pairs, 1 year history
• Commodities: Gold, Silver, etc., 1 year history

Total Data Volume: ~1.5 million records Estimated Load Time: 3-4 hours (with API rate limiting)

🗂️ Files Created

Database Schema

• backend/database/schema_v2.sql - Complete database schema with 7 tables + 3 views

ORM Models

• backend/app/models/stock_models.py - SQLAlchemy ORM models

Data Loader Service

• backend/services/historical_data_loader.py - Main historical data loader
• backend/scripts/init_database_v2.py - Database initialization script
• backend/scripts/load_historical_data.sh - Shell script wrapper
• backend/scripts/test_data_loader.py - Test script for small batch

🗄️ Database Tables

Core Tables

1. stocks - Stock master data

   symbol, name, market, industry, list_date
   

2. daily_quotes - Daily OHLCV data

   symbol, trade_date, open, high, low, close, volume, amount
   ~1.5M records for 1 year across all markets
   

3. technical_indicators - Pre-calculated indicators

   symbol, trade_date, ma5, ma10, ma20, rsi, macd, bollinger_bands, atr
   

4. fundamentals - Financial metrics

   symbol, report_date, pe_ratio, pb_ratio, roe, net_margin, debt_to_equity
   

5. recommendations - Strategy-based recommendations

   symbol, recommend_date, strategy, score, predicted_return, confidence, reason
   

6. strategy_configs - Strategy configuration

   strategy_name, filters, weights, enabled
   Pre-loaded: value, growth, momentum, comprehensive
   

7. data_load_tasks - Track loading progress

   task_name, market, status, total_records, loaded_records, error_message
   

Views

1. latest_recommendations - Latest recommendations with stock info and prices
2. stock_technical_summary - Technical indicators summary per stock
3. stock_fundamental_summary - Fundamental metrics summary per stock

🚀 Quick Start

Step 1: Initialize Database

# Already completed ✅
# Database 'stockai' created
# Schema_v2.sql executed
# 7 tables + 3 views created
# 4 default strategies inserted

Verify:

docker-compose exec -T postgres psql -U stockai -d stockai -c "\dt"
# Should show: stocks, daily_quotes, technical_indicators, fundamentals, recommendations, strategy_configs, data_load_tasks

docker-compose exec -T postgres psql -U stockai -d stockai -c "SELECT * FROM strategy_configs;"
# Should show: value, growth, momentum, comprehensive

Step 2: Load Historical Data

Option A: Rebuild Docker image (Recommended for production)

# Rebuild backend image to include new files
docker-compose build backend
docker-compose up -d backend

# Run data loader
docker-compose exec backend python services/historical_data_loader.py --market a_share --years 1

Option B: Run locally (Faster for development)

# Install dependencies
cd backend
source .venv/bin/activate  # or create venv if not exists
pip install -r requirements.txt

# Run loader
python services/historical_data_loader.py --market a_share --years 1

Step 3: Monitor Progress

# Check loading tasks
docker-compose exec -T postgres psql -U stockai -d stockai -c "SELECT * FROM data_load_tasks ORDER BY created_at DESC LIMIT 5;"

# Check loaded stocks
docker-compose exec -T postgres psql -U stockai -d stockai -c "SELECT COUNT(*) FROM stocks;"
docker-compose exec -T postgres psql -U stockai -d stockai -c "SELECT COUNT(*) FROM daily_quotes;"

# Check latest data
docker-compose exec -T postgres psql -U stockai -d stockai -c "SELECT * FROM stock_technical_summary LIMIT 10;"

⚙️ Configuration

API Keys (Already configured in .env)

• TUSHARE_TOKEN=e8030804de076b48b445276b3c789cc5922afc6b078ecb8382df9df6
• ALPHA_VANTAGE_API_KEY=FFJVQK6LCQ5RSPNH

Rate Limiting

• TuShare: 500 calls/min → 0.5s delay between calls
• yfinance: ~2000 calls/hour → 1s delay between calls
• Alpha Vantage: 25 calls/day → 12s delay between calls

Batch Processing

• Batch size: 50 stocks per batch
• Total batches for 5000 A-shares: 100 batches
• Estimated time: ~50 mins for 5000 stocks (0.5s delay × 5000)

📊 Data Pipeline Flow

1. Fetch stock list (TuShare: stock_basic API)
   ↓
2. For each stock:
   ├─ Save to `stocks` table (symbol, name, industry, market)
   ├─ Fetch 1-year daily quotes (TuShare: daily API)
   ├─ Save to `daily_quotes` table (OHLCV data)
   ├─ Calculate technical indicators (feature_engineer)
   ├─ Save to `technical_indicators` table (MA, RSI, MACD, etc.)
   ├─ Fetch fundamentals (TuShare: daily_basic API)
   └─ Save to `fundamentals` table (PE, PB, ROE, etc.)
   ↓
3. Update `data_load_tasks` table (progress tracking)

🔍 Testing

Test with Small Batch

# Test with 3 stocks only
docker-compose exec backend python scripts/test_data_loader.py

# Expected output:
# ✅ Found 5000 total stocks
# ✅ Fetched 30 days of data
# ✅ Stock info saved
# ✅ 30 quotes saved
# ✅ Technical indicators calculated

Verify Data Quality

# Check data completeness
docker-compose exec -T postgres psql -U stockai -d stockai <<EOF
SELECT
    s.symbol,
    s.name,
    COUNT(dq.id) as quote_days,
    COUNT(ti.id) as indicator_days,
    MAX(dq.trade_date) as latest_date
FROM stocks s
LEFT JOIN daily_quotes dq ON s.symbol = dq.symbol
LEFT JOIN technical_indicators ti ON s.symbol = ti.symbol
GROUP BY s.symbol, s.name
ORDER BY latest_date DESC
LIMIT 10;
EOF

⏰ Load Time Estimates

A-Shares (5000 stocks)

• Data fetching: ~50 minutes (0.5s × 5000)
• Technical indicators: ~20 minutes (calculated in Python)
• Fundamentals: ~10 minutes (TuShare API calls)
• Total: ~80 minutes

US Stocks (100 stocks)

• yfinance batch loading: ~5 minutes
• Total: ~10 minutes

HK Stocks (50 stocks)

• yfinance batch loading: ~3 minutes
• Total: ~5 minutes

Forex & Commodities (15 assets)

• Alpha Vantage: ~3 minutes (12s delay)
• Total: ~5 minutes

Grand Total: ~100 minutes (~1.5 hours)

🔧 Troubleshooting

Issue: "Database stockai does not exist"

docker-compose exec -T postgres psql -U stockai -d postgres -c "CREATE DATABASE stockai;"
docker-compose exec -T postgres psql -U stockai -d stockai < backend/database/schema_v2.sql

Issue: "Module not found: historical_data_loader"

# Rebuild Docker image
docker-compose build backend
docker-compose restart backend

Issue: "TuShare API rate limit exceeded"

# In historical_data_loader.py, increase delay:
self.api_delay = 1.0  # Increase from 0.5 to 1.0 seconds

Issue: "Out of memory during load"

# Reduce batch size:
self.batch_size = 20  # Reduce from 50 to 20

📈 Next Steps

After data is loaded:

1. Verify Data

   docker-compose exec -T postgres psql -U stockai -d stockai -c "SELECT COUNT(*) FROM daily_quotes;"
   # Expected: ~1,500,000 records
   

2. Generate Recommendations (see Strategy Engine docs)

   # Strategy engine will read from database
   # Generate daily recommendations based on configured strategies
   

3. Update Dashboard API (see API Integration docs)

   # Recommendations API will query database views
   # Return real data instead of mock data
   

📞 Status

Database Schema: ✅ Completed (7 tables, 3 views, 4 strategies) ORM Models: ✅ Completed Data Loader: ✅ Completed (A-shares implemented) Testing: ⏸️ Pending (requires Docker rebuild) Full Load: ⏸️ Pending (estimated 1.5 hours)

Ready to proceed with:

• Strategy Engine implementation
• Recommendations API update
• Dashboard integration