【株式投資・ダーツ・簿記・暮らしの日記】

休みの前日は風呂に入らない。

【GASコード丸ごと】スプレッドシートで株価自動取得・記録

株価自動取得のコード

 
気になる銘柄の株価を
自動で毎日取得したくなった。
銘柄が多くなってくると、手動でするのが億劫になってきたからだ。


moneylab.f-academy.jp

取得した方がいいデータについては
こちらのサイトを参考にさせていただいた!
ありがとうございます!


スクレイピングすなわちデータ取得をするので
Googleドライブのスプレッドシートを使う。
GAS(Google Apps Script)でやりたいことを実現する。
ちなみに、GASは
Googleが提供しているプログラミング言語のこと。

エクセルでも同様のことは実現可能のようだが
それはWindowsのエクセル限定らしい。
当方はMacのためエクセルではなく
Googleドライブのスプレッドシートを使うことにした。

設計図

 
やりたいことを元に設計図を考えた!

①列の最後に新しく追加。
②1行目にその日の日付を入力。
③シートのB列にまとめてある、調べたい法人の証券コード(銘柄コード)を、配列に格納し、配列から値を取り出せるようにする。
④③の配列から証券コードを取り出し、株価を調べられるサイトからスクレイピングする。
⑤④を調べたい法人の数だけ繰り返す。
⑥④のデータのままでは数式になっているので、同じセルへ値として貼り付ける。
⑦①〜⑥までの処理を、土日祝日以外、毎日決まった時間に繰り返す。


完成はこんなイメージになる。
f:id:plasticbagg:20200430103231p:plain

株価自動取得のコード

 
以上を踏まえて
早速、今回完成したコードを載せる。
なお、素人が作ったコードなので
うまく動かない(それはないはず)、無駄が多い、美しくない等。
あしからず!

①について

 
①列の最後に新しく追加

これをするには、データが入力されている列の一番最後の列番号を取得する必要がある。
その上で、その列の右隣に新たに列を挿入する。

特定の行、列の最後の番号を取得する方法について

 
moripro.net

こちらのサイトの内容をそのまま使わせていただいた!
ありがとうございます!

列の数字をアルファベットへ変換

 
そのままでは列が数字のままなので
数字を列のアルファベットに変換したい。

それについてはこちらのサイトで!

hiyonomemo.blogspot.com

こちらもそのまま使わせていただいた!
ありがとうございます!

②について

ちなみに、その日の日付の入力については
スプレッドシート→ツール→マクロ→マクロを記録
を利用して
「コマンドキー⌘」+「:」(Macなので)
その日の日付を入力するコードを取得した。

ここだけは簡単だった!

③について

 
③シートのB列にまとめてある、調べたい法人の証券コード(銘柄コード)を、配列に格納し、配列から値を取り出せるようにする。

配列を用意してそこから参照する

 
それについてはこちらのサイトで。

www.atmarkit.co.jp

基本的な骨組みはこちらを活用させていただいた!
ありがとうございます!

④について

 
④③の配列から証券コードを取り出し、株価を調べられるサイトからスクレイピングする。
 
株価のスクレイピングについては
こちらの株探(カブタン)を利用させていただいている。
kabutan.jp

Importxmlの使い方等について

 
どの要素を引っ張って来れば株価が取得できるかわからなかったが
こちらのサイトが大変わかりやすかった。
検証を押せばよかったのか!

xn--t8j3bz04sl3w.xyz

大変助かりました!
ありがとうございます!


そのままでは空白行からデータを取得した場合に
ヌルポになるのでiferror文を使った。

excel-ubara.com

こちらをそのまま使わせていただいた!
ありがとうございます!

⑤について

 
⑤④を調べたい法人の数だけ繰り返す。

これについては、③の配列のところでfor文を使ったので
そこに追加すればいい。

⑥について

 
⑥④のデータのままでは数式になっているので、同じセルへ値として貼り付ける。

これについても、②の日付入力と同様
マクロを記録
を利用して、値を貼り付けするコードを取得した。

しかし、for文の中で
スクレイピングのコードを入力後
それをコピーして値だけ貼り付ける
これはできなかった!

理論的なことはわからないが
感覚的には
スクレイピングのコード自体が
スプレッドシートに表示されることで初めて効力を発揮するものなので
一度セルに表示されてからでないと
コピーの参照先が存在しないため
それをコピーして値の貼り付けというのはできないのではないか。

そこで、なんとなく
コピーして値を貼り付ける専用のメソッドを別に用意して
for文の最後にそのメソッドを呼び出すようにしたところ
株価をスクレイピングした後
それをコピーして値だけ貼り付けることができた!

⑦について

 
⑦①〜⑥までの処理を、土日祝日以外、毎日繰り返す。

どうにかコードは完成し
思い通りの挙動も確認した。

あとは、定期的に自動で起動するようにしたい。

トリガーが任意のシートで起動しない

 
しかし、GASの便利機能であるトリガーを設定しても
どうにもうまくいかない。

検証用に1分置きに起動するようにしているが
任意のシートにおいてメソッドが実行された気配がない。
もしかしたらメソッド内において
任意のシートを指定する必要があるのかと思い色々修正したが
それでもうまくいかない。

そんな中、こちらのサイトを拝見したところ
GASを設定したときに、任意のスプレッドシートで使用していいか
許可をする画面があるらしい。

www.pre-practice.net

最初にコードを書いていたときには
ツール→マクロ→マクロを記録
このマクロを色々修正してコードを書いていた。

一旦新しいスプレッドシートを起動して
スクリプトエディタに
先ほど書いたコードをコピペした。

そして実行をしようとしたところ
このスプレッドシートでよいかという確認が!

許可を出したところ
任意のスプレッドシートで問題なく動いたのだ。
これでトリガーも使えるようになった。


しかし、何回か実行していたら
なぜか「このアプリは確認されていません」
という表示がでて起動できなくなった!

これについては

www.virment.com

こちらのサイトですぐに解決した!
ありがとうございます!

決まった時間に起動させる

次は、決まった時間に起動するようにしたい。
株式市場は、15:00で終了する。
名古屋の証券取引所は15:30で終了するらしい。
そこで、17:00に株価を取得できるようにしたい。

ところが、GASのトリガーはデフォルトでは
毎日特定の時間に起動させることはできず
1時間くらいの幅で起動させるしかない。

決まった時間に起動するコードは
こちらのサイトのコードをそのまま使わせていただいた!

tonari-it.com

ありがとうございます!

トリガーを削除する

 
そして、このコードでは
トリガーを毎回作成するので
トリガーがどんどん増えていってしまう。
そこで、作成したトリガーをその都度削除する必要がある。

これについても
先ほどのサイトでコードを紹介してくれていた!
それがそのまま使えた!

tonari-it.com

ありがとうございます!

土日祝日はトリガーが起動しないようにする。

 
さらに、土日祝日は株式市場がお休みのため
土日祝日には起動しないようにする必要がある。

ちなみに、株価情報の記録だけであれば
15:30以降に記録できればいい。
そのため、厳密にこの時間に起動するように
指定する必要はない。
しかし、土日祝日は起動しないようにするために
処理を追加しなければいけないため
そのついでに時間指定の処理も追加した。
そのため、トリガーのメインは
土日祝日は起動しないようにすることである。

土日祝日は起動しないようにするにあたり
先ほどまでのサイトのコードをベースにした。
コードがシンプルでコンパクトだったからだ!
感謝しております!
tonari-it.com

しかし、そのままでは
date初期化されておらず使えなかったので
var date = new Date();
この一文を最初に追加した!

こちらのサイトのコードを参考にした!

qiita.com

後始末について

 
このスプレッドシートを使わなくなった場合
スプレッドシートを削除しただけでは
トリガーは起動しつづける。
そのため、トリガーを削除することを忘れないようにする。

qiita.com

ミスした内容等

 
今回、久々にコーディングした!
3年ぶりくらいだったため
ありがちなミスを色々した。

ミス1

 
;
をつけ忘れた!

ミス2

 
O1と01を混同した!
アルファベットの半角大文字のOと、数字の0!

その他

 
コードを書いてそれが一発で思い通りに動くことなんてない!
そのため、どこまで正常に動いているかを確認していく必要がある。
ところが、久々過ぎてその行程を疎かにしてしまった。
あまりにもうまくいかないため、途中で気がついた!

検証にはログを確認するのが一般的だろうが
自分としては、アラートを表示するのが気分!

SpreadsheetApp.getUi().alert(repet);

これで検証するようになって
最終的には完成までこぎつけた!

まとめ

 
以上で、株価が自動で記録できるようになった。
これはあくまで環境に過ぎず
肝心の、どの株を買うか等については
これから学んでいこうと思う!

同じようなことをしたいと考えている方にとって
少しでもお役に立てたら幸いです!

UrlFetchAppを使う場合
一旦全てのデータをスクレイピングして
必要な部分だけに加工する。
これから取り出したい情報が増えたら
UrlFetchAppの使用も検討するか。