投資

手間無しコピペで完了!自動で投資スプレッドシートを管理する方法

みなさまこんにちは。歩(@moshikabu01)です。

本日はツイッターでご紹介させて頂いた、わたしのスプレッドシートの作成方法についてお話していこうと思います。

 

1.スプレッドシートの中身

こちらが実際のスプレッドシートの中身です。
マニュアル管理を少なくして、現在株価や、株価推移、年間平均額を出して、どの銘柄をどのタイミングで買うか、検討しやすいようにしています。

①株、為替のリアルタイム更新
②年間日別チャートの作成
③年間平均額と乖離率(年間平均額と現在株価の乖離率)の算出

ポイント① 株、為替のリアルタイム更新

自動で更新が可能なので、スプレッドシートのメンテナンスが非常に楽になります。
また、保有株数を入力すれば、リアルタイムでの評価額も算出可能です。

ポイント② 年間平均額と乖離率の算出

株価の年間平均額と現在の株価との比較が可能となり売買の参考になります。

✔︎ 乖離率が100%より下→現在の株価が年間平均を下回っており、買い求めやすい(セール中)
✔︎ 乖離率が100%より上→現在の株価が年間平均を上回っており、株価が上昇している傾向

わたしはこのリアルタイム更新スプレッドシートで、
ポートフォリオで追加した方が良いもの+乖離率が100%より下のもの」を買うようにしています。

ポイント③ 年間日別チャートの作成

チャートとして年間の株価推移が表せられるので、視覚的に株価が上昇傾向なのか、下降傾向なのかを知ることができます。これも株売買時の参考になります。

2.作成方法

では早速作っていきましょう!大前提としてGoogle スプレッドシートが必要になります。Googleをお使いの方はスプレッドシートが無料で使えます。まだGoogleアカウントを登録していない方は、無料なので登録をしてくださいね。

ではスプレッドシートを開きます。

①株のリアルタイム更新(例:マイクロソフト)

株価を出したいセルにカーソルをあて、セル内に下のような関数を入れます。

=GOOGLEFINANCE(“NASDAQ:MSFT”, “price”)

※NASDAQ:MSFT:取引所コードと銘柄を入力
※price:株価を表示

実際にスプレッドシートに入力するとこのようになります。

株価が$242.47と表示されています。これ入力時のマイクロソフトの株価です。これで毎日メンテナンスしなくても、スプレッドシートを開けば株価が最新の状態で現れるようになります。

②年間平均額と乖離率の算出

Step1. 過去1年の株価一覧表を1つの関数で出す

新しいスプレッドシートを開き、空いているセルに下のような関数を入れます。

=GOOGLEFINANCE(“NASDAQ:MSFT”,”price”, TODAY()-360,TODAY(),”daily”)

※NASDAQ:MSFT:取引所コードと銘柄
※price:株価
※TODAY()-365:開始日(開始日から遡って何日ぶんの株価を表示したいか)
※TODAY():終了日(いつまでの株価を表示したいか)
※daily:間隔

実際にスプレッドシートに入力するとこのようになります。

今日から365日遡ったときの株価が表示されます。
ひとつの入力(関数)で一発で過去1年分とってこれるのは凄いですよね。

Step2. 年間平均額

年間平均はこの一覧の平均なので、年間平均を表示したいセルの中に下記のような関数を書きます。

=AVERAGE(C:C)

Step1.内の年間株価を出している列がC列なので、そこを全選択したものの平均ですね。

Step.3 乖離率

対象のセルに下に該当する関数を入力します。

乖離率=現在の株価/年間平均株価

※現在の株価:①で算出した自動で更新される株価
※年間平均株価:② Step2で算出した株価の年間平均額

上記でも言った通り、もし100%を下回っていれば求めやすい、という指標ができます。

補足


・開始日と終了
今回は今日から遡り1年間の株価を反映させていますが、開始日と終了日を特定の日付にする事もできます。特定の日付は”DATE”で表す事ができます。

(例)=GOOGLEFINANCE(“NASDAQ:MSFT”,”price”,DATE(2020,12,1),DATE(2021,1,31),”daily”)

こうすると、2020年12月1日から2021年1月31日までの毎日の株価が表示されます。

・間隔
今回は毎日の株価表示させましたが、間隔を1週間にしたり1か月にしたりすることも可能です。

(例=GOOGLEFINANCE(“NASDAQ:MSFT”,”price”,DATE(2020,12,1),DATE(2021,1,31),”Weekly”)

こうすると、2020年12月1日から2021年1月31日までの週毎の株価が表示されます。

③年間日別チャート

グラフを書きたいエクセルに下記のような関数を入力します。

=SPARKLINE(GOOGLEFINANCE(“NASDAQ:MSFT”,”price”,today()-360, TODAY(),”daily”))
※上記の関数にSPARKLINEを追加

※NASDAQ:MSFT:取引所コードと銘柄
※price:株価
※TODAY()-365:開始日(開始日から遡って何日ぶんの株価を表示したいか)
※TODAY():終了日(いつまでの株価を表示したいか)
※daily:間隔

実際にスプレッドシートに入力するとこのようになります。

今日から365日遡った、日別のチャートが表示されます。
これも一覧表と同様、特定の日の間のチャートにしたり、weeklyチャートにすることも可能です。
これで視覚的株価トレンドも可視化可能です。

まとめ

上でまとめた指数も使い、評価額(USD,JPY)も算出することが可能です。

わたしはスプレッドシートに下記も記載しています。

✔︎ドル円為替
=GoogleFinance(“currency:USDJPY”)

✔︎VIX指数(恐怖指数)
=GOOGLEFINANCE(“INDEXCBOE:VIX”, “price”)

✔︎評価額(USD)
=株価×株数

✔︎評価額(JPY)
=評価額(USD)×ドル円為替

全てまとめるとこのような表になります。

マニュアルですが、わたしはこれに加えて

・1株あたりの年間配当金(マニュアル入力)
・年間配当予定金額(1株あたりの年間配当金×株数)
・評価損益(マニュアル入力)

を入力して金曜日に更新をかけています。

超ベーシックな内容をまとめましたが、Google Financeを活用すると、もっと色んな指標を自由に取得できます。Google Financeの関数をまとめたリンクはこちらです。

自由にカスタマイズして自分にあったスプレッドシートを作り、楽にメンテしていきましょう。

COMMENT

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です