ファイナンス

GoogleスプレッドシートでETFの資産ポートフォリオの図を作ってみた

更新日:

どうもヒトリベです。

GWが終わった人も多い中、私は有休を取得し延々と家に引きこもっております。

せっかくこんなに長く家に居る事もないので、資産を改めて見直そうとポートフォリオ表を作成しました。

完成図はこんな感じです。

ファンドごと、セクターごとといった感じで視覚化できます。

特にETFのセクター比率を把握するのは大事ですよね。

ついでに、月次の資産推移が分かる図も作成しました。

割と大変でしたが、後々楽になるし、視覚化することで簡単に見直しが出来るため有益です。

マネーフォワードMEも使用しているので、ざっくりした資産はそちらでも確認できますが、「月次の入出金額」「配当利回り」「ファンド別構成比」「資産クラス別構成比」「セクター構成比」などはレポートする機能が無いので。

マネーフォワードMEはあくまで家計簿アプリとして入出金管理だけに活用するつもりです。

作成に当たっての作業環境は下記の通りです。

作業環境

・Googleスプレッドシート

・Windows10

・Chrome

私が使用しているMSOのExcelのバージョンが古い為、クエリやマクロなどがうまく作動しなかったので、スプレッドシートにしました。

Excelを使用したことがあれば基本的な操作は同じなのですが、やはり細かいところで違いが。

逆に便利な機能もたくさんありました。

スプレッドシートの使い方は、下記の書籍が参考になりました。

2019年版で一つ古いですが、こちらの方が見やすかったです。

KindleUnlimitedなら無料でご覧いただけます。

Excelを使った方法はネットで検索されると結構出てきます。

表や図の作成や関数など慣れれば簡単なのですが、下記の書籍がお勧めです。

 

私は新入社員のころに、これらに書かれているテクニックをある程度覚えたので、Excelに関してはほぼマウスを使わずに、快速で作業出来ています。

では、作成開始から完成まで簡単に説明していきます。

 

① Googleスプレッドシートの起動~取引履歴入力

Googleスプレッドシートの起動

まずGoogleスプレッドシートhttps://www.google.com/intl/ja/sheets/about/にアクセスします。

パーソナルの下にある「Googleスプレッドシートを使ってみる」をクリックします。

GoogleアカウントのIDとパスワードを入力し、利用開始です。

お持ちでない方は無料なので、この機会にGoogleアカウントを作成してください。

最初の画面の「新しいスプレッドシートを作成」「空白」を押して、まっさらなシートからStartです。

楽天証券から取引履歴をDLして入力

今回は楽天証券から米国ETFの取引履歴を収集し、スプレッドシートに貼り付けます。

楽天証券にログインし、上部タブの「口座管理」>「取引履歴(商品別売買履歴)」をクリックします。

「米国株式」のタブを押して、期間:「すべて」と入力したら、画面中部の「CSV形式で保存」を押します。CSV形式のファイルがダウンロードされるのでお待ちください。

ダウンロードしたCSVファイルをエクセルで開き、得られたデータをすべて選択してコピーします。

エクセルをお持ちでなければ、メモ帳で開いたデータをすべて選択してコピーします。

コピーしたデータをスプレッドシートに貼り付けます。

なおメモ帳で開いた場合は、貼り付け後に右下に表示されるプルダウンから「テクストを列に分割」を選ぶと、カンマで列ごとに分割して貼り付けられます。

こうして作成したシートを「取引履歴」とします。

② ETFの現在価格及び為替の入力

ETFの現在価格の入力

次にシートを追加して、ETFの現在価格を入力します。

シートの名前は「株価指標」としました。

Webで調べて手入力しても良いのですが、面倒なのでimporthtml関数を使います。

追記

米国のETFであれば、Googlefinance関数を使用する方法もあります。

例えばVOOの現在価格を調べる場合は下記のとおりです。

=googlefinance("VOO")

こちらの方が簡単ですが、日本株や日本の投信には対応していないので

そうした株価には下記の方法が使用できます。

詳しくはGoogleのドキュメントエディタヘルプをご参照下さい。

https://support.google.com/docs/answer/3093339?hl=ja

例えば、VOOの現在価格を参照したい場合には、下記の通り入力します。

=importhtml("https://finance.yahoo.com/quote/VOO/","table",0)

今回はyahoo!ファイナンスのページから拝借しました。

お好きなページから取っていただけます。

指数を見つけるのにちょっと時間がかかりました。

投資先が多くないうちからコツコツやっときゃよかったと後悔。

話がそれましたが、入力すると下記のように現在価格などの情報が表示されます。

Previous Close 264.36
Open 267.53
Bid 269.00 x 800
Ask 269.72 x 1100
Day's Range 266.38 - 269.14
52 Week Range 200.55 - 311.59
Volume 3,198,496
Avg. Volume 8,673,541

Openの欄に表示されているのが、市場価格ですね。(2020.05.09日時点)

これに為替をかければ日本円での現在の価格が分かります。

為替の入力

為替も同様にimporthtml関数を用いてWebから情報を取り込みます。

下記は同様にyahoo!ファイナンスのページから拝借する場合。

=importhtml("https://stocks.finance.yahoo.co.jp/stocks/detail/?code=USDJPY=X","table",1)

アメリカ ドル / 日本 円 106.65

こんな感じで入力しておけば、次回から検索→入力の手間が減ります。

 

③ポートフォリオ表の作成

いよいよポートフォリオをまとめる表を作成します。

更にシートを追加して、「ポートフォリオ」と名付けます。

下記の通り、ティッカー、ファンド名、累計受渡金額、保有数量、現在価格、現在評価額、評価損益、損益率を入力します。

ここで、取引履歴のタブからデータを呼び出すときに便利な関数と入力の注意をいくつかご紹介します。

SUMIFS関数

累計受渡金額

つまり投資した金額の累計の計算です。

関数; =SUMIFS('取引履歴'!O:O,'取引履歴'!C:C,A2)

意味;取引履歴のタブにあるC列(ティッカー)が、ポートフォリオのシートにあるA2(VOO)と一致する場合に、取引履歴のタブにあるO列(受渡金額)を合計する。

次回以降は、取引履歴のシートに、楽天証券のページから取り出した取引履歴のデータを入力するだけでOKです。

SUMIFS関数は他の条件も追加できるので、配当再投資による入金は除くなどの指定も出来ます。

保有数量

先ほどの累計受渡金額と同じ考え方で計算させます。

関数;=SUMIFS('取引履歴'!H:H,'取引履歴'!C:C,A2)

意味;取引履歴のタブにあるC列(ティッカー)が、ポートフォリオのシートにあるA2(VOO)と一致する場合に、取引履歴のタブにあるH列(数量)を合計する。

SUBSTITUTE関数

現在価格

先ほど作成した株価指標のシートから、適切なセルを選んで参照するのみです。

為替を掛け算するのをお忘れなく。

なおサイトによっては、「,」や「円」や「ドル」といった余計な文字が入っている場合には、これらの文字を削除する必要があります。

関数;=SUBSTITUTE(SUBSTITUTE(B7,",","",1),"円","",1)

例えばB7のセルにある文字列から、「,」と「円を」除きたい場合は上記のように入力しました。

もっといい方法があるかもしれませんが…ちょっと勉強します。

現在評価額

上記の現在価格に保有数量を掛け算すればOKです。

評価損益

現在評価額から累計受渡金額を差し引いた額。

損益率

評価損益を累計受渡金額で割った値。

セクター比率

各ETFのセクター比率を入力します。

これも自動で入力させてもいいのですが、大きな見直しが入った時にまた確認すればいいと思います。

各ETFのセクター比率は各社のページに載っているので、そこからコピペしてきます。

例えばVOOだと下記のサイト。

https://www.vanguardjapan.co.jp/retail/investment-products/funds/detailview/etf/0970/equity/portfolio/us

最後に各ETFのセクターを合計します。

以上で大体の入力は終了です。

④ポートフォリオの円グラフの作成

ここからは簡単です。

ティッカーと現在評価額を選択して円グラフを作成すると、ETFの銘柄ごとの投資割合が分かります。

セクター名とセクターの合計額を選択して円グラフを作成すると、全ETFをまとめたセクター比率が分かります。

また、毎月集計していけば、月次の入出金や資産推移も分かります。

今回は楽天証券の米国ETFを例にしましたが、配当履歴や投信も併せて同様の方法で作成しておけば、配当利回りや資産クラス別投資比率もしっかり把握出来ます。

まとめ

いかがでしたか?

結構時間がかかったのですが、今後もこのポートフォリオ表と先日のライフプランニングの計画表を活用して計画的に積立投資を実行していきたいと思います。

長期投資では、積立投資、分散投資、我慢の投資が重要と言われています。

目先の株価の変動に惑わされずに、淡々と計画に沿って入金していくことで長期的に見て安定した利益が上げられると期待しています。

-ファイナンス
-, , , , , , , ,

Copyright© 春は短し旅せよ20代 , 2021 All Rights Reserved Powered by STINGER.