DEV Community: sanjay mehta

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.