こんにちは。KATSUです。
当ブログでは、主にExcelやAccessの基本的な使い方をまとめてきました。
基本テクだけでなく実務を想定した使い方についても、これからは少しずつまとめていく予定です。
今回は、日別の暗号資産単価をExcel関数で取得する方法を紹介します。
もくじ
今年はSTEPNやNFTなどで盛り上がった暗号資産業界ですが、年が明けてから始めないといけないことは確定申告のための税金計算です。
国内取引所の閉じられた世界であれば、全て円換算で表記されているので難しくはないです。
一方、海外取引所やメタマスクなどを使っていれば、日別の単価を使う場面も多くなります。
ぼく自身も、11月に入ってから半月ほど国税庁のフォームなどを使い試行錯誤してきましたが、ようやくゴールが見えてきたのでとここでまとめてみました。
今回紹介するのは、Excelの組み合わせ関数を使い、対象の日付の単価を引っぱる方法です。
完全自動化ではないものの、いちいち終値を手入力するよりはケタ違いに効率を上げられるでしょう。
手順1-CoinMarketCapの履歴データを丸ごとコピペする
暗号通貨のチャートを確認できるサイトで、一番メジャーなものはCoinMarketCapです。
納税の申告対象期間は1月1日から12月31日までですが、CoinMarketCapでも1年間の円換算データを取得することができます。
まずはCoinMarketCapのトップページに入りましょう。
≫ CoinMarketCap
税金計算で必要なのはJPY(日本円)です。
もし、表示されている金額がアメリカドルなどであれば、画面の右上で法定通貨の設定を変えておきましょう。
チャートを確認したい暗号通貨を選びます。
今回はNFTなどでもよく使うEtherium(イーサリアム)を選んでみました。
クリックするとEthのチャートが表示されました。
履歴データをクリックします。
表示がグラフから表に切り替わります。
1年間のデータを取得するため、下にスクロールします。
一番下まできたら「更に読み込む」をクリックします。
過去にさかのぼったデータが追加されました。
1年分が表示されるまで繰り返しましょう。
1月1日のデータが表示されました。
1月1日の時価総額をマウスでカーソルします。
選ばれた部分は文字と背景の色が変わっているはずです。
Shiftキーを押しながら表の左上をクリックします。
指定した日付から上の範囲が全て選ばれます。
マウスの右クリックで「コピー」をクリックします。
Excelを開き、右クリックで「貼付け」ボタンを選択します。
データが貼付けられました。
貼り付けられた表に「Cht_ETH」という名前をつけます。
計算表で使うのは1列目(A列)の日付、5列目(E列)の終値の２つです。
シート名も「Cht_ETH」にしておきましょう。
ETHと同じようにSOLのシートも作りました。
手順2-計算用の表を作る
別のシートに計算表を作ります。
レイアウトは、国税庁の暗号資産計算フォーム(移動平均法)を参考にします。
≫ 暗号資産の計算書（移動平均法用） - 国税庁
一般公開されている国税庁のフォームは、こんな感じのフォームです。
関数が使いやすいようアレンジを加えて表を自作します。
今回のテーマに挙げている「CoinMarketCapの終値取得」は、A列の日付とL列からO列までを使います。
B列からJ列はトレードの記帳に使いますのが、後で少しだけ触れていきます。
手順3-入力規則を使って通貨種を選べるようにする
複数の通貨を扱っている人は、ミスなく通貨種を選べるよう入力規則を設定しましょう。
L3セルを選んだあと、データタブ→入力規則の順にクリックします。
「入力値の種類」はリストを選び、「元の値」に通貨種をコンマ区切りで入力します。
入力規則の設定を行うと、ドロップダウンリストで通貨を選べるようになります。
Excelのコピペ機能では、入力規則だけを貼り付けることもできます。
このようなひと手間をかけることで、通貨種の入力ミスを防ぐことができます。
手順4-単価を取得する組み合わせ関数を作る
では、単価の列(M列とO列)に組み合わせ関数を入れていきましょう。
ここでは、3つの関数を組み合わせて数式を作ります。
- VLOOKUP関数：日々の単価を取得する
- INDIRECT関数：使いたい通貨を選ぶ(範囲の指定)
- IFERROR関数：未入力セルにエラー表示をさせない
まずは、VLOOKUPとINDIRECTだけをこのように組み合わせてみます。
=VLOOKUP($A3,INDIRECT("Cht_"&L3),5,0)
VLOOKUPの第二引数では、INDIRECTを組み合わせて指定する範囲を決めるということですね。
ETHのチャートは「Cht_ETH」という名前を付けていますので、文字列「Cht_」とL3セルの「ETH」を結合させて範囲を指定できます。
ただし、この状態では未入力でエラー表示されてしまいます。
では、IFERROR関数を使ってエラー発生時は空白になるよう、数式に手を加えていきます。
=IFERROR(VLOOKUP($A3,INDIRECT("Cht_"&L3),5,0),"")
数式を上のように修正すると、エラーが消えました。
これで数式が完成しました。
日付を指定するA列は絶対参照を使っているので、M列の数式をO列に貼り付けても正しく計算されます。
手順5-データを入れて動作を確認
では、実際に日付を通貨種を入れて動作確認してみましょう。
1月7日のETH単価を取得してみます。
A列に「1月7日」L列に「ETH」を入力すると、単価に「368,926」が反映されました。
シート名「Cht_ETH」のデータを確認してみましょう。
1月7日の終値を確認すると「￥368,926.13」となっているので、正しく取得できているようです。
次はETHをSOLに変えてみましょう。
単価は「15,759」に変わりました。
シート名「Cht_SOL」のデータを確認してみましょう。
1月7日の終値を確認すると「￥15.759.24」となっているので、SOLも正しく取得できています。
このようにして、暗号資産の計算表をつくるのに必要な単価取得は、Excelの関数で簡単に取得できます。
実際に、「Binanceで7.1SOLから0.3ETHを買った」という動きを計算表に入れると下のように記帳できます。
上のような記帳を一つ一つ丁寧に積み上げていけば、年間の計算表が完成します。
補足-Googleスプレッドシートを使う方法
日頃から暗号資産を触っている人は、ExcelよりもGoogleスプレッドシートを普段使いする人も多いのではないでしょうか。
Googleスプレッドシートを使うときの注意点についてもここで触れておきます。
Excelと同じ手順で単価を取得しようとしても、実はうまく反映されません。
CoinMarketCapの履歴データから貼り付けられた日付の列(A列)のデータ形式が原因です。
Excelの場合、日付列は自動的に日付型として貼り付けられます。
一方、Googleスプレッドシートは文字列として貼り付けられてしまいます。
表示形式で調整しようとしても、修正できません。
オートフィル機能で日付データを作り、差し替えるのがおすすめです。
まずは最新日から2日分だけを順番に入力します。
2つのセルを選択し、マウスの左ボタンを押したまま下に引っ張ります。
マウスのボタンからから手を離すと、日付が自動で追加されました。
この方法で1年間の日付を作り、履歴データの日付列を差し替えると、正しく単価が反映されます。
ひと手間かかる手順ですが、毎日行う作業ではありません。
年に数回という頻度からも、自動化しなくとも手動でデータを作れば十分ではないかと思います。
まとめ
今回は、Excelの組み合わせ関数を使ってCoinMerketCapの単価を取得する方法を紹介しました。
暗号資産取引の収支計算表を完成させるためには、一つ一つの記帳をていねいに積み重ねる必要があります。
加えて各通貨の単価を日別で引っ張るという非常に手間のかかる作業も待っています。
十分な収益が出ていれば税理士事務所に計算を依頼する方法もアリですが、収益が出る見込みが無い人はきっと「何とかして自分だけで完成させたい」と考えるでしょう。
PCを持っていればExcelやGoogleスプレッドシートを使った収支計算は可能です。
組み合わせ関数を使えば、数式のコピーだけで日別の単価を引っ張ることができるので、完全手作業に比べおそらく半分程度の手間で済みます。
また、暗号資産取引おことわりの税理士事務所の数も多い、という話もよく聞きます。
税理士の先生にお願いできるくらい十分な収益が出ている人でも、前準備として単価まで出しておくのは必要になるでしょう。
いずれにせよ、暗号資産の収支計算は必ず済ませておかないといけませので、無事に乗り越えていきましょう。
今後は、EtherScanなどのようなブロックチェーンデータを読み取る方法についても年末までには解説しますので待っていてくださいね。