DEV Community: sanjay mehta

I Built a Sector Rotation Map in Python. A Shuffle Test Humbled It.

sanjay mehta — Sat, 31 Oct 2026 05:45:26 +0000

This week I shipped a sector rotation map for Indian stocks. It plots NSE sector and thematic indices against the Nifty 500 and sorts them into four corners: Leading, Weakening, Lagging and Improving.

Then the obvious question hit me: does it actually tell you anything? If you buy whatever is Leading, do you beat the market? And do sectors really travel clockwise around the map, the way every explainer says?

So I rebuilt the whole thing in Python, ran it over ten years of weekly data, and checked. This post is the build: the maths, the code, two bugs, and a shuffle test that took my favourite finding away. The story version, with more charts, is on Medium (linked at the end).

Not investment advice. I built the data platform and the map used here. Data runs from July 2016 to 23 October 2026.

The maths

Every week, for every sector:

rs    = 100 * sector / nifty_500                  # relative strength
ratio = 100 * wma(rs, 5) / wma(rs, 15)             # has it been gaining ground lately?
mom   = 100 * ratio / ratio.shift(5)               # is that speeding up or fading?

wma is a weighted moving average, where the newest week counts most. Dividing the 5-week average by the 15-week one gives a number around 100. Above 100 means relative strength has been rising recently, so the sector has been outperforming the Nifty 500 over the last few weeks. The momentum line compares today's ratio with five weeks ago. Above 100, that outperformance is accelerating.

Two numbers, two thresholds at 100, four corners:

q = np.where(ratio >= 100, np.where(mom >= 100, "Leading", "Weakening"),
             np.where(mom >= 100, "Improving", "Lagging"))

The formula was the easy part. Every mistake I made was in the data handling.

Getting the data

I pulled daily levels for 21 indices and the Nifty 500 from the data API I built. Responses are paginated, so a small helper follows meta.next_cursor until has_more is false. The from date is 2015, so the 15-week averages have time to warm up before the test starts in July 2016.

Weekly bars, finished weeks only

iso = daily.index.isocalendar()
weekly = daily.groupby([iso.year, iso.week]).last()
weekly.index = [pd.Timestamp.fromisocalendar(y, w, 5) for y, w in weekly.index]
if weekly.index[-1] >= pd.Timestamp.today().normalize():
    weekly = weekly.iloc[:-1]  # this week hasn't finished yet

Grouping by ISO week and taking the last close gives one bar per week, holiday weeks included. Each week is labelled with its Friday, and if that Friday hasn't passed, the week is dropped. Run it on a Wednesday without that check and you're treating three days as a full week. My first version dropped the last week unconditionally, which quietly threw away a finished week whenever I ran it on a weekend.

The live map had a cousin of this bug. In weekly mode, a freshness check that looked at the wrong date decided, in the middle of the week, that every sector was stale, and the map came up empty. The fix was to compare each sector's last session with the map's as-of date.

The bug that almost fooled me

My first version built the daily table like this:

daily = pd.concat(series, axis=1).sort_index().ffill()

Forward-filling looks harmless. Holidays differ a little between indices, so why not carry the last price forward?

Because three of the indices (Healthcare, Consumer Durables, Oil & Gas) have a stray data point in mid-2016 and no continuous data until 2020. ffill() took that one price and copied it forward for years. Healthcare got about four years of invented, perfectly flat history.

Nothing errored, but the answer flipped. With the fill, Improving sectors beat the Nifty 500 over the next quarter by 0.30% on average. Without it, they lagged by 0.36%.

I caught it only because an earlier prototype built the weeks differently and the two disagreed. The fix was to delete .ffill():

daily = pd.concat(series, axis=1).sort_index()

GroupBy.last() already skips missing values inside a week, so holidays are fine, and a week with no data at all stays empty. The rolling averages see the gap, return NaN, and drop out instead of inventing a trend.

A second, smaller one, which a reviewer caught: I first computed the 13-week forward return after dropping the empty rows. Across a gap, shift(-13) then jumps 13 rows, not 13 weeks. The forward return is now computed on the full weekly series, before anything is dropped.

Checking it against the live map

Before trusting the backtest, I printed the latest week and compared it with the live page. All 21 indices matched the live page to two decimals on both ratio and momentum. IT: ratio 104.76, momentum 104.49. Pharma: 101.35 and 99.30. MNC: 97.33 and 98.19.

Result one: Leading doesn't predict much

For every weekly reading from July 2016, I recorded the corner and whether the sector beat the Nifty 500 over the next 13 weeks:

           weeks     avg beat_benchmark
quadrant
Improving   1707  -0.36%          47.9%
Lagging     3620  -0.28%          47.0%
Leading     3443  +0.47%          50.1%
Weakening   1616  +0.21%          47.8%

Any sector-week beat the benchmark 48.3% of the time, so Leading is about two points better. But the 13-week windows overlap, which makes 10,386 readings worth far fewer independent ones. When I kept only non-overlapping quarters, Weakening (+1.14%, 132 readings) came out ahead of Leading (+0.26%, 253). That flip looks like noise, so I wouldn't treat either number as an edge. These are also price indices, so dividends are left out. That slightly understates sectors that yield more than the Nifty 500.

Result two: the shuffle test

The clockwise rotation looked like the real finding. 67.0% of the 2,233 corner changes went clockwise.

Then I asked what random data would do. For each sector, I took the longest unbroken stretch of its real weekly moves, shuffled their order, rebuilt a relative-strength path, and ran the exact same map on it, 30 times:

s = rs.dropna()
run = (s.index.to_series().diff() > pd.Timedelta(weeks=2)).cumsum()
s = s[run == run.value_counts().idxmax()]  # longest unbroken stretch only
moves = np.log(s).diff().dropna().to_numpy()
path = np.exp(np.log(s.iloc[0]) + np.concatenate([[0], np.cumsum(rng.permutation(moves))]))
fake.append(readings(pd.Series(path, index=s.index))[lambda d: d.index >= "2016-07-01"])

real sectors: 2233 quadrant changes, 67.0% clockwise
shuffled data: 66.2% clockwise (range 63.6% to 68.0%)

The shuffled data rotates clockwise just as reliably: real sectors, at 67.0%, sit inside the shuffled range. Even the median time in Leading is the same: six weeks for real sectors, five to six for shuffled ones.

The reason is in the maths. The momentum axis is roughly the rate of change of the ratio axis. When momentum is above 100, the ratio is rising, so the dot moves right; below 100 it moves left. Right along the top and left along the bottom is clockwise, for any wiggly series, like a pendulum's position against its speed. And because the ratio compares a short and a long average of the same series, it swings around 100 for any input, so those loops circle the centre of the map. The rotation comes from the indicator itself. It's still a nice way to see a sector's recent path. It just isn't evidence of a cycle you can trade.

The full script

Set TAPETIDE_API_TOKEN and run it. It prints the latest map, the results table, the clockwise count and the shuffle test.

import os
import numpy as np
import pandas as pd
import requests

API = "https://api.tapetide.com/v1"
HEADERS = {"Authorization": f"Bearer {os.environ['TAPETIDE_API_TOKEN']}"}
SECTORS = ["nifty-it", "nifty-bank", "nifty-private-bank", "nifty-psu-bank", "nifty-fmcg",
           "nifty-pharma", "nifty-auto", "nifty-metal", "nifty-realty", "nifty-energy",
           "nifty-financial-services", "nifty-infrastructure", "nifty-media", "nifty-mnc",
           "nifty-pse", "nifty-cpse", "nifty-commodities", "nifty-india-consumption",
           "nifty-consumer-durables", "nifty-healthcare-index", "nifty-oil-and-gas"]
ORDER = ["Leading", "Weakening", "Lagging", "Improving"]


def closes(slug, start="2015-01-01"):
    rows, params = [], {"from": start, "limit": 1000}
    while True:
        body = requests.get(f"{API}/indices/{slug}/levels", headers=HEADERS, params=params, timeout=60).json()
        rows += body["data"]
        if not body["meta"]["has_more"]:
            break
        params["cursor"] = body["meta"]["next_cursor"]
    s = pd.DataFrame(rows)
    return pd.Series(s["close"].values, index=pd.to_datetime(s["date"]), name=slug)


daily = pd.concat([closes(s) for s in SECTORS + ["nifty-500"]], axis=1).sort_index()
iso = daily.index.isocalendar()
weekly = daily.groupby([iso.year, iso.week]).last()  # last real close per week; empty weeks stay NaN
weekly.index = [pd.Timestamp.fromisocalendar(y, w, 5) for y, w in weekly.index]
if weekly.index[-1] >= pd.Timestamp.today().normalize():
    weekly = weekly.iloc[:-1]  # this week hasn't finished yet


def wma(x, n):
    weights = np.arange(1, n + 1)
    return x.rolling(n).apply(lambda v: v @ weights / weights.sum(), raw=True)


def readings(rs):
    ratio = 100 * wma(rs, 5) / wma(rs, 15)               # is relative strength rising lately?
    mom = 100 * ratio / ratio.shift(5)                   # is that speeding up or fading?
    q = np.where(ratio >= 100, np.where(mom >= 100, "Leading", "Weakening"),
                 np.where(mom >= 100, "Improving", "Lagging"))
    next_13w = rs.shift(-13) / rs - 1                    # beat the benchmark next quarter?
    d = pd.DataFrame({"rs": rs, "ratio": ratio, "mom": mom, "quadrant": q, "next_13w": next_13w})
    return d[d["ratio"].notna() & d["mom"].notna()]


def clockwise_share(frames):
    moves = [(a, b) for d in frames for a, b in zip(d["quadrant"], d["quadrant"].iloc[1:]) if a != b]
    return np.mean([ORDER[(ORDER.index(a) + 1) % 4] == b for a, b in moves]), len(moves)


rs_all = {s: 100 * weekly[s] / weekly["nifty-500"] for s in SECTORS}
frames = [readings(rs)[lambda d: d.index >= "2016-07-01"] for rs in rs_all.values()]
panel = pd.concat(frames, keys=SECTORS, names=["sector", "week"])
print(panel.xs(panel.index.get_level_values("week").max(), level="week").round(2)
      .sort_values("ratio", ascending=False).drop(columns="next_13w").to_string())

test = panel.dropna(subset=["next_13w"])
print(test.groupby("quadrant")["next_13w"].agg(
    weeks="size", avg=lambda v: f"{v.mean():+.2%}", beat_benchmark=lambda v: f"{(v > 0).mean():.1%}"))
share, n = clockwise_share(frames)
print(f"real sectors: {n} quadrant changes, {share:.1%} clockwise")

# The same map built from shuffled weekly moves: same sizes, no real pattern.
rng = np.random.default_rng(11)
shuffled = []
for _ in range(30):
    fake = []
    for rs in rs_all.values():
        s = rs.dropna()
        run = (s.index.to_series().diff() > pd.Timedelta(weeks=2)).cumsum()
        s = s[run == run.value_counts().idxmax()]  # longest unbroken stretch only
        moves = np.log(s).diff().dropna().to_numpy()
        path = np.exp(np.log(s.iloc[0]) + np.concatenate([[0], np.cumsum(rng.permutation(moves))]))
        fake.append(readings(pd.Series(path, index=s.index))[lambda d: d.index >= "2016-07-01"])
    shuffled.append(clockwise_share(fake)[0])
print(f"shuffled data: {np.mean(shuffled):.1%} clockwise (range {min(shuffled):.1%} to {max(shuffled):.1%})")

What I'd try next

A better null model. Shuffling weekly moves breaks every streak. A block shuffle that keeps short runs intact would be a tougher test.
The daily map. The live page also has a daily version with 14/36/8 settings.
Run every new pattern past random data first. That one is less an idea than a rule now.

The live map is at tapetide.com/insight/sector-rotation, on Tapetide, the data platform I built. The findings and charts are in the companion piece on Medium. Not investment advice.

I Backtested Every FII Selling Streak Since 2014 in 58 Lines of Python

sanjay mehta — Sun, 25 Oct 2026 12:21:39 +0000

On 15 October, foreign institutional investors (FIIs) were net sellers of Indian stocks for the 15th session in a row. Each evening the provisional figure came out red, and the same question followed: is the market about to fall?

I wanted to answer that with data instead of vibes, so I wrote a small backtest: find every long FII selling streak since 2014, and check what the Nifty 50 did afterwards. The whole thing is 58 lines of Python. This post walks through how it works and the three traps I hit. The findings, with charts and caveats, are in the companion piece on Medium (linked at the end).

The short answer: after day 10 of a selling streak, the Nifty did about what it does after any random day.

Not investment advice. I built the data API used here. Data runs from 1 January 2014 to 23 October 2026.

The data

Two daily series:

FII/DII cash-market flows: net buying or selling by foreign and domestic institutions, in ₹ crore, from the exchanges' provisional figures.
Nifty 50 daily levels: open, high, low, close.

I pulled both from the market data API I built, which serves them as paginated JSON. Each response carries meta.has_more and meta.next_cursor, so one helper fetches everything:

import os
import requests
import pandas as pd

API = "https://api.tapetide.com/v1"
HEADERS = {"Authorization": f"Bearer {os.environ['TAPETIDE_API_TOKEN']}"}


def fetch_all(path, **params):
    """Follow next_cursor until the endpoint says there is nothing left."""
    rows, params = [], {"limit": 1000, **params}
    while True:
        page = requests.get(f"{API}{path}", headers=HEADERS, params=params, timeout=30)
        page.raise_for_status()
        body = page.json()
        rows += body["data"]
        if not body["meta"]["has_more"]:
            return pd.DataFrame(rows)
        params["cursor"] = body["meta"]["next_cursor"]


flows = fetch_all("/market/institutional-flows", **{"from": "2014-01-01"})
nifty = fetch_all("/indices/nifty-50/levels", **{"from": "2014-01-01"})

from is a Python keyword, hence the **{"from": ...} unpacking. 1,000 is the maximum page size, and a bigger value gets a 400 rather than being silently clamped. 3,151 flow rows and 3,170 Nifty rows means four requests each, so the whole data step is eight HTTP calls.

The join

df = flows[["date", "fii_net_cr"]].merge(nifty[["date", "close"]], on="date")
df = df.sort_values("date").reset_index(drop=True)

An inner join on date gives 3,151 sessions that have both a flow figure and a Nifty close.

Trap 1: the join hides missing days. The index has 3,170 sessions since 2014, so 19 sessions have no flow figure. An inner join drops them without a word. That matters here, because a missing day in the middle of a selling run glues two streaks into one.

I checked with a set difference. Two of the long streaks (December 2014 and October to November 2018) span a missing day. Re-running on the Nifty calendar, nifty[["date", "close"]].merge(flows[["date", "fii_net_cr"]], on="date", how="left"), makes a missing flow NaN. NaN < 0 is False, so the gap counts as "not selling" and breaks the streak. That gave 47 streaks instead of 48 (the October 2018 one drops below 10 sessions) and moved the 3-month average from +3.38% to +3.17%. The conclusion survived, so I kept the simpler join and wrote the limitation down. Always run that check before you trust a streak count.

Counting streaks in three lines

This is the part I like:

selling = df["fii_net_cr"] < 0
streak_id = (~selling).cumsum()
df["streak_day"] = selling.groupby(streak_id).cumsum()

~selling is True on every non-selling day, and its cumulative sum goes up by one each time. So every run of selling days shares one streak_id, the id of the last buying day before it. Inside each group, a cumulative sum of the booleans counts 1, 2, 3 through the run, and resets to 0 on the next buying day.

Here it is on the latest streak:

date	fii_net_cr	selling	streak_id	streak_day
23 Sep 2026	+1,617	False	k	0
24 Sep 2026	−5,027	True	k	1
25 Sep 2026	−3,694	True	k	2
…	…	…	k	…
8 Oct 2026	−12,944	True	k	10
…	…	…	k	…
15 Oct 2026	−5,285	True	k	15
16 Oct 2026	+1,166	False	k+1	0

No loops, and it works for any "consecutive days where X" question: up days, gap-ups, days above a moving average.

Forward returns

HORIZONS = {"1m": 21, "3m": 63, "6m": 126}
for label, n in HORIZONS.items():
    df[label] = df["close"].shift(-n) / df["close"] - 1

signals = df[df["streak_day"] == 10]

21, 63 and 126 trading sessions are roughly one, three and six months.

Trap 2: shift(-n) looks into the future. That's exactly what you want for measuring outcomes. It must never feed into a signal. The last 126 rows have no 6-month return yet, and they stay NaN. Don't fillna(0) them: pandas' mean() already skips NaN, and filling with zero would pull every average toward zero.

There's a subtler version of the same trap. The FII figure is published after the close, so nobody can trade at day 10's close. Measuring from the next session instead is one more shift, df["close"].shift(-n - 1) / df["close"].shift(-1) - 1. It moves the 3-month average after day 10 from +3.38% to +3.48%, which doesn't change anything here. It's still worth doing every time. These are Nifty 50 price returns, so dividends are excluded from both columns.

The result

def summary(rows):
    out = {h: f"{rows[h].mean():+.2%}" for h in HORIZONS}
    out["3m up"] = f"{(rows['3m'].dropna() > 0).mean():.0%}"
    out["n (3m)"] = rows["3m"].notna().sum()
    return out


print(pd.DataFrame({"day 10 of a streak": summary(signals), "every day": summary(df)}))

       day 10 of a streak every day
1m                 +0.75%    +0.98%
3m                 +3.38%    +3.07%
6m                 +5.27%    +5.99%
3m up                 66%       67%
n (3m)                 47      3088

Forty-eight times since 2014, foreign investors sold for 10 sessions straight. The latest (day 10 was 8 October 2026) is too recent to score, so the averages cover 47 cases, and 46 at six months. Their next one, three and six months looked like any other stretch.

Is a 0.3-point gap anything?

Trap 3: comparing two averages without a sense of their noise. The 3-month returns after day 10 have a standard deviation of about 6.65 percentage points, so with 47 cases the standard error of their mean is close to 1 point. A random-subset check (draw 47 days at random from all days, many times) shows what chance alone produces:

import numpy as np

rng = np.random.default_rng(7)
all_3m = df["3m"].dropna().to_numpy()
means = np.array([rng.choice(all_3m, 47, replace=False).mean() for _ in range(20_000)])
print(np.percentile(means, [5, 95]), (means >= signals["3m"].mean()).mean())

Ninety percent of random 47-day sets average between +1.15% and +5.01%, and 40% of them beat the selling-streak average. A 0.3-point gap is well inside the noise. The flip side: with 47 cases, this can't rule out a real effect of a point or two in either direction. The windows also overlap in time, so treat this as a sanity check, not a formal test.

I also recomputed the headline numbers with a SQL query against the database behind the API. They matched to two decimals. That rules out bugs in the pandas logic, though not problems in the underlying data.

The streak table

The last few lines group the selling days by streak_id and keep runs of 10 or more:

streaks = (
    df[selling]
    .groupby(streak_id[selling])
    .agg(start=("date", "first"), end=("date", "last"),
         sessions=("date", "size"), sold_cr=("fii_net_cr", "sum"))
)
long_ones = streaks[streaks["sessions"] >= 10]

48 streaks of 10+ sessions since 2014
     start        end  sessions    sold_cr
2026-02-26 2026-04-09        27 -173187.48
2024-09-27 2024-11-22        38 -166394.27
2025-01-03 2025-02-03        23  -92384.16
2026-09-24 2026-10-15        15  -84138.07
2022-02-14 2022-03-15        21  -80736.80
2026-05-26 2026-06-12        13  -70986.85
2020-02-24 2020-03-26        23  -70132.90
2021-11-16 2021-12-27        29  -69052.80

In nominal rupees, five of the six biggest streaks started in September 2024 or later. There are too few recent cases yet to test whether they behave differently.

What I'd add next

Size-weighted streaks. Right now a ₹10 crore selling day counts the same as a ₹10,000 crore one.
The domestic side. The flows endpoint also returns dii_net_cr. In 8 of the 10 streaks since January 2025, domestic institutions bought more than foreigners sold. Whether unabsorbed streaks behave differently is the next question.
Other indices. Swap the slug in the levels URL and the rest of the script runs unchanged.

The full script is below. Set TAPETIDE_API_TOKEN, run it, and it prints both tables.

import os
import requests
import pandas as pd

API = "https://api.tapetide.com/v1"
HEADERS = {"Authorization": f"Bearer {os.environ['TAPETIDE_API_TOKEN']}"}


def fetch_all(path, **params):
    """Follow next_cursor until the endpoint says there is nothing left."""
    rows, params = [], {"limit": 1000, **params}
    while True:
        page = requests.get(f"{API}{path}", headers=HEADERS, params=params, timeout=30)
        page.raise_for_status()
        body = page.json()
        rows += body["data"]
        if not body["meta"]["has_more"]:
            return pd.DataFrame(rows)
        params["cursor"] = body["meta"]["next_cursor"]


flows = fetch_all("/market/institutional-flows", **{"from": "2014-01-01"})
nifty = fetch_all("/indices/nifty-50/levels", **{"from": "2014-01-01"})

df = flows[["date", "fii_net_cr"]].merge(nifty[["date", "close"]], on="date")
df = df.sort_values("date").reset_index(drop=True)

# A streak is a run of consecutive sessions where FIIs were net sellers.
selling = df["fii_net_cr"] < 0
streak_id = (~selling).cumsum()
df["streak_day"] = selling.groupby(streak_id).cumsum()

# Forward Nifty returns: roughly 1, 3 and 6 months of trading sessions.
HORIZONS = {"1m": 21, "3m": 63, "6m": 126}
for label, n in HORIZONS.items():
    df[label] = df["close"].shift(-n) / df["close"] - 1

signals = df[df["streak_day"] == 10]


def summary(rows):
    out = {h: f"{rows[h].mean():+.2%}" for h in HORIZONS}
    out["3m up"] = f"{(rows['3m'].dropna() > 0).mean():.0%}"
    out["n (3m)"] = rows["3m"].notna().sum()
    return out


print(pd.DataFrame({"day 10 of a streak": summary(signals), "every day": summary(df)}))

streaks = (
    df[selling]
    .groupby(streak_id[selling])
    .agg(start=("date", "first"), end=("date", "last"),
         sessions=("date", "size"), sold_cr=("fii_net_cr", "sum"))
)
long_ones = streaks[streaks["sessions"] >= 10]
print(f"\n{len(long_ones)} streaks of 10+ sessions since 2014")
print(long_ones.sort_values("sold_cr").head(8).to_string(index=False))

The data came from Tapetide's market data API, which I built. The findings, charts and limitations are in the companion piece on Medium. Not investment advice.

The AI Told Me Foreign Funds Cut ICICI Bank by 12 Points. Then I Looked at the Other Column.

sanjay mehta — Mon, 12 Oct 2026 07:21:12 +0000

Most posts about AI stock research show the prompt and the polished answer. This one shows the middle: which tools got called, what came back, and the one figure I almost repeated.

The setup is Claude connected over the Model Context Protocol to a server that holds Indian market data: filings, prices and ownership for about 8,200 NSE and BSE companies. I built the server, so I can see both ends of the wire. I name it once, at the bottom.

The prompt

Compare HDFC Bank and ICICI Bank on ten-year profit growth, return
on equity, price to book, and how FII and DII holdings have moved
over the last four quarters. Then tell me the strongest argument
against whichever one looks cheaper.

No tool names. No hints about data shape. The model reads the catalogue the server exposes (62 tools, each with a short description and a JSON schema) and picks.

What it called

Seven calls:

search_stocks twice, to resolve the two names to the server's internal ids.
get_financials twice, annual consolidated. Ten-plus years of P&L and ratios per response.
get_shareholding twice, quarterly. Promoter, FII, DII and public percentages per quarter.
get_batch_quotes once for both, which carries price to book and trailing P/E with the price.

It skipped get_earnings_call_summary, get_forecasts and get_credit_ratings. The prompt did not ask for them, so it did not fetch them.

Seven calls against a free allowance of 50 a day. That is fine for one comparison and not fine inside a loop.

What came back

Profit, consolidated, ₹ crore, FY17 to FY26. HDFC Bank: 15,287 → 18,561 → 22,446 → 27,296 → 31,857 → 38,151 → 46,149 → 65,447 → 73,440 → 79,220. ICICI: 11,340 → 9,100 → 5,689 → 11,226 → 20,220 → 25,784 → 34,463 → 45,007 → 54,419 → 57,673. I checked FY17, FY19, FY24 and FY26 for both against a second set of filings. All eight matched within half a percent.

Return on equity, FY26. HDFC Bank 13.0 percent. ICICI 15.2 percent.

Quotes, 12 October 2026. HDFC Bank P/B 1.81, P/E 13.2. ICICI P/B 2.60, P/E 16.3.

Shareholding, latest four quarters on file. HDFC Bank FII: 47.67 → 44.05 → 41.82 → 39.39, December 2025 to September 2026. ICICI FII: 45.56 → 43.87 → 34.48 → 33.79, September 2025 to June 2026. The windows differ by a quarter because ICICI's September 2026 filing had not landed in this source yet.

Every figure came with a period label, which is what made the next part possible.

The answer, and the number inside it

Claude led with valuation (HDFC Bank cheaper on both multiples), then profit (HDFC steady; ICICI collapsed in FY19 during its bad-loan cleanup and has compounded faster since, roughly 39 percent a year from the trough against HDFC's 20), then ownership. On ownership it wrote that foreign institutions had been cutting both banks, and that the cut in ICICI was sharper: nearly twelve points across the window.

Then the argument I asked for: HDFC Bank is cheaper because its return on equity is lower and the market has not yet credited the 2023 merger with its housing-finance parent; ICICI is dearer because it already did the hard part. I thought that was a fair read.

But twelve points of ICICI stopped me. At a P/E of 16.3 on ₹57,673 crore of profit the bank is worth about ₹9.4 lakh crore, so twelve points is roughly ₹1.1 lakh crore of stock. Foreign funds do not do that without it being in every newspaper, and it was not.

Pulling the rows

I asked for the full quarterly table with every bucket, not the four-quarter FII summary.

Quarter	FII	DII	Public	Govt	Total
Sep 2025	45.56	45.06	9.11	0.27	100.00
Dec 2025	43.87	46.74	9.13	0.27	100.01
Mar 2026	34.48	39.94	25.36	0.22	100.00
Jun 2026	33.79	42.32	23.68	0.22	100.01

Between December and March, FII fell 9.4 points, DII fell 6.8, and "Public" rose 16.2. Sixteen percent of ICICI Bank did not change hands to retail investors in one quarter. Something was recategorised.

Then I asked Claude to pull the second ownership series the server carries, which is built from a different filing basis. It agrees with the first to the second decimal for September and December. For March 2026 it also shows FII 34.49 and DII 39.95, but its residual bucket is "Others" at 19.48 with "Public" at 5.87, where the first source has "Public" at 25.36. And for June 2026 it shows FII 49.82, DII 42.31, Public 6.03, Others 1.62.

So in one source ICICI's foreign holding is 33.8 percent in June. In the other it is 49.8. Same bank, same quarter, sixteen points apart, which is roughly the block that went into a residual bucket in March.

I do not know what that block is. My first guess was the bank's overseas depositary shares, but that would not explain why DII fell 6.8 points in the same quarter, so I am not going to assert it. What I can say is that one filing basis moved a large holding into a catch-all category in March and the two bases then disagree about where it sits.

HDFC Bank has no such jump in either series. The second one shows FII at 48.39, 47.67, 44.05, 41.83, 39.40 for September 2025 through September 2026, matching the first to within a few hundredths. That one is real.

The honest ownership statement, then: foreign funds have been reducing HDFC Bank for four straight quarters. For ICICI, the filings disagree and I cannot say.

Why I almost missed it

The number had a date. It came from a filing. The row summed to 100. It fit the story sitting next to it. Every check I run passed.

What caught it was size. Twelve points was too big. Had the residual bucket swallowed three points instead of sixteen, I would have published "FIIs are cutting ICICI faster" and been wrong in a way no reader could have detected.

Claude's summary was not false: the four FII numbers it quoted are what the filing says. But "cutting" is a verb about behaviour, and the filing only supports a statement about categories.

What I changed on the server

One sentence in the shareholding tool's description, which the model reads on every call:

CAUTION: a move of more than 5 points in any bucket in a single quarter may be a filing reclassification rather than trading. When you see one, report the public/other buckets for the same quarters alongside it and do not describe the move as accumulation or exit without a second source.

It is committed on a branch as I write this, not yet deployed, so I cannot tell you how the model behaves with it. I will update this post when it has run for a week. My hypothesis, untested, is that this kind of "accurate data, wrong verb" problem is better handled in the instructions that travel with the data than in the prompt, because the prompt changes every time and the description does not.

The ROE footnote

One more thing a careful reader would catch. Dividing P/B by P/E gives a trailing, ending-equity return on equity: about 13.7 percent for HDFC Bank and 16.0 for ICICI. The tool returned 13.0 and 15.2, which are fiscal-year figures on average equity. Both are right. The schema says which basis the tool returns. The summary did not, until I asked.

Limits

One session, one model, one pair of banks.
I built the server, so I knew a second ownership series existed and could ask for it by name. A user who did not would have had to notice the public-bucket jump cold.
The tool-description change is untested as of publishing. See above.

If you have hit a case where the data was correct and the conclusion drawn from it was wrong, how did you catch it? Mine was caught by a number being implausibly large, and I would like a better method than that.

The server is Tapetide MCP: about 8,200 NSE and BSE stocks plus indices, MCP, free for 50 calls a day, setup at tapetide.com/mcp. The investor's-eye write-up of the same session is on Medium. Not investment advice.

I Almost Published a Chart Saying the Nifty Fell 51% in 2003. It Fell 16%. Here Is the Bug.

sanjay mehta — Sun, 11 Oct 2026 07:41:33 +0000

I keep a few million rows of Indian stock market data in PostgreSQL as a hobby. I started in 2025 because the free sites kept changing their page layouts and breaking the scripts I used to read them. I am a developer, not a quant, and I have spent a year quietly getting things wrong.

Last week one of those mistakes got as far as a finished chart with a caption. The chart said the Nifty 50 fell 51 percent inside 2003. The true number is 16 percent. A reviewer caught it twenty minutes before I would have published it.

That one is first. Three more follow, in the order I made them. Each has the hours it cost and the SQL I use now.

1. The 51 percent that was not there

I wanted the deepest fall inside each calendar year, from a peak to the trough that followed. I wrote the obvious thing: lowest close divided by highest close, per year, minus one.

For 2003 that gave about 51 percent. I believed it. The year finished up 71 percent, so a huge dip inside it made for a great caption: "even the best years have a crash in them."

Except 2003 went up almost in a straight line. The low was in March and the high was in December. Low over high ignores which came first. It is just the year's range. The real peak-to-trough drop that year was 16 percent, and it happened in the spring, before the run.

A drawdown has a direction. The peak must come before the trough. The way to encode that is a running maximum.

with r as (
  select date,
         close,
         max(close) over (
           partition by extract(year from date)
           order by date
           rows between unbounded preceding and current row
         ) as running_high
  from index_closes
  where symbol = 'NIFTY50'
)
select extract(year from date)::int as yr,
       round((100 * min(close / running_high - 1))::numeric, 1) as max_drawdown_pct
from r
group by 1
order by 1;

Two things in there bit me on the way to getting it right.

The partition by is not optional. My first "fixed" version left it out, so the running high for 2003 was the 2000 peak, and the answer came back as 47 percent. Still wrong, just differently. If you want the fall within a year, the high has to reset each year.

The ::numeric cast is there because round(double precision, integer) does not exist in PostgreSQL. My prices load as doubles. I have hit that error maybe fifteen times this year. I now cast before I round, every time, without thinking.

Cost: about three hours across two evenings, plus the near miss.

2. The worst day that was really a worst exit

Same table. I asked: for every possible buy day since 2005, what was the worst ten-year outcome?

The answer was 23 March 2010. For three different indices. The same day.

I spent forty minutes looking for a corrupt row. The closes around that date were smooth: 5,205 on the 22nd, 5,225 on the 23rd, 5,260 on the 25th. Nothing wrong.

Then I checked the other end of the window. Ten years after 23 March 2010 is 23 March 2020, the bottom of the COVID crash. Buy days around 23 March 2010 all scored badly for the same reason. Their exits land in the same hole.

The query was right. I was looking at the wrong date.

-- print both ends, always
select entry.date  as entry_date,  entry.close as entry_close,
       exit.date   as exit_date,   exit.close  as exit_close,
       round((100 * (exit.close / entry.close - 1))::numeric, 1) as ret_pct
from   index_closes entry
join lateral (
  select date, close from index_closes
  where  symbol = entry.symbol and date >= entry.date + interval '10 years'
  order  by date limit 1
) exit on true
where  entry.symbol = 'NIFTY50'
order  by ret_pct
limit  1;

Cost: forty minutes, and a draft paragraph that called a correct result a data glitch, which I had to rewrite.

3. The company that became two companies

I keyed everything on the ticker symbol. Tata Motors was TATAMOTORS.

In 2025 the company split into two listed entities. The symbol I had hard-coded changed, and a second one appeared for the commercial vehicle business. My join returned no rows for the old symbol after the split date. Because I was computing sector averages with a left join, the missing rows became nulls, and the average quietly dropped them. The sector average for autos moved and I did not notice for three weeks.

Zomato renamed itself Eternal the same year. Smaller blast radius, same bug.

The fix is the one every data person already knows and I had skipped because it was more tables: an internal integer id as the stable key, and a symbols table with valid_from and valid_to.

create table company (
  id   integer primary key,
  name text not null
);

create table symbol_history (
  company_id integer not null references company(id),
  symbol     text    not null,
  valid_from date    not null,
  valid_to   date,                      -- null = current
  primary key (company_id, valid_from)
);

-- which company was 'TATAMOTORS' on 2024-06-01?
select c.*
from   symbol_history s
join   company c on c.id = s.company_id
where  s.symbol = 'TATAMOTORS'
and    '2024-06-01' between s.valid_from and coalesce(s.valid_to, '9999-12-31');

Cost: the three weeks I did not notice, then one evening to backfill.

4. Zero meant three different things

In my index table, a day could be absent (holiday), present with a close of zero (a padded gap from one loader), or present with a real close. My first forward-return query only checked that a row existed, so it happily computed returns against zeros and produced a few infinite percentages.

I fixed it with where close > 0 and moved on. Then I reused the table for a volume query, where a zero on a real trading day is meaningful, and the same filter was wrong there.

What I do now is dull. Holidays are not rows. Bad loads are nulls, never zeros. A zero is a zero. One evening to backfill, and I have not had this class of bug since.

-- loader rule: never write 0 for "unknown"
insert into index_closes (symbol, date, close)
values ($1, $2, nullif($3::double precision, 0));

Cost: the two bugs took maybe an hour to find, plus the backfill evening. The second one was embarrassing because I had just "fixed" it.

What I actually do differently

I do not have a grand rule. I have one habit I started the evening after the 2003 chart: before I put a single number in a caption, I look at the handful of rows that produced it. When I finally did that for the 51 percent, it took ten seconds. The date of the low came before the date of the high, and the caption fell apart.

I did not have the habit when it mattered. A reviewer caught that one. The habit is my attempt to not need the reviewer next time. It would not have caught the symbol split, because no single row looked wrong, but it would have caught the other three.

Limits

Hobby scale. A few million rows, one machine. Some of these fixes cost more at real scale.
Indian market data has its own oddities: lot sizes that change, expiry dates that get moved, holidays declared with a day's notice. Yours will have different ones.
I found four so far.

If you have a version of the 2003 mistake in your own domain, the kind where the number looks right and the dates say otherwise, I would like to hear it.

I Gave an AI Agent 20 Years of Stock Index Data. The Hardest Part Wasn't the AI.

sanjay mehta — Sun, 11 Oct 2026 03:05:58 +0000

I had a question that is annoying to answer in a spreadsheet and trivial to say out loud: if you bought an Indian stock index on any random day since 2005 and held for N years, how often did you lose money?

So I said it out loud to an AI assistant that had a Model Context Protocol connection to a server with daily index data back to 1996, and watched what it did. This post is about the plumbing, the one answer I was sure was wrong, and why that moment is the actual hard part of giving an agent real data.

If you want the investing takeaway, I wrote that up on Medium. This is the developer version.

I built the MCP server used here. Details at the end.

The prompt

Take every trading day from April 2005 to October 2016 as a buy day for
Nifty 50, Nifty Midcap 150 and Nifty Smallcap 250. For each buy day,
check the index level 1, 2, 3, 4, 5, 6, 7, 8 and 10 years later. Tell
me what share of buy days ended with the index lower. Also give the
median 5-year and 10-year growth rate, and the single worst 10-year
buy day.

No code in the prompt. No mention of which tools exist. The assistant reads the tool schemas the server exposes and decides.

What it actually called

Four tool calls, in this order:

Resolve the three index names to the server's identifiers. Index names drift ("Nifty Midcap 150" has had other names), and the server has an as-of resolver for exactly this.
Fetch daily closes for each index, 2005 to 2026. Three calls batched into one.
Fetch the observation calendar so it knows which dates are trading days and which are holidays. This matters for "the first trading day N years later".
Compute the forward-return table itself, in the model's own scratch space, then summarise.

Result, in about forty seconds:

1-year holds that lost money: Nifty 50 23.5%, Midcap 28.5%, Smallcap 34.2%
3-year: 4.8%, 13.7%, 20.8%
5-year: 1.7%, 2.3%, 15.2%
7-year and beyond: 0%, 0%, 0%
Median 10-year growth: 11.2%, 15.5%, 13.4% a year

I then recomputed the whole thing in SQL against the same table. Every percentage matched to one decimal. Good.

Where I thought it went wrong, and why I was the one who was wrong

I asked a follow-up: "What was the single worst 10-year buy day for each index?"

It answered 23 March 2010. For all three indices. The same date.

That looked like a data glitch to me. Three indices with different constituents, all bottoming out on the same Tuesday as the worst possible entry? I was sure a bad row was sitting in the table. I asked the agent to show me the closes around that date.

They were smooth. 5,205 on the 22nd, 5,225 on the 23rd, 5,260 on the 25th. Nothing wrong with the buy day at all.

Then I looked at the exit date. Ten years after 23 March 2010 is 23 March 2020. That was the bottom of the COVID crash. The Nifty 50 closed at 7,610 that day, down 13 percent from the day before and 38 percent from its February high.

So the "worst buy day" was never about the buy day. It was about where the exit landed. Any buy day in March 2010 would have scored almost identically, because they all exit into the same hole. The agent's answer was correct. My instinct that it was broken was the error.

Here is what bothers me about that. The agent gave me a true number with no explanation attached. A human analyst would have said "23 March 2010, but only because the exit falls on the COVID low". The agent said "23 March 2010" and stopped. I nearly threw away a correct answer because it arrived without its reason.

What I would change in the tool, not the model

The fix is not a smarter model. It is a tool description that asks for the reason to travel with the number.

{
  "name": "get_price_history",
  "description": "Daily or weekly closes. When a caller derives a single extreme from this data (worst day, best day, peak, trough), report BOTH the entry and exit dates and check each against get_observation_status so a crash day or holiday boundary is named, not hidden."
}

One sentence in a schema. Agents follow instructions in tool descriptions more reliably than instructions in prompts, because the description is present on every call and the prompt is not.

I have not shipped that change yet. I am writing it here first because I want to see whether other people have hit the same shape of problem: a correct answer that looks wrong because the context that makes it correct was never requested.

Three rules I now follow when exposing data to agents

1. Every extreme comes with both dates. A "worst 10-year window" is two dates, not one. If the tool only returns the entry, the caller will blame the entry.

2. Dates get a status, not just a value. Holiday, weekend, pre-listing, post-delisting, crash day. The server already exposes this as an observation-status tool. I did not ask for it, so the agent did not call it. The schema should tell it when to.

3. Identifiers are time-dependent. The index called "Nifty Midcap 150" today was not called that in 2005. Tata Motors became two companies in 2025. A tool that resolves names as-of a date is the difference between a right answer and a confident wrong one.

What this cost

Four tool calls for the main question, two for the follow-up, two more to look at the closes around the suspicious date. Eight calls total against a free tier of fifty a day. The SQL I wrote to verify it took me longer than the agent took to produce it.

Honest caveats

Price-only index data. Dividends excluded, so real returns are better than the numbers above.
One agent, one model family, one afternoon. Your tool descriptions may need different wording for different models.
The "zero losers at seven years" result is about Indian indices from 2005 to 2026. It is a fact about the past.
I built the server, so I knew the status tool existed and could check the exit date in seconds. A user who did not might have shipped "the data is broken" instead of "the exit landed on COVID".

What is the equivalent of "check outliers first" in your domain, and have you put it in a schema yet? I would genuinely like to know what others are writing into tool descriptions.

The server is at tapetide.com/mcp: about 8,200 NSE and BSE stocks plus indices, MCP, free for 50 calls a day. Not investment advice.

Foreign Investors Have Sold Indian Stocks for 6 Years Straight. I Asked an AI to Show Me Who Is Buying.

sanjay mehta — Sat, 10 Oct 2026 12:41:47 +0000

Every few weeks a headline says foreign investors are "dumping" Indian stocks. Every few weeks the Nifty refuses to collapse. I wanted to know why, and I wanted the answer from the data, not from a TV panel.

So I asked an AI assistant. Not a plain chatbot (I tested that separately, and it was wrong 18 times out of 30). This one had a Model Context Protocol connection to a server with daily institutional flow data back to 2014, index valuations back to 2016, and current filings for about 8,200 NSE and BSE companies.

Here is the prompt, the three charts it produced, and the one number I did not expect.

Not investment advice. Figures are from exchange disclosures as of 9 October 2026. I built the data server used here; details at the end.

The prompt

Show yearly net FII and DII flows in the Indian cash market since 2014,
then monthly since January 2024. How many consecutive months have
domestic institutions been net buyers? And where does today's Nifty 50
P/E sit against every trading day since 2016?

That is the whole input. Four tool calls happened behind it: one for the daily flow history, one for the monthly roll-up, one for the index valuation history, one for the latest close.

Chart 1: the yearly picture

Foreign institutions (FIIs) have been net sellers of Indian equities in every calendar year since 2021. Not most years. Every year.

2021: FII −₹0.9 lakh crore, DII +₹0.9 lakh crore
2022: FII −₹2.8 lakh crore, DII +₹2.8 lakh crore
2023: FII −₹0.2 lakh crore, DII +₹1.8 lakh crore
2024: FII −₹3.0 lakh crore, DII +₹5.3 lakh crore
2025: FII −₹3.1 lakh crore, DII +₹7.9 lakh crore
2026 to 9 Oct: FII −₹4.4 lakh crore, DII +₹6.8 lakh crore

The 2026 foreign outflow is already larger than any full year on record, with a quarter of the year left. Domestic institutions (DIIs, which is mostly your SIP money flowing through mutual funds, plus insurers and pension funds) have absorbed all of it and then some.

One lakh crore is roughly 12 billion US dollars, if that helps calibrate.

Chart 2: month by month

Thirty-four months from January 2024 to October 2026. Foreign money left in 26 of them. Domestic money arrived in all 34.

The tallest month was March 2026: FIIs sold ₹1.23 lakh crore, DIIs bought ₹1.43 lakh crore. In a single month.

The number I asked for and did not expect: counted back from today, domestic institutions have been net buyers for 39 consecutive months. The last month they were net sellers was mid-2023.

Chart 3 is a sentence, not a picture

The Nifty 50 closed at 22,520 on 9 October 2026, down about 14 percent from its end-2025 close of 26,130. Its trailing P/E was 19.3.

Across 2,659 trading days since January 2016, the index has been cheaper than that on 0.9 percent of them. The median P/E over the period is 23.2. The 2021 peak was 42.

So the market is down, foreigners are selling at a record pace, domestic investors are buying at a record pace, and the index is at a valuation it has visited on roughly 24 trading days in a decade. I am not telling you what that means. I am telling you it took one prompt to see it.

Why this needed MCP and not a chatbot

I ran the same question past the same model with no data connection first. It gave me a confident paragraph about "FII selling pressure in 2024" and a Nifty P/E of "around 22". It had no idea about 2025 or 2026 because it has no memory past its training cutoff, and it had no way to count a 39-month streak because it had never seen the months.

MCP is an open standard (Anthropic released it, it now lives under the Linux Foundation) that lets an assistant call outside tools mid-conversation. Connect a data server and the model stops remembering and starts fetching. Every number comes back with the date it was observed. You can ask it to show the rows behind any conclusion.

For developers, the interesting part is how little glue this takes. There is no API client to write. The assistant reads the tool schemas, picks the four it needs, and chains them. Here is the same workflow from Cursor or VS Code, which is one config block:

{
  "mcpServers": {
    "indian-markets": {
      "url": "https://mcp.tapetide.com/mcp"
    }
  }
}

First use opens a browser for Google sign-in. After that the assistant can call the flow, valuation, screening, financials and filing tools directly.

What to watch out for

Data freshness varies by dataset. Daily flows are end-of-day. Shareholding follows quarterly filings. Read the dates on every answer.
The model can still do arithmetic wrong. The data arrives correct; the summing is the model's. Ask for the underlying rows when a total matters.
"DII" is a bucket, not a person. It includes mutual funds, insurers, pension funds and banks. The streak tells you domestic institutions as a group kept buying. It does not tell you retail investors are right.
A free tier of 50 tool calls a day is plenty for questions like this one and not enough for an agent that loops. Be specific.

Try it yourself

Pick any question you have been answering by opening six browser tabs. Promoter pledge trends. FII holding changes across a sector. Which indices led last month. Ask it in one sentence, then ask for the rows.

The data server used here is one I built. It covers about 8,200 NSE and BSE stocks, speaks MCP, and is free for 50 calls a day. Setup guides for Claude, ChatGPT, Cursor and the CLIs are at tapetide.com/mcp. The companion piece on how badly a chatbot does without it is on Medium.

Not investment advice. Check the filings before you act on anything.