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