40代の資産運用,投資ブログ 

米国株を中心に,40代の資産運用,投資について書いています

グーグルスプレッドシートでポートフォリオを最新の状態に自動更新 (日本株)

グーグルスプレッドシートでポートフォリオを最新の状態に自動更新 (日本株)

昨日C55の保有する株式のポートフォリオを公開しましたが,これは楽天証券にログインし,「口座管理」→「ポートフォリオ」からデータをexcelに落として,それを編集して作成したグラフを用いたものでした.

www.c55hero.com

結構面倒でした.
これを毎回繰り返すのは,いくらブログのためと言っても大変です.
個人で複数の口座を持っている,もしくはご家族で複数の口座を保有されている投資家の方は,保有資産を把握するのにいちいちログインして確認して,また違う口座にログインしてを繰り返すのは,相当面倒だと思います.
今回,C55家の全口座(家族それぞれ 計5つ)の株式資産を把握するためのポートフォリオを,グーグルスプレッドシートを用いて作成しました.
厳密にはえびたいさんが作成されているスプレッドシートを自分用にアレンジしただけです.エラーがでて時間がかかったり,外国株を追加して一枚のポートフォリオにしたりと,C55が手こずったこと,工夫したことを共有することで,皆さんのお役に立てればと思います.
すでにご利用の方も多いと思いますが,改めてグーグルスプレッドシートを用いてポートフォリオを作成する方法を,エクセルが苦手な方でも作成できるように手順だけに絞って紹介したいと思います.
 

 

追記:2017年5月7日16:15

ちゃんとしたスプレッドシートを作成し,公開しました.

結構簡単に総資産を把握できるし,デフォルトで自動的にグラフも作成されます.

ご自由にお使いください.

 

www.c55hero.com

 

 

 

f:id:cgogohero:20170504223956p:plain

グーグルスプレッドシートを用いたポートフォリオ(日本株)

C55のポートフォリオ

f:id:cgogohero:20170505073528p:plain

 

字が細かくて申し訳ありませんが,上の枠組みの中に日本株,下の枠組みに米国株を振り分けています.

見出しは

銘柄名・証券コード・セクター・数量・取得単価・現在値・損益(円)・損益(%)・評価額(円)

です.このうち現在値が自動的に取得されることで,損益や評価額が自動計算され,その結果ポートフォリオの円グラフも自動更新されます.

さらに米国株(外国株)の場合,為替の値段を反映させることで,日本円での評価額も自動で計算されるようになっています.

セクター別のグラフも自動作成できるようにしました.

 

元となるスプレッドシート

「株でサラリーマン卒業」を運営されているえびたいさんのサイトよりダウンロードさせていただきました.ありがとうございます.

まずはこのサイトから元になるシートをダウンロードしてください.

スプレッドシートの使用には,googleのアカウントが必要です.

kablog.doorblog.jp

変更手順

日本株の場合

一番下のシートを選択する部分には「Summary」と「4桁の数字」の名前がついたシートが並んでいます.4桁の数字が証券コードです.

1銘柄1シートをあてがいます.

 ここではオリックス(8591)を用いて説明します.

まずシートの名前を該当する証券コードに変更します.

8591と入力します

f:id:cgogohero:20170504212146p:plain

次に,A1をクリックすると,上のfxのところに

=ImportHtml("http://info.finance.yahoo.co.jp/history/?code=8591.t","table",1)

と表示されると思います.この赤字の4桁の数字の部分を自分が変更したい証券コードに書き換えます.

次に青字のアルファベットですが,オリジナル版では,例えば3172のシートでは4桁の証券コードのあとの青字で示したアルファベットが「f」になっていると思います.これは福岡証券取引所を表しているため「f」となっています.東証の場合は「t」に変更します.

東証(一部、二部、JASDAQ、マザーズ)なら「t」

名証なら「n」

福証なら「f」

札証なら「s」 に変更します.

オリックスの場合は,「8591」「t」に書き換えて確定すると,上記のようにA1にオリックスの会社名,C1に株価,D1に前日比が出ます.

 

証券コードを入力してもエラーが出てしまう場合は,証券コードと上場されている取引所が合っているか確認してください.

それでもエラーが出る場合は,

=ImportHtml("http://info.finance.yahoo.co.jp/history/?code=8591.t","table",1) 

の後に何も書かれていないことを確認してください.

 

 

Summaryのシートに戻ります.

f:id:cgogohero:20170504214210p:plain

「現在値」の部分を編集します.

編集する「銘柄」の「現在値」に該当するセルをクリックするとfxのところに

=IF('9788'!C$1="---",'9788'!E$3,'9788'!C$1)

が表示されます.難しい関数の説明はここでは割愛します.

赤字の4桁の数字を該当する証券コードに変更します.オリックスの場合は8591ですので,

=IF('8591'!C$1="---",'8591'!E$3,'8591'!C$1)

となります.

入力を完了すると,株価が反映されます.

これで現在の株価の自動更新が完了しました.

 

アレンジ

C55は次のようにアレンジしました.

f:id:cgogohero:20170504215247p:plain

          ↓

f:id:cgogohero:20170504214210p:plain

買付日→証券コード 

セクターを追加挿入 

しました.

証券コードを入力すると,銘柄名が自動的に挿入される関数もできると思いますが,気合いで手入力です.

また重要なことですが,複数の口座で同じ銘柄を保有する場合は,数量,平均単価を計算しておく必要があります.これが一番面倒ですね! これもある程度の部分まで関数を用いて楽に計算できますが,やはりそれぞれの口座を開いて数字を確認する必要があります.ここは根性が必要です.

しかし,「保有する銘柄がずらーっと並ぶ表の見た目を気にしない」,「銘柄ごとの平均取得単価や評価額が見られない」,この点を気にしないのであれば,グラフ化される際には同一銘柄はまとめて計算されますので,自分で平均単価を計算するのは面倒だという方はまとめなくても問題ないです.

 

他の銘柄でも同様に

  1. シート名に証券コードを入力
  2. fxの =ImportHtml("http://info.finance.yahoo.co.jp/histor/?code=8591.t","table",1) 赤字の部分を該当する証券コードに変更
  3. さらに証券コードの右の青字の部分「t」が東証(一部、二部、JASDAQ、マザーズ)なら「t」のまま.名証なら「n」、福証なら「f」、札証なら「s」に変更.
  4. Summaryのシートに戻り,該当する「銘柄」の「現在値」のfxの部分

    =IF('8591'!C$1="---",'8591'!E$3,'8591'!C$1)

    の赤色の4桁の数字を3つとも該当する証券コードに変更する

これを繰り返していけば日本株に関しては完成です.

 

用いた関数

損益(¥) =数量*現在値-数量*取得単価

損益(%) =損益(¥)/(数量*取得単価)

評価額   =数量*現在値

 

 「*」はかけ算です.半角英数で「SHIFT」+「け」で入力できます.もしくはテンキーの右上にあります.

 

長くなりましたので,いったん終了します.

次は米国株の手順を紹介しますね.