How to create transactions history tables with python for your tax report

in HiveDevs4 years ago (edited)


source

I finally finished the scripts for storing transaction lists into an excel / csv file, which can then be imported into cointracking, cryptotax or koinly for generating a tax/income report.

You can find the scripts at https://github.com/holgern/hive-reports. Please create an issue in case of a bug a feature wish.

How does the scripts work

There are two scripts available for each service:

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,
Handling the hard fork on cryptotax
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

creating a new custom wallet

In the next step, you can drag the generated csv file into the import field.
You also need a second custom wallet:
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 or on PeakD

Sort:  

Hi @holger80, you have received a small bonus upvote from MAXUV.
This is to inform you that you now have new MPATH tokens in your Hive-Engine wallet.
Please read this post for more information.
Thanks for being a member of both MAXUV and MPATH!

Great work mate! This will work awesome for those with some dev chops or wish to get their hands dirty (like myself haha).

Can't wait to test out your solution vs our @HiveBit solution tonight and see where we can maybe improve or find even better solutions together!

Posted using Dapplr

I never even considered having enough Hive to worry about the tax filing. I may be getting to that point soon though!

Right? 😆

Wow muy interessnte y instructivo tu ppublicacion, gracias por compartir, te sigo y te dejo mi voto.

Thanks, this is exactly what I have looking for. By the way, Cointracker does accept both CSV and Excel file formats for bulk inputs.

Congratulations @holger80! You have completed the following achievement on the Hive blockchain and have been rewarded with new badge(s) :

You distributed more than 19000 upvotes. Your next target is to reach 20000 upvotes.

You can view your badges on your board And compare to others on the Ranking
If you no longer want to receive notifications, reply to this comment with the word STOP

Do not miss the last post from @hivebuzz:

Feedback from the last Hive Power Up Day
Hive Power Up Day - Let's grow together!

Thanks for this. The tax laws in my country differ, but this code will be a good basis for my own.
One decision I have to make is how to deal with HP inflation. I can either treat the inflation as income and pay income tax with the advantage that HIVE <-> HIVE POWER conversions (power up/down) might not be treated as token conversion events. Or, I can treat HIVE POWER as VESTS, in which case I can ignore inflation because VESTS doesn't grow and then treat HIVE <-> HP conversions as token conversion events.
We don't pay capital gains tax on buy-and-hold investments here so the second option might be useful.
Aaaah tax.

This might not be relevant but why do you even need to pay taxes if government doesn't know how much crypto you are holding or earning?

Couldn't you just keep your crypto without paying taxes?