見出し画像

ポートフォリオを一目瞭然へ。日米株と米国ETFと投資信託の株価（一部配当も）をスクレイピングで自動取得する

うさ

理想のポートフォリオに向けて

資産配分をするためには、現在の資産バランスを簡単に把握する必要があります。
ところが証券口座はログインまでが面倒だったり、資産が別口座に分かれていたりするので、全資産を簡単にまとめて見るのに手間が掛かります。
ヤフーファイナンスを使ってますが米国ＥＴＦを見る事ができないのです。
また、購入や売却でどう変化するかのシミレーションは証券口座ではできないです。

資産管理アプリを証券口座とリンクさせて、ポートフォリオを表示する方法もある、マネーフォワードとかカビョウとかmoomooなどなど。
これらでも良いと思うが、証券口座のログインIDとパスワードを連携させなきゃいけない。取引パスワードを入れないので勝手に資産が移動されることはないでしょうが、メルアド、氏名から資産金額まで筒抜けになるのは怖かいのでやめました。

　そこで、エクセルやGoogleスプレッドシートで、株価を自動取得して資産管理する方法を探しました。
エクセルしか知らなかったけれど、最終的にはGoogleスプレッドシートを使う事にしました。なにせ無料だし、PCでもスマホでも簡単に見ることができます。

Googleスプレッドシートは、ほぼエクセルです。
セルに株式を登録し、最新株価を自動取得させれば、資産状況が一目でわかる。まずはどんなものか紹介しよう（自慢か！）

基本フォーマット

証券コード、数量を入れておくと「最新株価」と「配当率」は自動取得できます。株価や配当率は　「株探」や「Googlefinance」「Bloomberg」などのサイトからオンラインで取得します。取得してしまえば、あとはエクセル同様に自分が欲しいように計算するだけです。

投資ポートフォリオの把握が簡単

計算結果は上のようにまとめました。ポートフォリオバランスを見るのが目的なので、個別の株価の前日比は出してません（それはヤフーファイナンスか、証券サイトでみればいいと割り切った）

ツリーマップグラフで、「ヒートマップ風」も表示しました。前日差は出してないので、本日のヒート感はまったくないですが、銘柄のリバランスには役に立ちます。

ヒートマップ風グラフ

株価を自動取得するには、スクレイピングという方法で取れます。しかし、これを調べて関数を作るのには相当に試行錯誤しました。
スクレイピングの方法はあちこちで紹介されているので、この記事では要点のみ記述していきます。

役に立ったサイト

スクレイピングの手法など参考にしたサイトは以下です。
みなさんありがとうございます。

苦労した点

せっかく丁寧なサイトがあったのに、自分が試行錯誤した所はここです。取得するサイトの構造により、スクレイピングも取り方を変えないとうまくいきませんでした。

・取得方法によっては株価が”文字”になり、数値計算ができない
・末尾に”円”が付いたり、同時に複数のデータを取ってしまう
・米国株、日本株、日本投資信託、米国ETF、配当など取得方法が異なる
・スクレイピング関数は５０を超えると不安定（読み込めなくなる）

　試行錯誤して、なんとか対処したポートフォリオシートを作り、これで３ヶ月くらい運用してます。手っ取り早く作成したい人は、この記事の最後に作成したSheetファイルを共有しますのでコピーして使って下さい。有料としますが、時間の節約にはなると思います。

スクレイピングの関数一覧

日本株と日本ETFの株価取得

最初はIMPORTXML関数で下記の様に取得していましたが、取得数が多くなるとLoadingエラーで固まります。
「IMPORTXML関数を減らしてください」と、うまく取れません。

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

　株価と配当の両方をIMPORTXML関数で取得すると、1銘柄で2回使ってしまいます。色々試して見ると、IMPORTXML関数は、１シートで５０を越えると不安定になります（別タグにしても制限は緩和されない）
株価と配当率と1銘柄に2箇所使うので２５銘柄くらいしかとれません。
そこで、株価の取得はGoogle Apps Script（マクロ　以下GAS）を使いました。GASにすることで、自分の環境では４０～５０銘柄くらいの株価と配当率を取得できるようになりました。

最初は「株探」だけから取得してましたが、「株探」は場中は取引がないと（出来高がゼロで初値がない）と株価を取得できずエラーとなるみたいです。そこで「株探」と「Googlefinance日本」の2つから取得するGASを作り、株探でエラーがでたらGoogleで取得するようにしました。

=iferror(getStockPriceKabutan(A1),getStockPriceGoogle(A1))

※　GASのインストールや使い方は、下記を参考にさせて頂きました。

上記で使用した「getStockPriceKabutan」と「getStockPriceGoogle」のGASコードはこれです。

function getStockPriceKabutan(code) {
  var url = "https://kabutan.jp/stock/?code=" + code;
  var html = UrlFetchApp.fetch(url).getContentText();
  var stockPriceText = Parser.data(html)
    .from('<span class="kabuka">') 
    .to('</span>')
    .build();
  var stockPrice = parseFloat(stockPriceText.replace(/[^0-9\.]/g, ""));
  return stockPrice;
}
function getStockPriceGoogle(code){
  let url = "https://www.google.com/finance/quote/" + code + ":TYO";
  let html = UrlFetchApp.fetch(url).getContentText();
  let StockPriceGoogle = Parser.data(html)
    .from("<div class=\"YMlKec fxKbKc\">")
    .to("</div>")
    .build();
  return StockPriceGoogle.replace("¥","");
}

日本株の配当率

高配当株を集めたいのでおおよその年間配当金が知りたいです。
そこで日本株の配当率を株探とBloomberg(エラー時)で取得しました。日本リートなどもこれで対応できてます。
　ただ、配当率はあくまでも過去の実績を元にしているので、今後の配当金は会社の業績や方針で変わってしまう。あくまでも参考程度とするべきでしょう。

=iferror(INDEX((IMPORTXML(CONCATENATE("https://kabutan.jp/stock/?code=",A1),"//*[@id='stockinfo_i3']/table/tbody/tr[1]/td[3]")),1,1)/100,IMPORTXML(CONCATENATE("https://www.bloomberg.co.jp/quote/",A1,":JP"),"//*[@id='content']/div/div/div[8]/div/div/div[13]/div[2]"))

日本ETFの配当率

　証券情報サイトでも、日本のETFの配当率は表示していないサイトがほとんどです。これは、日本株と異なり、日本ETFの配当金は企業IRなどで予想が発表されず、過去実績から予測するだけになりますし、年に複数回配当を出すところでは、季節変動もあるから表示しないのだと思います。

例えば、「NF・日経高配当50 ETF」(1489)の配当金は下記のとおり

2022年10月_940円　23年1月_145円　23年4月_966円　23年7月_104円　　

直近の配当金で計算すると、1月で計算すると年間配当金は580円　4月に計算すると3864円となる。実際の年間配当金は2155円なので、これでは大きく違ってしまいます。

ただ、銘柄によっては分配金の変動が少なく、それほど大きく差が出ません。そこで一応、取得する方法は記述しますが、結果は不正確です。
IRなどで配当金情報を見て、年間配当金額を直接入力することを強くお勧めします。

=IMPORTXML(CONCATENATE("https://www.bloomberg.co.jp/quote/",A54,":JP"),"//*[@id='content']/div/div/div[8]/div/div/div[15]/div[2]")

日本投資信託の株価

日本の投資信託は、日本経済新聞社のサイトで取得してます。
※　追記　日経サイトはデータ構成が頻繁に変わりますので、取得方式を改めました.販売している記事の方では、修正していますが、こちらの項目は取得関数を削除しました。

米国株およびETFの株価

Googleの株価関数とFinvizから取得してます。Googlefinanceは不安定で場が立っていると取れなかったりしたため、エラーがでたらFinvizから取るようにしています。

=iferror(GOOGLEFINANCE(A1),IMPORTXML(CONCATENATE("https://finviz.com/quote.ashx?t=",A1),"/html/body/div[2]/div[2]/div[4]/table/tbody/tr/td/div/table[1]/tbody/tr/td/div[2]/table/tbody/tr[11]/td[12]/b"))

米国株およびETFの配当率

なお、配当はGooglefinanceからはうまく取れない事が多く、Bloombergとfinvizから取るようにしました。
追記：こちらも取得サイトのデータ構造が変化したため、こちらでの公開は停止しました。

使用したポートフォリオファイルを有料記事として掲載します。面倒な方はこちらを購入して頂けると感謝致します。有料としますが時間とストレスの節約にはなると思います。
　追記：本記事公開後　取得サイトデータ構造が変化したため有料記事の方は対応しております。

お約束

　スクレイピングは保証されていないものですので、サイト構造の変化により当然使えなくなる可能性があります。また、スクレイピングが禁止される可能性もあります。（ヤフーファイナンスなどは禁止）
　本記事はスクレイピングを含めて推奨するものではなく、安定性や実行結果に責任を持つものではありません。活用は投資も含めて自己責任でお願い致します。
　本記事およびコードの著作権は放棄しておりません。引用や使用においては事前にご連絡ください。

勝手ですが、参考になりましたら　「スキ（♡）」をお願いいた
します。


この記事が気に入ったらサポートをしてみませんか？