{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "394e5e60",
   "metadata": {},
   "source": [
    "# 链上现金流低估值筛选：复算 Notebook\n",
    "\n",
    "## TL;DR\n",
    "\n",
    "这份 Notebook 复算页面使用的两套估值：`流通市值 / 正 earnings`（真 PE）与 `流通市值 / holdersRevenue`（持有人现金流类 PE）。它还验证最近 30 日跑速、FDV、供应完整性、价格覆盖和图表映射，不从页面 HTML 反向抄数。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "6eafe04e",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2026-07-13T09:29:40.635476Z",
     "iopub.status.busy": "2026-07-13T09:29:40.635246Z",
     "iopub.status.idle": "2026-07-13T09:29:40.679292Z",
     "shell.execute_reply": "2026-07-13T09:29:40.678307Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(13, 9, 310, 192, 28)"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from pathlib import Path\n",
    "import json, math, statistics\n",
    "import duckdb\n",
    "from IPython.display import Markdown, display\n",
    "\n",
    "ROOT = Path.cwd()\n",
    "SOURCE = ROOT / \"source\"\n",
    "\n",
    "valuation = json.loads((SOURCE / \"valuation_rows.json\").read_text())\n",
    "watchlist = json.loads((SOURCE / \"watchlist_rows.json\").read_text())\n",
    "prices = json.loads((SOURCE / \"price_monthly_rows.json\").read_text())\n",
    "financials = json.loads((SOURCE / \"financial_quarterly_rows.json\").read_text())\n",
    "chart_map = json.loads((SOURCE / \"chart_map.json\").read_text())\n",
    "quality = json.loads((SOURCE / \"data_quality.json\").read_text())\n",
    "\n",
    "len(valuation), len(watchlist), len(prices), len(financials), len(chart_map)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "27f22390",
   "metadata": {},
   "source": [
    "## Context & Methods\n",
    "\n",
    "- 市场与供应：CoinMarketCap UCID 为主；CMC 无有效值时用 CoinGecko。若两家市值因流通供应口径相差超过 15%，采用较高市值与相应供应，降低虚假低估风险。\n",
    "- 真 PE：仅在 Token Terminal 365 日 earnings 为正时计算。\n",
    "- 持有人回流倍数：流通市值 / DefiLlama 365 日 holdersRevenue。\n",
    "- 当前跑速：流通市值 /（30 日 holdersRevenue × 365/30）。\n",
    "- 严格入选：链上客户付款、正 earnings、真 PE<20、过去 12 个月已有回流。\n",
    "- 当前类 PE 入选：TTM 与 30 日年化持有人回流倍数都<20。\n",
    "- 财务图只使用完整季度；价格从最近两年起点或实际上市日开始，空缺不补零。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "3549f22d",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2026-07-13T09:29:40.680803Z",
     "iopub.status.busy": "2026-07-13T09:29:40.680632Z",
     "iopub.status.idle": "2026-07-13T09:29:40.685133Z",
     "shell.execute_reply": "2026-07-13T09:29:40.684488Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Valuation formulas recomputed for 22 rows\n"
     ]
    }
   ],
   "source": [
    "def safe_div(a, b):\n",
    "    return a / b if a is not None and b not in (None, 0) else None\n",
    "\n",
    "for row in valuation + watchlist:\n",
    "    expected_true = safe_div(row[\"market_cap_usd\"], row[\"tt_earnings_365d\"])         if row[\"tt_earnings_365d\"] is not None and row[\"tt_earnings_365d\"] > 0 else None\n",
    "    expected_holder = safe_div(row[\"market_cap_usd\"], row[\"dl_holder_revenue_ttm\"])\n",
    "    expected_current = safe_div(row[\"market_cap_usd\"], row[\"dl_holder_revenue_30d_annualized\"])\n",
    "    assert (expected_true is None and row[\"true_pe\"] is None) or math.isclose(expected_true, row[\"true_pe\"], rel_tol=1e-12)\n",
    "    assert math.isclose(expected_holder, row[\"holder_pe\"], rel_tol=1e-12)\n",
    "    assert math.isclose(expected_current, row[\"holder_pe_30d_runrate\"], rel_tol=1e-12)\n",
    "\n",
    "print(\"Valuation formulas recomputed for\", len(valuation) + len(watchlist), \"rows\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "20ce3950",
   "metadata": {},
   "source": [
    "## Data\n",
    "\n",
    "下面展示控制快照、市场数据 fallback/冲突和前 10 行分析粒度。完整原始 API 响应位于 `source/raw/`，页面只嵌入经审阅、有限行数的快照。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "00e3b9fd",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2026-07-13T09:29:40.686655Z",
     "iopub.status.busy": "2026-07-13T09:29:40.686494Z",
     "iopub.status.idle": "2026-07-13T09:29:40.693218Z",
     "shell.execute_reply": "2026-07-13T09:29:40.692612Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/markdown": [
       "**Generated:** `2026-07-13T09:27:35Z`  \n",
       "**Market fallbacks/conservative overrides:** VELO, FLIP, DBR, THOR  \n",
       "**>15% market-cap conflicts:** CAKE (15.6%), VELO (35.7%), DBR (176.8%)"
      ],
      "text/plain": [
       "<IPython.core.display.Markdown object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "[{'symbol': 'PUMP',\n",
       "  'market_source': 'CoinMarketCap',\n",
       "  'market_cap_usd': 606885040.8116436,\n",
       "  'true_pe': None,\n",
       "  'holder_pe': 1.9657588270590052,\n",
       "  'holder_pe_30d_runrate': 4.587160085717107,\n",
       "  'fdv_holder_pe': 4.893588048101623},\n",
       " {'symbol': 'CAKE',\n",
       "  'market_source': 'CoinMarketCap',\n",
       "  'market_cap_usd': 533500463.1019966,\n",
       "  'true_pe': 1.8957452042750085,\n",
       "  'holder_pe': 8.70836873839036,\n",
       "  'holder_pe_30d_runrate': 26.83751108963797,\n",
       "  'fdv_holder_pe': 9.109022541548025},\n",
       " {'symbol': 'AERO',\n",
       "  'market_source': 'CoinMarketCap',\n",
       "  'market_cap_usd': 478873905.8896335,\n",
       "  'true_pe': None,\n",
       "  'holder_pe': 3.7713926828288598,\n",
       "  'holder_pe_30d_runrate': 8.709481997014143,\n",
       "  'fdv_holder_pe': 7.566966397257421},\n",
       " {'symbol': 'GMX',\n",
       "  'market_source': 'CoinMarketCap',\n",
       "  'market_cap_usd': 59043889.69060304,\n",
       "  'true_pe': 2.3547102284073764,\n",
       "  'holder_pe': 5.909150562583634,\n",
       "  'holder_pe_30d_runrate': 10.402121269974058,\n",
       "  'fdv_holder_pe': 7.502882722898112},\n",
       " {'symbol': 'XVS',\n",
       "  'market_source': 'CoinMarketCap',\n",
       "  'market_cap_usd': 43881397.6661801,\n",
       "  'true_pe': 3.971961887260376,\n",
       "  'holder_pe': 16.502101305753023,\n",
       "  'holder_pe_30d_runrate': 46.997605211462954,\n",
       "  'fdv_holder_pe': 30.254614973262033},\n",
       " {'symbol': 'GNS',\n",
       "  'market_source': 'CoinMarketCap',\n",
       "  'market_cap_usd': 14153828.835865665,\n",
       "  'true_pe': None,\n",
       "  'holder_pe': 2.8759994871063417,\n",
       "  'holder_pe_30d_runrate': 13.523300444857375,\n",
       "  'fdv_holder_pe': 2.8759994879464212},\n",
       " {'symbol': 'VELO',\n",
       "  'market_source': 'Conservative max(CMC, CoinGecko)',\n",
       "  'market_cap_usd': 26032276.0,\n",
       "  'true_pe': None,\n",
       "  'holder_pe': 3.4052532852654243,\n",
       "  'holder_pe_30d_runrate': 7.339118002351902,\n",
       "  'fdv_holder_pe': 6.958448554169272},\n",
       " {'symbol': 'MPLX',\n",
       "  'market_source': 'CoinMarketCap',\n",
       "  'market_cap_usd': 14188947.363196498,\n",
       "  'true_pe': None,\n",
       "  'holder_pe': 2.525453505282754,\n",
       "  'holder_pe_30d_runrate': 15.325976447151879,\n",
       "  'fdv_holder_pe': 4.95202974311438},\n",
       " {'symbol': 'FLIP',\n",
       "  'market_source': 'CoinGecko fallback',\n",
       "  'market_cap_usd': 24780323.0,\n",
       "  'true_pe': 14.860824847526272,\n",
       "  'holder_pe': 8.169383934941166,\n",
       "  'holder_pe_30d_runrate': 12.495867164309686,\n",
       "  'fdv_holder_pe': 8.245333160145531},\n",
       " {'symbol': 'DBR',\n",
       "  'market_source': 'Conservative max(CMC, CoinGecko)',\n",
       "  'market_cap_usd': 86756896.0,\n",
       "  'true_pe': 13.44875373890456,\n",
       "  'holder_pe': 8.37150833325855,\n",
       "  'holder_pe_30d_runrate': 10.110099575108524,\n",
       "  'fdv_holder_pe': 15.720708732009804}]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "display(Markdown(\n",
    "    f\"**Generated:** `{quality['generated_at']}`  \\n\"\n",
    "    f\"**Market fallbacks/conservative overrides:** {', '.join(quality['market_fallbacks']) or 'None'}  \\n\"\n",
    "    f\"**>15% market-cap conflicts:** \" + \", \".join(\n",
    "        f\"{x['symbol']} ({x['difference']:.1%})\" for x in quality['market_source_conflicts_over_15pct']\n",
    "    )\n",
    "))\n",
    "\n",
    "preview_fields = [\"symbol\", \"market_source\", \"market_cap_usd\", \"true_pe\", \"holder_pe\", \"holder_pe_30d_runrate\", \"fdv_holder_pe\"]\n",
    "preview = [{field: row.get(field) for field in preview_fields} for row in valuation[:10]]\n",
    "preview"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "12644a05",
   "metadata": {},
   "source": [
    "## Results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "41a6f795",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2026-07-13T09:29:40.694944Z",
     "iopub.status.busy": "2026-07-13T09:29:40.694780Z",
     "iopub.status.idle": "2026-07-13T09:29:40.702776Z",
     "shell.execute_reply": "2026-07-13T09:29:40.702010Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/markdown": [
       "### 真 PE<20\n",
       "|项目|真 PE|TTM回流倍数|30日倍数|市场源|\n",
       "|---|---|---|---|---|\n",
       "|CAKE|1.90|8.71|26.84|CoinMarketCap|\n",
       "|GMX|2.35|5.91|10.40|CoinMarketCap|\n",
       "|XVS|3.97|16.50|47.00|CoinMarketCap|\n",
       "|DBR|13.45|8.37|10.11|Conservative max(CMC, CoinGecko)|\n",
       "|FLIP|14.86|8.17|12.50|CoinGecko fallback|"
      ],
      "text/plain": [
       "<IPython.core.display.Markdown object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/markdown": [
       "### 当前持有人现金流类 PE<20\n",
       "|项目|TTM倍数|30日倍数|FDV倍数|真 PE|\n",
       "|---|---|---|---|---|\n",
       "|QUICK|1.22|2.68|1.84|N/M|\n",
       "|PUMP|1.97|4.59|4.89|N/M|\n",
       "|THOR|2.37|6.35|2.37|N/M|\n",
       "|MPLX|2.53|15.33|4.95|N/M|\n",
       "|GNS|2.88|13.52|2.88|N/M|\n",
       "|VELO|3.41|7.34|6.96|N/M|\n",
       "|AERO|3.77|8.71|7.57|N/M|\n",
       "|APEX|4.00|17.51|14.44|N/M|\n",
       "|GMX|5.91|10.40|7.50|2.35|\n",
       "|FLIP|8.17|12.50|8.25|14.86|\n",
       "|DBR|8.37|10.11|15.72|13.45|"
      ],
      "text/plain": [
       "<IPython.core.display.Markdown object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "strict = sorted(\n",
    "    [row for row in valuation if row[\"strict_true_pe_pass\"]],\n",
    "    key=lambda row: row[\"true_pe\"],\n",
    ")\n",
    "current = sorted(\n",
    "    [row for row in valuation if row[\"holder_value_pass\"] and row[\"current_runrate_pass\"]],\n",
    "    key=lambda row: row[\"holder_pe\"],\n",
    ")\n",
    "\n",
    "assert [row[\"symbol\"] for row in strict] == quality[\"strict_true_pe_passes\"]\n",
    "assert [row[\"symbol\"] for row in current] == quality[\"current_cashflow_passes\"]\n",
    "assert all(row[\"true_pe\"] < 20 for row in strict)\n",
    "assert all(row[\"holder_pe\"] < 20 and row[\"holder_pe_30d_runrate\"] < 20 for row in current)\n",
    "\n",
    "def md_table(rows, fields, headers):\n",
    "    lines = [\"|\" + \"|\".join(headers) + \"|\", \"|\" + \"|\".join([\"---\"] * len(headers)) + \"|\"]\n",
    "    for row in rows:\n",
    "        vals = []\n",
    "        for field in fields:\n",
    "            value = row.get(field)\n",
    "            if isinstance(value, float):\n",
    "                value = f\"{value:.2f}\"\n",
    "            vals.append(str(value if value is not None else \"N/M\"))\n",
    "        lines.append(\"|\" + \"|\".join(vals) + \"|\")\n",
    "    return \"\\n\".join(lines)\n",
    "\n",
    "display(Markdown(\"### 真 PE<20\\n\" + md_table(\n",
    "    strict,\n",
    "    [\"symbol\", \"true_pe\", \"holder_pe\", \"holder_pe_30d_runrate\", \"market_source\"],\n",
    "    [\"项目\", \"真 PE\", \"TTM回流倍数\", \"30日倍数\", \"市场源\"],\n",
    ")))\n",
    "display(Markdown(\"### 当前持有人现金流类 PE<20\\n\" + md_table(\n",
    "    current,\n",
    "    [\"symbol\", \"holder_pe\", \"holder_pe_30d_runrate\", \"fdv_holder_pe\", \"true_pe\"],\n",
    "    [\"项目\", \"TTM倍数\", \"30日倍数\", \"FDV倍数\", \"真 PE\"],\n",
    ")))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "99deea59",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2026-07-13T09:29:40.704462Z",
     "iopub.status.busy": "2026-07-13T09:29:40.704266Z",
     "iopub.status.idle": "2026-07-13T09:29:40.729459Z",
     "shell.execute_reply": "2026-07-13T09:29:40.728720Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'valuation': 13, 'price_monthly': 310, 'financial_quarterly': 192}"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Execute the same source-path SQL declared in the portable artifact.\n",
    "con = duckdb.connect()\n",
    "screen_sql = (ROOT / \"queries/screen_snapshot.sql\").read_text()\n",
    "price_sql = (ROOT / \"queries/price_monthly.sql\").read_text()\n",
    "financial_sql = (ROOT / \"queries/financial_quarterly.sql\").read_text()\n",
    "\n",
    "sql_counts = {\n",
    "    \"valuation\": len(con.execute(screen_sql).fetchall()),\n",
    "    \"price_monthly\": len(con.execute(price_sql).fetchall()),\n",
    "    \"financial_quarterly\": len(con.execute(financial_sql).fetchall()),\n",
    "}\n",
    "assert sql_counts[\"valuation\"] == len(valuation)\n",
    "assert sql_counts[\"price_monthly\"] == len(prices)\n",
    "assert sql_counts[\"financial_quarterly\"] == len(financials)\n",
    "sql_counts"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "86d3f2eb",
   "metadata": {},
   "source": [
    "## Takeaways\n",
    "\n",
    "1. 真 PE 名单和持有人现金流类 PE 名单不是同一集合；没有正 earnings 的项目不能冒充真 PE。\n",
    "2. 当前类 PE 候选通过了 30 日反证，但仍要进一步扣除 emissions、未来解锁和退出流动性成本。\n",
    "3. 永久销毁、国库买回和锁仓分配的经济质量不同，政策百分比不能代替实际 365 日执行额。\n",
    "4. 页面使用的每张图都映射到一个明确 dataset 和 source id；价格与财务空缺未被补零。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "1dc6bd3b",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2026-07-13T09:29:40.730917Z",
     "iopub.status.busy": "2026-07-13T09:29:40.730747Z",
     "iopub.status.idle": "2026-07-13T09:29:40.738526Z",
     "shell.execute_reply": "2026-07-13T09:29:40.737738Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "All notebook QA checks passed\n"
     ]
    }
   ],
   "source": [
    "artifact = json.loads((ROOT / \"artifact.json\").read_text())\n",
    "chart_ids = {chart[\"id\"] for chart in artifact[\"manifest\"][\"charts\"]}\n",
    "mapped_ids = {row[\"chart_id\"] for row in chart_map}\n",
    "\n",
    "assert chart_ids == mapped_ids\n",
    "assert len(chart_ids) == 28\n",
    "assert all(quality[\"checks\"].values())\n",
    "assert all(row[\"true_pe\"] is None or row[\"true_pe\"] > 0 for row in valuation + watchlist)\n",
    "assert set(row[\"symbol\"] for row in valuation) == set(\n",
    "    row[\"symbol\"] for row in financials\n",
    ")\n",
    "\n",
    "print(\"All notebook QA checks passed\")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}