過去記事になりますが、EXCELのWebクエリの機能を用いて直近の為替レートを取得する記事（エクセルでもできた ～為替レートをシートに自動取得する方法（サンプルシート有））を書きました。その際にはExcel2013を使っていたのですが、最近、Excel2016でWebクエリを使ってみたところPower Query（取得と変換）という進化した仕組みになっていました。今回は、Excel2016版の為替レートをシートに自動取得する方法です。
試してみてExcel2016ではPower Queryとなってすごく使いやすくなったと思います。
※Excel2013の方は過去記事の方を参照してみてください。
※2019/12/17追記
記事を読まれた方から数日前からレート取得できないとのコメントを頂いたので確認したところ、当初記事で為替レートの提供元としていたInvesting.comからExcel2016用サンプルでは読み込めない、一方Excel2013用のサンプルでは取得可能な状況でした。そこで、こちらのExcel2016のPowerQuery用に、新たなレート提供元として「みんなのＦＸ」さんのブログパーツを使う形に修正しました。
Contents
サンプルファイル、Excelバージョンについて
この記事の手順で作成したExcelファイルをサンプルとして、こちらに置いています。ファイルを参考にされたい方は、ダウンロードしてみてください。
EXCELのバージョンは2016、作成時点では動作確認していますが、利用は自己責任でお願いします。
エクセルでWeb上のデータを取得する
元データとなるWebサイトを探す
エクセルで為替レートを取得する場合に以下２点に合うWebサイトを探す必要があります。
- 必要な通貨の為替レートが提供されている
FX投資の検討・管理の元データですので、必要な通貨に対応していることが条件 - エクセルのWebクエリ機能で取得できる
これまで上記を満たすサイトをいろいろ探してきているのですが、はるきちの以前のお薦めは、Investing.comの主要通貨ペアの最新為替レートのページでした。
非常に多くのレートが提供されていて、すごく助かっていたのですが冒頭に書いたようにExcelからは取得できなくなっている旨のコメントを頂きました。そこで、以前から試してみようと思っていた「みんなのＦＸ」さんのブログパーツを試したところ良い感じだったので、それを使う形に記事を修正しました。
みんなのＦＸさんのブログパーツのページはこちらです。また、下の表がブログパーツを引用した結果です。みんなのＦＸさんで取引可能な通貨ペアを全部盛り込んだので、取得できるレートはこれが最大と思います。
エクセルでPower QueryのWebから機能を使う
対象のWebサイトが決まれば、エクセルに取り込みます。新規ブックで「データ」タブ⇒「新しいクエリ」⇒「その他のデータソースから」⇒「Webから」を選択します。
データタブの中に「取得と変換」の項目があり、その中に「新しいクエリ」とありますが、その「取得と変換」がPower Queryのことになります。
次に表示された「Webから」のURLのところに、使うWebサイトのURLをコピぺして、OKを押します。
URLですがブログパーツを直接叩くために、こちらのページのURLをコピーして入れてください。
https://min-fx.jp/outside/rate_mini.php?pair=USDJPY,EURJPY,GBPJPY,AUDJPY,NZDJPY,TRYJPY,MXNJPY,ZARJPY,CNHJPY,CHFJPY,CADJPY,HKDJPY,SGDJPY,PLNJPY,NOKJPY,SEKJPY,EURUSD,GBPUSD,AUDUSD,NZDUSD,EURGBP,EURAUD,GBPAUD,AUDNZD,USDCHF,EURCHF,GBPCHF
を入れてください。
次の「ナビゲーター」では、Webサイトのコンテンツが分析されて項目として表示されています。「Table0」を選択した状況が下図です。
テーブルビューでうまく行かない際には、Webビューから選択することもできます。
ここでは、「Table0」を選択して「読み込み」をクリックします。「読み込み」のプルダウンを変更して「読み込み先」とすると新規ワークシートか既存ワークシートの選択が可能ですが、ここではそのまま読み込みます。
新しいワークシートが開き、選択した表がワークシートに読み込まれます。
データ利用と参照の仕方の工夫
先ほどの為替レートを読み込んだシートの名称を「為替レート読み込み」に変更、それを参照利用するシートとして「為替レート利用」を設定します。
利用の例ですが、通貨ペアUSD/JPYとEUR/JPYのビッドとアスクを参照し、仲値を計算しました。セルのA2、A3に、使いたい通貨ペアであるUSD/JPYとEUR/JPYを設定しています。参照上の工夫としてはvlookup関数を使っています。
セルB2に「==VLOOKUP($A2,Table_0[#すべて],4,FALSE)」と入れています。単純にセルB2に「=為替レート読み込み!B2」と直接セル参照を入れても同じ結果ですが、多少なりとも安全性が向上すると考えています。
直接セル参照すると元のWebサイトで通貨ペアの順序が変わっただけでも、目的のレートが参照できません。vlookup関数を使うことで、順序の変更は吸収可能ですし、その通貨ペアが見つからない場合はエラーになるからです。
この参照の工夫ですが、前回までのようにinvesting.comのページを参照していたように、いろいろな情報がある中で目的のレートを探すためには特に有効と思っています。ただ、今回はみんなのFXさんのブログパーツということで、こちらで指定した為替レートのみの情報となっているため、そのまま使っても特に問題はないかもしれません。あくまで念のためですので、参照上の工夫は取り入れるかどうかは皆さんでご判断を。
データの更新
読み込んだデータの更新ですが、いくつか方法があります。
ひとつは、必要な際に手動で更新する方法です。「データ」タブ⇒「すべて更新」をクリックします。
もうひとつは、「プロパティ」から更新タイミングを設定しておく方法です。「データ」タブ⇒「接続」をクリック。「クエリーTable０」を選択して右側の「プロパティ」をクリックします。そこで「ファイルを開くときにデータを更新する」にチェックをしておくと良いのではないでしょうか。
まとめ
Excel2016になってPower Queryとなり、為替レートだけでなくWeb上の情報を自動取得させる機能が使いやすくなっていました。
また、今回はみんなのFXさんのブログパーツを使わせていただきました。為替レートのみの情報源となっているので、みんなのFXさんで仕様や提供形態の変更がない限りは、安定して取得できるように思います。
みんなのFXさんありがとうございます。
使い慣れたExcelで為替レート取得できるのは便利なのですが、為替レートはリアルタイム性が重要であり、その点ではGoogleFinance関数として利用できるGoogleスプレッドシートの方が簡単です。
Googleスプレッドシートでの為替レート利用について、以下記事でも書いてみていますので良ければ読んでみてください。
はるきち