This guide walks you through building an investment tracking spreadsheet from scratch using Excel or Google Sheets. When you finish, you will have a working workbook with a holdings register, automatic gain/loss calculations, cash and dividend tracking, and a portfolio summary you can update in a few minutes each month. The guide is written for individual investors with a handful of accounts — brokerage, retirement, and taxable — who want a clear, single view of everything they own without paying for portfolio software.
Get smart everyday buys delivered free — and shop member deals
- Fast, free delivery on millions of items
- Access to Prime Big Deal Days deals on October 6–7
- Prime Video, Amazon Music and more included
Minimalist Investment Tracker: Portfolio and Investment Log Book for Tracking Stocks, ETFs, Dividends, Profit and Loss, Wealth Gr…
- ✔ Format: Paperback log book
- ✔ Tracks: Stocks, ETFs, dividends, profit and loss
- ✔ Planning Features: Wealth growth and financial planning pages
Investments Tracking Log Book & Journal
- ✔ Format: Log book and journal
- ✔ Tracks: Savings accounts, stocks and shares, cryptocurrencies (purchased and staked)
- ✔ Planning Features: None — focused on record keeping
No advanced spreadsheet skills are required. If you can enter data in cells and copy a formula down a column, you can complete this. Setup takes two to three hours; ongoing maintenance takes 15 to 30 minutes a month.
Difficulty: Beginner | Time: 2-3 hours initial setup; 15-30 minutes per month to maintain
What You’ll Need
Tools & Materials:
- Microsoft Excel (2016 or later) or Google Sheets (free with a Google account)
- Your most recent brokerage account statements (one per account)
- Trade confirmations or account transaction history for the past 12 months (available from your brokerage website)
- A list of all accounts you intend to track, including retirement and bank-held investment accounts
Knowledge:
- Basic spreadsheet use: entering data, selecting cells, copying formulas
- Knowing the difference between a ticker symbol and an account (one account can hold many tickers)
- Understanding basic investing terms: shares, cost basis, dividend, market price
Decide before you start whether this spreadsheet will track investments only or net worth including cash and property. This guide covers investments plus cash balances. If you want a full net worth view, add a simple assets-and-debts sheet at the end using the same structure. Also decide on a currency: if you hold investments in more than one currency, add a Currency column from the beginning — retrofitting it later is error-prone.
Minimalist Investment Tracker: Portfolio and Investment Log Book for Tracking Stocks, ETFs, Dividends, Profit and Loss, Wealth Gr…
The Minimalist Investment Tracker earns our top spot because it treats investing as an ongoing project rather than a series of disconnected entries. Where the Log Book & Journal focuses on recording what you own, this option pushes further into profit and loss tracking, dividend logging, and wealth-growth planning, giving you a paper trail of how your net worth is actually trending over time.The minimalist framing is a real strength for buyers who find dense financial journals overwhelming. Entry pages are structured around the questions most long-term investors ask — what did I buy, what did it earn, and how has the total changed — rather than exhaustive fields for every conceivable asset detail. Compared with the Investments Tracking Log Book, this makes it better suited to planning-focused investors holding conventional assets like stocks and ETFs, and less suited to someone juggling staked crypto.The tradeoff is coverage. If your portfolio includes digital assets or multiple savings vehicles, the Log Book & Journal handles that variety more gracefully. This pick makes the most sense for the investor whose priority is seeing wealth growth over years, not cataloguing every asset type.
Pros:
- Combines portfolio logging with profit and loss tracking in one place
- Dedicated dividend tracking supports income-focused investors
- Wealth-growth pages encourage long-term financial planning
- Minimalist layout keeps entries quick and unintimidating
Cons:
- No explicit support for cryptocurrencies or staked assets
- Manual entry means every figure must be updated by hand
- Less page depth for very large, multi-account portfolios
Best for: Long-term stock and ETF investors who want dividends and portfolio growth tracked in one structured, planning-oriented book
Not ideal for: Crypto-heavy investors or those with many unconventional assets the format wasn’t designed around
Bottom line: The most complete all-round pick for conventional investors who want planning features, not just a transaction log.
“The most complete all-round pick for conventional investors who want planning features, not just a transaction log.”
Investments Tracking Log Book & Journal
Where our top pick leans toward planning, the Investments Tracking Log Book & Journal leans toward coverage — and that difference defines who should buy it. This is the rare paper tracker built for the reality of modern portfolios: it explicitly accommodates high-yield savings accounts, stocks and shares, purchased cryptocurrencies, and staked assets in one organized format.That crypto and staking support is the standout differentiator. Compared with the Minimalist Investment Tracker, which assumes a conventional stock-and-ETF portfolio, this option stands out for investors whose holdings span exchanges, wallets, and banks. It functions as a centralized master record — useful for tax time, estate organization, or simply remembering where everything sits when an app login fails.The honest tradeoff is that it is a logger, not a planner. You will not find wealth-growth summaries or dividend-income projections here, so investors focused on long-range planning should default to our top pick instead. And like any paper system, limited page space means a very active trader could fill it quickly. For the moderately-sized, multi-asset portfolio, though, it hits a sweet spot the Tracker does not.
Pros:
- Explicitly tracks crypto, including purchased and staked assets
- Covers everything from savings accounts to shares in one book
- Simple, organized format makes consolidation effortless
- Fully offline — no platform, subscription, or app dependency
Cons:
- Manual updates required for every position
- Limited planning or wealth-growth features
- May run out of space for large or highly active portfolios
Best for: Investors with mixed portfolios spanning cash, stocks, and crypto who want one offline master record
Not ideal for: Investors who want growth projections, dividend summaries, or other planning features
Bottom line: The clear choice for multi-asset investors, especially crypto holders, who value a centralized offline record over planning tools.
“The clear choice for multi-asset investors, especially crypto holders, who value a centralized offline record over planning tools.”
As an Amazon Associate we earn from qualifying purchases.
Before You Start
Gather every statement first and open your brokerage websites before opening the spreadsheet. Most setup failures happen because people start entering data, realize they are missing an account, and abandon a half-built file. Write a quick list on paper: every account, the institution, the account type, and its current value. Check it against your email for statements from the past year — forgotten old 401(k) plans are the most commonly missed accounts.
One warning on privacy: this spreadsheet will contain your complete financial picture. Store it in a folder you control, do not email it to yourself unencrypted, and do not store it on a shared computer. If you use Google Sheets, protect the file with two-factor authentication on your Google account.
Finally, decide on your snapshot date — the date you will treat as your starting point, usually the last day of last month. Every beginning balance you enter should come from that same date or your totals will not reconcile.
Step-by-Step Instructions
Step 1: Create the workbook and four base sheets
Open a new spreadsheet file and name it something you will recognize, such as Investments 2025. Create four separate sheets (tabs at the bottom) and name them exactly: Holdings, Transactions, Cash & Accounts, and Summary. Rename the default ‘Sheet1’ rather than leaving generic names, because formulas later will reference these sheet names and typos in tab names are the most common formula error.
Tip: In Google Sheets, double-click the tab name to rename. In Excel, right-click the tab and choose Rename.
Check: You see four tabs at the bottom of the workbook, each with the correct name and no spelling errors.
Step 2: Build the Holdings sheet with column headers
On the Holdings sheet, enter these headers in row 1, one per column: Account, Ticker, Asset Name, Asset Type, Shares, Cost Basis per Share, Total Cost, Current Price, Current Value, Gain/Loss $, Gain/Loss %. Select row 1 and apply bold formatting, then freeze the top row (View → Freeze → 1 row in Google Sheets; View → Freeze Panes in Excel) so headers stay visible as you scroll.
Use a consistent Asset Type vocabulary from a short list — Stock, ETF, Mutual Fund, Bond, Crypto, Other — typed identically every time. This consistency is what lets you subtotal by asset type later without fixing mismatches.
Check: Row 1 shows all eleven headers in order, is bold, and stays visible when you scroll down.
Step 3: Enter your holdings from your statements
Open your first brokerage statement and enter one row per holding: the account name (use a short code like ‘Fidelity-Taxable’ that you will reuse everywhere), ticker, asset name, asset type, share count, and cost basis per share. Work through every statement account by account. Leave the Current Price column empty for now.
For mutual funds, use the fund ticker and enter the share count exactly as shown on the statement, including decimals. For cost basis per share, divide the total cost shown on your statement by the share count if your broker only reports the total — and note in a comment if you estimated.
Tip: Enter the account name identically in every row for the same account. ‘Fidelity’ in one row and ‘Fid-Taxable’ in another will break your account-level subtotals later.
Check: Every holding from every statement has its own row, and the share counts on screen match the statements when you spot-check three random rows.
Step 4: Add the calculation formulas and copy them down
Assuming your first data row is row 2, enter these formulas: in H2 (Current Value) type =E2*G2… but first make sure column letters match your layout — Current Value is Shares times Current Price: =E2*I2 is wrong if Current Price is column H. Work left to right: Total Cost (G2) = =E2*F2. Current Value (I2) = =E2*H2. Gain/Loss $ (J2) = =I2-G2. Gain/Loss % (K2) = =IF(G2=0,0,J2/G2).
Then select cells G2 through K2 on the first data row, copy them, select the same columns down to your last data row, and paste. All rows should calculate at once.
Tip: The IF wrapper on Gain/Loss % prevents a divide-by-zero error on holdings with zero cost basis, such as transferred shares without basis records.
Check: Every row shows numbers, not error codes, in columns G through K. Pick one row and multiply shares by price on a calculator to confirm the value matches.
Step 5: Enter current prices and format currencies
Look up each ticker’s current price on your brokerage site or a free quote site and type it into the Current Price column. Prices older than a day are fine for monthly tracking — precision matters far less than consistency. After entering prices, select columns F, G, H, I, and J and apply currency formatting, then format column K as a percentage with one decimal place.
Google Sheets users can try typing =GOOGLEFINANCE(“AAPL”) in the Current Price cell to pull live prices; this works for many US-listed stocks and ETFs but not mutual funds or some international listings. If it returns an error, just type the price manually.
Check: Current Value and Gain/Loss columns populate with plausible numbers, and no cell shows #ERROR or #REF.
Step 6: Build the Transactions log
Go to the Transactions sheet and enter headers in row 1: Date, Account, Ticker, Action (Buy / Sell / Dividend / Transfer), Shares, Amount, Notes. Enter your transactions going forward from your snapshot date — every buy, sell, and dividend from now on gets a row here. Format the Date column as a date and the Amount column as currency.
This sheet is your audit trail. When a number in Holdings stops matching your statement, this log tells you which transaction was missed.
Check: The sheet has seven labeled columns and at least your snapshot date recorded as a starting reference if you wish; the date column displays real dates, not text.
Step 7: Build the Cash & Accounts sheet
Create a simple table: Account, Institution, Account Type (Taxable / IRA / 401k / Other), Cash Balance, Snapshot Value, Current Investment Value. For the last column, use a SUMIF that pulls from Holdings: in F2, enter =SUMIF(Holdings!A:A, A2, Holdings!I:I), which adds up the Current Value of every holding whose Account matches this row’s account name. Copy it down for each account row.
Check: Each account’s Current Investment Value roughly matches the total value shown on that account’s most recent statement (within a few percent, since prices move).
Step 8: Build the Summary sheet with portfolio totals
On the Summary sheet, create labeled rows and pull totals with formulas: Total Portfolio Value = =SUM(Holdings!I:I); Total Cost = =SUM(Holdings!G:G); Total Gain/Loss = total value minus total cost; Total Cash = =SUM(‘Cash & Accounts’!D:D); Grand Total = portfolio value plus cash. Below that, add a breakdown by asset type: for each type, use =SUMIF(Holdings!D:D,”ETF”,Holdings!I:I) and divide by total value to get your allocation percentages.
Add a monthly history table at the bottom: one row per month-end with the date and the Grand Total copied as a value (paste special → values only). Over time this becomes your performance chart data.
Check: The Grand Total is within a few percent of the sum of your brokerage statement totals, and your allocation percentages add up to approximately 100%.
Step 9: Reconcile against your statements and finish
Compare each account’s value on the Summary and Cash & Accounts sheets against the same account’s current value on the brokerage website. Investigate any difference over 2%. The usual causes are a mistyped share count, a missing holding, or a stale price. Fix the data — never adjust formulas to force a match. When every account lines up, save the file and, if it lives on your computer, set a reminder to back it up or keep a copy in cloud storage.
Check: Every account reconciles within 2% of the live brokerage value, and you have saved a backup copy of the file.
Common Mistakes to Avoid
- Forgetting accounts entirely, especially old 401(k) plans or small DRIP accounts — Before entering data, list every institution that has sent you a statement in the past year and check the list against your email and tax return (1099 forms reveal forgotten accounts).
- Inconsistent account or asset-type names that break SUMIF totals — Type account names and asset types identically every time, or create a dropdown list using Data Validation so entries are forced to match.
- Overwriting formulas with typed numbers when updating prices — Only type into the Current Price column. Columns G through K on Holdings are formula columns — if you accidentally overwrite one, copy the formula from the row above.
- Recording the total portfolio value on the wrong date each month, making the history table misleading — Pick a fixed date — the last business day of the month — and always update prices and record the snapshot on that date.
Troubleshooting
Problem: A formula shows #REF! or #NAME? instead of a number
Solution: #REF! means a referenced cell was deleted — undo if possible or rebuild the formula. #NAME? usually means the sheet name in the formula has a typo or is missing quotes around a name with spaces, such as ‘Cash & Accounts’. Check that tab names match the formula exactly.
Problem: Summary total does not match the sum of your brokerage statements
Solution: Work account by account on the Cash & Accounts sheet to isolate the mismatch. Most often a holding row is missing, a share count has a decimal in the wrong place, or one account name was misspelled so its holdings fall outside the SUMIF range.
Problem: GOOGLEFINANCE returns an error for a ticker
Solution: This function does not cover mutual funds or some international tickers. Replace it with a manually typed price from your brokerage site, and update it on your monthly snapshot date like any other holding.
Problem: Gain/Loss numbers look wrong after entering a cost basis
Solution: Check that you entered cost basis per share, not total cost, in column F. If your statement shows only the total, divide by shares first, or change the formula in G2 to use your total directly and remove the per-share multiplication.
What Success Looks Like
Your spreadsheet is working correctly when all of the following are true: (1) every account you own appears on the Cash & Accounts sheet and each one’s Current Investment Value reconciles within about 2% of the live value on the brokerage website; (2) the Summary sheet’s Grand Total equals total investments plus total cash; (3) allocation percentages by asset type sum to roughly 100%; (4) updating a single price on the Holdings sheet immediately flows through to the Summary total; and (5) you have recorded your first month-end snapshot in the history table. If all five hold, the workbook is structurally sound and ready for monthly use.
Next Steps
Set a recurring calendar reminder for the last business day of each month: update prices, log the month’s transactions from the Transactions sheet into any needed Holdings adjustments (share counts change when you buy or sell), record the month-end total in the history table, and reconcile against each statement. After three months of snapshots, add a simple line chart on the Summary sheet using the history table to visualize growth. Once a year, before tax season, use the Transactions sheet to cross-check the 1099 forms your brokers send — the dividend totals should match. If your portfolio grows past roughly thirty positions across many accounts, or you want automatic trade imports, that is the point to consider dedicated portfolio software; until then, this spreadsheet will do the job.
Frequently Asked Questions
Should I use Excel or Google Sheets for this?
Either works — every formula in this guide exists in both. Google Sheets is free, backs up automatically, and offers the GOOGLEFINANCE function for live prices, which makes it the easier starting choice. Excel is better if you want offline access, more powerful charting, or already own a license. Pick one and stay with it; converting between them mid-year invites formula errors.
How often do I need to update prices?
Monthly is enough for long-term tracking. The purpose of the spreadsheet is seeing your overall position and allocation trend, not day-trading precision. Pick the last business day of each month, update all prices at once, and record the snapshot. Updating more often adds work without adding insight for most investors.
How do I handle dividends that are reinvested automatically?
Log the dividend in the Transactions sheet with the date, ticker, and amount. Then, when you do your monthly update, add the reinvested shares to the share count on the Holdings sheet and add the dividend amount to your total cost basis (recompute cost per share as total cost divided by new share count). This keeps your gain/loss calculations accurate and creates a record that matches your 1099-DIV at tax time.
What about holdings in multiple currencies?
If you hold US and non-US investments, add a Currency column on the Holdings sheet and a Current Price column that is always in your home currency. Look up the converted price directly rather than maintaining exchange-rate formulas — a monthly manual lookup is simpler and less error-prone for a handful of positions.
Can I track performance against an index like the S&P 500?
Yes, once you have several months of history. Add a column to your monthly history table for the S&P 500’s level on the same date (widely available free online). Comparing the percentage change in your portfolio against the percentage change in the index over the same months gives a rough benchmark check without needing time-weighted return formulas.
Fall Picks
fall essentials
As an affiliate, we earn on qualifying purchases.
