ElasticsearchのAggregationsを使ってXBRLで提供されている企業情報を集計する

はじめに

これは Elasticsearch Advent Calendar 2014 17日目の記事です。

今回は株式会社ユーザベースの提供する SPEEDA で扱っている企業の財務データの中から、

• 2014年12月13日時点で 公開 されている情報
• 日本の上場している企業 (投信やREITを除く、東証、名証、福証、札証)
• XBRLで公開されているデータ (3549社)
• 通期かつ12ヶ月の決算
• 連結決算が有る場合は連結、無い場合は単体の財務データ
• 東証33業種分類を使用
• 注意: 全ての上場企業がXBRLを公開しているわけではない
• 注意: 連結の財務データがあるのにXBRLでは単体の財務データしか公開していない会社がある
• 注意: 集計している2013年度はまだ決算発表がされていない企業がある
• 重要: 下記の結果等については一切の保証をいたしません

を使用してElasticsearchの集計機能で遊んでみます。

• XBRLとは

株式会社ユーザベースの提供する SPEEDA では、XBRLで公開されていないデータも含め、全世界の 企業(約100万社)・業界(約550業種)・統計(約20万項目)・M&A情報(約100万件) のデータを取り扱っています。

データについて

今回は以下のような構造で Elasticsearch にデータを投入してみました。企業をベースドキュメントとして、 nested type として年度毎の財務データを持つようにしています。

XBRLからの取り込みについては、とても長くなるので解説しません。arelleとかあるので頑張ってください。

マッピング(抜粋)

{
  "xbrl" : {
    "mappings" : {
      "historical" : {
        "properties" : {
          "company_name" : {
            "type" : "string",
            "index" : "not_analyzed"
          },
          "industry_name" : {
            "type" : "string",
            "index" : "not_analyzed"
          },
          "finance" : {
            "type" : "nested",
            "include_in_root" : true,
            "properties" : {
              "latest" : {
                "type" : "boolean"
              },
              "year" : {
                "type" : "integer"
              },
              "assets" : {
                "type" : "double"
              },
              "cash_and_deposits" : {
                "type" : "double"
              },
              ... 以下財務科目が続く ...
            }
          }
        }
      }
    }
  }
}

とりあえず投入してみたXBRLの財務科目

売上高: NetSales
営業利益: OperatingIncome
経常利益: OrdinaryIncome
税引前純利益: IncomeBeforeIncomeTaxes
純利益: NetIncome

資産: Assets
流動資産: CurrentAssets
現預金: CashAndDeposits
固定資産: NoncurrentAssets

負債: Liabilities
流動負債: CurrentLiabilities
固定負債: NoncurrentLiabilities

純資産: NetAssets
株主資本: ShareholdersEquity

Elasticsearchの集計機能

Elasticsearch には aggregations とよばれる、集計機能があります。 Elasticsearch のドキュメントの aggregationのページ にとても詳しく書いてあるので一通り読んでもらうのが一番良いですが、

データをフィルタリングした後、

• 数値フィールドの最小値・最大値・合計・平均値・分散・標準偏差の算出
• パーセンタイル
• カーディナリティ(データ種類数)
• 地理計算
• 出現頻度
• 範囲毎の集計
• ヒストグラム

等なんかいろいろできます。とりあえず、ドキュメントをざっと眺めるのが良いです。

Elasticsearchの集計機能は、 データの持たせ方を工夫するとむちゃくちゃ速い です。ぜひ触ってみてください。

とりあえずやってみる: 33業種それぞれに属する企業の数を数える

特定のフィールドを内容毎にドキュメント数を出したいときには、terms aggregation を使います。

今回の場合、業種名がindustry_nameというフィールドに入っているのでこれで集計してやります。今回は説明のため、名称をそのまま入れてしまっていますが、コード値の方が扱いやすいことも多いかもしれません。

terms aggregation を使用するときに気をつけないといけないのは、あくまで term を集計するものだという点です。Elasticsearchでは、与えられた文字列を analyzer を通して、ホワイトスペースで分割したり、形態素解析して分割したりして、データを term という単位で格納します。今回のように名前とかを集計する場合には、mapping の段階で analyzer を通さないように("index" : "not_analyzed")しておかなければおかしな結果になります。

では、実際に使ってみます。クエリとしては下記の通りです。33業種なのでsizeを33として指定しています。

{
  "size": 0,
  "_source": false,
  "aggregations": {
    "industry": {
      "terms": {
        "field": "industry_name",
        "size": 33
      }
    }
  }
}

結果

{
  "took": 6,
  "timed_out": false,
  "_shards": {
    "total": 1,
    "successful": 1,
    "failed": 0
  },
  "hits": {
    "total": 3549,
    "max_score": 0,
    "hits": []
  },
  "aggregations": {
    "industry": {
      "doc_count_error_upper_bound": 0,
      "sum_other_doc_count": 0,
      "buckets": [
        {
          "key": "サービス業",
          "doc_count": 361
        },
        {
          "key": "情報・通信業",
          "doc_count": 357
        },
        ... 以下続く ...
        {
          "key": "鉱業",
          "doc_count": 7
        },
        {
          "key": "空運業",
          "doc_count": 6
        }
      ]
    }
  }
}

33業種の分類では(XBRLデータを提供している会社の中では)サービス業と情報・通信業が多いみたいですね。逆に鉱業と空運業が少ないです。何となくわかります。

2013年度の売上高が1兆円以上の企業数を33業種毎にカウントしてみる

次はフィルタリングした結果を集計してみましょう。2013年の売上高が1兆円以上のデータを集計してみます。

財務データは年度毎に nested type として格納されているので、 nested filter を使用してフィルタリングを行います。

ここで気をつけなければならないのは、filter でフィルタリングする場合は filtered query を使わなければいけない所です。Elasticseachの仕組みとして、query -> aggregate -> filter の順で処理されます。そのためフィルタリング結果の集計を行う場合は、query の中に filter を記述する必要があります。filtered query でも結果はキャッシュされるので条件の追加削除は高速に行われます。

{
  "size": 0,
  "_source": false,
  "query": {
    "filtered": {
      "filter": {
        "nested": {
          "path": "finance",
          "filter": {
            "bool": {
              "must": [
                {
                  "term": {
                    "finance.year": 2013
                  }
                },
                {
                  "range": {
                    "finance.net_sales": {
                      "from": 1.0e+12
                    }
                  }
                }
              ]
            }
          }
        }
      }
    }
  },
  "aggregations": {
    "industry": {
      "terms": {
        "field": "industry_name.raw",
        "size": 33
      }
    }
  }
}

結果

{
  "took": 4,
  "timed_out": false,
  "_shards": {
    "total": 1,
    "successful": 1,
    "failed": 0
  },
  "hits": {
    "total": 97,
    "max_score": 0,
    "hits": []
  },
  "aggregations": {
    "industry": {
      "doc_count_error_upper_bound": 0,
      "sum_other_doc_count": 0,
      "buckets": [
        {
          "key": "卸売業",
          "doc_count": 16
        },
        {
          "key": "輸送用機器",
          "doc_count": 16
        },
        ... 以下続く ...
      ]
    }
  }
}

卸売業と輸送用機器の数が多いみたいですね。卸売業は商社とか、輸送用機器は自動車メーカとかがふくまれる業種です。

ただ、実際に会社名とかを表示させてみたのですが、 三菱商事とか三井物産とかトヨタ自動車とかが単体決算しかXBRLで提供していないようで ちょっと実態と違う結果になっていました。当然ですが SPEEDA には、連結決算のデータも格納されています。XBRLのデータだけじゃちょっと無理みたいですね。

2013年度の株主資本や現預金の分布を見てみる

数値の分布を見る場合はヒストグラムを使います。Elasticsearchには histogram aggregation があるのでこれを使ってみます。

aggregationの場合も、nested typeを集計する場合は、nested aggregations を使用する必要があるので使います。その際にフィルタリングも行います。

企業の財務データみたいにそのまま分布を取ってしまうと
"0円〜1兆円、1兆円〜2兆円、2兆円〜3兆円..." みたいな形で分布が出てしまいよくわからないので、対数を取ります。桁数をみる意味でもこういう場合はlog10が良いですね。

Elasticsearchでは、集計の際にフィールドの代わりにscriptの実行結果を集計する事が出来ます。スクリプトはGroovyで書けちゃったりするので結構複雑な関数を呼び出したりも出来ます。
というわけで、script フィールドに log10(doc['finance.shareholders_equity'].value) みたいに書いてやります。その上で、histogram aggregationの interval に 1 を指定して数字の桁が1つ増える毎に数を取ってみます。

最終的には下記の様なクエリになりました。

{
  "size": 0,
  "_source": false,
  "aggregations": {
    "finance": {
      "nested": {
        "path": "finance"
      },
      "aggregations": {
        "finance": {
          "filter": {
            "term": {
              "finance.year": 2013
            }
          },
          "aggs": {
            "shareholders_equity": {
              "histogram": {
                "script": "log10(doc['finance.shareholders_equity'].value)",
                "interval": 1
              }
            },
            "cash_and_deposits": {
              "histogram": {
                "script": "log10(doc['finance.cash_and_deposits'].value)",
                "interval": 1
              }
            }
          }
        }
      }
    }
  }
}

結果

{
  "took": 9,
  "timed_out": false,
  "_shards": {
    "total": 1,
    "successful": 1,
    "failed": 0
  },
  "hits": {
    "total": 3549,
    "max_score": 0,
    "hits": []
  },
  "aggregations": {
    "finance": {
      "doc_count": 22840,
      "finance": {
        "doc_count": 3454,
        "cash_and_deposits": {
          "buckets": [
            {
              "key": 6,
              "doc_count": 3
            },
            {
              "key": 7,
              "doc_count": 26
            },
            {
              "key": 8,
              "doc_count": 449
            },
            {
              "key": 9,
              "doc_count": 1882
            },
            {
              "key": 10,
              "doc_count": 868
            },
            {
              "key": 11,
              "doc_count": 121
            },
            {
              "key": 12,
              "doc_count": 2
            }
          ]
        },
        "shareholders_equity": {
          "buckets": [
            {
              "key": 0,
              "doc_count": 11
            },
            {
              "key": 6,
              "doc_count": 1
            },
            {
              "key": 7,
              "doc_count": 11
            },
            {
              "key": 8,
              "doc_count": 137
            },
            {
              "key": 9,
              "doc_count": 1313
            },
            {
              "key": 10,
              "doc_count": 1429
            },
            {
              "key": 11,
              "doc_count": 506
            },
            {
              "key": 12,
              "doc_count": 42
            },
            {
              "key": 13,
              "doc_count": 1
            }
          ]
        }
      }
    }
  }
}

お金有るところはあるんだなぁ、という感じでうらやましいです。

まとめ

ElasticsearchのAggregationsを使っていろいろ集計してみました。ここでは、3549社のデータを使って集計してますが、100万社60年分のデータを使っていろいろ複雑なフィルタリングをした上で集計してもたいてい1秒以内にフィルタリング結果が返ってきます。ちょっぱやです。

Elasticsearchのクエリは複雑なように見えますが、構造化されているのでプログラムで扱う場合はとても綺麗にプログラムが書けます。これをSQLで頑張ることを考えると地獄です。

最後にですが、XBRLのデータはちゃんと使うにはまだ色々データが足りなくて使いにくいです。SPEEDA には全てのデータがそろっています。株式会社ユーザベースでは、データで遊んでみたいエンジニアを募集 ( Wantedly )しています。

Data Visualization Advent Calendar 2014 の22日にも登録しているので、この結果を使用してデータの可視化をしてみたいと思います。

明日の記事は @KunihikoKido さんです。