【コピペで株価自動取得】Googleスプレッドシートでポートフォリオの資産管理をする方法

ちゅーや

こんにちは、ちゅーやです。
今回はGoogleスプレッドシートを使ったポートフォリオの管理自動化について解説します!

ポートフォリオを管理するアプリは数多くリリースされていますが、「日本株」「米国株」「投資信託」すべてに対応しているサービスは現状有りません。

Googleスプレッドシートであれば、それらすべての価格を自動で更新し、マクロを使って日々の価格を記録していくことも可能です。

この記事を読めば分かること
✔ Googleスプレッドシートで資産を記録する方法
✔ 株価を取得する方法
✔ 投資信託の価格を取得する方法
✔ 日ごとの資産額を自動で記録するマクロの簡単な設定方法

目次

GoogleスプレッドシートとはExcelのようなもの

Googleスプレッドシートは、検索エンジンで有名なGoogleが提供する表計算ソフトの事です。

表計算ソフトで有名なのがマイクロソフトが提供するExcelですが、GoogleスプレッドシートはExcelと同様のものと考えていただければOKです。

Googleスプレッドシートはクラウド上で編集するため、インターネットがつながっていないと利用できないという難点はあるものの、無料でどこでも使うことができる利便性の高さが売りです。

また、Excelはファイルを開くことができるのは1人までですが、Googleスプレッドシートは複数人が一気に編集をすることができます。

そのため、最近ではExcelではなくGoogleスプレッドシートを活用する人も増えてきました。

Excelではダメなの?

もちろんExcelでもOKです。

ただ、Excelだと今回紹介するやり方を真似するだけでは資産管理Excelを作ることはできません。

数式のアレンジ等が必要になるので、その点は注意してください!

Googleスプレッドシートで資産管理する方法

表を用意する

googleスプレッドシートで資産管理

まずは、ポートフォリオの詳細をまとめるシートを作成しましょう。

項目は好きな項目を追加していただければよいと思いますが、私は以下のような項目をまとめています。

  • 株式や債券、仮想通貨等の種類
  • 購入数量
  • 取得株価(USD, JPY)
  • 現在株価(USD, JPY)
  • 評価額(現在株価×購入数量 USD, JPY)
  • 評価損益(USD, JPY)
  • 構成国
  • 構成セクター
  • ドル円

米国株の株価を取得する

Googleスプレッドシートであれば、簡単に米国株の株価を取得することが可能です。

Google Finance関数を使えば株価だけでなく、前日比や時価総額も簡単に取得できます。

株価の取得は以下の関数をコピペ!
=GOOGLEFINANCE(A7,”price”)
A7はティッカーシンボルです。ここを任意の値に変えることで米国企業の株価を取得できます。

また、「price」を以下のように変化させることで様々なデータが取得できます。

  • 変動額:change
  • 変動率:changepct
  • 時価総額:marketcap
  • 株価収益率:pe
  • ベータ値:beta
  • 一株当たり利益:eps

日本株の株価を取得する

米国株はGoogle Finance関数で簡単に株価を取得することが出来ましたが、この関数は日本株に対応していません。

そのため、日本株がポートフォリオにある方は、少し煩雑な作業をしないといけません。

そこで今回は、そうした煩雑な作業をすっ飛ばしてコピペで日本株の株価を更新できるよう、関数を用意しています。

株価の取得は以下の関数をコピペ!
=IMPORTXML(CONCATENATE(“https://kabutan.jp/stock/?code=”,A10),”/html/body/div[1]/div[3]/div[1]/div[2]/table[1]/tbody/tr[4]/td[1]”)
A10は証券コードを入力してください。

ちなみにこの関数では、株探から株価を取得していますが、この仕組みのことをスクレイピングと言います。

Yahoo Financeはこのスクレイピングを禁止していますので、もしもご自身で行う際にはYahoo Finance以外から情報を取得するようにしてください!

投資信託の株価を取得する

投資信託も日本株と同様にスクレイピングを行う必要があります。

ただ、投資信託には証券コードのように単純明快な識別方法がなく、証券会社等からスクレイピングをしようにも個別にURLとXPathと呼ばれるコードをコピーしてこないといけません。

ただ、通常同じサイトから基準価格を取得する場合は、基本的にXPathは同じで、URLを変えるだけで基準価格を取得することが出来ます。

今回は、楽天証券から基準価格を取得しているいます。以下のコードのURLを変更すれば楽天証券で取り扱いのある投資信託については基準価格の取得が可能です。

eMaxisSlim全世界株式
=IMPORTXML(“https://www.rakuten-sec.co.jp/web/fund/detail/?ID=JP90C000H1T1“,”//*[@id=’MainPage’]/div[2]/div[1]/div/table/tbody/tr[1]/td[1]/span“)
赤:URL, 青:XPath

毎日の資産を記録に残すマクロの作り方

ポートフォリオの株価自動取得が完了したら、日々の資産額を自動で書き込んでいく仕組みも整えて、資産の推移を確認できるようにしてしまいましょう!

マクロを使って資産を記録する仕組みを作る

googleスプレッドシートで資産管理

少しごちゃごちゃしていますが、上の図のように投資対象毎に資産をまとめて、元本や含み益等も記録しています。

行2には先ほど作ったポートフォリオからリアルタイムの資産額を引っ張ってきています。

これをマクロで下の行に記録して行くことで資産推移を残していきます。

マクロは以下のようになっています。

function myFunction() {  
var spreadsheet = SpreadsheetApp.getActive();  
spreadsheet.getRange(‘K28’).activate();  
spreadsheet.setActiveSheet(spreadsheet.getSheetByName(‘シート名’), true);  
spreadsheet.getRange(‘4:4’).activate();  
spreadsheet.getActiveSheet().insertRowsBefore(spreadsheet.getActiveRange().getRow(), 1);  
spreadsheet.getActiveRange().offset(0, 0, 1, 
spreadsheet.getActiveRange().getNumColumns()).activate();  
spreadsheet.getRange(‘A4’).activate();  
spreadsheet.getRange(‘A2:L2’).copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
};

ただ、私はマクロを組めるわけではありません。

実際は、「マクロの記録」を活用して、指定した操作をマクロ化してもらっています。

STEP
①拡張機能からマクロの記録を選択
googleスプレッドシートで資産管理(マクロ)
STEP
シートを選択する

資産を記録していくシートを選択します。

STEP
上に一行を挿入する
googleスプレッドシートで資産管理

最終更新日の一つ上に行を挿入します。

STEP
資産額をコピぺ
googleスプレッドシートで資産管理

行2の値を先ほど挿入した行にコピペします。

貼り付けをする際、「特殊貼り付け」→「値のみ貼り付け」をするようにしてください。

トリガーを追加して毎日資産を記録してもらう

STEP
Apps Scriptを開く
googleスプレッドシートで資産管理
STEP
トリガーを追加する
googleスプレッドシートで資産管理(マクロ)

トリガーを追加するを選択します。

STEP
トリガーを設定する
googleスプレッドシートで資産管理

今回は、1日に1回更新したいので、上記のように設定をしてください。

時刻に関しては皆さんの好みで結構です!

まとめ:資産をしっかり管理しよう!

今回はGoogleスプレッドシートで資産を管理する方法を解説しました。

この方法なら、手をかけることなく資産の推移を追うことが出来ます。

適宜グラフなどの図を作成していくとわらに良いものとなると思います。ぜひ自分なりにアレンジしてみてください!

ちゅーや

最後までご覧いただきありがとうございました!

よかったらシェアしてね!
  • URLをコピーしました!
  • URLをコピーしました!

コメント

コメントする

目次
閉じる