How to create transactions history tables with python for your tax report
[source](https://pixabay.com/de/photos/audit-buchhaltung-finanzbuchhaltung-4190945/)
I finally finished the scripts for storing transaction lists into an excel / csv file, which can then be imported into [cointracking](https://cointracking.info/), [cryptotax](https://cryptotax.io/) or [koinly](https://app.koinly.io/) for generating a tax/income report.
You can find the scripts at https://github.com/holgern/hive-reports. Please create an [issue](https://github.com/holgern/hive-reports/issues) in case of a bug a feature wish.
## How does the scripts work
There are two scripts available for each service:
* cointracking
* [liquid-hive-report.py](https://raw.githubusercontent.com/holgern/hive-reports/master/cointracking/liquid-hive-report.py)
* [staked-hive-report.py](https://raw.githubusercontent.com/holgern/hive-reports/master/cointracking/staked-hive-report.py)
* cryptotax
* [liquid-hive-report.py](https://raw.githubusercontent.com/holgern/hive-reports/master/cryptotax/liquid-hive-report.py)
* [staked-hive-report.py](https://raw.githubusercontent.com/holgern/hive-reports/master/cryptotax/staked-hive-report.py)
* koinly
* [liquid-hive-report.py](https://raw.githubusercontent.com/holgern/hive-reports/master/koinly/liquid-hive-report.py)
* [staked-hive-report.py](https://raw.githubusercontent.com/holgern/hive-reports/master/koinly/staked-hive-report.py)
The liquid-hive-report script creates a transaction list regarding all movements to and from the wallet. The staked-hive-report scripts creates a transaction list regarding staked token.
Let's check an example where 100 HIVE have been powered up.
The first script will create a withdrawal, where 100 HIVE are removed from the wallet balance, whereas the second script creates a deposit entry about 100 HIVE.
Powering up and powering down the same amount of HIVE are not taxable events. They are handled as a normal transfer between your Hive wallet and your virtual powered up Hive wallet. Whenever more HIVE is powered down as it was powered up previously, a deposit transaction is created and the newly added HIVE are labeled as staking rewards.
I do not distinguish between author, curation, inflation or witness rewards, I only care when rewards have been liquidated and then they are considered just as staking rewards.
Assuming you powered up 100 HIVE and you earned another 100 Hive Power through rewards. These 100 Hive Power are stored in the blockchain as VESTS.
The time when these 100 powered up HIVE were earned do not matter for the tax calculation, as they are not moveable or tradable. As long as these 100 HIVE stay powered up, they are sleeping. At that point in which more than 100 HIVE have been powered down, a taxable event is created and an value can be attached to the received rewards.
I handle this by counting the amount of powered up HIVE, whenever the sum is getting negative (more powered down than powered up), I add a deposit transaction the difference amount as staking rewards.
I'm not a tax consultant, so everything I wrote may be completely wrong and I do not take any responsibility.
## Hive fork
Only transactions after block number 41818753 are considered. I'm counting the exact amount of HIVE/HBD at the fork time and create a deposit with these amounts.
The sum of powered up HIVE is also counted and a deposit is created at the fork time for the powered up HIVE.
The deposits are labeled as airdrop/fork depending on the used service. For Cryptotax, this needs to be done manually. I created a Hardfork deposit and a withdrawal on the Steem wallet manually, in order to handle this.
You can see how I added two entries about 55934.253 HIVE to my steem_holger80_powered_up wallet,
I clarified then the deposit as hard fork:
I did the same for HIVE and HBD for the steem_holger80 wallet.
## How to use the scripts
### Liquid HIVE/HBD
The following parts of the script needs to be modified:
```
stm = Hive(node=nodelist.get_hive_nodes())
# stm = Steem(node=nodelist.get_steem_nodes())
print(stm)
account_name = "holger80"
data_account_name = "hive_%s" % account_name
symbol = "HIVE"
backed_symbol = "HBD"
hive_fork_block = 41818753
has_fork = True
limit_to_year = False
current_year = 2020
csv_filename = "koinly_%s_%d.csv" % (data_account_name, current_year)
```
* `account_name` - hive/steem account name
* `symbol` - HIVE / STEEM
* `backed_symbol` - HBD / SBD
* `has_fork` - True for Hive, False for Steem
* `limit_to_year` - When set to True, a separate account is created for each year. This has the advantage, that the previous year can remain untouched, when something is changed for the next tax report. The remaining balance is transferred at the end of each year to the next account.
* `current_year` - Only used when `limit_to_year` is True
### Staked HIVE
The following parameter can be changed for the staked script:
```
account_name = "holger80"
data_account_name = "hive_%s_powered_up" % account_name
symbol = "HIVE"
hive_fork_block = 41818753
has_fork = True
limit_to_year = False
current_year = 2020
```
## Automatically classify transfers
You can add a `clarification` depending on account names for transfers:
For example for incoming transfers:
```
if ops["from"] in ["reward.app"]:
clarification = "staking"
else:
clarification = ""
```
You can add this in the `elif ops["type"] == "transfer":` section.
I will consider of some kind of json configuration file to handle this. Without modification, every transfer is stored without clarification.
## Running the script
You need python and the following packages:
```
pip3 install beem pandas
```
## Koinly
The really great thing is that you can use this service for free and there is no transaction limit. You only need to pay when you want to create a report.
You need to create two custom wallets:
Go to https://app.koinly.io/wallets/new and enter
In the next step, you can drag the generated csv file into the import field.
You also need a second custom wallet:
where you can import the results of the staked-hive-report script.
## Cryptotax
You need to pay when you want to enter more than 50 transactions.
You do not need to create a new wallet, it will be created automatically when you import the excel file.
The import of both excel files is done here: https://app.cryptotax.io/import-manual
## Cointracking
You need to pay when you want to enter more than 200 transactions.
You do not need to create a new wallet. It will be automatically created. The import is done here
https://cointracking.info/import/import_csv/
Only csv files work.
You need to delete the wallet, when you want to import a newer version of the csv file.
## Conclusion
Doing tax for crypto is not fun and takes really a lot of time. I think my scripts do help a lot, as entering everything by hand is not feasible.
Creating the tax report for Steem was complicated as the account history api had returned some event twice. My script handle this now.
There are some edge cases which are not handled by my script, e.g. escrow and mining in the early beginning of Steem.
Let me know, if you use a different tax service that has a different import layout. If you send me a template, I can add this service quite easily.
___
*If you like what I do, consider casting a vote for me as witness on [Hivesigner](https://hivesigner.com/sign/account-witness-vote?witness=holger80&approve=1) or on [PeakD](https://peakd.com/witnesses)*
See: How to create transactions history tables with python for your tax report by @holger80