Googleスプレッドシートで株価と配当情報を取得する

先日個人投資家の嗜みである株管理のエクセルを Googleスプレッドシート に移行中である記事を取り上げました。

と書きましたが別の方法で配当情報を取得できました。

GOOGLEFINANCE関数 とあわせて Googleスプレッドシート で株価情報を取得する方法を紹介します。


GOOGLEFINANCE関数

Google Finance から過去・現在の株価情報を取得する関数です。

面白いですよね。インターネットから直接取得してくる。いまはエクセルも株価情報を取得できるようになりましたが Google が先行していた記憶です。ソフトウェアとインターネット、どちらを主導に培ってきたのか文化でしょうか。良し悪しではなく、切磋琢磨していることを感じます。

GOOGLEFINANCE関数 のヘルプはこちらです。構文は下記の通り。

GOOGLEFINANCE(銘柄, [属性], [開始日], [終了日|日数], [間隔])

銘柄

「取引所コード」+「ティッカー」となりますが「取引所コード」を省略すると市場を自動判別してくれるそうです。私が使っている限り省略してもきちんと取得してくれます。

例  “NYSEARCA:VYM"  “NASDAQ:MSFT"

取引所コードとティッカーの間にはコロン、文字入力する場合はダブルクォーテーションを入れましょう。セル引用であれば B2&":"&C2 のように記入します。この辺りはエクセルと同じです。自分は下記のような表を作成して参照させています。

ティッカー種別市場セクター
VTIETFNYSEARCA全米
VHTETFNYSEARCAヘルスケア
BNDETFNASDAQ米国債券
VYMETFNYSEARCA高配当
VTETFNYSEARCA全世界
VOOETFNYSEARCAS&P500
HDVETFNYSEARCA高配当
VDCETFNYSEARCA生活必需品
QQQETFNASDAQ情報技術
MSFT個別NASDAQ情報技術
PG個別NYSE生活必需品
JNJ個別NYSEヘルスケア
GIS個別NYSE生活必需品
MMM個別NYSE資本財
PEP個別NASDAQ生活必需品
CSCO個別NASDAQ情報技術
T個別NYSE電気通信
XOM個別NYSEエネルギー
PM個別NYSE生活必需品
RTX個別NYSE資本財
KO個別NYSE生活必需品
MCD個別NYSE一般消費財
RDS.B個別NYSEエネルギー
クラゴロファンドの採用銘柄

属性、開始日など

リアルタイムデータと過去データで利用する値が異なります(投資信託データ用の属性もありますが米国内の投信はよくわかりませんw)。

詳細はヘルプ(リンク)を参照してください。私は下記の属性を利用しています。

  • リアルタイムデータ
    • “price" – リアルタイムの見積価格。最大 20 分まで遅延する場合があります。
    • “pe" – 株価収益率。
    • “eps" – 1 株当たりの収益。
    • “changepct" – 前取引日の終値からの株価の変動率。
  • 過去のデータ
    • “close" – 指定した日付の終値。

こんな具合に表示されます。ETF ではpe(per)とepsが取れないものもあるようです。個別株ではないのでさほど問題ありません。

使用例です。リアルタイムデータでは日付指定は不要です。"close"属性は指定日の終値を取得できます。

  • “price" – GOOGLEFINANCE(“VYM","price")
  • “pe" – GOOGLEFINANCE(“VTI","pe")
  • “eps" – GOOGLEFINANCE(“VTI","eps")
  • “changepct" – GOOGLEFINANCE(“VYM","changepct")
  • “close" – GOOGLEFINANCE(“VYM","close","2020/6/30″)

セルの中にミニグラフを書けるのですが(この機能はエクセルに無いと思います)このように指定しています。TODAY()-[日数] では、今日から指定日数の遡りを指定しています。

SPARKLINE(GoogleFinance(“VYM","price", TODAY()-[日数], TODAY()))

SPARKLINE関数 でミニグラフを作成できます。構文は SPARKLINE(データ, [オプション]) なのですがオプションを省略すると折れ線グラフ(line)となります。


配当

前回の記事では挫折していた配当情報の取得です。GOOGLEFINANCE関数 には配当情報がありませんので、どこからかスクレイピングすることになります。

当初は finance.yahoo.com から IMPORTXML関数 で取得を試みましたが、取れたり取れなかったり安定しません。さっきまで取得出来ていたのに気づいたら #N/A とかなってるし。ネットの海で探してもみんな苦労しているようでした。

結局 finviz.com から IMPORTHTML関数 で取得させてもらうことにしました。finviz.com はヒートマップの提供で有名なサイトですね。

https://finviz.com/map.ashx?t=sec

構文は下記のようにしています。B2 部分は参照セルで「ティッカー」を示します。

SUBSTITUTE(INDEX(IMPORTHTML(“https://finviz.com/quote.ashx?t="&B2,"table",11),8,2),"*","")

マイクロソフト(MSFT)のサイトで解説します。

https://finviz.com/quote.ashx?t=MSFT

IMPORTHTML(“https://finviz.com/quote.ashx?t=MSFT","table",11)

IMPORTHTML関数 を使いこのURL11番目の要素テーブル形式で取得します。

INDEX(IMPORTHTML(“https://finviz.com/quote.ashx?t="&B2,"table",11),8,2)

INDEX関数 を使い取得したテーブルの 8行目、2列目の値を取得します。

SUBSTITUTE(INDEX(IMPORTHTML(“https://finviz.com/quote.ashx?t="&B2,"table",11),8,2),"*","")

SUBSTITUTE関数 を使い「*」の文字を「」に変換(削除)します。

これで「1.01%」という値を取得できました。あとは株価を掛けることでパーセントから実数(額)に、保有株数から配当額予想などを算出しています。


自分も同じですが配当の取得では皆さん結構苦労されているようです。使い慣れている finance.yahoo.com から取得できるとよかったんですけどね、目的は達成できましたのでよかったです。

雑談

Posted by craftgoro