米国株の騰落率(値上がり率、値下がり率)を年初来、月別、週別でGoogleスプレットシートを用いて自動取得する方法を紹介します。株価の騰落率を簡単に管理できるので、非常に便利です。テンプレートも無料公開しているので、ぜひご参考にして下さい！
Googleスプレッドシートとは？
- Googleスプレッドシートとは？ 何が便利？
オンラインで現在の株価、過去の株価を自動で取得できます。
しかもGoogleアカウントさえあれば無料です。 - Excel V.S. Googleスプレッドシート
株管理は絶対スプレッドシートがおススメです。
Googleスプレットシートでポートフォリオの自動管理の記事はこちら☟ぜひ使ってみて下さい！
▶Googleスプレッドシートで株管理(テンプレあり)
今回は米国株での株価取得の紹介ですが、”日本株”の株価管理方法は下記で紹介しています！日本株の株価情報をGoogleスプレットシートで取得する方法は、複雑でネットにも説明があまりないため、ぜひご参考ください！
▶Googleスプレッドシートで日本株情報の取得（テンプレあり）
今回は指定した米国株の銘柄の現在株価、1週間前、1ヵ月前、年初の株価を取得し、値上がり率や値下がり率を自動更新する方法を紹介します！利確や損切ルールを決めて、周りに流されず粛々とポートフォリオを管理していくための、非常に便利なツールとなると思います！！
Googleスプレットシートのテンプレート公開
今回作り方を紹介する株価管理用のスプレッドシートを現在無料で公開中です。
黄色セルにティッカーシンボルを入れるだけで、上記の情報が自動更新されるようになっています。下記にスプレットシートを共有化していますので、ぜひ試してみて下さい！
【テンプレートのコピー方法】
１：上記リンクを開く
２：シートを「右クリック」
３：「別のワークブックにコピー」
４：「新しいスプレットシート」の順にクリック。
ご自身のスプレットシートとしてコピーし開くことができます！
作り方も一読いただき中身を理解してテンプレートを確認すると色々と応用がきくと思います！
今回のような週次の株価情報、決算情報などを呟いているのでTwitterもぜひフォロー下さい！質問等も可能な限り対応します！
Googleスプレッドシートで株価を自動更新させる項目
【ゴール】
今回作成するスプレットシートでは、黄色セルのティッカーシンボルを入力すれば、企業名や各期間の株価が自動更新されるものです！こちらを作成する手順を紹介します。
【今回自動更新する情報】
・企業名
・現在株価
・1週間前の株価
・1ヵ月前の株価
・年初来の株価
・各期間の騰落率(値上がり、値下がり率)
ティッカーシンボルさえ入力すれば、欲しい銘柄情報を自動更新できます！
保有銘柄の損切や利確をする上で非常に便利なので、ぜひ参考にしてみて下さい！
株価の自動更新シートの作成手順
Googleスプレッドシートの立ち上げ
- Googleアカウント作成
アカウントが無いとできません。アカウント無い方は作成必要です(作成方法はググってください！) - Googleスプレッドシートの立ち上げ
下記のGoogleスプレッドシートのページに飛ぶ
https://www.google.com/intl/ja_jp/sheets/about/
「Googleスプレッドシートを使ってみる」をクリック
右下「プラス」画像をクリック！
これで立ち上がります！見た目はExcelみたいな感じですね。 - ティッカーシンボルの入力欄を作成
ティッカーシンボルを入れれば各期間の株価が取れるようにしたいので、ティッカーシンボルの入力欄を作成していきます。私は下記のように保有株とウォッチリストを区別するため、保有株には●を入れて管理するようにしています。色やフォントの大きさはお好みで！！
企業名、現在株価の自動取得
1.企業名の取得
企業名の列を作って、関数を追加します。
=IFERROR(GoogleFinance(C5,”name”))
GoogleFinance 関数でC5セルであるティッカーシンボルを参照し、企業名を自動で取ってくるという便利な関数です。＝GoogleFinance(C5,”name”)でも問題ありませんが、エラーが出たときに空白を返して欲しいので、IFERROR （）で囲っています。
Vanguard Total Stock Market Index Fund ETF というVTIの企業名にあたる部分を自動取得することができました！
後は、下までコピーすればOKです！
2.現在株価の取得
続いて、ティッカーシンボルから現在の株価を取得します。こちらも GoogleFinance 関数 で簡単にできます。現在値の列を作って
=IFERROR(GoogleFinance(C5,”price”))
と入力します。さっきと同様にC5セルのティッカーシンボルの株価を取ってこいという指示をしています。
こちらも同様に下までコピー！
週別、月別、年初来の株価を自動取得
自動取得できた現在株価と比較する各時点の株価も自動で取得していきます。
1. 1週間前株価の取得
現在株価と同様に GoogleFinance 関数 で簡単にできます。1週間前の列を作って、
=INDEX(GOOGLEFINANCE($C5,”price”,TODAY()-8),2,2)
と入力します。 GOOGLEFINANCE($C5,”price”,TODAY()-8) でC5のティッカーシンボルの8日前の終値を取ってくる式になっています。
こちらも下までコピーしたら完成です。自動で1週間前の株価を取得することができました！
ちなみに、INDEX()で囲むのは、 =GOOGLEFINANCE($C5,”price”,TODAY()-8)だけだと、下記のように表示されるため、2行、2列目の株価だけを取ってこいと指示しています。
2. 1ヵ月前株価の取得
続いて、1ヵ月前の株価を取得していきます。
=INDEX(GOOGLEFINANCE($C5,”price”,TODAY()-31),2,2)
今度は31日前をとってくるように指示しています。
こちらも下までコピーしたら完成です。
3. 年初めの株価の取得
続いて、年初めの株価の取得です。TODAY()-31 とかを入れたところに、そのまま欲しい日付を入れればよいです。
=INDEX(GOOGLEFINANCE($C5,”price”,”2022/01/01″),2,2)
日付の部分を好きな日付にすれば、ある時点の欲しい株価情報を取得することができます。コロナ前の日付やリーマンショック前、後などの、把握したい日付を入れてみて下さい！日付指定できるので便利です！
こちらも、下までコピー。
これで、現在株価、1週間前、1月前、年始めの株価をティッカーシンボルから自動で取得できるようになりました。
米国株の騰落率（値上がり、値下がり率）の計算
取得した各期間の株価を使用して、週次、月次、年初来の株価の騰落率、つまり値上がり率、値下がり率を算出していきましょう。ここまできたら、ただの割り算ですね！
1. 1週間前株価と現在株価の比較
現在株価 ÷ 1週間前株価 – 1
=$E5/F5-1
で騰落率を求められます。単純に現在株価からどのくらい上がった？下がった？かを計算しているだけです。
2. 1ヵ月前株価と現在株価の比較
同様に1ヵ月前と比較すればOKです。
現在株価 ÷ 1ヵ月前株価 – 1 なので、
=$E5/G5-1
3. 年初来株価と現在株価の比較
こちらも同様に
現在株価 ÷ 年始め株価 – 1 なので、
=$E5/H5-1
これを3列をさっきと同様に下にコピーすると、
騰落率を各期間で表示することができました！
おわりに
今回の方法で、自分の保有株や検討しているウォッチリストの株が1週間前や1ヵ月前、年初来でどの程度動いたかを把握することができます！
米国株全体の動きに類するVTIに、所有している個別株のパフォーマンスが負けてるか？勝ってるか？を確認し、市場全体との比較をする際にも便利です。騰落率の管理は、どのタイミングで買い、売るかを判断する重要材料ですので、ぜひ参考にしてみて下さい！
これまで、私がGoogleスプレットシートで作成した株関連のテンプレートを下記記事でまとめています。配当利回り、PER、PBR、日本株情報などを自動で取得できるので、ぜひご参考ください！
▶Googleスプレットシートで株情報を自動取得するテンプレートまとめ
最後まで読んで頂きありがとうございました！
良かったら、クリック応援よろしくお願いします！！☟
コメント