import pandas as pd
from IPython.display import Markdown
from tulip.data.haver import (
    get_series as get_haver_series,
    get_collection as get_haver_collections,
)
from tulip.plots import plot_lines, add_line_on_opposite_axis
import plotly.graph_objects as go
from plotly.subplots import make_subplots
from tulip_mania.columns import columns

# ------- Functions ---------------


def style_cc_plot(fig):
    # Base colors for the main HH / NFC / Gov series
    base_colors = {
        "HH": "#1f77b4",  # blue
        "NFC": "#2ca02c",  # green
        "Gov": "#d62728",  # red
    }

    def lighten_hex(hex_color, factor=0.4):
        """
        Lighten a #RRGGBB color by mixing it with white.
        factor in (0,1): 0 = no change, 1 = white.
        """
        hex_color = hex_color.lstrip("#")
        r = int(hex_color[0:2], 16)
        g = int(hex_color[2:4], 16)
        b = int(hex_color[4:6], 16)
        r_l = int(r + (255 - r) * factor)
        g_l = int(g + (255 - g) * factor)
        b_l = int(b + (255 - b) * factor)
        return f"#{r_l:02x}{g_l:02x}{b_l:02x}"

    # Apply colors based on trace name
    for tr in fig.data:
        name = tr.name or ""

        # Decide which block: HH, NFC, Gov
        if name.startswith("HH"):
            base = base_colors["HH"]
        elif name.startswith("NFC"):
            base = base_colors["NFC"]
        elif name.startswith("Gov"):
            base = base_colors["Gov"]
        else:
            continue  # leave anything else unchanged

        # "o/w" traces get a lighter shade of the same color
        is_sub = "o/w" in name
        color = base if not is_sub else lighten_hex(base, factor=0.5)

        # Set both line and marker colors if present (covers Bar/Scatter/etc.)
        if hasattr(tr, "marker"):
            tr.marker.color = color
        if hasattr(tr, "line"):
            tr.line.color = color

    return fig

Other Countries Credit Creation Monitor¶

United Kingdom¶

# Net Lending/Borrowing
uk_net_lending = {
    "HH": "NSSZQ@UK",
    "NFC": "DTALQ@UK",  # NFC
    "PC": "CPCMQ@UK",  # Public Corporations, Gov controlled, more than 50% of expenses to non-gov, independent gov. E.g. Channel 4
    "FC": "NHCQQ@UK",  # Financial Corporations
    "CGov": "NMFJQ@UK",  # Central Government
    "LGov": "NMOEQ@UK",  # Local Government
}

uk_net_lending_collection = get_haver_collections(uk_net_lending)

uk_gdp = get_haver_series("S112NGDP@G10").ts
uk_gdp_12m = uk_gdp.rolling(4).sum()
uk_net_lending_collection_df = uk_net_lending_collection.df.rolling(4).sum()
uk_net_lending_collection_df.columns = list(uk_net_lending.keys())
uk_net_lending_collection_df = uk_net_lending_collection_df.div(uk_gdp_12m, axis=0)

uk_flows_df = pd.DataFrame(
    {
        "Households & NPISH": uk_net_lending_collection_df["HH"].round(2),
        "Financial Corporations": uk_net_lending_collection_df[["NFC", "PC"]].sum(
            axis=1
        ),
        "General Government": uk_net_lending_collection_df[["CGov", "LGov"]].sum(
            axis=1
        ),
    }
)

plot_lines(
    uk_flows_df,
    years_limit=10,
    show_0=True,
    title="<b>UK Net Lending/Borrowing by Sector</b> (% of GDP, Trailing 12M)",
    tick_format="0.0%",
)

Notes for Sasha to finish.

Make sure you are excluding financial corporations. When we mention “companies” we refer to non-banks. Banks typically hold the loan given to a company so they net out.
Try finding in haver less lagged, more frequent data points. The central banks should give some informtion by aggregating the information they get from supervised banks.
Try to find for each country the average mortgage rate, and the average cost of the loan/debt

Germany¶

# Germany section starts below

# Germany Credit Creation Data
# Sources: Deutsche Bundesbank via Haver GERMANY database
#
# Haver Codes:
#   HH Consumer:  DENCNLH8@GERMANY - New loans to households for consumption (flow)
#   HH Housing:   DENCNLH9@GERMANY - New loans to households for house purchase (flow)
#   NFC:          DENFNLN@GERMANY  - New loans to non-financial corporations (flow)
#   Gov:          DENFCE1@GERMANY  - MFI lending to domestic general government (stock)
#                 Path: Financial > Money, Banking and Credit > Lending to Nonbanks > Domestic General Government

de_gdp = get_haver_series("S134NGDP@G10").ts
de_consumer_flow = get_haver_series("DENCNLH8@GERMANY").ts
de_housing_flow = get_haver_series("DENCNLH9@GERMANY").ts
de_nfc_flow = get_haver_series("DENFNLN@GERMANY").ts
de_gov_stock = get_haver_series("DENFCE1@GERMANY").ts  # Stock - needs .diff()

# Monthly - aggregate to quarter-end (QE) to match GDP frequency
de_consumer_q = de_consumer_flow.resample("QE").sum()
de_housing_q = de_housing_flow.resample("QE").sum()
de_nfc_q = de_nfc_flow.resample("QE").sum()

# Government is a STOCK series - convert to flow with diff()
de_gov_q = de_gov_stock.resample("QE").last()
de_gov_flow_q = de_gov_q.diff()

# Calculate trailing 12M (4 quarters)
de_consumer_12m = de_consumer_q.rolling(4).sum()
de_housing_12m = de_housing_q.rolling(4).sum()
de_nfc_12m = de_nfc_q.rolling(4).sum()
de_gov_12m = de_gov_flow_q.rolling(4).sum()
de_gdp_12m = de_gdp.rolling(4).sum()

# Align and normalize (Mil.EUR loans / Bil.EUR GDP -> divide by 1000)
aligned_de_consumer, aligned_de_gdp = de_consumer_12m.align(de_gdp_12m, join="inner")
aligned_de_housing, _ = de_housing_12m.align(de_gdp_12m, join="inner")
aligned_de_nfc, _ = de_nfc_12m.align(de_gdp_12m, join="inner")
aligned_de_gov, _ = de_gov_12m.align(de_gdp_12m, join="inner")

de_consumer_pct_gdp = (aligned_de_consumer / 1000 / aligned_de_gdp) * 100
de_housing_pct_gdp = (aligned_de_housing / 1000 / aligned_de_gdp) * 100
de_nfc_pct_gdp = (aligned_de_nfc / 1000 / aligned_de_gdp) * 100
de_gov_pct_gdp = (aligned_de_gov / 1000 / aligned_de_gdp) * 100

de_hh_df = pd.DataFrame(
    {
        "HH Consumer Loans": de_consumer_pct_gdp.round(2),
        "HH Housing Loans": de_housing_pct_gdp.round(2),
    }
)

de_nfc_df = pd.DataFrame(
    {
        "NFC Loans": de_nfc_pct_gdp.round(2),
    }
)

de_gov_df = pd.DataFrame(
    {
        "Gov MFI Lending": de_gov_pct_gdp.round(2),
    }
)

# Create 3-panel Germany chart like US
de_sub_fig = {}

# Panel 1: Households
fig_hh = plot_lines(
    de_hh_df,
    years_limit=10,
    default_y_range=(-2, 6),
    figsize=(400, 600),
    top_margin=170,
    tick_suffix="%",
    show_0=True,
)
style_cc_plot(fig_hh)
de_sub_fig["HH"] = fig_hh

# Panel 2: Non-Financial Corporations
fig_nfc = plot_lines(
    de_nfc_df,
    years_limit=10,
    default_y_range=(-2, 10),
    figsize=(400, 600),
    top_margin=170,
    tick_suffix="%",
    show_0=True,
)
style_cc_plot(fig_nfc)
de_sub_fig["NFC"] = fig_nfc

# Panel 3: Government
fig_gov = plot_lines(
    de_gov_df,
    years_limit=10,
    default_y_range=(-4, 4),
    figsize=(400, 600),
    top_margin=170,
    tick_suffix="%",
    show_0=True,
)
style_cc_plot(fig_gov)
de_sub_fig["Gov"] = fig_gov

# Display 3 panels side-by-side
with columns(3, gap="2px", vertical_alignment="bottom") as cols:
    cols[0].plot(de_sub_fig["HH"])
    cols[1].plot(de_sub_fig["NFC"])
    cols[2].plot(de_sub_fig["Gov"])

Eurozone¶

# Eurozone Credit Creation Data
# Sources: European Central Bank via Haver EUDATA and EUFIN databases
#
# Haver Codes:
#   HH Total:     I023LHT@EUDATA  - MFI loans to households, total (flow, transactions)
#   HH Housing:   I023LHLT@EUDATA - MFI loans to households for house purchase (flow)
#   NFC:          I023LNFT@EUDATA - MFI loans to non-financial corporations (flow)
#   Gov:          U023SLEG@EUFIN  - MFI stock of loans to Euro Area general government (stock)
#                 Path: Financial Data (EUFIN) > MFI Loans > Aggregated Balance Sheets >
#                       Outstanding Amounts > Euro Area > MFIs > General Government

ez_gdp_raw = get_haver_series("S025NGDP@G10").ts
ez_gdp = ez_gdp_raw / 1000  # Convert Mil.EUR to Bil.EUR

# Household loan flows (transactions, Bil.EUR)
ez_hh_loans = get_haver_series("I023LHT@EUDATA").ts
ez_housing_loans = get_haver_series("I023LHLT@EUDATA").ts

# NFC loan flows (transactions, Bil.EUR)
ez_nfc_loans = get_haver_series("I023LNFT@EUDATA").ts

# Government - MFI Stock of Loans (Mil.EUR, need to convert)
ez_gov_stock = get_haver_series("U023SLEG@EUFIN").ts / 1000  # Stock - needs .diff()

# Interest rates
ez_rate_hh = get_haver_series("O023CBH@EUDATA").ts.rename("HH Cost of Borrowing")
ez_rate_short = get_haver_series("O023CBS@EUDATA").ts.rename("Short-Term Rate")
ez_rate_long = get_haver_series("O023CBL@EUDATA").ts.rename("Long-Term Rate")

# Monthly - aggregate to quarter-end (QE)
ez_hh_q = ez_hh_loans.resample("QE").sum()
ez_housing_q = ez_housing_loans.resample("QE").sum()
ez_nfc_q = ez_nfc_loans.resample("QE").sum()

# Government is a STOCK series - convert to flow with diff()
ez_gov_q = ez_gov_stock.resample("QE").last()
ez_gov_flow_q = ez_gov_q.diff()

# Calculate trailing 12M (4 quarters)
ez_hh_12m = ez_hh_q.rolling(4).sum()
ez_housing_12m = ez_housing_q.rolling(4).sum()
ez_nfc_12m = ez_nfc_q.rolling(4).sum()
ez_gov_12m = ez_gov_flow_q.rolling(4).sum()
ez_gdp_12m = ez_gdp.rolling(4).sum()

# Align (all now in Bil.EUR)
aligned_ez_hh, aligned_ez_gdp = ez_hh_12m.align(ez_gdp_12m, join="inner")
aligned_ez_housing, _ = ez_housing_12m.align(ez_gdp_12m, join="inner")
aligned_ez_nfc, _ = ez_nfc_12m.align(ez_gdp_12m, join="inner")
aligned_ez_gov, _ = ez_gov_12m.align(ez_gdp_12m, join="inner")

# Convert to % of GDP
ez_hh_pct_gdp = (aligned_ez_hh / aligned_ez_gdp) * 100
ez_housing_pct_gdp = (aligned_ez_housing / aligned_ez_gdp) * 100
ez_nfc_pct_gdp = (aligned_ez_nfc / aligned_ez_gdp) * 100
ez_gov_pct_gdp = (aligned_ez_gov / aligned_ez_gdp) * 100

ez_hh_df = pd.DataFrame(
    {
        "HH Total Loans": ez_hh_pct_gdp.round(2),
        "HH o/w House Purchase": ez_housing_pct_gdp.round(2),
    }
)

ez_nfc_df = pd.DataFrame(
    {
        "NFC Loans": ez_nfc_pct_gdp.round(2),
    }
)

ez_gov_df = pd.DataFrame(
    {
        "Gov MFI Lending": ez_gov_pct_gdp.round(2),
    }
)

# Create 3-panel Eurozone chart like US
ez_sub_fig = {}

# Panel 1: Households
fig_hh = plot_lines(
    ez_hh_df,
    years_limit=10,
    default_y_range=(-2, 6),
    figsize=(400, 600),
    top_margin=170,
    tick_suffix="%",
    show_0=True,
)
style_cc_plot(fig_hh)

# Add HH interest rate on opposite axis
ez_sub_fig["HH"] = add_line_on_opposite_axis(
    fig_hh,
    ez_rate_hh,
    years_limit=10,
    invert=True,
    color="black",
    tick_suffix={"y1": "%", "y2": "%"},
)

# Panel 2: Non-Financial Corporations
fig_nfc = plot_lines(
    ez_nfc_df,
    years_limit=10,
    default_y_range=(-2, 6),
    figsize=(400, 600),
    top_margin=170,
    tick_suffix="%",
    show_0=True,
)
style_cc_plot(fig_nfc)

# Add Long-Term rate on opposite axis (more relevant for NFC)
ez_sub_fig["NFC"] = add_line_on_opposite_axis(
    fig_nfc,
    ez_rate_long,
    years_limit=10,
    invert=True,
    color="black",
    tick_suffix={"y1": "%", "y2": "%"},
)

# Panel 3: Government
fig_gov = plot_lines(
    ez_gov_df,
    years_limit=10,
    default_y_range=(-4, 4),
    figsize=(400, 600),
    top_margin=170,
    tick_suffix="%",
    show_0=True,
)
style_cc_plot(fig_gov)
ez_sub_fig["Gov"] = fig_gov

# Display 3 panels side-by-side using columns helper
with columns(3, gap="2px", vertical_alignment="bottom") as cols:
    cols[0].plot(ez_sub_fig["HH"])
    cols[1].plot(ez_sub_fig["NFC"])
    cols[2].plot(ez_sub_fig["Gov"])

Australia¶

# Australia Credit Creation Data
# Sources: Reserve Bank of Australia via Haver ANZ database
#
# Haver Codes:
#   HH Dwelling:     AUSFHMX@ANZ - Housing finance commitments, dwelling (flow)
#   HH Construction: AUNFBLC@ANZ - Housing finance, construction loans (flow)
#   NFC:             AUSFCB@ANZ  - Credit to business/non-financial (stock)
#   Gov:             AUNFCG@ANZ  - Lending to government (stock)
#                    Path: Australia > Financial > Money, Banking and Credit > Bank Lending >
#                          Bank Lending by Sector (RBA Table D5) > Lending to government

au_gdp = get_haver_series("S193NGDP@G10").ts

# Household lending (Housing section)
au_dwelling = get_haver_series("AUSFHMX@ANZ").ts
au_construction = get_haver_series("AUNFBLC@ANZ").ts

# Business/NFC lending - RBA Table D2 Credit Aggregates
try:
    au_nfc_credit = get_haver_series("AUSFCB@ANZ").ts  # Stock - needs .diff()
except:
    au_nfc_credit = get_haver_series("AUSFPPF@ANZ").ts

# Government lending - RBA Table D5 Bank Lending by Sector
au_gov_stock = get_haver_series("AUNFCG@ANZ").ts  # Stock - needs .diff()

# Interest rate - RBA Cash Rate Target
try:
    au_rate = get_haver_series("AURBAT@ANZ").ts.rename("RBA Cash Rate")
except:
    try:
        au_rate = get_haver_series("AURBA@ANZ").ts.rename("RBA Cash Rate")
    except:
        au_rate = None

# Resample to quarter-end (QE)
au_dwelling_q = au_dwelling.resample("QE").sum()
au_construction_q = au_construction.resample("QE").sum()

# NFC and Gov are STOCK series - convert to flow
au_nfc_q = au_nfc_credit.resample("QE").last()
au_nfc_flow_q = au_nfc_q.diff()

au_gov_q = au_gov_stock.resample("QE").last()
au_gov_flow_q = au_gov_q.diff()

# Calculate trailing 12M (4 quarters)
au_dwelling_12m = au_dwelling_q.rolling(4).sum()
au_construction_12m = au_construction_q.rolling(4).sum()
au_nfc_12m = au_nfc_flow_q.rolling(4).sum()
au_gov_12m = au_gov_flow_q.rolling(4).sum()
au_gdp_12m = au_gdp.rolling(4).sum()

# Align and normalize (all in Bil.A$)
aligned_au_dwelling, aligned_au_gdp = au_dwelling_12m.align(au_gdp_12m, join="inner")
aligned_au_construction, _ = au_construction_12m.align(au_gdp_12m, join="inner")
aligned_au_nfc, _ = au_nfc_12m.align(au_gdp_12m, join="inner")
aligned_au_gov, _ = au_gov_12m.align(au_gdp_12m, join="inner")

au_dwelling_pct_gdp = (aligned_au_dwelling / aligned_au_gdp) * 100
au_construction_pct_gdp = (aligned_au_construction / aligned_au_gdp) * 100
au_nfc_pct_gdp = (aligned_au_nfc / aligned_au_gdp) * 100
au_gov_pct_gdp = (aligned_au_gov / aligned_au_gdp) * 100

au_hh_df = pd.DataFrame(
    {
        "HH Dwelling": au_dwelling_pct_gdp.round(2),
        "HH Construction": au_construction_pct_gdp.round(2),
    }
)

au_nfc_df = pd.DataFrame(
    {
        "NFC Credit": au_nfc_pct_gdp.round(2),
    }
)

au_gov_df = pd.DataFrame(
    {
        "Gov Bank Lending": au_gov_pct_gdp.round(2),
    }
)

# Create 3-panel Australia chart like US
au_sub_fig = {}

# Panel 1: Households
fig_hh = plot_lines(
    au_hh_df,
    years_limit=10,
    default_y_range=(-2, 6),
    figsize=(400, 600),
    top_margin=170,
    tick_suffix="%",
    show_0=True,
)
style_cc_plot(fig_hh)

# Add rate overlay if available
if au_rate is not None:
    au_sub_fig["HH"] = add_line_on_opposite_axis(
        fig_hh,
        au_rate,
        years_limit=10,
        invert=True,
        color="black",
        tick_suffix={"y1": "%", "y2": "%"},
    )
else:
    au_sub_fig["HH"] = fig_hh

# Panel 2: Non-Financial Corporations
fig_nfc = plot_lines(
    au_nfc_df,
    years_limit=10,
    default_y_range=(-2, 6),
    figsize=(400, 600),
    top_margin=170,
    tick_suffix="%",
    show_0=True,
)
style_cc_plot(fig_nfc)
au_sub_fig["NFC"] = fig_nfc

# Panel 3: Government
fig_gov = plot_lines(
    au_gov_df,
    years_limit=10,
    default_y_range=(-2, 4),
    figsize=(400, 600),
    top_margin=170,
    tick_suffix="%",
    show_0=True,
)
style_cc_plot(fig_gov)
au_sub_fig["Gov"] = fig_gov

# Display 3 panels side-by-side
with columns(3, gap="2px", vertical_alignment="bottom") as cols:
    cols[0].plot(au_sub_fig["HH"])
    cols[1].plot(au_sub_fig["NFC"])
    cols[2].plot(au_sub_fig["Gov"])

Canada¶

# Canada Credit Creation Data
# Sources: Statistics Canada / Bank of Canada via Haver CANADA database
#
# Haver Codes:
#   HH Total:    V1R36700@CANADA  - Household credit, total (stock)
#   HH Consumer: V1R36703@CANADA  - Consumer credit (stock)
#   NFC:         V122647@CANADA   - Business credit (stock, SA)
#                Path: Canada > Financial > Credit Measures > Business & Household Credit > Business credit
#   Gov:         V37767@CANADA    - Chartered Banks Nonmortgage Loans to Prov/Municipal Gov (stock, quarterly)
#                Path: Canada > Financial > Chartered Banks Assets and Liabilities >
#                      Nonmortgage Loans (End of Period) > Provincial and municipal governments

ca_gdp = get_haver_series("S156NGDP@G10").ts
ca_hh_stock = get_haver_series("V1R36700@CANADA").ts
ca_consumer_stock = get_haver_series("V1R36703@CANADA").ts

# NFC - Business Credit
ca_nfc_stock = get_haver_series("V122647@CANADA").ts  # Stock - needs .diff()

# Government - Chartered Banks Nonmortgage Loans to Prov/Municipal Gov
ca_gov_stock = get_haver_series("V37767@CANADA").ts  # Stock, quarterly - needs .diff()

# Convert STOCK to FLOW
ca_hh_flow = ca_hh_stock.diff()
ca_consumer_flow = ca_consumer_stock.diff()
ca_nfc_flow = ca_nfc_stock.diff()
ca_gov_flow = ca_gov_stock.diff()

# Resample to quarter-end (QE)
ca_hh_q = ca_hh_flow.resample("QE").sum()
ca_consumer_q = ca_consumer_flow.resample("QE").sum()
ca_nfc_q = ca_nfc_flow.resample("QE").sum()
ca_gov_q = ca_gov_flow.resample("QE").last()  # Already quarterly

# Calculate trailing 12M (4 quarters)
ca_hh_12m = ca_hh_q.rolling(4).sum()
ca_consumer_12m = ca_consumer_q.rolling(4).sum()
ca_nfc_12m = ca_nfc_q.rolling(4).sum()
ca_gov_12m = ca_gov_q.rolling(4).sum()
ca_gdp_12m = ca_gdp.rolling(4).sum()

# Align and normalize
aligned_ca_hh, aligned_ca_gdp = ca_hh_12m.align(ca_gdp_12m, join="inner")
aligned_ca_consumer, _ = ca_consumer_12m.align(ca_gdp_12m, join="inner")
aligned_ca_nfc, _ = ca_nfc_12m.align(ca_gdp_12m, join="inner")
aligned_ca_gov, _ = ca_gov_12m.align(ca_gdp_12m, join="inner")

ca_hh_pct_gdp = (aligned_ca_hh / aligned_ca_gdp) * 100
ca_consumer_pct_gdp = (aligned_ca_consumer / aligned_ca_gdp) * 100
ca_nfc_pct_gdp = (aligned_ca_nfc / aligned_ca_gdp) * 100
ca_gov_pct_gdp = (aligned_ca_gov / aligned_ca_gdp) * 100

ca_hh_df = pd.DataFrame(
    {
        "HH Total Credit": ca_hh_pct_gdp.round(2),
        "HH o/w Consumer": ca_consumer_pct_gdp.round(2),
    }
)

ca_nfc_df = pd.DataFrame(
    {
        "NFC Business Credit": ca_nfc_pct_gdp.round(2),
    }
)

ca_gov_df = pd.DataFrame(
    {
        "Gov Bank Lending": ca_gov_pct_gdp.round(2),
    }
)

# Create 3-panel Canada chart like US
ca_sub_fig = {}

# Panel 1: Households
fig_hh = plot_lines(
    ca_hh_df,
    years_limit=10,
    default_y_range=(-2, 6),
    figsize=(400, 600),
    top_margin=170,
    tick_suffix="%",
    show_0=True,
)
style_cc_plot(fig_hh)
ca_sub_fig["HH"] = fig_hh

# Panel 2: Non-Financial Corporations
fig_nfc = plot_lines(
    ca_nfc_df,
    years_limit=10,
    default_y_range=(-4, 8),
    figsize=(400, 600),
    top_margin=170,
    tick_suffix="%",
    show_0=True,
)
style_cc_plot(fig_nfc)
ca_sub_fig["NFC"] = fig_nfc

# Panel 3: Government
fig_gov = plot_lines(
    ca_gov_df,
    years_limit=10,
    default_y_range=(-1, 2),
    figsize=(400, 600),
    top_margin=170,
    tick_suffix="%",
    show_0=True,
)
style_cc_plot(fig_gov)
ca_sub_fig["Gov"] = fig_gov

# Display 3 panels side-by-side
with columns(3, gap="2px", vertical_alignment="bottom") as cols:
    cols[0].plot(ca_sub_fig["HH"])
    cols[1].plot(ca_sub_fig["NFC"])
    cols[2].plot(ca_sub_fig["Gov"])

from tulip_mania.notebook_related import notebook_updated

notebook_updated()