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