A coin cannot be sold or withdrawn before it is in the account. So when a running balance in your ledger drops below zero, the row where it turns negative is usually fine. Something is missing above it: an inflow or an opening balance that never made it into the sheet. Less often, rows are simply in the wrong order.
You do not need the exchange balance to start. Split the ledger by account and asset, sort oldest to newest, compute a running balance line by line, and note two things: the first negative row and the lowest value the balance reaches. The walkthrough below uses an illustrative account called “account B”, holding ETH, with all times in UTC.
Compute the running balance: where it first dips, how low it goes
The running balance answers one question per row: how much does the ledger think you held after this line? Previous balance, plus inflow, minus outflow and any fee listed on its own. If one sheet mixes accounts or assets, separate them first; an inflow in one account can hide a negative in another.
| Time (UTC) | Entry | Change | Running balance |
|---|---|---|---|
| 10-01 00:00 | Opening (September statement) | +0.5000 | 0.5000 |
| 10-03 08:14 | Buy | +0.3000 | 0.8000 |
| 10-08 02:40 | Withdrawal | −0.7500 | 0.0500 |
| 10-12 13:05 | Sell | −0.2500 | −0.2000 |
| 10-14 09:31 | Buy | +0.1000 | −0.1000 |
| 10-20 17:48 | Sell | −0.0500 | −0.1500 |
| 10-27 06:02 | Buy | +0.4000 | 0.2500 |
Two readings come out of this table. The balance first goes negative at 10-12 13:05, so the missing inflow happened before that moment. The lowest point is −0.2000, so at least 0.2000 ETH is missing. Treat that as a floor, not the answer. The real gap can be bigger; the balance simply never fell further because later outflows were small.
Month-end gives you a second check. The ledger ends at 0.2500. If the exchange shows 0.4500, the difference is 0.2000, the same as the floor, which points to a single missing entry. If the exchange shows more than 0.4500, there is a further gap that never pushes the balance below zero, and you will need to find the day the difference started instead.
Tax and portfolio apps flag these rows too. Koinly’s explanation is blunt: the software can point to the transaction that results in a negative balance, but the missing one has to be found by comparing against the exchange’s balance ledger by hand (source below). The same goes for a spreadsheet. Editing the negative row moves you in the wrong direction.
Negative from the first outflow of the period: check the opening balance
If the balance goes negative on the very first sell or withdrawal and never climbs back above zero, the usual cause is an opening quantity that was left out or entered as zero. Drop the 0.5000 opening row from the example and the withdrawal on 10-08 lands at −0.4500.
Resist filling the gap by working backwards from the closing balance. A back-solved figure absorbs every other omission in the month, so the sheet balances while the problems stay hidden. How to log a pending opening balance and where to find dated evidence is covered in Missing Opening Balance in Your Crypto Ledger.
Positive for a while, then negative: the inflow sits in another file or account
This is the example’s pattern. The first rows are positive, the balance breaks below zero on 10-12 and stays there for several rows. You are looking for an inflow of at least 0.2000 that happened before 10-12 13:05. Checking these places in order is faster than rereading the month:
- a transfer in from another account on the same platform, such as a funding or earn account, which is invisible if you only exported the trading account (see month-end account scope and internal transfers);
- a conversion recorded in its own history and never merged into the trade file (see Convert missing from your spot file);
- a deposit from another exchange or your own wallet, where the sending side is outside this ledger;
- rewards, cashback or airdrops, which often live on a separate page or export;
- a ticker change, with the inflow booked under the old code and the outflow under the new one (see linking old and new tickers).
In the example, the funding account’s transfer history turns up one line: 10-10 21:16, 0.2000 ETH moved in. With that row added, the balance after the 10-12 sale is 0.0000 and month-end becomes 0.4500. Sold out, not short.
The opposite can also happen: an outflow counted twice. When two export files overlap by a few days, the same sale enters the ledger twice (see overlapping dates in export files). Around the first negative row, look for two lines with the same time, quantity and order ID. Confirm the duplicate before deleting one, and keep the original files untouched.
Negative for one row, positive on the next: suspect the sort order
A single negative row followed immediately by a positive one, with both rows at the same second or a few seconds apart, rarely means a missing entry. More likely a sell got sorted ahead of the buy that funded it. Common ways this happens:
- a buy and a sell in the same second, reshuffled when the sheet was sorted by time;
- an export listed newest first, with one chunk left unreversed when files were joined;
- one file in UTC and another in local time, so entries from the same day interleave wrongly after merging (see CSV dates a day apart).
Give the sort a stable tiebreaker: time first, then the row number from the original file, and convert every file to one time zone before merging. If the negative disappears after re-sorting, nothing needs to be added.
A tiny negative, such as −0.00000002
When a balance that should be zero after selling or withdrawing everything stops just below zero, the cause is rarely a missing inflow. Look at two things. Was the fee deducted twice, once inside a net quantity and again as its own line (see which asset paid your fee)? Or did a spreadsheet trim long decimals or switch them to scientific notation, shaving a little off each row (see CSV problems after opening a spreadsheet)?
Do not round it to zero and move on. Open the original CSV as plain text and compare the last decimal places. If display precision explains it, write that in your open-items list together with the quantity shown in the original file.
Borrowed coins and margin: record the debt on its own line
If you trade on margin or borrow coins, what you borrowed is a liability, not extra holdings. Booking it as a negative asset balance mixes it with the omissions above, and you lose the ability to tell a normal negative from a broken one. Record the loan as its own liability line and clear it on repayment; the asset column should then stay at zero or above. Full bookkeeping for those accounts is beyond this note.
Before you find the cause, how to mark it
Do not add an inflow that happens to equal the gap and label it “adjustment”. The sheet will balance, and nobody will know where that 0.2000 came from. Give it a line in the month-end open-items list instead:
- account alias and asset;
- time and type of the first negative row, plus the lowest balance reached (the minimum gap);
- which pattern it follows: from the first outflow, negative for a stretch, one row only, or a tiny remainder;
- files already checked and files still to check;
- status: unverified, cause found, entry added.
An unresolved line carries forward into next month’s list under its original month rather than vanishing (how to version those lists is in naming reconciliation versions). Once you find the real record, add that record itself, with the source file name and timestamp.