{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "# How did bank margins and deposits change?\n", "Join regulatory bank financials to policy rates and inflation, then distinguish deposit growth from purchasing-power growth.\n", "FDIC certificate 628, 2020\u20132025 quarterly reports. NIMY is the provider\u2019s year-to-date annualised margin. Rates and CPI use quarter averages. Real deposit growth adjusts balance-sheet deposits for CPI; it does not measure customer flows." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "\"\"\"Join regulatory bank financials to policy rates and inflation, then distinguish deposit growth from purchasing-power growth.\n", "\n", "Research example. Live observations can change.\n", "\"\"\"\n", "\n", "from datetime import UTC, datetime\n", "from decimal import Decimal\n", "from textwrap import fill\n", "\n", "import matplotlib.pyplot as plt\n", "import numpy as np\n", "import pandas as pd\n", "\n", "AS_OF = datetime.now(UTC).date()\n", "NAVY, BLUE, PINK = \"#132244\", \"#8fb9ff\", \"#e7a2cc\"\n", "\n", "\n", "def fetch(qj):\n", " return {\n", " \"fdic_bank\": qj.domains.call(\n", " \"regulatory.banking.get_institutions\", provider=\"fdic\", filters=\"CERT:628\", limit=1\n", " ),\n", " \"fdic_history\": qj.domains.call(\n", " \"regulatory.banking.get_financials\",\n", " provider=\"fdic\",\n", " filters=\"CERT:628 AND REPDTE:[20200101 TO 20251231]\",\n", " limit=30,\n", " sort_by=\"REPDTE\",\n", " sort_order=\"ASC\",\n", " ),\n", " \"funds\": qj.domains.call(\n", " \"macro.indicators.get_fred_data_series_by_id\",\n", " provider=\"fred\",\n", " search_id=\"DFF\",\n", " start=\"2020-01-01\",\n", " end=\"2025-12-31\",\n", " ),\n", " \"bls_cpi\": qj.domains.call(\n", " \"macro.us.get_cpi\", provider=\"bls\", start_year=2020, end_year=2025, category=\"all\", seasonally_adjusted=True\n", " ),\n", " }\n", "\n", "\n", "def analyze(raw):\n", " institution = frame(raw[\"fdic_bank\"], date=None).iloc[0]\n", " if int(institution[\"CERT\"]) != 628:\n", " raise ValueError(\"Institution identity does not match the bank financials\")\n", " reports = frame(raw[\"fdic_history\"], date=None)\n", " reports.index = pd.to_datetime(reports[\"REPDTE\"], format=\"%Y%m%d\", utc=True)\n", " reports = reports.sort_index()\n", " rates, cpi = macro(raw[\"funds\"], \"DFF\"), bls_cpi(raw[\"bls_cpi\"])\n", " rows = []\n", " for end, report in reports.iterrows():\n", " start = end - pd.DateOffset(months=3)\n", " quarter_rates = rates.loc[(rates.index > start) & (rates.index <= end)]\n", " quarter_cpi = cpi.loc[(cpi.index > start) & (cpi.index <= end)]\n", " if quarter_rates.empty or len(quarter_cpi) != 3:\n", " continue\n", " rows.append(\n", " {\n", " \"Report date\": end,\n", " \"NIM %\": float(report[\"NIMY\"]),\n", " \"Policy rate %\": quarter_rates.mean(),\n", " \"Real deposits\": decimal(report[\"DEP\"]) / decimal(str(quarter_cpi.mean())),\n", " }\n", " )\n", " data = pd.DataFrame(rows).set_index(\"Report date\")\n", " if len(data) < 8:\n", " raise ValueError(\"Need at least eight matched quarterly reports\")\n", " # Require the exact prior-year quarter rather than shifting across gaps.\n", " previous = data[\"Real deposits\"].reindex(data.index - pd.DateOffset(years=1))\n", " data[\"Real deposit growth %\"] = [\n", " 100 * (ratio(now, old) - 1) if pd.notna(old) else np.nan\n", " for now, old in zip(data[\"Real deposits\"], previous, strict=True)\n", " ]\n", " fig, (top, bottom) = canvas(\"FDIC bank 628 \u00b7 \" + str(institution[\"NAMEHCR\"]), \"%\", panels=2)\n", " top.plot(data.index, data[\"NIM %\"], label=\"FDIC NIMY\")\n", " top.plot(data.index, data[\"Policy rate %\"], label=\"Effective Fed funds \u00b7 quarter mean\")\n", " legend(top)\n", " bottom.bar(data.index, data[\"Real deposit growth %\"], width=55, color=PINK)\n", " bottom.set_ylabel(\"CPI-adjusted deposit growth \u00b7 YoY %\")\n", " bottom.axhline(0, color=\"#afc1dc\", lw=0.8)\n", " from matplotlib.dates import DateFormatter, YearLocator\n", "\n", " for axis in [top, bottom]:\n", " axis.set_xlim(data.index.min() - pd.Timedelta(days=50), data.index.max() + pd.Timedelta(days=50))\n", " axis.xaxis.set_major_locator(YearLocator())\n", " axis.xaxis.set_major_formatter(DateFormatter(\"%Y\"))\n", " return data.drop(columns=\"Real deposits\").reset_index(), fig\n", "\n", "\n", "def payload(response):\n", " if hasattr(response, \"to_dict\"):\n", " response = response.to_dict()\n", " while isinstance(response, dict):\n", " if response.get(\"error\") or response.get(\"error_code\") or response.get(\"success\") is False:\n", " raise ValueError(\"Provider request failed\")\n", " if \"data\" in response:\n", " response = response[\"data\"]\n", " elif \"value\" in response:\n", " response = response[\"value\"]\n", " else:\n", " break\n", " return response\n", "\n", "\n", "def frame(response, date=\"date\"):\n", " rows = payload(response)\n", " if not isinstance(rows, list) or not rows:\n", " raise ValueError(\"No provider observations returned\")\n", " result = pd.DataFrame(rows)\n", " if date:\n", " result[date] = pd.to_datetime(result[date], utc=True)\n", " result = result.sort_values(date)\n", " return result\n", "\n", "\n", "def decimal(value):\n", " if pd.isna(value):\n", " raise ValueError(\"Required monetary value or quantity is missing\")\n", " return Decimal(str(value))\n", "\n", "\n", "def ratio(numerator, denominator):\n", " denominator = decimal(denominator)\n", " if denominator == 0:\n", " raise ValueError(\"Required denominator is zero\")\n", " return float(decimal(numerator) / denominator)\n", "\n", "\n", "def macro(response, column):\n", " values = frame(response).set_index(\"date\")[column]\n", " values = pd.to_numeric(values, errors=\"coerce\").dropna()\n", " if values.empty:\n", " raise ValueError(f\"No numeric observations for {column}\")\n", " return values\n", "\n", "\n", "def canvas(title, ylabel, panels=1):\n", " plt.rcParams.update(\n", " {\n", " \"figure.facecolor\": NAVY,\n", " \"axes.facecolor\": NAVY,\n", " \"savefig.facecolor\": NAVY,\n", " \"text.color\": \"#e9eef8\",\n", " \"axes.labelcolor\": \"#afc1dc\",\n", " \"xtick.color\": \"#afc1dc\",\n", " \"ytick.color\": \"#afc1dc\",\n", " \"axes.edgecolor\": \"#34486b\",\n", " \"grid.color\": \"#34486b\",\n", " \"font.size\": 10,\n", " \"axes.prop_cycle\": plt.cycler(color=[BLUE, PINK, \"#ba9dea\"]),\n", " }\n", " )\n", " fig, axes = plt.subplots(panels, 1, figsize=(9, 6.1), squeeze=False, layout=\"constrained\")\n", " fig.suptitle(fill(title, width=58), fontsize=17, x=0.04, ha=\"left\", weight=\"medium\")\n", " for ax in axes.flat:\n", " ax.grid(axis=\"y\", alpha=0.45)\n", " ax.set_axisbelow(True)\n", " ax.spines[[\"top\", \"right\"]].set_visible(False)\n", " axes.flat[0].set_ylabel(ylabel)\n", " return fig, list(axes.flat)\n", "\n", "\n", "def legend(ax):\n", " ax.legend(frameon=False, labelcolor=\"#e9eef8\", fontsize=9)\n", "\n", "\n", "def run():\n", " from quantjourney.sdk import QuantJourneyAPI\n", "\n", " qj = QuantJourneyAPI.from_env()\n", " raw = fetch(qj)\n", " table, fig = analyze(raw)\n", " print(table.to_string(index=False))\n", " fig.savefig(OUTPUT, dpi=180)\n", " plt.show()\n", "\n", "\n", "def bls_cpi(response):\n", " return macro(response, \"cpi_all\")\n", "\n", "\n", "OUTPUT = \"sdk_bank_cycle.png\"\n", "\n", "if __name__ == \"__main__\":\n", " run()\n" ] } ], "metadata": { "kernelspec": { "display_name": "Python 3", "language": "python", "name": "python3" }, "language_info": { "name": "python" } }, "nbformat": 4, "nbformat_minor": 5 }