米国株のティッカーを入力するだけで銘柄情報を表示できるGoogle スプレッドシートのつくり方を紹介します。
当記事の手順を進めると、以下のような銘柄一覧表をつくることができます。
表示する項目
- 企業名
- 時価総額
- 発行済み株式数
- 株価
- 株価騰落率（前日、週間、月間、年初来、コロナショック）
- チャート（過去1年）
- 四半期売上 前年同期比
- PSR：時価総額 ÷ （直近四半期売上高 x 4）
誰でも簡単につくれるように難易度の低いやり方から段階的に紹介していきますので、是非挑戦してみてください。
手順1：Google Spreadsheetを用意
まず、Google Spreadsheetを用意してください。Google アカウントを作っていない方はまず登録が必要です。
Spreadsheetの作成をしたら、以下のようにセルを色づけしておきます。
深緑背景が項目名で黄色背景がティッカーを入力するセルです。
（色の設定は好みなので省略していただいて構いません）
手順2：企業名、時価総額、発行済み株式数、株価を取得
さっそく銘柄情報を取得していきましょう。まず、企業名、時価総額、発行済み株式数、株価を取得します。これらの情報はスプレッドシートで用意されている GOOGLEFINANCE関数を使用することで簡単に取得できます。
GOOGLEFINANCE(銘柄, [属性], [開始日], [終了日|日数], [間隔])
例：Appleの株価を取得する場合
入力例：=GOOGLEFINANCE("AAPL", "price")
表示結果：112.82
上記の ”price” の部分を、取得したい任意の属性に変更することで株価以外の情報も取得できます。どのような情報が取得できるかは、以下の説明ページから確認が可能です。（当記事はこちらを読まなくても進めらる構成となっています）
今回使用するのは株価の他に、企業名（”name”）、時価総額（”marketcap”）、発行済み株式数（”shares”）の3つです。
関数で使用する銘柄の部分は手入力したセルを参照することで毎回入力する手間を省けます。
実際にスプレッドシートへ入力すると、以下のように自動で各項目の値が表示されます。数値は時価なので変動します。
金額の書式が反映されない場合は、メニューから「その他の通貨」から米ドルを選択しましょう。
もし百万ドル単位で表示したい場合は、カスタム数値形式を選択し #,##.#0,,"M";-#,##.#0,,"M";0 と設定すると変更できます。
手順3：株価騰落率を取得
次に株価騰落率を取得します。前日株価騰落については先ほどの GOOGLEFINANCE関数を使用して取得できます。
=GOOGLEFINANCE("AAPL", "changepct")
しかし、このままだとパーセント表示にならないので ÷ 100 をして書式をパーセント表記にします。
=GOOGLEFINANCE("AAPL", "changepct") / 100
これで前日騰落率を表示することができました。
次に週間、月間、年初来、コロナショック時からの騰落率ですが、これらは過去の株価を取得して計算します。週間株価騰落を例に紹介します。
週間株価騰落の計算式は、現在株価 / 前週株価 - 1 で算出できます。
現時点では現在株価を取得できているので、過去の株価さえ取得できれば表示することができますね。
実は過去の株価もGOOGLEFINANCE関数で取得できます。”price”の次に取得したい日を設定することで当時の株価を取得できます。
=GOOGLEFINANCE("AAPL","price","2020/09/01")
これでGOOGLEFINANCE関数から過去の株価を取得できましたが、項目名や日付が邪魔です。そんな時はINDEX関数を使用します。この関数を使うと指定したセルを参照することができます。
=INDEX(GOOGLEFINANCE("AAPL","price","2020/09/01"),2,2)
「Date」が表示されているセルが関数を入力しているセルとなっているので、そこを起点として上から2行目、左から2列目を指定します。
これで過去の株価のみを取得できるようになりました。しかし、このままだと手動で日付を変えなければいけないのでもうひとつ手間を加えます。
=INDEX(GOOGLEFINANCE("AAPL","price",TODAY()-8),2,2)
日付を入力していた箇所を TODAY()-8 に変更しました。この TODAY() も関数のひとつで現在の日付を返してくれます。これに -8 すると8日前の日付を取得することができます。（数字は任意に変更できます）
これで先週の日付も自動で取得できたので週間株価騰落を算出できます。同じ要領で週間、月間、年初来、コロナショック（3/23）からの騰落率も取得できるので試してみてください。
過去の株価の列を非表示にすると表示がすっきりするのでお勧めです。
手順4：チャートを取得
次にチャートを表示してみましょう。ここでは SPARKLINE 関数を使用します。この関数はセルの中に折れ線、積み重ね棒、縦棒グラフを表示することができます。
若干複雑な関数なので、まず先にチャートを表示する例を紹介します。
=SPARKLINE(GOOGLEFINANCE("AAPL","price",TODAY()-365,TODAY(),"WEEKLY"),{"charttype","line";"linewidth",1;"color","#5f88cc"})
今回もまた GOOGLEFINANCE 関数が使われていますね。それではまずこちらから見ていきましょう。
GOOGLEFINANCE("AAPL","price",TODAY()-365,TODAY(),"WEEKLY")
ここでは365日前から現在の日付まで週単位”WEEKLY”で取得しています。
このデータをそのまま SPARKLINE関数に渡すだけでグラフを表示できます。もちろん、日数を変更したり日別”DAILY”に変更することも可能なのでお好みの期間に調整してみてください。
もしチャートの形式や線の太さ・色などを変えたい場合は、上で紹介した様に {"charttype","line";"linewidth",1;"color","#5f88cc"} といった文字列を設定することで自由に変更できます。
手順5：四半期売上 前年同期比を取得
続いては四半期売上の前年同期比を取得します。残念ながらこれまで使ってきた GOOGLEFINANCE 関数では売上高を取得することはできません。
そこでIMPORTXML関数を使って外部サイトのデータを読み込みます。この方法を覚えると売上高に限らず、様々なデータを扱えるようになりますので是非マスターしてください。
外部サイトではこのようなデータの読み込みを禁止している場合があります。又、多数のリクエストはサイトに負荷を掛けてしまうため、用量用法を守ってお使いください。
今回、使用するのはモーニングスターのページです。データを取得する上で広告などの表示は読み込み速度の低下に繋がるのでこのようなシンプルなページが理想的です。
では、さっそく前年期の売上を取得しましょう。
=IMPORTXML("https://quotes.morningstar.com/stockq/c-financials?&t=AAPL","/html/body/div/div[2]/table/tbody/tr[5]/td[6]")
最初の項目には先程のURLを、後の項目には取得したい値のXPathを入力します。XPathとは、XML/HTMLから要素を取得するための言語です。
XML/HTMLはツリー構造のようになっていて、XPathではこの構造に沿って要素を指定することができます。以下の例ではh1という要素を指定しています。
一見、難しそうに思いますがこのXPathはGoogle Chromeであれば簡単にコピーできます。
まず、ブラウザ上のリンクではない場所で右クリックを押して、以下のメニューを表示して「検証」を選択しましょう。
すると半分くらいの高さで別の黒い画面が表示されるので、以下の手順に沿って別のメニューを表示します。
取得したいデータの行を選択した上で、このようなメニューが表示されたら後は「Copy full XPath」をクリックするだけです。
この例では以下のXPathが取得できれば成功です。これを先程のIMPORTXML関数に入力するとスプレッドシート上で選択した値が表示されます。
/html/body/div/div[2]/table/tbody/tr[6]/td[7]あとは同じ手順を踏み、取得した値を直近四半期 / 前年四半期 - 1 で計算すると四半期売上の前年同期比を表示することができます。取得した値は桁を合わせておくと他の値と計算しやすいです。
手順6：PSR を取得
最後にPSR（株価売上高倍率）を取得したいと思いますが、実はここで新しく覚えることはもうありません。これまでの手順でやってきた内容を応用して表示することができます。
年間売上高をそのまま使うパターンは新規に前年通期売上高を取得しましょう。直近四半期売上をx4するパターンでは既に取得した値を掛け合わせるだけで算出できます。
まとめ
これまでの手順を全て反映するとティッカーを入力するだけで様々な情報が取得できるオリジナルのスプレッドシートを作れるようになります。
昨今は便利なツールが多いですが、情報量が多かったり必要以上に銘柄を見過ぎてしまうと、銘柄の数字の異常に気づきづらくなってしまいます。
私は自分が見たいデータを無駄なく集めることで個々の銘柄の数字の温度感を知り、適切なタイミングで売買できるようになると考えています。
皆さんも自分が必要なデータを集められるように是非カスタマイズしてみてください。
最後に注意点として、スプレッドシートは通信量が多すぎると読み込みが遅くなってしまいます。外部サイトのデータを取得する場合は、先方側の負荷にもなってしまうため必要最低限の数で運用することをお勧めします。
