By Benjamin Roure L'Hermitte
A spreadsheet to track your crypto is free, it is yours, and it works. Until the day the same coin sits on three platforms, a swap has no obvious purchase price, and the value column has not been refreshed in six weeks.
- A tracking sheet is not a trade journal: it answers what your holdings are worth, not how you traded.
- Six columns are enough, and every extra column is one more entry to keep up over time.
- A spreadsheet's weak spot: the same coin held on two platforms, counted twice or half forgotten.
- Refreshing prices is the real cost: it comes back on every visit, and a price that was not refreshed does not flag itself.
The spreadsheet is the first instinct, and it is a good one. It costs nothing: Excel is free to use on the web with a Microsoft account, as Microsoft's page on Microsoft 365 for the web states. It asks nobody for access to your accounts, and you control every cell. This article gives the minimal structure that survives, then names the three places where it eventually gives way. Not to sell you something by line three, but so you know what to expect.
What a tracking sheet needs, and nothing more
The temptation is to put everything in. That is exactly what dooms the file: the more columns to fill, the higher the cost of every entry, and a spreadsheet nobody fills in is worth nothing. Six columns answer the only question you are really asking, which is what your holdings are worth and what they cost you.
| Column | What it holds | Why it is essential |
|---|---|---|
| Asset | BTC, ETH, SOL… | The grouping key. One row per asset AND per platform, never one row per purchase |
| Quantity | Units held | What you actually own, decimals included |
| Platform | Exchange, wallet, cold storage | Without it you cannot know where your capital sits, nor reconcile a balance |
| Unit cost basis | What one unit cost you, on average | The one column that turns a value into a result |
| Current price | The market price now | The column that goes stale, and therefore must be refreshed |
| Value | Quantity × price | Computed, never typed |
Everything else follows from those six. Unrealised P&L is value minus total cost. A row's weight is its value over the total. Percentage performance is P&L over cost. If you catch yourself typing a value a formula could produce, that is the sign the file is about to drift away from reality.
One optional column earns its place more than you would think: the date the row was last checked. It plays no part in the maths; it tells you how much to trust the total on screen.
Trap 1: the same coin on two platforms
This is the first flaw to watch for once a coin sits in two places. You hold ether on an exchange and ether on a hardware wallet (what that choice changes for the custody of your funds is explained in the basics of crypto trading). Two rows, then. From there, three errors become possible.
- Double-counted weight. Each row computes its share of the total on its own. Ether shows up twice, at 12% and 9%, and nowhere do you read that you are in fact 21% exposed to a single asset.
- Cost basis cut in half. Each row carries its own average cost. Neither tells you what your whole position cost, which is the only thing that matters when you decide to sell.
- The transfer that invents a gain. You move crypto from the exchange to cold storage. Create a new row at today's price instead of carrying the original cost basis, and your sheet reports a gain that never happened, erasing the position's real performance.
The spreadsheet fix exists: one holdings sheet per platform, and a summary sheet aggregating by asset with one SUMIF per sheet, the function that sums the values in a range that meet a criterion (Excel's SUMIF function). Total cost sums, total quantity sums, and consolidated cost basis is the ratio of the two. It works. It simply requires never forgetting to carry a new row through to the summary, and that is where discipline wears out. In a free crypto portfolio tracker where you enter each asset once, with its whole quantity and cost, there is nothing left to aggregate.
Trap 2: cost basis after a swap
As long as you buy with cash, cost basis is obvious: amount paid divided by quantity received. The difficulty starts when you swap one crypto for another. You convert SOL into ETH: what is the cost basis of the ETH you just received?
International accounting standards answer it this way: an asset received in exchange for another is measured at the fair value, meaning the market value, of the one you gave up, at the time of the exchange (IAS 38, paragraphs 45 to 47). That is the standard the IFRS Interpretations Committee applies to cryptocurrencies that are not held for sale in the ordinary course of business (its June 2019 decision, Holdings of Cryptocurrencies). Your new ETH cost you whatever your SOL was worth at that instant, and the gain or loss on the SOL is recorded separately. Carry the SOL's historical cost basis across instead, and the ETH's displayed performance blends two positions. This is a tracking convention, not a tax rule.
Fractional buys are simpler but just as easy to get wrong. Three purchases of the same coin at three prices do not make three rows: they make one row whose cost basis is the quantity-weighted average, never the average of the three prices. This is the weighted average cost formula, one of the two that IAS 2, paragraphs 25 and 27 describes, alongside first-in, first-out. Example: 0.5 ETH at $2,000, 1 ETH at $1,600 and 0.5 ETH at $2,400 cost $3,800 for 2 ETH, or $1,900 each, whereas the average of the three prices gives $2,000.
Trap 3: prices, which go stale
The most ordinary one. A spreadsheet does not know the price of bitcoin on its own: either you type it in, or you fetch it through a price function or an add-in. Each route has its flaw.
| Method | What it costs | Where it breaks |
|---|---|---|
| Typing it in | A few minutes on every visit | It only holds if you redo it every time. Otherwise the total silently goes wrong |
| Built-in price function | Nothing upfront | Documented for stocks, funds and currencies, with no mention of crypto. Prices delayed, provided as is |
| Add-in calling a price API | An API key to manage, a call limit, an add-in to install | Without the add-in the formula shows #NAME?. Past the limit it shows a rate-limit error |
What the documentation says. Microsoft presents its Stocks data type for a ticker symbol, a company name or a fund name, with information it describes as delayed and provided "as-is" (Excel help on stock quotes). Google states that the GOOGLEFINANCE function may be delayed by up to 20 minutes. Neither page mentions a cryptocurrency. CoinGecko's add-in does cover them, but it needs an API key and returns a rate-limit error once the call limit is exceeded (CoinGecko's documentation for Excel).
What the three share: a price that was not refreshed does not flag itself. In Excel, Microsoft points to Data, Refresh All to make sure a quote is up to date (Excel help on exchange rates); in Google Sheets, the CoinGecko add-on's formulas stay cached for one to two hours (CoinGecko's documentation for Google Sheets). The cell keeps its last value, the total still looks plausible, and you can end up deciding on an old number. A spreadsheet only dates a price if you add a column for it.
When a spreadsheet is enough, and when it stops being
There is no universal answer, but there is a fairly clear threshold. The spreadsheet stays the right tool as long as you hold few rows, on one or two platforms, and look at your portfolio occasionally. The same holds for trades: a trade journal Excel template carries you until the row count wins. It stops being right when one of three things happens.
- You hold the same asset in more than two places and can no longer state your total exposure to it from memory.
- You have started swapping cryptos, so your cost basis depends on maths you will not redo by hand.
- You open the file and your first thought is that you need to update the prices, rather than a question about your portfolio.
If that is where you are, the next step is not necessarily to pay for anything. Tradoshi's crypto portfolio tracker takes over this work, free and on every plan: you enter what you hold by hand, cold storage included, with what the position cost you in total if you know it, and prices, value, each asset's weight and unrealised P&L are computed for you. Enter each asset once, with its whole quantity and cost: Trap 1 has nothing left to bite on, since there is nothing left to aggregate.
One point worth stating: this tracker needs no trading statement. You have nothing to import and no account to connect in order to use it.
Do not confuse portfolio tracking with a trading journal
The two look alike from a distance and answer opposite questions. Portfolio tracking looks at what you hold: its value, its cost, its split. A trading journal looks at what you did: your entries, exits, discipline and edge.
A buy-and-hold investor only needs the first. An active trader needs both, and cramming everything into one tab blurs the two. If you place trades, the journal side has its own demands, covered in our crypto trading journal guide: 24/7 markets, perpetual funding, maker and taker fees.
Frequently asked questions
Is a spreadsheet enough to track a crypto portfolio?
Yes, as long as you have few rows, one or two platforms, and accept updating prices yourself. It stops being enough when the same asset is spread across several places, or when you swap cryptos, because cost basis becomes maths to redo on every move.
How do I compute cost basis after several purchases?
As a quantity-weighted average: total paid divided by total quantity received. That is the weighted average cost formula. Never the arithmetic mean of the purchase prices, which is wrong as soon as the quantities bought differ.
How do I calculate crypto profit in Excel?
With the six columns in the order of the table, A to F, a row's value is =B2*E2, its total cost is =B2*D2, and unrealised profit is the difference between the two, =F2-B2*D2. For a percentage, divide that profit by total cost. For the whole portfolio, sum the values and the costs first, then run the calculation on the two totals: averaging each row's percentage treats a $100 position like a $10,000 one. This is unrealised profit for tracking, not a tax calculation.
One row per purchase or one row per asset?
One row per asset and per platform, whose cost basis is updated on each new purchase. One row per purchase bloats the file and makes each asset's weight unreadable, which is precisely the information you came for.
Does Tradoshi replace my spreadsheet?
It takes over the same job with less maintenance: you enter what you hold, cold storage included, and prices, value, each asset's weight and unrealised P&L are computed. Cost basis stays the figure you enter. It is free on every plan.
Does this article help me file my crypto taxes?
No, and that is deliberate. Filing is a tax matter, its rules depend on the country where you are taxed, and it is not computed like a tracking sheet. In the United States, for example, the IRS treats exchanging one virtual currency for another as a disposal that creates a capital gain or loss, applies first in, first out when you do not identify the units sold, and has you report on Form 8949 (IRS FAQ on virtual currency transactions).