Trading holidays, settlement and missing dates

You ask for a year of data and get around 245 rows, not 365. You download two scrips over identical dates and one returns 240 rows while the other returns 61. A date you were certain was a public holiday has a row on it anyway. None of these are bugs. This guide explains where the gaps come from, and which ones should worry you.

Four reasons a date is missing

Rows exist for trading days only, and there are several distinct ways a calendar date fails to become one.

1. Weekends.BSE’s equity segment does not trade Saturday or Sunday. That removes about 104 days a year on its own.
2. Exchange holidays. Typically somewhere in the mid-teens per year, published annually by the exchange. They follow the Indian festival calendar, so most are lunar-dated and move year to year — you cannot hardcode last year’s list.
3. The scrip did not trade. The market was open; nobody bought or sold this particular security. No trade, no row. On BSE this is by far the largest source of gaps.
4. Suspension. A scrip halted for regulatory or corporate reasons produces no rows for the duration, and nothing in the file marks the reason.

Reasons 1 and 2 apply identically to every scrip. Reasons 3 and 4 are specific to the one you downloaded — which is the entire explanation for differing row counts.

Why two scrips give different row counts

Download RELIANCE and some group X small-cap over the same twelve months. The first returns roughly every trading day. The second might return sixty rows, or twelve.

This is the single most important thing to internalise about BSE data. Around 5,000 equity scrips are listed, but only about 15% sit in group A. A large part of the remainder trades sporadically — some scrips genuinely go weeks without a transaction. A “daily” series for those names is nothing of the sort.

A quick diagnostic on any export: divide your row count by the approximate number of trading days in the range (about 250 a year, or 21 a month).

Read this alongside No. of Trades. A row built from four transactions is present but not meaningful, which is a different failure from being absent. The group reference covers why some scrips are structurally illiquid.

The holiday that has a row anyway

Around Diwali, BSE runs Muhurat trading — a short, ceremonial session held on a day that is otherwise a holiday, considered auspicious for opening positions for the new Samvat year.

It produces a genuine row on a date your holiday list says the market was shut. It is also a real outlier: the session lasts about an hour, so volume and turnover are a fraction of a normal day, and Spread H-L is usually tiny. If you are computing average daily volume, this row will drag it down for reasons that have nothing to do with the stock.

The session date and timing are announced each year and do not follow a fixed rule, so check the exchange’s circular rather than assuming.

Settlement, and why delivery data lags

Prices and volumes are known when the session ends. Delivery figures are not — they are a settlement output, produced after the exchange and depositories work out who owes what.

Indian equities settle on a T+1 rolling basis: a trade on Monday settles Tuesday. That is the standard cycle, having replaced T+2 a few years ago. A shorter optional cycle has since been introduced for a limited set of securities; whether any given scrip is in scope changes over time, so check the exchange if it matters to you.

The practical consequences for your file:

• The newest rows usually have blank delivery columns. Prices are final, settlement is not yet published. Expected, not broken.
• Scattered older gaps occur too.BSE’s delivery archive is not uniformly complete, and its file naming and formats have changed over the years.
• Blank is not zero. Filling gaps with 0 will corrupt every average you compute. See the delivery guide.

If you need a complete delivery series, end your range several sessions before today and still check for gaps.

One thing settlement does not explain: scrips in trade-to-trade groups settle on the same cycle as everything else. What they lose is the ability to net off intraday — not speed.

The calendar trap in spreadsheets

This is where missing dates cause real analytical errors rather than mild confusion.

Because rows are trading days, row offsets are not time offsets. Twenty rows back is not twenty days back — it might be four weeks, or on a thin scrip, eight months. Every formula of the form =E22/E2-1 silently assumes otherwise.

Two habits fix this:

• For anything time-based, index on the date column. To get the price nearest 30 calendar days before a row, look up =[@Date]-30 against column A with an approximate match, rather than counting rows.
• Check the actual span before annualising. Rather than assuming 250 trading days, compute it:
  =COUNT(A:A)/((MAX(A:A)-MIN(A:A))/365)
  If that returns something far below 250, your scrip is not trading daily and annualised volatility from it will understate real risk — the quiet days are absent rather than recorded as zero-return.

Which gaps actually matter

Benign: weekends, published holidays, blank delivery on recent rows, a short Muhurat session, occasional no-trade days on a mid-cap.

Worth investigating: a multi-week gap in a normally liquid scrip — check for a suspension or a corporate action around that date. Prices present but volume zero. A gap immediately followed by a very large price move, which often means something happened while trading was halted.

Structural, not fixable: a scrip that simply does not trade often. No amount of data cleaning turns twelve rows into a usable year. The honest response is to conclude the series cannot answer your question — which is worth reading about before you start.

Keep reading

• Every column in your BSE export, explained — What Open, High, Low, Close, WAP, No. of Shares, No. of Trades, Total Turnover, Deliverable Quantity, % Deli. Qty and the two Spread columns actually mean — and where people misread them.
• BSE scrip codes: why the number matters more than the ticker — BSE identifies every security by a six-digit scrip code, not by symbol. What the code is, why two companies can share a ticker, and how to find the right one.
• BSE groups: A, B, T, Z, X, M and what they change — BSE sorts listed scrips into groups that govern how they trade — including group T, where intraday trading is banned outright. What each letter means for the price history you just downloaded.

Or go straight to the download console and pull a file.