- アプリではなくスプレッドシートで投資管理したい方
- SBI証券や楽天証券など複数の口座をお持ちでスプレッドシートで一括管理したい方
- スプレッドシートで好きにカスタマイズしたい方
複数の証券口座を持っていると投資管理が煩雑になってしまうことありませんか。Googleスプレッドシートで投資管理をすることで一括管理ができます。
投資管理をスプレッドシートで行うにあたり課題となりやすいのが、株式の現在価格を取得する事です。本記事では、自動で株式の現在価格を取得する方法についてまとめていきます。
それでは行きましょう！
資産管理スプレッドシート
まずは、私が投資管理に利用しているスプレッドシートの項目などについて紹介します。
以下画像が投資管理シートの一部になります。
項目について
●A列：日付…購入日を記録しています。
●B列：証券コード…証券コードを記入しています。自動で現在値を取得する際に利用します。
●C列：銘柄…銘柄名を記入しています。
●D列：平均取得価格…平均いくらで購入したかを記入しています。
●E列：保有株数…保有している株数を記入しています。
●F列：入金金額…平均取得価格×保有株数＝入金金額としています。
●G列：ステータス…利益確定や保有中などがわかるようにしています。
●H列：取引日数…何日間保有していたのかを記載しています。
●I列：時価評価額…現在値を自動で取得しています。
●J列：時価評価額…現在値を自動で取得しています。IMPORTXML関数とGOOGLEFINANCE関数でネット上の情報を収集しています。
●K列：保有金額…確定ステータスの場合は、いくらで売却したのか、執行中ステータスの場合は、いくらの評価額になっているかを示しています。
●L列：損益（円）…確定ステータスの場合は、いくらの損失または利益が出たのか。執行中ステータスのものは現在いくら損失または利益が出ているのかを示しています。
●M列：損益（%）…投資金額に対しての損益率を示しています。
●N列：決済日…決済を行った日付を記載しています。
その他にも、なぜ購入したのか売却したのか意思決定した要因などをまとめた備考欄などもつけています。
資産評価額の自動取得に必要な2つの関数
今回、資産評価額の自動集計に非常に便利な関数を2つご紹介しようと思います。私のスプレッドシートでいえばJ列に以下数式が使われています。
IMPORTXML関数はWEBスクレイピングを行うもので取得までに時間がかかる事があります。その為、できる限りGOOGLEFINANCE関数で取得し、取得できない投資信託などIMPORTXML関数を利用するなどの使い分けがおすすめです。
●GOOGLEFINANCE関数
スプレッドシートのGOOGLEFINANCE関数は以下のように入力します。
=GOOGLEFINANCE(銘柄,属性,開始日,終了日,間隔)
| 引数 | 説明 |
|---|---|
| 銘柄 | 表示したい銘柄や通貨コードを指定します。 株価：取引所コードと銘柄 為替レート：通貨コード |
| 属性 | [省略可] どのタイミングの値を表示するかを指定できます。過去の値を取得する時や期間を指定する場合に使用します。省略した場合はリアルタイムの値を取得します。 指定できる項目が多いため、使用する時は以下のページで内容をご確認ください。 https://support.google.com/docs/answer/3093281?hl=ja |
| 開始日 | [省略可] 過去の値を取得する場合の期間の開始日を指定できます。 |
| 終了日 | [省略可] 過去の値を取得する場合の期間の終了日を指定できます。 |
| 間隔 | [省略可] 更新頻度をDAILYかWEEKLYの指定できます。 |
取得例①：米ドル/円の為替レートを表示する
為替レートを表示するには、通貨コード3桁同士を組み合わせて指定します。
1ドルが日本円でいくらか知りたい時は、米ドル：USD/日本円：JPYで「USDJPY」を指定します。
1. レートを表示させたいセルを選択します。
2. =GOOGLEFINANCE(“CURRENCY:USDJPY”) と入力します。
取得例②：GOOGLEFINANCE関数で株価取得する場合
NYダウの銘柄コード（ティッカーシンボル）は、「INDEXDJX: .DJI」 です。「NYダウ ティッカー」などと調べるとGoogle先生が教えてくれます。
1. レートを表示させたいセル（今回はセルB2）を選択します。
2. =GOOGLEFINANCE(“INDEXDJX:.DJI”) と入力します。
●IMPORTXML関数
ImportXML関数を使うためには、サイトのURLと取得する情報のXPathが必要です。ここでは、ImportXML関数で情報を取得するための手順を紹介します。
1.取得するサイトを選ぶ
今回は、eMAXIS Slimの先進国株式インデックスの評価額を取得したいと思います。
Webスクレイピングを行う際の注意事項：
・robots.txtのアクセス制限内容を守る
・利用規約を確認する
・過度なアクセスをしない
※WEBスクレイピングに関しては自己責任で管理してください。※
2.eMAXIS Slimの先進国株式インデックスの評価額が表示されているページを探す
三菱UFJ国際投信の公式ページに記載がありました。
URL：https://emaxis.jp/fund/252653.html
3.XPathの取得方法
①先ほどのページを開きます。
②基準評価額の箇所にカーソルを当てる
③右クリック＞検証＞右クリック＞Copy＞Copy XPath
とすると、以下情報が取得できます。
/*[@id=”basedata”]/td[2]
4.スプレッドシートに関数を入れる
構文：=IMPORTXML（”URL” , ”XPath”）
=IMPORTXML(“https://emaxis.jp/fund/252653.html”,”//*[@id=’basedata’]/td[2]”)
以上で取得ができるようになります。
WEBスクレイピング可否や取得タイミングが不明確な部分があるので注意してください。基本的には、GOOGLEFINANCE関数を利用していただくのが良いかと思います。
自由なカスタマイズ
スプレッドシートで管理する最大のメリットは、自由なカスタマイズです。私が行っているカスタマイズをいくつかご紹介します。
●資産ポートフォリオ：偏った投資になっていないかなどをチェックします。
●暴落時アラート：主に主要なインデックスに対してですが、5%以上の暴落の際にアラートが出るようにしています。
●利回り計算：投資金額に対して何パーセントで運用できているのかなども確認できる仕様にしています。
最後に
スプレッドシートでの投資管理の魅力でもあるカスタマイズを楽しみながら好みを探していってもらえればと考えています。
最後までご覧いただきありがとうございます。
ご覧頂いた方の参考になればうれしいです。