資産配分・アセットアロケーション

【EXCEL】第3弾　Excelで資産管理しよう！　アセットアロケーション　
ポートフォリオに外国株を加えよう！　
債券・リートも入れて、リスク分散しよう！　
外国株価の取得方法は…

・項目・証券会社列を追加する

作成したポートフォリオ管理表をそのまま使う場合は、コード列の左に列を追加する
右クリック→挿入→テーブルの列（左）

ポートフォリオ管理表のタイトルは、以下の通り
項目・証券会社を加え、業種・安定性は削除しました
黄色の塗り潰しは、入力項目です

ポートフォリオ管理表の作成方法は、こちらを見てね！

【EXCEL】Excelで資産管理しよう！　第2弾-①　株式ポートフォリオ管理シート作成　資産表をバージョンアップ！　配当金の効率は？　今の利益は？　年間配当金は？　NISA・特定口座別税金計算対応

【EXCEL】Excelで資産管理しよう！　第2弾-①　株式ポートフォリオ管理シート作成　保有銘柄の資産状況・配当金効率の確認　資産状況を一目でわかりやすくしました　リアルタイムで株価を取得しながら、ポートフォリオの状態を確認しましょう！実質収益・配当金は？NISA・特定口座別に税金計算保有銘柄の銘柄別構成比・業...

www.youtube.com

項目～税金のデータをポートフォリオ管理の右端に以下の様に作成しておきます
項目・証券会社は好きなものに変更してくださいね！

Y3セル：日本の所得税
Y4セル：日本の住民税
Y5セル：米国源泉徴収
Y6セル：100-15.315-5(%)
Y7セル：100-10(％)

データ→データの入力規則　入力値の種類：リスト
データの入力規則で項目・証券会社を指定します

項目・証券会社を入力します

口座の列が変わっていますので、口座の入力規制元の値の範囲を変更しておいてください

・損益の計算式の修正
税金率の位置が変わったので、変更してください

・配当金の計算式の修正

外国株の項目・証券会社・ティッカー・口座を入力します
所有株のデータを入力してください

Googleからスプレッドシートを作成します

このシートにデータを取得していきます

・今の日時の取得

GoogleFinance関数を使って、株価等を取得する

・＄/￥の取得

・銘柄名の取得

・株価の取得
属性省略：Price（ディフォルト）

・分配金
GoogleFinance関数で取得できないので、証券会社等で調べてください

SBI証券から取得する例を紹介します

外国株価取得できました！

・ウェブに公開
動画ではExcelファイルにダウンロードする方法を紹介しましたが、
こちらの方が更新が簡単です

ファイル→共有→ウェブに公開

ExcelあるいはCSVファイルで埋め込む

URLをコピーする

・ExcelでWebからデータの取得
データ→Webから
コピーした、URLを貼り付け

作成したデータが取り込めた！

新規シート名：外国株としました
Excelファイルにすると、日付データが数値に戻ってしまうので、

右クリック→セルの書式設定→表示形式　日付形式にする

ちなみに、CSVファイルを指定すると、日本語は文字化けします

外国株のデータをExcelファイルに取り込むことができました

右クリック→更新
このテーブルが更新される

・更新タイミングを指定

デザイン→プロパティ　クエリプロパティクリック

定期的に更新する　　　　　　　　　：指定時間ごとに更新
ファイルを開くときにデータ更新する：開くたび最新データに更新

これまでの指定はテーブル単位

・すべて更新
このブックのすべてのテーブルが更新される

投資信託のテーブルがあれば、同時に更新されますよ！

・最新＄/￥
ポートフォリオ管理表に取得あるいは更新した外国株シートの＄/￥を参照させる
Y8セル＝＄/￥（外国株シートD2セル）
外国株価や税金の計算に使います

・銘柄名称
C列のデータを参照する

オートフィルします

・現在値
同様にD列を参照

・取得価格
＝株数×取得単価×取得時の＄/￥（￥150/＄としました）
円換算します

・時価評価額
＝株数×現在値×最新＄/￥

・損益
外国株の外国での譲渡益はありません
日本の税金20.315％のみなので、日本株と同じ

NISA　or　損出が出ている時：時価評価額-取得価格　　　　
　　利益が出ている時　 　　：（時価評価額-取得価格）*79.685％

・損益率
=[@損益]/[@取得価格]　（日本株と同じ）

・１株配当
外国株シートE列を参照

・配当金
NISA：ROUND(株数*１株配当,2)*90％*ドル円 （外国税のみ）
特　定：ROUND(株数*１株配当,2)*90％*ドル円*79.685％（二重課税確定申告なし）

外国税・確定申告時の計算・外国税控除など、詳しくはこちらを見てね！　　　　　　　　　　　　　　

【EXCEL】Excelで資産管理しよう！　外国株式　税金の計算　外国税額控除とは？　所得税の二重課税　確定申告で取り戻せる金額は？

【EXCEL】Excelで資産管理しよう！　外国株式　税金の計算　外国税額控除とは？　外国税を確定申告で取り戻そう！　税金計算式　EXCELで表すと…外国株もポートフォリオに取り込んで、アセットアロケーションを完成させよう！*********************************************...

www.youtube.com

・配当金利回り・配当金構成比
日本株と同じ（そのままオートフィル）

別シート（別ファイル）に取得してある投資信託のデータを参照します

投資信託データの取得方法はこちらを見てね！

【EXCEL & VBA】Excelで資産管理しよう！　リアルタイム株価取得　Webから投資信託価格取得＆取得データを最新データに更新　データ変換　マクロで更新　ブックを開いたときに更新するには…

【EXCEL&VBA】Excelで資産管理しよう！　リアルタイムで株価を取得しますWebから投資信託の価格を取得し、最新データに更新しますWebからデータを取得した人は、ぜひデータの更新も見てね！MarketspeedⅡRSS関数、LEFT関数、LEN関数、FIND関数、PowerQueryからWebからデ...

www.youtube.com

投資信託シートの各データを参照する
現在値は、投資信託各テーブルの更新ボタンをクリックすることにより最新データに更新される
（すべて更新ですべてのテーブル一発更新）

・取得価格＝株数×取得単価
・時価評価額＝株数×現在値
・損益：日本株と同じ
・配当金：なし

円・ドルもポートフォリオに加えましょう
＄
・取得単価＝取得時の＄/￥
・現在値＝外国株シートの最新＄/￥
・取得価格=[@保有株数]*[@取得単価]
・時価評価額=[@保有株数]*[@現在値]

アセットアロケーション作成完了！

・ピボットテーブルで項目ごと集計

挿入→ピボットテーブル

項目・時価評価額に✓

・ピボットテーブルからピボットグラフ作成
ピボットグラフ→円

データラベル→その他のオプション
分類名・パーセンテージに✓

・グラフの順番の変更
①並び替え（昇順・降順）
②各ラベルを移動する
　☩十字、ラインがでたら、目的位置まで引っ張ります

　円を国内株式の下に移動

アセットアロケーショングラフ完成！
理想の配分になっていますか？
時間の経過とともに、配分が変わってきます
時々、チェックしましょうね！