Good Investor 良き投資による自由な生活の実現を楽しみながら目指すブログ

なんとか準富裕層へ到達。長期目線では高配当米国株式を中心に積立中、中短期ではシストレの売買で資産を積み上げ、長期投資の原資を増やす作戦。仮想通貨もウォッチ中。経済的自由を達成する道半ばを楽しみながら投資に取り組んでいます。ブログ記事は興味があるもの多数につき多岐にわたります。

エクセルでの投資管理にPowerQueryを利用することでデータ取得が凄く捗る

f:id:hydech:20190804115921j:plain
エクセルは便利だぞ!

はじめに

皆さん、こんにちは。

今日は、Excel(エクセル)で新しく利用してみた機能が非常に良かったのでご紹介です。

私は投資対象の管理や、計算にエクセルを利用しています。エクセルは最強のBIツールですからね!非常に便利です。

そんな中で以下の機能があることを最近知りました。(というか昨日知った)

f:id:hydech:20190804104743p:plain
PowerQuery利用の流れ

Power Query は、データ ソースの検索、接続、結合、絞り込みを行って分析ニーズを満たすことのできるデータ接続テクノロジーです。 power Query の機能は、Excel と power BI デスクトップで利用できます。

support.office.com

要は、

様々なデータソース(データの習得先)からデータを持ってきて、必要に応じた変換、加工をしてエクセル上に表示できる

そんな機能ですね。

皆さんも普段、エクセルでのデータの取得にVBAを利用したり、コピペでエクセルに張り付けてなどの作業をしていませんか? 私も今までVBAでやっていたんですけど、プログラミングをするのが面倒くさい時があるんですよね。その点、この「PowerQuery」は利用方法を覚えれば対話的に設定ができるので簡単でした。最新のデータがある場合は、ボタン一つで再取得して反映させることも可能です。

というわけで、株価データ等、Web上から最新の価格データを取得して投資判断なりに利用できるように組み込んだのでご紹介です。

Excel(エクセル)をどんな感じで利用しているか。

一応、先に私がどのようにエクセルを投資に利用しているかを載せておきます。

以下はサンプルです。最新の価格情報と一定期間の過去データを取得することで幾つかの指標を計算しているものです。(画像の左側の部分ですね。価格データとインジケータ)

右側には自己資金の内、どの程度のポジションを持つことをマイルールで許可しているかを表示しています。

f:id:hydech:20190804105521p:plain
投資対象のサマリを表示

こんな感じで、最新の価格データを取得してエクセル上で計算、自分が欲しているデータを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以前の方はアドオンをインストールする必要があります。

Excel で Power Query を使い始めるには - Qiita

Step3. データの取得

では、ここからはエクセルで実際に作業を始めていきます。

f:id:hydech:20190804112122p:plain
「データ」タブから「Webから」を選択する

エクセルの「データ」タブを選択してください。そこから取得元を選ぶことができます。今回は、「Webから」を選択していただきます。

f:id:hydech:20190804112215p:plain
取得先のURLを入力

上記画面に取得先のURL情報を入力します。今回であれば、「https://api.cryptowat.ch/markets/bitflyer/btcjpy/ohlc?periods=86400」となります。

f:id:hydech:20190804112533p:plain
データ取得結果

その後上記のような画面になります。よくわかりませんね。実際のAPIの出力を見て頂ければわかりますが、"result"内部に価格のデータが入っているデータ構造です。つまり、"result"内部のデータをうまいこと抜き出す必要があります。これからこの部分をやっていきます。

"result"内部のデータを抜き出すということでドリルダウンのように進んでいきます。"result"の"Record"を選択してください。以下のように1階層潜ることができます。

f:id:hydech:20190804112901p:plain
"86400"内部にデータがList構造で含まれているのが分かる

上記のように"86400"という項目が出てきます。この中にいわゆる日足データが保存されている形です。さらに潜ってみましょう。"86400"の”List"を選択してください。

f:id:hydech:20190804113041p:plain
Listというデータ構造で500個のデータがあるのがわかる

すると上記のようにずらっとデータが並んでいます。500個のデータが縦方向に並んでいますが、500日分のデータがあるということになります。その1つ1つに価格データがList構造で含まれているということになります。ここからは、List構造のデータを展開していきます。

エクセル上の「リスト」という部分を右クリックしてください。ここから「テーブルへ変換」を選択します。変換時に区切り記号などを聞かれると思いますが、デフォルトのままで構いません。

f:id:hydech:20190804113515p:plain
テーブル返還後

上記画像の状態になりましたでしょうか。いよいよ"List"に格納されたデータを取り出します。"Column1"という部分の右隣に矢印ボタンがあるかと思います。ここをクリックして、「値を抽出する」を選択します。

f:id:hydech:20190804113756p:plain
","を区切り記号に指定する

"List"内部は価格データが","区切りで保存されているので、ここでは上記画像のように指定します。

f:id:hydech:20190804113855p:plain
"List"内部のデータの抽出が完了

やっと、”List”内部のデータを抽出できました。あとちょっとです。せっかくエクセルで扱うのでそれぞれの値を単一の項目に分解しましょう。"Column1"の部分で右クリックをして、「列を分割」、「区切り記号による列の分割」を選択します。同様に","で列の分割を行います。

f:id:hydech:20190804114107p:plain
列の分割後

はい、来ましたね。もう我々が望んでいたデータ構造です。せっかくなので列名もつけましょう。列名である"Column1.X"を選択して「名前の変更」から簡単にできます。

f:id:hydech:20190804114358p:plain
列名修正後

これで完成ですね。分かりやすくなりました。※7列目は不要なので削除しています。

f:id:hydech:20190804114452p:plain
「ホーム」>「閉じて読み込む」を選択して完了する

以上で準備完了です。設定を保存してデータを読み込んでみましょう。(「ホーム」>「閉じて読み込む」を選択して設定は完了です。)

f:id:hydech:20190804114622p:plain
データをエクセルに取得完了

キタ━━━━(゚∀゚)━━━━!!

完璧ですね。TimeStamp等がcryptowatchの仕様上UNIXタイムになっていますのでこの部分の修正等は必要ですが、基礎となるデータは取り込みが完了しました。

UNIXタイムの時間変換は以下で可能です。"UNIXTIME"にUnixTimeの値が格納されているセルを指定する感じで。
("UNIXTIME" + 32400) / 86400 + 25569

また、画像右の「クエリと接続」の部分を右クリックして「最新の情報に更新」を選択すればデーががリフレッシュされます。

まとめ

ということでエクセルのPowerQueryに関してでした。

慣れるまではちょっと大変かもしれませんが、一度慣れるとあとは簡単です。そして、1度作れば弄る必要がありません。

以前はVBAで書いてましたが、今後はPowerQueryを利用しようかなと思っています。