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

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.