One export gives you one stock. A portfolio needs several, combined onto a shared timeline so that every date lines up across every holding. That combining step is most of the work, and doing it carelessly produces a tracker that looks right and reports nonsense.
This builds a historical portfolio value series from scratch. It is a record-keeping and analysis exercise, not a recommendation about what to hold.
Step 1: download each holding
One download per stock, all over the same date range. Use the earliest date across your whole portfolio as the start, even for stocks you bought later — you can trim afterwards, but you cannot invent data you did not download.
Put each stock on its own sheet, named for the symbol: RELIANCE, TCS, HDFCBANK. Keep the exports untouched. Every calculation below happens on new sheets that reference these, so you always have the raw data to check against.
Step 2: build the date spine
This is the part that determines whether the tracker works. You need one authoritative list of dates that every stock will be looked up against.
Do not use one stock’s dates and assume the rest match. Different stocks trade on different days — suspensions, illiquidity, listing dates and series changes all cause divergence. Two exports over the same calendar range routinely have different row counts. Why that happens.
On a new sheet called Prices, put the dates from your most liquid holding in column A — a large-cap that almost certainly traded every session. That becomes the spine. Then label columns B onward with your symbols.
In B2, look up that stock’s close for that date:
=IFERROR(VLOOKUP($A2,RELIANCE!$A:$H,8,FALSE),"")
Column 8 is CLOSE. Or with XLOOKUP:
=XLOOKUP($A2,RELIANCE!$A:$A,RELIANCE!$H:$H,"")
Note the $A2 — the column is anchored, the row is not, so you can fill right across symbols and down across dates. Change the sheet name in each column to match its symbol.
Check the join before continuing
Blanks mean that stock did not trade on that date. A few are fine. Count them per column:
=COUNTBLANK(B2:B500)
If a column is heavily blank, either that stock genuinely was not trading or your export covers a different range. Investigate before proceeding — a broken join here silently propagates into every number downstream.
For occasional blanks, carrying the previous price forward is the standard treatment: the position still exists, it simply did not reprice. Wrap the lookup so it falls back to the row above:
=IFERROR(VLOOKUP($A2,RELIANCE!$A:$H,8,FALSE),B1)
Be conscious of what this does: it creates artificial zero-return days. For valuation that is correct. For volatility it understates risk. If you are measuring risk rather than value, exclude carried-forward rows instead of smoothing over them.
Step 3: record your holdings
On a sheet called Holdings, one row per position:
| A — Symbol | B — Quantity | C — Buy date | D — Buy price |
|---|---|---|---|
| RELIANCE | 50 | 2025-01-15 | 1240.50 |
| TCS | 25 | 2025-03-02 | 3890.00 |
Use your actual contract-note prices, not the day’s close. They differ, and the gap is your real execution cost. Keep brokerage and taxes in a separate column if you want a true cost basis — they are not negligible over many trades.
Step 4: portfolio value over time
Back on Prices, in a column to the right of your symbols, multiply each day’s prices by the quantities held:
=SUMPRODUCT(B2:D2,TRANSPOSE(Holdings!$B$2:$B$4))
This assumes the symbol columns are in the same order as the holdings rows. Verify that once — a mismatch here is invisible and produces confidently wrong numbers. In older Excel, confirm with Ctrl+Shift+Enter.
If different positions were bought on different dates, gate each holding’s contribution by its buy date rather than valuing shares you did not yet own:
=IF($A2>=Holdings!$C$2,B2*Holdings!$B$2,0)
One column per holding, then sum them. More columns, but far easier to audit than a single clever formula — and you will want to audit this.
Step 5: the numbers worth having
With portfolio value in column P:
- Daily return:
=P3/P2-1 - Total return:
=P500/P2-1 - Annualised volatility:
=STDEV.S(Q3:Q500)*SQRT(252) - Drawdown from peak:
=P2/MAX($P$2:P2)-1, worst value via=MIN(...) - Current weight of a holding:
=B500*Holdings!$B$2/$P$500
Weights are the most immediately useful output. Positions drift: a winner quietly grows into an outsized share of the portfolio, and most people have no idea how concentrated they have become until they compute it.
For contribution — how much each holding moved the whole portfolio — multiply the holding’s return by its weight at the start of the period. Contributions sum to the portfolio return, which makes it easy to check your arithmetic.
What breaks this
- Corporate actions. The most damaging failure here. A split halves the price in your data but your quantity stays fixed, so the tracker reports a loss that never happened. You must adjust the quantity on the ex-date, or adjust the price history. Method. Bonus shares are the same problem in reverse.
- Multiple series producing duplicate dates. VLOOKUP returns the first match, so you may silently get the wrong row. Filter each export to one series before building the spine. Details.
- Dividends are missing. Cash you actually received does not appear anywhere in price data. Your tracker understates real returns.
- Costs are missing unless you add them — brokerage, STT, stamp duty, and tax on gains.
- Sold positions. This design assumes you still hold everything. Closed positions need an end date and separate treatment.
- Adding money confuses returns. If you contribute cash mid-period, simple value-based returns conflate deposits with performance. You need a time-weighted return to separate them — a bigger topic than this guide.
Keeping it current
Re-download each holding periodically over the same start date and repaste into the symbol sheets. Because everything downstream references those sheets by position, the whole tracker recalculates. Monthly is plenty for most people.
Reconcile against your broker statement the first time you build this. Values will not match to the rupee — closing prices, corporate actions and costs all differ — but they should be close. A large discrepancy means a join or a quantity is wrong, and it is far better to find that now.
This is a record-keeping tool. It is not investment advice, and nothing here is a recommendation to buy, sell or hold anything — 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.