2-1-1.基本テンプレートの作り方 PR

コード表の作成方法(株管理ポートフォリオ作成用テンプレート)

株式投資【Excelテンプレート】コード表の作り方
記事内に商品プロモーションを含む場合があります

 今回は、当ブログで配信しているテンプレートの中のコード表の作成方法について説明します。

 これは、シニア投資初心者である私が資産寿命を延ばすためにはまずMy資産の見える化可視化が重要だと思い、試行錯誤しながらおこなっているMy資産の管理方法するときに使っているテンプレートの一部です。

かん

 このコード表(【B】コード表)は、当ブログで配信しているテンプレートの中にある【C】データベースからVLOOKUP関数で読み込むことができ、オリジナルな分析ができるようにという考えから作成しています。

 この記事では

  1. テンプレートの中のコード表の作成方法
  2. 株式売買が終わり現在保有していない銘柄のコード表上の措置(削除していいのか否か?)

がわかります。 

 ②番目については、結論から言うと、下記のとおり、その銘柄をコード表から削除せずにそのままコード表に残しておくようにしてください。

■売買が終わり現在保有していない銘柄のコード表上の措置■

【結果】削除せずにそのままコード表に残しておく

【理由】時系列で過去からの推移を調べるときに必要になるため。(例)為替リスク割合や金額についてMy資産の推移を見ようとしても、既にコード表から削除していれば、数値は残っていてもVLOOKUP関数でコード表から呼び込むことができずエラー表示となってしまう。

 以下は、そのコード表の作成方法です。

コード表の作成方法(株式投資Excelテンプレート用)

コード表の作成方法(株式投資Excelテンプレート用)・コード表の作成方法
※【B】コード表(上記はコード表の一部です)

 上の図はMy資産の管理方法するときに使っているテンプレートの中にある【B】コード表の一部です。

かん

 コード表の最上段の項目欄は、1番左列の銘柄のコードと、2番目の列の銘柄名は基本ですが、そのほかは皆さんも自分の使い道に合わせてオリジナルの項目を作成してください。

 コード表の列の項目は、私の場合、オリジナルのアイデアがあれば、順次、右の列へ追加しています。(※これはVLOOKUP関数の性質上、左から何列目かが重要となるので、順番を入れ替えることにより作業が繁雑になるからです。)

コード表の項目

「チェッカー・コード」欄と「銘柄」欄

1)コードが数値の場合は文字列化すること

 当記事のテンプレートは、VLOOKUP関数を利用するため、チェッカーコード欄は必ず文字列を入力してください。数値になっているとエラー表示となります。(※例えば、下の図の「941」が数値になっていると「エラー表示」となり、「941」を文字列にすると「チャイナ・モバイル」を呼び込む)※Microsoftサポート:Excelで数値を文字列として設定する

2)表記のブレ対策など

【香港市場の場合】

コード表の作成方法・表記のブレ対策など(香港市場の場合)

 日本市場、米国市場の場合、株式やETFの表記のブレにはないと思います。ただ私の場合だと、香港市場の商品でブレが発生しましたので、下記の通り、対策を取っています。事例で説明すると、図の通り、

  • 「Tracker Fund香港」は、「02800」と「2800」
  • 「チャイナ・モバイル」は、「00941」と「941」

とそれぞれ同じ銘柄なのにコード番号が違うものが2つあります。これは、証券会社各社やマネーフォワードMEのそれぞれのWEBサイトで商品は同じなのにコード番号の表記の違いがあり、それをそのままコピペしてコード表に貼り付けています。そのため、同一銘柄なのに、2つのコード番号の表記がある商品の名称を「銘柄」欄で統一化しています。

【投資信託の場合】

コード表の作成方法(株式投資Excelテンプレート用)投資信託の場合

・投資信託にチェッカーやコード番号が表記されていないことへの対策

 投資信託の場合、各証券会社やマネーフォワードMEのWEBサイト上に保有商品のコード番号等の表記がありませんでした。よって、その対策は、投資信託の名称をそのまま「チェッカーコード」欄と「銘柄」欄の2カ所へコピペすることにしました。

・投資信託の表記のブレ対策

 投資信託の場合、各証券会社やマネーフォワードMEのWEBサイト上に

  • 保有商品の名称(銘柄)は、同一であっても表記にブレがある場合
  • 名称変更があった商品は、同一であっても時期により名称(銘柄)が違う場合

がありました。よって、その対策は、下図のように投資信託の名称を「銘柄」欄で統一化することにしました。(※最下段のように名称変更があった商品は、コード表により新しい商品名に統一する。)

コード表の作成方法(株式投資Excelテンプレート用)投資信託の場合:銘柄欄で統一
※ 左:「チェッカーコード」欄           右:「銘柄」欄
かん

 したがって、株式や投資信託などの大きな括りで集計するときは問題ありませんが、個別の商品名で集計するときは「銘柄」欄を基準し集計してくださいね。

私が随時に作成した項目とその注意事項

  • 現在までに作成し公表している項目~「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対応)<無料>
【図解Excelテンプレート】【ネオモバ×Yahooファイナンス連携】株投資信託・資産運用・資産管理ポートフォリオの作成と基本テンプレートの使い方(SBI・楽天・ネオモバイル証券3社合体版・NISA対応)<無料> 当ブログでは、Excelによるピボットテーブルだけで株式ポートフォリオの作成と見える化、可視化ができる、シニア投資初心者にも分かりやすい無料テンプレートを配信しています。  当ブログのExcelテンプレートは、My資産の暴落リスクや為替リスクの保有割合を出したり、My資産の同時リスク(暴落リスク、為替リスク)発生を想定したシミュレーション等の予測作業も可能です  また複数証券会社の合体や家族まとめて一体化などいろいろ自分の思い通りオリジナルな見える化も可能です。  Excel版以外にもGoogleスプレッドシート版や、ExcelとマネーフォワードMEを融合させたバージョンもあります。  今回の記事は、ヤフーファイナンスのポートフォリオの様式変更に伴うSBIネオモバイル証券のテンプレートのバージョンアップ版を紹介しています。SBI証券と楽天証券の3社合体バージョンで、SBIネオモバイル証券の部分のみが変更されています。NISA対応版です。  これらのテンプレートが何か役に立てば幸いと思っています。...

今回の記事のまとめ

じゃなさん

今回の記事のまとめじゃな

  1. 株式投資Excelテンプレートの中のコード表の作成方法
    1. コードは、必ず文字列化しておく
    2. 同じ商品でも各社で表記のブレがあるので、銘柄欄で名称を統一させる
    3. コード表は、テーブル化フィルター機能を利用する
  2. 株式売買が終わり現在保有していない銘柄のコード表上の措置(削除していいのか否か?)
    1. 削除せずにそのままコード表に残しておく
かん

 このコード表は、全て同じファイルの一つの別シートに作成して配信していますが、これは、配信するため便宜上おこなっています。本来は、別ファイルに保存して、全てその一つのコード表を読みにいくようにした方がいいと思います。この点については、別途記事にします。

【関連記事】

【要注意!】エクセル・スプレッドシート及びピボットテーブルの操作方法で注意する点

要注意!【エクセル:エラー事例】株管理ポートフォリオ(円グラフ)作成用のエクセル・スプレッドシート及びピボットテーブルの使い方で注意する点
要注意!【エクセル:エラー事例】株管理ポートフォリオ(円グラフ)作成用のエクセル・スプレッドシート及びピボットテーブルの使い方で注意する点 今回の記事は、ExcelのピボットテーブルをGoogleスプレッドシートにコンバートすることに関する悩みや、ピボットテーブルの参照元データの範囲指定の失敗等に対し回答するものです。  当ブログの株式投資ExcelテンプレートをGoogleスプレッドシートにコンバートする際、ピボットテーブルの集計で同じようなミスが何度も出てくると思われます。  私がそのようなときに解決した方法、その原因と対策を記事にしました。参考になれば幸いです。...

 コード表に、投資信託の内訳を任意の銘柄として追加した記事

投資信託の内訳を含めた株管理ポートフォリオ(円グラフ)の作り方とExcel基本テンプレートの使い方(SBI証券版・NISA対応)<無料>
投資信託の内訳を含めた株管理ポートフォリオ(円グラフ)の作り方とExcel基本テンプレートの使い方(SBI証券版・NISA対応)<無料> 今回の記事は、投資信託を含めたマイ資産のポートフォリオを作るのが面倒だと悩んでいる方へ向けたものです。  一度作成してしまえば次回からは意外と簡単にポートフォリオができてしまいます。  私が現に使っているExcelテンプレートを無料で公開しますので、ぜひお使いください。ピボットテーブルで集計をしており、使いやすさ、分かりやすさを追求して作成しております。参考になれば幸いです。...

株管理ポートフォリオ作成の基本記事は(他社分あり)

【いまさら聞けない基本の「き」】エクセルで株管理ポートフォリオ(円グラフ)の作り方とExcelテンプレートの使い方の基本の記事5本<全て無料>(SBI・楽天証券・2024年の新NISA移行対応予定)※Googleスプレッドシート版あり
【2024年の新NISA移行対応】【いまさら聞けない基本の「き」】エクセルで株管理ポートフォリオ(円グラフ)の作り方とExcelテンプレートの使い方の基本の記事4本<全て無料>(SBI・楽天証券)※Googleスプレッドシート版あり 今回の記事は、いまさら聞けない基本の「き」として、エクセルによるSBI証券と楽天証券とSBIネオモバイル証券の株管理ポートフォリオの作り方とテンプレートの使い方の基本記事を集めました。途中からブログ記事を読んだ方で、基本に戻って作り方や使い方を知りたいと悩んでいる方へ向けたものです。  一度作成してしまえば次回からは意外と簡単にポートフォリオができてしまいます。  全て無料配信です。  私が現に使っているExcelテンプレートを多数公開していますので、ぜひお使いください。ピボットテーブルで集計をしており、使いやすさ、分かりやすさを追求して作成しております。  参考になれば幸いです。...

親子2世代運用についてはコチラ↓

老後資金は親子2世代運用がおすすめ!Excelテンプレートで株管理ポートフォリオの作成【SBI証券・楽天証券・NISA対応】<無料配信>Googleスプレッドシート版も有
老後資金は親子2世代運用がおすすめ!Excelテンプレートで株管理ポートフォリオの作成【SBI証券・楽天証券・NISA対応】<無料配信>Googleスプレッドシート版も有 今回の記事は、親子2世代運用を目指している定年後のシニアの方やシニアを親に持つ方へむけての記事です。NISAにも対応しています。SBI証券や楽天証券の複数口座や複数名義など集計に悩んでいる方は、私が現に使っているExcelテンプレートを公開しますので、ぜひお使いください。ピボットテーブルで集計をしており、使いやすさ、分かりやすさを追求して作成しております。参考になれば幸いです。...

 リスク別テンプレート一覧についてはコチラ↓

【おすすめ】リスク別・株管理ポートフォリオの作成とExcelテンプレートの使い方の記事一覧(SBI・楽天証券・NISA対応・Googleスプレッドシートあり)<無料>
【おすすめ】リスク別・株管理ポートフォリオの作成とExcelテンプレートの使い方の記事一覧(SBI・楽天証券・NISA対応・Googleスプレッドシートあり)<無料> 当ブログでは、ExcelやGoogleスプレッドシートによるコピペとピボットテーブルだけで株式ポートフォリオ等の見える化、可視化ができる、シニア投資初心者にも分かりやすい株式投資信託・資産運用管理用の無料テンプレートを配信しています。テンプレートは一部を除きNISAにも対応しています。  当ブログのExcelやGoogleスプレッドシートのテンプレートは、My資産の暴落リスクや為替リスクの保有割合を出したり、My資産の同時リスク(暴落リスク、為替リスク)発生を想定したシミュレーション等の予測作業も可能です  また複数証券会社の合体や家族まとめて一体化などいろいろ自分の思い通りオリジナルな見える化も可能です。  Excel版以外にもGoogleスプレッドシート版や、ExcelやGoogleスプレッドシートとマネーフォワードMEを融合させたバージョンもあります。  今回の記事は、これら配信しているテンプレートを「リスク種別・テンプレート」として記事を紹介しています。  これらのテンプレートが何か役に立てば幸いと思っています。...

 これまでに公開したテンプレートに関する記事一覧はコチラです。

※【楽天市場】楽天市場で「エクセル・ピボットテーブル」関連の商品購入はこちらから↗

※【メルカリ】メルカリで「エクセル・ピボットテーブル」関連の商品購入はこちらから↗

ABOUT ME
CAN(かん)
●60歳でサラリーマン定年退職後、10年目。「今日が一番若い日」と言いながらブログを始めたブログ初心者のオヤジです。●3つの証券会社分&家族全員分&100銘柄分をまとめて見える化できるExcelテンプレート無料配信中!
こちらの記事もおすすめ!
【おすすめ】公開記事<全部>「株管理ポートフォリオ(円グラフ)作成&株価暴落/為替円高リスクの見える化ができるExcelテンプレートの使い方」記事一覧(SBI・楽天証券・NISA対応・Googleスプレッドシート)<無料> 2.株管理ポートフォリオとテンプレート

【おすすめ】公開記事<全部>「株管理ポートフォリオ(円グラフ)作成&株価暴落/為替円高リスクの見える化ができるExcelテンプレートの使い方」記事一覧(SBI・楽天証券・NISA対応・Googleスプレッドシート)<無料>

2022年9月20日
らくぶる
じゃなさん 今回は、公開してる記事全部のリストってことじゃな? かん そうです。今回の記事は、2022年4月から私が配信してきたテ …
【おすすめ】マネーフォワードMEの便利な使い方:【図解エクセル】株価暴落を想定したポートフォリオの作り方とテンプレートの使い方(リバランスのやり方)<無料>(SBI・楽天証券等・1人用) 2-2-1.暴落下落リスク

【おすすめ】マネーフォワードMEの便利な使い方:【図解エクセル】株価暴落を想定したポートフォリオの作り方とテンプレートの使い方(リバランスのやり方)<無料>(SBI・楽天証券等・1人用)

2022年5月21日
らくぶる
じゃなさん 今度は 「SBI証券」+「楽天証券」+「SBIネオモバイル証券」版のマネーフォワードMEを使った株価暴落想定し …