Credit-deposit ratios for Delhi, Haryana and UP: sanction vs use

Where to get the tables

On the RBI website, open Publications, then Annual, then Handbook of Statistics on Indian States. The 2024-25 edition was released on 11 December 2025. The banking section contains:

The direct file links sit behind a browser check, so a plain script request may receive an HTML page instead of a workbook. Download through a browser and confirm the file opens before you parse it. Each workbook has two sheets, T_153(i) covering 2004 to 2014 and T_153(ii) covering 2015 to 2025. The figures are per cent, as at end-March. The footer cites RBI's Basic Statistical Returns of Scheduled Commercial Banks as the source.

Why a hiring manager would look twice

Banks, NBFCs, rating agencies and state finance departments all use the CD ratio as a first read of credit demand and financial inclusion. The analytical point, that one quantity can be allocated to geography in two defensible ways, applies well beyond banking. Sales can be booked by billing address or delivery address, and revenue by head office or branch. A candidate who can show both and explain the gap is doing real analysis rather than copying a figure.

What makes the workbook awkward

• Regions and states share one column. "NORTHERN REGION" sits above Chandigarh, Delhi and Haryana. Tag region rows before any ranking, or a region will be ranked as a state.
• Header year types. The final year header reads as 2025.0 while the others are integers. Cast every year column to int.
• Title, unit and footnote rows. Keep rows between the header and "ALL INDIA", and drop the notes below.
• A dash means nil, not applicable or negligible, according to the table's own footnote. Treat it as missing, not as zero.
• Boundary changes. Dadra & Nagar Haveli includes Daman & Diu from 2020, and Ladakh appears as its own row. Long-run comparisons for these rows need a note.
• Two sheets per table. Merge on the state name after stripping trailing spaces and footnote asterisks.

Code: reshape, join and compare on synthetic rows

The rows below are synthetic. They copy the workbook's layout (region rows, a float year header, a dash) but the ratios are invented and are not RBI figures.

import numpy as np
import pandas as pd

def fake_sheet(values):
    """Imitates one RBI sheet: a label column, then one column per year."""
    cols = ["Region/State/Union Territory", 2023, 2024, 2025.0]
    return pd.DataFrame(values, columns=cols)

sanction = fake_sheet([
    ["NORTHERN REGION", 70.0, 72.0, 73.0],
    ["State A", 88.0, 86.5, 87.0],
    ["State B", 52.0, 57.0, 59.5],
    ["Territory C", "-", 40.0, 41.0],
    ["CENTRAL REGION", 55.0, 57.0, 58.0],
    ["State D", 44.0, 46.0, 47.5],
    ["ALL INDIA", 74.0, 76.0, 77.0],
])
utilisation = fake_sheet([
    ["NORTHERN REGION", 72.0, 74.5, 75.0],
    ["State A", 85.0, 83.0, 82.5],
    ["State B", 61.0, 66.0, 69.0],
    ["Territory C", "-", 44.0, 45.5],
    ["CENTRAL REGION", 57.0, 59.5, 60.0],
    ["State D", 47.0, 49.5, 50.5],
    ["ALL INDIA", 74.0, 76.0, 77.0],
])

def tidy(df, basis):
    df = df.rename(columns={"Region/State/Union Territory": "unit"})
    df.columns = ["unit"] + [int(c) for c in df.columns[1:]]
    long = df.melt(id_vars="unit", var_name="year", value_name="cd_ratio")
    long["cd_ratio"] = pd.to_numeric(long["cd_ratio"].replace("-", np.nan))
    long["level"] = np.select([long["unit"].str.endswith("REGION"), long["unit"] == "ALL INDIA"],
                              ["region", "nation"], default="state")
    return long.assign(basis=basis)

panel = pd.concat([tidy(sanction, "sanction"), tidy(utilisation, "utilisation")])
wide = panel.pivot(index=["unit", "level", "year"], columns="basis", values="cd_ratio").reset_index()
wide["gap_pp"] = (wide["utilisation"] - wide["sanction"]).round(1)

nation = wide[wide["level"] == "nation"]
assert (nation["gap_pp"] == 0).all(), "all-India should match on both bases"

latest = wide[(wide["level"] == "state") & (wide["year"] == 2025)].sort_values("gap_pp")
print(latest[["unit", "sanction", "utilisation", "gap_pp"]].to_string(index=False))
print(wide[wide["unit"] == "Territory C"][["year", "sanction", "utilisation"]].to_string(index=False))

A positive gap means more credit is used in the state than its own branches sanction, so loans booked elsewhere are flowing in. A negative gap means the state's branches sanction loans that are spent outside it. The assertion on the national row is the cheapest test you have: if it fails on the real data, your join or your cleaning is wrong. Territory C keeps its missing 2023 value instead of showing a false zero.

Reading the real gaps carefully

On the real 2025 figures, Haryana's utilisation ratio is 7.8 percentage points above its sanction ratio. In March 2020 the gap was 10 points (54.8% against 64.8%). A plausible reading is that some credit used in Haryana, around Gurugram and Faridabad for example, is sanctioned by branches in Delhi or elsewhere. Treat that as a hypothesis. The table does not say which states the loans come from, and nothing in it identifies borrowers or sectors. Delhi's utilisation ratio was above its sanction ratio in 2024 and below it in 2025, so a single year's sign is not a pattern.

Analysis plan

1. 1Download both workbooks, confirm they are real XLSX files, and record the edition and release date.
2. 2Tidy the four sheets into one long panel with unit, level, year, basis and ratio, and assert the all-India match.
3. 3Chart 2004 to 2025 on both bases for Delhi, Haryana, UP and all-India.
4. 4Rank every state by the 2025 gap, and flag those whose sign changed in the last five years.
5. 5Add Tables 155 and 156 to show whether a moving ratio came from credit, deposits or both.

Deliverables

• Line chart: four panels, each with sanction and utilisation lines for one unit.
• Gap table: state, both 2025 ratios, gap, and gap in 2020.
• README: edition and table numbers, how the browser-check download was handled, the dash rule, boundary notes, and the hypothesis label on any explanation of Haryana's gap.

Interview questions

• Why must the all-India ratio be the same in both tables?
• Haryana's utilisation ratio is higher than its sanction ratio. What would you need to confirm where the extra credit is sanctioned?
• A CD ratio rises. Name two different causes and how you would separate them.
• How would you handle a state that is split or merged partway through your series?

Turning wide government tables into tidy panels is a recurring exercise in the 3–4 month Data Analyst programme, taught live online with optional in-person labs at our Noida centre. For another RBI source, try the card payments project.