kotaro Money Blog

機械学習用の為替データベースを構築（毎日自動更新）

為替データをSQLiteで管理する際に、為替ペアごとにデータベースを分けるべきか、それとも一つのデータベースで管理するべきかという疑問が生まれるかと思います。

目的がデータの蓄積、アノマリー分析、そして機械学習の学習用データへの応用であることを考慮に入れて考察すると、結論、一つのデータベースで管理してください。

1. データの一貫性と管理の容易さ

一つのデータベースで管理する場合:

  • 一貫性の維持が容易：スキーマの変更やデータの更新が一度に行えます。
  • 管理が簡単：データベースファイルが一つだけなので、バックアップや移行が容易です。

ペアごとにデータベースを分ける場合:

  • 管理が複雑化：複数のデータベースを個別に管理する必要があります。
  • スキーマの一貫性が難しい：各データベースでスキーマを統一するのが手間です。

2. クエリの柔軟性と分析の容易さ

一つのデータベースで管理する場合:

  • クロスペア分析が容易：異なる通貨ペア間での相関関係やパターンを分析しやすくなります。
  • 複雑なクエリが可能：JOINやサブクエリを用いて高度なデータ分析が行えます。

ペアごとにデータベースを分ける場合:

  • クロスデータベースクエリが困難：SQLiteは複数のデータベース間での直接的なクエリをサポートしていません。
  • データ統合が手間：分析のためにデータを統合する必要がある場合、追加の処理が必要になります。

3. パフォーマンスとスケーラビリティ

一つのデータベースで管理する場合:

  • パフォーマンス：SQLiteは大規模なデータセットでも高いパフォーマンスを発揮しますが、適切なインデックス設計が必要です。
  • スケーラビリティ：通貨ペアの数が増えても、テーブルやレコードを追加するだけで対応できます。

ペアごとにデータベースを分ける場合:

  • パフォーマンスのバラつき：データベースごとにパフォーマンスが異なる可能性があります。
  • スケーラビリティの限界：データベースの数が増えると管理とパフォーマンスの両面で負荷が増大します。

4. 機械学習とデータ分析への応用

一つのデータベースで管理する場合:

  • データ前処理が容易：機械学習モデルの訓練に必要なデータを一括で抽出できます。
  • データの一貫性：統一されたフォーマットでデータを保持でき、前処理が簡単になります。

ペアごとにデータベースを分ける場合:

  • データ収集が複雑化：各データベースからデータを個別に抽出・統合する必要があります。
  • 前処理の手間：データフォーマットの不一致が起こりやすく、前処理に時間がかかります。

以上の点を総合的に考えると、全ての為替ペアを一つのデータベースで管理することをお勧めします。

これにより、データの一貫性と管理の容易さが確保され、クロスペアの分析や機械学習への応用もスムーズに行えます。

具体的な手順

では、ここから具体的な手順についてみていきます。

必要なソフトウェアのインストール

  • Python（3.x系）
  • DBeaver
  • SQLite（DBeaverに内蔵されていますが、コマンドラインで操作したい場合はインストールしてください）

必要なPythonライブラリのインストール

以下のコマンドを使用して、必要なライブラリをインストールします。

pip install yfinance pandas sqlite3

手順2: SQLiteデータベースの作成

DBeaverで新しいSQLiteデータベースを作成

  1. DBeaverを起動します。
  2. メニューから 「ファイル」 -> 「新規」 -> 「SQLite」 を選択します。
  3. データベースファイルの保存場所と名前を指定します（例：forex_data.db）。
  4. 接続が確立されたら、データベースが作成されています。

この時、データベースのパッケージをそのままクラウド同期のフォルダに入れておくと便利です。

データベーススキーマの設計

為替データを保存するテーブルを設計します。

以下はシンプルなテーブル構造の例です。

CREATE TABLE forex_data (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    currency_pair TEXT NOT NULL,
    date DATE NOT NULL,
    open REAL,
    high REAL,
    low REAL,
    close REAL,
    adjusted_close REAL,
    volume INTEGER,
    UNIQUE(currency_pair, date)
);

テーブルの作成

  1. DBeaverで新しいSQLエディタを開きます。
  2. 上記のSQLスクリプトをコピーして貼り付け、実行します。
  3. テーブルが正常に作成されたことを確認します。

筆者のデータベース構造

ここからは実際にデータベースの構築をしていきます。

重要なのは、何をアーカイブしていくのか？

筆者の場合は次のような構造にしています。

  • テクニカル指標のデータは、蓄積した基本データから後で計算できるため、データベースには含めない。
  • 経済指標イベントなどのファンダメンタルデータは、別の専用データベースで管理する。
  • 基本的なデータの蓄積に焦点を当てる。取引量（ボリューム）データは削除する（yfinanceでエラーが出る可能性があるため）。
  • 将来的にデータソース（例：Alpha Vantage API）が変わっても、数十年にわたりデータを蓄積できるような汎用性の高いデータベース構造を設計したい。

基本データのみのデータベース設計

  • シンプルで汎用性のあるスキーマ：異なるデータソースからのデータでも対応可能なフィールド名とデータ型を使用します。
  • 標準的なフィールド名：業界で一般的に使用されるフィールド名を採用し、将来の互換性を確保します。
  • 最低限のデータ項目：基本的な価格情報に焦点を当て、余分なデータは含めません。

テーブル構造の提案

テーブル名: forex_prices
カラム一覧
  1. id (INTEGER, PRIMARY KEY AUTOINCREMENT)
    • ユニークなレコードID。
  2. currency_pair (TEXT, NOT NULL)
    • 通貨ペア（例：USD/JPY）。
  3. date (DATE, NOT NULL)
    • 日付情報。
  4. time (TIME, NULL)
    • 時間情報（データの頻度が日次以上の場合に使用）。
  5. open (REAL, NOT NULL)
    • 始値。
  6. high (REAL, NOT NULL)
    • 高値。
  7. low (REAL, NOT NULL)
    • 安値。
  8. close (REAL, NOT NULL)
    • 終値。
  9. adjusted_close (REAL, NULL)
    • 調整後終値。為替の場合は通常終値と同じですが、将来的な拡張性のために含めます。
ユニーク制約
  • UNIQUE(currency_pair, date, time)
    • 通貨ペア、日付、時間の組み合わせでユニークなレコードを保証します。
インデックスの設定
  • INDEX idx_currency_date_time (currency_pair, date, time)
    • クエリのパフォーマンスを向上させます。

データ型とフィールド名の標準化

  • フィールド名は一般的な金融データベースで使用される名称を採用します（例：open, high, low, close）。
  • データ型はSQLの標準的な型を使用します。
    • 日付: DATE型
    • 時間: TIME型（またはDATETIME型を使用してdate_timeカラムにまとめることも可能）
    • 価格: REAL型

将来のデータソース変更に対応するための考慮

データソース間の差異を吸収する設計

  • フィールドの汎用性：複数のデータソースで共通して提供されるフィールドのみを使用します。
  • データ取得スクリプトの柔軟性：データ取得スクリプトをモジュール化し、データソースを簡単に切り替えられるようにします。

データ取得スクリプトの設計

  • データソースごとのモジュール：yfinanceやAlpha Vantageなど、データソースごとにデータ取得モジュールを作成します。
  • データの標準化：データ取得後に共通のフォーマットに変換します。

エラーハンドリングとデータ検証

  • エラーハンドリング：データ取得時のエラーを適切に処理し、ログに記録します。
  • データ検証：データの整合性をチェックし、不正なデータの挿入を防ぎます。

実装手順の更新

データベースの作成

  1. DBeaverで新しいSQLiteデータベースを作成します（例：forex_data.db）。
  2. テーブルforex_pricesを作成します。以下のSQLスクリプトを使用してください。
CREATE TABLE IF NOT EXISTS forex_prices (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    currency_pair TEXT NOT NULL,
    date DATE NOT NULL,
    open REAL NOT NULL,
    high REAL NOT NULL,
    low REAL NOT NULL,
    close REAL NOT NULL,
    adjusted_close REAL
);

過去できるだけ遡ってデータを蓄積

import yfinance as yf
import pandas as pd
import sqlite3

# 通貨ペアのリスト
currency_pairs = [
    'USDJPY=X', 'EURJPY=X', 'GBPJPY=X', 'AUDJPY=X', 'NZDJPY=X',
    'CADJPY=X', 'CHFJPY=X', 'SGDJPY=X', 'HKDJPY=X', 'SEKJPY=X', 'MXNJPY=X',
    'EURUSD=X', 'GBPUSD=X', 'AUDUSD=X', 'NZDUSD=X',
    'USDCAD=X', 'USDCHF=X', 'USDSGD=X', 'USDHKD=X', 'USDSEK=X', 'USDMXN=X'
]

# データベースへの接続（フルパス指定）
db_path = r"C:\Users\あなたのパスdata.db"
conn = sqlite3.connect(db_path)
cursor = conn.cursor()

for pair in currency_pairs:
    try:
        # yfinanceから最大限の過去データを取得
        data = yf.download(pair, period='max')
        if data.empty:
            print(f"No data found for {pair}")
            continue
        
        # データを整形してデータベースに合わせる
        data.reset_index(inplace=True)
        data['currency_pair'] = pair.replace('=X', '')  # 通貨ペア名から'=X'を削除

        # 必要なカラムのみを選択し、列名をデータベースに対応させる
        data = data[['Date', 'currency_pair', 'Open', 'High', 'Low', 'Close', 'Adj Close']]
        data.rename(columns={
            'Date': 'date',
            'Open': 'open',
            'High': 'high',
            'Low': 'low',
            'Close': 'close',
            'Adj Close': 'adjusted_close'
        }, inplace=True)

        # データをデータベースに挿入
        data.to_sql('forex_prices', conn, if_exists='append', index=False)

        print(f"Data for {pair} inserted successfully.")

    except Exception as e:
        print(f"Error retrieving data for {pair}: {e}")

# データベース接続を閉じる
conn.close()

yfinanceを使用して取得したデータが過去何年分を蓄積できているかは、各通貨ペアやデータ提供者（Yahoo Finance）のサポートによるところが大きいです。

一般的に、yfinanceを使うと、可能な限り過去のデータを遡って取得できますが、通貨ペアや市場によって取得できる年数が異なる場合があります。

毎日の自動更新

あとは、毎日最新の情報を更新していくだけです。

import yfinance as yf
import sqlite3
import datetime

# データベースへの接続
db_path = r"C:\Usersあなたのフルパス.db"
conn = sqlite3.connect(db_path)
cursor = conn.cursor()

# 通貨ペアのリスト
currency_pairs = [
    'USDJPY=X', 'EURJPY=X', 'GBPJPY=X', 'AUDJPY=X', 'NZDJPY=X',
    'CADJPY=X', 'CHFJPY=X', 'SGDJPY=X', 'HKDJPY=X', 'SEKJPY=X', 'MXNJPY=X',
    'EURUSD=X', 'GBPUSD=X', 'AUDUSD=X', 'NZDUSD=X',
    'USDCAD=X', 'USDCHF=X', 'USDSGD=X', 'USDHKD=X', 'USDSEK=X', 'USDMXN=X'
]

# 今日の日付を取得
today = datetime.date.today()

# 通貨ペアごとにデータを取得してデータベースに蓄積
for pair in currency_pairs:
    try:
        # yfinanceから今日のデータを取得
        print(f"Downloading data for {pair} on {today}...")
        data = yf.download(pair, start=today, end=today + datetime.timedelta(days=1), interval='1d')
        
        if data.empty:
            print(f"No data found for {pair} on {today}")
            continue

        # データを整形してデータベースに合わせる
        data.reset_index(inplace=True)
        data['currency_pair'] = pair.replace('=X', '')  # 通貨ペア名から '=X' を削除

        # 必要なカラムのみを選択し、列名をデータベースに対応させる
        data = data[['Date', 'currency_pair', 'Open', 'High', 'Low', 'Close', 'Adj Close']]
        data.rename(columns={
            'Date': 'date',
            'Open': 'open',
            'High': 'high',
            'Low': 'low',
            'Close': 'close',
            'Adj Close': 'adjusted_close'
        }, inplace=True)

        # 小数点以下二桁に丸める
        data['open'] = data['open'].round(2)
        data['high'] = data['high'].round(2)
        data['low'] = data['low'].round(2)
        data['close'] = data['close'].round(2)
        data['adjusted_close'] = data['adjusted_close'].round(2)

        # データベースに挿入
        data.to_sql('forex_prices', conn, if_exists='append', index=False)

        print(f"Data for {pair} inserted successfully.")

    except Exception as e:
        print(f"Error retrieving data for {pair} on {today}: {e}")

# データベース接続を閉じる
conn.close()

最新の情報を見るクエリ

SELECT
    p.currency_pair,
    p.date,
    p.open / 100.0 AS open,
    p.high / 100.0 AS high,
    p.low / 100.0 AS low,
    p.close / 100.0 AS close,
    p.adjusted_close / 100.0 AS adjusted_close
FROM
    forex_prices p
INNER JOIN (
    SELECT
        currency_pair,
        MAX(date) AS latest_date
    FROM
        forex_prices
    GROUP BY
        currency_pair
) AS latest ON
    p.currency_pair = latest.currency_pair AND
    p.date = latest.latest_date
ORDER BY
    p.currency_pair;