{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 03 — Treasury & Pool Analysis\n",
    "\n",
    "Analyzing the flow of value through the TEC's key pools:\n",
    "- Common Pool (funding source for conviction voting)\n",
    "- Reserve Pool (bonding curve collateral)\n",
    "- Token balances and USD valuations over time\n",
    "- Treasury health and sustainability metrics"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2026-04-03T21:06:05.969858Z",
     "iopub.status.busy": "2026-04-03T21:06:05.969506Z",
     "iopub.status.idle": "2026-04-03T21:06:08.944904Z",
     "shell.execute_reply": "2026-04-03T21:06:08.942451Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Common Pool: 2022-01-19 to 2023-12-19 (871 rows)\n",
      "Reserve Pool: 2022-01-19 to 2023-12-19 (886 rows)\n",
      "Token Balances: 2023-12-20 to 2025-12-10 (1985 rows)\n",
      "\n",
      "Token symbols in balances: <StringArray>\n",
      "['DAI', 'TEC', 'rETH']\n",
      "Length: 3, dtype: str\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "import matplotlib.dates as mdates\n",
    "import seaborn as sns\n",
    "\n",
    "sns.set_theme(style='whitegrid', palette='deep')\n",
    "plt.rcParams['figure.figsize'] = (14, 6)\n",
    "plt.rcParams['figure.dpi'] = 100\n",
    "\n",
    "DATA = '../data/onchain'\n",
    "\n",
    "# Load pool data\n",
    "common = pd.read_csv(f'{DATA}/dune_common_pool.csv')\n",
    "reserve = pd.read_csv(f'{DATA}/dune_reserve_pool.csv')\n",
    "balances = pd.read_csv(f'{DATA}/dune_token_balances_usd.csv')\n",
    "\n",
    "# Parse dates\n",
    "common['date'] = pd.to_datetime(common['day'])\n",
    "reserve['date'] = pd.to_datetime(reserve['day'])\n",
    "balances['date'] = pd.to_datetime(balances['day'])\n",
    "\n",
    "print('Common Pool:', common['date'].min().date(), 'to', common['date'].max().date(), f'({len(common)} rows)')\n",
    "print('Reserve Pool:', reserve['date'].min().date(), 'to', reserve['date'].max().date(), f'({len(reserve)} rows)')\n",
    "print('Token Balances:', balances['date'].min().date(), 'to', balances['date'].max().date(), f'({len(balances)} rows)')\n",
    "print('\\nToken symbols in balances:', balances['token_symbol'].unique())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. Common Pool & Reserve Pool Over Time (Gnosis Chain era)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2026-04-03T21:06:09.097931Z",
     "iopub.status.busy": "2026-04-03T21:06:09.097051Z",
     "iopub.status.idle": "2026-04-03T21:06:21.621444Z",
     "shell.execute_reply": "2026-04-03T21:06:21.617017Z"
    }
   },
   "outputs": [
    {
     "data": {
      "image/png": "[encoded data, 163616 characters]",
      "text/plain": [
       "<Figure size 1600x1400 with 3 Axes>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Common Pool: 1,073,976 peak → 0 end (-100.0% drawdown)\n",
      "Reserve Pool: 1,119,096 peak → -0 end (-100.0% drawdown)\n"
     ]
    }
   ],
   "source": [
    "fig, axes = plt.subplots(3, 1, figsize=(16, 14), sharex=True)\n",
    "\n",
    "# Common Pool balance\n",
    "ax = axes[0]\n",
    "ax.plot(common['date'], common['balance'], color='#2ecc71', linewidth=1.5)\n",
    "ax.fill_between(common['date'], common['balance'], alpha=0.2, color='#2ecc71')\n",
    "ax.set_ylabel('Balance (native token)')\n",
    "ax.set_title('Common Pool Balance Over Time')\n",
    "# Annotate key levels\n",
    "ax.axhline(y=common['balance'].iloc[0], color='gray', linestyle='--', alpha=0.3)\n",
    "ax.text(common['date'].iloc[0], common['balance'].iloc[0] * 1.02,\n",
    "        f'Start: {common[\"balance\"].iloc[0]:,.0f}', fontsize=9)\n",
    "ax.text(common['date'].iloc[-1], common['balance'].iloc[-1] * 1.02,\n",
    "        f'End: {common[\"balance\"].iloc[-1]:,.0f}', fontsize=9)\n",
    "\n",
    "# Reserve Pool balance\n",
    "ax = axes[1]\n",
    "ax.plot(reserve['date'], reserve['balance'], color='#3498db', linewidth=1.5)\n",
    "ax.fill_between(reserve['date'], reserve['balance'], alpha=0.2, color='#3498db')\n",
    "ax.set_ylabel('Balance (native token)')\n",
    "ax.set_title('Reserve Pool Balance Over Time')\n",
    "ax.text(reserve['date'].iloc[0], reserve['balance'].iloc[0] * 1.02,\n",
    "        f'Start: {reserve[\"balance\"].iloc[0]:,.0f}', fontsize=9)\n",
    "ax.text(reserve['date'].iloc[-1], reserve['balance'].iloc[-1] * 1.02,\n",
    "        f'End: {reserve[\"balance\"].iloc[-1]:,.0f}', fontsize=9)\n",
    "\n",
    "# Inflows & outflows for common pool\n",
    "ax = axes[2]\n",
    "ax.bar(common['date'], common['inflow'], color='#2ecc71', alpha=0.6, label='Inflow', width=1)\n",
    "ax.bar(common['date'], -common['outflow'].abs(), color='#e74c3c', alpha=0.6, label='Outflow', width=1)\n",
    "ax.set_ylabel('Flow Amount')\n",
    "ax.set_title('Common Pool Daily Flows')\n",
    "ax.legend()\n",
    "ax.axhline(y=0, color='black', linewidth=0.5)\n",
    "\n",
    "for ax in axes:\n",
    "    ax.xaxis.set_major_formatter(mdates.DateFormatter('%b %Y'))\n",
    "    ax.xaxis.set_major_locator(mdates.MonthLocator(interval=2))\n",
    "\n",
    "plt.setp(axes[-1].xaxis.get_majorticklabels(), rotation=45)\n",
    "plt.tight_layout()\n",
    "plt.savefig(f'{DATA}/../snapshots/treasury_pools.png', dpi=150, bbox_inches='tight')\n",
    "plt.show()\n",
    "\n",
    "# Key metrics\n",
    "cp_drawdown = (common['balance'].iloc[-1] - common['balance'].max()) / common['balance'].max()\n",
    "rp_drawdown = (reserve['balance'].iloc[-1] - reserve['balance'].max()) / reserve['balance'].max()\n",
    "print(f'\\nCommon Pool: {common[\"balance\"].max():,.0f} peak → {common[\"balance\"].iloc[-1]:,.0f} end ({cp_drawdown:.1%} drawdown)')\n",
    "print(f'Reserve Pool: {reserve[\"balance\"].max():,.0f} peak → {reserve[\"balance\"].iloc[-1]:,.0f} end ({rp_drawdown:.1%} drawdown)')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. Post-Migration Token Balances (Optimism era)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2026-04-03T21:06:21.628412Z",
     "iopub.status.busy": "2026-04-03T21:06:21.627450Z",
     "iopub.status.idle": "2026-04-03T21:06:24.628378Z",
     "shell.execute_reply": "2026-04-03T21:06:24.623487Z"
    }
   },
   "outputs": [
    {
     "data": {
      "image/png": "[encoded data, 155664 characters]",
      "text/plain": [
       "<Figure size 1600x1000 with 2 Axes>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Total treasury USD:\n",
      "  Max: $348,595\n",
      "  Min: $153,108\n",
      "  Latest: $208,710\n",
      "  Drawdown from max: -40.1%\n"
     ]
    }
   ],
   "source": [
    "# Pivot balances by token\n",
    "fig, axes = plt.subplots(2, 1, figsize=(16, 10))\n",
    "\n",
    "for token in balances['token_symbol'].unique():\n",
    "    subset = balances[balances['token_symbol'] == token].sort_values('date')\n",
    "    \n",
    "    # Native balance\n",
    "    axes[0].plot(subset['date'], subset['balance'], label=token, linewidth=1.5)\n",
    "    # USD balance\n",
    "    axes[1].plot(subset['date'], subset['balance_usd_f'], label=token, linewidth=1.5)\n",
    "\n",
    "axes[0].set_ylabel('Token Balance')\n",
    "axes[0].set_title('Treasury Token Balances (Post-Migration)')\n",
    "axes[0].legend()\n",
    "\n",
    "axes[1].set_ylabel('USD Value')\n",
    "axes[1].set_title('Treasury USD Valuation')\n",
    "axes[1].legend()\n",
    "\n",
    "for ax in axes:\n",
    "    ax.xaxis.set_major_formatter(mdates.DateFormatter('%b %Y'))\n",
    "    ax.xaxis.set_major_locator(mdates.MonthLocator(interval=2))\n",
    "\n",
    "plt.setp(axes[-1].xaxis.get_majorticklabels(), rotation=45)\n",
    "plt.tight_layout()\n",
    "plt.savefig(f'{DATA}/../snapshots/treasury_balances_usd.png', dpi=150, bbox_inches='tight')\n",
    "plt.show()\n",
    "\n",
    "# Total USD over time\n",
    "total_usd = balances.groupby('date')['balance_usd_f'].sum().sort_index()\n",
    "print(f'\\nTotal treasury USD:')\n",
    "print(f'  Max: ${total_usd.max():,.0f}')\n",
    "print(f'  Min: ${total_usd.min():,.0f}')\n",
    "print(f'  Latest: ${total_usd.iloc[-1]:,.0f}')\n",
    "print(f'  Drawdown from max: {(total_usd.iloc[-1] - total_usd.max()) / total_usd.max():.1%}')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. Treasury Sustainability Analysis\n",
    "\n",
    "How long could the treasury sustain operations at various burn rates?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2026-04-03T21:06:24.635805Z",
     "iopub.status.busy": "2026-04-03T21:06:24.634862Z",
     "iopub.status.idle": "2026-04-03T21:06:27.747932Z",
     "shell.execute_reply": "2026-04-03T21:06:27.744585Z"
    }
   },
   "outputs": [
    {
     "data": {
      "image/png": "[encoded data, 85660 characters]",
      "text/plain": [
       "<Figure size 1600x1000 with 2 Axes>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Sustainability metrics:\n",
      "  Avg monthly outflow: 49,831\n",
      "  Avg monthly inflow: 49,831\n",
      "  Avg monthly net: -0\n",
      "  Months with negative net flow: 17/24\n"
     ]
    }
   ],
   "source": [
    "# Calculate net flow rates for common pool\n",
    "common_monthly = common.set_index('date').resample('ME').agg({\n",
    "    'inflow': 'sum',\n",
    "    'outflow': 'sum',\n",
    "    'balance': 'last'\n",
    "})\n",
    "common_monthly['net_flow'] = common_monthly['inflow'] - common_monthly['outflow'].abs()\n",
    "\n",
    "fig, axes = plt.subplots(2, 1, figsize=(16, 10))\n",
    "\n",
    "# Monthly net flows\n",
    "ax = axes[0]\n",
    "colors = ['#2ecc71' if v >= 0 else '#e74c3c' for v in common_monthly['net_flow']]\n",
    "ax.bar(common_monthly.index, common_monthly['net_flow'], color=colors, width=20)\n",
    "ax.axhline(y=0, color='black', linewidth=0.5)\n",
    "ax.set_ylabel('Net Flow')\n",
    "ax.set_title('Common Pool Monthly Net Flows (positive = growing)')\n",
    "ax.xaxis.set_major_formatter(mdates.DateFormatter('%b %Y'))\n",
    "\n",
    "# Runway analysis\n",
    "ax = axes[1]\n",
    "# Monthly outflow rate\n",
    "monthly_outflow = common_monthly['outflow'].abs()\n",
    "ax.bar(common_monthly.index, monthly_outflow, color='#e74c3c', alpha=0.6, width=20)\n",
    "ax.set_ylabel('Monthly Outflow')\n",
    "ax.set_title('Common Pool Monthly Spending')\n",
    "ax.xaxis.set_major_formatter(mdates.DateFormatter('%b %Y'))\n",
    "\n",
    "# Add trend line\n",
    "avg_outflow = monthly_outflow.mean()\n",
    "ax.axhline(y=avg_outflow, color='red', linestyle='--', alpha=0.5,\n",
    "           label=f'Avg: {avg_outflow:,.0f}/month')\n",
    "ax.legend()\n",
    "\n",
    "plt.tight_layout()\n",
    "plt.savefig(f'{DATA}/../snapshots/treasury_sustainability.png', dpi=150, bbox_inches='tight')\n",
    "plt.show()\n",
    "\n",
    "print(f'\\nSustainability metrics:')\n",
    "print(f'  Avg monthly outflow: {avg_outflow:,.0f}')\n",
    "print(f'  Avg monthly inflow: {common_monthly[\"inflow\"].mean():,.0f}')\n",
    "print(f'  Avg monthly net: {common_monthly[\"net_flow\"].mean():,.0f}')\n",
    "print(f'  Months with negative net flow: {(common_monthly[\"net_flow\"] < 0).sum()}/{len(common_monthly)}')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4. Reserve Ratio Analysis\n",
    "\n",
    "The ABC's reserve ratio determines the price curve shape. How did it evolve?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2026-04-03T21:06:27.753421Z",
     "iopub.status.busy": "2026-04-03T21:06:27.752637Z",
     "iopub.status.idle": "2026-04-03T21:06:30.800490Z",
     "shell.execute_reply": "2026-04-03T21:06:30.796751Z"
    }
   },
   "outputs": [
    {
     "data": {
      "image/png": "[encoded data, 151772 characters]",
      "text/plain": [
       "<Figure size 1600x1000 with 2 Axes>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Pool composition:\n",
      "  Start - Reserve: 31.0%, Common: 69.0%\n",
      "  End   - Reserve: 147.6%, Common: -47.6%\n"
     ]
    }
   ],
   "source": [
    "# Reserve ratio = reserve_balance / (token_supply * price)\n",
    "# We can approximate from pool data\n",
    "# For now, show reserve pool vs common pool ratio\n",
    "\n",
    "# Align dates\n",
    "common_daily = common.set_index('date')['balance'].resample('D').last().ffill()\n",
    "reserve_daily = reserve.set_index('date')['balance'].resample('D').last().ffill()\n",
    "\n",
    "aligned = pd.DataFrame({\n",
    "    'common_pool': common_daily,\n",
    "    'reserve_pool': reserve_daily\n",
    "}).dropna()\n",
    "\n",
    "aligned['total'] = aligned['common_pool'] + aligned['reserve_pool']\n",
    "aligned['reserve_share'] = aligned['reserve_pool'] / aligned['total']\n",
    "aligned['common_share'] = aligned['common_pool'] / aligned['total']\n",
    "\n",
    "fig, axes = plt.subplots(2, 1, figsize=(16, 10))\n",
    "\n",
    "ax = axes[0]\n",
    "ax.stackplot(aligned.index,\n",
    "             aligned['reserve_pool'], aligned['common_pool'],\n",
    "             labels=['Reserve Pool', 'Common Pool'],\n",
    "             colors=['#3498db', '#2ecc71'], alpha=0.7)\n",
    "ax.set_ylabel('Balance')\n",
    "ax.set_title('Pool Composition Over Time')\n",
    "ax.legend(loc='upper right')\n",
    "\n",
    "ax = axes[1]\n",
    "ax.plot(aligned.index, aligned['reserve_share'], color='#3498db', linewidth=1.5, label='Reserve Share')\n",
    "ax.plot(aligned.index, aligned['common_share'], color='#2ecc71', linewidth=1.5, label='Common Share')\n",
    "ax.set_ylabel('Share of Total')\n",
    "ax.set_title('Pool Share Ratio Over Time')\n",
    "ax.legend()\n",
    "ax.set_ylim(0, 1)\n",
    "\n",
    "for ax in axes:\n",
    "    ax.xaxis.set_major_formatter(mdates.DateFormatter('%b %Y'))\n",
    "    ax.xaxis.set_major_locator(mdates.MonthLocator(interval=2))\n",
    "\n",
    "plt.tight_layout()\n",
    "plt.savefig(f'{DATA}/../snapshots/pool_composition.png', dpi=150, bbox_inches='tight')\n",
    "plt.show()\n",
    "\n",
    "print(f'\\nPool composition:')\n",
    "print(f'  Start - Reserve: {aligned[\"reserve_share\"].iloc[0]:.1%}, Common: {aligned[\"common_share\"].iloc[0]:.1%}')\n",
    "print(f'  End   - Reserve: {aligned[\"reserve_share\"].iloc[-1]:.1%}, Common: {aligned[\"common_share\"].iloc[-1]:.1%}')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5. Post-Conviction Voting Treasury Spending (Optimism era)\n",
    "\n",
    "After conviction voting was shut down (~Dec 2023), the DAO migrated to Optimism and made manual treasury allocations. These show up as discrete balance drops in the token_balances_usd data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2026-04-03T21:06:30.808210Z",
     "iopub.status.busy": "2026-04-03T21:06:30.807433Z",
     "iopub.status.idle": "2026-04-03T21:06:36.949584Z",
     "shell.execute_reply": "2026-04-03T21:06:36.944625Z"
    }
   },
   "outputs": [
    {
     "data": {
      "image/png": "[encoded data, 155556 characters]",
      "text/plain": [
       "<Figure size 1600x1400 with 3 Axes>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Post-CV Major Spending Events (>5% balance drop):\n",
      "Date           Token        Amount   Change  Balance After\n",
      "------------------------------------------------------------\n",
      "2024-03-21     DAI         160,589    -53%        144,255\n",
      "2024-05-29     TEC          97,000   -100%            128\n",
      "2025-11-04     DAI          52,671    -34%        101,543\n"
     ]
    },
    {
     "data": {
      "image/png": "[encoded data, 96132 characters]",
      "text/plain": [
       "<Figure size 1600x600 with 1 Axes>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Post-Migration Treasury Summary:\n",
      "  Starting value: $344,246 (Dec 2023)\n",
      "  Peak value: $348,595\n",
      "  Current value: $208,710\n",
      "  Total spending events: 3\n",
      "  Drawdown from peak: -40.1%\n"
     ]
    }
   ],
   "source": [
    "# Analyze post-CV treasury spending from token balances\n",
    "# Find discrete spending events (large balance drops) for each token\n",
    "\n",
    "fig, axes = plt.subplots(3, 1, figsize=(16, 14))\n",
    "\n",
    "spending_events = []\n",
    "\n",
    "for i, token in enumerate(balances['token_symbol'].unique()):\n",
    "    subset = balances[balances['token_symbol'] == token].sort_values('date').copy()\n",
    "    ax = axes[i]\n",
    "    \n",
    "    # Plot balance\n",
    "    ax.plot(subset['date'], subset['balance'], color='steelblue', linewidth=1.5, label='Balance')\n",
    "    ax.fill_between(subset['date'], subset['balance'], alpha=0.15, color='steelblue')\n",
    "    \n",
    "    # Detect spending events: daily change > 5% drop\n",
    "    subset['change'] = subset['balance'].diff()\n",
    "    subset['pct_change'] = subset['change'] / subset['balance'].shift(1)\n",
    "    \n",
    "    threshold = -0.05\n",
    "    big_drops = subset[(subset['pct_change'] < threshold) & (subset['change'].abs() > 100)]\n",
    "    \n",
    "    for _, drop in big_drops.iterrows():\n",
    "        ax.axvline(x=drop['date'], color='#e74c3c', alpha=0.4, linewidth=1)\n",
    "        ax.annotate(f'{drop[\"change\"]:+,.0f}\\n({drop[\"pct_change\"]:.0%})',\n",
    "                   xy=(drop['date'], drop['balance']),\n",
    "                   xytext=(10, 15), textcoords='offset points',\n",
    "                   fontsize=7, color='#e74c3c', fontweight='bold',\n",
    "                   arrowprops=dict(arrowstyle='->', color='#e74c3c', lw=0.8))\n",
    "        spending_events.append({\n",
    "            'date': drop['date'],\n",
    "            'token': token,\n",
    "            'amount': abs(drop['change']),\n",
    "            'pct_change': drop['pct_change'],\n",
    "            'balance_after': drop['balance'],\n",
    "        })\n",
    "    \n",
    "    ax.set_ylabel(f'{token} Balance', fontsize=11)\n",
    "    ax.set_title(f'{token} Treasury Balance — Post-Migration', fontsize=13)\n",
    "    ax.xaxis.set_major_formatter(mdates.DateFormatter('%b %Y'))\n",
    "    ax.xaxis.set_major_locator(mdates.MonthLocator(interval=3))\n",
    "    ax.legend(fontsize=10)\n",
    "\n",
    "plt.setp(axes[-1].xaxis.get_majorticklabels(), rotation=45)\n",
    "plt.tight_layout()\n",
    "plt.savefig(f'{DATA}/../snapshots/post_cv_spending.png', dpi=150, bbox_inches='tight')\n",
    "plt.show()\n",
    "\n",
    "# Summary of spending events\n",
    "if spending_events:\n",
    "    spend_df = pd.DataFrame(spending_events).sort_values('date')\n",
    "    print(f'\\nPost-CV Major Spending Events (>{abs(threshold):.0%} balance drop):')\n",
    "    print(f'{\"Date\":<14} {\"Token\":<6} {\"Amount\":>12} {\"Change\":>8} {\"Balance After\":>14}')\n",
    "    print('-' * 60)\n",
    "    for _, evt in spend_df.iterrows():\n",
    "        print(f'{evt[\"date\"].strftime(\"%Y-%m-%d\"):<14} {evt[\"token\"]:<6} {evt[\"amount\"]:>12,.0f} {evt[\"pct_change\"]:>7.0%} {evt[\"balance_after\"]:>14,.0f}')\n",
    "\n",
    "# Total USD treasury over time with spending events marked\n",
    "fig, ax = plt.subplots(figsize=(16, 6))\n",
    "total_usd_daily = balances.groupby('date')['balance_usd_f'].sum().sort_index()\n",
    "ax.plot(total_usd_daily.index, total_usd_daily.values, color='steelblue', linewidth=2)\n",
    "ax.fill_between(total_usd_daily.index, total_usd_daily.values, alpha=0.15, color='steelblue')\n",
    "\n",
    "# Mark spending events\n",
    "for evt in spending_events:\n",
    "    if evt['date'] in total_usd_daily.index:\n",
    "        usd_val = total_usd_daily.loc[evt['date']]\n",
    "        ax.scatter([evt['date']], [usd_val], color='#e74c3c', s=80, zorder=5,\n",
    "                  marker='v', edgecolors='black', linewidth=0.5)\n",
    "\n",
    "ax.set_ylabel('Total USD Value', fontsize=12)\n",
    "ax.set_xlabel('Date', fontsize=12)\n",
    "ax.set_title('Total Treasury USD Value with Major Spending Events', fontsize=14)\n",
    "ax.xaxis.set_major_formatter(mdates.DateFormatter('%b %Y'))\n",
    "ax.xaxis.set_major_locator(mdates.MonthLocator(interval=3))\n",
    "plt.setp(ax.xaxis.get_majorticklabels(), rotation=45)\n",
    "plt.tight_layout()\n",
    "plt.savefig(f'{DATA}/../snapshots/post_cv_treasury_usd.png', dpi=150, bbox_inches='tight')\n",
    "plt.show()\n",
    "\n",
    "print(f'\\nPost-Migration Treasury Summary:')\n",
    "print(f'  Starting value: ${total_usd_daily.iloc[0]:,.0f} ({total_usd_daily.index[0].strftime(\"%b %Y\")})')\n",
    "print(f'  Peak value: ${total_usd_daily.max():,.0f}')\n",
    "print(f'  Current value: ${total_usd_daily.iloc[-1]:,.0f}')\n",
    "print(f'  Total spending events: {len(spending_events)}')\n",
    "print(f'  Drawdown from peak: {(total_usd_daily.iloc[-1] / total_usd_daily.max() - 1):.1%}')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6. Key Findings"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2026-04-03T21:06:36.955890Z",
     "iopub.status.busy": "2026-04-03T21:06:36.954936Z",
     "iopub.status.idle": "2026-04-03T21:06:36.975442Z",
     "shell.execute_reply": "2026-04-03T21:06:36.971606Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "============================================================\n",
      "TREASURY & POOLS — KEY FINDINGS\n",
      "============================================================\n",
      "\n",
      "COMMON POOL:\n",
      "  Peak balance: 1,073,976\n",
      "  Final balance: 0\n",
      "  Total outflows: 1,195,952\n",
      "  Net negative months: 17/24\n",
      "\n",
      "RESERVE POOL:\n",
      "  Peak balance: 1,119,096\n",
      "  Final balance: -0\n",
      "\n",
      "POST-MIGRATION (Optimism):\n",
      "  Treasury peaked at: $348,595\n",
      "  Treasury at end: $208,710\n",
      "\n",
      "KEY QUESTIONS:\n",
      "  1. Was the ABC tribute rate sufficient to replenish the common pool?\n",
      "  2. How did the reserve ratio compare to the theoretical target?\n",
      "  3. Did the treasury drawdown correlate with token price decline?\n",
      "  4. Could different spending limits have extended the runway?\n",
      "\n"
     ]
    }
   ],
   "source": [
    "print('=' * 60)\n",
    "print('TREASURY & POOLS — KEY FINDINGS')\n",
    "print('=' * 60)\n",
    "print(f'''\n",
    "COMMON POOL:\n",
    "  Peak balance: {common[\"balance\"].max():,.0f}\n",
    "  Final balance: {common[\"balance\"].iloc[-1]:,.0f}\n",
    "  Total outflows: {common[\"outflow\"].abs().sum():,.0f}\n",
    "  Net negative months: {(common_monthly[\"net_flow\"] < 0).sum()}/{len(common_monthly)}\n",
    "\n",
    "RESERVE POOL:\n",
    "  Peak balance: {reserve[\"balance\"].max():,.0f}\n",
    "  Final balance: {reserve[\"balance\"].iloc[-1]:,.0f}\n",
    "\n",
    "POST-MIGRATION (Optimism):\n",
    "  Treasury peaked at: ${total_usd.max():,.0f}\n",
    "  Treasury at end: ${total_usd.iloc[-1]:,.0f}\n",
    "\n",
    "KEY QUESTIONS:\n",
    "  1. Was the ABC tribute rate sufficient to replenish the common pool?\n",
    "  2. How did the reserve ratio compare to the theoretical target?\n",
    "  3. Did the treasury drawdown correlate with token price decline?\n",
    "  4. Could different spending limits have extended the runway?\n",
    "''')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "TEC Analysis",
   "language": "python",
   "name": "tec-analysis"
  },
  "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": 4
}
