エクセルでもできた~為替レート自動取得方法&サンプルシート

この記事はExcel2013のWebクエリを利用した記事ですが、Excel2016でのPower Queryを利用した為替レート取得の記事を書きました。Excel2016の方は、こちらの記事を確認してみてください。

—————-
2018年11月3日追記、修正
閲覧いただきありがとうございます。
この記事を参照してくださっている方が思ったよりいらっしゃったので内容を見直しました。
・「エクセル内での参照の工夫」の章を追加
・サンプルシートをダウンロード用に作成
—————-

先日の記事「スワップ鞘取り ~資金管理にGoogleスプレッドシートを活用する方法」で、Googleスプレッドシートで為替レートを自動取得する方法を紹介しました。GoogleFinance関数を使うことでリアルタイムに近い為替レートを自動取得してFX投資での資金管理をしていくことにしました。

はるきちは、データ分析業務に携わっていたことがあり、ず~っとエクセルを使ってきておりまして、Googleスプレッドシートよりはエクセルを使うのが楽ちんです。ということでエクセルでも為替レートを自動取得出来ましたので紹介します。

スポンサーリンク

エクセルでWeb上のデータを取得する

元データとなるWebサイトを探す

エクセルで為替レートを取得する場合に以下2点に合うWebサイトを探す必要があります。

  1. 必要な通貨の為替レートが提供されている
    FX投資の検討・管理の元データですので、必要な通貨に対応していることが条件
  2. エクセルのWebクエリ機能で取得できる

この1と2を満たすWebサイトを見つける必要あるのですが、探すのは少し面倒です。まず、FX業者さんのホームページの為替レート情報をいくつか試しました。最近、中国元にも関心があるためポジションを持っているSBI証券(為替レートページ)や楽天証券(為替レートページ)で確認しましたが、うまく取得できませんでした。

いろいろ探してみた結果、Investing.comの各国通貨レートのページで取得してみています。

エクセルでWebクエリ機能を使う

対象のWebサイトが決まれば(前の探している段階で既にこの作業は必要ですが)、エクセルに取り込みます。新規ブックで「データ」タブ⇒「Webクエリ」を選択します。

Webクエリの画面で、「アドレス」に対象ページのURLを入れて「移動」をクリック。しばらくすると対象ページが読み込まれますので、読み込みたい「テーブル(表)」を指定します。今回は、左上の「黄色い矢印」を選択し(緑に変わる)、右下の「取り込み」をクリックします。次のダイアログで、既存シートか新規シートを指定するとデータが読み込まれます。

かなりスクロールするくらい下の方になりますが図の通りです。
通貨の為替レートだけでなく、ビットコインや金、銀などのレートも含まれています。

この後は、自分の使いたいシートから必要な部分を参照して使うことができます。

エクセル内での参照の工夫(2018/11/03に本章追加)

この章の内容は、言葉だと少しわかりにくいかもしれないので、サンプルのエクセルシートを作成しました。ダウンロードはこちら
EXCELのバージョンは2013、今日時点では動作確認していますが、利用は自己責任でお願いします。

自分の使いたいシートから必要な部分をセル参照して使うことができます。ただ、セル参照だと参照しているWebサイト側で少しでもレイアウトが変わると正しく参照できませんし、その場合もエラーになりにくいので気が付かない可能性が高まります。

その点の工夫としてvlookup関数を使うことにしました。「元データ(Investment.com)」のシートは、先ほどのWebクエリのシートです。もう一枚、「為替レート(整理後)」のシートを作ります。

  • A列の通貨ペアの部分は、通貨ペア名テキストを入力(「元データ(Investment.com)」シートからコピペ)しています。
  • B列、C列がポイントです。セルB2には「=VLOOKUP($A2,’元データ(Investment.com)’!$C$1:$E$999,2,FALSE)」と入れています。
    • 「元データ(Investment.com)」シートのC列から、セルA2の「EUR/USD」を探し、その隣の列のEUR/USDのレートを取得しています。
    • 引数に”FALSE”を指定して、ヒットしなかった場合はエラーになるようにします。
    • ここは仮定なのですがInvestment.comからは、C列に通貨のテキスト表示、C、D列に為替レート(ビッド、アスク)が入るとしています。上下にずれてもvlookup関数で取得できます。左右にずれるとエラーになるはずです。

例えば、EUR/USDの為替レートを参照する際に、Webクエリのシートを直接セル参照(”=’元データ(Investment.com)’!D386″)とするよりも、”=VLOOKUP($A2,’元データ(Investment.com)’!$C$1:$E$999,2,FALSE)”とした方が、以下の点で良いとの考えです。

  • 参照しているWebサイトのレイアウト変更を多少は吸収できる
    • 上下にずれても、vlookupで検索しているので追随できる
  • きちんと参照できていない場合のリスクが減る
    • 上下にずれた場合に、気が付かずに他のレートを参照したままになるリスクが減る
    • 左右にずれて検索で見つからない場合には、エラーになる

あくまで、はるきちの想定で参照するWebサイトの影響はどうしてもあります。取り入れるかどうかは皆さんでご判断を。

データの更新

読み込んだデータの更新ですが、いくつか方法があります。

ひとつは、必要な際に手動で更新する方法です。「データ」タブ⇒「すべて更新」or「更新」をクリックします。

もうひとつは、「プロパティ」から更新タイミングを設定しておく方法です。「ファイルを開くときにデータを更新する」としておくと良いのではないでしょうか。

まとめ

エクセルにもWebクエリ機能があり、為替レートだけでなくWeb上の情報を自動取得させることができます。ちょっと使ってみた感触をまとめてみます。

  • メリット
    • 多様な使い方ができる
      GoogleFinance関数のように金融系の為替レートや株価(米国)に特化した関数ではないため、いろんな会社のスワップポイント情報を集めるとか多様な使い方ができそうです。
      注)GoogleスプレッドシートにもImportHTML関数等で近いことができます。
    • 使いなれたエクセルでデータ活用できる
      これは万人に共通するわけではありませんが、Googleスプレッドシートよりも使いなれた人が多いと思います。
  • デメリット
    • 取得先のWebサイトの仕様に影響される
      今回で言えば、為替レート提供サイトの仕様が変わると取得できなくなったり、シートの調整が必要になります。

はるきちはエクセルを使い慣れていますので、エクセルで為替レート取得できるならと喜びましたが、為替レート取得に限ればGoogleスプレッドシートの方が簡単です。為替レート取得以外のところがメインの場合はエクセル、為替レート取得以外は簡単ならGoogleスプレッドシートといった、目的に応じた使い分けということになりそうです。

また今回使ったInvestment.comの為替レートページですが、取得できるデータが多すぎて大変であったり、仕様が変わりそうな不安という感触もありました。
エクセルのWebクエリを使っている/試した方で、このサイトが取得先として良いよというお薦めがあれば是非コメントで教えてください。

はるきち

———–

この記事ではExcel2013(詳しくは下の図)を使っています。

コメント

  1. 通りすがり より:

    エクセルで為替レートが参照できるのは助かります。
    サンプルシートは参考になりました。

  2. しん2222 より:

    分かりやすい説明で、取り込む作業まで出来ました。
    USD以外で取り込みたい場合はどうしたらよろしいでしょうか?

    • はるきち より:

      しん2222さん
      はるきちです、コメントありがとうございます。
      USD以外のレートにしたい場合の方法は二つあります。
      ひとつは、希望のレートのあるWebサイトから取得する方法です。ですがこれは少し面倒と思います。
      もうひとつは、エクセル上でUSDのレートを使って計算してしまう方法です。例えば、ポンド円がほしい場合に
      GBPJPY = GBPUSD * USDJPY
      をエクセル上で計算して使います。
      私は、エクセル上に希望のレートだけ計算するシートを追加してこんな形でやっています。

  3. タイガー戦車 より:

    紹介してくれたinvesting.comからエクセルへ
    データタブ「Webから」接続できないのでデータが読み込めません。

    すでにあるファイルからも「更新ボタン」を押しても
    接続できなくなってしまいました。
    対処法はありますか?
    エクセルは最新版です。

    • はるきち より:

      タイガー戦車さん
      読んでいただき、ありがとうございます。
      データが読み込めないとのことなので、私もこのページに置いてあるサンプルをダウンロードして実行してみたところ、問題なく取得ができました。
      もしかしたらタイミングの問題などがあったのかもしれません。
      再度試していただくか、
      私が、試したExcelは、 Excel 2013(15.0.5197.1000) MSO(15.0.5172.1000)64ビット です。
      タイガー戦車さんのExcelは最新版とのことなので、こちらより新しいと思います。
      このページの冒頭で紹介しているExcel2016用の記事(http://nevertoolate.tokyo/822)でのサンプルを使うと状況が改善するかもしれません。
      はるきち

  4. タイガー戦車 より:

    はるきち様へ
    数日前までは、接続できていたのに理由がよくわかりません。

    ・リンクに貼ってあるページ(EXCEL2016版)を使用したら
     データが更新できません。

    ・このページのリンク(2018/11/03に本章追加)を使用したら
     データが更新できました。

    大変助かりました。

    • はるきち より:

      タイガー戦車さん

      ひとまず、動作されたとのことで良かったです。
      ただ、確かにExcel2016版記事のサンプルでは取得がうまく行かないですね。
      急ぎ記事にその旨追記しました。
      お知らせいただきありがとうございました。

      そういえば、子供のころにリモコン戦車(ラジコンでなく)のプラモデルで遊んだのですが、その一台がタイガー戦車だったような。
      古くてすみません。

      はるきち

    • はるきち より:

      タイガー戦車さん

      Excel2016で取得できなくなった理由の確かなところはわからないのですが、代替のレート提供元として「みんなのFX」さんのブログパーツを使う形に変更してみました。
      コメント頂いたことで早めに気が付くことができて助かりました。

      ありがとうございました。

      はるきち

  5. 槇原 信雄 より:

    紹介してくれたinvesting.comからエクセル(Office365)へ為替レートDATAを貼り付ける事が出来ました。
    大変助かりました。ありがとうございます。

    • はるきち より:

      槇原さん

      はるきちです
      参照してくださって、ありがとうございます。
      しばらく、仕事が忙しくてBlogのこと見れていなかったので承認するのが遅れました。
      ごめんなさい。

      今後ともよろしくお願いします。