Building a portfolio tracker from several exports

One export gives you one scrip. A portfolio needs several, combined onto a shared timeline so you can value the whole thing on any given date. The arithmetic is easy. The part that catches people is that your scrips did not all trade on the same days — and naively pasting columns side by side produces a tracker that is quietly wrong from the first no-trade day onwards.

The shape of the thing

Five sheets:

1. One sheet per scrip, each holding a raw export. Name each tab after the scrip code — 500325, not RELIANCE, since codes are the stable identifier and tickers change.
2. A Holdings sheet: what you own and at what cost.
3. A Prices sheet: the dated matrix, one column per scrip.
4. A Value sheet: quantity × price per scrip per date.
5. A Summary sheet for the numbers you actually look at.

Download every scrip over the same requested date range. It will still return different row counts — that is the problem we are about to solve, not a mistake on your part.

Step 1 — Build a date spine

You need one master list of dates: the union of every date any of your scrips traded. Do not use one scrip’s dates as the master unless it is by far the most liquid — you would silently drop days the others traded.

Simplest reliable approach: paste every date column from every scrip sheet into one long column in a scratch area, then use Data → Remove Duplicates and sort ascending. That is your spine in Prices!A.

In Google Sheets you can do it in one formula instead:

=SORT(UNIQUE({'500325'!A2:A; '532540'!A2:A; '500180'!A2:A}))

Then sanity-check it. The count should be close to the number of trading days in your range — around 250 a year. Far fewer means every scrip you picked is illiquid; far more means a date-format problem is creating duplicate-looking entries.

Step 2 — Pull prices onto the spine

With dates down column A and one scrip per column from B, put the scrip code in each header row and look up the close:

=XLOOKUP($A2, INDIRECT("'"&B$1&"'!A:A"), INDIRECT("'"&B$1&"'!E:E"), "")

The INDIRECT lets one formula fill the whole grid by reading the sheet name from the header, so adding a scrip means adding a tab and a column. If you would rather avoid INDIRECT, write it out per column:

=IFERROR(INDEX('500325'!$E:$E, MATCH($A2, '500325'!$A:$A, 0)), "")

Column E is Close. For thin scrips consider column F (WAP) instead — a turnover-weighted average cannot be set by one small trade at the bell, so it is the more robust valuation input. The column reference explains the difference.

Exact match, always. The 0 in MATCH is not optional. An approximate match will happily pair a date with a nearby one and give you a plausible, wrong portfolio.

Step 3 — Decide what a gap means

Your matrix now has blanks wherever a scrip did not trade. This is the decision that determines whether your tracker is right.

A blank does not mean you owned nothing that day. It means no trade set a new price. You still held the shares, and the position still had value — the last known price is the best available estimate of it.

So for portfolio valuation, carry the last price forward. Build a second matrix alongside the first:

=IF(B2<>"", B2, N1)

where N1 is the cell directly above in the carried-forward matrix. Seed the first row with the raw value. Now every date has a price for every scrip, and portfolio value is computable on any date.

Two things to be honest with yourself about:

• Carried-forward prices are estimates, not observations. Add a parallel flag — =IF(B2="","stale","")— so you can see how much of a given day’s valuation is real. If a third of your portfolio is stale on most days, the daily series is decorative.
• Never carry forward for returns or volatility. A carried-forward price produces an artificial 0% return, which understates volatility and drags correlations toward zero. Use carried-forward prices for valuation; use only real observations for risk statistics. This is the distinction people miss.

Step 4 — Holdings

On the Holdings sheet, one row per position: scrip code, name, quantity, buy date, buy price. Keep it flat and simple.

If you bought a scrip in several tranches, either keep one row per tranche (better — it preserves the actual record) or collapse to a weighted average cost:

=SUMPRODUCT(qty_range, price_range)/SUM(qty_range)

Quantity is where corporate actions bite. If a scrip split 1:5 while you held it, your share count multiplied by five on the ex-date and the price divided by five. Since this data is unadjusted, a fixed quantity against a post-split price will understate your position by 80% for the entire period after the split — or overstate it before, depending which number you entered. Either adjust the price history, per the corporate actions guide, or hold quantity as a dated series too. Adjusting the prices is less work.

Step 5 — Value, weights, contribution

On the Value sheet, mirror the price matrix but multiply by quantity:

=IFERROR(Prices_ff!B2 * INDEX(Holdings!$C:$C, MATCH(Prices!B$1, Holdings!$A:$A, 0)), 0)

Then the numbers worth having:

Contribution is the one most people skip and then miss the point. A position that rose 40% but is 2% of the portfolio contributed less than a position that rose 3% at a 30% weight. Weight times return, summed across positions, reconciles to the portfolio return — and it tells you where performance actually came from.

Note it uses yesterday’s weight against today’s return. Using same-day weight double-counts the move and the contributions will not sum correctly.

Step 6 — Checks that catch real errors

Put these on the summary sheet and glance at them:

• Weights sum to 1. =SUM(weights_row). If not, a scrip code in a price header does not match the holdings sheet — usually a code stored as text in one place and a number in the other.
• Contributions reconcile. Sum of contributions should equal the portfolio return for that date, to rounding. If not, the weight-lag is wrong.
• Stale fraction.What share of today’s value comes from carried-forward prices. Above roughly a third, stop treating the daily series as meaningful.
• Coverage per scrip. Rows returned ÷ dates in spine, per scrip. Anything far below 1 is dragging staleness through your whole portfolio and you should know which position it is.
• Largest single-day position move. Sort descending. A clean −50% or −80% is an unadjusted corporate action, not a crash.

What this will not do

Worth stating plainly so you do not discover it at the wrong moment. There is no dividend income here — this data has no dividend column, so returns are price-only and understate what you actually earned. There is no realised-P&L or tax-lot accounting; this is a position tracker, not a book of record. Brokerage, STT and other costs are absent. And every price is end-of-day, so intraday value is out of scope entirely.

For anything with money or a tax filing attached, reconcile against your broker and depository statements. The scope note covers the boundaries, and the disclaimer covers the rest.

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.