Nástroje, automatizace a praxe
Portfolio Tracker in Excel or Google Sheets: How to Build One
Key takeaways
- Track at minimum: ticker, number of units, average purchase price, current value, and return in %.
- For automatic prices in Google Sheets use the GOOGLEFINANCE function — no API key required.
- Add a column for each position's portfolio weight so you can see concentration.
- You can only find your true portfolio return via XIRR, not by comparing values directly.
- Update the spreadsheet with every purchase or sale — not retrospectively.
A portfolio tracker is a spreadsheet that shows the total value of your investments, the performance of each position, and the weight of individual assets in real time. No brokerage app gives you that overview when you hold investments across multiple platforms.
Essential tracker columns
- Ticker — fund or stock identifier (e.g. VWCE)
- Number of units — updated continuously
- Average purchase price (in the currency of purchase)
- Current price — automatic or manual
- Current value = units × current price
- Return % = (current value / total invested) − 1
- Portfolio weight % = position value / total portfolio value
Calculate average purchase price as a weighted average: recalculate on every top-up. Formula in Excel: =(old_price × old_units + new_price × new_units) / (old_units + new_units).
Automatic prices in Google Sheets
The function =GOOGLEFINANCE("NASDAQ:VWCE","price") fetches the current price without an API key. For ETFs on Xetra use the prefix ETR:. Get the EUR/CZK rate via =GOOGLEFINANCE("CURRENCY:EURCZK"). The tracker then updates itself every time you open it.
What a tracker does not replace
A simple tracker shows paper gains. True return accounting for the timing of contributions is only measured by XIRR. A tracker does not calculate tax obligations — maintain a separate record for that or use a dedicated application. How to properly benchmark your portfolio against the market is covered in the benchmarking article.
FAQ
What are the best free portfolio trackers?
Google Sheets with GOOGLEFINANCE is the most flexible. Among ready-made apps, Portfolio Performance (free, offline) and Parqet work well. Excel Online is a good option if you already have Office 365.
How do I track a portfolio across multiple brokers?
Add a Broker column to the spreadsheet. You can then filter or aggregate using SUMIF. Alternatively, use a separate tab for each broker and a summary tab that aggregates values.
Do I have to update the tracker manually?
Prices in Google Sheets update automatically via GOOGLEFINANCE. Unit counts, however, must be updated manually after each purchase or sale — no free tool does that for you without API access to your broker.