Kraken: Action Types in CSV Exports

[Griffsano]

Summary

The current implementation for reading the Kraken CSV exports is according to the documentation of the "type" field:
https://support.kraken.com/hc/en-us/articles/360001169383-Explanation-of-Ledger-Fields

However, it seems like the CSV exports have changed and do not correspond to the definition above anymore. This leads to completely different behavior for some action types.

For the exports that I have, I can see the following behavior:

To-do list

• Fix staking / unstaking
• Staking rewards: Recognize reward entries
• Staking rewards: Stake rewarded coins correctly (not FIFO)
• Update logic for deposits / withdrawals
• Resolve coin names, e.g. XBT/BTC (for matching withdrawals / deposits)
• Kraken API: Try inverse coin pair, e.g. for EUR/USD
• Kraken API: Update logic to support virtual sells
• Margin trading: Closed positions
• Margin trading: Settled positions

Actions that currently do not work

Staking / Unstaking

A combination of "withdrawal"/"deposit" and two "transfer" rows.
The current implementation considers "transfer" as airdrop. Depending on how the CSV exports looked before, it could be more or less complicated to guarantee backwards-compatibility. Also related to #57.
For #87, a similar behavior was observed, although slightly different (four "transfer" rows).
For ETH, replace asset with XETH and asset.S with ETH2.S.

Staking

txid	refid	time	type	subtype	aclass	asset	amount	fee	balance
""	refid 1	date time 1	"withdrawal"	""	"currency"	asset	- amount	fee	""
""	refid 2	date time 2	"deposit"	""	"currency"	asset.S	amount	fee	""
txid 1	refid 1	date time 3	"transfer"	"spottostaking"	"currency"	asset	- amount	fee	balance
txid 2	refid 2	date time 4	"transfer"	"stakingfromspot"	"currency"	asset.S	amount	fee	balance

Unstaking

txid	refid	time	type	subtype	aclass	asset	amount	fee	balance
""	refid 1	date time 1	"withdrawal"	""	"currency"	asset.S	- amount	fee	""
""	refid 2	date time 2	"deposit"	""	"currency"	asset	amount	fee	""
txid 1	refid 2	date time 3	"transfer"	"spotfromstaking"	"currency"	asset	amount	fee	balance
txid 2	refid 1	date time 4	"transfer"	"stakingtospot"	"currency"	asset.S	- amount	fee	balance

Staking Rewards

According to the Kraken documentation, staking rewards should be listed as "reward".
However, they are actually logged as "deposit" and "staking" actions. The "deposit" row would probably mess with #4 and "staking" is currently not known and throws an error.
We should be careful when adding the "staking" operation: We want to add the rewarded coin to staking, not just any via FIFO.
For ETH, replace asset.S with ETH2 (not ETH2.S as for ETH staking above).

txid	refid	time	type	subtype	aclass	asset	amount	fee	balance
""	refid 1	date time 1	"deposit"	""	"currency"	asset.S	reward	fee	""
txid	refid 2	date time 2	"staking"	""	"currency"	asset.S	reward	fee	balance

Margin Trades

Margin trades are opened with "margin" and closed with "margin"/"settled", in-between there may be "rollover" fees for the open position.
I could not find any "margin trade" as documented on the Kraken website.
The row "margin" is currently not known and throws an error. "settled" and "rollover" rows are detected but also throw an error because margin trades are not supported yet (related to #52). For now, I would suggest to skip these lines with a warning, but still process the rest of the CSV.

Closed Position

Note that the CSV export only contains the base currency for the position (e.g. ZEUR for BTC/EUR). There is no way of finding out for which crypto the position was opened (BTC in this case).
Also, there is no way of linking the opening and closing actions ("margin" types) as they have different "refid" values.

txid	refid	time	type	subtype	aclass	asset	amount	fee	balance
txid 1	refid 1	date time 1	"margin"	""	"currency"	base asset	0	fee	base balance
txid 2	refid 1	date time 2	"rollover"	""	"currency"	base asset	0	fee	base balance
txid 3	refid 1	date time 3	"rollover"	""	"currency"	base asset	0	fee	base balance
txid 4	refid 2	date time 4	"margin"	""	"currency"	base asset	gain/loss	fee	base balance

Settled Position

There is no way of linking the opening and settling actions as they have different "refid" values.
The sign of "amount" depends if the position was settled with gains or losses.

txid	refid	time	type	subtype	aclass	asset	amount	fee	balance
txid 1	refid 1	date time 1	"margin"	""	"currency"	base asset	0	fee	base balance
txid 2	refid 1	date time 2	"rollover"	""	"currency"	base asset	0	fee	base balance
txid 3	refid 1	date time 3	"rollover"	""	"currency"	base asset	0	fee	base balance
txid 4	refid 2	date time 4	"settled"	""	"currency"	base asset	base amount	fee	base balance
txid 4	refid 2	date time 4	"settled"	""	"currency"	asset	- amount	fee	asset balance

Adjustment / Sale

Two further types could occur in the CSV export according to the documentation: "adjustment" and "sale". I don't think we need to implement them for now as they cover only very special actions.

Actions that work

The following actions work with the current implementation and are just for information.

Deposit / Withdrawal

Two "deposit" / "withdrawal" rows.

Deposit

txid	refid	time	type	subtype	aclass	asset	amount	fee	balance
""	refid	date time 1	"deposit"	""	"currency"	asset	deposited amount	fee	""
txid	refid	date time 2	"deposit"	""	"currency"	asset	deposited amount	fee	balance

Withdrawal

txid	refid	time	type	subtype	aclass	asset	amount	fee	balance
""	refid	date time 1	"withdrawal"	""	"currency"	asset	- withdrawn amount	fee	""
txid	refid	date time 2	"withdrawal"	""	"currency"	asset	- withdrawn amount	fee	balance

Spend / Receive (Trade via the Buy Crypto button)

A combination of "spend" and "receive" rows. Example for buying crypto with fiat:

txid	refid	time	type	subtype	aclass	asset	amount	fee	balance
txid 1	refid	date time 1	"spend"	""	"currency"	base asset	- base amount	fee	base balance
txid 2	refid	date time 1	"receive"	""	"currency"	asset	amount	fee	balance

Trades

A combination of two "trade" rows. Example for buying crypto with fiat:

txid	refid	time	type	subtype	aclass	asset	amount	fee	balance
txid 1	refid	date time 1	"trade"	""	"currency"	base asset	- base amount	fee	base balance
txid 2	refid	date time 1	"trade"	""	"currency"	asset	amount	fee	balance

[Griffsano]

Hi @shredEngineer @wearymanateevedaknotstabooresisting, I'm asking you since you worked on previous Kraken implementations: Do you happen to have old ledger CSV exports that include staking, unstaking and staking rewards?
Kraken apparently has changed the action types in the ledgers.csv and I want to ensure backwards compatibility before implementing the changes. Also, if you have any records of margin trades, that would be helpful. Thank you very much!

[shredEngineer]

@Griffsano Sorry, I don't have staking or margin trades in my data. :/

[oldgitdaddy]

Hi all, Is somebody working on this one? I get an error for type staking on my kraken report.
See my error below. Happy to hear from you soon! Happy new year!

2022-01-02 14:11:04,251 book         ERROR    /home/pi/CoinTaxman/account_statem                                                                                         ents/ledgers.csv: 13: Other order type 'staking' is currently not supported. Ple                                                                                         ase create an Issue or PR.
Traceback (most recent call last):
  File "/home/pi/CoinTaxman/src/main.py", line 44, in <module>
    main()
  File "/home/pi/CoinTaxman/src/main.py", line 32, in main
    status = book.read_files()
  File "/home/pi/CoinTaxman/src/book.py", line 904, in read_files
    self.read_file(file_path)
  File "/home/pi/CoinTaxman/src/book.py", line 859, in read_file
    read_file(file_path)
  File "/home/pi/CoinTaxman/src/book.py", line 555, in _read_kraken_ledgers
    raise RuntimeError

[shredEngineer]

I don't know if I can allocate time for this right now, but I do know that we would need some sample data beforehand. :) So if you could provide us with some real exported data on this, that would certainly help. I'm no Kraken power user myself, so I don't have exports with staking in it.

[oldgitdaddy]

Will be happy to share. How can we do that best? I have created a csv, with no fees, amount and balance. Still dates are there and txIDs.... So sharing this in public may be a bit odd.... Any idea is welcome.

[scientes]

Txids=replace with something
Date=if you feel uncomfortable with them change them to other dates
Amount and Fee would be still nice but you can anonimize them if you want

[Griffsano]

Hey, I'll push my working hotfix in a couple of hours and will tag you.

[oldgitdaddy]

Ok. I have removed txids and altered the dates. This is just a snapshot of the data....

I will be happy to test any fix you push.

"txid","refid","time","type","subtype","aclass","asset","amount","fee","balance"
"","QCCNPU5-JCHILF-COMBWG","2021-02-10 10:06:39","deposit","","currency","ZEUR",500.0000,0.0000,""
"","QCCNPU5-JCHILF-COMBWG","2021-02-10 10:08:19","deposit","","currency","ZEUR",500.0000,0.0000,500.0000
"","TOUPQD-AQOLA-AYA2VU","2021-05-11 18:55:10","trade","","currency","ZEUR",-200.0000,0.3200,299.6800
"","TOUPQD-AQOLA-AYA2VU","2021-05-11 18:55:10","trade","","currency","DOT",6.2500000000,0.0000000000,6.2500000000
"","TCSZJV-5YGSR-E3OQ6Q","2021-05-18 17:46:21","trade","","currency","ZEUR",-100.0000,0.1600,199.5200
"","TCSZJV-5YGSR-E3OQ6Q","2021-05-18 17:46:21","trade","","currency","DOT",4.1666666600,0.0000000000,10.4166666600
"","BUHHIWP-UBPSTP-CO65OD","2021-05-18 17:49:42","withdrawal","","currency","DOT",-10.4166666600,0.0000000000,""
"","RUI4U34-RQ42JX-Q334OO","2021-05-18 17:52:20","deposit","","currency","DOT.S",10.4166666600,0.0000000000,""
"","BUHHIWP-UBPSTP-CO65OD","2021-05-18 17:52:28","transfer","spottostaking","currency","DOT",-10.4166666600,0.0000000000,0.0000000000
"","RUI4U34-RQ42JX-Q334OO","2021-05-18 17:52:46","transfer","stakingfromspot","currency","DOT.S",10.4166666600,0.0000000000,10.4166666600
"","RUIGB6Q-GDOWY2-RRO72B","2021-05-20 01:15:51","deposit","","currency","DOT.S",0.0044380200,0.0000000000,""
"","ST3YXL2-5KJ7G-6WTIXW","2021-05-20 12:07:57","staking","","currency","DOT.S",0.0044380200,0.0000000000,10.4211046800
"","TP7MRH-JKLNG-OYGXHJ","2021-05-23 19:36:03","trade","","currency","ZEUR",-100.0000,0.1600,99.3600
"","TP7MRH-JKLNG-OYGXHJ","2021-05-23 19:36:03","trade","","currency","DOT",5.5555555500,0.0000000000,5.5555555500
"","BUHIYDN-EEDPTT-5MZ2OQ","2021-05-23 19:53:14","withdrawal","","currency","DOT",-5.5555555500,0.0000000000,""
"","RUIUE4T-FKBS3P-6CRCQV","2021-05-23 19:56:27","deposit","","currency","DOT.S",5.5555555500,0.0000000000,""
"","BUHIYDN-EEDPTT-5MZ2OQ","2021-05-23 19:56:41","transfer","spottostaking","currency","DOT",-5.5555555500,0.0000000000,0.0000000000
"","RUIUE4T-FKBS3P-6CRCQV","2021-05-23 19:56:50","transfer","stakingfromspot","currency","DOT.S",5.5555555500,0.0000000000,15.9766602300
"","RUINS55-CEAUJA-XGFBE5","2021-05-23 01:05:35","deposit","","currency","DOT.S",0.0140788800,0.0000000000,""
"","STSTDNK-FJMJY-W3A22T","2021-05-23 04:09:48","staking","","currency","DOT.S",0.0140788800,0.0000000000,15.9907391100
"","RUIVIHC-PFD4VQ-GJZ5K6","2021-05-23 01:05:28","deposit","","currency","DOT.S",0.0157602700,0.0000000000,""
"","ST6EP53-OLJO3-V7RAD7","2021-05-23 04:18:19","staking","","currency","DOT.S",0.0157602700,0.0000000000,16.0064993800
"","RUIADK5-7RA3R7-CSKSUB","2021-06-02 01:05:42","deposit","","currency","DOT.S",0.0210345700,0.0000000000,""
"","STFECFO-HNIES-7YWRIJ","2021-06-02 04:16:28","staking","","currency","DOT.S",0.0210345700,0.0000000000,16.0275339500
"","RUIIBQS-HIN67L-TOUO7J","2021-06-03 01:05:34","deposit","","currency","DOT.S",0.0157962600,0.0000000000,""
"","STNF5CY-XY37V-YETWHE","2021-06-03 04:17:24","staking","","currency","DOT.S",0.0157962600,0.0000000000,16.0433302100
"","RUIUGZ4-Z53SNL-2E63XM","2021-06-07 01:05:50","deposit","","currency","DOT.S",0.0210829800,0.0000000000,""
"","ST7R3DW-UUQPB-OYYCHT","2021-06-07 04:26:32","staking","","currency","DOT.S",0.0210829800,0.0000000000,16.0644131900
"","RUIT3NO-FTZXB6-4BC7HI","2021-06-10 01:05:42","deposit","","currency","DOT.S",0.0158325700,0.0000000000,""
"","STSZLWF-WJB2U-TJVUH7","2021-06-10 04:20:39","staking","","currency","DOT.S",0.0158325700,0.0000000000,16.0802457600
"","RUIQIO3-CO5FSQ-M7W4YS","2021-06-10 01:05:54","deposit","","currency","DOT.S",0.0211314300,0.0000000000,""
"","STB22YP-ZJBEV-6WZNCR","2021-06-10 04:26:08","staking","","currency","DOT.S",0.0211314300,0.0000000000,16.1013771900
"","RUICG6X-5VOBWX-EZEZVZ","2021-06-10 01:05:42","deposit","","currency","DOT.S",0.0158689500,0.0000000000,""
"","STMRQ6Z-QI43Z-4REUM5","2021-06-17 04:26:17","staking","","currency","DOT.S",0.0158689500,0.0000000000,16.1172461400
"","RUIPP26-ZMQAB3-JBHIG3","2021-06-21 01:05:49","deposit","","currency","DOT.S",0.0211800100,0.0000000000,""
"","STLXB5D-IIUK3-P4X5RK","2021-06-21 04:50:34","staking","","currency","DOT.S",0.0211800100,0.0000000000,16.1384261500
"","RUIMZKX-CDI5G5-ZKM6OY","2021-06-24 01:05:51","deposit","","currency","DOT.S",0.0159054000,0.0000000000,""

[gladomat]

I'm not sure my problem fits here, but I get the following error for a kraken import:

ledgers.csv row 7: Parameters for refid QYTM757-VSJIHJ-JGLBQP (Deposit) do not agree: coin.

The assertion fails at this line:

book.py", line 650, in _read_kraken_ledgers assert ( AssertionError: coin

And it stems from the following two values 'EUR.HOLD'=='EUR'.

Any ideas?

[provinzio]

CoinTaxman is trying to match kraken internal deposit and withdrawal operations together. It expects two refids (QYTM757-VSJIHJ-JGLBQP) to have the same operation type, change amount and same coin. In your case it seems like you have two lines in your csv with the same refid but different coins.

... and it looks like kraken is using weird symbols.... try to replace EUR.HOLD with EUR for a quick fix. Does this work for you? Do you have other coins in your account statement with the suffix .HOLD?

[gladomat]

Just to clarify, I do indeed have two lines with the same transaction and this is what it looks like

1. [missing txid] QYTM757-VSJIHJ-JGLBQP date deposit currency ZEUR 11 0.1
2. txid001 QYTM757-VSJIHJ-JGLBQP date deposit currency EUR.HOLD 11 0.1 10.9

Do you have other coins in your account statement with the suffix .HOLD?

Somehow strange, it only appears for deposit and spend and only in a limited time frame. After that time frame it turns into ZEUR. I'll try changing it to EUR and will report.

[provinzio]

Btw have a look at core.py. The variable kraken_asset_map is meant for this kind of conversion. :) I'll hope that this fixes your problem, so that we can add EUR.HOLD to the list for correct conversion.

[gladomat]

It ran through successfully! Thanks for the tip! You can add it to the kraken_asset_map. Or I can add it and do a PR.

[provinzio]

@gladomat Just did it :)

[githubuserthatsright]

I do not know if my stuff belongs here or not.
I am trying to use CTM to calculate not just my spot trades, but also the margin trades that without asking me converted the underlying crypto to fiat for a margin trade.
What I mean is: I margin trade BTCEUR long. Since I still have spot BTC, instead of paying the fee for opening and closing the margin trade in EUR, Kraken, the f***ing ***ots steal my bitcoin and thus incur the risk of a taxable profit, because it MIGHT be that the 5 EUR they are taking from my BTC might be converted at a higher price than at purchase time.

This is of course not manually calculateable at all if you have > 1000 spot and margin trades as FIFO and fractions become utterly insane.

I do not even know if CTM can do any of this. But even just exporting my ledger and running CTM leads to this:

2024-08-27 14:09:55,825 book         INFO     Reading file from exchange kraken_ledgers at C:\Users\username\AppData\Local\Programs\CoinTaxman\account_statements\ledgers.csv
Traceback (most recent call last):
  File "C:\Users\username\AppData\Local\Programs\CoinTaxman\src\main.py", line 71, in <module>
    main()
  File "C:\Users\username\AppData\Local\Programs\CoinTaxman\src\main.py", line 36, in main
    status = book.read_files()
  File "C:\Users\username\AppData\Local\Programs\CoinTaxman\src\book.py", line 1811, in read_files
    self.read_file(file_path)
  File "C:\Users\username\AppData\Local\Programs\CoinTaxman\src\book.py", line 1763, in read_file
    read_file(file_path)
  File "C:\Users\username\AppData\Local\Programs\CoinTaxman\src\book.py", line 716, in _read_kraken_ledgers
    assert change
AssertionError

I do not know if this is because the ledger mixes margin trading and spot (which this issue seems to be about???? The error/debug info isn't helpful to me at all to know what is wrong), but clearly I cannot be the only person affected by an exchange messing up like this, especially as a Kraken user.
There is the 1:1 same story with accidentally trading BTCUSD, which gives you USD as profit = same as buying USD as a fx thing, which also would count as the same logic as a crypto trade: Taxable if profitable within 365 days when converted back to EUR.

All of this is such a nightmare and I am pretty sure I have a decent chance of becoming an unwilling criminal thanks to exchanges not giving us summaries and none of these calculations being possible by hand.

I don't know what to do or what is wrong with CTM. I do not see a solution here.

I used cointracer (well worth supporting but abandoned in 2022 sadly, also open source and truly a great solution) for the spot profit / losses, but these tiny fee conversions? I have no idea what to do..

[provinzio]

CoinTaxman expects the changes to be positive. Therefore the assert.

If your change value is negative, you have to make sure that the row from the csv is correctly converted to a CoinTaxman. You might to program an if case if change<0 and append the correct operation type to book.