The purpose of this Cryptocurrency Transaction Tracker is to track all cryptocurrency transactions and provide a summary of the transactions that shoul be reported on taxes.

I am not a CPA, nor do I have extensive tax knowledge. The information in this spreadsheet was created using my understanding of current tax law and how Cryptocurrency transactions should be reported.

I advise you to get tax advice from your tax advisor and use this for your own knowledge and as a guuide - I do not take any responsibility for any incorrect reporting of taxes.

This spreadsheet was also create for USA taxes, and dollar amounts are returned in USD. If you would want a different currency, certain formulas would have to be updated.

You must have the CRYPTOFINANCE add-on installed for this sheet to work - it is used to get the historical and current USD value of each coin for each transaction.

https://chrome.google.com/webstore/detail/cryptofinance/bhjnahcnhemcnnenhgbmmdapapblnlcn?hl=en

Information about how to use this add-on can be found here:

https://jbuty.com/how-to-get-crypto-currencies-rates-and-more-in-google-sheet-1a57e571bc14

Please donate to him if you like this function - it is extremely powerful and easy to use.

CRYPTOFINANCE only gets the coin's historical price at each hour, not each minute. So although the price may be slightly different than the exact minute you made a trade, at the end, those minor differences will all wash out as long as you accurately report the amount of fiat you received.

The add-on uses the coinmarketcap API, which has limits to how often the API can be called. If you have LOTS of transactions, it may take a while for the API to work correctly (can get stuck on "...Loading"

If you have lots of transactions, I would only do a small chunk at a time, and once values show up, copy the formulas that contain the CRYPTOFINANCE formula and "Paste Values" so limit the number of API calls that are made. Be patient.

In fact, once you get going, I would paste the values of all the historical transactions for every column. The reason for this is two-fold:

1) The historical value will not change

2) Google Sheets has limits to how many cells can contain formulas - if you have lots of transactions, it would be in your best interest to limit the number of cells containing formulas by pasting the values once you have entered your transactions

This sheet only includes a couple examples of how it calculates values from exchanges, mining, etc.

Due to the high number of different coins, exchanges, etc., it is impossible to include examples of each.

If you use different exchanges, mine different coints, etc. you will need to create your own formulas, using the example formulas provided as a guideline to help you.

As a disclosure, I've not done extensive testing so there are likely a few mistakes.

Let's get started. It may be easier to follow along with the sample spreadsheet as well.

This sheet is the template sheet for which transactions will be recorded. For each currency you have transactions in, you need a corresponding copy of the BLANKSHEET tab.

For example, if you bought ETH, sold it for BTC, and then sold BTC for ARK, you would need 3 copies of this tab, each named with the "ticker" of the coin.

You should only have to fill out the fields that are highlighted yellow.

All transactions should be reported in correct date/time order. This is to accurately calculate the cost basis of each transaction, as well as to determine if the transaction would be considered long-term or short-term

BLANKSHEET is laid out as follows:

When you paste "sell" transactions, it is important that you only paste them 1 at a time. There are formulas to calculate if the transaction is both long-term and shor-term.

i.e. You bought 1 BTC on 1/1/2016. You bought 1 BTC on 6/1/2017. On 7/1/2017, you sold 1.5 BTC. Part of that transaction would be long-term gain/loss, and part of it would be short-term gain/loss.

If a scenario similar to above happens, the row below the "sell" transaction will calculate what the short-term part of the transaction would be, and update values accordingly.

If you paste 2 "sell" transactions at the same time, then you overwrite some of the formulas in this calculation and it will mess up the spreadsheet.

All tabs have red highlighted cells about 50-100 rows down. These are the last cells in which formulas exist. It is a reminder to copy the entire row and add new rows, if needed.

Due to Google Sheets' limits on cells with values, I didn't want to have formulas 1,000 rows down on every sheet.

Do not "paste values" of the "taxable" sections of this sheet (the last 3 sections)

Sometimes, the CRYPTOFINANCE function will be unable to calculate the historical USD value of a coin (coins that don't translate to USD directly).

If this happens, you have to enter the "paired" coin that can be used to get the USD value, in column AV.

This tab has many hidden "helper" columns to calculate the formulas. Each helper column has a header that should help explain what it is doing.

If you start out buying ETH or BTC or LTC on Coinbase or another platform for USD, you will need to manually enter those transactions. See the sample spreadsheet for how that is done.

There is a "Funds Lost Between Trade" field that is open-ended. You enter the amount of funds that were "lost" between the last "sell" transaction and the current "sell" transaction.

These funds are not calculated in your net proceeds, but they are needed to accurately track how much of each coin you have left.

For example, you buy 1 ETH on Coinbase, you transfer it to Bittrex and sell it for .1 BTC. You enter that "buy" transaction for ETH on the ETH tab, you then enter the "sell" transaction on the ETH tab.

But let's say that the fee to transfer that ETH from Coinbase was .01 ETH. That .01 ETH is the value of "funds lost".

This field could be transfer fees, voting for a delegate (aka ARK), etc.

The "third" section of these tabs are the "buy" transactions that correspond with the "sell" transaction. You copy and paste the values of this section in the "buy" coin tab.

If you have Mining or PoS "buy" transactions, the "Obtainment Method" (column C) must contain "PoS" or "Mining" somewhere to properly register as a taxable "other income" transaction

Taxable Transactions

This tab has a button that you can click to generate the taxable transactions for any tax year.

Just update the "Tax Year" cell before clicking the button.

Do not change the format of the "Tax Year" cell, as the formulas that read it need it in that specific format to function, it seems.

The button will go to each sheet and paste the transactions that need to be reported as "interest" (mining, proof of stake reward, etc.), long term gains, and short term gains.

This tab calculates the current portfolio value of each coin you have.

Just list each coin in column A (this should be the tab name, i.e. BTC or ETH).

Change Cell C2 to show the current value. You can change it to any number (you just need to change it so the formulas can update)

If cell C2 is 0, then the value will show as 0 for all coins - this is if you are entering transactions and you don't want to keep calling the coinmarketcap API, since it will be unnecessary calls, which could cause it to stop returning data for a short time.

Bittrex Data/Binance Data

These tabs demonstrate different formulas for how any "trade" transactions should be reported. I have both buy/sell transactions from Bittrex, but Binance I only have "buy" transactions.

The data comes straight from the exchanges (you need to find the button that exports all of your trades)

The Binance tab is incomplete, since I did not have an example of what a "sell" transaction would look like.

If you use other exchanges, use the formulas in these tabs to help determine how the data should be formatted for that exchange.

For Bittrex and Binance, just paste the exported data from those exchanges into the "second" section. The formulas that calculate the values for the BLANKSHEET tabs are in the "first" section

These transactions would be pasted in the "second" section of the BLANKSHEET tabs, as "sell" transactions.

You have to be careful to paste the right transaction into the right tab. Whenever you trade coin A for coin B, you always paste the transaction in the coin A tab.

The "third" section of the BLANKSHEET tab will then calculate the values you paste in the coin B tab.

This is all relatively straightforward, except for the "fee" section.

You need to make sure that when you calculate the "fee", it is not being included as part of the amount you bought/sold of a coin.

i.e. You buy 10 ETH with 1 BTC on Binance, and there is a .1 BTC fee.

You need to make sure that when formatted, you know if that .1 BTC was included in the 1 BTC you sold or not. If it is included, the formula would have to account for that.

i.e. The "amount of selling coin" would need to say .9 BTC, and the fee would be .1 BTC

It appears as thought Bittrex includes the fee on a "sell" transaction, but it is broken up in the "buy" transaction.

Binance probably does it a similar way, but I could not confirm that.

The "amount selling" needs to always be the amount of the coin you got rid of to obtain another coin. You can review the formulas to see how that affects which column is used as the "amount selling" and the "amount buying"

ARK Wallet Data/VTC Mining

These are 2 more examples of different formulas for how "buy" transactions should be reported (using data exported from the ARK Wallet or VTC Core Wallet)

The formulas are also straightfoward and are merely examples for VTC and ARK. They could be retrofitted to other coins.

Due to the high volume of how many mining transactions you could have, you may have to do this part in chunks, since the coinmarket API (using CRYPTOFINANCE) function is used and has limits to how many times it can be called in a given period of time. Be patient.

You paste the exported data in the "second" section. The formulas that calculate the values for the BLANKSHEET tabs are in the "first" section.

These transactions would be pasted in the "first" section of the BLANKSHEET tabs, as "buy" transactions.

Future Updates I Would like to Implement

Automatically copy formulas when you paste a transaction to limit the need to manually copy rows.

Have a script copy the spreadsheet each tax year and remove the transactions that would not be needed for the next tax year (if you have many transactions, this would help keep the spreadsheet smaller year-to-year.