【edited 2017-09-06】My Detailed Crypto Trading History with Google Spreadsheet, automatically fetching rates and calculating profit/loss

Visit this Google Spreadsheet.

How To Make Trading History

  • Just type. No automation.

  • When I bought coins not by JPY, but by BTC or ETH, I wrote JPY equivalence on "acquisition cost column" by hand.

  • When you sell coins, acquisition cost is not sell price, but buy price * (sell amount / holding amount).

    • Otherwise, acquisition cost will be minus when you win! This is a problem for profit percentage calculation.

    • This is maybe the moving average method.

How to calculate profit automatically

  • Google Spreadsheet (or Excel, Numbers, etc.) has very awesome function IMPORTHTML!

  • In new sheet ("rate" sheet in this case), write =IMPORTHTML("https://coinmarketcap.com/","table"), then you get super nice table.

    • This URL above shows only top 100 coins. You may want to get all coins, but when you input =IMPORTHTML("https://coinmarketcap.com/all/views/all/","table"), Google Spreadsheet says too much data error.
  • Since I am Japanese, I want to get JPY data, but even when I typed =IMPORTHTML("https://coinmarketcap.com/#JPY","table"), I still got USD data.

  • So, I got JPY-USD exchange table like this. =IMPORTHTML("https://stocks.finance.yahoo.co.jp/stocks/history/?code=USDJPY=X", "table") (K1 cell in this case)

struggles

  • You have to tinker many things.

  • Price column (D column in rate sheet) contains cumbersome dollar mark. So, Google Spreadsheet think that this is just a characters, not a numeric. You cannot calculate with this!

    • You should remove dollar mark by replace function, and convert it into numeric by value function like this. =value(replace(D2,1,1,))

    • replace(D2,1,1,) means that characters in D2 cell from 1st character with 1 length will be converted into "".

    • If you use other than USD, you may multiply it by exchange rate like this. =value(replace(D2,1,1,)) * $O$2 (I column in this case)

    • You must type $O$2, not O2, because of autofill behavior.

  • Go back to main sheet (history sheet in this case), and let us insert appropriate exchange rate from rate sheet.

  • For example, I will insert XRP-JPY exchange rate (M column in this case).

    • =sumif(rate!E:E, "*"&L4&"*", rate!I:I)

    • This means that from E column in rate sheet, you search the characters in L4 cell ("XRP"), and sum up the numeric in I column.

    • You may "sum up" by mistake when there is another unit which contains "XRP". Please tell me better function than sumif.

  • 【added 2017-09-06】You should use vlookup. Write =vlookup(J4, rate!$B:$I, 8, false) in M4 cell (ripple rate).

  • 【added 2017-09-06】This means that you search the characters in J4 cell ("ripple") in B column to I column, and that you fetch the 8th cell in the row (JPY rate) which contains the hit cell ("ripple"). And these data are not sorted ("false"). It does not work if you write "true," which I do not understand.

  • You can configure conditional formatting. If profit is more than 0, background color will be green, otherwise red.

  • Purple means hand-written rate. You cannot get minor coins (remember you got top 100 coins), and tokens which have not yet listed on exchange.

  • holding (K column) also uses sumif. For example, =sumif(D:D,L4, C:C) in ripple.

    • This means that you sum up amount (C column), if D column (unit) contains the characters in L4 cell ("XRP").

taxable

When you sell coins and get fiat (like USD), the profit is taxable. So if you hold profit in the form of coins forever, the profit is not taxable (now, this will be changed in the future). But any coins may crash. Not suited for long-time storage. Is USDT taxable (though USDT may crash…)?

【added 2017-09-06】

Japan National Tax Agency says that the profit is “miscellaneous income,” which is up to 45%. And they might say in the future that when you sell some cryptos and get another cryptos (even if not fiat), the profit is taxable. Keep watching. http://www.nta.go.jp/taxanswer/shotoku/1524.htm

Leave a Reply

Your email address will not be published. Required fields are marked *

CAPTCHA