pip install duckdb akshare pandas

import akshare as ak
import duckdb
import pandas as pd
from datetime import datetime, timedelta

# Connect to DuckDB (creates a local file database)
con = duckdb.connect("sector_monitor.db")

# Get Shenwan tier-1 industry sector list
sector_df = ak.stock_board_industry_name_em()
print(f"Monitoring {len(sector_df)} industry sectors")

# Get data for the last 90 calendar days (~60 trading days)
end_date = datetime.now().strftime("%Y%m%d")
start_date = (datetime.now() - timedelta(days=90)).strftime("%Y%m%d")

# Create the main table
con.execute("""
    CREATE TABLE IF NOT EXISTS sector_daily (
        date DATE,
        sector VARCHAR,
        open DOUBLE,
        close DOUBLE,
        high DOUBLE,
        low DOUBLE,
        volume BIGINT,
        amount BIGINT,
        amplitude DOUBLE,
        change_pct DOUBLE,
        change_amount DOUBLE,
        turnover_rate DOUBLE
    )
""")

# Test with first 5 sectors
for _, row in sector_df.head(5).iterrows():
    sector_name = row["板块名称"]
    try:
        df = ak.stock_board_industry_hist_em(
            symbol=sector_name,
            start_date=start_date,
            end_date=end_date,
            period="daily",
            adjust="qfq"
        )
        if df.empty:
            continue
        df["sector"] = sector_name
        
        # Register DataFrame as temp table and write to DuckDB
        con.register("df_tmp", df)
        con.execute("""
            INSERT INTO sector_daily 
            SELECT date, sector, open, close, high, low, 
                   volume, amount, amplitude, change_pct, 
                   change_amount, turnover_rate
            FROM df_tmp
        """)
        print(f"  ✓ {sector_name}: {len(df)} records")
    except Exception as e:
        print(f"  ✗ {sector_name}: {e}")

-- Create momentum view
CREATE OR REPLACE VIEW sector_momentum AS
WITH daily_return AS (
    SELECT
        sector,
        date,
        (close - LAG(close) OVER (PARTITION BY sector ORDER BY date)) 
            / NULLIF(LAG(close) OVER (PARTITION BY sector ORDER BY date), 0) AS daily_ret
    FROM sector_daily
),
momentum AS (
    SELECT
        sector,
        MAX(date) AS latest_date,
        -- 5-day cumulative return (geometric compounding)
        EXP(SUM(LN(1 + COALESCE(daily_ret, 0))) 
            OVER (PARTITION BY sector ORDER BY date 
                  ROWS BETWEEN 4 PRECEDING AND CURRENT ROW)) - 1 AS ret_5d,
        -- 20-day cumulative return
        EXP(SUM(LN(1 + COALESCE(daily_ret, 0))) 
            OVER (PARTITION BY sector ORDER BY date 
                  ROWS BETWEEN 19 PRECEDING AND CURRENT ROW)) - 1 AS ret_20d,
        -- 60-day cumulative return
        EXP(SUM(LN(1 + COALESCE(daily_ret, 0))) 
            OVER (PARTITION BY sector ORDER BY date 
                  ROWS BETWEEN 59 PRECEDING AND CURRENT ROW)) - 1 AS ret_60d,
        -- 20-day average trading amount (capital activity gauge)
        AVG(amount) OVER (PARTITION BY sector ORDER BY date 
                          ROWS BETWEEN 19 PRECEDING AND CURRENT ROW) AS avg_amount_20d
    FROM daily_return
)
SELECT DISTINCT
    sector,
    ret_5d,
    ret_20d,
    ret_60d,
    avg_amount_20d,
    -- Composite momentum score: short-term trend weighted highest
    ret_5d * 0.5 + ret_20d * 0.3 + ret_60d * 0.2 AS momentum_score
FROM momentum
WHERE date = (SELECT MAX(date) FROM daily_return)
ORDER BY momentum_score DESC;

-- Ranking and signal generation
CREATE OR REPLACE VIEW sector_signals AS
WITH ranked AS (
    SELECT *,
        ROW_NUMBER() OVER (ORDER BY momentum_score DESC) AS rank_asc,
        ROW_NUMBER() OVER (ORDER BY momentum_score ASC) AS rank_desc
    FROM sector_momentum
)
SELECT
    sector,
    ROUND(ret_5d * 100, 2) AS ret_5d_pct,
    ROUND(ret_20d * 100, 2) AS ret_20d_pct,
    ROUND(momentum_score * 100, 2) AS score,
    ROUND(avg_amount_20d / 1e8, 1) AS avg_amount_hundred_million,
    CASE 
        WHEN rank_asc <= 5 THEN '🔥 Strong Momentum'
        WHEN rank_asc <= 15 THEN '⚡ Moderate Momentum'
        WHEN rank_desc <= 5 THEN '❄️ Avoid / Weak'
        ELSE '➡️ Neutral'
    END AS signal,
    CASE
        WHEN rank_asc <= 5 AND avg_amount_20d > 1e10 THEN 'BUY'
        WHEN rank_desc <= 5 THEN 'SELL / AVOID'
        ELSE 'HOLD / WATCH'
    END AS action
FROM ranked
ORDER BY rank_asc;

# Generate report text directly from DuckDB
report = con.execute("""
SELECT 
    strftime(current_date, '%Y-%m-%d') || ' A-Share Sector Rotation Daily' AS title,
    '---' AS sep1,
    '🔥 Top 5 Strong Sectors:' AS section1,
    string_agg(
        '  ' || sector || ' | 5d: ' || ret_5d_pct || '% | Score: ' || score || ' | Signal: ' || action,
        chr(10)
    ) FILTER (WHERE rank_asc <= 5) AS top_sectors,
    '---' AS sep2,
    '❄️ Bottom 5 Weak Sectors:' AS section2,
    string_agg(
        '  ' || sector || ' | 20d: ' || ret_20d_pct || '% | Signal: ' || action,
        chr(10)
    ) FILTER (WHERE rank_desc <= 5) AS bottom_sectors,
    '---' AS sep3,
    '💡 Trading Advice:' AS section3,
    CASE
        WHEN count(*) FILTER (WHERE action = 'BUY') >= 3 
        THEN 'Market sentiment is optimistic. Watch for pullback entries in strong sectors.'
        WHEN count(*) FILTER (WHERE action = 'BUY') = 0 
        THEN 'No clear buy signals. Consider waiting or monitoring defensive sectors.'
        ELSE 'Structural market. Focus on sectors with sustained momentum.'
    END AS advice
FROM sector_signals
""").fetchone()

report_text = '\n'.join([str(r) for r in report if r])
print(report_text)

def send_telegram(bot_token, chat_id, text):
    import requests
    url = f"https://api.telegram.org/bot{bot_token}/sendMessage"
    requests.post(url, json={"chat_id": chat_id, "text": text})

#!/bin/bash
cd /path/to/project
python3 collect_data.py     # Data collection
python3 compute_signals.py  # Indicator computation
python3 send_report.py      # Push delivery

CREATE OR REPLACE VIEW multi_factor_score AS
SELECT 
    m.sector,
    m.momentum_score * 0.3 +      -- Momentum factor
    v.volume_change * 0.2 +       -- Volume change factor
    p.price_stability * 0.2 +     -- Price stability factor
    r.relative_strength * 0.3     -- Relative strength factor
    AS composite_score
FROM sector_momentum m
JOIN sector_volume v USING (sector)
JOIN sector_stability p USING (sector)
JOIN sector_rel_strength r USING (sector);

-- Backtest: weekly rebalance, buy top 3 momentum sectors
WITH weekly_rank AS (
    SELECT 
        date,
        sector,
        momentum_score,
        ROW_NUMBER() OVER (PARTITION BY date ORDER BY momentum_score DESC) AS rnk
    FROM sector_daily_momentum
    WHERE dayofweek(date) = 5  -- Every Friday rebalance
)
SELECT 
    sector,
    COUNT(*) AS hold_weeks,
    AVG(ret_20d) AS avg_return,
    STDDEV(ret_20d) AS volatility,
    AVG(ret_20d) / NULLIF(STDDEV(ret_20d), 0) AS sharpe_ratio
FROM weekly_rank
WHERE rnk <= 3
GROUP BY sector
ORDER BY sharpe_ratio DESC;