ポートフォリオはGoogleスプレッドシートで関数を使ってつくろう!初心者でも簡単に株価を取得できるよ!

Googlespreadsheet 株式投資

先日、TwitterのフィードでGoogleスプレッドシートだとGoogleファイナンスの関数を使って、株価を自動的に取得できるよってのが流れてきたんです。

どの方のツイートかは忘れましたが、おお!これはぜひ作るべきだと思い、自分用に作ってみました。

私はまだ株を勉強し始めたところなので、色々と学んでますが、株をずっとやっている人はもうこういうのは当たり前なんでしょうが、簡単に作れる解説をしてみます。

めざす出来上がりはこんな感じです。
Tickerと取得株価、株数を入れるだけであとは自動で計算してくれるシートをめざします。
手順通りにやれば誰でもできるものです。
下記が完成形です。

Googleスプレッドシート

まずGoogleスプレッドシートとは??

まずGoogleスプレッドシートってなんぞや?という方もいるかもしれないですね。

GoogleスプレッドシートとはGoogleドキュメント中の1つであり、Excelに似たようなオフィスソフトの一つです。

ウィキペディアによるとGoogleドキュメントは下記の通りで解説されていました。

Google ドキュメント(グーグル ドキュメント)、旧名 Google Docs & Spreadsheets(グーグル ドックス アンド スプレッドシーツ、2007年まで)は、Googleが無料提供する、ウェブブラウザ内で動くオフィスソフト

ウィキペディア

ウェブブラウザ内で動くため、スマートフォン、タブレット、パソコンからアクセスすることが可能です。

ブラウザ上で動くから、関数を使って株価の取得が可能なんですね。

じゃぁ、どうやって作るの?ということですよね。

作り方を解説したほうが良いのか、それとも作ったやつ配布したほうが良いのか?って思ったんですが、作ったやつを配布すると、ここがこうできませんとか、これをちょっとカスタマイズしてくださいとか色々と言われそうなので、ベースの作り方を解説することにしました笑

ベースの作り方を解説するので、これをもとに自分のお好みに変えてください。

では、いきましょう。

ステップ1:まずスプレッドシートを開く

皆さん、Googleのアカウントを持っていますよね。

アカウントにログインした状態で、ブラウザの検索窓に「Googleスプレッドシート」といれて検索しましょう。

こんな感じに表示されますね。

Googleスプレッドシート

はい、では一番上のGoogleスプレッドシートをクリック。
そうするとこの画面が出てきます。

Googleスプレッドシート

はい、パーソナルをクリックします。
下記の画面が出てきます。

Googleスプレッドシート

一番最初なので、空白のスプレッドシートを選択しましょう。
こんな画面が開きます。

Googleスプレッドシート

赤枠で囲まれた無題のスプレッドシートというところに自分がつけたいファイル名を入力します。
今回は「ポートフォリオ2019」と入れてみました。

これで、ポートフォリオのスプレッドシートファイルができました。
次は中身を作っていきましょう。

ステップ2:スプレッドシートの項目を作る

ではまず下記のように、項目名を入力していきましょう。

Googleスプレッドシート

右から、日付、Ticker、株価、取得株価、株数、評価額、取得額、評価損益、%です。 
必ずA1のセルから始めてくださいね。
あとの関数に関係してきますので。

色を変えたい場合は、赤枠で囲まれたバケツをクリックして、好きなカラーを選びましょう。

ここで罫線を付けたくなりますが、罫線はいりません笑
いや、好きだったらつけてもらったら良いのですが、こういうドキュメントというのはなるべくあとからも加工しやすい、フィルターを使ってデータの場所が入れ替わっても良いように保つのが一番で、罫線やセルの結合はない方が良いのです。
罫線とセルの結合ほどめんどくさいものはありません笑
これはエクセルドキュメント、仕事用のドキュメントにもあてはまることなので、罫線とセルの結合は使わないのがベストです。笑
なぜ?と思った方はググってみてください。

話はそれましたが、次は実際の項目を入力していきましょう。

ステップ3:項目に実際の値を入力する

はい、では下記の赤枠で囲まれた項目を入力していきましょう。

Googleスプレッドシート

日付、Ticker、取得株価、株数を入力します。
これは皆さん違うので、自分で入力すべき項目ですね。
裏を返せば、これを入力したらその他は自動的に計算されるものなのです。

日付は別になくても良いのですが、まぁあった方がわかりやすいですよね。
では、はい、次。
次は関数を入力していきます。

ステップ4:関数を入力する

では、先程入力しなかった株価、評価額、取得額、評価損益、%にそれぞれ関数を入れていきましょう。
はい、面倒なので下記の関数をコピーしましょう。
赤字のところをコピーしてください。

  • 株価:=GOOGLEFINANCE(B2,”price”)
  • 評価額:=C2*E2
  • 取得額:=D2*E2
  • 評価損益:=F2-G2
  • %:=H2/G2

はい、コピーして入れましたか?
コピーするとなんてことでしょう!
自動的に計算されましたよね?!
されていない?!そんなあなたは”price”がちゃんと認識されていないようです。
認識された場合は緑色で表示されます。
その場合、このチョンチョンを消して、自分でチョンチョンを打ち直してみてください。

打ち直す場合は必ず半角でお願いします。

こんな感じで黒色でpriceと表示されているので、チョンチョンを打ち直すと、下記のように緑色に表示されると思います。
緑色の表示になったら、値がとれるはずです。

ちなみにこのチョンチョンは半角のダブルクオーテーションマークって名前でしたよね・・・?

Googleスプレッドシート

Tickerや取得単価、株数が人それぞれなので、表示されるものは違いますが、このような感じになったのではないでしょうか??

ちなみに最初、GoogleFinance関数でTickerを取得するとき、=GOOGLEFINANCE(”=B2”,”price”)
と書いていたのですが、どうしてもTickerが取得できなくて、試行錯誤しました。
スプレッドシートではそのままダイレクトにB2と指定するだけで値を取得してくれるのですね。

何言っているかわかんねーよという方は次にいきましょう笑

では、次にマイナス表示のときは赤色で表示、%は%で表示されるようにしましょう。

ステップ5:マイナス表示を赤色で表示させ、%を%で表示させる。

最初の出来上がり画像を見ると、評価損益がマイナスの場合は赤色で表示されています。そして、隣の%は%で表示されていますね。
その設定をしていきましょう。

まずはカーソルをH2にあわせます。
マイナス表示を赤色で表示させる設定は、表示形式>条件付き書式をクリックします。

Googleスプレッドシート

条件付き書式をクリックすると、項目を設定する画面が右側に出てくると思います。
下の画面ですね。

Googleスプレッドシート

この出てきた画面のセルの書式設定の条件というところで、「次より小さい」を選択します。

Googleスプレッドシート

はい、選択しましたか?
選択すると、その下に数値を入れるボックスが出てきます。
そこに「」(半角)と入力しましょう。
これで「0より小さい時」という条件になります。

Googleスプレッドシート

はい、では0より小さいときに赤色の文字にしましょう。

Googleスプレッドシート

Aと表示されているところをクリックし、出てきたパレットから赤色を選択します。
これで、文字色が赤になるように設定できました。
他の色が良い場合は、他の色を選んでくださいね。

ついでに背景色はなしにしましょう。

背景色はバケツのアイコンをクリックすると選択できます。
バケツのアイコンをクリック>なしを選択しましょう。
もちろん、背景色は他の色を選びたいという人は好きな色を選んでくださいね。

完了ボタンをおします。
すると下記のような表示になるかと思います。
これで、マイナスのとき赤色表示になりました。

続いて、%を%表示にしましょう。
カーソルをI2にあわせます。

Googleスプレッドシート

表示形式>数字>パーセントを選択します。
あら、不思議。一気に%表示になりましたね。
そして、ここでも先程と同じようにマイナスのときは赤字表示設定を行います。

ステップ6:入力した関数を下の行にもコピーしていく

2列目に入力した関数たちを下の行にもコピーしていくことで、下の行に値を入れても自動で計算されるようにしましょう。

コピーしたいのは株価のC列とF〜Iの評価額、取得額、評価損益、%ですね。
ではまず、C2にカーソルをあわせます。
そしてそのセルの右下に出ているポッチにカーソルを合わせます。
そうするとカーソルが十字にかわりますね。
その十字に変わったままクリックして、下の行にドラッグします。
そうすると、数式だけがコピーされます。

Googleスプレッドシート

コピーされたセルには#N/Aと表示されるかと思います。
それでOKです。
同じことをF〜Iでも繰り返します。
そうすると下記のように表示されるかと思います。

Googleスプレッドシート

試しに、3行目に新しいTickerと取得株価、株数(数字を入れる場合は必ず半角で!)を入れてみてください。
あら不思議。自動で計算されましたよね?

Googleスプレッドシート

はい!これで出来上がりですよ〜〜〜
疲れましたね〜

まとめ

GoogleFinance関数ではアメリカ株しか取得できないらしいです。
なので、日本株はできないらしいです。

そして、わからないところはひたすらググってみてください!笑

GoogleFinanceの関数に関してはここで詳しく解説されています。当日の始値とか時価総額とか色々取得できるので使いこなせるようになると楽ですね!

GOOGLEFINANCE - ドキュメント エディタ ヘルプ
Google Finance から現在や過去の証券情報を取得します。 使用例 GOOGLEFINANCE("NASDAQ:GOOG", "price", DATE(2014,1,1), DATE(2014,12,31), "DAILY") GOOGLEFINANCE("NASDAQ:GOOG","pric

追記

このようにして、株価を入力するだけで円グラフの自動化も可能になります。
どうやってやるのか気になる方はぜひコメントください。

<関連記事>
自分のポートフォリオはポートフォリオビジュアライザーで過去データを使って検証が可能です。
またこれからポートフォリオを作ろうと考えている人にも、使ってみてほしいツールの一つです。

コメント

タイトルとURLをコピーしました