[Bitcoin-qt] When exporting txns to csv, ledger doesn't match wallet balance.

[DeathAndTaxes]

I used the export to csv functionality in bitcoin-qt to export txns from wallet for reconciliation of accounting ledger.


Here is a subset which shows the types of entries.  The values have been replaced with placeholders to protect the details of the actual transactions.

Code:

Confirmed Date              Type                Address                             Amount          TxId
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
TRUE      12/18/2013 00:04  Payment to yourself                                       -0.00010000   596bd0f1a1f6943005ec686ae0e3b4443359f90bcfe7ffd6cd575a3e9e480846
TRUE      09/26/2012 18:46  Sent to             1G3udQm7bS74Yp4kTjnMX5GuZRXN6WyWe6  -248.00400000   67707a92ed98819fa7fe5060572c9db735f1db37be05b421ac5bd7f3e8cfd1ac
TRUE      09/26/2012 18:46  Sent to             12PduCSQ48ZM1qDJdKiphjdbifTdA4BUKK  -306.00000000   67707a92ed98819fa7fe5060572c9db735f1db37be05b421ac5bd7f3e8cfd1ac
TRUE      07/10/2012 09:28  Received with       1PA6v4KWZvehUAhHtu2b4RSAR6htq7vwkR     8.00000000   deac39699e0a66894080c0f8fe54c0fd2c281d7f3555719e29c6f00fb584379d
TRUE      07/10/2012 20:52  Sent to             1qvyovB7WUmiyEdoTBgQ26ZYyrFjNc4uKG   -85.27738000   7e411a1e70de5148ec931d35fd23fe652814b718952e0a4d 42a7de54b0e5c30
TRUE      06/05/2012 22:43  [Other]                                                   -0.12461652   5b40192065586ba30ece8abd7c9253c5d021bce0da9f89a138547a01cbe7559c
TRUE      03/22/2012 19:18  Mined               14KWZvehUAhHtu2tMyykv6PiRMycPz8KtM     0.03337541   3a08a00cf04433423cadd4c5c6688765dccc66f272bbf8e0a642150bed61dd21
TRUE      02/28/2012 11:48  Mined               14KWZvehUAhHtu2tMyykv6PiRMycPz8KtM     0.19822471   0ae3e24993a6ac56a5dcde559189abadc484012165b36027e81afb5e2fcf6899

Since the export has the value of all "inbound" txns and "outbound" txns the sum of the amount column should equal the current balance shown in the client (i.e new wallet "receives" 100 BTC, "spends" 20 BTC and 30 BTC would have a "balance" of 50 BTC.  The ledger would show +100, -30, -20 in the amount column.  The sum of the ledger would also be 50 BTC).

This isn't the case when I did an export.  There is a difference of about 1% between the current "balance" in the client (sum of UTXO) and the sum of the amount column in the ledger.  So why don't the numbers match?  I figured there was a simple reason and five hours later I can't come up with a rational explanation. The number "should" match so even a discrepancy of 1 satoshi would bother me.

Here are some potential explanations for the discrepancy but they turned out to be dead ends.

• The txn history contains unconfirmed or double spent txns.  Verified this isn't the case.
• The txn history is corrupt or has incorrect entries due to a reorg.  I used "zapwallettxes" to clear the txn history.  The client then rebuilds it from the current blockchain.
• I sent funds from one address in the wallet to another and the history reports this incorrectly.   The history reports it correct as the "Payment to yourself" entries only show the net change (i.e. fees as a negative amount).
• I dumped a private key and used it in another wallet.  Upon a rescan after zapwallettxes the wallet should have picked these up.
• I deleted a private key (using pywallet).  This would mean txn records would not show but it wouldn't be reflected in the client balance (i.e. any unspent outputs would be unknown to the client) so the running sum and current balance should still match.
• I imported a private key into the wallet after it had been used in another wallet (this I know I did with a very old vanity address).  The wallet may not have shown that were created prior to the import but after zapwallettxes it should "see" the older txns from the previous wallet as its own.
• The export does not correctly report txns with multiple outputs.  Verified it does.  They are listed as unique entries.
• The export does not correctly handle "change".  It does.  Change doesn't affect "balance" so they are not included in the export.
  

I am sure there is an rational explanation but I just can't see it.  Any ideas?

[1714768489]

1714768489

[1714768489]

1714768489

[DeathAndTaxes]

A bump.  Any ideas?  It would be important from an audit and accounting standpoint for the export to always produce the current "balance" of the wallet.   I would imagine no accountant or IRS auditor is going to use the blockchain directly.

[DannyHamilton]

Figuring this out is probably going to require an analysis of your data that involves sharing more information than you want to share publicly.

I just tried it with a few of my Bitcoin Core wallets, and the sum of the amount column exactly matches my wallet balance on each of them.

Clearly there is something different about the wallet you are looking at, but whatever it is, I haven't encountered it yet in any of the wallets I checked.

A quick thought I just had while typing this...

None of the wallets I tried it with include a multi-sig address (native or P2SH).  Does your wallet include payments sent to (or received at) any multi-sig addresses? If so, perhaps the Export isn't handling that properly?

[Fantastory]

I have the same issue.
Summary on exported data for the 2020-08-01 misses about 40% of balance.
The wallet is 5 years old.

It must be something more than "Payment to yourself" records since i removed them manualy.

Any way
I think it would be a good idea to change amount to 0 on "Payment to yourself" lines.
Address column for those record is empty - only transaction id and date are filled.

[Fantastory]

I found that I have many unconfirmed transactions from years ago.
Probably those transactions are dropped.

I fix the report by adding 3 additional columns

H      =IF(A2,IF(C2="Payment to yourself",0,1),0)
I       =I3+H2*F2
J       =J1-H2*F2               
And  filling J1 with current balance.

Now summary differs only by 0.001%
I thought it is because of rounding on calculations (it is libre office) so I multiplied all amounts by 100000000.
But still the is the same difference.

[HCP]

I think it would be a good idea to change amount to 0 on "Payment to yourself" lines.

Why? The amount in a "Payment to yourself" line is due to transaction fees being spent and is going to result in a (small) reduction to your overall balance... unless you somehow paid a fee of zero satoshis!!?!

If you did indeed change all those values to zero, that is most likely why your spreadsheet value will be incorrect... as you're effectively ignoring any reduction in your balance due to transaction fees for those transactions.