トップ > CCI > 簡単！Googleスプレッドシートで株価管理

簡単！Googleスプレッドシートで株価管理

CCI Google Google Spread Sheet Gsuite

はじめに

Googleスプレッドシートを利用した株価管理について紹介していきたいと思います。
株価データ管理は難しそうなイメージがあると思いますが、理解してしまえば他のデータ管理でも役立つと思います。

今回はすべてGoogleスプレッドシートの中で完結できるようにしました。

目指す株価データ管理のイメージ

自分の保有している資産を視覚的に把握できるようにします。
「Finviz」のS&P500のMAPをモデルにしたいと思います。

「Finviz」は米国株の企業分析・スクリーニングサイトです。
「Finviz」のS&P500のMAPは以下のように表示され、視覚的に米国株式市場の動向を把握できるツールとして投資家に好んで活用されています。

出典元：https://finviz.com/map.ashx

MAPの特徴は以下の通りになります。

  • 時価総額の大きさに応じてセクター（株の分類グループ）、銘柄のMAPに占めるブロック面積が大きくなる。
  • 業界ごとの株価の値動きの推移を色で視覚的に表示する。

すなわち、階層データを表すツリーマップと数字データの推移を色で表すヒートマップの二つの要素を兼ね備えたMAPになっています。

「Finviz」に倣って、自分の保有している資産のポートフォリオ（保有割合）と株価の動きを把握できるMAPを作成していきます。

作成のステップ

以下のステップを踏んでMAPを作成していきます。

  1. 株価データ表を作成
  2. 株価データ表からツリーマップのインプットデータを作成
  3. ツリーマップを作成（色でヒートマップの要素を付与）

株価データ表の項目

MAPのインプットデータとなる株価データ表をスプレッドシートで作成します。
株価データ表では以下の項目を管理します。

  • 銘柄名
  • コード
  • セクター（株の分類グループ）
  • 保有株数
  • 前日との時価差分比
  • 株価（リアルタイム）
  • 株価（前日の終値）

株価データについては、日々取得するのは手間なので、Googleスプレッドの関数を利用して自動で取得するようにします。

GoogleFinance関数で株価データを取得

Google関数の中にGoogelFinance関数という関数があります。
この関数では、「米国株価」のデータの取得ができます。

【GoogleFinance関数の構文】
　GOOGLEFINANCE(銘柄, [属性], [開始日], [終了日|日数], [間隔])

例えば、「APPLE」の株価のデータを取得したい場合には以下の通り記述します。
　GOOGLEFINANCE("APPL","price")

　また、以下のように引数を設定することで、指定した期間の株価のデータ取得も可能です。

例えば、今日の10日前から今日までの「APPLE」の株価データを取得したい場合には以下の通り記述します。

　GOOGLEFINANCE("APPL","price",TODAY()-10,TODAY(),DAILY)

また、属性の指定の仕方を変えることで、取得できるデータが変わります。
指定できる属性と取得できるデータは例として以下のものがあります。

属性取得できるデータ
priceopen 当日の始値
high当日の高値
low当日の安値
volume 当日の取引量
marketcap 株式の時価総額
change 前取引日の終値からの株価の変動
changepct  前取引日の終値からの株価の変動率
closeyest前日の終値
volumeavg 1 日の平均取引量


詳細はGoogleFinance関数のヘルプを参照してください。
参照：GoogleFinance関数のヘルプ

※日本株価のデータ取得は？
GoogleFinance関数では日本株の個別銘柄への対応はしていないためデータの取得ができません。
もし、Googleスプレッドシートで自動で日本株でデータ管理を行いたい場合には、「IMPOTRTHTML関数」というGoogle関数を利用してWEBサイトの株価データをデータスクレイピングするという方法もあります。
ただし、データスクレイピングすることを許可していないWEBサイトもあるため利用には注意が必要です。
データスクレイピングをする場合には、事前にサイトの利用規約でデータスクレイピングをしても問題ないかを確認した上で、データの取得を実行してください。

株価データ表を作成

GoogleスプレッドシートでGoogleFinance関数を実行して株価データを取得します。

スプレッドシートの各項目にそれぞれ以下の関数をセットしていきます。

【Google関数をセットする項目とセットする関数】
　・前日との差分比　GOOGLEFINANCE（B2,"change"）
　・株価（リアルタイム）GOOGLEFINANCE(B2,"price")
　・株価（前日終値）GOOGLEFINANCE(B2,"closeyest")

これで株価データ表を作成できます。

ツリーマップの階層データを作成

株価データ表からツリーマップ用のデータ表を作成します。
ツリーマップの構成する要素は全部で4つです。
Googleのツリーマップグラフの説明を引用すると以下になります。

・1 列目: 階層内のオブジェクトの名前を入力します。
・2 列目: オブジェクトの親の名前を入力します。親名は 1 列目にも入力する必要があります。
・3 列目: オブジェクトごとに正の数値を入力します。この値により各ボックスのサイズが決まります。親名の合計は 3 列目にも入力する必要があります。
・4 列目（省略可）: ボックスの色を表す正の数値を入力します。

引用元：Googleのツリーマップグラフの説明

すこしわかりにくいと思いますので、実際に作成するデータとあわせてみていきます。
作成するツリーマップの階層構造はポートフォリオ、セクター、銘柄の3階層になっています。
1列目、2列目のデータで階層構造を設定します。
3列目には、ツリーマップのボックス面積になる時価総額の値を設定します。
4列目には、時価の推移を色で表すため差分比の値を設定します。階層構造を表した状態のデータが以下になります。

この場合、「セクター」の親となる階層が「ポートフォリオ」ということになります。「銘柄」の親となる「階層」がセクターとなります。
このような3つの階層データを作成します。

では、階層ごとに順を追ってデータを作成していきます。
まず、第1階層になるポートフォリオの階層のデータを表に記載します。
1列目に階層の名前、3列目にツリーマップの全体の面積を表すため時価総額合計を設定します。

次に、第2階層になるセクターの階層データを表に記載します。
1列目にセクター情報を記載します。
株価データでセクター毎にまとまっていない場合には、UNIQUE関数を利用してセクター情報の抽出をすると簡単です。
UNIQUE関数は指定したデータの範囲内から重複したデータを削除して抽出することができます。

【UNIQUE関数の構文】
　UNIQUE(データ範囲指定)

2列目に親階層になる「ポートフォリオ」を記載します。
3列目には、ツリーマップのセクターのボックス面積を表すために、セクター毎の時価総額を設定します。銘柄のデータを設定した後に設定します。

続いて第3階層になる銘柄の階層データを表に記載します。
1列目に銘柄情報、2列目にセクター情報、3列目に銘柄毎の時価総額、4列目に銘柄毎の前日との差分比を設定します。
株価データ表のデータをQUERY関数を利用して抽出します。
QUERY関数は、「データ」に「条件（クエリ）」を指定して、目的のデータを抽出する関数です。元のデータに変更が発生しても自動で結果が反映されます。QUERYのデータ範囲には、株価データ表の銘柄、セクター、時価総額、前日との差分比を、クエリには指定した範囲のデータを抽出するように指定します。

【QUERY関数の構文】
QUERY(データ範囲指定,クエリ,見出し)

最後に一度置いておいたセクター階層のデータの3列目、セクター毎の時価総額をSUMIF関数を利用して設定します。SUMIF関数は、指定した条件に一致する合計を算出できます。範囲には先ほどQUERYで抽出した銘柄毎のデータ、条件にはセクター、合計範囲を銘柄データの時価総額を指定します。

【SUMIF関数の構文】
SUMIF（範囲,条件,合計範囲）

設定し終わると以下のようになります。

これで、ツリーマップのインプットデータができます。

※QUERY関数単独でのデータ作成方法
ヒートマップ用データの作成ですが、二つの表を作成・結合して準備する方法もあります。
結合させる表は、株価データ表のデータをもとにセクター単位に階層、時価総額を管理した表と銘柄単位に階層、時価総額、差分比を管理した表になります。

データの結合にQUERY関数を利用します。
【データを縦に結合する際のQUERY関数の構文】
　QUERY({データA;データB})

QUERY関数でのデータ結合の場合には、結合するデータの範囲指定をセル指定でなく、列指定にすることで元の株価データ表へのデータ追加にあわせてヒートマップ用データに追加データが自動的に反映されるという利点があります。

元となる株価データ表にヒートマップ作成用に、I列に階層データ「ポートフォリオ」を追加して、以下のQUERY関数の実行をして株価データ表からヒートマップ用のデータを作成します。

QUERYのデータ内容は以下の通りになります。

【構文のデータAにあたるQUERY内容】
セクター、追加した階層データ（ポートフォリオ）、セクターの時価総額合計、列数合わせ用のデータ（データ結合には必ず列の数をそろえる必要があるため）

【構文のデータBにあたるQUERY内容】
個別銘柄の階層データになるセクター、時価総額、前日との差分比

ツリーマップを作成する

作成したデータをもとにツリーマップを作成します。
まず、ツリーマップに利用するデータを範囲選択し、Googleスプレッドシートのメニューの「挿入」→「グラフ」を選択します。

続いてグラフエディタの「設定」の「グラフの種類」からツリーマップを選択します。

ツリーマップを選択をするとデータからツリーマップが生成されます。

ツリーマップのインプットデータの4列目の値の大きさによって各ボックス内の色も変わるようになっており、色の変化で時価の推移を確認することができ、ヒートマップの要素も付与されています。
また、グラフエディタの「カスタマイズ」でご自身の好みに色の設定ができます。

これで自分の保有している資産のポートフォリオ（保有割合）と株価の動きを把握できるMAPの完成です。

ふりかえって

調べてみると様々なアイディアを詰め込んでデータ管理をしていることを知ることができたので、Googleスプレッドシートで実現できることへの可能性を感じることができました。

ツリーマップの作成自体は関数とデータの準備の仕方が理解ができれば難しくありませんでした。
ただ、Googleスプレッドシートで用意されたグラフにあわせる都合、ツリーマップのインプットデータの作成にすこし不自由を感じたので今後改良をしていきたいと思いました。

また、BIツールと連携できれば、より面白いデータ活用できそうと思いましたので、次回はBIツールの利用も考えていきたいです。