You can create an investment portfolio tracker in a spreadsheet by recording each holding, its cost and a current price, then calculating market value and gain or loss. First decide whether you need a snapshot of what you own now or a transaction ledger that preserves purchases and sales over time; the second takes more upkeep but can support more detailed reporting.
Choose what your tracker needs to record
A holdings snapshot is the simplest option: one row per investment, showing what you hold today. It is useful for a quick view of position values, but it does not preserve the history needed to reconstruct buys, sells, or realized activity.
As an Amazon Associate I earn from qualifying purchases.
A transaction ledger records each event. A practical starting set of columns is date, account, ticker or other identifier, transaction type, quantity, price, fees, and currency. Add dividends, cash movements, or multiple-account details only if you intend to maintain and use them. For example, The Measure of a Plan describes a tracker with trade-log and realized-gain reporting, but says cash is not tracked by default (The Measure of a Plan’s tracker).
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
Build a holdings snapshot
Set up one row per position
Create columns for account (if relevant), exchange and ticker or identifier, number of shares, average cost per share, current price, market value, and dollar gain or loss. Keep shares and average cost separate: if you bought the same security at different times, the latest purchase price is not necessarily your average cost.
For each position, calculate market value as shares multiplied by current price. Calculate a basic dollar gain or loss by subtracting the position’s cost basis from its market value. A simple template can start with these inputs and calculations; FinancialAha’s free template, for example, uses one row per position and leaves price updates to the user (FinancialAha’s investment tracker).
Sum the position values for a basic portfolio total. Label this clearly as a snapshot: it may not reflect cash, fees, dividends, currency effects, or other items unless you enter and calculate them separately.
Use percentage returns carefully
Dollar gain alone does not make differently sized positions easy to compare. A percentage can help, but its meaning depends on the method: state whether it includes dividends, fees, contributions and withdrawals, currency conversion, and timing. The available tracker examples handle performance details differently, so there is no single percentage-return formula established here as best for every portfolio.
Build a transaction ledger for history
If you want the sheet to retain activity rather than only show current positions, enter each purchase and sale as a separate row. Use consistent transaction types and identifiers, and include fees and currency when they affect your records. A ledger can provide a basis for reconstructing holdings and reviewing realized activity, but the design and formulas must match your reporting needs.
Decide explicitly whether to include dividends, deposits, withdrawals, cash balances, more than one account, or foreign-currency transactions. Omitting one of these may be fine for a narrow overview; it can make a broader performance report incomplete. Do not treat a tracker’s gain figure as a tax calculation.
Choose how to get prices
Google Sheets price data
Google Sheets offers GOOGLEFINANCE(ticker, [attribute], [start_date], [end_date|num_days], [interval]) for supported securities and historical data. Google recommends including the exchange symbol with the ticker to improve accuracy and reduce discrepancies. Its price quote may be delayed by up to 20 minutes; not all markets are covered, most international exchanges are unsupported, and mutual-fund attributes are not supported. Historical data cannot be retrieved through the Sheets API or Apps Script. See Google’s GOOGLEFINANCE documentation.
Changing spreadsheet recalculation settings does not guarantee a fresher quote. Google’s calculation-settings guidance says external-data functions update on their own schedules and notes that GOOGLEFINANCE may be delayed by up to 20 minutes (Google Sheets calculation settings). Google describes the data as informational, not for trading or advice.
Manual prices and update checks
Manual entry is straightforward, but a stale price makes the resulting value stale too. If you enter prices yourself, record a last-updated date or time and consider a visible manual override for symbols your data source does not support. FinancialAha’s free tracker specifically requires users to enter current prices manually (FinancialAha’s investment tracker).
Decide when a spreadsheet is no longer enough
There is no universally best approach; the trade-offs are setup effort, data coverage, automation, reporting, privacy, and ongoing maintenance.
| Approach | What it can offer | What to check |
|---|---|---|
| Manual holdings spreadsheet | A simple current-position view with manually entered prices and basic value and dollar gain/loss calculations. FinancialAha describes this approach for its free template. | Price upkeep, formula maintenance, and whether you need percentage returns or transaction history. |
| Transaction-ledger spreadsheet | Trade history and, in The Measure of a Plan’s described tracker, realized-gain reports; cash tracking is not included by default. | How it handles cash, accounts, currencies, and the transactions you need to record. |
| Spreadsheet add-on | tradeGIST describes imports and reporting for stocks, crypto, and options within a Sheets workflow. | Supported brokers and instruments, required setup, permissions, data handling, current cost, and whether its reporting meets your needs. These are vendor descriptions, not independent test results. |
| Dedicated service | Sharesight markets coverage for stocks and ETFs on more than 40 exchanges, as well as cash, currencies, and unlisted assets; its undated US product page says up to 10 holdings can be tracked free. | Confirm current coverage, integrations, geography, reporting, privacy terms, and pricing. Both coverage and free-tier details are vendor claims and may change. |
Sources: FinancialAha, The Measure of a Plan, tradeGIST’s Google Workspace Marketplace listing, and Sharesight’s US product page. The descriptions are not independent comparative testing; verify current features, terms, and data coverage before relying on any option.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Check the tracker against your records
- Reconcile holdings, cash, and transactions against broker statements, especially after imports or corporate actions.
- Check that identifiers, share counts, costs, and prices are entered consistently.
- Confirm formulas on a few positions before relying on portfolio totals, and investigate unexpected differences.
- Do not put passwords or full account credentials in a workbook. Before connecting an add-on or service, review its current permissions and data-handling terms. tradeGIST says its portfolio records stay within the spreadsheet, but that is a vendor claim; check the actual permissions for the product you use.
A spreadsheet is a recordkeeping aid, not an investment recommendation or a tax report. Google’s warning on GOOGLEFINANCE and tradeGIST’s statement that it is not tax software are relevant limitations, not substitutes for checking your own records or obtaining appropriate professional advice.
Free tools Windows power users keep installed
One-click scans. No signup required.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
