読者です 読者をやめる 読者になる 読者になる

C55 40代の資産運用,投資ブログ 

米国株を中心に,40代の資産運用,投資について書いています

【ダウンロードフリー】自動更新されるグーグルスプレッドシートを用いたポートフォリオ(日本株,米国株両用)

自動更新されるグーグルスプレッドシートを用いたポートフォリオ(日本株,米国株両用)

米国株投資家のみなさま,グーグルスプレッドシートを用いて,株価や評価額が自動更新されるポートフォリを作成しました.日本株も同じシートで管理できます.

作成したポートフォリオの写真です.

https://goo.gl/photos/YvufbGF9nXFbL5KQ7

 

せっかくなので,投資家の皆さんに公開するとともに,編集可能としていますので,まだお持ちでない方は自由にダウンロードして,ご自身のポートフォリオとしてお使いください.

うまく使えるようであれば,みなさまのブログで紹介していただければ幸いです.

 

f:id:cgogohero:20170507103527p:plain

 

 

スプレッドシートの説明と解説

説明

日本株,米国株を取引されている投資家の方で,証券口座を複数(個人で複数,家族で複数など)お持ちの方は,資産の把握が面倒だと思います.1箇所で全ての株式資産を確認できるポートフォリオをグーグルスプレッドシートを用いて作成したので,公開します.

 

えびたいさんが公開しているスプレッドシートを参考にして,C55用にアレンジしています.えびたいさんのオリジナルはこちらから

資産管理に役立つ!Googleドキュメント スプレッドシートでリアルタイム株価を取得する方法 : 株でサラリーマン卒業

 

アレンジした点

  1. 日本株の設定を少し簡略化しました
  2. 米国株も同じシートで管理できるようにしました
  3. 米国株の評価額を日本円で自動計算できるようになっています
  4. 日本株,米国株の評価額が比較できます
  5. グラフも自動作成されます

 

スポンサーリンク

 

 

スプレッドシート・ダウンロード

こちらをクリックすればスプレッドシートにつながります.

 

2017.5.8更新 公開用 株式投資資産管理 - Google スプレッドシート

 

2017年5月7日 公開分は共同編集可能の設定で公開したので,編集が加わりオリジナルの物と変化してしまいました.そのため,日本株での現在値の自動更新などができ無くっていました.

現在は閲覧のみの設定で公開し直しています.「閲覧のみ」の設定ですが,下記の手順に従って設定すれば,自由に使えるようになります.

 

 

f:id:cgogohero:20170508214549p:plain

 

閲覧のみとなっていますので,そのままでは編集ができません.

「ファイル」→「コピーを作成」でご自身のgoogle driveに保存して編集を開始してください.

 

f:id:cgogohero:20170506144515p:plain

 

解説

各項目の関数の説明,変更の手順などを示します.

米国株は「Symbol」入力で全て終了しますが,日本株の銘柄名や現在値を自動取得するにはちょっとした工夫が必要となっています.

 

日本株の項目

概要

概要を説明します.

・銘柄別にシートを作成.

・銘柄別シート上で「銘柄」(銘柄名)と「現在値」(現在の株価)を自動取得し,そのデータを「Summary」シートに反映

(えびたいさんのオリジナルでは株の取引がなかった時にブランクとならないような設定にされていましたが,証券コードを入力するのが面倒なので,この機能を省いています)

 

具体的手順

1.4桁の証券コードをシート名に入力

 一番下のシートの名前を証券コードに変更します.「Summary」は残しておいてくださいね.

    f:id:cgogohero:20170506152959p:plain

 

2.A1のセルに下記をコピペ

 

=ImportHtml("http://info.finance.yahoo.co.jp/history/?code=8591.t","table",1)

 

  f:id:cgogohero:20170506153149p:plain

注意:必ずA1のセルにコピペしてください

 

3.赤字の部分をシートに記入した証券コードと同じ数字に変更

   例はオリックス(8591)になっています

 

4.青字の t の部分を,その銘柄が上場している証券所によって変更する

    東証(一部、二部、JASDAQ、マザーズ)なら「t」

    名証なら「n」

    福証なら「f」

    札証なら「s」 に変更します.

 

5.入力を完了すると下記の様な表示になります.

  f:id:cgogohero:20170506153257p:plain

  A1に銘柄名が,C1に現在値が表示されています.

 

6.「Summary」のシートで,「証券コード」に4桁の数字を入力

Summaryのシートに戻り,証券コードのセルに,証券コードを入力します.

f:id:cgogohero:20170506153746p:plain

 

 自動的に「銘柄」と「現在値」が表示されます.

「銘柄」には,

=INDIRECT(B3&"!A1")

の関数を登録しています.内容は「B3のセルに入力された数字のシートの,A1(銘柄名)と同じ値を入力」です.

 

「現在値」には

=INDIRECT(B3&"!C1")

の関数を登録しています.内容は「B3のセルに入力された数字のシートの,C1(現在の株価)と同じ値を入力」です.

 

ですので,個別のシートと具体的手順1の,A1への入力が必須となります.

 

7.「セクター」「数量」「取得単価」はご自身で入力してください.

申し訳ありませんが,「セクター」の自動収集は現在のところ設定できません.銘柄収集と同じ理屈ですので,余裕があれば挑戦してみます.

「数量」「取得単価」は売買の都度集計してください.新規購入した場合には,数量を合算したり,平均単価を再計算しなくても下に枠を追加して行けば,グラフ化されたときには自動で合算してグラフ化してくれます.

 

8.「損益(¥)」「損益(%)」「評価額(円)」

損益(¥) =数量*現在値-数量*取得単価

損益(%) =損益(¥)/(数量*取得単価)

評価額   =数量*現在値

この関数によって自動計算されているハズです.もちろん数量や現在値は該当するセルとなります.

 

スポンサーリンク

 

米国株の項目

概要

米国株は「Symbol」入力で全て終了します.ドル建て資産が日本円ではどうなっているかを自動計算するため,ドル円の値を自動取得しています.

具体的手順

1.「Symbol」を入力します.

Symbolのセルにticker symbolを入力します.

f:id:cgogohero:20170507100104p:plain

自動的に「銘柄」と「現在値」が表示されます.

 

関数はそれぞれ

=GoogleFinance(B13,"name")

=GoogleFinance(B13,"price")

内容は,「B13に入力されSymbolの名前(株価)を表示」となっています.

 

2.「sector」「数量」「平均単価」はご自身で入力してください

日本株同様「セクター」の自動収集は現在のところ設定できません.銘柄収集と同じ理屈ですので,余裕があれば挑戦してみます.

「数量」「平均単価」は売買の都度集計してください.新規購入した場合には,数量を合算したり,平均単価を再計算しなくても下に枠を追加して行けば,グラフ化されたときには自動で合算してグラフ化してくれます.

 

3.「損益($)」「損益(%)」「評価額($)」

損益($) =数量*現在値-数量*平均単価

損益(%) =損益($)/(数量*平均単価)

評価額($) =数量*現在値

この関数によって自動計算されているハズです.もちろん数量や現在値は該当するセルとなります.

 

4.「損益(¥)」「損益(%)」「評価額(円)」

「評価額($)」を現在の為替相場に合わせて自動的に計算されるようにします.そのためには現在の為替相場のデータが必要です.

 

f:id:cgogohero:20170507100338p:plain

USD/JPY,EUR/JPYの表示があり,現在の値が表示されています.

それぞれの関数は

 

USD/JPY

 =GoogleFinance("currency:USDJPY","average")

EUR/JPY 

 =GoogleFinance("currency:EURJPY","average")

です.

 

「評価額(円)」は,「評価額($)」かけるドル円相場ですので

「評価額(円)」 =評価額($)*ドル円の値段が表示されているセル

 

日本株,米国株資産の合計

 日本株,米国株それぞれを合計した表です.

f:id:cgogohero:20170507100850p:plain

 日本株,米国株の評価額を合計しています.

 

グラフ

以下の7つのグラフがデフォルトで自動作成される様になっています.

  • 日本株,米国株それぞれの銘柄別評価額の割合
  • 日本株,米国株それぞれのセクター別評価額の割合
  • 日本株,米国株それぞれの銘柄別損益
  • 日本株,米国株合計評価額の割合

 

補足

入力が必要なセルには薄いグレーで塗りつぶししてあります.

ご自由にアレンジしてお使いください.

日本株,米国株資産の合計の下に保有する現金額を追加すれば,現金を含めたポートフォリオも作れます.

 

 みなさんのお役に立てることを願っております.

もしうまく使えるようであれば,みなさまのブログで紹介していただければ幸いです.

 

免責:不具合があっても当方は責任をもちません.ご理解ください.