How to Track a Stock Portfolio in Excel or Google Sheets (2026): Formulas That Work
Build a stock and ETF portfolio tracker in Excel or Google Sheets: profit including commissions, percent gain, average cost, live prices with GOOGLEFINANCE, STOCKHISTORY or the Stocks data type, totals excluding cash, several brokers in one sheet, and annual return with XIRR. Formulas checked against Microsoft and Google documentation.
9 min readFact-checked against primary sources on · figures re-verified on regulator, issuer or SEC filings — not copied from other sites
How to Track a Stock Portfolio in Excel or Google Sheets
Quick Answer
Keep one Transactions sheet and calculate everything else from it. Each row is one buy or sell: date, broker, ticker, quantity, price, fee. A Holdings sheet sums those rows per ticker and adds a live price.
The formulas that matter:
- Cost including commissions:
quantity × buy price + buy fee - Profit including commissions:
quantity × current price − sell fee − cost - Percent gain:
profit ÷ cost - Live price:
=GOOGLEFINANCE("FRA:VWCE")in Google Sheets; the Stocks data type orSTOCKHISTORYin Excel for Microsoft 365 - Annual return with deposits at different times:
=XIRR(cash flows, dates)
Prices from both are delayed (Google: up to 20 minutes; Excel's STOCKHISTORY generally only after the trading day ends) and not every European listing is covered, so keep a manual-price fallback.
Function syntax checked against Microsoft Support and Google Docs Editors Help on 26 October 2026. The example numbers are illustrative. Not investment advice.
The layout: two sheets are enough
Transactions (one row per trade, never edited afterwards):
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Date | Broker | Ticker | Type | Quantity | Price | Fee |
| 2 | 03.03.2025 | XTB | FRA:VWCE | BUY | 10 | 110.00 | 1.00 |
| 3 | 01.09.2025 | XTB | FRA:VWCE | BUY | 5 | 120.00 | 1.00 |
Holdings (one row per ticker, all formulas):
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Ticker | Quantity | Cost | Avg cost | Price | Value | Profit |
| 2 | FRA:VWCE | 15 | 1,702.00 | 113.47 | 130.00 | 1,950.00 | 247.00 |
Add a Dividends sheet (date, ticker, gross, tax withheld, net) if you hold dividend payers, and an Allocation summary once you have more than a few positions.
The formulas, cell by cell
Quantity held (Holdings!B2): buys minus sells.
=SUMIFS(Transactions!E:E, Transactions!C:C, A2, Transactions!D:D, "BUY")
- SUMIFS(Transactions!E:E, Transactions!C:C, A2, Transactions!D:D, "SELL")
Cost including buy commissions (C2): what you paid, fees included.
=SUMPRODUCT((Transactions!C2:C1000=A2)*(Transactions!D2:D1000="BUY"),
Transactions!E2:E1000*Transactions!F2:F1000 + Transactions!G2:G1000)
In the example: 10 × 110 + 1 + 5 × 120 + 1 = 1,702.00. Use fixed ranges (2:1000) in SUMPRODUCT, not whole columns; whole columns make it slow and the header row breaks the multiplication.
Average cost per share (D2): =C2/B2 → 113.47. Including fees here is what makes the profit figure honest.
Market value (F2): =B2*E2 → 1,950.00.
Profit including commissions (G2): value, minus the fee you'd pay to sell, minus cost.
=F2 - 1 - C2
With a €1 sell fee: 1,950 − 1 − 1,702 = 247.00. Put the sell fee in its own cell (e.g. $J$1) instead of typing 1.
Percent gain (H2): =G2/C2 → 14.5%. Format the cell as a percentage.
Portfolio total excluding cash: if you keep a cash row in Holdings, add a Type column (ETF, Stock, Cash) and sum everything else:
=SUMIFS(F:F, I:I, "<>Cash")
After you sell part of a position: the simple cost formula above keeps all buys in the cost. When you start selling, either reduce the cost by the average cost of the shares sold (sold quantity × D2), which is the average-cost method, or track lots separately. Tax rules decide which method your country uses; your broker's annual statement is the figure that counts for tax.
Made your pick? See how it fits the rest of your money — accounts, cards and investments in one view.
See your Freedom Runway — freeLive prices
Google Sheets: GOOGLEFINANCE
=GOOGLEFINANCE("FRA:VWCE", "price")
- Syntax:
GOOGLEFINANCE(ticker, [attribute], [start_date], [end_date|num_days], [interval]).priceis the default attribute; others includepriceopen,high52,low52,changepct,closeyestandcurrency. - Always include the exchange. Google's help says to use both exchange and ticker ("NASDAQ:GOOG", not "GOOG"); without it, Sheets picks an exchange itself, and for an ETF listed in several places that may be the wrong currency.
- Limits from Google's own help: quotes may be delayed up to 20 minutes; "not all markets are covered" and most international exchanges are not supported; mutual fund attributes are not supported; historical data can't be read through the Sheets API or Apps Script.
- Currency conversion: many sheets use
=GOOGLEFINANCE("CURRENCY:USDEUR")for an exchange rate. It is not documented as a ticker format in the function reference, so test it in your sheet and keep a manual rate as a fallback.
Excel: Stocks data type or STOCKHISTORY
Stocks data type (Microsoft 365 or a free Microsoft account): type the ticker or fund name in a cell, select it, choose Data > Stocks, then pull the price with Add Column > Price, or with a formula such as =A2.Price. Microsoft notes the data is delayed and "not for trading purposes or advice".
STOCKHISTORY (Microsoft 365 subscriptions):
=STOCKHISTORY(stock, start_date, [end_date], [interval], [headers], [property0], ...)
interval: 0 daily (default), 1 weekly, 2 monthly.headers: 0 none, 1 headers (default), 2 identifier and headers. Properties: 0 Date, 1 Close, 2 Open, 3 High, 4 Low, 5 Volume.- Name the exchange with its ISO market identifier code, for example
"XETR:VWCE"for Xetra or"XNAS:MSFT"for Nasdaq. - It "generally only updates after a trading day completes", so it gives the last close, not a live price.
Latest close in one cell: ask for just the Close column without headers over the last week, and take the last value:
=TAKE(STOCKHISTORY("XETR:VWCE", TODAY()-7, TODAY(), 0, 0, 1), -1)
The 7-day window covers weekends and holidays. TAKE is available in Excel for Microsoft 365.
Annual return with XIRR
When you add money at different times, a simple percent gain hides how long the money was invested. XIRR gives the annualised return of irregular cash flows.
List every deposit as a negative number with its date and today's value as a positive number dated today. From the example:
| Date | Cash flow |
|---|---|
| 03.03.2025 | −1,101.00 |
| 01.09.2025 | −601.00 |
| 26.10.2026 | +1,949.00 (value after the sell fee) |
=XIRR(B2:B4, A2:A4) returns about 9.6% a year. The same position shows 14.5% as a simple percent gain, because the money was invested for less than two years.
Microsoft's rules for XIRR: you need at least one negative and one positive value; no date may come before the first date; dates should be real dates, not text. Otherwise you get #NUM! or #VALUE!. Count each deposit to the broker once; if dividends are reinvested inside the account, don't add them as deposits.
Several brokers in one sheet
The Broker column in Transactions does the work. Add it as a condition to any formula:
=SUMIFS(Transactions!E:E, Transactions!C:C, A2, Transactions!B:B, "XTB", Transactions!D:D, "BUY")
or build a pivot table with Broker and Ticker as rows and quantity as values to see what you hold where. Most brokers export a transaction history as CSV or XLSX; paste it into a separate sheet and map its columns to yours, rather than retyping trades.
When the spreadsheet gets too much
Spreadsheets are free and transparent but need upkeep: missing trades, splits and ticker changes break them quietly, and EU listings are not always covered by price functions. Freenance (our product) does the Transactions sheet's job for part of a portfolio: it imports XTB and mBank eMakler trade exports from a file, connects Binance and Bybit by API key, and imports Polish Treasury bond holdings; positions at other brokers (DEGIRO, IBKR, Trade Republic) are still entered by hand. Bank statements import as CSV or MT940, so holdings and spending sit in one net worth, with your Financial Freedom Runway — the number of months your liquid assets would cover your average expenses. The broker and crypto imports start at the 29.99 PLN/month plan, after a 14-day trial.
FAQ
How do I calculate profit on a stock including commissions in Excel?
Profit = (quantity × current price − sell fee) − (quantity × buy price + buy fee). For 15 shares bought for €1,702 including fees and now worth €1,950, with a €1 sell fee: 1,950 − 1 − 1,702 = €247.
What is the formula for percent gain on a portfolio?
Profit divided by cost, including fees: =G2/C2, formatted as a percentage. €247 on a €1,702 cost is 14.5%. For an annualised figure when you invested at different times, use XIRR.
How do I get live stock prices in Google Sheets?
=GOOGLEFINANCE("EXCHANGE:TICKER", "price"), for example =GOOGLEFINANCE("NASDAQ:GOOG"). Quotes may be delayed by up to 20 minutes and not every exchange is covered.
Does Excel have a stock price function?
Yes, in Microsoft 365: the Stocks data type (Data > Stocks) for current quotes, and STOCKHISTORY for daily, weekly or monthly history. STOCKHISTORY generally updates only after the trading day ends.
How do I exclude cash from my portfolio total?
Give each Holdings row a type and sum with a condition: =SUMIFS(F:F, I:I, "<>Cash").
Can one spreadsheet track several brokers?
Yes. Add a Broker column to every transaction and use it as a SUMIFS condition or a pivot-table row. Import each broker's CSV export into its own sheet instead of retyping trades.
Sources
- Microsoft Support: STOCKHISTORY function; Get a stock quote (Stocks data type); XIRR function
- Google Docs Editors Help: GOOGLEFINANCE