Excelを活用した最新の株式ポートフォリオ管理
はじめに
株式投資を行う上で、自分の保有銘柄を適切に管理することは非常に重要です。本記事では、VBAの知識を必要とせず、最新のExcel機能を活用して効率的に株式ポートフォリオを管理する方法を紹介します。2000年の初版から大幅に更新し、現在のExcelの機能と東証（JPX）のデータ提供方法に合わせた内容になっています。
1. 上場銘柄情報の取得
まずは、日本取引所グループ（JPX）のウェブサイトから最新の上場銘柄情報を取得します。
1.1 JPXウェブサイトからの直接ダウンロード
- JPX公式サイト（https://www.jpx.co.jp/）にアクセスします
- メニューから「マーケット情報」→「上場銘柄一覧」を選択します
- 「東証上場銘柄一覧」のExcelファイルまたはCSVファイルをダウンロードします
- 通常は毎月初旬に更新されています
1.2 ExcelのWebクエリを使ったデータ取得（直接取得方法）
東証の銘柄情報を直接Excelに取り込む方法も有効です：
- Excelを起動し、新規ワークブックを作成します
- 「データ」タブから「データの取得と変換」→「Webから」を選択します
- 以下のURLを入力します（※URLは変更されている可能性があるため、JPXサイトで最新リンクを確認してください）
https://www.jpx.co.jp/markets/statistics-equities/misc/01.html - 「Power Queryエディター」が起動したら、表示されるデータプレビューから必要な「東証上場銘柄一覧」テーブルを選択します
- 「ホーム」タブの「閉じて読み込む」をクリックして、データをExcelに取り込みます
2. Power Queryを使ったデータの加工
取得した上場銘柄データは、そのままでは使いにくい場合があります。Power Queryを使って効率的に加工しましょう。
2.1 不要な列の削除と列名の変更
- 取り込んだデータシート上で、任意のセルをクリックします
- 「クエリ」タブ（または「データ」タブの「クエリ」グループ）から「クエリの編集」を選択します
- Power Queryエディターが起動したら以下の操作を行います：
- 不要な列を右クリックして「削除」を選択（例：33業種コードなど）
- 列名を変更する場合は、列ヘッダーをダブルクリックして新しい名前を入力
- 日付形式や数値形式に変換したい列は、列ヘッダーを右クリック→「データ型の変更」から適切な形式を選択
2.2 銘柄情報のフィルタリング
特定の市場（プライム、スタンダード、グロース）や業種に絞り込みたい場合：
- Power Queryエディターで、フィルタリングしたい列のヘッダーにあるフィルターアイコン（▼）をクリックします
- 希望する条件にチェックを入れて「OK」をクリックします
- 複数の条件でフィルタリングする場合は、他の列でも同様の操作を行います
2.3 加工したデータの読み込み
データ加工が完了したら：
- 「ホーム」タブの「閉じて読み込む」をクリックします
- 加工済みデータがExcelシートに読み込まれます
3. 株価データの取得
次に、株価データを取得する方法を紹介します。複数の方法がありますので、状況に応じて最適なものを選んでください。
3.1 STOCK関数（Microsoft 365サブスクリプション向け）
Microsoft 365サブスクリプションユーザーは、Excel内蔵の「STOCK」関数を使用できます：
- =STOCK(“証券コード.T”, “属性”)の形式で関数を入力します
- 例：トヨタ自動車の現在価格を取得する場合
=STOCK("7203.T", "price") - 証券コードの後に
.Tを追加することで東証の銘柄として認識されます
- 例：トヨタ自動車の現在価格を取得する場合
- 属性には以下が指定可能です：
- “price” – 現在の株価
- “open” – 始値
- “high” – 高値
- “low” – 安値
- “prev_close” – 前日終値
- “volume” – 出来高
3.2 minokamo.tokyoからのデータ取得
- 下記URLから最新の株価データを取得できます：あくまでサンプル
https://minokamo.tokyo/file/stock/zk.xlsx - このデータを使って銘柄情報と組み合わせる方法：
- 「データ」タブから「データの取得と変換」→「Webから」を選択
- 上記URLを入力してデータを取得
- 銘柄情報と同様にPower Queryで必要に応じて加工
3.3 Yahoo!ファイナンスなど外部サイトからのデータ取得
- Yahoo!ファイナンスなどの株価情報サイトからCSVデータをダウンロードできます
- 「データ」タブから「データの取得と変換」→「ファイルから」→「テキスト/CSV」を選択
- ダウンロードしたCSVファイルを選択して取り込みます
4. ポートフォリオ管理表の作成
銘柄情報と株価データを取得できたら、実際のポートフォリオ管理表を作成していきます。
4.1 基本的なポートフォリオ管理表
- 新しいシートを作成し、以下の列を設定します：
- 銘柄コード
- 銘柄名
- 業種
- 市場区分
- 取得価格
- 取得数量
- 取得日
- 現在価格
- 評価額
- 損益
- 損益率（％）
- 自分が保有する銘柄の情報を入力します（銘柄コード、取得価格、取得数量、取得日）
- 最新の銘柄情報から情報を取得する関数を設定します：
- 銘柄名を取得するXLOOKUP関数の例：
=XLOOKUP(A2, 銘柄情報シート!$A:$A, 銘柄情報シート!$B:$B, "不明") - ※XLOOKUP関数が使えない古いバージョンのExcelの場合はVLOOKUPを使用：
=VLOOKUP(A2, 銘柄情報シート!$A:$B, 2, FALSE)
- 銘柄名を取得するXLOOKUP関数の例：
- 評価額、損益、損益率を計算する数式を設定します：
- 評価額 = 現在価格 × 取得数量
- 損益 = 評価額 – (取得価格 × 取得数量)
- 損益率 = 損益 ÷ (取得価格 × 取得数量) × 100
4.2 高度なポートフォリオ管理機能の追加
基本的な管理表に以下の機能を追加するとより便利になります：
- 総評価額と総損益のサマリー：
- SUMIFSやSUBTOTAL関数を使用して合計を表示
- 業種別や市場区分別のサブ合計を表示
- 条件付き書式を使った視覚化：
- 「ホーム」タブの「条件付き書式」を使用
- 例：損益率がプラスの場合は緑、マイナスの場合は赤で表示
- タイムスタンプの追加：
- 管理表の上部に更新日時を表示（TODAY関数やNOW関数を使用）
- 例：
=TEXT(NOW(), "yyyy年mm月dd日 hh:mm")
5. 最新Excelの高度な機能を活用した分析
Excel 2019以降や Microsoft 365では、強力な新機能が追加されています。これらを活用してさらに高度な分析を行うことができます。
5.1 XLOOKUP関数による柔軟な検索
=XLOOKUP(検索値, 検索範囲, 戻り値の範囲, [検索値が見つからない場合], [一致モード], [検索順序])例：
=XLOOKUP(A2, 銘柄情報シート!$A:$A, 銘柄情報シート!$B:$B, "不明", 0)5.2 FILTER関数によるデータフィルタリング
=FILTER(範囲, 条件, [空の場合])例：特定業種の銘柄だけを抽出
=FILTER(B2:F100, C2:C100="情報・通信業", "該当なし")5.3 SORT関数によるデータの並べ替え
=SORT(範囲, [並べ替え基準の列], [並べ替え順序], [列ごとの並べ替え])例：損益率の高い順に並べ替え
=SORT(A2:K20, 11, -1)5.4 ピボットテーブルを使った業種別分析
- 「挿入」タブから「ピボットテーブル」を選択
- データ範囲を選択し、新しいシートに作成
- ピボットテーブルのフィールドリストから：
- 行に「業種」をドラッグ
- 値に「評価額」と「損益」をドラッグ
- 「分析」タブから「スライサー」を追加して、より柔軟なフィルタリングを実現
6. データの自動更新とセキュリティ
6.1 クエリの自動更新設定
- 「データ」タブから「接続の管理」を選択
- 更新したい接続を選択し、「プロパティ」をクリック
- 「使用方法」タブで「バックグラウンドで更新する」にチェック
- 「更新」タブで自動更新の頻度を設定（例：ブックを開いたときに更新）
6.2 セキュリティの考慮点
- 外部データソースへの接続を含むブックは保存時に警告が表示されることがあります
- 「信頼できるドキュメント」として設定することで警告を減らせます
- 重要なポートフォリオ情報を含むブックはパスワード保護を検討してください：
- 「ファイル」→「情報」→「ブックの保護」→「パスワードを使用して暗号化」
6.3 バックアップの重要性
- 定期的にバックアップを作成（OneDriveなどのクラウドストレージを活用）
- 重要な更新の前には「名前を付けて保存」で別バージョンを保存
7. まとめと応用例
7.1 この管理方法のメリット
- VBAを使わずに最新の株価データを取得できる
- Power Queryによるデータ加工で効率的な管理が可能
- 最新のExcel関数を活用した高度な分析
- 自動更新機能による常に最新のポートフォリオ状況の把握
7.2 応用例
- 配当金情報の追加と年間配当利回りの計算
- 過去の取引履歴を記録して長期的なパフォーマンスを分析
- グラフやチャートを活用した視覚的な資産配分の分析
- Power BIとの連携によるさらに高度な視覚化（Microsoft 365ユーザー向け）
7.3 最後に
この記事で紹介した方法を応用すると、さらに高機能なポートフォリオ管理ツールを自分で作ることができます。自分の投資スタイルに合わせてカスタマイズし、効率的な資産管理を実現しましょう。
※この記事は2025年3月時点の情報に基づいています。Excelのバージョンやウェブサイトの構成は変更される可能性がありますので、最新情報をご確認ください。
以下の記事は2000年7月に投稿しましたが、リンク先には変更は無いようです。しかし、エクセルの使い方が変更になり、その進化を見守るため残しておきます。
本日の課題は、自分の保有銘柄をエクセルを使って管理する、というものです。
VBAの知識を使うことなく株価を取得して、今現在の資産を管理してみましょう。今日は簡単な入門ということにします。応用すると高機能なものが作れてしまいます。
それではやってみるわけですが、まずは東証にどれくらいの銘柄があるのかを把握してみます。日本取引所のホームページでそのデータをダウンロードしますが実はこれを使用します。日本取引所のトップページから次のように進んでいきます。マーケット情報→統計情報（株式関連）とリンクをたどっていきます。このページの下の方にその他統計資料の項目があり「東証上場銘柄一覧」というリンクがあるのでクリックします。
次はこのファイルをダウンロードせず右クリックにて「リンクのアドレスをコピー」します。このリンクアドレスが変更になる可能性もありますがここさえ押さえておけばあとでエクセルを使用する際、いつでも対処できます。
ここから、EXCELを使用します。白紙のページでメニューバーのデータをクリックし、データの取得からWEBを探しクリックします。そうするとURLを入力する画面が出ますのでここに先ほどのURLを貼り付けます。
接続が確立されるまで待ち、完了したらナビゲーター画面でSheet1を選択しましょう。データのプレビューが現れたら右下にある「データの変換」ボタンをクリックします。
しばらくするとPower Query エディターが表示されます。今は何も編集することはないので「閉じて読み込む」をクリックします。データ件数が約4000あるシートが追加されます。これで銘柄数やその銘柄のセクターなどが確認できました。ただし、肝心な株価がありません。これは有料、無料サイトなどで詳細なデータを取得することができますが今回は下記のサイトのデータを利用します。先ほどの銘柄のぶんだけ最新の株価を取得できます。
https://minokamo.xyz/file/stock/zk.xlsx
最新の株価と銘柄情報を入手できたので、あとは関数を使い収益などを出すだけです。過去データがあれば過去の収益も出すことができ傾向を見るのに役に立ちますね。自分はやってみました。どのような状況で自分の損益がどうなったかを知ることができました。みなさんも試してみてはいかがでしょうか。どのような相場状況に強いのか、または弱いのかを把握するのに役に立ちます。
コメント