blog/data/2024-03-07-bitcoin-ledger.txt

   1 Title: Plain text accounting file from your bitcoin transactions
   2 Tags: english, bitcoin
   3 Date: 2024-03-07 18:00
   4
   5 <p>A while back I wrote a small script to extract the Bitcoin
   6 transactions in a wallet in the
   7 <ahref="https://plaintextaccounting.org/">ledger plain text accounting
   8 format</a>.  The last few days I spent some time to get it working
   9 better with more special cases.  In case it can be useful for others,
  10 here is a copy:</p>
  11
  12 <p><blockquote><pre>
  13 #!/usr/bin/python3
  14 #  -*- coding: utf-8 -*-
  15 #  Copyright (c) 2023-2024 Petter Reinholdtsen
  16
  17 from decimal import Decimal
  18 import json
  19 import subprocess
  20 import time
  21
  22 import numpy
  23
  24 def format_float(num):
  25     return numpy.format_float_positional(num, trim='-')
  26
  27 accounts = {
  28     u'amount' : 'Assets:BTC:main',
  29 }
  30
  31 addresses = {
  32     '<some address>' : 'Assets:bankkonto',
  33     '<some address>' : 'Assets:bankkonto',
  34 }
  35
  36 def exec_json(cmd):
  37     proc = subprocess.Popen(cmd,stdout=subprocess.PIPE)
  38     j = json.loads(proc.communicate()[0], parse_float=Decimal)
  39     return j
  40
  41 def list_txs():
  42     # get all transactions for all accounts / addresses
  43     c = 0
  44     txs = []
  45     txidfee = {}
  46     limit=100000
  47     cmd = ['bitcoin-cli', 'listtransactions', '*', str(limit)]
  48     if True:
  49         txs.extend(exec_json(cmd))
  50     else:
  51         # Useful for debugging
  52         with open('transactions.json') as f:
  53             txs.extend(json.load(f, parse_float=Decimal))
  54     #print txs
  55     for tx in sorted(txs, key=lambda a: a['time']):
  56 #        print tx['category']
  57         if 'abandoned' in tx and tx['abandoned']:
  58             continue
  59         if 'confirmations' in tx and 0 >= tx['confirmations']:
  60             continue
  61         when = time.strftime('%Y-%m-%d %H:%M', time.localtime(tx['time']))
  62         if 'message' in tx:
  63             desc = tx['message']
  64         elif 'comment' in tx:
  65             desc = tx['comment']
  66         elif 'label' in tx:
  67             desc = tx['label']
  68         else:
  69             desc = 'n/a'
  70         print("%s %s" % (when, desc))
  71         if 'address' in tx:
  72             print("  ; to bitcoin address %s" % tx['address'])
  73         else:
  74             print("  ; missing address in transaction, txid=%s" % tx['txid'])
  75         print(f"  ; amount={tx['amount']}")
  76         if 'fee'in tx:
  77             print(f"  ; fee={tx['fee']}")
  78         for f in accounts.keys():
  79             if f in tx and Decimal(0) != tx[f]:
  80                 amount = tx[f]
  81                 print("  %-20s   %s BTC" % (accounts[f], format_float(amount)))
  82         if 'fee' in tx and Decimal(0) != tx['fee']:
  83             # Make sure to list fee used in several transactions only once.
  84             if 'fee' in tx and tx['txid'] in txidfee \
  85                and tx['fee'] == txidfee[tx['txid']]:
  86                 True
  87             else:
  88                 fee = tx['fee']
  89                 print("  %-20s   %s BTC" % (accounts['amount'], format_float(fee)))
  90                 print("  %-20s   %s BTC" % ('Expences:BTC-fee', format_float(-fee)))
  91                 txidfee[tx['txid']] = tx['fee']
  92
  93         if 'address' in tx and tx['address'] in addresses:
  94             print("  %s" % addresses[tx['address']])
  95         else:
  96             if 'generate' == tx['category']:
  97                 print("  Income:BTC-mining")
  98             else:
  99                 if amount < Decimal(0):
 100                     print(f"  Assets:unknown:sent:update-script-addr-{tx['address']}")
 101                 else:
 102                     print(f"  Assets:unknown:received:update-script-addr-{tx['address']}")
 103
 104         print()
 105         c = c + 1
 106     print("# Found %d transactions" % c)
 107     if limit == c:
 108         print(f"# Warning: Limit {limit} reached, consider increasing limit.")
 109
 110 def main():
 111     list_txs()
 112
 113 main()
 114 </pre></blockquote></p>
 115
 116 <p>It is more of a proof of concept, and I do not expect it to handle
 117 all edge cases, but it worked for me, and perhaps you can find it
 118 useful too.</p>
 119
 120 <p>To get a more interesting result, it is useful to map accounts sent
 121 to or received from to accounting accounts, using the
 122 <tt>addresses</tt> hash.  As these will be very context dependent, I
 123 leave out my list to allow each user to fill out their own list of
 124 accounts.  Out of the box, 'ledger reg BTC:main' should be able to
 125 show the amount of BTCs present in the wallet at any given time in the
 126 past.  For other and more valuable analysis, a account plan need to be
 127 set up in the <tt>addresses</tt> hash.  Here is an example
 128 transaction:</p>
 129
 130 <p><blockquote><pre>
 131 2024-03-07 17:00 Donated to good cause
 132     Assets:BTC:main                           -0.1 BTC
 133     Assets:BTC:main                       -0.00001 BTC
 134     Expences:BTC-fee                       0.00001 BTC
 135     Expences:donations                         0.1 BTC
 136 </pre></blockquote></p>
 137
 138 <p>It need a running Bitcoin Core daemon running, as it connect to it
 139 using <tt>bitcoin-cli listtransactions * 100000</tt> to extract the
 140 transactions listed in the Wallet.</p>
 141
 142 <p>As usual, if you use Bitcoin and want to show your support of my
 143 activities, please send Bitcoin donations to my address
 144 <b><a href="bitcoin:15oWEoG9dUPovwmUL9KWAnYRtNJEkP1u1b">15oWEoG9dUPovwmUL9KWAnYRtNJEkP1u1b</a></b>.</p>