182 lines
7.9 KiB
Python
182 lines
7.9 KiB
Python
#!/usr/bin/env python3
|
||
# -*- coding: utf-8 -*-
|
||
"""build_panel_hk_v2.py — 港股 12维面板 v2（补行业动量+估值分位）
|
||
|
||
在 v1（大盘+个股技术）基础上补：
|
||
- sec_ret20/sec_above：港股行业动量（行业归属 stock_sectors + 日K等权均值）
|
||
- pe_q/pb_q/mcap_q：估值分位（stock_fundamentals_history，每日截面分位）
|
||
输出：/tmp/panel_12d_hk.pkl（覆盖 v1）
|
||
"""
|
||
import sys, sqlite3
|
||
import numpy as np
|
||
import pandas as pd
|
||
|
||
# ── 消息通道统一路由(broadcast/xmpp by delivery) ──
|
||
try:
|
||
from messenger import install_stdio_hook as _msh
|
||
_msh()
|
||
except Exception:
|
||
pass
|
||
sys.path.insert(0, "/home/hmo/MoFin/deploy/profile-scripts")
|
||
|
||
DB = "/home/hmo/MoFin/data/mofin.db"
|
||
|
||
def calc_rsi(closes, n=14):
|
||
if len(closes) < n + 1:
|
||
return [None] * len(closes)
|
||
out = [None] * len(closes)
|
||
for i in range(n, len(closes)):
|
||
gains, losses = [], []
|
||
for j in range(i - n + 1, i + 1):
|
||
ch = closes[j] - closes[j - 1]
|
||
gains.append(max(ch, 0)); losses.append(max(-ch, 0))
|
||
ag, al = sum(gains) / n, sum(losses) / n
|
||
out[i] = 100 if al == 0 else 100 - 100 / (1 + ag / al)
|
||
return out
|
||
|
||
def main():
|
||
conn = sqlite3.connect(DB)
|
||
hsi = pd.read_sql("SELECT date, close FROM stock_daily WHERE code='hkHSI' ORDER BY date", conn)
|
||
codes = [r[0] for r in conn.execute(
|
||
"SELECT code FROM hk_connect_stocks WHERE is_active=1 ORDER BY code").fetchall()]
|
||
sector = dict(conn.execute(
|
||
"SELECT code, sector_name FROM stock_sectors WHERE source='hk_em'").fetchall())
|
||
conn.close()
|
||
|
||
# 大盘指标（同v1）
|
||
hsi = hsi.sort_values("date").reset_index(drop=True)
|
||
hsi["mkt_ret20"] = hsi["close"].pct_change(20) * 100
|
||
ma20 = hsi["close"].rolling(20).mean()
|
||
hsi["mkt_above"] = (hsi["close"] > ma20).astype(float)
|
||
hsi["mkt_rsi"] = calc_rsi(list(hsi["close"].values))
|
||
hsi["mkt_adx"] = hsi["close"].rolling(14).apply(
|
||
lambda x: abs(x.iloc[-1]-x.iloc[0])/(x.max()-x.min()+1e-9)*100 if len(x)>1 else 0, raw=False)
|
||
mkt = hsi.set_index("date")[["mkt_above","mkt_adx","mkt_rsi","mkt_ret20"]]
|
||
|
||
# 行业指数（等权日收益 → 20日动量）
|
||
print("构建港股行业指数...", flush=True)
|
||
sec_daily = {} # sector -> DataFrame(date, ret1)
|
||
for idx, code in enumerate(codes):
|
||
sec = sector.get(code)
|
||
if not sec:
|
||
continue
|
||
conn = sqlite3.connect(DB)
|
||
df = pd.read_sql("SELECT date, close FROM stock_daily WHERE code=? ORDER BY date",
|
||
conn, params=(code,))
|
||
conn.close()
|
||
if len(df) < 90:
|
||
continue
|
||
df = df.sort_values("date").reset_index(drop=True)
|
||
df["ret1"] = df["close"].pct_change() * 100
|
||
sec_daily.setdefault(sec, []).append(df[["date","ret1"]])
|
||
if (idx+1) % 150 == 0:
|
||
print(f" 行业收集 {idx+1}/{len(codes)}", flush=True)
|
||
|
||
sec_index = {}
|
||
for sec, parts in sec_daily.items():
|
||
allp = pd.concat(parts, ignore_index=True)
|
||
g = allp.groupby("date")["ret1"].mean().reset_index() # 等权行业日收益
|
||
g["sec_ret20"] = g["ret1"].rolling(20).sum() # 20日累计
|
||
sec_index[sec] = g.set_index("date")["sec_ret20"]
|
||
print(f"行业指数: {len(sec_index)} 个行业", flush=True)
|
||
|
||
# 估值分位（每日截面）
|
||
print("加载历史估值...", flush=True)
|
||
conn = sqlite3.connect(DB)
|
||
hist = pd.read_sql(
|
||
"SELECT code, date, pe_ttm, pb FROM stock_fundamentals_history WHERE length(code)=5", conn)
|
||
conn.close()
|
||
if len(hist) > 0:
|
||
hist["pe_q"] = hist.groupby("date")["pe_ttm"].rank(pct=True)
|
||
hist["pb_q"] = hist.groupby("date")["pb"].rank(pct=True)
|
||
hist = hist[["code","date","pe_q","pb_q"]]
|
||
else:
|
||
hist = pd.DataFrame(columns=["code","date","pe_q","pb_q"])
|
||
print(f"估值历史: {len(hist)} 条", flush=True)
|
||
|
||
# 资金流（当日主力净流入，万元）
|
||
print("加载资金流...", flush=True)
|
||
conn = sqlite3.connect(DB)
|
||
flow = pd.read_sql(
|
||
"SELECT code, date, flow_in, flow_out FROM hk_flow_daily", conn)
|
||
conn.close()
|
||
if len(flow) > 0:
|
||
flow["flow1"] = (flow["flow_in"] - flow["flow_out"]) / 10000.0 # 万元→亿
|
||
flow = flow[["code","date","flow1"]]
|
||
else:
|
||
flow = pd.DataFrame(columns=["code","date","flow1"])
|
||
print(f"资金流: {len(flow)} 条", flush=True)
|
||
|
||
# 市值（当前市值×价格比反推历史，总股本短期不变）
|
||
print("加载市值...", flush=True)
|
||
conn = sqlite3.connect(DB)
|
||
fund_mc = pd.read_sql(
|
||
"SELECT code, mcap_total FROM stock_fundamentals WHERE length(code)=5", conn)
|
||
conn.close()
|
||
fund_mc = fund_mc.dropna()
|
||
# 每只股票最近收盘价（面板尾部）作为反推基准——在面板构建后统一计算
|
||
|
||
# 构建面板
|
||
rows = []
|
||
for idx, code in enumerate(codes):
|
||
sec = sector.get(code)
|
||
conn = sqlite3.connect(DB)
|
||
df = pd.read_sql(
|
||
"SELECT date, open, close, high, low, volume FROM stock_daily WHERE code=? ORDER BY date",
|
||
conn, params=(code,))
|
||
conn.close()
|
||
if len(df) < 90:
|
||
continue
|
||
df = df.sort_values("date").reset_index(drop=True)
|
||
c = df["close"].values
|
||
df["rsi"] = calc_rsi(list(c))
|
||
ma60 = df["close"].rolling(60).mean()
|
||
ma20 = df["close"].rolling(20).mean()
|
||
df["bias60"] = (df["close"]/ma60 - 1) * 100
|
||
df["bias20"] = (df["close"]/ma20 - 1) * 100
|
||
df["ret1"] = df["close"].pct_change(1) * 100
|
||
df["ret5"] = df["close"].pct_change(5) * 100
|
||
df["ret20"] = df["close"].pct_change(20) * 100
|
||
lo20 = df["low"].rolling(20).min()
|
||
df["dist_lo20"] = (df["close"]/lo20 - 1) * 100
|
||
df["hi20_new"] = (df["close"] >= df["high"].rolling(20).max()).astype(int)
|
||
v5 = df["volume"].rolling(5).mean()
|
||
v20 = df["volume"].rolling(20).mean()
|
||
df["vol_ratio"] = (v5/v20).round(3)
|
||
df["code"] = code
|
||
df = df.merge(mkt, left_on="date", right_index=True, how="left")
|
||
# 行业动量
|
||
if sec and sec in sec_index:
|
||
df = df.merge(sec_index[sec].rename("sec_ret20"), left_on="date", right_index=True, how="left")
|
||
else:
|
||
df["sec_ret20"] = np.nan
|
||
# 估值分位
|
||
df = df.merge(hist[hist["code"]==code][["date","pe_q","pb_q"]], on="date", how="left")
|
||
# 资金流
|
||
df = df.merge(flow[flow["code"]==code][["date","flow1"]], on="date", how="left")
|
||
rows.append(df[["code","date","close","mkt_above","mkt_adx","mkt_rsi","mkt_ret20",
|
||
"sec_ret20","rsi","bias60","bias20","dist_lo20","ret1","ret5","ret20",
|
||
"vol_ratio","hi20_new","pe_q","pb_q","flow1"]])
|
||
if (idx+1) % 100 == 0:
|
||
print(f" 面板 {idx+1}/{len(codes)}", flush=True)
|
||
|
||
panel = pd.concat(rows, ignore_index=True)
|
||
# 市值：当前市值×价格比反推历史（总股本短期不变）+ 每日市值分位
|
||
fund_mc = fund_mc.dropna()
|
||
last_px = panel.sort_values("date").groupby("code")["close"].last().rename("last_close")
|
||
fm = fund_mc.merge(last_px, on="code", how="inner")
|
||
fm = fm[fm["last_close"] > 0]
|
||
panel = panel.merge(fm[["code", "mcap_total", "last_close"]], on="code", how="left")
|
||
panel = panel[panel["last_close"] > 0]
|
||
panel["mcap"] = panel["mcap_total"] * panel["close"] / panel["last_close"]
|
||
panel["mcap_q"] = panel.groupby("date")["mcap"].rank(pct=True)
|
||
for col in ["sec_above", "news3", "flow5", "limit_up"]:
|
||
panel[col] = np.nan
|
||
panel.to_pickle("/tmp/panel_12d_hk.pkl")
|
||
print(f"\n港股面板v2: {len(panel)} 行 × {len(panel.columns)} 列", flush=True)
|
||
print("新增: sec_ret20(行业动量) + pe_q/pb_q(估值分位) + flow1(资金流) + mcap_q(市值分位)", flush=True)
|
||
print("仍缺: flow5(港股无历史)/news3/行业above(后续补)", flush=True)
|
||
|
||
if __name__ == "__main__":
|
||
main()
|