【小技】仮想通貨取引した日のドル円レートをエクセルとスプレッドシートで一括入手する方法

Pocket
LINEで送る

仮想通貨取引の集計を手動で行う際に一括してドル円レートを入手したいと思いませんか?

この記事では、エクセル2021とGoogleのスプレッドシートでドル円レートを一括入手する方法をまとめました。

加えて、BscScanなどのエクスプローラーを使って取引記録から日付を入手して、その日のドル円レートを表示する方法を解説しました。

エクセル2021だと直接ドル円レートをWebにとりに行くことができないので、スプレッドシートを使っています。

エックセルバのージョンによってはWebからドル円レートを取得できるので、その場合はこの記事は参考にならないかもです。

なお、BSCScanなどのエクスプローラーの日付は表示形式がバラバラなので、1つの表示形式にする数式をエクセル関数で作ったので参考になればと思います。

仮想通貨取引における収益計算をする際、円ベースの計算に役立てばと思います。

この記事でわかること

  • Googleのスプレッドシートでドル円レートを入手する関数
  • エクスプローラーの取引履歴から日付を統一形式で出力する関数
  • スプレッドシートから得たデータを元に特定の日のドル円レートを見つける関数

まず、スプレッドシートでドル円レートを取得する方法です。

数式はGOOGLEFINANCE関数を使用します。

上の数式をスプレッドシートのB1に記入し、数式を入れると以下のように自動で表示されます。

A2はレートを取得開始日する日を入力しています。

仮想通貨投資の1年の集計を想定しているので、360日分を取得する数式を立てておきたいところですが、数式作成日より未来の日になるとエラーが出てうまく動かないです。

なので、開始日を1月1日として、とりあえず120日分(約4ヶ月分)のドル円レートを取得する場合の数式になります。

次に、エクセル2021で特定の日のドル円レートを取得する数式を紹介します。

まず、先ほどスプレッドシートで表示した日付(B列)とドル円レート(C列)の列をエクセルにコピペします。

続いて、D列にROUNDDOWN関数とVALUE関数を使ってDate(B列)の時間を削除する数式を入力します。

D列の数式

ドル円のデータベース(検索範囲)としてD,E列を作成します。

(E列はC列をコピーしています)

次に、取引したチェーンのエクスプローラーで表示される日付をエクセル関数で検索しやすい形式(例:2022/4/22)に変換を行います。

各チェーンのエクスプローラーと日時の表示はこのようにバラバラになっているのでそれぞれの形式に合った変換式を作成しました。

BNB Chain(BscScan)、Avalanche(SNOWTRACE)、Polygon(Polygonsacan)、Fantom(FTMScan)、Ethereum(Etherscan)

例:Jan-06-2022 04:00:00 PM +UTC

Harmony(Harmony Block Explorer)

例:2022-04-02T18:00:08.000Z

DFK(DFK Chain Explorer)

例:Apr 30, 2022, 11:00:59 PM GMT+9

Terra(Terra Finder)

例:Apr 17, 2022, 10:00:08 PM (JST)

OSMOSIS(MINTSCAN)

例:2022/4/22 22:00:00

各チェーンの日付の表示ごとに日時を抜き取る数式がこちら(I列)。

(OSMOSISは変換しないです)

VALUE関数を利用しています。

次に、ドル円レートを検索する際、日付だけが必要なので、抜き出した日付から時間を切り捨てる数式がこちら(J列)。

ROUNDDOWN関数を使用しています。

抜き出した日付をもとに、先に作成したドル円のデータベースから目的の日のドル円を取得します。

数式はこちらになります(K列)。

VLOOKUP関数を使用しています。

これで取引した日のドル円レートを入手することができました。

チェーンごとに数式は異なるので、使用しているチェーンの数式を参考にしてください。

ドル円レートをスプレッドシートで取り込んでエクセルに貼り付けるという手間はかかりますが、これで円ベースの収益計算が可能になるはずです。

トランザクションの解析ツールはKoinlyなどいろいろ存在しますが、完璧なものがないのが現状ですね。

最終的には手作業に頼らざる得ない状況なので、仮想通貨の運用に少しでも役立てればと思います。

ちなみに、最近注目視しているツールとしてはCryptoVisonです。

まだベータ版ですが多くのチェーンに対応しています。

日本人の方が構築されているようで、期待しています。

関連記事

オススメ記事

Pocket
LINEで送る

フォローする