Working with your BSE export in Excel or Google Sheets

Formulas written against the exact layout this tool produces. The export has thirteen columns starting in A1, with data from row 2, sorted oldest to newest — so A is Date, B–E are Open/High/Low/Close, F is WAP, G is No. of Shares, H is No. of Trades, I is Total Turnover, J and K are the delivery fields, and L/M are the two spreads.

Everything below works in both Excel and Google Sheets. Two things to do first.

Before you start

Check for corporate actions. Every formula here assumes a continuous price series. If the stock split during your range, the numbers will be wrong — quietly, in the case of anything cumulative. The screen for this is in the returns section below, and the corporate actions guide explains how to adjust.

Confirm the sort order. Rows come out oldest-first. If you have re-sorted, every formula referencing the previous row is now computing backwards. Sort ascending by Date before proceeding.

Daily returns

In N2, leave it blank (no prior day), then from N3 downwards:

=IF(OR(E3="",E2=""),"",E3/E2-1)

Format as a percentage. The IF guard matters — a blank price would otherwise produce a −100% return and contaminate everything downstream.

For anything you intend to compound or aggregate, use log returns instead; they add across time, which simple returns do not:

=IF(OR(E3="",E2=""),"",LN(E3/E2))

Now run the corporate-action screen. Sort a copy of the returns column by absolute value, descending, and read the top twenty. Real single-day moves are untidy — −7.3%, +11.8%. A clean −50.0% or −80.0% is a split or bonus. Cross-check by looking at whether Total Turnover stayed roughly flat across that date: if the rupee value traded did not change, the price did not really move.

Moving averages

A 20-day simple moving average of close, in O21 and filled down:

=IF(COUNT(E2:E21)<20,"",AVERAGE(E2:E21))

The COUNTguard suppresses the average until there are twenty actual values, so you do not get a “20-day” average computed from six days at the top of the sheet.

For a thinly traded scrip, a WAP-based average is more robust than a close-based one, because WAP cannot be set by a single small trade at the bell — swap E for F. On BSE, where most of the listed universe is thin, this is usually the better choice.

Volatility

Standard deviation of daily returns, annualised with roughly 250 trading days in an Indian market year:

=STDEV.S(N3:N252)*SQRT(250)

Use log returns for this. And be careful what you conclude on an illiquid scrip: days it did not trade are missing rather than zero-return, so the realised volatility of a thin stock computed this way understates the risk of actually holding it.

A rolling 20-day annualised volatility, to see how it changed over time:

=IF(COUNT(N3:N22)<20,"",STDEV.S(N3:N22)*SQRT(250))

Running peak and drawdown

Peak close to date, in P2 as =E2, then from P3:

=MAX(P2,E3)

Drawdown from that peak:

=IF(P3=0,"",E3/P3-1)

Maximum drawdown over the whole period is then =MIN(Q:Q). If it comes out at −80% or −90% on a stock you know did not collapse, you have an unadjusted split — see above.

Turnover, WAP and trade size

Three quick derived measures that are more useful on BSE data than they sound:

Average trade size — a read on who is participating:

=IF(H2=0,"",G2/H2)

Implied WAP check — turnover over volume should reproduce the WAP column. Where it does not, treat the row as suspect:

=IF(G2=0,"",I2/G2)

Delivered value in rupees — usually more informative than delivery percentage alone, because it responds to both participation and price:

=IF(OR(J2="",F2=""),"",J2*F2)

Note the blank guard on the delivery column. Delivery fields are frequently empty on recent rows, and blank is not zero — the delivery guide explains why.

Filtering out unreliable rows

This is the step that most improves the quality of any BSE analysis. Add a column flagging rows thin enough to distrust — say, fewer than 50 trades:

=IF(OR(H2="",H2<50),"thin","ok")

Then compute your statistics over the ok rows only, using AVERAGEIF / STDEV over a filtered range. The right threshold depends on the scrip — for a group A name almost every row will pass; for an X-group scrip almost none will, which is itself the answer to whether that scrip is worth analysing.

A one-block summary

Paste these somewhere off to the side for a quick read on any export:

Use MEDIAN rather than AVERAGE for the volume and delivery rows. Both are heavily skewed by occasional block trades, and the median describes a typical day far better than the mean does.

Read Median trades per day first. If it is in single or low double digits, the scrip is thin and nothing else in the table means very much — which is worth understanding before you go further.

Pitfalls worth naming

• Dates arriving as text. Should not happen — the export writes real date cells — but if a date column left-aligns and MIN returns 0, that is the symptom. Re-import with the column typed as a date.
• Treating blanks as zeros. AVERAGE skips blanks but counts zeros. Filling gaps with 0 will silently drag every average down.
• Assuming calendar continuity.Rows are trading days. Weekends, holidays and no-trade days are simply absent, so “20 rows ago” is not “20 days ago”. For anything date-based, index on the date column rather than on row offsets.
• Annualising with the wrong day count. 250 is a conventional approximation for Indian markets. If you need precision, count the actual rows per year in your own data.
• Comparing spread columns across stocks. Spread H-L and Spread C-O are absolute rupee figures. Divide by Close before comparing anything at different price levels.

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.