Cleaning up the data you collected
- Author
- CRYPTO PORT Editorial
- Published
- Updated
- Reading time
- 7 min
In short
The CSVs you collected will not line up as they are: different date formats, different decimal handling, different names for the same kind of event. Normalising those and removing duplicates is real work — and every change you make should be written down. If you are unsure whether an adjustment is appropriate, ask a tax accountant.
Key points
- Normalise date formats and how time zones are handled
- Remove duplicates where the same event appears in more than one file
- Map each exchange's own labels for event types onto one consistent set
- Record every adjustment you make, with the reason
Definition
Bringing transaction data of differing formats into one consistent shape that a calculation can actually use.
Start with dates and times. Services variously record in Japanese local time or UTC, to the second or only to the minute. Decide which basis you will use and convert everything to it. Transactions around the turn of the year sit exactly where a difference in basis can move them into a different tax year, so treat that boundary with particular care.
Then duplicates. A transfer from one exchange to another appears twice — as a withdrawal in one file and a deposit in another. Treating those as two separate events doubles your apparent holdings. The same happens when you have both a wallet's own history and an explorer export. Use the transaction hash, or matching timestamp and quantity, to collapse each pair into a single movement.
Third, event types. 'Trade', 'swap', 'reward', 'fee', 'deposit', 'withdrawal' are labelled differently and at different granularity by each service. Decide on your own set of categories and build a mapping table from each service's wording onto it — build it once and you can reuse it every year.
Watch the decimals. Crypto quantities carry many decimal places, and spreadsheets will happily round the tail off or convert a value to scientific notation. Calculating from a rounded quantity leaves you with a balance that is very slightly wrong at the end and an enormous amount of time spent finding out why. Importing the original columns as text avoids the whole problem.
Most importantly, log what you changed: 'row removed as duplicate', 'timestamp converted from UTC to JST', 'reclassified as a reward'. If someone later asks you to explain the figures, that log is what separates a reasoned adjustment from an unexplained edit. Where an adjustment is a judgement call, do not settle it yourself — ask a tax accountant.
Watch out for
- · This page is a general orientation, not tax advice
- · Never rewrite a figure simply to make the numbers agree
- · Keep the basis for every adjustment — an edit you cannot explain is one to avoid
Frequently asked questions
Is a transfer between my own exchanges a disposal?
Moving assets between accounts you control is generally treated as a change of location rather than a disposal, but details such as the treatment of the transfer fee still need thought. Check the National Tax Agency's guidance and ask a tax accountant where it is unclear.