私の投資履歴⑩　Googleスプレッドシートで日本株、米国株の資産管理

2021年4月30日2021年9月13日

前回は、証券会社のアプリやマネーフォワード MEを使った私の資産管理方法について紹介しました。用途ごとにアプリを使い分けていましたが、より自分好みの管理方法に近づけるためGoogleスプレッドシートを使った管理方法にチェレンジしてみようと思いました。目指しているのは以下の管理ができることです。

リアルタイムな資産把握
日本株、米国株、投資信託の一元管理
配当金の月別管理
資産の推移を把握
ポートフォリオのグラフ化

特に、「４．資産の推移を把握」、「５．ポートフォリオのグラフ化」は自分の好みが出るところだと思いますので、自分にピッタリとアプリがなくチャレンジしようと思ってきっかけでした。記事の内容は自由にコピペしてもらって結構ですので、皆さんの好みの資産管理方法の一助になれたらと思います。

現在の株価を自動取得する方法

Googleスプレッドシートで株価を自動取得し表示する方法ですが、米国株は「GOOGLEFINANCE」という関数が用意されていますので簡単に自動取得できますが、日本株には対応していないためスクレイピングで自動取得したいと思います。

米国株

Googleスプレッドシートで米国株価を自動取得する時に使う関数は、GOOGLEFINANCE関数です。ドキュメントヘルプエディタはこちら。構文は以下の通りです。

GOOGLEFINANCE(銘柄, [属性], [開始日], [終了日|日数], [間隔])
・銘柄 – 証券の銘柄コード
・[属性] – 省略可 – デフォルトは “price"

「A4」にティッカーシンボル、「B4」に以下の式を入れると現在の株価が表示されます。最大 20 分まで遅延する場合があります。

=GOOGLEFINANCE(A4)

また、式を直接記入する場合の記入例は、以下のようになります。

=GOOGLEFINANCE(“NASDAQ:AMZN","price")

日本株

日本株の株価の自動取得方法について調べていくと、Yahoo！ファイナンスや株探（Kabutan）を使ったスクレイピング方法に関する記事がみつかりましたが、Yahoo!ファイナンスはスクレイピングが禁止されていることが明示されています。株探利用規約では、第４条（７）に「本サイトのサーバーに不当な負担をかける行為や本サービスの運営を妨げる行為。」が禁止事項であげられており、株探ではスクレイピングはグレーですが、個人利用で過度に負荷をかけない範囲で利用したいと思います。

第４条（禁止事項）
（７）本サイトのサーバーに不当な負担をかける行為や本サービスの運営を妨げる行為。

ところで、『スクレイピングとは？』ですが、ウェブサイトから情報を抽出するコンピュータソフトウェア技術のことです。似たような技術にAPI(Application Programming Interface)がありますが、APIはサービス提供側が一定の条件を設けた上で公開している開発者向けの機能であるのに対し、スクレイピングは公式にサポートされているものではありません。また、スクレイピングは規約に反する可能性があります。例えば、先に紹介したYahoo!ファイナンスの他、Twitterでも利用規約によって明示的に禁止されています。また、スクレイピングはWebサイトの構造が変更されるとデータがうまく取れなくなる可能性があります。URLが変更になった場合も影響を受けますが、その可能性は低いかと思います。

IMPORTXML関数を利用

Googleスプレッドシートでスクレイピングする時に使う関数は、IMPORTXML関数です。ドキュメントヘルプエディタはこちら。構文は以下の通り単純です。

IMPORTXML(URL, XPath クエリ)
・URL – 検証するページの URL です。
・XPath クエリ – 構造化データで実行する XPath クエリです。

具体的に、「三菱ＵＦＪフィナンシャル・グループ(8306)」の株価を株探（Kabutan）からスクレイピングしてみます。まずは「URL」を確認します。Chromeを使って株探へアクセスし、三菱UFJFGを検索してください。URLは、『https://kabutan.jp/stock/?code=8306』でした。『8306』は三菱UFJFGの銘柄コードですのこの値を変えると他の銘柄のページに移動できます。

次に、「XPath クエリ」を確認します。Chromeを使用している場合、そのまま「F12」を押して下さい。すると以下の画面になると思います。

次に「Ctrl + Shift + C」若しくは、〇の部分をクリックして下さい。

そのまま取得したい情報が記載されているところ（今回は株価の部分）をクリックして下さい。すると右側のコードが書かれた画面に色が付きます。

そこで、「右クリック」→「Copy」「Copy Xpath」を選択して下さい。

クリップボードに『//*[@id="kobetsu_left"]/table[1]/tbody/tr[4]/td[1]』がコピーされたと思います。

それでは、Googleスプレッドシートを開いて下図の表を用意して下さい。取得したURLを「B2」に、取得したXPath クエリを「B3」に貼り付け、「B5」に『=IMPORTXML(B2,B3)』と記入して下さい。株価が取得できれば成功です。

数式を直接記入する場合は以下なります。

=IMPORTXML(“https://kabutan.jp/stock/?code=8306“,"//*[@id='kobetsu_left’]/table[1]/tbody/tr[4]/td[1]")

以下の2点について注意が必要です。

「URL」はコピーした内容を半角ダブルクォーテーション（"）でくくること
「XPath クエリ」は、クリップボードのダブルクォーテーション（”）をシングルクォーテーション（’）に変換すること

次に、銘柄コードの書かれたセルを参照して株価を取得できるように変更を加えました。

CONCATENATE関数を使用して、銘柄コードのみをセル参照で取得できる様に修正しました。CONCATENATE関数を使って「URL」の文字列を操作します。「A9」に銘柄コードを手入力します。「B9」に以下の式を入れます。

=IMPORTXML(CONCATENATE(“https://kabutan.jp/stock/?code=",A9),"//*[@id=’kobetsu_left’]/table[1]/tbody/tr[4]/td[1]")

「A9」、「B9」のセルを下の行へコピーすると複数の銘柄の株価を同時に取得できるようになります。

Google Apps Script(GAS)を利用

Google Apps Script(GAS)を使い、①ライブラリの登録、②関数の登録、③Googleスプレッドシートでの利用と非常に簡単な３ステップで株価取得できます。詳細はこちらの記事を参照願います。投資信託の基準価額の取得方法もコードと合わせて載せています。株価の自動記録については、こちらの記事を参照願います。

日付を指定して株価を自動取得する方法

米国株

米国株の株価を日付を指定して自動取得する場合は、以下のようにGOOGLEFINANCE関数の引数に日付を追加することで株価を取得できます。

=GoogleFinance(“AAPL", “price", DATE(2020,1,4))

ただし、このままですと結果が2×2の表で返されてしましました。GOOGLEFINANCE関数を使って過去データの1セル分だけを表示させるには、INDEX関数を組み合わせて表の2列目2行目の値のみを変えるように変更します。

=INDEX(GoogleFinance(“AAPL", “price", DATE(2020,1,4)),2,2)

すると、日付してした株価だけが表示されるようになりました。

それでは一気に2020年1月末を基準に15ヵ月分の月末データを取得する表を作っていきます。

「B11」に先ほどの関数を入力します。「ティッカー」、「日付」を参照できるように編集しています。

=INDEX(GoogleFinance($A11, “price",B$10),2,2)

「B10」～「P10」の月末の日付ですが、手入力でもいいですが、EOMONTH関数を使って月末日を自動計算することもできます。

EOMONTH(開始日, 月)
・開始日 – 表示形式が日付の年月日を指定
・月 – 指定した開始日の何ヶ月後の月末を取得したいのか指定。正の数を指定すると数カ月後を指定、負の数を指定すると数ヶ月前を指定、0を指定すると当月を指定します。

=EOMONTH($B$10,C8)

ちなみに、以下のようにすれば、月初日を指定することもできます。

=EOMONTH($B$10,C8)+1

日本株

日本株の株価を日付を指定して自動取得する場合は、株探でスクレイピングすることでできます。現在の株価をスクレイピングしたのと基本的には同じ方法となります。ここでは米国株と同じく、月末の価格を取得する方法を記載いたします。

まずはURLの確認からですが、株探で銘柄を検索し、「時系列」→「月次」のタブをクリックします。三菱UFJFGのURLは、https://kabutan.jp/stock/kabuka?code=8306&historical=per&ashi=monでした。

次に「F12」を押してコードを表示させ、2021年3月末の株価に関するXpathを取得します。

//*[@id="stock_kabuka_table"]/table[2]/tbody/tr[1]/td[1]

また、2021年2月末、2021年1月末、2018年11月末のXpathは以下となっておりました。

//*[@id="stock_kabuka_table"]/table[2]/tbody/tr[2]/td[1]

//*[@id="stock_kabuka_table"]/table[2]/tbody/tr[3]/td[1]

//*[@id="stock_kabuka_table"]/table[2]/tbody/tr[29]/td[1]

赤字の部分は１か月ごとに増えていることがわかりました。

「C7」のIMPORTXML関数が以下のように編集しました。銘柄コード、月の指定方法を赤字のようにしています。「XPath クエリ」のダブルクォーテーションからシングルクォーテーションへの変更を忘れずに。

=IMPORTXML(CONCATENATE(“https://kabutan.jp/stock/kabuka?code=",$A7,"&historical=per&ashi=mon"),CONCATENATE(“//*[@id=’stock_kabuka_table’]/table[2]/tbody/tr[“,C$2,"]/td[1]"))

今のやり方ですと、毎月表を少しメンテする必要があるため、今後改善したいと思ってます。

おまけ）「B7」の銘柄は、以下のIMPORTXML関数で自動取得させています。=IMPORTXML(CONCATENATE(“https://kabutan.jp/stock/?code=",A7),"/html/body/div[1]/div[3]/div[1]/section/div[1]/div[2]/div[1]/div[1]/h2/text()")