KATOエンジニヤリング開発日誌

「アウトプット無きエンジニアにインプットもチャンスも無い」の精神で書いています

Pythonで日本株の月次平均収益率を算出するスクリプトを開発した

これまでの開発でKABU+から様々なデータを取得できるようになったので、そろそろデータの活用をしたいと思い適当な統計指標を探していたところ、下記の本に記載されている「月次平均収益率」が簡単で有意義な統計指標だと感じました。

完全独習 統計学入門

完全独習 統計学入門

「月次平均収益率」が何なのかこの本を参考にすると、「ある銘柄の株が1ヶ月間の間に何パーセント値上がり(値下がり)したかを表す指標」で、この指標を数ヶ月間に渡って集計すれば時系列データとしても使えそうに思いました。

月次平均収益率の算出方法

月次平均収益率の計算式は下記のようになっています。

 \displaystyle 月次平均収益率=\frac{期末株価 - 期首株価}{期首株価}

これは単純に値上がりしたか値下がりしたかを見るのではなく、どちらかというと「どれくらい株価が変動するリスクがあるか」のボラティリティを測るのに良さそうです。

ソースコード

今回作成したスクリプトのソースコードは下記になります。

※GitHubにも載せています

github.com

#coding:utf-8

import statistics_config
import logging
import slackweb
import configparser
import mysql.connector
import sys
import datetime
from dateutil.relativedelta import relativedelta


BASE_DIR = '/usr/local/script/'
account = BASE_DIR + 'config/account.ini'
account_config = configparser.ConfigParser()
account_config.read(account, 'UTF-8')

# Constants
DB_USER = account_config.get('db', 'DB_USER')
DB_PASSWORD = account_config.get('db', 'DB_PASSWORD')
DB_HOST = account_config.get('db', 'DB_HOST')
DB_DATABASE = account_config.get('db', 'DB_DATABASE')
INPUT_TABLE_NIKKEI225 = 'nikkei225_topix_stock_prices'
INPUT_TABLE_ALL_STOCK_PRICES = 'japan_all_stock_prices'
OUTPUT_TABLE = 'monthly_average_rate_of_return'

args = sys.argv

if __name__ == '__main__':
    # モジュール名でロガーを生成する(メインモジュールは 名前が '__main__' になる)
    log = logging.getLogger(__name__)
    # Slack Incoming webhook設定
    slack_log_url = account_config.get('slack', 'slack_log_url')
    slack = slackweb.Slack(url=slack_log_url)

    log.info('統計処理 月次平均収益率 算出 : 開始')

    # 対象の日付を設定(引数でYYYYMM形式で年月を入れるとその年月の統計処理を対象とする)
    if len(args) < 2:
        # 引数を指定しない場合は前月を対象とする
        TODAY = datetime.date.today()
        temp_target_month = TODAY - relativedelta(months=1)
        TARGET_MONTH = datetime.datetime.strftime(temp_target_month, "%Y%m")
    else:
        TARGET_MONTH = args[1]


    # MariaDB connect
    try:
        conn = mysql.connector.connect(user=DB_USER, password=DB_PASSWORD, host=DB_HOST, database=DB_DATABASE)
        cursor = conn.cursor()

        # 「nikkei225_topix_stock_prices」テーブルから対象年月の期首日と期末日を取得する
        cursor.execute('''
                SELECT MIN(dt), MAX(dt)
                FROM {DB_NAME}.{TABLE_NAME}
                WHERE dt LIKE \'{start_date}-{end_date}-%\'
            '''.format(
            DB_NAME=DB_DATABASE,
            TABLE_NAME=INPUT_TABLE_NIKKEI225,
            start_date=TARGET_MONTH[:4],
            end_date=TARGET_MONTH[4:])
        )
        opening_and_end_day = cursor.fetchall()
        opening_day = opening_and_end_day[0][0]
        end_day = opening_and_end_day[0][1]

        # 「japan_all_stock_prices」テーブルから各株式の期首日と期末日から月次平均収益率を算出する
        # 終値がNULLのものは出来高0のため。出来高が少ないものは投資対象とはならないためここでは単純に除外している。
        cursor.execute('''
                SELECT
                    a.security_code AS security_code,
                    a.company_name AS company_name,
                    '{month}' AS target_month,
                    a.closing_price AS start_closing_price,
                    b.closing_price AS end_closing_price,
                    ROUND((b.closing_price - a.closing_price) / a.closing_price, 4) AS monthly_average_rate_of_return
                FROM
                    {DB_NAME}.{TABLE_NAME} a
                INNER JOIN
                    {DB_NAME}.{TABLE_NAME} b
                ON
                    a.security_code = b.security_code
                AND
                    a.company_name = b.company_name
                WHERE
                    a.dt = '{start_date}'
                AND
                    b.dt = '{end_date}'
                AND
                    (a.closing_price IS NOT NULL AND b.closing_price IS NOT NULL)
        '''.format(
            month=TARGET_MONTH,
            DB_NAME=DB_DATABASE,
            TABLE_NAME=INPUT_TABLE_ALL_STOCK_PRICES,
            start_date=opening_day,
            end_date=end_day)
        )
        monthly_average_rate_of_return_result =  cursor.fetchall()

        # 「monthly_average_rate_of_return」テーブルにINSERTする
        # 全件Bulk InsertするとINSERT文の長さ制限に掛かるので100件ずつINSERTする
        continue_flag = True
        while(continue_flag):
            insert_query = '''INSERT INTO {DB_NAME}.{TABLE_NAME} 
                (security_code, company_name, target_month, start_closing_price, end_closing_price, monthly_average_rate_of_return)
                VALUES '''.format(
                DB_NAME=DB_DATABASE,
                TABLE_NAME=OUTPUT_TABLE
            )
            target_record_count = len(monthly_average_rate_of_return_result)
            if target_record_count < 100:
                record_count = target_record_count
                continue_flag = False
            else:
                record_count = 100

            for _ in range(record_count):
                record = monthly_average_rate_of_return_result.pop()
                insert_block = "({SC}, '{company_name}', '{month}', {start_price}, {end_price}, {value}),".format(
                    SC=record[0],
                    company_name=record[1],
                    month=record[2],
                    start_price=record[3],
                    end_price=record[4],
                    value=record[5]
                )
                insert_query += insert_block
            insert_query = insert_query[:-1] + ';'
            cursor.execute(insert_query)
            conn.commit()
    except mysql.connector.Error as e:
        log.error(e)
        slack.notify(text="【ERROR】統計処理 月次平均収益率 算出:異常終了")
        conn.close()
        sys.exit(1)

    log.info('統計処理 月次平均収益率 算出 : 終了')
    slack.notify(text="月次平均収益率 計算処理正常終了")
    conn.close()

処理は大きく分けて3つのステップを踏んでいます。

1. 期首日と期末日を取得する

# 「nikkei225_topix_stock_prices」テーブルから対象年月の期首日と期末日を取得する
cursor.execute('''
        SELECT MIN(dt), MAX(dt)
        FROM {DB_NAME}.{TABLE_NAME}
        WHERE dt LIKE \'{start_date}-{end_date}-%\'
    '''.format(
    DB_NAME=DB_DATABASE,
    TABLE_NAME=INPUT_TABLE_NIKKEI225,
    start_date=TARGET_MONTH[:4],
    end_date=TARGET_MONTH[4:])
)
opening_and_end_day = cursor.fetchall()
opening_day = opening_and_end_day[0][0]
end_day = opening_and_end_day[0][1]

この部分が該当します。「opening_day」に期首日が、「end_day」に期末日が入ります。

日経225のテーブルから期首日と期末日を取得しています。本当は各株式から算出するべきかも知れませんが、DBへの負荷が多くなりそうだったのと、対象の日付を統一した方が後々整合性がとれそうな感じがしたので簡易な方法で取得することにしました。

2. 各株式の月次平均収益率を算出する

# 「japan_all_stock_prices」テーブルから各株式の期首日と期末日から月次平均収益率を算出する
# 終値がNULLのものは出来高0のため。出来高が少ないものは投資対象とはならないためここでは単純に除外している。
cursor.execute('''
        SELECT
            a.security_code AS security_code,
            a.company_name AS company_name,
            '{month}' AS target_month,
            a.closing_price AS start_closing_price,
            b.closing_price AS end_closing_price,
            ROUND((b.closing_price - a.closing_price) / a.closing_price, 4) AS monthly_average_rate_of_return
        FROM
            {DB_NAME}.{TABLE_NAME} a
        INNER JOIN
            {DB_NAME}.{TABLE_NAME} b
        ON
            a.security_code = b.security_code
        AND
            a.company_name = b.company_name
        WHERE
            a.dt = '{start_date}'
        AND
            b.dt = '{end_date}'
        AND
            (a.closing_price IS NOT NULL AND b.closing_price IS NOT NULL)
'''.format(
    month=TARGET_MONTH,
    DB_NAME=DB_DATABASE,
    TABLE_NAME=INPUT_TABLE_ALL_STOCK_PRICES,
    start_date=opening_day,
    end_date=end_day)
)
monthly_average_rate_of_return_result =  cursor.fetchall()

この部分が該当します。SQLの結果が「monthly_average_rate_of_return_result」変数に次のような形式で格納されます。

[
    (証券コード1, 会社名1, 対象年月, 期首日, 期末日, 月次平均収益率),
    (証券コード2, 会社名2, 対象年月, 期首日, 期末日, 月次平均収益率),
    .
    .
    .
    (証券コードn, 会社名n, 対象年月, 期首日, 期末日, 月次平均収益率)
]

タプル型の各会社のレコードを格納しているリストが返却されます。

3. テーブルにINSERTする

# 「monthly_average_rate_of_return」テーブルにINSERTする
# 全件Bulk InsertするとINSERT文の長さ制限に掛かるので100件ずつINSERTする
continue_flag = True
while(continue_flag):
    insert_query = '''INSERT INTO {DB_NAME}.{TABLE_NAME} 
        (security_code, company_name, target_month, start_closing_price, end_closing_price, monthly_average_rate_of_return)
        VALUES '''.format(
        DB_NAME=DB_DATABASE,
        TABLE_NAME=OUTPUT_TABLE
    )
    target_record_count = len(monthly_average_rate_of_return_result)
    if target_record_count < 100:
        record_count = target_record_count
        continue_flag = False
    else:
        record_count = 100

    for _ in range(record_count):
        record = monthly_average_rate_of_return_result.pop()
        insert_block = "({SC}, '{company_name}', '{month}', {start_price}, {end_price}, {value}),".format(
            SC=record[0],
            company_name=record[1],
            month=record[2],
            start_price=record[3],
            end_price=record[4],
            value=record[5]
        )
        insert_query += insert_block
    insert_query = insert_query[:-1] + ';'
    cursor.execute(insert_query)
    conn.commit()

最初はBulc INSERTで全件一度にINSERTするクエリを作成するように実装しましたが、MySQLに送信できる文字数に制限があり、それに引っ掛かる恐れがあったので100件ずつに分けてBulc INSERTするようにしました。フラグでwhile文の管理をするのが素人臭くて色々方法を考えましたが思いつかなかったのでとりあえずこの形にしました。折をみてリファクタリングをしたいと思います。

まとめ

今回は取得したデータを利用して統計指標を初めて作成することができました。月次平均収益率は計算式が簡単なわりに投資の指標として重要なボラティリティが測れる重要な指標だと思います。月次平均収益率の時系列データを利用した機械学習や、業界・業種毎の平均収益率を比較してみるのも面白いかもしれません。