BigQuery を使用した財務時系列分析

もしあなたが計量アナリストであれば、マーケット トレンドのインサイトを得るために、さまざまなツールや技術を駆使して市場取引の履歴などのビッグデータを活用していることでしょう。相場価格の変化、取引、および他のイベントは予測可能な間隔で発生するため、そのようなデータは財務時系列を表し、頻度分析や移動平均などの確立された技術を用いて分析することができます。

しかし巨大なデータセットを扱うことは容易ではない場合があります。成長し続けるデータセットに対して、従来型のツールでは十分に機能を拡張できないことがあります。ストレージ要件もデータセットと同じ速度で増加するため、コンピュータのハードドライブにデータをダウンロードすることは、もはや有効なアプローチではありません。そして、従来型のデータベース クエリから適切なデータのサブセットを得るには長い時間がかかる場合もあります。

Google BigQuery では、追加専用のテーブルに対して SQL 形式のクエリを実行し、さらに Google のインフラストラクチャの処理能力を活用して非常に迅速に結果が返されるようにして、このような問題を解決しています。BigQuery は、ウェブ上で、コマンドラインで、また REST API などの API を通じて使用できます。BigQuery を Google Cloud Platform の他のコンポーネントやサードパーティのツールと組み合わせると、現在必要とされるデータ分析ソリューションを構築でき、さらに将来におけるソリューションの拡張も自信を持って行うことができます。

財務データを扱う際には、セキュリティは常に重要です。Google Cloud Platform は、データを安全かつプライバシーに配慮したいくつかの方法で保持するのに役立ちます。たとえば、すべてのデータが伝送時および保存時に暗号化されます。Cloud Platform は ISO 27001SOC3、FINRA、PCI に準拠しています。

目標

  • データセットを BigQuery にロードします。
  • BigQuery ウェブ UI を使用して、さまざまな財務時系列クエリを実行します。
  • クエリの結果を可視化します。

事前準備

  • Google Cloud Platform Console のプロジェクトに対して BigQuery API を有効にします。
  • 課金を有効にします。
  • Google Cloud SDK をクライアントのオペレーティング システムにインストールします。

サンプルデータの読み込み

このチュートリアルでは外国為替(FX)のスポット市場データを使用します。このデータは長期間にわたる国際通貨の為替レートの相場を表しています。これらの値には、通貨取引のブローカーから提示された相場価格が記録されます。これは人々が旅行をしたとき、大規模な金融機関が国や地域の間で資金を移動させたとき、または投機家が通過の取引によって利益を上げたいときなどに発生します。データには次の形式を使用します。

<venue>,
<from-currency>/<to-currency>,
<timestamp>,
<bid-price>,
<ask-price>

サンプル データセット内の相場価格は、すべて英ポンド(GBP)と米ドル(USD)間の交換に対するものです。タイムスタンプは常にグリニッジ標準時(GMT)で表され、最後の 3 桁の数字は常にゼロになります。これによって時系列の最小単位は 1 ミリ秒になります。入札価格は、買い手がその通貨に対して支払うと提示した額を表し、売り呼び値は売り手が提示した単位あたりの価格を表します。

サンプルデータを読み込む手順は次のとおりです。

  1. 現在のプロジェクトを設定します。ターミナル ウィンドウで次のコマンドを入力します。

    gcloud config set project <your_project_id>
    
  2. BigQuery に新しいデータセットを作成します。

    bq mk timeseries
    
  3. CSV ファイルからデータを読み込みます。最初のファイルには 2014 年 1 月のデータが、2 番目のファイルには 2 月のデータが含まれています。これらのデータセットの読み込みには、しばらく時間がかかる場合があります。

    bq load timeseries.gbpusd_0114 gs://solutions-public-assets/time-series-master/GBPUSD_2014_01.csv venue:STRING,currencies:STRING,time:TIMESTAMP,bid:FLOAT,ask:FLOAT
    
    bq load timeseries.gbpusd_0214 gs://solutions-public-assets/time-series-master/GBPUSD_2014_02.csv venue:STRING,currencies:STRING,time:TIMESTAMP,bid:FLOAT,ask:FLOAT
    
  4. BigQuery ウェブ UI を開きます。

  5. テーブルのスキーマを確認します。プロジェクト名の下で、timeseries データセットを展開して、gbpusd_0114 をクリックします。

次の図は gbpusd_0114 テーブルのテーブル スキーマを表しています。gbpusd_0214 テーブルのスキーマは同じです。

テーブルの詳細

1 月の相場価格に対してクエリを実行する

FX データの様子を調べるために使用できる簡単なクエリから始めます。

  1. BigQuery ウェブ UI で、[Compose Query] をクリックします。

  2. [New Query] テキスト ボックスに、次のクエリを入力します。

    SELECT
     venue,
     currencies,
     time,
     bid,
     ask
    FROM
     timeseries.gbpusd_0114
    ORDER BY
     time ASC
    LIMIT   1000;
    
  3. [Run Query] をクリックします。

    このクエリでは返される結果を最初の 1,000 行に制限しています。これはブラウザに大量のデータが返されるのを防ぐためと、費用を減らすためです。[Next] や [Prev] のリンクなど、ツールで用意されているコントロールを使用して、しばらくデータの内容を確認してください。time 列のデータの形式に、元のデータの最小単位であるミリ秒に至るまでは表示されていないことに注意してください。

    2014-01-01 00:10:10 UTC
    
  4. 組み込み関数を使用するようクエリを変更して、時刻情報の形式を変更します。[New Query] テキスト ボックスで、SELECT 文の最初の部分を次のように変更します。

    SELECT FORMAT_UTC_USEC(time) AS time,
    

    FORMAT_UTC_USEC は組み込み関数で、時刻データの形式を協定世界時(UTC)に変更します。BigQuery には組み込み関数が多数用意されており、これらを使用して大規模なデータセットに対してクエリを容易に実行できます。

  5. [Run Query] をクリックして、結果を確認します。データの形式が UTC に変わっており、タイムスタンプが最小単位まで表示されています。

    2014-01-01 00:00:05.763000
    

一定の時間枠の入札を表示する

次に、特定の時間枠内の結果だけが表示されるようクエリを変更します。これによって、より管理しやすい結果が導き出され、その結果を CSV ファイルとして迅速にダウンロードすることができます。

[New Query] テキスト ボックスに、次のクエリを入力して実行します。このクエリでは、取得する結果を 1 月 1 日の 30 分間の時間枠に限定します。

SELECT
  TIME(time) AS time,
  bid
FROM
  timeseries.gbpusd_0114
WHERE
  time BETWEEN TIMESTAMP("2014-01-01 00:00:00.000")
  AND TIMESTAMP("2014-01-01 00:29:59.999")
ORDER BY
  time ASC;

このクエリでは TIME 関数を使用して、時刻の形式を短縮して表示します。得られた結果は特定の日のものであることがわかっていて、タイムスタンプ全体を確認する必要がないためです。次の手順で作成するグラフはこの形式を使用することで見やすくなっています。

結果を CSV ファイルとしてダウンロードして、グラフを Google スプレッドシートで表示すると、このデータを可視化できます。手順は次のとおりです。

  1. [Download as CSV] をクリックします。
  2. [Google スプレッドシート] をクリックして、新しいスプレッドシートを作成します。
  3. [ファイル] > [インポート] をクリックします。使用しているコンピュータからファイルをアップロードして、結果をインポートします。
  4. [ファイルをインポート] ダイアログ ボックスで [現在のシートを置換する] を選択し、[インポート] をクリックします。
  5. Ctrl+A キーを押して、スプレッドシート内のすべてのデータを選択します。
  6. [挿入] > [グラフ] をクリックして、データから新しいグラフを作成します。
  7. [グラフ] タブで折れ線グラフを選択して、[挿入] をクリックします。

以下のようにデータが折れ線グラフとして表示されます。

入札を表す折れ線グラフ

このグラフから、この日の最初の 20 分間においてはポンドに対してドルは概ね低く推移し、次の 5 分間にわずかに持ち直していることがわかります。

テーブルを結合する

BigQuery に読み込んだデータは 2 つの CSV ファイルに分かれていて、2 つのテーブルに入っています。テーブルクエリを組み込むと、次の例に示すようにこれらのデータを容易に結合できます。この場合は、SQL 文によって「gbpusd」が含まれる ID を持つすべてのテーブルを結合しています。これは作成したテーブルの両方に該当します。このクエリは単にデータセット内の最初と最後のタイムスタンプを返します。

SELECT
  MIN(time) AS time1,
  MAX(time) AS time2
FROM
  (TABLE_QUERY(timeseries,
      'table_id CONTAINS "gbpusd"'));

特定のテーブルを名前で結合することもできます。たとえば、次のクエリでは、1 月 31 日から 2 月 1 日にかけての深夜前後(時間帯は GMT)の売り呼び値を取得します。SELECT 句でカラムをカンマで区切って並べると、BigQuery SQL では UNION が実行されます。これは以前に SQL を使用していたユーザーが期待する従来の JOIN オペレーションではありません。

SELECT
  STRFTIME_UTC_USEC(time,"%m-%d-%Y %r") AS time,
  ask
FROM
  timeseries.gbpusd_0114,
  timeseries.gbpusd_0214
WHERE
  time BETWEEN TIMESTAMP("2014-01-31 23:30:00.000")
  AND TIMESTAMP("2014-02-01 00:29:59.999")
ORDER BY
  time ASC;

このクエリでは STRFTIME_UTC_USEC 関数を使用して、次の 2 つの理由によってタイムスタンプの形式を設定しています。まず、タイムスタンプを、2 日間にわたってソートできる形式にする必要があります。HOUR などといった形式は、ソート結果に日付の変化が反映されないため、意味を持ちません。次に、グラフのラベルを適切な形式にします。たとえば、UTC でのミリ秒までが含まれる時刻全体をグラフのラベルに使用することはできますが、細かすぎる時刻はグラフのラベルとしては特に有効ではありません。

次の図は、グラフ化された結果を表しています。

複数のファイルに関する折れ線グラフ

ここでは、2 つのテーブルの境界で売り呼び値に起きたことが正確にわかります。

データの頻度を分析する

金融市場では相場価格の変化率から興味深い情報が得られることが時々あります。クエリを実行して、一定時間内におけるティックと呼ばれる FX の取引数に関する情報を取得することができます。ティックとは、時計の針の音のように一定の時間単位を示すものではないことに注意してください。1 ティックとは、多くの場合は価格の変化を表す 1 つの相場価格です。

次のクエリでは、2014 年 1 月 16 日の 1 時間ごとのティックの数を返します。

SELECT
  HOUR(time) AS hour,
  COUNT(time) AS num_ticks
FROM
  timeseries.gbpusd_0114
WHERE
  time BETWEEN TIMESTAMP("2014-01-16 00:00:00.000")
  AND TIMESTAMP("2014-01-16 23:59:59.999")
GROUP BY
  hour
ORDER BY
  hour ASC;

次の棒グラフはその結果を表しています。

頻度に関する棒グラフ

このグラフから、その日の正午と午後 4 時(時間帯は GMT)の間にティックの量が最大になったことがわかります。

次に、以下のクエリを実行すると、2014 年 1 月の 1 か月間の毎日における 1 時間ごとのティックの平均数がわかります。このクエリでは、ティックの総数をその月の取引日数で割った後、時間単位でグループ化して平均値を計算します。

SELECT
  HOUR(time) AS hour,
  COUNT(time)/COUNT(DISTINCT DAY(time)) AS avg_ticks
FROM
  timeseries.gbpusd_0114
GROUP BY
  hour
ORDER BY
  hour ASC;

次のグラフは平均の分布を表しています。この分布は、1 月 16 日について得た結果と似ています。したがって、その日は相場の動きに関してはその月における極めて平均的な日だったことがわかります。

平均頻度を表す棒グラフ

移動平均を計算する

財務時系列分析の基本的なテクニックの 1 つが単純移動平均(SMA)です。BigQuery を使用して FX データの SMA を計算できます。次のクエリは、1 月 16 日の午前中の 3 時間における入札価格の 60 秒間の SMA の時系列を取得します。

SELECT
  TIME(S1.time) AS bid_time,
  AVG(S2.bid) AS avg_bid
FROM
  timeseries.gbpusd_0114 AS S1
JOIN EACH
  timeseries.gbpusd_0114 AS S2
ON
  S1.time = S2.time
WHERE
  TIMESTAMP_TO_SEC(S2.time)
  BETWEEN (TIMESTAMP_TO_SEC(S1.time) - 60)
  AND TIMESTAMP_TO_SEC(S1.time)
  AND
  S1.time
  BETWEEN TIMESTAMP("2014-01-16 09:00:00.000")
  AND TIMESTAMP("2014-01-16 11:59:59.999")
GROUP BY
  bid_time
ORDER BY
  bid_time ASC;

このクエリは組み込み関数の AVG を使用して平均を計算しています。まず、テーブルのタイムスタンプに対して自己結合を実行し、次に WHERE 句を使用して結合の結果を 60 秒の時間範囲に限定して移動平均のためのウィンドウを作成します。JOIN 句では、EACH 修飾子を使用して BigQuery に結合で 2 つの大きなテーブルを参照するというヒントを与えています。クエリの実行エンジンではこのヒントを使用して最適化を行います。最後に、GROUP BY 句で移動平均を集約し、ORDER BY 句で結果をソートします。

次のグラフはこのデータの折れ線グラフを表しています。

移動平均を表す折れ線グラフ

テストデータを削除する

テストデータを使用する作業が終了したら、データセットを削除する必要があります。手順は次のとおりです。

  1. BigQuery ウェブ UI で、timeseries というデータセット名の右側にある下向き矢印のボタンをクリックします。
  2. [Delete dataset] をクリックします。
  3. 削除操作の確認が表示されたら、[OK] をクリックします。

BigQuery の詳細については、BigQuery のドキュメントをご覧ください。

次のステップ

Google Cloud Platform のその他の機能を試すには、チュートリアルをご覧ください。

このページは役立ちましたか?評価をお願いいたします。

フィードバックを送信...