企業の財務情報をパース

1
2
3
4
5
6
7

import matplotlib.pyplot as plt
import numpy as np
import os
import pandas as pd
import re
import seaborn as sns
from IPython.core.display import display

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155

# 英日辞書
en_US_ja_JP_table = {
    "Revenue": "売上",
    "Cost of revenue": "収益コスト",
    "Gross profit": "粗利益",
    "Sales, General and administrative": "販売費及び一般管理費",
    "Other operating expenses": "その他営業費用",
    "Total operating expenses": "営業費用",
    "Operating income": "営業利益",
    "Interest Expense": "支払利息",
    "Other income (expense)": "Other income (expense)",
    "Income before taxes": "Income before taxes",
    "Provision for income taxes": "Provision for income taxes",
    "Net income from continuing operations": "Net income from continuing operations",
    "Net income available to common shareholders": "普通株式に係る当期純利益",
    "Net income": "当期純利益",
    "Basic": "Basic",
    "Diluted": "Diluted",
    "EBITDA": "EBITDA",
    "Revenue ": "売上",
    "Gross Margin %": "	売上総利益率",
    "Operating Income ": "営業利益",
    "Operating Margin %": "営業利益率",
    "Net Income ": "純利益",
    "Earnings Per Share USD": "EPS (USD)",
    "Dividends USD": "配当 (USD)",
    "Payout Ratio % *": "配当性向",
    "Shares Mil": "株数 (Mil)",
    "Book Value Per Share * USD": "1株あたり純資産",
    "Operating Cash Flow ": "営業キャッシュフロー",
    "Cap Spending ": "資本的支出",
    "Free Cash Flow ": "フリーキャッシュフロー（FCF）",
    "Free Cash Flow Per Share * USD": "1株あたりFCF",
    "Working Capital ": "運転資本",
    "Key Ratios -> Profitability": "",
    "Margins % of Sales": "Margins % of Sales",
    "COGS": "売上原価",
    "Gross Margin": "売上高総利益率",
    "SG&A": "販売費及び一般管理費",
    "R&D": "研究開発費",
    "Operating Margin": "営業利益率",
    "Net Int Inc & Other": "資金運用利益+その他",
    "EBT Margin": "EBTマージン",
    "Debt/Equity": "負債比率",
    "Receivables Turnover": "売上債権回転率",
    "Inventory Turnover": "棚卸資産回転率",
    "Fixed Assets Turnover": "固定資産回転率",
    "Asset Turnover": "総資産回転率",
    "USD Mil": "(USD Mil)",
    "-Year Average": "年平均",
}

def get_preprocessed_df(
    filepath,
    is_transpose=True,
    drop_ttm_lastqrt=True,
    ttm_datetime="2021-12",
    lastqtr_datetime="2021-12",
    add_ticker_column=True,
):
    """Morningstar, Incのデータを整形する

    Args:
        filepath (str): ファイルパス
        is_transpose (bool, optional): タイムスタンプ列を縦にするかどうか. Defaults to True.
        drop_ttm_lastqrt (bool, optional): TTM/Last Qrtの記録は削除する. Defaults to True.
        ttm_datetime (str, optional): 「TTM」をどの日付に置き換えるか. Defaults to "2021-12".
        lastqtr_datetime (str, optional): 「Latest Qtr」をどの日付に置き換えるか. Defaults to "2021-12".
        add_ticker_column (bool, optional): ticker symbolを示した列を追加するか. Defaults to True.

    Returns:
        DataFrame: 整形済みデータフレーム
    """
    df_preprocessed = []
    df_header = []
    row_header = ""
    df = pd.read_table(filepath, header=None)

    if not drop_ttm_lastqrt:
        print(f"[Note] TTM は {ttm_datetime} 、Last Qtrは {lastqtr_datetime} の日付として扱われます。")

    for idx, row in enumerate(df[0]):
        # 数値中の「,」を置換する
        row = re.sub('"(-?[0-9]+),', '"\\1', row)
        row = re.sub(',(-?[0-9]+)",', '\\1",', row)

        # 英語を対応する日本語に置き換える
        for str_en, str_jp in en_US_ja_JP_table.items():
            if str_en in row:
                row = row.replace(str_en, str_jp)

        # TTMがある行はタイムスタンプなのでヘッダー扱いにする
        if "TTM" in row or "Latest Qtr" in row:
            if drop_ttm_lastqrt:
                row = row.replace("TTM", "###IGNORE###")
                row = row.replace("Latest Qtr", "###IGNORE###")
            else:
                assert ttm_datetime not in row, "その日付はすでに存在しています！"
                assert lastqtr_datetime not in row, "その日付はすでに存在しています！"
                row = row.replace("TTM", ttm_datetime)
                row = row.replace("Latest Qtr", lastqtr_datetime)
            df_header = row.split(",")

            if is_transpose:
                df_header[0] = "月"
            else:
                df_header = ["月"] + df_header
            continue

        # 数値に変換できるデータは数値に変換してDataFrameに追加
        if len(row_splited := row.split(",")) > 1:
            row_data = [
                float(v) if re.match(r"^-?\d+(?:\.\d+)$", v) is not None else v
                for v in row_splited
            ]

            if is_transpose:
                row_data[0] = (
                    f"{row_header}/{row_data[0]}" if row_header else f"{row_data[0]}"
                )
            else:
                row_data = [row_header] + row_data
            df_preprocessed.append(row_data)
        else:
            # 先頭の行はファイルのタイトルが入っているので無視
            row_header = f"{row}" if idx > 0 else ""

    # データフレーム作成
    df_preprocessed = pd.DataFrame(df_preprocessed)
    df_preprocessed.columns = df_header
    if drop_ttm_lastqrt:
        df_preprocessed.drop("###IGNORE###", axis=1, inplace=True)

    # 不要な文字列を削除
    df_preprocessed.fillna(np.nan, inplace=True)

    if is_transpose:
        df_preprocessed = df_preprocessed.T.reset_index()
        df_preprocessed.columns = df_preprocessed.iloc[0, :]
        df_preprocessed.drop(0, inplace=True)
        df_preprocessed["月"] = pd.to_datetime(df_preprocessed["月"])
        for colname in df_preprocessed.columns:
            if colname != "月":
                df_preprocessed[colname] = pd.to_numeric(
                    df_preprocessed[colname], errors="coerce"
                )

    if add_ticker_column:
        filename = os.path.basename(filepath)
        ticker_symbol = filename[: filename.index(" ")]
        df_preprocessed["ticker"] = [
            ticker_symbol for _ in range(df_preprocessed.shape[0])
        ]

    return df_preprocessed

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

df_is_gogl = get_preprocessed_df("data/GOGL Income Statement.csv")
df_kr_gogl = get_preprocessed_df("data/GOGL Key Ratios.csv")
df_is_zim = get_preprocessed_df("data/ZIM Income Statement.csv")
df_kr_zim = get_preprocessed_df("data/ZIM Key Ratios.csv")
df_is_sblk = get_preprocessed_df("data/SBLK Income Statement.csv")
df_kr_sblk = get_preprocessed_df("data/SBLK Key Ratios.csv")

df_income_statement = pd.concat([df_is_gogl, df_is_sblk, df_is_zim])
df_key_ratio = pd.concat([df_kr_gogl, df_kr_zim, df_kr_sblk])

display(df_income_statement.head())
display(df_key_ratio.head())

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

def show_barchart(data, x="月", y="売上", hue="ticker", is_log_scale=False):
    """バーチャートを表示する

    Args:
        data (pandas.DataFrame): データフレーム
        x (str, optional): 時間軸. Defaults to "月".
        y (str, optional): 比較する指標. Defaults to "売上".
        hue (str, optional): 何基準で比較するか. Defaults to "ticker".
        is_log_scale (bool, optional): logスケールで表示するかどうか. Defaults to False.
    """
    sns.set_theme(style="whitegrid", rc={"figure.figsize": (10, 4)})

    g = sns.barplot(data=data, x=x, y=y, hue=hue)
    g.set_xticklabels(
        [xt.get_text().split("-01")[0] for xt in g.get_xticklabels()]
    )  # TODO: mdates.DateFormatterで日付を表示するとなぜか日付がずれるのでラベルを直接書き換える
    g.tick_params(axis="x", rotation=90)
    if is_log_scale:
        g.set_yscale("log")
    plt.legend(loc="upper left", title="Ticker Name")
    plt.title(f"{y}の比較", fontsize=14)
    plt.show()

1
2

show_barchart(df_income_statement, x="月", y="売上", hue="ticker")
show_barchart(df_income_statement, x="月", y="Operating expenses/当期純利益", hue="ticker")