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

I am a developer who has been working on a hobby project involving Indian stock market data, stored in PostgreSQL. In the process of creating charts and writing articles, I have made a few mistakes that were caught by reviewers before publication. Here are four instances where the data presented was incorrect due to bugs in the SQL queries.

1. The 51% fall that was not there: In an attempt to find the deepest fall in each calendar year, I used a simple formula of lowest close divided by highest close, minus one. For 2003, this calculation gave approximately 51%. However, this did not account for the direction of the drawdown, as the peak must come before the trough.

The actual peak-to-trough drop that year was only 16%, which occurred in the spring before the overall market upswing. The mistake was due to not using a running maximum to establish a per-year high, resetting with each year.

2. The worst day that was really a worst exit: I asked the database to find the worst 10-year outcome starting from any buy day since 2005. The query returned 23 March 2010 as the worst day for three different indices. However, I spent forty minutes trying to find a corrupted row before realizing that the same day was actually the worst exit point ten years later, during the COVID crash.

The query was correct; I simply looked at the wrong date. It is essential to check both the entry and exit dates when calculating returns.

3. The company that became two companies: I used ticker symbols to key my data, with Tata Motors as TATAMOTORS. In 2025, Tata Motors split into two listed entities, causing the old symbol to become invalid. My query joining on the hard-coded symbol returned no rows after the split date. This led to missing rows being treated as null values in sector averages, causing the data to be inaccurate for three weeks before it was noticed.

The solution was to use an internal integer ID as the stable key, with a separate symbols table containing valid_from and valid_to dates.

4. Zero meant three different things: In my index table, a zero value could represent three different things. One was a missing day, which could cause the drawdown calculation to be inaccurate. Another was a day where the close price was zero, which could skew the average return calculation. Lastly, a zero could simply represent a day with no trading activity. It is crucial to carefully consider the meaning of zero values in data analysis to avoid misinterpretations.