複数のETF銘柄に投資をしていると、自分はどんな企業にどれだけ投資をしているか、なんとなく定性的な推定はできますが、定量的には簡単には推定することができません。また、インデックス型ETFはほったらかし投資が可能とはいえ、構成銘柄企業の最新ニュースを把握することにより、投資や経済の勉強になります。
私は、Yahoo!Finance（USA版）とEXCELを使ってETF構成銘柄の動きを把握しておりますので、その方法を紹介したいと思います。
個別銘柄保有数を推定する基本的な考え方
例えば、VOO（バンガードS&P500ETF）の所有株数を100とし、現在単価が＄360と仮定します。VOOではAAPL（アップル）の構成割合は6.47％、MSFT（マイクロソフト）の構成割合は5.50％となっています。VOOへの投資総額は$360.00×100株＝$36,000.00なので、個別銘柄への投資額は構成割合比を乗じることにより算出することができます。よって、AAPLへの投資額は$36,000.00×6.47％＝$2,329.20、MSFTの投資額は$36,000.00×5.50％＝$1,980.00とみなすことができます。
次に、AAPLの現在単価が$150.00、MSFTの現在単価が$240.00とします。すると、VOOを通したAAPLの持株数は$2,329.20÷$150.00＝15.528株、MSFTの持株数は$1,980.00÷$240.00＝8.250株と推定することができます。VOOは構成銘柄が約500社ありますので、すべての構成銘柄について同様に持株数を計算することが可能です。
- VOO内のAAPL持株数： $36,000.00×6.47％÷$150.00 ＝15.528株
- VOO内のMSFT持株数： $36,000.00×5.50％÷$240.00 =8.250株
VOOの100株の他、VTI（高配当株ETF）を100株（現在価格$200.00）所有していたとします。この場合、上記と同様にAAPL（構成割合5.56％）とMSFT（構成割合4.68％）から、持株数を計算します。投資総額は$200.00×100株＝$20,000.00となるので、
- VTI内のAAPL持株数： $20,000.00×5.56％÷$150.00 ＝7.413株
- VTI内のMSFT持株数： $20,000.00×4.68％÷$240.00 =3.900株
よって、VOOとVTIをそれぞれ100株保有していた場合、合算します。
- AAPL社保有株数 15.528（VOO）+7.413（VTI）＝ 22.941株
- MSFT社保有株数 8.250（VOO）+3.900（VTI）＝ 12.150株
これをいちいち手計算で求めることは大変なので、EXCELを用いて効率的に計算します。
ETFの構成銘柄とその比率に関するデータベースの作成
ETFの構成銘柄とその比率は、ETFを発行している会社（例えばバンガード社やブラックロック社）のWEBページには最新版が更新されていますが、上位15社から20社程度しか示されていない場合が多いので、私は「投資の森」のサイトのETF構成銘柄リストを使っております。
CSVデータが提供されていると嬉しいのですが、無さそうなので「もっと見る」を何度もクリックしてS&P500を構成する全銘柄を表示させて、この表を、白紙のEXCELにコピペしています。貼りつけの際は貼り付け先の書式に合わせるのがちょっとしたテクニックです。シート名をETF名にして、保有するETFの構成銘柄と比率を表にまとめておきましょう。VYMは3000銘柄を超えますので、行数も多くなりますが、下位になると構成銘柄比率が「0.00％」となりますので、「0.01％」の銘柄の行まで作成しておくと良いでしょう。
私は、上図のようにVYMからVTVまでETF11銘柄のリストを作成しています。
※投資の森に掲載されている構成銘柄では、メタ・プラットフォーム社のシンボルが「FB」と旧称Facebook社のシンボルになっていますので、ここは「META」と修正しておきました。
構成銘柄１つを選んでCSVファイルを作る
私は、米国版Yahoo！Financeを使って、各銘柄の現在値を把握しています。米国市場が停止している日本時間（現在は6:00～23：30、夏時間は5:00～22：30）に利用することにより、前日の終値を活用しています。まずはトップページ上部にある「My Portfollio」をクリックしましょう。
次に、チャートの下にある「+ Create Portfolio」をクリックします。
左側の「Basic Portfolio」を選択します。
ポートフォリオ名は何でも構いません。ここでは「ETF内訳」と入力して「Submit」します。お金の単位はUS Dollar(USD)のままでかまいません。
何でもよいので１銘柄のシンボルを入力しましょう。ここではアップル社のAAPLを入れると下部にAAPLが現れるのでこれを選択し、「+ Add Symbol」をクリックします。
すると、AAPL社だけのポートフォリオができました。
チャートの右下にある「Export」をクリックして、CSVファイルを出力します。Windows11を利用されている方は、「ダウンロード」というフォルダー内に「quotes.csv」というファイルが出力されます。
これをEXCELを使って開いてみると、こんな表示になります。
Date欄は「#######」ですが、これは列の幅が不足しているからであり、気にしなくてかまいません。気になる人は、列の幅を自動調整してもいいでしょう。A列からP列までを選択して、「書式」プルダウンメニューから「列の幅の自動調整」を選ぶと、簡単に調整できます。でも、列の幅調整はCSVファイルの用途としては無意味な操作なので省略してかまいません。
これが、米国版Yahoo！Financeでポートフォリオを作成する際にベースとなるCSVデータの書式と認識してください。重要なのは、カラムの並びと順序、Symbolの記号（ここではAAPL）と現在株価（Current Price 135.21）です。
ETFの複数構成銘柄のCSVファイルを作る
次に、VOOの構成銘柄と比率で作成したEXCELを開き、Bカラム（左から2列目）にあるシンボルを、quates.csvにコピー⇒貼り付けします。Yahoo!Financeでは、200銘柄ぐらいまでは対応できますが、S&P500のすべてである500を超えた銘柄数ではエラーとなります。ここでは簡単のため20銘柄にしておきましょう。貼り付けた後の画面はこのようになります。このまま、CSVファイル形式のまま「quates.csv」を一旦保存して閉じます。
Yahoo！Financeの「My Portfolios/ETF内訳」に戻ります。今度は、チャートの右下にある「Import」ボタンを押して、quotes.csvを読み込みます。
「ファイルを選択」をクリックします。
「quotes.csv」が保存されているフォルダを探して、「quotes.csv」ファイルを選択します。
quotes.csvが表示されていることを確認してSubmitボタンをクリックします。
これで、20銘柄のポートフォリオ「Imported from Yahoo」ができました。銘柄だけをCSVに記載しておけば、簡単にここまで出来てしまうのはありがたいですね。
ここで留意しておくことは、以下の4点です。
- quotes.csvの銘柄表示順序とYahoo!Financeの表示順序が、上下逆になっていること
- 持株数「Shares」が空欄になっていること
- quotes.csvのカラムには「Shares」という欄が見当たらないこと
- ここに示した銘柄に関する最新経済ニュースが、表の下に一覧になっていること
銘柄数を多くすると、ポートフォリオ表が縦長になるだけでなく、最新経済ニュースが膨大な表示量になりますので、ブラウザで表示されるまでの時間も多くかかりますので注意しましょう。
複数構成銘柄ごとの保有評価金額を計算する
銘柄ごとの持株数の計算方法の基本は冒頭に述べた通りですが、EXCELの関数を駆使することにより、手早く計算が可能となりますので、その方法を記載いたします。例として
- VOO（S&P500追随ETF）を100株
- ESGU（ESG米国投資ETF）を400株
- VYM（高配当米国株ETF）を300株
保有していたと仮定します。
3種のETFの構成銘柄と比率を記載したEXCELファイル内に新しいシートを作成し、このような表を作成します。
AからJカラムまでの計算式を示したものが下記となります。
3行目から4行目は、ちょっと手間ですけど、手入力でETF名、持株数、現在単価を入力します。
AからCカラムまでの5行目以降は、VOOの構成ファイルデータベースをそのままコピー＆貼り付けとします。
DカラムはVOOシートのDの値に一致していますが、計算式を使います。これは、構成銘柄順が異なる「ESGU」の構成比率Fカラムと「VYM」の構成比率Ｈカラムにも利用するためです。D5はこんな計算式です。
VLOOKUP関数で構成銘柄比率があるシートから銘柄名に一致するものを抽出し、一致する銘柄がなければ値０とする式となっています。D5カラムの数式を下方向にそのままコピーできるように、抽出するSymbolのB5を$B5にして列名だけ固定、参照するデータベースは$B$2:$D$600と行列を固定する「絶対参照と相対参照」機能を使って工夫しています。
Eカラム、Gカラム、Iカラムは、ETFを保有していたことによる各銘柄ごとの保有価値となりますので、これらをＪカラムで合計して「銘柄ごとの保有評価金額」を出すことができました。
複数構成銘柄ごとの持株数を計算する
次は各銘柄の現在値を上手に拾う方法です。先ほどYahoo!Financeで作成した「Imported from Yahoo」を表示し、ここから「Export」ボタンを押して再びCSVファイルを出力します。今度は、ダウンロードフォルダー内に「quotes(1).csv」が出来ています。これは開く必要ありませんので、Yahoo!Financeで出来上がった「quotes(1).csv」をImportして下さい。
このように、一度CSVファイルをExportして、そのままInportすることにより、銘柄の並び方が逆になります。AAPLから始まる並びになったので、またまたExportでCSVファイル「qultes(2).csv」を出力します。
ようやく、各銘柄の現在単価のリストが出来ました。これを、計算表の右側（1行あけたNカラム目から右）にコピー＆貼り付けします。
現在価格KカラムはOカラムをコピー＆貼り付けでも構いませんが、一応銘柄が一致していることを確認するIF文をつけた関数にしています。持株数は保有評価価格（J）を現在単価（K）で割り算して算出します。念のため、割り算でエラーが出た時には「-」を表示させています。いよいよ、持株数（Lカラム）の算出ができました。
ETFの構成銘柄ポートフォリオを完成させる
「quotes(2).csv」ファイルを開いて、持株数をQuantity（Lカラム）に「コピー＆値の貼り付け」をします。普通の貼り付けでは計算式がコピーされるので注意して下さい。
このままYahoo！FinanceからImportすると、銘柄順が逆転してAAPLが一番下になってしまいますので、保有評価額が多い順に表示したポートフォリオにします。
Mカラムに、保有株数を計算したシートの合計金額（Jカラム）を「コピー＆値の貼り付け」します。ここも普通の貼り付けではないので注意して下さい。
「データ」タグを設定し、Mカラム内のセル1か所を選択し、「並べ替え」をクリックします。
先頭行をデータの見出しとして使用するにチェックを入れ、最優先されるキーをMカラムに相当する「Comission」とし、順序を小さい順にしてOKをクリックします。
並べ替えが終了しました。Yahoo!Financeでインポートする際、順序が逆になりますので、保有評価額が小さい順に並べました。ここで「quotes(2).csv」を保存します。
これを、Yahoo!Financeからインポートすると、下記のようになります。VOOの構成銘柄比率順ではなく、ESGUやVYMの構成銘柄比率が反映され、特にVYMで構成比率が高いJNJ、XOM、JPMが上位にランキングされていることがわかります。
持株数Sharesに、苦労して求めた持株数が反映されていることが確認できました。また、これでよければ、チャート右下の「Setting」をクリックして、「Rename Portfolio」で適宜ポートフォリオ名を変更しておきましょう。ポートフォリオの下部には、ここの掲載されたETFの構成銘柄に関する経済ニュースが一覧表示されます。
ほったらかしのインデックスETF投資でも、その構成銘柄の上位ポートフォリオと、その関連ニュースが出てくるので、一度手間をかけるだけで勉強になると思います。
なお、ETFも銘柄の株価は上下しますので、あくまで作成した時点でのポートフォリオとなります。また、ETFの構成銘柄と比率は定期的に見直されて更新されるので、データベースファイルもたまには更新するようにしましょう。
最後になりましたが、今回作成したETF構成銘柄のデータベースと、保有株数を計算するEXCELシートは「CreateETF_Portfolio」です。これを個人的に加工して利用するのは構いませんが、商用に利用することは控えて下さい。また、このファイルを使用したことによる損害（金銭的な損害、パソコンの障害など）、ダウンロードして加工したファイルに起因する損害が発生しても、私は一切責任を取りません。あらかじめご承知おきください。
もっと簡単な方法があるようでしたら、ぜひ教えていただけますでしょうか。よろしくお願いいたします。
