A while back, I got frustrated with the manual effort required in getting accurate transaction data from Luno into Xero in the correct format.
Initially I thought I’d see if one could set up a bank feed between one’s Luno wallets and Xero but that’s seemingly not possible for now. I also couldn’t seem to find anything available to solve for my specific use case, so I decided it was time to learn some code and automate the process.
Here’s how it works.
The hassle of getting accurate transaction data from Luno into Xero stems largely from the following:
- Currently you need to export & import the data manually.
- The transaction statements you download from Luno include ‘fund reservations’ for orders (which aren’t actual transactions) so you’ll need to clean these out in addition to formatting the data etc.
- You can’t add BTC as a currency in Xero. This means there is some rate conversion to do for any BTC (or ETH) balances before uploading.
- When you ‘instabuy’ BTC on Luno the transaction is listed as one transaction but includes the maker/taker fee. To illustrate: if I instabuy R1000 worth of BTC I might get R980 worth of BTC, with the remainder being a fee. That is listed as one transaction, but ideally, you want to see it as two.
- Lastly, if you’re holding any BTC, the price will fluctuate significantly, so the balance you’re reporting in Xero isn’t going to be very accurate most of the time.
The TLDR is as follows:
- Create the relevant accounts in Xero into which you want to post transactions i.e. ZAR wallet, BTC Wallet, Fees etc.
- Fetch your transaction history from Luno (via their API).
- Clean transaction history for relevant posts only, and create a unique transaction identifier.
- Create rules as to which transactions go where, then transform unix timestamps to dates (and some other nitty gritty).
- Transform the transaction data into a manual journal format (that Xero’s API expects).
- Filter journals retrieved from Luno against those already posted to Xero.
- Post new transactions into Xero via its Manual Journals endpoint.
- Set up triggers to run each day to post new transactions
- Set up a trigger to revalue the BTC wallet account based on the current BTC price.
The end result:
The end result is quite neat. You end up with accurate running balances for your wallets in Xero on your Balance Sheet, accurate fees on your PnL statement and a suspense account (see below) from which you can allocate your deposits and transfers as well as running total of your gains/losses — all in Xero with no manual input.
- Google App Scripts (though you could use an alternative)
- Xero Api (Endpoints: Manual Journals & Reports)
- Luno Api (Endpoints: Accounts, Balance, Trades)
A few things to note.
I’ve been running the above process for a while now and it works just fine. A couple of things to note however.
- Since I’m not currently tracking the weighted average cost of BTC purchases (or alt FIFO) all gains/losses whether realised or unrealised are posted into the same account. This means that at year-end an allocation between realised and unrealised gains will still need to be performed for tax purposes.
- Money coming into one’s BTC wallet, or being sent out of one’s wallet, could be from any 3rd party for any number of reasons. Thus these legs of the journals have to get posted to a suspense account to be manually attributed. This works fine in my case but might not be ideal if you were receiving a high volume of small amounts, in which case augmenting this approach would likely work better.
Lastly, remember to be careful with your API secrets, and when creating API keys for a crypto exchange, make them read only wherever possible.
There’s a large amount of detail I’ve glossed over above, but if you’re interested in automating accounting processes or in setting up something like this, reach out.
Lastly, a huge thanks to Nona gents Dave, Jon, Roy and Ryan for their help in setting this up.