How to automate crypto bookkeeping using a crypto sub-ledger & Xero

gm accountants 👋

If your company deals with crypto, your first instinct might be to grab a CSV from a block explorer and manually categorize all those transaction hashes.

Hate to break it to you, but spreadsheets make crypto bookkeeping a nightmare, slow, tedious, and way too time-consuming. Plus block explorers have not been designed for accounting purposes. They lack context on the nature of the transaction and data accuracy from some obscure chains would wear out even the best accountant.

A smarter move?  Using crypto sub-ledgers alongside your accounting software instead.

In this tutorial, you will learn how to import transactions from your wallet, categorize them according to your Xero Chart of Accounts (COA) mapping, and sync them into Xero using a crypto sub-ledger tool.

⚡Creating the Chart of Accounts in Xero by Token

Head over to your Xero account and under Accounting, click Chart of Accounts (COA).

Next, follow these steps to create the COA; 

• Hover to the top right of your screen, click on ‘Add Account’’, and add your token asset accounts. (Account Type: Current Asset)

Note: I created individual token accounts for my significant holdings, and aggregated all other token holdings under an account called ‘Other Crypto Assets’.

Next, create your crypto-specific expense accounts. Again hover over to the top left of your screen, click on ‘Add Account’’, and add the following;

• Gas Fees (Account Type: Expenses)
• Realized Net Gain/Loss (Account Type: Expenses)

Lastly and this is specific for TRES, the crypto sub-ledger we’re using, you would have to create a swap holding account to map swap transactions. (Account Type: Current Assets)

💻 Setting up Sub-Ledger 

A sub-ledger allows you to integrate your on-chain activity into your accounting software with greater ease. Through importing wallets, labeling transactions, mapping wallets, and creating journal rules, the sub-ledgers would be able to record your crypto activity to fit your business’s chart of account requirements. 

For this exercise, I’ve used TRES as my sub-ledger. It’s fair to say that TRES is much more than a sub-ledger; it is more of an all-encompassing financial compliance suite. What we typically refer to as sub-ledger features fall under the ‘TRES FinOS’ platform, and in this exercise, we will go through their ‘Accounting’, ‘Reconciliation’, and ‘Reporting’ features.

(Note: Instead of being solely a crypto sub-ledger, TRES refers to itself as a financial data lake and financial compliance suite.)

You can use any sub-ledger of your liking but do ensure your sub-ledger allows the following; 

• To sync the Xero COA you created, and
• Automatic syncing of your wallet transactions into Xero.

(NOTE: I highly recommend exploring different sub-ledgers. While they all offer similar core functionality for labeling transactions, each one comes with unique value-added features that can make a difference in the long term.)

Step 1. Link Xero Chart of Accounts 

In TRES, I proceeded to link my Xero Chart of Accounts. In the sidebar, choose Integrations > Xero.

Do check that the current asset (i.e tokens) and expense accounts you created in Xero have been correctly synced.

Step 2. Add Wallets 

Before choosing a sub-ledger, please make sure it supports your company’s data sources (i.e. blockchains, exchanges, custodians). Even if a sub-ledger claims to support the blockchain you require, it is advisable to ask for a trial period to test the integrity of the data first.

There is fierce competition between sub-ledgers battling on who has the most integrations possible (i.e. blockchains, exchanges, custodians, DeFi protocols), and sometimes the data integrity is overlooked. TRES covers 160+ networks and you can also refer to our tools page for a list of the most popular crypto sub-ledgers.

Once you’ve imported your wallets into TRES or any  other sub-ledger, a good practice is to manually reconcile tokens appearing on the platform against tokens appearing on the block explorer. 

However, TRES has a cool ‘Reconciliation’ feature under Ledger > Actions > Reconcile, where it aligns the tokens to the blockchain/exchange balance, and any discrepancies are identified and can be resolved manually. (In this example, I did not have any discrepancies)

Step 3. Cost Basis Calculations

In most jurisdictions, cryptocurrencies are treated similarly to property for tax purposes, and you should calculate a realized gain/loss upon disposal (i.e swap, payment, trading, liquidity providing, off-ramping, bridging, etc..)

The realized gain/loss is the difference between the ‘fair value’ and the cost basis of the crypto asset. A core feature of a sub-ledger is to automate the realized gain/loss calculation of your transactions under various accounting methods like FIFO, LIFO, or WAC. 

In this example, I’ve used the Weighted Average (WAC) method, and TRES offers all of the most widely used cost basis globally.

Step 4. Chart of Accounts

What’s one of the biggest time-savers with crypto sub-ledgers? Automation rules. TRES gives you two types: Default rules & Custom rules.

With Default rules, I set up mappings for gas fees and realized gains/losses. Since TRES auto-detects gas fees, every time transactions sync to Xero, it automatically books them under ‘Gas Fees (6001)’. Same deal for realized gains/losses. Just set it up once, and let TRES handle the rest

Custom rules are where you’ll be spending some time when implementing a sub-ledger. 

Here you have the freedom to set up any sort of rule that fits your business activity. TRES allows you to set up rules by wallets, assets, transaction type, contact name, contract address, blockchains, and more. 

Here’s what I set up for this tutorial;

• ETH - all ETH transactions get mapped to Xero COA ‘ETH 1001’
• Other Crypto Assets - all transactions other than ETH, USDC & USDT get mapped to Xero COA ‘Other Crypto Assets 1004’
• Salaries - all transactions of a specific wallet address get assigned to a contact name ‘Max Finley’ and mapped to Xero COA ‘Wages & Salaries 6680’
• Staking Rewards - all transactions from a specific contract address get mapped to Xero COA ‘Staking Rewards 4001’

Step 5. Categorizing Transactions 

One advice when implementing a sub-ledger like TRES is to automate recurring transactions in the prior step, as much as possible to avoid manual categorization. 

For this tutorial, I have categorized 4 different types of transactions - Inbound, Outbound, Swap & Internal Wallet transfers. Note that a realized gain/loss journal entry will only be created for the Outbound and Swap transactions. An internal transfer between wallets is not considered a disposal.

Head over to your sidebar, and under ‘Ledger’, I have categorized; 

1. Transfer tokens - Inbound transaction
2. Transfer tokens - Outbound transaction
3. Account - Wages & Salaries automation that I created for contact name ‘Max Finley’ under Step 4 above has  been automatically mapped
4. Fee - Default rule that we created for gas fees under Step 4 above has  been automatically mapped
5. Swap transaction - Trading transaction, and on the first line, you can see I swapped CVX for ETH
   
   1. TRES recommends using a swap holding account for both the income and expense legs of the journal.
   2. For the incoming ETH - DR ETH & CR Swap Holding Account (Income)
   3. For the outgoing CVX - DR Swap Holding Account (Expense) & CR Other Crypto Asset
6. Internal wallet transfer - Since I’m using a token view COA set up here, this transaction will be nullified.

‍

Once all transactions have been categorized, you can sync them into Xero.

Step 6. Journal entry example in Xero 

Once the transaction syncs to Xero, navigate to Accounting > Journal Report > Manual Journals, where you'll find the journal entry in the Draft section.

Now, let’s walk through an example of a journal entry for an expense transaction.

The great thing about using a sub-ledger like TRES is that all the custom and default rules we set up earlier will automatically map transactions to Xero’s Chart of Accounts (COA), streamlining the process and making it highly efficient.

👋 Get Started Today 

That’s it folks, you’ve just leveled up your crypto accounting skills. I hope you found this guide useful in making sense of crypto transactions by using a crypto sub-ledger in combination with Xero.

The key takeaway? Managing crypto transactions requires more than just spreadsheets, it demands a proper sub-ledger. By integrating a crypto sub-ledger, you’ll automate gas fees, recurring transactions and track realized gains/losses effortlessly, and significantly lighten the workload at month-end. For a more in-depth understanding of using crypto sub-ledgers and having different COA set-ups, including a wallet view, you can consider joining the next cohort of the Crypto Accounting Academy.

With the right tools, crypto accounting doesn’t have to be overwhelming.