今回は、当ブログで配信しているテンプレートの中のコード表の作成方法について説明します。
これは、シニア投資初心者である私が資産寿命を延ばすためにはまずMy資産の見える化可視化が重要だと思い、試行錯誤しながらおこなっているMy資産の管理方法するときに使っているテンプレートの一部です。
このコード表(【B】コード表)は、当ブログで配信しているテンプレートの中にある【C】データベースからVLOOKUP関数で読み込むことができ、オリジナルな分析ができるようにという考えから作成しています。
この記事では
- テンプレートの中のコード表の作成方法
- 株式売買が終わり現在保有していない銘柄のコード表上の措置(削除していいのか否か?)
がわかります。
②番目については、結論から言うと、下記のとおり、その銘柄をコード表から削除せずにそのままコード表に残しておくようにしてください。
以下は、そのコード表の作成方法です。
目次
コード表の作成方法(株式投資Excelテンプレート用)
![コード表の作成方法(株式投資Excelテンプレート用)・コード表の作成方法](https://rakuenblog.com/wp-content/uploads/2022/10/image-3-1024x174.png)
上の図はMy資産の管理方法するときに使っているテンプレートの中にある【B】コード表の一部です。
コード表の最上段の項目欄は、1番左列の銘柄のコードと、2番目の列の銘柄名は基本ですが、そのほかは皆さんも自分の使い道に合わせてオリジナルの項目を作成してください。
コード表の列の項目は、私の場合、オリジナルのアイデアがあれば、順次、右の列へ追加しています。(※これはVLOOKUP関数の性質上、左から何列目かが重要となるので、順番を入れ替えることにより作業が繁雑になるからです。)
コード表の項目
「チェッカー・コード」欄と「銘柄」欄
1)コードが数値の場合は文字列化すること
当記事のテンプレートは、VLOOKUP関数を利用するため、チェッカーコード欄は必ず文字列を入力してください。数値になっているとエラー表示となります。(※例えば、下の図の「941」が数値になっていると「エラー表示」となり、「941」を文字列にすると「チャイナ・モバイル」を呼び込む)※Microsoftサポート:Excelで数値を文字列として設定する
2)表記のブレ対策など
【香港市場の場合】
![コード表の作成方法・表記のブレ対策など(香港市場の場合)](https://rakuenblog.com/wp-content/uploads/2022/10/image-1.png)
日本市場、米国市場の場合、株式やETFの表記のブレにはないと思います。ただ私の場合だと、香港市場の商品でブレが発生しましたので、下記の通り、対策を取っています。事例で説明すると、図の通り、
- 「Tracker Fund香港」は、「02800」と「2800」
- 「チャイナ・モバイル」は、「00941」と「941」
とそれぞれ同じ銘柄なのにコード番号が違うものが2つあります。これは、証券会社各社やマネーフォワードMEのそれぞれのWEBサイトで商品は同じなのにコード番号の表記の違いがあり、それをそのままコピペしてコード表に貼り付けています。そのため、同一銘柄なのに、2つのコード番号の表記がある商品の名称を「銘柄」欄で統一化しています。
【投資信託の場合】
![コード表の作成方法(株式投資Excelテンプレート用)投資信託の場合](https://rakuenblog.com/wp-content/uploads/2022/10/image-2.png)
・投資信託にチェッカーやコード番号が表記されていないことへの対策
投資信託の場合、各証券会社やマネーフォワードMEのWEBサイト上に保有商品のコード番号等の表記がありませんでした。よって、その対策は、投資信託の名称をそのまま「チェッカーコード」欄と「銘柄」欄の2カ所へコピペすることにしました。
・投資信託の表記のブレ対策
投資信託の場合、各証券会社やマネーフォワードMEのWEBサイト上に
- 保有商品の名称(銘柄)は、同一であっても表記にブレがある場合
- 名称変更があった商品は、同一であっても時期により名称(銘柄)が違う場合
がありました。よって、その対策は、下図のように投資信託の名称を「銘柄」欄で統一化することにしました。(※最下段のように名称変更があった商品は、コード表により新しい商品名に統一する。)
![コード表の作成方法(株式投資Excelテンプレート用)投資信託の場合:銘柄欄で統一](https://rakuenblog.com/wp-content/uploads/2022/10/image-4.png)
したがって、株式や投資信託などの大きな括りで集計するときは問題ありませんが、個別の商品名で集計するときは「銘柄」欄を基準し集計してくださいね。
私が随時に作成した項目とその注意事項
- 現在までに作成し公表している項目~「3区分・大」「3区分・中」「セクター・1」「セクター・2」「通貨」「対象国など」「高配当」「口座区分」「個別・ETF・投信・ほか」「為替リスク」「暴落リスク」「為替・係数」「暴落・係数」「番号」
- これらは、私なりの考えで作成した項目です。最初は「3区分・大」「3区分・中」のみでしたが、ピボットテーブルでの集計方法を考えていくうちに段々と項目が増え、順次項目を追加していきました。
- VLOOKUP関数を使用するので、項目を追加するときは、原則、右へ右へと増やしていきました。(※これはVLOOKUP関数の性質上、左から何列目(列番号)かが重要となるので、順番を入れ替えることにより作業が繁雑になるからです。)※Microsoftサポート=VLOOKUP (検索対象、検索する場所、返される値を含む範囲内の列番号、1/TRUE または 0/FALSE として示される近似または完全一致 – を返します)
- 追加は、「右へ右へ」といいましたが、最右翼は「番号」欄として通し番号を付けておいた方がコード表を管理するのになにかと便利です。
コード表のテーブル化とフィルター機能の利用
1)コード表のテーブル化
Excelには表形式のデータを扱うための「テーブル」という機能があり、これを使うことで
- セルの書式設定や色分けなどを自動化できる。
- セル範囲をテーブルに変換することで、範囲指定することなくテーブル名で関数の利用ができる。
- データの増減も簡単である。
など便利な機能が利用できます。※Microsoftサポート:Excelでテーブルを作成する
2)コード表のフィルター機能
Excelには表形式のデータを扱うための「フィルター機能」があり、これを使うことで、
- データの絞り込みができる
- データの追加時に利用できる
など便利な機能が利用できます。コード表の項目欄についている「▽」のものです。
※Microsoftサポート:Excelで範囲または表のデータをフィルター処理する
その他
NISAの区分はコード表ではできない
コード表では、NISAの区分はできません。 当ブログのテンプレートでは、【C】データベースに「NISA」の項目を入れていますので、その項目で
- フィルター機能による絞り込み
- ピボットテーブルによる集計
等ができ大変便利ですので、そちらを利用してください。
補助表の作成(SBIネオモバイル証券の場合のみ、ヤフーファイナンスと連携させデータを取得)
SBIネオモバイル証券については、コード表以外に補助表を別シートに作成しており、ヤフーファイナンスとの連携を図って数値(株価の現在値など)を取り込んでいますので、それを利用してください。
![【図解Excelテンプレート】【ネオモバ×Yahooファイナンス連携】株投資信託・資産運用・資産管理ポートフォリオの作成と基本テンプレートの使い方(SBI・楽天・ネオモバイル証券3社合体版・NISA対応)<無料>](https://rakuenblog.com/wp-content/uploads/2022/09/20220920-portfolio-mieruka-excel-template-howtouse-rakuten-sbi-neomobilesyoken-use-yahoofinance-320x180.jpg)
今回の記事のまとめ
今回の記事のまとめじゃな
- 株式投資Excelテンプレートの中のコード表の作成方法
- コードは、必ず文字列化しておく
- 同じ商品でも各社で表記のブレがあるので、銘柄欄で名称を統一させる
- コード表は、テーブル化しフィルター機能を利用する
- 株式売買が終わり現在保有していない銘柄のコード表上の措置(削除していいのか否か?)
- 削除せずにそのままコード表に残しておく
このコード表は、全て同じファイルの一つの別シートに作成して配信していますが、これは、配信するため便宜上おこなっています。本来は、別ファイルに保存して、全てその一つのコード表を読みにいくようにした方がいいと思います。この点については、別途記事にします。
【関連記事】
【要注意!】エクセル・スプレッドシート及びピボットテーブルの操作方法で注意する点
![要注意!【エクセル:エラー事例】株管理ポートフォリオ(円グラフ)作成用のエクセル・スプレッドシート及びピボットテーブルの使い方で注意する点](https://rakuenblog.com/wp-content/uploads/2022/11/20221123-portfolio-matome-huzoroi-excel-spreadsheet-pivot-becareful-320x180.jpg)
コード表に、投資信託の内訳を任意の銘柄として追加した記事
![投資信託の内訳を含めた株管理ポートフォリオ(円グラフ)の作り方とExcel基本テンプレートの使い方(SBI証券版・NISA対応)<無料>](https://rakuenblog.com/wp-content/uploads/2022/11/20221119-portfolio-mieruka-excel-howtomake-sbisec-toushin-utiwake-1-320x180.jpg)
株管理ポートフォリオ作成の基本記事は(他社分あり)
![【いまさら聞けない基本の「き」】エクセルで株管理ポートフォリオ(円グラフ)の作り方とExcelテンプレートの使い方の基本の記事5本<全て無料>(SBI・楽天証券・2024年の新NISA移行対応予定)※Googleスプレッドシート版あり](https://rakuenblog.com/wp-content/uploads/2022/11/20221130-portfolio-matome-kihonnoki-excel-spreadsheet-320x180.jpg)
親子2世代運用についてはコチラ↓
![老後資金は親子2世代運用がおすすめ!Excelテンプレートで株管理ポートフォリオの作成【SBI証券・楽天証券・NISA対応】<無料配信>Googleスプレッドシート版も有](https://rakuenblog.com/wp-content/uploads/2022/11/20221109-portfolio-mieruka-excel-spreadsheet-2sedai-unyou-320x180.jpg)
リスク別テンプレート一覧についてはコチラ↓
![【おすすめ】リスク別・株管理ポートフォリオの作成とExcelテンプレートの使い方の記事一覧(SBI・楽天証券・NISA対応・Googleスプレッドシートあり)<無料>](https://rakuenblog.com/wp-content/uploads/2022/09/20220925-portfolio-mieruka-excel-howtouse-template-article-list-of-risk-type-320x180.jpg)
これまでに公開したテンプレートに関する記事一覧はコチラです。
※【楽天市場】楽天市場で「エクセル・ピボットテーブル」関連の商品購入はこちらから↗
※【メルカリ】メルカリで「エクセル・ピボットテーブル」関連の商品購入はこちらから↗