関数(Excel)

Excelの関数を組み合わせてCoinMaretCapの単価を取得する

スポンサードリンク

こんにちは。KATSUです。

当ブログでは、主にExcelやAccessの基本的な使い方をまとめてきました。
基本テクだけでなく実務を想定した使い方についても、これからは少しずつまとめていく予定です。

今回は、日別の暗号資産単価をExcel関数で取得する方法を紹介します。

今年はSTEPNやNFTなどで盛り上がった暗号資産業界ですが、年が明けてから始めないといけないことは確定申告のための税金計算です。

国内取引所の閉じられた世界であれば、全て円換算で表記されているので難しくはないです。
一方、海外取引所やメタマスクなどを使っていれば、日別の単価を使う場面も多くなります。

ぼく自身も、11月に入ってから半月ほど国税庁のフォームなどを使い試行錯誤してきましたが、ようやくゴールが見えてきたのでとここでまとめてみました。

今回紹介するのは、Excelの組み合わせ関数を使い、対象の日付の単価を引っぱる方法です。
完全自動化ではないものの、いちいち終値を手入力するよりはケタ違いに効率を上げられるでしょう。

スポンサードリンク

手順1-CoinMarketCapの履歴データを丸ごとコピペする

暗号通貨のチャートを確認できるサイトで、一番メジャーなものはCoinMarketCapです。
納税の申告対象期間は1月1日から12月31日までですが、CoinMarketCapでも1年間の円換算データを取得することができます。

まずはCoinMarketCapのトップページに入りましょう。
 ≫ CoinMarketCap

Coin MarketCapのトップページ

税金計算で必要なのはJPY(日本円)です。
もし、表示されている金額がアメリカドルなどであれば、画面の右上で法定通貨の設定を変えておきましょう。

日本円に設定

チャートを確認したい暗号通貨を選びます。
今回はNFTなどでもよく使うEtherium(イーサリアム)を選んでみました。

Ethのデータを取得

クリックするとEthのチャートが表示されました。

Ethのチャート画面

履歴データをクリックします。

履歴データをクリック

表示がグラフから表に切り替わります。

グラフから表に切り替わる

1年間のデータを取得するため、下にスクロールします。

下にスクロール

一番下まできたら「更に読み込む」をクリックします。

更に読み込む

過去にさかのぼったデータが追加されました。
1年分が表示されるまで繰り返しましょう。

過去のデータが表示される

1月1日のデータが表示されました。

1年前まで表示させる

1月1日の時価総額をマウスでカーソルします。
選ばれた部分は文字と背景の色が変わっているはずです。

1月1日分をマウスカーソル

Shiftキーを押しながら表の左上をクリックします。

Shiftキーでまとめて選択

指定した日付から上の範囲が全て選ばれます。

1年間のデータが選択された

マウスの右クリックで「コピー」をクリックします。

右クリックでコピー

Excelを開き、右クリックで「貼付け」ボタンを選択します。

Excelに貼り付け

データが貼付けられました。

貼り付けられたデータ

貼り付けられた表に「Cht_ETH」という名前をつけます。

範囲に名前をつける

計算表で使うのは1列目(A列)の日付、5列目(E列)の終値の2つです。

計算で使う列

シート名も「Cht_ETH」にしておきましょう。

シート名の変更

ETHと同じようにSOLのシートも作りました。

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」を結合させて範囲を指定できます。

ただし、この状態では未入力でエラー表示されてしまいます。

VLOOKUPだけではエラー発生

では、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」に変わりました。

SOLに変更

シート名「Cht_SOL」のデータを確認してみましょう。
1月7日の終値を確認すると「¥15.759.24」となっているので、SOLも正しく取得できています。

反映された数値と同じ

このようにして、暗号資産の計算表をつくるのに必要な単価取得は、Excelの関数で簡単に取得できます。

実際に、「Binanceで7.1SOLから0.3ETHを買った」という動きを計算表に入れると下のように記帳できます。

計算の一例

上のような記帳を一つ一つ丁寧に積み上げていけば、年間の計算表が完成します。

補足-Googleスプレッドシートを使う方法

日頃から暗号資産を触っている人は、ExcelよりもGoogleスプレッドシートを普段使いする人も多いのではないでしょうか。
Googleスプレッドシートを使うときの注意点についてもここで触れておきます。

Excelと同じ手順で単価を取得しようとしても、実はうまく反映されません。

Googleスプシの問題点

CoinMarketCapの履歴データから貼り付けられた日付の列(A列)のデータ形式が原因です。

Excelの場合、日付列は自動的に日付型として貼り付けられます

Excelは日付型に自動

一方、Googleスプレッドシートは文字列として貼り付けられてしまいます
表示形式で調整しようとしても、修正できません。

Googleスプシは文字列型

オートフィル機能で日付データを作り、差し替えるのがおすすめです。

まずは最新日から2日分だけを順番に入力します。

最新日から2日分を入力

2つのセルを選択し、マウスの左ボタンを押したまま下に引っ張ります

マウスで下に引っ張る

マウスのボタンからから手を離すと、日付が自動で追加されました。

日付が降順に追加

この方法で1年間の日付を作り、履歴データの日付列を差し替えると、正しく単価が反映されます。

単価を反映される

ひと手間かかる手順ですが、毎日行う作業ではありません。
年に数回という頻度からも、自動化しなくとも手動でデータを作れば十分ではないかと思います。

まとめ

今回は、Excelの組み合わせ関数を使ってCoinMerketCapの単価を取得する方法を紹介しました。

暗号資産取引の収支計算表を完成させるためには、一つ一つの記帳をていねいに積み重ねる必要があります。
加えて各通貨の単価を日別で引っ張るという非常に手間のかかる作業も待っています。

十分な収益が出ていれば税理士事務所に計算を依頼する方法もアリですが、収益が出る見込みが無い人はきっと「何とかして自分だけで完成させたい」と考えるでしょう。

PCを持っていればExcelやGoogleスプレッドシートを使った収支計算は可能です。
組み合わせ関数を使えば、数式のコピーだけで日別の単価を引っ張ることができるので、完全手作業に比べおそらく半分程度の手間で済みます。

また、暗号資産取引おことわりの税理士事務所の数も多い、という話もよく聞きます。
税理士の先生にお願いできるくらい十分な収益が出ている人でも、前準備として単価まで出しておくのは必要になるでしょう。

いずれにせよ、暗号資産の収支計算は必ず済ませておかないといけませので、無事に乗り越えていきましょう。

今後は、EtherScanなどのようなブロックチェーンデータを読み取る方法についても年末までには解説しますので待っていてくださいね。

スポンサードリンク

スポンサードリンク

-関数(Excel)