EXCELのWEBクエリを使用した簡単なツールのご紹介です。
週に1回、月に1回、資産状況を確認する際に、保有銘柄の騰落を確認しています。
国内の個別銘柄、米国の個別銘柄のほかに、米国株式のETFもいくつか保有していますが、米国ETFの騰落の原因を確認する際に、米国ETFを構成する個別銘柄の騰落を確認したい場合があります。
EXCELのWEBクエリを使って、これを確認するツールを作成しました。
具体的に何をするツールか
具体的に何ができるのかと云うと、例えば、4月末から5月24日までの期間に、[VIG]米国増配株式ETF の株価は-2.61%と下落していますが、その要因は「構成比率でトップの [MSFT]マイクロソフト が-2.98%と落ちているほか、[TXN]テキサス・インスツルメンツ、[UTX]ユナイテッド・テクノロジーズ、[NIKE]ナイキ などの主要銘柄が大きく下落したことにある」とかとかを把握できるようになります。
米国ETF構成個別銘柄の指定期間における騰落確認ツール for EXCEL
まずはダウンロードしてください。
米国ETF構成個別銘柄の指定期間における騰落確認ツールをダウンロードする
（2019年5月28日版）
期間と確認対象のETFを指定する
ファイルを開いて、最初に期間と確認対象とするETFを指定します。
下の画像の2つの赤丸の部分を入力します。
サンプルでは、期間に2019年4月30日～2019年5月24日を指定し、確認対象のETFに「VIG」を指定しています。
日付は、米国市場が開場されていた日を指定してください。閉場していた日を指定すると正しい結果が得られません。
確認対象のETFには複数の銘柄を指定することもできます。以下のようになりますね。
指定期間における構成銘柄の騰落率を取得する
準備はこれだけです。クイックアクセス・ツールバーの「データ」を選択し、「クエリと接続」の「すべて更新」をクリックしてWEBクエリを実行します。
初めて実行したときには、以下のような警告が出るかも知れません。
送信している情報は、対象銘柄のコードと対象日付のみです。それを送信せずに株価を取得することはできないので、「このファイルのプライバシーレベルのチェックを無視します。」を選択して「保存」をクリックしてください。
以下のように、指定期間における「ETF自体の騰落率」と「ETF構成銘柄TOP15の騰落率」が更新されます。
指定期間におけるETF自体の騰落率は-2.61%、主要構成銘柄の内、[TXN]テキサス・インスツルメンツ、[UTX]ユナイテッド・テクノロジーズ、[NIKE]ナイキ などの下落率が大きいことが分かります。
なお、表中の「構成比率」は、あくまでも現時点での構成比率であって、指定期間における構成比率ではありません。
注意事項
個別銘柄の騰落を1銘柄ずつWEBから取得しているため、更新には時間がかかります。通信状況にもよりますが、ETF1銘柄につき、およそ1分30秒程度の時間がかかります。
更新時間が長いので不安になる方もいると思います。クイックアクセス・ツールバーの「データ」を選択し、「クエリと接続」の「クエリと接続」をONにしてください。画面右側に進捗状況が表示されます。
ETFの構成銘柄については、https://etfdb.com から、ETFおよび構成銘柄の株価の情報は、https://finance.yahoo.com/ から取得しています。稀にこれらのサイトが404エラーを返すことがあります。この場合にはリトライしてください。
また、稀に更新の進捗が長時間停まったまま動かなくなる場合があります。この場合には、EXCELのウインドウの下部もしくは「クエリと接続」の一覧中の「キャンセル」をクリックして処理を中断して、リトライしてください。
＜関連情報＞
複数の米国ETFを組み合わせたときのセクター比率を確認するツール。
【ETF構成確認ツール】複数の米国ETFを合わせたときのセクター比率を確認するのだ
