基準価額を自動取得して、資産管理できるエクセルファイルの作り方

投資信託ガイドTop > ポートフォリオ > 基準価額を自動取得して、資産管理できるエクセルファイルの作り方

« 投資信託積み立てで老後資金を作るなら、SBI証券がおすすめ | メイン | 積み立てリバランスの資金配分を自動計算するエクセルファイルの作り方 »

基準価額を自動取得して、資産管理できるエクセルファイルの作り方

 エクセルに基準価額・口数を自分で入力し、投資信託の損益・投資割合を確認している方もおられるでしょう。

 ただ、この方法は、投資銘柄数が多くなると、基準価額を更新するのが面倒になります。投資銘柄数が多くなると、更新しなければならない基準価額も増えるからです。そのため、「信託報酬が安いインデックスファンドが発売されても新規購入しない方」もおられるかもしれません。

 しかし、それはもったいないです。なぜなら、長期投資の場合、信託報酬のわずかな違いで、最終的な運用結果が大きく変わるからです。

 では、積み立て銘柄が増えても、運用状況の確認を簡単に行うにはどうすればいいのでしょうか?

 以下の方法で、「基準価額を自動で取得できるエクセルファイル」を作りましょう。一度作っておけば、ボタンを2回クリックするだけで運用状況が確認できるようになります。

  • ●完成図(こんなファイルを作ります)
  • 1.自分で入力する欄を作成
  • 2.自動で計算される欄を作成
  • 3.エクセルのWEBクエリ機能で、基準価額を自動取得するシートを作成
  • ●実際の使用例

完成図(こんなファイルを作ります)

完成図


1.自分で入力する欄を作成

自分で入力する欄を作成

ア.銘柄名を入力する欄を作成
イ.口数を入力する欄を作成


2.自動で計算される欄を作成

自動で計算される欄を作成

ア.現在の基準価額が、自動で計算される欄を作成
あとで入力しますので、今は空欄にしておきます。
イ.各資産の評価額が、自動で計算される欄を作成
評価額は、口数×基準価額÷10000口 で計算します。 例えば「E2」には、=B2*C2/10000 と入力します。 基準価額は、10000口あたりの価格ですので、最後に10000で割ります。
各資産の評価額が、自動で計算される欄を作成
ウ.全体の評価額が、自動で計算される欄を作成
全部の評価額を合計する、計算式を入力します。 例では、=SUM(E2:E9) と入力しています。
全体の評価額が、自動で計算される欄を作成
エ.各資産の投資割合が、自動で計算される欄を作成
各資産のグループを合計した数値を、全体の評価額で割ります。 例では、上から、
=SUM(E2:E3)/E11
=SUM(E4:E7)/E11
=SUM(E8:E9)/E11 と入力しています。
例では、上から2段、4段、2段で、投資資産のグループ分けをしています。内訳は、国内株式に2銘柄、外国株式に4銘柄、国内債券に2銘柄です。
各資産の投資割合が、自動で計算される欄を作成


3.エクセルのWEBクエリ機能で、基準価額を自動取得するシートを作成

3-1.各銘柄の現在の基準価額を、自動で取得できるようにする
ア.新しいシートを作成する
a.エクセルの下の方にある、+マークをクリックする
エクセルの下の方にある、+マークをクリックする
b.シート名を、基準価額を取得する銘柄名に変える
シート名をダブルクリックして、新しい名前を入力します。例では、シート名を、Sheet2 → eMAXIS TOPIXインデックス に変更しています。
シート名を、基準価額を取得する銘柄名に変える
c.A1をクリックして、カーソルを「A1」にあわせる
A1をクリックして、カーソルを「A1」にあわせる
イ.Yahoo!ファイナンスで、基準価額を取得する銘柄のページのURLを手に入れる
ここでは、「eMAXIS TOPIXインデックス」を例に挙げます。
a.Yahoo!ファイナンスのページを表示する
b.キーワード入力欄に銘柄名を入力し、「株価検索」をクリックする
「検索結果がない」と出る場合は、アルファベットと日本語の間にスペースを入れて再検索してみてください。
キーワード入力欄に銘柄名を入力し、「株価検索」をクリックする
c.銘柄名を見つけたらクリックする
銘柄名が、検索結果の一番上にでてこない場合もあるので注意してください。
銘柄名を見つけたらクリックする
d.銘柄の詳細ページの、URLをコピーする
このURLはあとで使います。
銘柄の詳細ページの、URLをコピーする
ウ.WEBクエリ機能で、Yahoo!ファイナンスの銘柄ページを取り込む
a.「データ」タブをクリックして、「Webクエリ」をクリックする
「データ」タブをクリックして、「Webクエリ」をクリックする
b.「新しいWebクエリ」の「アドレス(D):」に、さきほど手に入れたURLをペーストして、「移動(G)」をクリックする
例では、
http://stocks.finance.yahoo.co.jp/stocks/detail/?code=0331209A
と入力しています。
「新しいWebクエリ」の「アドレス(D):」に、さきほど手に入れたURLをペーストして、「移動(G)」をクリックする
スクリプトエラーがでたら、いいえ(N)を選択します(複数回表示された場合も、すべて「いいえ(N)」を選択してください)。
スクリプトエラーがでたら、いいえ(N)を選択します
c.「左上の黄色の矢印」をクリックし、右下の「取り込み(I)」をクリックする
「左上の黄色の矢印」をクリックし、右下の「取り込み(I)」をクリックする
d.「既存のワークシート(E):」が選択され、「=$A$1」と入力されていることを確認し、「OK」をクリックする
「既存のワークシート(E):」が選択され、「=$A$1」と入力されていることを確認し、「OK」をクリックする
エ.「基準価額」欄に、取得した基準価額が転記されるようにする
a.取得した銘柄の「基準価額」セルに、「=」を入力する
取得した銘柄の「基準価額」セルに、「=」を入力する
b.基準価額を取得した、銘柄のシートをクリックする
基準価額を取得した、銘柄のシートをクリックする
c.基準価額が書かれたセルをクリックする
セルは、C列の54行目か、55行目あたりにあります。
基準価額が書かれたセルをクリックする
d.Enterキーを押す
これで、Sheet1のC2に、「='eMAXIS TOPIXインデックス'!C54」という式が記入されます。
Sheet1のC2に、「=eMAXIS TOPIXインデックス!C54」という式が記入されます
3-2.後は、投資する銘柄数だけ同じ事を繰り返す
↓は、次の銘柄「ニッセイTOPIXインデックスファンド」のために、新しいシートを作成している画像です。
「ニッセイTOPIXインデックスファンド」のために、新しいシートを作成している画像
(補足)次回起動したときに、セキュリティ警告がでるので、「コンテンツの有効化」をクリックする
「コンテンツの有効化」をクリック


実際の使用例

「データ」リボンにある、「すべて更新(のアイコン)」をクリックするだけで自動更新できます。
「データ」リボンにある、「すべて更新(のアイコン)」をクリック


補足説明

このファイルを使っていて遭遇した、トラブルと解決方法

突然基準価額が取得できなくなる

突然基準価額が取得できなくなる


原因は、基準価額の行が、たまに1行ずれるからです。下の例では、今まで54行目にあった基準価額が、55行目にずれています。
今まで54行目にあった基準価額が、55行目にずれている


解決するには、指定する行の数字をずらします。

解決するには、指定する行の数字をずらす






「おすすめ投資信託」を解説した記事はこちら
>>http://teiiyone.com/blog/cat19/




カテゴリー一覧

投資ツール