税金の計算をする際に、避けて通れないのが、通貨の時価を算出しなければいけない事です。仮想通貨の履歴を取得しても、そこから円換算した時価を算出しなければいけない為、相当厄介です。地獄でした・・。
今回、私が仮想通貨の終値を取得し、それを自身の取引履歴に反映させるまでを記事にしてみましたので、参考になれば、幸いです。実際に反映させてみると意外な結果が出ていて、正直ビックリしました。
※最初にお断りしておきますが、私は税理士ではないので、あくまで仮想通貨の金額を計算する上での話となります。税金についてのお問い合わせは税理士さんに確認をして下さい。
以下の内容に沿ってお話していきます。エクセルの関数を多用していますし、内容もかなり複雑ですので、余裕のある時に読まれる事を推奨します。
１、仮想通貨の取引履歴を取得
各国内/海外取引所の取引履歴やメタマスク等の取引履歴の取得方法は、以下のリンクにて説明していますので、確認をお願い致します。
取引履歴を取得したら、以下の画像のような形でファイルを作成しておきます。
以下の画像はG-taxのファイル形式でまとめたものになります。※あくまで一例ですので、ご自身に合った形でまとめて頂ければと思います。
最初に、この全取引履歴を1つのファイルにまとめた形式のものがなければ、話になりません。これを作るために、相当かかります。
以下の例は、「★しゅん」さんのファイル形式になります。
国税局の形式を見たい方は、以下のリンクから確認ができます。
暗号資産に関する税務上の取扱い及び計算書について（令和3年12月）｜国税庁 (nta.go.jp)
仮想通貨の全取引の通貨記号を取得（手数料も含む）
取引履歴の作成が出来たら、全取引履歴の通貨記号を取得しておきましょう。
ピポットテーブルを使うと簡単に全ての通貨名の取得ができます。
- 表内のセルを選択
- 「Ctrl」+「A」キーを押して、表全体を選択
- 上部タブ「挿入」→「ピポットテーブル」を選択
- 「テーブル又は範囲から」を選択、「OK」を押して、新規シートに作成
- 「取引通貨名(+)」の項目を「行」の箇所に移動※買い側の取引通貨の記載している列の項目を選択
- 出来たピポットテーブルをコピペして、列をずらして貼り付け
- 「取引通貨名(+)」の項目を「行」から元に戻し、代わりに「取引通貨名(-)」を「行」の箇所に移動※売り側の取引通貨の記載している列の項目を選択
- 5.と7.で出来たピポットテーブルの差分をVLOOKUPを掛けてチェックし、差分を加えて完成。
表内のセルを選択→「Ctrl」+「A」キーを押して、表の全体を選択→「挿入」→「ピポットテーブル」を選択→「テーブル又は範囲から」を選択します。
ピポットテーブルの作成ダイアログボックスが出てくるので、変更等はせず、そのまま「OK」をクリック※設定が変更されていなければ、新規ワークシートが選択されているはず
新規ワークシートにピポットテーブルの作成項目が出てきますので、「取引通貨名(+)=買い側の取引通貨」の項目を「行」の箇所に移動させる事で、A列に重複の無い「取引通貨」を抽出した列が作成されます。
出来たピポットテーブルをコピペして、列をずらして貼り付け→「取引通貨名(+)」の項目を「行」から元に戻し、代わりに「取引通貨名(-)=売り側の取引通貨」を「行」の箇所に移動※取引通貨の記載している列の項目を選択
A列とE列に作ったピポットテーブルの差分をVLOOKUPでチェックします。F列に「=VLOOKUP(E4,A:A,1,FALSE)」という関数を入力します。※これは、E列にある通貨記号がA列にもあるかどうかをチェックする事になります。同じ記号が出てくれば、A列にもE列にも同じ記号が存在する事になります。
これをE列の下までコピペします（F4セルの右下にカーソルをもっていき、カーソルが「⇒」から「+」マークに変わったら、ダブルクリックするとE列の下まで関数がコピペされます）。
F列に「＃N/A」があったら、売り側の通貨記号の中で、買い側には、記載が無い通貨記号になりますので、それらを買い側の通貨記号に加える事で、全ての通貨記号が網羅できるはずです。※基本的には、買い側にしか新しい通貨記号は出ない想定ですが（新通貨を円/USDT等で購入をした場合、必ず買い側に新通貨が出る為）、しかし、ボーナスやエアドロップ等で取得した通貨の場合は、売り側だけにしか表記されていないものがあります。その為に全ての通貨記号を拾う為に、買い側/売り側の差分をチェックして網羅します。
同様に手数料の箇所についても通貨記号を取得しておきましょう。買い側と売り側にも記載がない手数料が発生している可能性もあります。
２、仮想通貨の時価（円換算）履歴を取得
項目１で取引履歴から割り出した全ての仮想通貨の通貨記号をエクセルに取得をして頂いたという前提でのお話となります。
全取引履歴の仮想通貨記号の日本円換算額をエクセルファイルに取得してもらいます。
取得方法については、以下のリンクの3つ目の項目を参照していただければと思います。
「CoinmarketCap」から取得したエクセルファイルは以下の様になっていると思います。
この時、各通貨毎にシートを分けて取得。シート名には通貨記号を記載（各履歴がどの通貨のものなのかを分かるようにしておきましょう）。
後で１つのシートに結合するので、最初から１つのシートで対応したいという方は、目次３-②まで進んで、取得しながら修正を加えていっても良いかもしれません。
上記２つのファイルをエクセルの関数を使って上手く組み合わせて時価（円）を計算していきます。
３、エクセルで関数を使ってファイル加工
①、目次1で取得したファイルを加工
以下の手順で加工を行います。※但し、次の②の作業を先に完了しておく事が必要です。②のファイルを参照する必要が有る為
- 目次１のファイルの日時部分を分解して、年月日だけに
- 年月日+通貨記号を結合
- 結合したキーを基にVLOOKUP関数を使って終値を取得。この関数を入れる前に、事前に目次２で取得したファイルを加工しておく必要が有る
- 終値に数量を掛けて日本円に換算
関数の設定については、皆さんの作っているファイルの項目の並び順や列が異なっている場合は、参照先が変わるので、各自で修正して下さい。
K列～N列でB列の日付から年/月/日を取り出し、再結合させます
K列：年。B列の日付を関数で分解して「年」だけを取り出し。K2セルに「=YEAR(B2)」入力し、あとは下までセルコピー
L列：月。B列の日付を関数で分解して「月」だけを取り出し。L2セルに「=MONTH(B2)」入力し、あとは下までセルコピー
M列：日。B列の日付を関数で分解して「日」だけを取り出し。M2セルに「=DAY(B2)」入力し、あとは下までセルコピー
N列：「日付結合」。「年月日」を結合した列。N2セルに「=DATE(K2,L2,M2)」入力し、あとは下までセルコピー。
O列：「買い側結合」。「年月日」と「買い側の通貨記号（D列）」を結合した列。O2セルに「=N2&D2」入力し、あとは下までセルコピー。
P列：「売り側結合」。「年月日」と「売り側の通貨記号（F列）」を結合した列。P2セルに「=N2&F2」入力し、あとは下までセルコピー。
Q列：「手数料結合」。「年月日」と「手数料の通貨記号（I列）」を結合した列。Q2セルに「=N2&Q2」入力し、あとは下までセルコピー。
ここまで出来たら、もうほとんど完成間近。
O～Q列をキーにして、②で作った全通貨結合シートから時価を引っ張ります。
R列：「買い側円時価」。O列をキーとして全通貨結合シートから円時価を取得。R2セルに「=VLOOKUP(O2,全通貨の時価履歴!A:F,6,FALSE)」入力し、あとは下までセルコピー。
S列：「売り側円時価」。P列をキーとして全通貨結合シートから円時価を取得。S2セルに「=VLOOKUP(P2,全通貨の時価履歴!A:F,6,FALSE)」入力し、あとは下までセルコピー。
T列：「手数料円時価」。Q列をキーとして全通貨結合シートから円時価を取得。T2セルに「=VLOOKUP(Q2,全通貨の時価履歴!A:F,6,FALSE)」入力し、あとは下までセルコピー。
※「＃N/A」と出ているのは、マッチングしなかったものになります。とりあえず無視してOKです。全ての取引の円換算額が必要となるわけではないからです。送付/預入/ボーナス等の取引においては、円換算額は不要です。
ここで、買い側と売り側の通貨の円時価を算出していますが、実はほとんどの計算では、売り側の換算額しか使いません。というのも、売買時の買い側と売り側の円時価は終値とは一致しないからです。それも当たり前で、CoinmarketCapで取得した通貨履歴はあくまで各通貨の終値ベースであって、あなたが取引した時の時価ではないからです。
仮にUSDT/XRPの売買をした時に、あなたが取引した時の価格がUSDT（120円）：XRP（75円）だったして、それぞれの通貨価値は終値では、それぞれ価格が変わってしまいます。その為、終値ベースではUSDT（125円）：XRP（71円）となっていたりしますので、買い側と売り側の通貨をそれぞれで計算しても、円換算値は一致しません。その為、私は売り側のみの換算額を参照し、買い側の換算額を売り側の換算額に合わせています。
しかし、一部の通貨では売り側の通貨が、まだ上場されていなかったり、CoinMarketCapに取引価額が掲載されていない場合があります。その時に、買い側の円通貨換算額を割り当てるという事をしています。
本題とは、逸れましたが、円換算額を算出しましょう。円換算額は各通貨の数量に円時価をかけ合わせる事で算出ができます。
U列：「買い側円換算」。U2セルに「=E2*R2」入力し、あとは下までセルコピー。
V列：「売り側円換算」。V2セルに「=G2*S2」入力し、あとは下までセルコピー。
W列：「買い側円換算」。W2セルに「=J2*T2」入力し、あとは下までセルコピー。
これで円換算額も算出できました。ここからこのデータを精緻化していきます。次の目次（４、全てのデータが計算できているかを確認）で確認を行います。
②、目次２で取得したファイルを加工
以下の順にファイル加工を行います。
シート毎に通貨が分かれている事を前提に話を進めます。
- 「全通貨結合」シートを作成
- A列：「結合」列を作成し、B列～H列は取得した内容を貼り付け。I列：通貨名 / J列：年 / K列：月 / L列：日を作成
- B列～H列は取得したデータをそのまま貼り付け。I列は取得データの通貨名を記載
シートを新しく作り、各列に以下項目を設定します（下記画像参照）。
A列：「結合」。「年月日」と「通貨記号」を結合した列。A2セルに「=DATE(J2,K2,L2)&I2」入力し、あとは下までセルコピー。※VLOOKUP関数の検索列にする為
B列～H列：通貨履歴で取得した内容と同じ項目を設定します。そのまま貼り付けを行っていきます
I列：通貨記号。通貨履歴で取得した通貨記号を対象となる全ての行に記載
J列：年。B列の日付を関数で分解して「年」だけを取り出し。J2セルに「=YEAR(B2)」入力し、あとは下までセルコピー
K列：月。B列の日付を関数で分解して「月」だけを取り出し。K2セルに「=MONTH(B2)」入力し、あとは下までセルコピー
L列：日。B列の日付を関数で分解して「日」だけを取り出し。L2セルに「=DAY(B2)」入力し、あとは下までセルコピー
下記の様なシートができると思います。これを各通貨全て順にこなしていき、全ての通貨を１枚のシートにまとめます。
1通貨の貼付と関数の設定が終わったら、そのまま次の通貨のデータを貼り付けて、関数の設定を行っていきます。
全通貨結合シートが出来上がったら、①で作っているエクセルファイルにシートコピーをしておきましょう。
全通貨結合シートのシート名部分を、右クリックしてショートカットメニューを出して、「移動またはコピー」をクリック
ダイアログボックスが出るので、移動先ブック名の欄の右の▼をクリックするとブックの選択画面が出るので、①の取引履歴のファイルを選択し、下の「コピーを作成する」にチェック入れて、「OK」をクリックします。①のファイルに同じシートが複製されています。
４、全てのデータが計算できているかを確認
前目次までで、計算は全て終わりましたが、ここからは、しっかりと計算ができているかを確認作業をしていきます。なぜ、この作業を行うのかと言うと、通貨の売買取引があるのに、円換算額が計算できていない箇所を割り出す為です。
エラーが出る可能性としては、以下の通りになります。
- 履歴データが取れていない。取り忘れ又は上場直後で履歴データそのものがない為に起こる
- 取引履歴の通貨記号が間違っている。私のケースでは、GATEの履歴データで、USDTの取引データなのに、USDと記載されていた事がありました。
- 手数料で使われている通貨と売買での取引での通貨が異なる。手数料のみで使われている通貨は取得漏れしている場合がありますので、後から追加取得します。
- 日本円の場合、全通貨結合シートに無い為、VLOOKUP関数に引っ掛からない
- CoinmarketCapに履歴の掲載はない。しかし、パンケーキスワップ等で上場している通貨
以下の関数を設定していき、オートフィルターで確認をしていきます。
X列：買い側通貨記号。X2セルに「=D2」入力、下までセルコピー
Y列：買い側円換算額。Y2セルに「=IF(X2=”JPY”,E2,IF(X2=0,””,U2))」入力、下までセルコピー。※日本円だった場合は、そのままの金額を引っ張ります。通貨記号の記載が無い場合は空欄にします。
Z列：売り側通貨記号。Z2セルに「=F2」入力、下までセルコピー
AA列：買い側円換算額。Y2セルに「=IF(Z2=”JPY”,G2,IF(Z2=0,””,V2))」入力、下までセルコピー。※日本円だった場合は、そのままの金額を引っ張ります。通貨記号の記載が無い場合は空欄にします。
AB列：買い側通貨記号。X2セルに「=I2」入力、下までセルコピー
AC列：買い側円換算額。Y2セルに「=IF(AB2=”JPY”,J2,IF(AB2=0,””,W2))」入力、下までセルコピー。※日本円だった場合は、そのままの金額を引っ張ります。通貨記号の記載が無い場合は空欄にします。
ここからはオートフィルターを使って、エラー値を探していきます。
１行目のどこでも良いのでセルを選択し、上部データタブ→フィルターをクリック。
１行目にフィルターがかかりますので、X列の▼をクリック
「0」の箇所のチェックだけを外して、OK
X列のフィルターを掛けたままにして、Y列の▼をクリック。数値が沢山出てきますが、一番下「#N/A」が無いかをチェックします。あれば、「#N/A」だけにチェック入れて、「OK」をクリック。
「＃N/A」だけにするには、一番上に、「すべて選択」のチェックを外します。これで全てのチェックが外れます。その後、一番下へ行って、「＃N/A」だけにチェックを入れます。
フィルタリングを実行すると、「Y列」が「＃N/A」で「X列」に「通貨記号」が入っている箇所があれば、X列の「通貨記号」がエラーの原因になりますので、対処をしていきます。※CoinmarketCapで取得忘れているものは取得をします。CoinmarketCapで取得ができないものは個別対処します（他サイトから当時の時価を個別に調査して取得します）。
最初に、取引内容が、「売買」ではなく、「送付」や「預入」での場合は、円換算額を計算する必要はありませんので、除外ができます。課税対象ではないからです。しかし、送付や預入時に手数料がかかっている場合は、その手数料は計算対象となりますので、手数料の円換算ができていない場合は、取得が必要です。
また、ボーナス（ステーキングや流動性の追加による報酬の発生）については、金額は0円での取得となりますので、円換算額は必要有りません。履歴の記載は必要になります。ボーナスを他の通貨と交換（売買）した時に、課税の対象となってきますので、円換算額が必要となります。
この時、Z列とAA列で金額が取得出来ている場合は、そちらの金額を当てはめる事で対処が可能ですので、無視できます。
上記画像の例で言えば、「DAI」はCoinmarketCapで履歴が取得できません。しかし、売り側でJPYで円換算額（1200円）が取得出来ていますので、これを当てはめる事で金額が出せるので問題ありません。次に「POINT」については、海外取引所GATE内のみで使われる換金不能の通貨なので、計算不要（GATEでは売買時の手数料を、このPOINTで一部使用ができる）。「BHERO」はBombcryptでボムヒーローを購入した際の履歴でBCOIN側で金額（5223円）の取得ができていますので、こちらも問題はないです。
このチェックを「売り側：Z列とAA列」「手数料：AB列とAC列」でも行い、漏れがないかどうかをチェックします。手数料の場合だけは、他の列で当て込みが出来ないので、エラー値があった場合は個別対処が必須になります。
※送付時や預入時には送付した各通貨の円換算時価は必要ないです。売買時のみ円換算時価が必要になります。しかし、送付時やDefi（ステーキング）への預入時は、手数料がかかる為、手数料については円時価計算が必要になります。手数料は経費として減算ができますので、しっかり計算しておいた方が良いです。
このチェックをして全ての履歴が問題ない事を確認してから全計算を行っていきます。
５、各通貨毎の円換算額の合計値を算出
この先は、話が長くなってしまったので、別の記事にして、対応していきます。
この先の話も、結果が出た後で、かなりおかしな値を見つけてしまい（買った量と売った量がかなり違う）、エラー値を修正していかなければならず、かなり大変な作業となっています。
下記リンクよりご確認下さい。
コメント