今回は、ネットにはあまり載っていない情報だったので、自分用のメモとしてブログを書くことにする。
やりたいことは、その銘柄の前週終値に対する騰落率をGoogleスプレッドシートで自動表示できないか、というもの。
うん、最新株価だけではなくて、予想PERとか実績PBRとかも併せて表示できるといいよね、ということで自分なりに試行錯誤して完成させてみた。
参考にさせていただいたサイト
今この時期になってGoogleスプレッドシートでうんうんうなりながらパソコン画面に貼り付けになって知識不足のワタシであるが、昔の偉人たちはすでにこのような使い方を探求していたサイトがいくつかあった。
ワタシが今回参考にさせていただいたブログは以下のとおりだ。この場を借りて御礼を申し上げたい。
VeryGLADのブログ「yahoo financeの時系列データをgoogleスプレッドシートで取得する」
5億円稼いだらニートになるんだ。「Google スプレッドシートで株価情報とか取得しちゃいましょう。」
特にこれらの二つのサイトが分かりやすかった。
御礼を申し上げつつも、ほぼこれらの記事のパクリだったりするので、恩を仇で返すのかと言われそうで気になってしまうが、自分なりに試行錯誤した点や関数をアレンジした部分もあるのでご容赦いただきたい。
あと最初に言っておくが、今回の記事はスマホ向けには書いていない。もしも可能ならパソコンを開いてアクセスしていただけると幸甚である。
Googleスプレッドシートを用意する
今回活用するGoogleスプレッドシートは以下のデータを表示させることとした。
- 銘柄コード
- 銘柄名
- 市場
- 最新株価
- 予想PER
- 予想EPS
- 実績PBR
- 株価（前週終値）
- 週間騰落率
要はこんなシートで自動的に管理したいわけですわ。
実はもっとたくさん自動表示させることは可能であるが、あまり自動表示させすぎると、シートが重くなって表示がLoarding…のまま固まってしまうこともある。
ということで最低限の項目だけ自動表示させることとした。
なお、この中で手入力するのは「銘柄コード」だけ。また、予想PERは最新株価と予想EPSがあれば計算できるし、週間騰落率も株価が分かれば計算できてしまう。
よって、Googleスプレッドシート上で自動表示させるのは、銘柄名、市場、最新株価、予想EPS、実績PBRと株価（前週終値）である。
Googleスプレッドシートに関数を入力する
コピペで対応できた作業
ということでさっそく作業。
簡単なのは、銘柄名、予想EPS、実績PBRの３つ。こちらは先ほどご紹介したサイト、5億円稼いだらニートになるんだ「Google スプレッドシートで株価情報とか取得しちゃいましょう。」の記事、最下部にGoogleスプレッドシートのひな型が公開されていた。
ここに設定されている構文をそのままコピペしてあげればよい。
すなわち…
銘柄名
=ImportXML(CONCATENATE(“http://stocks.finance.yahoo.co.jp/stocks/history/?code=”,A2),”//th[@class=’symbol’]”)
予想EPS
= IMPORTXML(CONCATENATE(“http://kabutan.jp/stock/?code=”,A2),”//*[@id=’kobetsu_right’]/div[ 3]/table/tbody/tr[ 3]/td[ 4]”)
実績PBR
=IMPORTXML(CONCATENATE(“http://kabutan.jp/stock/?code=”,A2),”//*[@id=’stockinfo_i3′]/table/tbody/tr/td[ 2]/text()”)
コピペだけならとても簡単ね。
独自に追加した項目を取得
これ以外の株価、市場あたりの情報はネットには落ちていなかったので、サイトを参考に自力で構文を書いてみた。これができるのも先のブログで結果だけではなくやり方まで書いてくれているおかげだ。本当にありがたい限り。
まず、株価関連。
先ほどの公開されているサイトでは株価取得がヤフーファイナンスから自動取得する設定となっていた。
でも、データをもっているサイトは統一したほうがいいと思うし、ヤフーファイナンスはデータ構造も分かりにくかったので、株価データもかぶたんから引っ張ってくることとした（なお、ヤフーファイナンスはそもそも株価自動取得するスクレイピングを禁止してるという話も聞く）。
最新株価を取得する作業としては、かぶたんのウェブサイトを開き、URLと引用するデータの場所を特定し、Googleスプレッドシートに落としていく。
基本的な構文は=IMPORTXML(URL,xpath)であるので、URLの部分に先ほどのサイトのURLをコピペしてあげればいい。
二つの構文をくっつけるCONCATENATE関数
ただ単にこのURLにコピペしては6035の情報しか取得できなくなるし、編集しようと思うと関数一つ一つに修正を加える必要があってとても面倒である。
例えばセルA2に入力したコードの株を自動取得する、という構文に書き換えれば、シート内でコピペだけで済む。
そこでIMPORTXMLの中にCONCATENATE関数というのを挟むのが常套手段らしい。
CONCATENATE関数は二つの表示をひとつにまとめるというもの。
普通にエクセルでも、二つのセルを”＆”でつなげて表示させることが可能だ。これを関数で行う場合はCONCATENATE関数を使う。２つでも３つでもつなげて表示させることができる。
なので、
- https://kabutan.jp/stock/kabuka?code=6035&ashi=wekの6035の部分をセルの値（セルA2）に置き換える
- &ahi=wekの部分とCONCATENATE関数でつなげる
こうすることで、A列に入力した銘柄コードの株価を自動表示できる。つまりCONCATENATE関数の中はこのように定義できる。
CONCATENATE(“https://kabutan.jp/stock/kabuka?code=”,A2,”&ashi=wek”)
ダブルコーテーションの位置を間違えませんように。
次に”xpath”を特定する。xpathってワタシも聞き馴染みはないんですけど、要はウェブサイトのどのあたりに自動表示させるデータが入ってるのかを示す位置情報のようなもの（あってるかな）。
xPathの特定も簡単。Googlechromeでかぶたんを開きF12を押すと画面右側になんだかわからない英語がいっぱい出てくる。
ここで左上の↑みたいなボタンを押して、引用場所をドラッグ状態にすると、そのデータのある場所を自動的に表示してくれる。これをコピーするだけ。
言葉だけ分からないので画面で示すとこんな感じ。それでも分かりにくいか。
例えば、最新株価のxpathは、
//*[@id=”stock_kabuka_table”]/table[ 1]/tbody/tr/td[ 4]
と表示される。なので、これを先ほどのIMPORTXMLのxpath部分に組み込んでみる。
つまり、IMPORTXML（URL, “//*[@id=”stock_kabuka_table”]/table[ 1]/tbody/tr/td[ 4]” ）
となる。注意点としては、IMPORTXMLで””で括るので、 “stock_kabuka_table” はシングルコーテーションに変えてあげる必要があるとのこと。
xpath
= “//*[@id=‘stock_kabuka_table’]/table[ 1]/tbody/tr/td[ 4]” ）
うーん、ややこしい。
ということでIMPORTXMLでURLの部分とxpathの部分を組み込むとこうなる。
最新株価
=IMPORTXML(CONCATENATE(“https://kabutan.jp/stock/kabuka?code=”,A2,”&ashi=wek”),”//*[@id=’stock_kabuka_table’]/table[ 1]/tbody/tr/td[ 4]”)
なんだかわからない人はコピペしちゃってオッケー。同じ要領で前週終値はこのような式になる。
前週終値
=IMPORTXML(CONCATENATE(“https://kabutan.jp/stock/kabuka?code=”,A2,”&ashi=wek”),”//*[@id=’stock_kabuka_table’]/table[ 2]/tbody/tr[ 1]/td[ 4]”)
あとは市場区分はこちら
=IMPORTXML(CONCATENATE(“http://kabutan.jp/stock/?code=”,A2),”//*[@id=’stockinfo_i1′]/div[ 1]/span”)
慣れてくると結構面白くて自分で簡単に構文を作れるようになりますよ。
最後に
ということでGoogleスプレッドシートで株価騰落率を表示する設定方法についてまとめてみた。
最初はとっつきにくかったが、やってみると結構慣れてきていろんな情報を自動取得できそうだ。ただ、この知識も数週間すると忘れてしまうことだろう。
自分の備忘録も含めてまとめてみた次第である。
自分用に整理しておこうと思ったが、この記事を書くのに2時間半もかかってしまった。そろそろ自分の株の勉強に時間をさくこととしたい。
ぜひ参考とされたし！
