メタマスクの中や別々の取引所に仮想通貨があって、今いくら持っているのかわからない！
簡単に一括で仮想通貨の評価額を管理する方法はない？
こんな悩みにお答えします。
こんにちは！ででんです。
今回は、エクセルを使って仮想通貨の損益を管理する方法を解説します。
作成する管理表は↓画像の通り。
こんな人にオススメ
- エクセルを使って仮想通貨の損益管理がしたい人
- webクエリで仮想通貨のリアルタイム価格を取得したい人
- 色々な取引所を利用している人
- 仮想通貨の損益を自動で計算したい人
リアルタイム価格をwebから取り込むことができるため、自分が保有している仮想通貨の評価損益額を自動計算することが可能！
これを使えば、いちいち各取引所のアプリを立ち上げて計算する必要がありません！
INDEX関数やMATCH関数を使用するため、ちょっと苦手意識が出てしまうかもしれませんが、この記事の手順を踏めば、誰でも作成することができます！
概要
今回作成するエクセルファイルは、エクセルの標準機能である「webクエリ」を利用したものです。
HTMLファイルをExelに取り込む機能を言います。
（中略）
Webクエリ を使用すると、HTMLファイルの表からデータを取り込めます。
「Web クエリ」とは？ - Lenovo Support JP
webクエリを利用し、仮想通貨のリアルタイム価格を表示しているwebサイトの情報をエクセルに反映させます。
リアルタイム価格のセルを損益計算の関数に入れ込むことで、データ更新に応じて計算結果も更新されるという仕組みです。
保有している仮想通貨の量と投資金額を手入力のは必要だけど、あとはデータ更新するだけで評価額を自動計算してくれます！
具体的な手順5ステップ
ここからは、具体的なファイルの作成手順をご紹介します。
手順は以下の通り。
順番に解説していきます。
表を用意
管理画面のメインとなる表を作成します。
下記画像のような表を作りました。
表の項目は以下の通り。
横（行）はご自身で保有している仮想通貨名を入れてください！
webクエリの設定
上部リボンから「データ」を選択→「webから」をクリックします。
↓画像のウィンドウが表示されるため、情報先のサイトURLを入力します。
サイトには「みんかぶ」の【暗号資産(仮想通貨)リアルタイムレート・時価総額情報】を使用します。
以下のURLをコピペし、【OK】をクリックしてください。
いくつかのテーブルが抽出されるので、リアルタイム価格が含まれているものを選択してダブルクリックします。
クエリ設定画面がポップアップするので、そのまま【閉じて読み込む】をクリックします。
先ほど選択したテーブルが新しいシートとして作成されます。
これで、webクエリの設定は完了です。
管理表へリアルタイム価格を表示させる
作成した表にリアルタイム価格を表示させるには、INDEX関数とMATCH関数を組み合わせる必要があります。
これらの関数についてはこちらの記事でわかりやすく説明されているため、ぜひチェックしてみてください。
ビットコインを例に行っていきます。
まず、現在価格のセルを選択し、「=INDEX(」と入力します。
webクエリで追加したシートをクリックし、「仮想通貨名」と「現在価格」をすべて範囲指定し、最後に「,（コンマ）」を入力します。
※↓画像のC2・D2から下のセルをすべて選択。
「MATCH（」と入力し、MATCH関数を呼び出します。
その後、管理表シートをクリックし、「ビットコイン(BTC)」のセルを選択してください。
※↓画像のB3セル
同様に「,」で区切ります。
管理表の仮想通貨表記をみんかぶの「通貨名」と同じに固定したのは、webクエリシートの仮想通貨表記と同じにするためです！
ここが同じじゃないと、MATCH関数がうまく動作しなくなります！
webクエリシートをクリックし、今度は「仮想通貨名」のセルのみすべて範囲選択します。
※↓画像C2から下のセルをすべて選択
その後、「,0)」と入力します。
最後に「,2)」と入力すると、一番最初に指定したセルにwebクエリから取得したリアルタイム価格が表示されます。
そのほかの通貨も同様の手順でリアルタイム価格の取得ができます。
リアルタイム価格を表示するだけなのに、どうしてこんなに複雑なの？？
それは、情報元のサイトが時価総額に応じたランキング形式だから…
単純にリアルタイム価格のセルを指定すると、ランキングの入れ替わりで計算がおかしくなってしまうんです。
情報元がただリアルタイム価格を表記するだけのサイトならもっと簡単なんだけど、
そのようなサイトでwebクエリで参照できるところは見つけられませんでした…
保有している通貨の日本円換算額を計算する
保有している仮想通貨を日本円に換算するために必要な計算式は以下の通り。
日本円換算額の計算式
保有量 × 現在価格（日本円）＝ 日本円換算額
こっちはとってもシンプル！
同じくビットコインを例にします。
管理表内の【日本円換算】のセルを選択し、「=現在価格セル*保有枚数」と入力。
↓画像の場合だと、「=C3*D3」となります。
「保有枚数」のセルにご自身で持っている仮想通貨の量を入力すると、その日本円換算額が自動で計算されます。
評価損益額の計算
評価損益額の算出に必要な計算式は以下の通り。
評価損益額の計算式
各仮想通貨の日本円換算額合計 - 投資金額 = 評価損益額
↓画像を例にすると、
- E6に各仮想通貨の日本円換算額合計を入力
- F6に投資金額の合計を入力
- G6に「=E6-F6」と入力
以上で管理表ファイルの作成は完了です！
お疲れさまでした！
リアルタイム価格の更新
仮想通貨のリアルタイム価格を更新するためには、クエリのデータ更新を行う必要があります。
上部リボン → データ →「データ更新」 と進むことで、リアルタイム価格の更新を行うことができるので、都度更新を行ってください。
注意点
今回の管理表を利用するにあたり、2点注意していただきたいことがあるためご紹介します。
手入力が必要な項目がある
残念ながら、管理表の「仮想通貨保有枚数」や「投資金額」のセルは、手動で修正する必要があります。
仮想通貨を買い増した場合は、該当セルの数値を手直ししてください。
APIを使いこなせたら、この辺も自動化できるのかな？
ぼくにはそこまでの技術はありませんでした…
手数料等は別途計算が必要
今回作成した管理表は、手数料等の計算は省いています。
したがって、手数料やスプレットも加味した評価額が必要な場合は、別途で計算式を立てる必要があります。
この管理表は、ざっくりとした評価額を知りたい方のためのものだと思っていただければ幸いです。
ぼくは手数料の計算は面倒だったので、省略しちゃいました。
まとめ
今回は、エクセルを使って仮想通貨の損益を自動計算できる管理表の作成方法をご紹介しました。
仮想通貨管理表まとめ
- 複数の取引所やメタマスクの通貨まで管理できる
- webクエリ機能でリアルタイム価格で損益を計算可能
- INDEX関数とMATCH関数でwebクエリからリアルタイム価格を取得
- 仮想通貨保有量や投資金額は手入力が必要
- 手数料やスプレットは別途計算が必要
この表を使えば、複数の取引所で保有している仮想通貨や、メタマスク内の仮想通貨も合わせて評価額を計算することができます。
保有資産計算のお役に立てれば幸いです。
それでは！