https://www.buffett-code.com/ Ruby on Railsで作る 銘柄スクリーニング {“name”: “shoe116”, “company”: “バフェットコード”, “date”:”2024-12-13”}

今日話すこと 1. バフェットコードと銘柄条件検索（スクリーニング） 2. スクリーニングを実装するときに問題になること a. データモデリングの話 b. パフォーマンスの話 3. Scenicで”Screening Model”を作る 4. まとめ

whoami: Shu Suzuki(@shoe116) - お仕事 - Yahoo! Japanの広告システム -> データ基盤 - Mercari/Merpayでデータ基盤 - Buffett Code共同創業 - （さくらインターネットの監視プラットフォームのお手伝い） - 好き - ロック、アイドル、アイリッシュ音楽

バフェットコードについて 企業情報を網羅している - 日本上場・未上場企業 - 米国の上場企業 - 個社別の詳細なページ 便利な企業横断の分析機能 - 条件検索（スクリーニング） - 企業比較（comps） - 資料検索 https://www.buffett-code.com/

バフェットコードについて 企業情報を網羅している - 日本上場・未上場企業 - 米国の上場企業 - 個社別の詳細なページ 便利な企業横断の分析機能 - 条件検索（スクリーニング） - 企業比較（comps） - 資料検索 https://www.buffett-code.com/

条件検索（スクリーニング） よさげな株の銘柄を探す - 一言でいうとクエリビルダー - 各種条件を & で match - 財務数値 - 株価指標 - 市場・業種 - その他重要指標 https://www.buffett-code.com/global_screening

スクリーニング対象のデータ 色々あるが、今日主に触れるのは以下の3つ - 企業の業績を表す「財務数値」 - 投資家からの評価を表す「株価指標」 - 銘柄の所属する「市場・業種」 それぞれデータの取得元と更新頻度が異なり、別々のtable に保存されている。

スクリーニングはめんどくさい：データの特性(1) 財務数値 - 銘柄コード(ticker) と 決算四半期でunique - 基本的に四半期決算（3ヶ月）ごとに更新、だが年に1回本決 算しか更新されない値もある - 売上や利益、資産や負債など「決算」にまつわる数値が該当 し、企業の収益性・健全性・成長性がわかる - スクリーニングの対象は「直近四半期」or「直近本決算」

スクリーニングはめんどくさい：データの話（2） 株価指標 - ticker X 日付でunique - 毎日変わる株価、不定期に変わる株数に依存する - 時価総額や配当利回りが該当し、その銘柄が投資家からどの ように評価されているかがわかる - スクリーニングの対象は「直近営業日」

スクリーニングはめんどくさい：データの話（3） 市場・業種 - 期間の概念はなく、ticker uniqueなデータ - 「プライム」「スタンダード」などの4市場 - 「水産・農林業」「情報・通信業」など33業種 その他重要指標 - 従業員数や上場年数

スクリーニングはめんどくさい：データの話（4) かなり複雑なクエリをGUIの設定値から作ることになる - どの項目（column）がどのデータ（table）かをresolveする 必要がある - 財務数値・株価指標と言った時系列データについては銘柄ご とに「最新の」レコードを選んでJOINする必要がある

スクリーニングはめんどくさい：パフォーマンスの話 機能の特性上、宿命的な問題がある - すべてのcolumnが条件になりうる => 「適切なINDEX」はない - 時系列データの扱いが厄介 - 「銘柄毎の最新」を探しつつの複数tableのJOINは非常に 遅い - 毎日データが増える - 検索パターンが無数＆試行錯誤を前提とした機能なので、 cacheは不向き

Screening Modelを作る - 異なるデータ（table）を結合しての実装は厳しい - Rails/ActiveRecordの恩恵がほとんど受けられない - Webサービスで期待されるパフォーマンスは期待できない => Screening専用のデータセット（モデル）の整備

Screening Modelを作るメリット - 機能の複雑さ・パフォーマンスの懸念が一気に解決する - column => tableのresolveは不要になる - 「最新の」値だけ選んで連れてくればいい - 上場企業は高々4000、full scanで大丈夫 - Ransackを使える - Rails/ActiveRecordで自然な形で検索が実装できる - 自前で書くコードが大幅に削減できる

Screening Modelのデメリットと対応策 - 一言でいうと、データをダブって持つ（非正規化） - 非正規化には自明なデメリットがある - データの一貫性が失われる - 「スクリーニング用のデータ」を作成・更新する処理が必 要になる - Scenicを使って、Materialized Viewでmodelを定義する - Viewを使うことで、一貫性を保って別データを定義 - Materialized Viewの更新は日次で十分

Screening Modelの作り方 1. Scenicを使って、マイグレーションファイルを作る rails generate scenic:view search_results 2. WITH句を駆使して、ticker unique なviewを定義（後 述） 3. マイグレーションの実行

WITH latest_quarterly_financials AS ( SELECT a.ticker, a.fiscal_year, a.fiscal_quarter, a.net_sales,... FROM quarterly_financials AS a JOIN ( SELECT ticker, MAX(fiscal_year) AS latest_fiscal_year, MAX(fiscal_quarter) AS latest_fiscal_quarter FROM quarterly_financials GROUP BY ticker ) AS latest ON a.ticker = latest.ticker AND a.fiscal_year = latest.latest_fiscal_year AND a.fiscal_quarter = latest.latest_fiscal_quarter ), 直近四半期の値とJOINして 最新値を持つレコードを特定

latest_daily_indicator AS ( SELECT a.ticker, a.date, a.stock_price, … FROM daily_indicator AS a JOIN ( SELECT ticker, MAX(date) AS latest_date FROM daily_indicator GROUP BY ticker ) AS latest ON b.ticker = latest.ticker AND a.date = latest.latest_date ) 最新日付の値とJOINして 最新値を持つレコードを特定

SELECT lqf.ticker, lqf.fiscal_year, lqf.fiscal_quarter, lqf.net_sales, … lqi.date, lqi.stock_price, … m.market_code,... FROM latest_quarterly_financials lqf LEFT JOIN latest_daily_indicator ldi ON lqf.ticker = lsp.ticker LEFT JOIN listing_markets m ON lqf.ticker = listing_markets.ticker … 必要なデータをJOINしていく - 直近四半期の財務数値 - 最新日付の株価指標 - 市場の情報

今日のまとめ 1. バフェットコードの銘柄条件検索（スクリーニング）で できること 2. スクリーニングを実装する難しさと工夫 a. Scenicを使ってMaterialized viewでScreening Modelを定義 b. Screening Model + ransackでスクリーニングを実装

今日話さなかったこと 1. 各種データどうやって作ってんの？ ものすごく大変な思いをして作っています XBRLとかデータパイプラインの話は鮨食べながらしましょう 2. バフェットコードって採用してるの？ 絶賛採用活動中です→ https://career.buffett-code.com/