Can't convert Binance US yearly tax report

[theDanielJLewis]

Binance US offers a way to export yearly tax reports now. But BittyTax won't convert them.

The heading line looks like this:

User_Id,Time,Category,Operation,Order_Id,Transaction_Id,Primary_Asset,Realized_Amount_For_Primary_Asset,Realized_Amount_For_Primary_Asset_In_USD_Value,Base_Asset,Realized_Amount_For_Base_Asset,Realized_Amount_For_Base_Asset_In_USD_Value,Quote_Asset,Realized_Amount_For_Quote_Asset,Realized_Amount_For_Quote_Asset_In_USD_Value,Fee_Asset,Realized_Amount_For_Fee_Asset,Realized_Amount_For_Fee_Asset_In_USD_Value,Payment_Method,Withdrawal_Method,Additional_Note

Here's the error I get:

file: 2021-2023 Binance US Reports.csv unrecognised
bittytax_conv: error: no data file(s) could be processed

[theDanielJLewis]

Smacking my head! I didn't realize the Binance.US converter is only in the unreleased version.

[theDanielJLewis]

Still having problems! Here's the error now that it ran through my transactions:

ERROR Unexpected error: "[<class 'decimal.ConversionSyntax'>]"
row[44348] ['REDACTED', '2023-12-27 06:19:43', 'Spot Trading', 'Sell', 'REDACTED', '2315908486', '', '', '', 'SOL', '0.30000000', '32.10713789', 'USDT', '', '33.62035428', 'BNB', '0.00027232', '0.08814381', 'Wallet', '', '']

[nanonano]

Hi @theDanielJLewis, glad you got it sorta working.

How often does that error appear? Is it just that one row? Is it only sells to USDT (the quote asset)?

The error is happening because the "Realized_Amount_For_Quote_Asset" field is not populated, looks like a bug in the Binance.US output, or could be that since it's USDT, the "Realized_Amount_For_Quote_Asset_In_USD_Value" field could be used instead.

Although I've seen examples where the quote asset is USD and "Realized_Amount_For_Quote_Asset" IS populated correctly.

[theDanielJLewis]

I get thousands of those errors. I'm working with a CSV that goes back to 2021, so there's a mix of USD and USDT.

Should I rename or merge some columns? In my case, I started with the 2021 export and copied all the rows from 2022 and 2023 onto it. So should I simply change "USD" to "USDT anywhere in the headers?

[nanonano]

Please can you post a few more example rows with the errors so I can confirm the pattern.

I'll have to patch the parser with a workaround for when the "Realized_Amount_For_Quote_Asset" field is not populated correctly.

By the way, you can pass all of your Binanace.US yearly exports into the conv tool and it will consolidate them for you.

[theDanielJLewis]

It turns out the headings are the same in the CSVs for each year. Thanks for the tip on merging them with conv. I tried to consolidate them for TurboTax, but then it said it had too many lines! And then TurboTax couldn't process a smaller file. Thus why I'm here.

Here's sample data I put together for a test file (account and transaction numbers have been replaced):

User_Id,Time,Category,Operation,Order_Id,Transaction_Id,Primary_Asset,Realized_Amount_For_Primary_Asset,Realized_Amount_For_Primary_Asset_In_USD_Value,Base_Asset,Realized_Amount_For_Base_Asset,Realized_Amount_For_Base_Asset_In_USD_Value,Quote_Asset,Realized_Amount_For_Quote_Asset,Realized_Amount_For_Quote_Asset_In_USD_Value,Fee_Asset,Realized_Amount_For_Fee_Asset,Realized_Amount_For_Fee_Asset_In_USD_Value,Payment_Method,Withdrawal_Method,Additional_Note
123456789,2023-01-04 02:45:23,Spot Trading,Sell,187217877,348957234,"","","",AVAX,3.77000000,45.53260248,USD,"",43.76970000,BNB,0.00053740,0.13925092,Wallet,"",""
123456789,2023-01-07 03:13:05,Distribution,Staking Rewards,140087758188,2013237788,VTHO,40.41913037,0.03760151,"","","","","","","","","",Wallet,"",""
123456789,2023-01-09 02:30:42,Spot Trading,Buy,248888691,325432,"","","",APE,2.11000000,9.74007690,USD,"",10.12778900,BNB,0.00010838,0.02955539,Wallet,"",""
123456789,2023-06-30 13:48:10,Spot Trading,Buy,457651937,3265465426,"","","",BNB,0.04700000,11.29737965,USDT,"",10.92368759,BNB,0.00014100,0.03389214,Wallet,"",""
123456789,2023-06-30 16:22:48,Spot Trading,Sell,279742526,425326,"","","",LTC,0.72400000,78.31169822,USDT,"",71.23290792,BNB,0.00090748,0.21813077,Wallet,"",""
123456789,2023-10-06 07:40:28,Spot Trading,Buy,7448721,254262,"","","",LOOM,33.00000000,5.31653463,USDT,"",5.21495137,BNB,0.00009347,0.01996104,Wallet,"",""
123456789,2023-10-06 09:20:58,Spot Trading,Sell,7470748,6745713346,"","","",LOOM,811.00000000,130.65786621,USDT,"",142.71706045,BNB,0.00255581,0.54580736,Wallet,"",""
123456789,2023-12-07 17:43:21,Convert,Convert,"",2565761345,"","","",BUSD,0.11103549,0.11105007,USDT,0.11055878,0.11059384,USDT,0.00552794,0.00552969,Wallet,"",""
123456789,2023-12-07 17:43:21,Convert,Convert,"",62546546872,"","","",XTZ,0.00698199,0.00634272,USDT,0.00632568,0.00632769,USDT,0.00031629,0.00031639,Wallet,"",""
123456789,2023-12-07 17:43:21,Convert,Convert,"",234654724,"","","",XLM,0.50000000,0.06308689,USDT,0.06280000,0.06281991,USDT,0.00314000,0.00314100,Wallet,"",""

Running this through conv gave me this output:

file: Binance US test.csv matched as "Binance.US"
WARNING Parser failure for "Binance.US"
row[2] ['123456789', '2023-01-04 02:45:23', 'Spot Trading', 'Sell', '187217877', '348957234', '', '', '', 'AVAX', '3.77000000', '45.53260248', 'USD', '', '43.76970000', 'BNB', '0.00053740', '0.13925092', 'Wallet', '', '']
ERROR Unexpected error: "[<class 'decimal.ConversionSyntax'>]"
row[4] ['123456789', '2023-01-09 02:30:42', 'Spot Trading', 'Buy', '248888691', '325432', '', '', '', 'APE', '2.11000000', '9.74007690', 'USD', '', '10.12778900', 'BNB', '0.00010838', '0.02955539', 'Wallet', '', '']
ERROR Unexpected error: "[<class 'decimal.ConversionSyntax'>]"
row[5] ['123456789', '2023-06-30 13:48:10', 'Spot Trading', 'Buy', '457651937', '3265465426', '', '', '', 'BNB', '0.04700000', '11.29737965', 'USDT', '', '10.92368759', 'BNB', '0.00014100', '0.03389214', 'Wallet', '', '']
ERROR Unexpected error: "[<class 'decimal.ConversionSyntax'>]"
row[6] ['123456789', '2023-06-30 16:22:48', 'Spot Trading', 'Sell', '279742526', '425326', '', '', '', 'LTC', '0.72400000', '78.31169822', 'USDT', '', '71.23290792', 'BNB', '0.00090748', '0.21813077', 'Wallet', '', '']
ERROR Unexpected error: "[<class 'decimal.ConversionSyntax'>]"
row[7] ['123456789', '2023-10-06 07:40:28', 'Spot Trading', 'Buy', '7448721', '254262', '', '', '', 'LOOM', '33.00000000', '5.31653463', 'USDT', '', '5.21495137', 'BNB', '0.00009347', '0.01996104', 'Wallet', '', '']
ERROR Unexpected error: "[<class 'decimal.ConversionSyntax'>]"
row[8] ['123456789', '2023-10-06 09:20:58', 'Spot Trading', 'Sell', '7470748', '6745713346', '', '', '', 'LOOM', '811.00000000', '130.65786621', 'USDT', '', '142.71706045', 'BNB', '0.00255581', '0.54580736', 'Wallet', '', '']
ERROR Unexpected error: "[<class 'decimal.ConversionSyntax'>]"
output EXCEL file created: BittyTax_Records-2.xlsx

So that's 6 errors for 10 transaction records, and that's all 6 of the "spot trading" transactions in this data.

[nanonano]

Thanks for the test file.

I've just pushed a fix for this here: https://github.com/BittyTax/BittyTax/commit/6d0b1f353dd11d35968b9bf085341195dc1ed555.

Please test.

[theDanielJLewis]

That test file worked! But I got 2 (at least it's only 2) more errors when I ran my full 2021–2023 CSV.

(Account and transaction numbers replaced.)

row[38581] ['12345', '2023-03-15 14:30:41', 'Spot Trading', 'Buy', '2344', '34566', '', '', '', 'NEAR', '24.40000000', '47.00617690', 'BUSD', '', '49.30801881', 'BNB', '0.00047984', '0.14737083', 'Wallet', '', '']
ERROR Unexpected error: "[<class 'decimal.ConversionSyntax'>]"
row[38586] ['12345', '2023-03-15 15:17:15', 'Spot Trading', 'Buy', '2345', '34567', '', '', '', 'NEAR', '0.30000000', '0.57794480', 'BUSD', '', '0.60624613', 'BNB', '0.00000586', '0.00179975', 'Wallet', '', '']
ERROR Unexpected error: "[<class 'decimal.ConversionSyntax'>]"

[theDanielJLewis]

And in the bittytax command on my full file, I got this one error (strange that it's a negative transaction on a staking reward).

import: 'Binance.US' row[32345] ['Staking', '-0.005213', 'ALGO', '-0.003957347938448189', '', '', '', '', '', '', 'Binance.US', '2022-02-08 07:10:03', '']

[nanonano]

Not sure what that is, there must a "Staking Rewards" operation in your Binance.US data file with a negative number.

Could be a bug or might be that you are paying a fee for Staking, can you remember?

[nanonano]

Was it just one row which fails like that?

[theDanielJLewis]

Yes, on bittytax, I got only that one error. I'll try simply deleting that line from the file (even though my converted file is probably missing those two NEAR trades that glitched above).

It's a big file. So I'll be waiting a while for it to open.

By the way, I promoted BittyTax to a couple crypto communities, including 3Commas, and I encouraged them to donate if BittyTax works for them (as I will do if it works for me)!

[theDanielJLewis]

Oh, I think I know why there's a negative staking reward. I found this while looking at Binance US's trading fees (as I think about switching back to Coinbase):

Binance.US deducts a 25% to 35% service fee from earned staking rewards, which is reflected in the estimated rewards rate for each staking asset. Our standard service fee is 25% for ETH; 30% for ADA, ATOM, DOT, SOL, and XTZ; and, up to 35% for other assets.

[nanonano]

The 2 NEAR trades just needs me to add BUSD to the patch (along with USD, USDT), and those will parse.

Thanks for promoting BittyTax, really appreciated. Please give the project a star also :)

Was it you who was asking for USA tax rules support? I'm working on this currently, should be a beta available next week.

Oh, I think I know why there's a negative staking reward. I found this while looking at Binance US's trading fees (as I think about switching back to Coinbase):

Ok, I'll add negative "Staking Rewards" as a Spend transaction so it all balances correctly.

I'll do patch in the morning.

[theDanielJLewis]

When I removed that negative staking reward from my Binance US data, reconverted (still getting those two NEAR errors), and then ran bittytax again, I now get these warnings and errors:

WARNING Balance at Binance.US:KNC is negative -1.215
WARNING Balance at Binance.US:VTHO is negative -4,736.55027255
WARNING Balance at Binance.US:VTHO is negative -4,708.55027255
WARNING Balance at Binance.US:VTHO is negative -5,499.55027255
WARNING Balance at Binance.US:USDT is negative -0.17409302
WARNING Balance at Binance.US:USDT is negative -0.1740974
WARNING Balance at Binance.US:NEAR is negative -24.7
WARNING Balance at Binance.US:NEAR is negative -24.7
WARNING Balance at Binance.US:NEAR is negative -24.7
WARNING Balance at Binance.US:NEAR is negative -24.7
WARNING Balance at Binance.US:NEAR is negative -24.7
WARNING Balance at Binance.US:NEAR is negative -24.7
WARNING Balance at Binance.US:NEAR is negative -24.7
WARNING Balance at Binance.US:NEAR is negative -24.7
WARNING Balance at Binance.US:NEAR is negative -24.7
WARNING Balance at Binance.US:NEAR is negative -24.7
WARNING Balance at Binance.US:NEAR is negative -24.7
WARNING Balance at Binance.US:NEAR is negative -24.7
WARNING Balance at Binance.US:NEAR is negative -24.7
WARNING Balance at Binance.US:NEAR is negative -19.4
WARNING Balance at Binance.US:NEAR is negative -24.7
WARNING Balance at Binance.US:NEAR is negative -24.6
WARNING Balance at Binance.US:NEAR is negative -24.7
WARNING Balance at Binance.US:NEAR is negative -24.7
WARNING Balance at Binance.US:NEAR is negative -24.7
WARNING Balance at Binance.US:NEAR is negative -21.1
WARNING Balance at Binance.US:NEAR is negative -24.7
WARNING Balance at Binance.US:NEAR is negative -24.7
WARNING Balance at Binance.US:NEAR is negative -24.7
WARNING Balance at Binance.US:NEAR is negative -24.7
WARNING Balance at Binance.US:NEAR is negative -24.7
WARNING Balance at Binance.US:NEAR is negative -15.5
WARNING Balance at Binance.US:NEAR is negative -18.3
WARNING Balance at Binance.US:NEAR is negative -19
WARNING Balance at Binance.US:NEAR is negative -24.7
WARNING Balance at Binance.US:NEAR is negative -24.7

The NEAR warnings are probably related to those two unconverted transactions.

And then I got this:

integrity check: failed
WARNING Integrity check failed: audit does not match section 104 pools, please check Withdrawals and Deposits for missing fees

Asset                Audit Balance          Section 104 Pool                Difference
ADA                           33.6                4,528.8821              +4,495.2821
BNB                     5.26159337                5.90289679              +0.64130342
BTC                     0.09976456                0.11976456                    +0.02
EOS                              0                  -218.046                 -218.046
ETH                     0.66528023                0.63398885              -0.03129138
LTC                              0               23.15332442             +23.15332442
OXT                              0               -253.056498              -253.056498
USDC                             0                     503.5                   +503.5
USDT                5,462.80621904            7,391.20621904                 +1,928.4
XLM                          4,273               12,914.2494              +8,641.2494

These all look like old outgoing crypto transfers. But I'm not quite sure what I need to do to fix these.

Was it you who was asking for USA tax rules support? I'm working on this currently, should be a beta available next week.

Oh, this doesn't support what I need for USA taxes, yet? (I was thinking all I needed were my long- and short-term capital gains and losses.)

[nanonano]

Hi @theDanielJLewis, I've done a patch to fix the NEAR trades and the negative staking reward, see https://github.com/BittyTax/BittyTax/commit/5620631d021accc1a7e9b7417f0fd8e741404b71. Please uninstall/re-install and test.

When I removed that negative staking reward from my Binance US data, reconverted (still getting those two NEAR errors), and then ran bittytax again, I now get these warnings and errors:

This is the Audit checking your transaction history, the negative warning will happen if you spend more of an asset than you have.

The NEAR ones should be fixed by this latest patch.

The small amount of USDT difference might be due to the patches work-a-round for when the "Realized_Amount_For_Quote_Asset" field is not populated, and the "Realized_Amount_For_Quote_Asset_In_USD_Value" field is used instead. Please report this issue to Binance.US.

KNC/VTHO, maybe you are missing an airdrop for these, or tokens that got renamed?

These all look like old outgoing crypto transfers. But I'm not quite sure what I need to do to fix these.

The Integrity Check will fail if you have not included your complete transaction history, have you used other wallets or exchanges? The deposits/withdrawals have to match exactly.

Oh, this doesn't support what I need for USA taxes, yet? (I was thinking all I needed were my long- and short-term capital gains and losses.)

It's currently only UK tax rules, but lots of the functionality is the same for all countries, see International Support.

Support for USA will be available as a feature branch for beta testing this week. It uses FIFO and will calculate your short-term/long-term capital gains, etc.

[nanonano]

Hi @theDanielJLewis, if you would like to beta test the US version of BittyTax details are here: https://github.com/BittyTax/BittyTax/wiki/USA

[theDanielJLewis]

Sigh. I'm trying, but I apparently just can't provide the data Bitty needs.

After about 20 minutes of processing the XLSX file, and a bunch of warnings with it (I guess related to either Binance's forced switch from USD to USDT, or also some transaction history from other exchanges I can't get), I still get the error "ERROR Unmatched disposal(s) detected, turn on logging [-d] to see transactions."

By the way, support for ByBit and Gate.io would be nice, since some of my pre-2023 transactions were on those exchanges.

[theDanielJLewis]

Sidenote: for people like me who have a lot of transactions, it would be nice if that final error displayed the transactions anyway without having to rerun the 20-minute process with -d.

[theDanielJLewis]

Ugh. And even after enabling -d and rerunning, I still don't know what the problematic transactions are because -d displayed every transaction and overfilled my console. Then, Bitty wouldn't make a report—or even an estimate—for me anyway.

I hate stupid taxes.

[nanonano]

After about 20 minutes of processing the XLSX file, and a bunch of warnings with it (I guess related to either Binance's forced switch from USD to USDT, or also some transaction history from other exchanges I can't get), I still get the error "ERROR Unmatched disposal(s) detected, turn on logging [-d] to see transactions."

This error happens when the FIFO logic can't find a buy to match with the sell of an asset.

By the way, support for ByBit and Gate.io would be nice, since some of my pre-2023 transactions were on those exchanges.

There's already a request to add Gate.io here: https://github.com/BittyTax/BittyTax/issues/152. Please raise one for ByBit and share a sample export.

Ugh. And even after enabling -d and rerunning, I still don't know what the problematic transactions are because -d displayed every transaction and overfilled my console. Then, Bitty wouldn't make a report—or even an estimate—for me anyway.

I have something in the works which will give you a report of FIFO mismatches, etc to make this process easier.

[nanonano]

I've added support for Gate.io to both master, and USA-tax-rules branches if you would like to test it? See https://github.com/BittyTax/BittyTax/commit/557c088aebd9dfe2dc1d6aaa3cb7139c7b63154b.

Any issues please report them here: #152.