Working with your NSE export in Excel or Google Sheets

Every formula below is written against the exact layout this tool produces, so you can paste them without remapping columns. They work identically in Excel and Google Sheets unless noted.

The layout you are working with

Headers occupy row 1; data starts at row 2. Columns land as follows:

Rows arrive sorted oldest-first, and dates are written as real date values rather than text — no DATEVALUE conversion needed.

Two things to do first

Skip these and every number downstream is quietly wrong.

1. Filter to a single series. If column B contains anything besides EQ, you may have two rows for one date. Check with =SUMPRODUCT((COUNTIF(A2:A1000,A2:A1000)>1)*1) — any result above zero means duplicate dates. Which rows to keep.
2. Look for corporate actions. Raw prices are unadjusted, so a split shows up as a genuine-looking 50% crash. Flag candidates with =IF(ABS(H3/H2-1)>0.2,"CHECK","") and investigate each hit before trusting any return series. Full method here.

Daily returns

In a free column — say P — starting at row 3, since the first row has no prior day:

=H3/H2-1

Format as a percentage and fill down. Use CLOSE (H), not LTP (G) — the two differ, and CLOSE is the official mark. Why.

There is a shortcut worth knowing: column F already holds the exchange’s reference previous close, so =H3/F3-1 gives a return that is often already corrected for corporate actions on the ex-date. It is a useful cross-check — where the two formulas disagree sharply, you have almost certainly found a corporate action.

For work that involves summing returns over time, use log returns instead, since they add cleanly:

=LN(H3/H2)

Total return over the whole period

With data in rows 2 to 500: =H500/H2-1

Annualised (CAGR), using the actual elapsed days:

=(H500/H2)^(365/(A500-A2))-1

Moving averages

A 20-day simple moving average. Enter at row 21 (the first row with twenty observations behind it) and fill down:

=AVERAGE(H2:H21)

For 50-day and 200-day, the same pattern from rows 51 and 201. To avoid misleading partial averages in the early rows, guard it:

=IF(ROW()<21,"",AVERAGE(H2:H21))

A volume-weighted average over the same window — usually more meaningful than a plain price average:

=SUMPRODUCT(H2:H21,J2:J21)/SUM(J2:J21)

Volatility

With daily returns in column P, annualised volatility over the full sample (252 trading days is the usual convention):

=STDEV.S(P3:P500)*SQRT(252)

Rolling 20-day volatility, at row 22 and filled down: =STDEV.S(P3:P22)*SQRT(252)

In Google Sheets STDEV.S works, and plain STDEV is equivalent for this purpose.

Average true range

Because column F carries the previous close, true range is a single expression with no helper column. At row 2:

=MAX(D2-E2,ABS(D2-F2),ABS(E2-F2))

Then a 14-day ATR is just =AVERAGE(Q2:Q15) over that column, filled down from row 15.

Drawdown

Drawdown measures the fall from the highest point reached so far. In a free column at row 2, note the anchored first reference:

=H2/MAX($H$2:H2)-1

Filling down expands the window automatically, so each row compares against the running peak. The worst drawdown in the sample is then =MIN(R2:R500), and the date it occurred:

=INDEX(A2:A500,MATCH(MIN(R2:R500),R2:R500,0))

Rolling highs and lows

52-week high and low over roughly 250 trading sessions, at row 251:

=MAX(D2:D251) and =MIN(E2:E251)

Distance from the 52-week high, as a percentage:

=H251/MAX(D2:D251)-1

Note these use HIGH and LOW rather than CLOSE, which is the convention — an intraday spike counts.

Volume and participation

Average trade size, which separates block activity from broad flow:

=J2/L2

Today’s volume relative to its own 20-day norm:

=J21/AVERAGE(J2:J21)

A VWAP sanity check — this should land close to column I, and inside the day’s high-low range:

=K2/J2

When comparing activity across different stocks use VALUE (K), not VOLUME (J). Share counts are not comparable between a ₹20 stock and a ₹4,000 one.

Monthly summaries with a pivot table

Add two helper columns — =YEAR(A2) and =MONTH(A2) — then build a pivot with year and month as rows. Useful values:

• Sum of VALUE for monthly turnover;
• Average of DELIVERY % for participation trends;
• Max of HIGH and Min of LOW for the monthly range.

Monthly returns do not come out of a pivot correctly, because you need first and last close rather than an average. Compute those separately with INDEX/MATCH on the first and last date of each month.

Charting

For a price chart, select column A and column H and insert a line chart. Because dates are real date values, the axis spaces sessions correctly.

For a candlestick chart, Excel’s Open-High-Low-Close type requires the columns in exactly that order — copy C, D, E, H into a fresh block in that sequence first, since the export places LTP between LOW and CLOSE.

Plotting volume on the same axis as price will flatten the price line into nothing; put it on a secondary axis or in a separate chart beneath.

Pitfalls worth repeating

• Unadjusted prices. Any return series spanning a split or bonus is wrong until you handle it. Method here.
• Duplicate dates from multiple series will silently corrupt every rolling window. Filter first.
• Blank delivery cells mean not yet published, not zero.AVERAGE ignores blanks correctly, but any formula that treats blank as 0 will understate the result.
• Calendar gaps are normal.Rows are trading sessions, so a “20-day” average spans about four calendar weeks and more across holidays.
• Row counts in these examples are illustrative. Adjust the ranges to your actual data extent.

None of the above is a trading strategy or a recommendation — it is spreadsheet mechanics. See the disclaimer.

Keep reading

• Every column in your NSE export, explained — What OPEN, HIGH, LOW, PREV. CLOSE, LTP, CLOSE, VWAP, VOLUME, VALUE, NO OF TRADES, DELIVERY QTY and DELIVERY % actually mean in an NSE historical data file — and where people misread them.
• NSE series codes: EQ, BE, BZ, SM and ST — Why your export sometimes has two rows for the same date, what each NSE series code means for how a stock trades, and which rows you probably want to keep.
• Delivery percentage: how to read it, and its limits — How NSE delivery quantity and delivery percentage are calculated, what high and low readings suggest about participation, and the cases where the number misleads.

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