Excelで保有投資信託を管理する方法(まとめ)

最近はマネーフォワードMEのような家計簿アプリで金融資産の残高を簡単に確認できるようになりましたが、投資信託のつみたてや収益の記録を取るという点では機能が不十分です。

私はマイクロソフトのExcelを用いて投資信託の運用状況をモニターしており、ファイルを開くと基準価額データを自動で収集し、現在の資産状況が更新されるようにしています(追加投資の記録は手動で記録が必要ですが)。

Excelでの資産管理は、プログラミングを知らない私のような素人でも簡単にできますし、自分で自由にデータを加工できる点が、かゆいところに手が届いて非常に助かります。

と言う訳で、Excelを使った投資信託の管理方法についてまとめておきます(もちろん投資信託に限らず、個別株でも同じことができます)。

主にExcel2016の写真を使って説明しますが、Excel2000などかなり古いExcelでもwebデータの取り込みができますし、Googleスプレッドシートでのデータの取り込み方法も説明します

スポンサーリンク

関数について基礎知識

Excelでは「関数」を使うことにより平均値や標準偏差を簡単に求める事ができます。

自分で覚えている関数なら、直接セルに入力すれば良いですし、メニューの数式>関数の挿入(下図)より関数名の一覧と説明が検索できるので、ここからやりたい事を選んでも良いです。

資産管理に使用する基本的な関数を以下に挙げておきます。

=AVERAGE(範囲)・・・指定範囲の数値の平均値を算出
=SUM(範囲)・・・指定範囲の数値の合計値を算出
=STDEV(範囲)・・・指定範囲の数値の標準偏差を算出
=MAX(範囲)・・・指定範囲の数値の最大値を表示
=MIN(範囲)・・・指定範囲の数値の最小値を表示
=TODAY()・・・現在の日付を表示

運用に関する基本計算について

「現在評価額」=「基準価額」/10000*「保有口数」 (※基準価額が1万口当たりの場合)
「損益」=「現在評価額」+「実現損益」-「投資金額」
「騰落率」=「損益」/「投資金額」
「構成比率」=「現在評価額」/ SUM(全ファンドの現在評価額を選択)
「月間騰落率」=「今月評価額」/(「前月評価額」+「今月追加投資額」)-1
「平均騰落率(年率)」=AVERAGE(各月の月間騰落率)*12
「騰落率標準偏差(年率)」=STDEV(各月の月間騰落率)*12

資産運用の成績を管理するワークシートをExcelで作る際に使用する基本計算式を挙げました。

保有口数や投資金額、実現済みの損益は証券口座等の記録から手入力せざるを得ないと思いますが、投資信託の基準価額はExcelのwebクエリ機能を使って自動で取り込むことができるので(後述)、いちど好きな配置で表を作っておけば、ひと目で状況把握が可能となります。

Webクエリでwebからデータを取得する方法

Excelの「Webクエリ」という機能を利用することで、Web上の様々なデータをExcelに取り込み、リアルタイムに情報収集することができます。これによりいちいち運用会社のHPで基準価額をチェックしなくても良くなる訳です。

1.メニューより「新しいクエリ」を選択する

運用会社のHPやYahoo!ファイアンスのページからデータを取得するには、

データ>新しいクエリ>その他のデータソースから>Webからを選びます。

2.フォーム画面でURLを指定する

さて、URLを入力する画面が出てきますので、欲しい基準価額データの掲載されているURLを入力します。例としてeMAXIS Slim先進国株のページを選んでみました。

このページの場合は「Table0」をクリックすると「基準価額」の情報が格納されていることがわかります(運用会社によってHPの構成が異なるので試行錯誤が必要)。

ちなみに、複数のデータを取る場合はYahoo!ファイナンスの方が同じパターンでいけるので簡単かもしれません。

3.Excelシートに表が取り込まれる

取り込むデータを指定してLoadを押すと、無事にデータがExcel上に取り込まれました。

表全体が貼り付けられてしまうため、運用状況などをまとめているシートとは別のシートに取り込み、運用状況のシートからお目当てのセルを参照するのが良いでしょう。

Excelのファイルにはクエリの情報が保存されていますので、毎回このようなURLの指定を行わなくても、次からは簡単に最新情報に更新する事が可能です。

データの自動更新を行うには、データ>接続 を選択します。

そうすると「ブックの接続」の一覧が出てくるので、プロパティから設定を行います。webクエリの名前や説明についてもこのプロパティから変更ができます。

「接続のプロパティ」では、ファイルを開くときにデータを更新するにチェックを入れておくと、次回から自動で基準価額が更新されます。

最初の設定が少し面倒かもしれませんが、保有ファンドが増える前にワークシートと更新の仕組みを作ってしまえば、その後の管理は楽になります。

海外ETFなど外貨建てのものも、米国のYahoo! Financeと、銀行などの為替公示相場(例:みずほ銀行外国為替公示相場)のデータを取り込めば時価で円換算して表示できますし、グラフを描いてみたりと後はお好みで好きなようにやってみてください。

Googleスプレッドシートでwebからデータを取得する

ちなみに、マイクロソフトExcelを持っていない方でも、Googleスプレッドシート(無料)で同じことができますが、こちらは少し知識がないと難しいかもしれません。

Googleスプレッドシートでは「importhtml」という関数を使用します。

=importhtml(URL,クエリ,指数)

クエリには目的のデータを含む要素を”list”か”table”で指定し、指数には対象のlistかtableに指定されている指数番号を入力するとデータの取り込みが完了します。

早速、試してみましょう!

取得したデータを加工する

「Webクエリ」の機能によって、運用会社のHPからその日の基準価額が取得できるようになると紹介しました。

が、実際にやってみると、運用会社によっては「数字の後に「円」の文字がついてしまい、いちいちこの「円」という文字を削らないと・・・」という不便な問題が発生します。

実はこの「円」の文字も簡単に自動で消せます

文字列を扱う関数に「LEFT」というものがあり、左から何文字をとってくるというものです。

=LEFT(文字列参照先,文字数)

また、「LEN」という関数では、その文字列が全部で何文字あるかを表示します。

=LEN(文字列参照先)

これらを組み合わせて、

=LEFT(文字列参照先,LEN(文字列参照先)-1)

と、やってやれば最期の1文字「円」だけを除いた値が入力される訳ですね。

運用会社でのファンドの追加やwebの表示変更などで、参照先のセルがずれてしまう事が良くあるので、ファンド名の所も「LEFT」関数を使って適当な長さで入れておけば、おかしい時にはすぐに気づく事ができます。

ちなみに、右から何文字とってくる関数「RIGHT」もありますよ。

ブログランキング参加中。
応援頂けると大変嬉しいです。
にほんブログ村 株ブログ 投資信託へ
にほんブログ村
スポンサーリンク
スポンサーリンク
 投資コラム(一般) 
スポンサーリンク
この記事がお役にたちましたら、是非シェアして頂けると嬉しいです。
Twitter・RSSを購読する

おすすめ記事

投信で手堅くlay-up!(インデックス投資ブログ)

この記事の感想を下のコメント欄でお寄せください

  1. 子だくさんパパ より:

    はじめまして。

    資産管理を始めようと思い、いろいろネットで調べたところマネーフォワードに行き着きましたが、やはりセキュリティが心配なのでエクセル管理を考えています。

    ついては、出来れば作られたエクセルファイルを頂くことは出来ませんか?

    田中

    • じゅん@ じゅん@ より:

      >子だくさんパパさん
      こんにちは。
      以前にも問い合わせを頂き、2015年2月1日にメールにて送信したログがありますのでご確認下さい(なんらかの理由で届いていなければスミマセン)。
      なお、ご承知かもしれませんが、ネット上の素性のわからないエクセルファイルを開く事はウイルス感染のリスクがあります。
      お送りしたファイルに問題がない事は確認しておりますが、私も無用なトラブルは避けたい為、今後ファイルを公開する予定はありません。