はじめに
皆さん、こんにちは。
今日は、Excel（エクセル）で新しく利用してみた機能が非常に良かったのでご紹介です。
私は投資対象の管理や、計算にエクセルを利用しています。エクセルは最強のBIツールですからね！非常に便利です。
そんな中で以下の機能があることを最近知りました。（というか昨日知った）
PowerQuery利用の流れ Power Query は、データ ソースの検索、接続、結合、絞り込みを行って分析ニーズを満たすことのできるデータ接続テクノロジーです。 power Query の機能は、Excel と power BI デスクトップで利用できます。
要は、
様々なデータソース（データの習得先）からデータを持ってきて、必要に応じた変換、加工をしてエクセル上に表示できる
そんな機能ですね。
皆さんも普段、エクセルでのデータの取得にVBAを利用したり、コピペでエクセルに張り付けてなどの作業をしていませんか？ 私も今までVBAでやっていたんですけど、プログラミングをするのが面倒くさい時があるんですよね。その点、この「PowerQuery」は利用方法を覚えれば対話的に設定ができるので簡単でした。最新のデータがある場合は、ボタン一つで再取得して反映させることも可能です。
というわけで、株価データ等、Web上から最新の価格データを取得して投資判断なりに利用できるように組み込んだのでご紹介です。
Excel（エクセル）をどんな感じで利用しているか。
一応、先に私がどのようにエクセルを投資に利用しているかを載せておきます。
以下はサンプルです。最新の価格情報と一定期間の過去データを取得することで幾つかの指標を計算しているものです。（画像の左側の部分ですね。価格データとインジケータ）
右側には自己資金の内、どの程度のポジションを持つことをマイルールで許可しているかを表示しています。
こんな感じで、最新の価格データを取得してエクセル上で計算、自分が欲しているデータを1発で出力するようなエクセルファイルを作ることができるわけです。色々な商品に投資している方にとっては便利そうでしょ？
Excel（エクセル）のPowerQueryの使い方（cryptowatch）編
ということで、ここからは実際に使い方を書いていきます。自分のためにもね。記録を残さないと忘れるのだ。。
今回は、対象のデータとしてビットコインを扱おうかと思います。取引所としてはBitFlyerです。データ自体は、cryptowatchのAPIを利用したく思います。
Step1. 取得対象のデータを準備
まずは、取得先のデータの準備です。基本的には毎回最新の価格データを取得していきたいのでWebのAPIを利用することになります。今回は、cryptowatchということで取得先のURLは以下です。
https://api.cryptowat.ch/markets/bitflyer/btcjpy/ohlc?periods=86400
上記URLをブラウザで開くと、JSON形式の価格データがテキストで表示されると思います。これをエクセルに整形して取り込みます。ちなみに、「periods=86400」は日足データの取得を意味しています。
cryptowatchのデータ形式に関しては本記事の主題ではないため、参考になるサイトを記載しておきますね。
cryptowatch APIから受信するJSON形式のデータについて理解を深めよう! | Liibercraft｜リーベルクラフト
CryptowatchのAPIで取得したbitcoin価格のJSONデータから欲しい数字を取り出す | 文系でもわかる！BitcoinのBOT自動売買トレードの始め方
Step2. エクセルのバージョン確認
PowerQueryは新しく機能なのでご利用のエクセル利用するにあたって準備が必要です。
エクセルの2016以降を利用の方はデフォルトで機能を入っています。2013以前の方はアドオンをインストールする必要があります。
Step3. データの取得
では、ここからはエクセルで実際に作業を始めていきます。
エクセルの「データ」タブを選択してください。そこから取得元を選ぶことができます。今回は、「Webから」を選択していただきます。
上記画面に取得先のURL情報を入力します。今回であれば、「https://api.cryptowat.ch/markets/bitflyer/btcjpy/ohlc?periods=86400」となります。
その後上記のような画面になります。よくわかりませんね。実際のAPIの出力を見て頂ければわかりますが、"result"内部に価格のデータが入っているデータ構造です。つまり、"result"内部のデータをうまいこと抜き出す必要があります。これからこの部分をやっていきます。
"result"内部のデータを抜き出すということでドリルダウンのように進んでいきます。"result"の"Record"を選択してください。以下のように１階層潜ることができます。
上記のように"86400"という項目が出てきます。この中にいわゆる日足データが保存されている形です。さらに潜ってみましょう。"86400"の”List"を選択してください。
すると上記のようにずらっとデータが並んでいます。500個のデータが縦方向に並んでいますが、500日分のデータがあるということになります。その1つ1つに価格データがList構造で含まれているということになります。ここからは、List構造のデータを展開していきます。
エクセル上の「リスト」という部分を右クリックしてください。ここから「テーブルへ変換」を選択します。変換時に区切り記号などを聞かれると思いますが、デフォルトのままで構いません。
上記画像の状態になりましたでしょうか。いよいよ"List"に格納されたデータを取り出します。"Column1"という部分の右隣に矢印ボタンがあるかと思います。ここをクリックして、「値を抽出する」を選択します。
"List"内部は価格データが","区切りで保存されているので、ここでは上記画像のように指定します。
やっと、”List”内部のデータを抽出できました。あとちょっとです。せっかくエクセルで扱うのでそれぞれの値を単一の項目に分解しましょう。"Column1"の部分で右クリックをして、「列を分割」、「区切り記号による列の分割」を選択します。同様に","で列の分割を行います。
はい、来ましたね。もう我々が望んでいたデータ構造です。せっかくなので列名もつけましょう。列名である"Column1.X"を選択して「名前の変更」から簡単にできます。
これで完成ですね。分かりやすくなりました。※7列目は不要なので削除しています。
以上で準備完了です。設定を保存してデータを読み込んでみましょう。（「ホーム」>「閉じて読み込む」を選択して設定は完了です。）
ｷﾀ━━━━(ﾟ∀ﾟ)━━━━!!
完璧ですね。TimeStamp等がcryptowatchの仕様上UNIXタイムになっていますのでこの部分の修正等は必要ですが、基礎となるデータは取り込みが完了しました。
UNIXタイムの時間変換は以下で可能です。"UNIXTIME"にUnixTimeの値が格納されているセルを指定する感じで。
("UNIXTIME" + 32400) / 86400 + 25569
また、画像右の「クエリと接続」の部分を右クリックして「最新の情報に更新」を選択すればデーががリフレッシュされます。
まとめ
ということでエクセルのPowerQueryに関してでした。
慣れるまではちょっと大変かもしれませんが、一度慣れるとあとは簡単です。そして、１度作れば弄る必要がありません。
以前はVBAで書いてましたが、今後はPowerQueryを利用しようかなと思っています。
