まだ含み益だけで一喜一憂しているの?配当集計講座3-3 損益推移グラフの作成

 

どうも、ミスターマーケット(@mrmarket_japan)です。

 

ひさしぶりに休日にまとまった時間を確保できたので、配当集計講座を追加しました!

 

現在、配当集計講座では「損益推移グラフ」の作成方法を紹介しています。

 

損益推移グラフとはミスターマーケットが各企業の配当を紹介する際に利用している下↓のようなグラフです。

 

 

損益推移グラフでは評価損益(含み損益・黄棒)と青棒の配当受取累計(配当益・青棒)、さらに実現損益(利益確定済み売買損益・薄青棒)を区別しながらも、最終損益の推移を見える化しています。

 

損益推移グラフの1番の効果は配当の効果を実感できることです。時間がたてばたつほど、配当が積み重なっていくことがわかります

 

ミスターマーケット
上↑のグラフでも、配当を表す青棒が少しずつ、でも確実に伸びていることがわかります!これぞ配当の力なのです!そして、それを見える化したのが損益推移グラフというわけ!

 

証券会社の画面では主に含み損益ばかり表示されるため、配当の力は忘れがちです。特に長期投資では本来、配当が累積されることで最終収益が押し上げられるため、配当の力を見える化することはマスト(必要)だと思っています( ・`ー・´) + キリッ

 

もし、含み損だけど、配当も含めて考えると大きな収益をもたらしてくれていた、そんなこともありえると思います。

 

そして、長期投資はヒマでつまらない投資といわれますが、「投資収益の見える化」で少しは楽しい投資になるかなっと思っています。

 

損益推移グラフの作成は全4回でできたらいいなと思っています。現在の予定としては下↓のとおりです。

 

 

3-1損益推移グラフをつくろう-データの引用

3-2損益推移グラフをつくろう-引用データシートの加工

3-3損益推移グラフをつくろう-集計シートの作成←今回

3-4損益推移グラフをつくろう-グラフの作成

 

 

よかったらおつきあいください。

 

ちなみに、そんなグラフを作ってみたいけど一からつくるのは大変だなと思う方は【投資の見える化・簡易版】10分で投資配当グラフをつくる方法がオススメです。

 

また、配当よりも先にポートフォリオの管理(見える化)をしたいという方は【米国株も!日本株も!管理できる】ポートフォリオ推移グラフの作り方がオススメです。

 

 

 

3-3-1集計シートをつくる

 

 

まずは①をポチ。

そして、②をダブルポチポチして新しくできたシートの帯に「集計」と名前をつけましょう。

 

(もう、もう解説なしでもできるようになってきているかな…?)

 

3-3-2集計シートの見出しと関数を入力する

 

ここからはコピペできるところはコピペして処理していただくのが楽かなと思います。

 

まずは各項目の見出しをつけしょう

 

 

①下記のように見出しをつけます。

A1:「日付」

B1:「年/月」

C1:「保有株数」

D1:「投資金額($)」

E1:「実現損益($)」

F1:「株価($)」

G1:「評価額($)」

H1:「評価損益($)」

I1:「配当受取金額($)」

J1:「配当受取累計($)」

 

次に各項目に数式等を埋め込みます。

 

A2には適当な年月の月末の日付を入力します。

そしてA3にはA2でつけた年月の翌月の日付を入力します。

 

 

今回はサンプルのコカコーラ(KO)の配当データが2009年4月であったため、

A2には「2009/3/31」

A3には「2009/4/30」

と入力しました。

 

 

そして、(つぎが少し大切です)

 

 

②のAの部分をポチでA列を選択し、

そのまま③「表示形式」をポチ、④「数字」をポチ、⑤****/**(今回は2008/09になっています)をポチします。

 

日付の表示方式を変更したわけです。

下↓のようになっていたらgoooooodです!

 

 

そして、

B2には「=CONCATENATE(LEFT(A2,4),“/”,RIGHT(A2,2))」と入力します。

(A2の左の4つの文字と、/、A2の右の2つの文字を組み合わせを組み合わせた文字列をつくる関数です)

 

下↓のようになります。

 

そして、C2、D2、E2、は飛ばして、次に入力するにはF2です。

 

 

上↑のようにF2には「=VLOOKUP(A2,‘株価データ’!$1:$10000,3)」と入力します。

 

(株価データシートにある、A2と同じ日付データの株価を持ってきてくれという関数です)

 

そうすると…

関数が、株価データシートから2009年3月31日の株価データを持ってきてくれました!

 

ミスターマーケット
おぉ、アンビリーバボォー

 

さらに行きます。

G2には「=C2*F2」(保有株数*株価)

H2には「=G2D2」(評価額-投資金額)

 

次のI2は少し長い関数になります。

 

 

I2には上↑のように「=IF(ISERROR(VLOOKUP(B2,‘個別企業配当($)データ’!$1:$1000,2,FALSE)),0,
VLOOKUP(B2,‘個別企業配当($)データ’!$1:$1000,2,FALSE))」といれます。

 

(データあれば個別企業配当($)データシートのB2と同じ文字列データの配当データをもってきてちょって関数)

 

んで、最後の、

J2には「=IF(J1=“配当受取累計($)”,0,J1+I2)」と入力します!

 

ミスターマーケット
お疲れ様でした。ふぅ、一息つきましょう。

 

3-3-3集計シートを一気につくりあげる!

 

関数を入れなかったA列(日付データは入れた)、C列、D列、E列は入力が必要な列ですので、わかりやすいように色をつけます。

 

①をポチしてA列を選択します。

②のバケツをポチして、③で好きな色を選択します。

 

C列・D列・E列も同じように色をつけましょう。

 

 

そして、C2、D2、E2に実際の数字を入力します(適当な数字でもかまいません)。

 

(↓ミスターマーケットは実際の数字:C2に100、D2に2957、E2に0を入力しました)

 

さっそく、2009年3月末の評価損益が-759と計算されました。

 

んで、次はB2からJ2をしたのセルにコピーします。

 

オートフィル機能をつかいます。

(【1-3-3:集計のために少しデータを加工する】でも説明しましたので、下の解説でわからない場合は詳しくはそちらを見てください)

④をポチってしたままでー、⑤までづらーとマウスをもってきて、一回指を離してB2からJ2を選択します。

そして、選択して青色かかっている枠の右下⑥にマウスカーソル:矢印↑を近づけると矢印↑が「十」になるので、

 

 

ポチーとしたまま、下におろします。

 

そうすると、コピーされますB2からJ2がB3からJ2にコピーされます。すると…

 

2009年4月30日の株価データや、その月の配当データも持ってきてくれます。

 

ミスターマーケット
おぉ!すごい!(自作自演w)

 

そして、いよいよ、集計シートを一気につくりあげる時がきました。

さきほどと同じように、オートフィル機能をつかってさらに下にコピーしていきます。

 

今度は⑦A2から⑧J3を選択して、

⑨で「十」にして、ずらーとしたにのばしてください(最新の年月まで)。

 

そうすると…

今回は2017年12月のデータまでをつくりました。KOからの配当受取金額は1300ドルにもなっていました!

 

 

全体をみると下↓のような感じです!

 

 

つ、ついに、

2009年から2017年までの株価推移や配当データが一気に集計されたのです!

ミスターマーケット
おつかれさまでした

 

あとはグラフをつくるだけです。下↓のような感じで!

 

 

 

グラフをつくるのはとっても簡単ですよ!

 

っとはいっても、少し疲れたので、

グラフ作成の説明は後日にしたいと思います。

 

ミスターマーケットの本日のノイズでした。

 

SPONSORED LINK

2018年もamazon prime dayがはじまっています!

 

2018年もわが社のamazonの年1イベントであるprime dayがはじまっています!

AMZN 

ちなみに、2017年のプライムデーでミスターマーケットが 購入したのが宅トレ器具のバーニングマシーン。


手でもっとグルグル回すと筋トレになるよってやつです。


youtubeの動画を見ながらグルグルグルグル回すんですけど、ムッチクチャ辛いです。10分の動画ですけど、胸や肩がパンパンになりますw 

リアルでもデブとか言われたことないので成果はほどほどあるんじゃないでしょーか? 



そして、あともう1つが、電動歯ブラシ。


もちろん、こちらも我が社P&Gのブラウンの電動歯ブラシです。 

長期投資するには健康が第1。そして、健康のために大切にしたいのが歯です。 

こちらはさっきの宅トレ器具と違って努力いらず。口の中を一周するだけで手動とは比較できないほどのツルツルさを味わえます。

ちゃんと口を閉じるのを忘れると洗面台が大変なことになるのは注意してくださいね。 

 

おっと、最後に忘れていけないのが、amazonギフトカードです。

5000円のギフトカードで最大1,000ポイントがつく、プライムデーの撒き餌(まきえさ)キャンペーン!!

 

Amazonギフト券(配送タイプ)5,000円購入で最大1,000ポイントキャンペーン

とりあえず、撒き餌キャンペーンのギフト券だけでも購入しておけばよいかなと思います。

在庫なくなり次第終了らしいのでとりあえず、amazonプライム会員なら詳細確認のうえ、要検討です!

コメントを残す

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

CAPTCHA


ABOUTこの記事をかいた人

どうも!ミスターマーケットです。 Mr.マーケットは30代半ばの普通の会社員です。20歳から株式投資をはじめ、現在は日本株と米国株で配当を中心に長期投資を行なっています。よろしくお願いします。