エクセルで為替レートをシートに自動取得する方法（決定版）追記：使い方制約有

2017/10/28 Excel＆Google Spreadsheet

この件は一度記事にしています。「エクセルでもできた　～為替レートをシートに自動取得する方法」です。

その際に便利ではあるが面倒＆不安定なところとして挙げたのは、為替レート取得先のWebサイトの状況に依存するという点です。データ取得先難民化するリスクがどうしてもあるのです。

その点がなんとかならないかとずっと考えていて、裏技チックなのですが必要なレートを取得するアイデアを思いついたので共有します。
個人的には「決定版」と思っています。

中身は、エクセルとGoogleスプレッドシートを組み合わせる裏技です。

————–
■追記20171028
自分としては決定版と考えていたこの方法ですが、使い方に制約がありました。
Excelシートで為替レートを取得する際に、Googleスプレッドシート側も立ち上げる必要があります。ExcelのWebクエリでのアクセスでは、Googleスプレッドシート側の為替レートが最新値に更新されないようです。
これまでの自己使用や検証では、両方のシートを立ち上げて比較確認していたために気が付きませんでした。この記事については、その点をご理解の上で試してみてください。
この件、他の方法も試していくので良い方法があれば共有いたします。
————–

スポンサーリンク

自分用為替レート参照サイトをGoogleスプレッドシートで作る

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

  1. 必要な通貨の為替レートが提供されている
  2. エクセルのWebクエリ機能で取得できる

この１と２を満たすWebサイトを見つけるのが手間でした。また、取得先サイトの仕様が変わるとそれに追随する必要があります。

今回は、この参照用WebサイトをGoogleスプレッドシートで自分仕様で作ってしまうというアイデアです。

現在、はるきちが使っている参照用サイトを参考として（こちら）共有します。
詳細はそれを参照してください。参考シートを使ってエクセルでうまく取り込めたら、自分用為替レートサイトをGoogleスプレッドシート自作すると良いと思います。

作成手順は、以下の通りです。

■手順１：画像のように、目的の為替レートのみのシンプルなシートを作ります。

  • 為替レートは「=GoogleFinance(“currency:”&A2, “average”)」という形で取ってきています。関数の詳細は、Googleドキュメントのヘルプ（こちら）を参照してください。

Googleスプレッドシートで作成した為替レート参照サイト

■手順２：エクセルからもアクセスできるように共有設定を行います。

  • 右上の「共有」をクリック、そして「共有可能なリンクを取得」をクリック。
    この手続きで当該シートはリンクのURLを知っている人はアクセス可能になります。公開しても良いデータのみを載せるようにしてください。

Googleスプレッドシートの共有１

  • 次に、「リンクをコピー」をクリック、「完了」をクリックです。

Googleスプレッドシートの共有２

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

エクセルに取り込みます。新規ブックで「データ」タブ⇒「Webクエリ」を選択します。

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

すこし右下に為替レートが入ります。今回の例では、赤枠のセルのB40～C51の部分に入ります。

エクセルのサンプル１

エクセル内での参照の工夫

この後は、自分の使いたいシートから必要な部分をセル参照して使うことができます。ただ、セル参照だとGoogleスプレッドシート側で少しでも場所が動くと正しく参照できませんし、その場合もエラーになりにくいので気が付かない可能性が高まります。

その点の工夫としてvlookup関数を使うことにしました。画像の為替レート読み込みシートは、先ほどのWebクエリのシートです。もう一枚、為替レート整理シートを作りました。

  • A列のDATETIME～CNHJPYの部分は、テキストを入力（Googleスプレッドシートからコピペ）しています。
  • B列がポイントです。セルB2には「=VLOOKUP(A2,為替レート読み込み!$B$1:$C$999,2,FALSE)」と入れています。
    • 為替レート読み込みシートのB列から、セルA2の「USDJPY」を探し、その隣の列のUSDJPYのレートを取得しています。
    • 引数に”FALSE”を指定して、ヒットしなかった場合はエラーになるようにします。
      仮定ですが、Googleスプレッドシートからは、B列に通貨のテキスト表示、C列に為替レートが入るとしています。上下にずれてもvlookup関数で取得できます。左右にずれるとエラーになるはずです。
  • セルA1、B1のDATETIMEは、Googleスプレッドシート側での日付、時刻を取得して最新のシートを参照しているかの確認にしています。B1の表示が日付時刻にならなかったら、セル書式設定で”日付時刻”の表示形式を選択してください。

エクセルで為替レートを取得するサンプル画面

例えば、USDJPYの為替レートを参照する際に、Webクエリのシートを直接セル参照（”=為替レート読み込み!C41″）とするよりも、”=VLOOKUP(“USDJPY”,為替レート読み込み!$B$1:$C$999,2,FALSE)”とした方が安全性が増すという考えです。

データの更新設定

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

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

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

まとめ

ちょっと裏技的ですが、エクセルに為替レートを取り込む際に、Googleスプレッドシートを使った自作為替レートサイトを作成する方法を紹介しました。

Googleスプレッドシートも使う分の手間が増えますが、前記事で書いた下記のデメリットを大幅に軽減できますし、必要な為替レートをGoogleFinance関数で取得できるので良いと思います。

デメリット
取得先のWebサイトの仕様に影響される 今回で言えば、為替レート提供サイトの仕様が変わると取得できなくなったり、シートの調整が必要になります。

自分としてはこれを「決定版」として使っていくつもりです。

はるきち

———–

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