上の表は、僕が日常的に株取引を管理している表です。名付けて「取引記録Excel管理表」といいます。むずかしいマクロとか数式は一切無しで、自分の取引を客観的に分析するのに便利なツールです。
この記事では、「取引記録Excel管理表」をExcelでイチから作成する手順についてご紹介します。参考になれば幸いです。
※過去にこの管理表についてご紹介したブログは下記リンクからどうぞ。
過去記事「取引記録Excel管理表」
取引記録Excel管理表の作成手順
では、取引記録Excel管理表を作成していきましょう。
今回はMicrosoft Office 2013のExcelを使います。
Excelのバージョンによっては、ツールバーや設定画面が違う場合もありますので、ご了承下さい。
1. ブック・シートの作成
↓ まずはブックを作成します。デスクトップやフォルダの余白を右クリックして「新規作成」-「Microsoft Excelワークシート」で作成できます。
↓ ファイル名を分かりやすい名前に変えておきます。ファイル名の先頭に年月日の数字をつけておくと管理しやすいです。（例）「20180722取引記録Excel管理表.xlsx」
↓ Excelを開きます。
↓ わかりやすいシート名をつけておきます。
僕の場合は「日本国内株」と「米国株」の2種類を取引していて、取引通貨が違うのでそれぞれ別シートで管理しています。日本国内株のシートは「JPY」（JAPANのYenの略）」、米国株のシートは「USD」（USAのDollarの略）としています。
2. 表の作成
↓ まずは罫線を引いていきます。A列からI列まで、10行分くらいに格子の罫線をつけます。
↓ 先頭行に項目名を入力します。
各列の項目名一覧です。
| 列名 | 項目名 |
| A列 | 銘柄 |
| B列 | コード |
| C列 | 買付日 |
| D列 | 買値（\） |
| E列 | 売却日 |
| F列 | 売値（\） |
| G列 | 日数 |
| H列 | 損益率 |
| I列 | 損益額（\） |
ぜったいこの名前じゃないといけないことはないので、わかりやすい名前を自由につけてください。
3. セル書式設定
各セルの書式を設定していきます。
書式設定をしておくと、見栄えがよくなったり、入力した数値から自動計算してくれたりするので、ぜひ設定しておきましょう。
↓ 設定の一覧です。
| 列名 | 項目名 | セルの書式設定の表示形式 | 数式（2行目の場合） |
| A列 | 銘柄 | 標準 | （無し） |
| B列 | コード | 標準 | （無し） |
| C列 | 買付日 | ユーザー定義：「yyyy/mm/dd」 | （無し） |
| D列 | 買値（\） | 通貨：「小数点以下の桁数：0」「通貨記号：なし」 | （無し） |
| E列 | 売却日 | ユーザー定義：「yyyy/mm/dd」 | （無し） |
| F列 | 売値（\） | 通貨：「小数点以下の桁数：0」「通貨記号：なし」 | （無し） |
| G列 | 日数 | 標準 | 数式「=E2-C2+1」 |
| H列 | 損益率 | ユーザー定義：「[黒]0.0%;[赤]-0.0%」 | 数式「=(F2/D2)-1」 |
| I列 | 損益額（\） | ユーザー定義：「[黒]#,##0_ ;[赤]-#,##0」 | 数式「=F2-D2」 |
上の設定一覧は、Excelの2行目に対して行う設定です。なので数式は「=E2-C2+1」のように2行目を指定する式になっています。（下の図で少し濃い箇所がExcelの2行目にあたります）
[参考] 設定一覧の「セルの書式設定」画面
参考までに、セルの書式設定画面の表示形式タブの状態です。
セルの書式「標準（銘柄、コード、日数）」：A、B、G列
セルの書式「通貨（買値/売値）」：D、F列
セルの書式「ユーザー定義（買付日/売却日）」：C、E列
セルの書式「ユーザー定義（損益率）」：H列
セルの書式「ユーザー定義（損益額）」：I列
[参考] 設定一覧の「数式」画面
参考までに、各セルの数式の中身です。
数式「日数」：G列
数式「損益率」：H列
数式「損益額」：I列
4. 設定を確認し、体裁をととのえる
書式設定と数式が完成したら、何か銘柄の情報を入力してみます。
A列からF列までの情報を入力すると、G列（日数）、H列（損益率）、I列（損益額）が自動計算されます。
↓ たとえばトヨタ自動車とソニーを取引した場合の情報を入れてみます。儲かった場合は損益率、損益額が黒文字で、損した場合は赤文字で表示されれば設定OKです。
↓ あとはお好みで、1行目のタイトルのセルに色をつけたり、太字にしたり、フォントの大きさを変えたり、あなたの美的センスでカスタマイズして下さい！
5. 運用ルールについて
上の表は、僕が実際に株式取引で使ってる取引記録Excel管理表です。
僕なりの細かい運用ルールをご紹介しておきます。ご参考までにどうぞ。
■取引を終えたらセルを灰色に塗る
買付と売却が終わった銘柄は、その後に情報更新することが無いので、取引終了銘柄として分かりやすくなるよう、セルをすべて灰色に塗りつぶしておきます。
■ナンピン買いしたら、買付金額を「平均取得単価」の金額に手修正する
例えば「トヨタ自動車（7203）」を7000円で100株買ったとして、そのあと株価が下がったので6000円で100株ナンピン買いしたとします。このとき管理表の行を1行増やし合計2行としますが、D列「買値（\）」は「7000」と「6000」ではなく、2行とも平均取得単価の「6500」に修正します。最終的な損益計算をしやすくするためです。
平均取得単価は、証券会社サイトの保有銘柄情報に必ず掲載されていますので、その金額を参照します。
■買値、売却日、売値（D、E、F列）のセルに色を塗っておく
なぜD、E、F列だけに色を塗っておくかというと、記載の要/不要をわかりやすくするためです。
具体的には以下の3つのブロックに分けて考えます。
- ブロック①
銘柄、コード、買付日（A、B、C列）：買付けた日以降は更新することがない列 - ブロック②
買値、売却日、売値（D、E、F列）：ナンピン買いをしたときや最終的に売却したタイミングで更新することがある列 - ブロック③
日数、損益率、損益額（G、H、I列）：自動計算の数式が入っているので、手入力したらダメな列
いろいろ運用を試してみて、この色分けが使いやすいと思いました。
■現物、信用、NISAなど見分けらえるようにしておく
管理表のA列「銘柄」の記載を次のようにしています。
- 現物取引株の場合、「キユーピー」
- 信用取引株の場合、「キユーピー〔信用〕」
- NISA口座で買った場合、「キユーピー〔NISA〕」
なぜこんな記載をするかというと、同じ銘柄でも現物・信用・NISAは口座が別々で、資産評価も別々で計算されるため、見分けがつくようにしています。
