【テンプレ公開】Googleスプレッドシートで日本株の騰落率(値上がり/値下がり率)を自動計算

Googleスプレットシートを用いて、指定した日本株の銘柄の企業名、現在株価、1週間前、1ヵ月前、1年前株価を取得し、値上がり率や値下がり率を自動更新する方法を紹介します！自動更新でき、Excelで管理するよりも超便利なのでぜひ試してみて下さい！

Googleスプレッドシートとは？
Googleスプレッドシートで株価の自動更新
騰落率計算シートの作成方法
騰落率計算シートのテンプレート公開中
おわりに

Googleスプレッドシートとは？

Googleスプレッドシートとは？　何が便利？
なんといっても、オンラインで現在の株価、過去の株価を自動で取得できます。
しかもGoogleアカウントさえあれば無料です。
Excel V.S. Googleスプレッドシート
株管理は絶対スプレッドシートがおススメです。

Excel：オフラインで使用(ネット環境必要なし)
→日々変動する株情報に対応できない。
スプレッドシート：オンラインで使用(ネット環境必要)
→ネット情報を取得するので、日々変動する株価や為替をリアルタイムに自動更新させることができる！

Googleスプレットシートでポートフォリオの自動管理の記事はこちら☟
人気記事ですので、ぜひ使ってみて下さい！

今回は日本株Verの株価の自動更新手順を紹介しますが、同様に米国株の記事も下記でまとめているので、米国株をされている方は、ぜひ見てみて下さい！☟

利確や損切ルールを決めて、周りに流されず粛々とポートフォリオを管理していくための、非常に便利なツールとなると思いますので、ぜひ参考にしてみて下さい！

Googleスプレッドシートで株価の自動更新

【ゴール】

今回作成するスプレットシートでは、黄色セルの証券コードを入力すれば、企業名や現在株価などの各期間の株価が自動取得され、値上がり、値下がり率を把握できます！期間騰落率を計算させることができます。

【今回自動更新する情報】

・企業名
・現在株価
・1週間前の株価
・先月の株価
・１年前の株価
・各期間の騰落率(値上がり、値下がり率)

証券コードさえ入力すれば、欲しい銘柄情報を自動更新できます！

保有銘柄の損切や利確をする上で非常に便利なので、ぜひ参考にしてみて下さい！

騰落率計算シートの作成方法

Googleスプレットシートを用いた株価の自動更新、期間騰落率の計算シートを作成する手順を紹介します。記事の最後にテンプレートも公開しています。

Googleスプレットシートの立ち上げ

Googleアカウント作成
Googleアカウントが無いとできません。
アカウント無い方は作成必要です
(作成方法はググってください！)
Googleスプレッドシートの立ち上げ
下記のGoogleスプレッドシートのページに飛ぶ
https://www.google.com/intl/ja_jp/sheets/about/
「Googleスプレッドシートを使ってみる」をクリック
右下「プラス」画像をクリック！
これで立ち上がります！見た目はExcelみたいな感じです。
証券コードの入力欄を作成
証券コードを入れれば各期間の株価が取れるようにしたいので、入力欄を作成します。私は下記のように保有株を区別するため、保有株には●を入れて管理するようにしています。色やフォントの大きさはお好みで！！

証券コードから企業名の自動取得(スクレイピング)

企業名の取得

ここで問題なのが、
＝GoogleFinance(C6,”name”)
と米国株では GoogleFinance 関数を用いて簡単に取得できた企業名や株価の情報が、日本株は非対応のため利用できません…。なので、スクレイピングで欲しい情報を持ってくる方法を紹介します。

株探（Kabutan）から情報を取ってきています。

スクレイピングとは？
ウェブサイトから情報を収集し、解析、加工などの処理に活用することです。

※注意1
スクレイピングが禁止されているサイトもあるので、利用規約を読んで活用しましょう。(例えば、Yahoo!ファイナンスは禁止が明示されています。本サイトでは株探を使用し、株探利用規約の「第4条(禁止事項)の(7)本サイトのサーバーに不当な負担をかける行為や本サービスの運営を妨げる行為。」にあたらない程度の個人利用で過度に負荷をかけない範囲で利用したいと思います。

※注意2
スクレイピングの弱点でもありますが、URLやサイトのレイアウトが変更になると、もちろん上記のURLやXpathも変更する必要があります。

Googleスプレットシートでスクレイピングを使う関数は下記の IMPORTXML関数です。

IMPORTXML(URL, XPathクエリ)

URL:データを取得したいサイトのURL
XPathクエリ：そのサイトのどこを取ってくるかの指定(構造化データ)

下記をセルにコピーし、C6セルに証券コードが入っていれば企業名を取得できます。

=IMPORTXML(CONCATENATE("https://kabutan.jp/stock/?code=",C6),"//*[@id='kobetsu_right']/div[4]/h3")

それでは、 具体的にIMPORTXML関数を用いて、企業名の取得方法を説明します。(なぜ↑で書いたコードになったかの説明になります。

株探（Kabutan）から武田薬品工業の証券コードである「4502」を検索

このサイトの「武田薬品工業」の名前を取ってきたいので、URLは

URL = https://kabutan.jp/stock/?code=4502

となりますね。

次にXPathクエリ を確認し、「武田薬品工業」の名前の場所を確認します。
Chromeを使用の場合は、そのまま「F12」を押します。
すると、下記の画面のように右側にウィンドウが出ます。

次に「Ctrl + Shift + C」もしくわ、下記の赤四角の部分をクリックしてください。

そのまま取得したい情報「武田薬品工業」が記載されたところをクリックします。(カーソルを当てると、ハッチングされます)

すると、右側のコードに色がつくので、「右クリック」→「Copy」→「Copy Xpath」を選択します。

これで、XPathクエリである

XPathクエリ = //*[@id=”kobetsu_right”]/div[4]/h3

を取得することができました。

あとは、IMPORTXML(URL, XPathクエリ) の形にGoogleスプレットシートに入れればOKなので、

=IMPORTXML(“https://kabutan.jp/stock/?code=4502“,”//*[@id=’kobetsu_right’]/div[4]/h3“)

となります。これで武田薬品工業という名前を取得することができました！

※注意　ペーストした後に変更必要
・URLはコピーした内容をダブルクォーテーション(”)でくくる
・ XPathクエリ はダブルクォーテーション(”) の部分をシングルクォーテーション(‘) に変える

【CONCATENATE関数でセルを参照して情報を取得】

上の状態だと、URLは「4502」が入っており武田薬品工業しか取ってこれないので、Googleスプレットシートの証券コードが入っているセルを参照するように、URLの部分を CONCATENATE関数で修正します。

=IMPORTXML(CONCATENATE(“https://kabutan.jp/stock/?code=”,C6),”//*[@id=’kobetsu_right’]/div[4]/h3“)

これで、証券コードを参照した企業名の取得ができます！！下までコピーしてやると、各証券コードの企業名が自動取得できました！

証券コードから現在株価の取得(スクレイピング)

現在株価の取得

続いて同様にスクレイピングで証券コードから現在の株価を取得します。
現在値の列とTODAY()関数で今日の日付を取得した列を作りました。

=TODAY()

先ほどと同様に、 IMPORTXML(URL, XPathクエリ)の形でGoogleスプレットシートに入れるので、 URLとXPathクエリを取得していきます。

URLはさきほどと一緒です。

URL = https://kabutan.jp/stock/?code=4502

XPathクエリは下記場所の終値を取ってくることにしました。

これをさっきと同様にXPathクエリのコピーをすると

XPathクエリ ＝ //*[@id=”kobetsu_left”]/table[1]/tbody/tr[4]/td[1]

先ほど作ったコードにXPathクエリだけ上記に変更して

=IMPORTXML(CONCATENATE("https://kabutan.jp/stock/?code=",C6),"//*[@id='kobetsu_left']/table[1]/tbody/tr[4]/td[1]")

※ URLはダブルクォーテーション(”)でくくること、XPathクエリ はダブルクォーテーション(”) の部分をシングルクォーテーション(‘) に変えることを忘れないようにしましょう！

現在値株価の取得ができました！

期間別(週別、月別、年別)の株価を自動取得(スクレイピング)

自動取得できた現在株価と比較する各時点の株価も自動で取得していきます。

1.　1週間前株価の取得

1週間前という列を作成し、現在日付であるセルに7日を引いて1週間前の日にちを表示させました。

=E5-7

こちらも同様に1週間前の株価をスクレイピングで取ってきましょう！ IMPORTXML(URL, XPathクエリ)の形でGoogleスプレットシートに入れればよいので、 URLとXPathクエリを取得していきます。

URLを取得するために、1週間前の株価がどこに乗っているか探しましょう。「時系列」→「週次」と進むと1週間ごとの株価の終値が出てますので、この最初の行が1週間前の株価になりますね！

URL　＝　https://kabutan.jp/stock/kabuka?code=4502&historical=per&ashi=wek

続いて、XPathクエリを取得します。1週間前の株価である一番上を取ってくればよいので、先ほどと同様に「右クリック」→「Copy」→「Copy Xpath」で取得し、メモ等に貼り付けます。

XPathクエリ = //*[@id=”stock_kabuka_table”]/table[2]/tbody/tr[1]/td[1]

後は同様に CONCATENATE 関数で囲ってセルを指定するように変更します。(企業名取得で説明したコードのURL、XPathクエリを変えるだけですね)
※URLの部分は証券コード(4502)が入っている部分をセル指定にするためCONCATENATE関数で下記のコードに変えます。

=IMPORTXML(CONCATENATE("https://kabutan.jp/stock/kabuka?code=",C6,"&historical=per&ashi=wek"),"//*[@id='stock_kabuka_table']/table[2]/tbody/tr[1]/td[1]")

これで1週間前の株価の取得ができました！

2.　1ヵ月前株価の取得

スクレイピングの都合上、先月最終日の株価を取得するため先月最終という列を作成し、EOMONTH関数を用いて、現在日の前の月の最終日を表示させます。

=EOMONTH(E5,-1)

※EOMONTH（イーオーマンス、エンドオブマンス）関数は、指定した日を基準として、月の最終日(月末の日付)を求めることができます。

こちらもスクレイピングで先月最終日の株価を取ってきましょう！ IMPORTXML(URL, XPathクエリ)の形でGoogleスプレットシートに入れればよいので、 URLとXPathクエリを取得していきます。

URLを取得するために、先月最終日の株価がどこに乗っているか探しましょう。「時系列」→「月次」と進むと月ごとの株価の終値が出てますので、この最初の行が先月最終日の株価になりますね！

URL　＝　https://kabutan.jp/stock/kabuka?code=4502&historical=per&ashi=mon

続いて、XPathクエリを取得します。1週間前の株価である一番上を取ってくればよいので、先ほどと同様に「右クリック」→「Copy」→「Copy Xpath」で取得します。

XPathクエリ = //*[@id=”stock_kabuka_table”]/table[2]/tbody/tr[1]/td[1]

後は同様に CONCATENATE 関数で囲ってセルを指定するように変更します。

=IMPORTXML(CONCATENATE("https://kabutan.jp/stock/kabuka?code=",C6,"&historical=per&ashi=mon"),"//*[@id='stock_kabuka_table']/table[2]/tbody/tr[1]/td[1]")

これで先月最終日の株価の取得ができました！

3.　1年前株価の取得

続いて1年前株価の取得です。同様にEOMONTH関数を用いて、現在日の13月前の最終日の終値を取得します。※厳密には1年前ではないですが、スクレイピングの関係上、今回は13ヵ月前の最終日の株価を1年前株価として取得することにしています。

=EOMONTH(E5,-13)

こちらもスクレイピングで13ヵ月前の最終日の株価を取ってきましょう！ IMPORTXML(URL, XPathクエリ)の形でGoogleスプレットシートに入れればよいので、 URLとXPathクエリを取得していきます。

URLは先月最終月と同様です。

URL　＝　https://kabutan.jp/stock/kabuka?code=4502&historical=per&ashi=mon

XPathクエリだけ13ヵ月前を指定します。13ヵ月前である場所を指定して、先ほどと同様に「右クリック」→「Copy」→「Copy Xpath」で取得します。

XPathクエリ = //*[@id=”stock_kabuka_table”]/table[2]/tbody/tr[13]/td[1]

後は同様に CONCATENATE 関数で囲ってセルを指定するように変更します。

=IMPORTXML(CONCATENATE("https://kabutan.jp/stock/kabuka?code=",C6,"&historical=per&ashi=mon"),"//*[@id='stock_kabuka_table']/table[2]/tbody/tr[13]/td[1]")

これで先月最終日の株価の取得ができました！

取得する情報を変えれば、欲しい時点の株価情報を取得することができますね！

これで、現在株価、1週間前、先月、1年前の株価を証券コードから自動で取得できるようになりました！

騰落率（値上がり、値下がり率）の計算

取得した各期間の株価を使用して、週次、月次、年初来の株価の騰落率、つまり値上がり率、値下がり率を算出していきましょう。ここまできたら、ただの割り算ですね！

1.　1週間前株価と現在株価の比較

現在株価 ÷ 1週間前株価 – 1

=$E6/F6-1

で騰落率を求められます。単純に現在株価からどのくらい上がった？下がった？かを計算しているだけです。

2.　1ヵ月前株価と現在株価の比較

同様に先月最終の株価でも計算すればOKです！

現在株価 ÷ 先月最終株価 – 1 なので、

=$E6/G6-1

3.　年初来株価と現在株価の比較

こちらも同様に

現在株価 ÷ 1年前株価 – 1 なので、

=$E6/H6-1

これで下記の完成です！

騰落率を各期間で表示することができました！
超便利なのでぜひ参考にしてください！

騰落率計算シートのテンプレート公開中

今回作り方を紹介する株価管理用のスプレッドシートを現在無料で公開中です。

黄色セルにを証券コードを入れるだけで、上記の情報が自動更新されるようになっています。下記にスプレットシートを共有化していますので、ぜひ試してみて下さい！

作り方を一読いただき中身を理解しテンプレートを確認すると、色々と応用がきくと思います！

株価管理(日本株)共有用

【テンプレートのコピー方法】
１：上記リンクから、シートを「右クリック」
２：「別のワークブックにコピー」
３：「新しいスプレットシート」の順にクリック
ご自身のスプレットシートとしてコピーし開くことができます！

ぜひ、ご自身のGoogleスプレットシートにコピーして使ってみて下さい！

Googleスプレットシートを用いた配当金の管理の作成手順、テンプレート公開も下記ブログでしているので、ぜひご参考下さい！

▶Googleスプレッドシートで配当管理(利回り取得)

リンク

おわりに

これで、自分の保有株や検討しているウォッチリストの株が週次や月次、年次でどの程度動いたかを把握することができます！日本株は米国株とは異なり、超便利なGoogleFinance 関数が非対応なので、スクレイピングでゴリ押しで情報を取ってきました。最初はコードが長く難しいと感じるかと思いますが、何度か書いてみるとURLやXPathクエリを変えるだけだな、簡単だなと感じて頂けると思います！

これまでGoogleスプレットシートで作成したテンプレートを下記記事でまとめています。ぜひご参考ください！
▶Googleスプレットシートで株情報を自動取得するテンプレートまとめ