イエティの雪だるま投資記

20代社会人のお金と投資に関するブログです ツイッターもやってます。https://twitter.com/yuki_otoko7

Excel×株③エクセルでPF管理

こんばんは イエティです。

 

Excel×株関連記事」特集の第3回となる今回。テーマは「Excelを使ったPF管理」についてです。

 

私は、「100万円長期投資」企画にて、毎週末に成績画像を公開していますが、その画像はExcelで管理・更新しています。

 

なので、今回は画像を織り交ぜながら私の管理法を紹介します。(実際はマクロを使用していますが、今回はボリューム上省略。もし需要があれば完全版も紹介します。)

 

概要

今回は、イエティPFで管理している3つの要点を把握できる画像を作成します。

1.保有株管理

  • 保有銘柄、株数
  • 平均取得金額
  • 現在の価額
  • 評価損益
  • 先週との比較
  • 現金額、株式評価額、総資産、総損益

2.成績

  • 週間成績
  • 年初来成績
  • 運用来成績
  • 日経平均との比較

3.グラフ

 

以上です。項目は多いですがプロットさえ作れば毎週1,2分で更新できます。それでは実際に作ってみます。今回は例として今のイエティPFと同じ資産状態で7月8日にスタートしたと仮定します。

〈作り方〉

 1.まず入力用、計算用、表の3シートを作成、わかる項目を入れていきます。

(入力用)

f:id:ieteli:20170709125446j:image

(計算用)
f:id:ieteli:20170709125511j:image

(表)
f:id:ieteli:20170709125524j:image

なお、計算用シートは勝手に計算してもらうように項目以外は全て関数を入れましょう。(ちなみに週間成績だとD5セル=「B5÷B4-1」という風に簡単な割り算を入れています。)

 

2.それでは各シートを完成していきます。

①入力用

まずは、シートの薄赤色に関数を入れます。

  • 時価評価額=現在の価額×株数
  • 損益=時価評価額-(平均取得株価×株数)
  • 総資産=「=sum(時価評価額の範囲)+現金」

そして、左の黄色の欄に「PF総資産」、「日経平均」を入れたら完成です。(画像は関数が上手く動いているか確認するために来週分を適当に入れてます)
f:id:ieteli:20170709192037j:image

また、ちゃんと関数が入っていれば、計算用シートはこうなります。エラーは気にしなくて良いです。
f:id:ieteli:20170709192221j:image

②表

ここからがポイントです。入力用と計算用シートを上手く活かして分かりやすく、簡単に管理できるシートを作っていきます。

Point1 成績表示

まずは、成績データを表示させます。
f:id:ieteli:20170709191141j:image

まず、もし上記の画像のように7月15日でのデータを表すためには、上のボックスは

週間PF、運用来PF に0.4%

週間日経、運用来日経 に-1.1%

と表示させる必要があります。なので、下記の関数を入力します。


f:id:ieteli:20170709193953j:image

一見難しそうですが、データの入っている数を数えて、それに対応する場所をそれぞれ計算用シートから引っ張ってきているだけなので、理屈は簡単です。

Point 2 銘柄

あとは、画像のように関数を入れて、
f:id:ieteli:20170709200243j:image

グラフを入れれば完成です。

これで、背景をいじったり、見せ方をカスタマイズするといつもの成績画像になります。


f:id:ieteli:20170709200428j:image

まとめ

説明難しい! 本当は後半で関数一つ一つ説明を入れたかったのですが、書ききれないので画像にまとめる形となってしまいました… 分かりづらいと思いますが、一回見様見真似で作ってもらえれば、案外できるものなのでぜひ挑戦してもらえたらなと思います。(グラフもはいってなくてすみません…)

 

また、今回については

  • PF作成Excelシートの配布

などを後日、考えています。もっと関数や使い方などを分かりやすくして誰でも使えるような形で配布出来ればなと考えているので、少し時間を貰えればなと思います。

 

次回は番外編。

Yahooファイナンスを使ったPF管理についてです。

こちらも、簡単に管理できるようなシステムになっているので使い方などを記事化出来ればなと思います。

 

7月12日20時(頃😅)更新予定です。是非ご覧ください。