Google スプレッドシートによる為替レートの取得方法の色々

情報源

アメリカ株投資をしていると為替レートの情報がほしくなる時があります。

例えば、

  • 現在の為替レートがほしい
  • 自分のポートフォリオのある時点での評価損益を知るためにその時点での為替レートがほしい
  • 自分のポートフォリオの評価損益の履歴を見るために為替レートの履歴がほしい

などなど、他にもほしいケースはあると思いますが、だいたいこんなところだと思います。

Google スプレッドシートのGoogleFinance関数だと非常に簡単に取得できます。

ある日の為替レートの取得方法

以下のようにセルに入力すれば、簡単に取得できます。

=GoogleFinance("currency:usdjpy")

出力イメージは以下のような感じです。

googlefinance_kawase_today

指定日から今日までの為替レートの履歴

今度は為替レートの履歴の取得です。以下のように入力します。

=GoogleFinance("currency:usdjpy","price","2016/7/24",TODAY())

実際の出力イメージは以下のような感じです。

googlefinance関数による出力イメージ

この方法はちょっと調べたいといったときには便利です。

しかし、1つのスプレッドシートで複数のシートを用いて色々と分析し、そのスプレッドシートをずっとメンテナンスする場合は非常に大変な目に合います。

なので次以降のパターンがお勧めです。

指定日から今日までの為替レートの履歴(開始日を他のセルを参照するパターン)

A1のセルに「2016/7/24」と日付が入力されている場合以下のようにすることで上記の出力イメージと同様の結果が得られます。

これの良い点は、ある日付(ここでは開始日)をいくつかのセルで利用する場合、開始日の入力がある1つのセルの値を変更するだけでいい点です。

=GoogleFinance("currency:usdjpy","price",A1,TODAY())

A1の値をそのシートでしか使わない場合はこの方法でいいかなと思います。

しかし、そのA1の値を他のシートでも利用する場合は「シート1!A1」などの指定方法をしないといけないので、他のシートでもA1(ここでは開始日)を利用したい場合は次の方法がおすすめです。

指定日から今日までの為替レートの履歴(開始日は名前付きのセルを参照するパターン)

これが一番おすすめです。開始日の入っているセルをA1などとしていると、「開始日は○○シートのA1だ」と覚えておかなければなりません。これは意外と面倒です。

そこで、セルに名前を付けて、その名前で開始日を指定する方法の登場です。

まずは、メニューの[データ]-[名前付き範囲]を選びます。そうすると右の方に「名前付き範囲」というパネルが出てきます。そのパネルの[範囲を追加]をクリックします。

そうすると名前の入力欄とセルを選択する欄が表示されるので、名前を入力して、開始日が入力されているセルを指定します。

googlefinance関数で投資開始日を名前付き範囲で指定する場合

下準備はここまでです。セルに以下のように入力します。

=GoogleFinance("currency:usdjpy","price",開始日,TODAY())

この「開始日」は他のシートでも利用できるのでこの方法が一番おすすめです。慣れてしまえば簡単ですよ(^^ゞ

指定日から今日までの為替レートの履歴(Date、Closeのようなタイトル行はいらない場合

上記で取得したデータは必ずタイトル行として「Date」と「Close」が入ってきてしまいます。

これが不要だということがあると思います。

思い当たるケースとしては以下がありますね。

  • 5年分のデータがほしい、でもスプレッドシートを開くたびに5年分のデータをダウンロードされるのは重くていやだ
  • なので、古い4年間分くらいはわざわざデータをダウンロードしないでほしい

上記のケースだと、以下のような形で実現することになると思うのです。

  • 古い4年分をGoogleFinance関数で取得して、そのデータを値のみの貼り付けをし、データを固定で持つ(そうするとわざわざダウンロードされない)
  • 古い4年分のデータの最終行の次の行から直近の1年分のデータはGoogleFinane関数を使ってデータを取得

そうすると固定のデータの後のGoogleFinance関数のデータの間に「Date」、「Close」が挟まってデータ分析の邪魔をします。

以下のようなイメージです。

googlefinance_kawase_title

そういった場合、以下のようにすることでデータの連続性が保つことができます。

=FILTER(GoogleFinance("currency:usdjpy","price","2016/7/24",TODAY()),INDEX(GoogleFinance("currency:usdjpy","price","2016/7/24",TODAY()),,2)<>"Close")

 

少し長くなってしまいましたが、以上です。