はじめに
Google Apps Scriptではトリガーを用いて定期的にスクリプトを実行出来ると聞き、勉強がてら自動取得と自動更新を作ってみようと思い立ちました。
常に変わり続ける数値といえば、株価です。
株価を定期的にモニタリングするようなスクリプトを作成しています。
当エントリ中で扱っている株及び投資信託につきましては、実例としてのサンプルであり購入・保有を推奨するものではありません。
Google Apps Script のはじめかた
Google Apps Scriptってどこからどうやれば? という方はまず下記を参照ください。
Google Apps Script で Google スプレッドシートを操作する
Google Apps Scriptからスプレッドシートを扱う方法については下記を参照ください。
1. 株価を取得する
株価の取得方法を、米国株、日本株、そして投資信託の3パターン紹介します。
1.A. 米国株と為替レートを取得する(GOOGLEFINANCE関数)
スプレッドシートにはGOOGLEFINANCE()という関数が提供されています。
米国株限定ではありますが、1関数のみで株価を取得出来るのは非常に手軽です。
セルに=GOOGLEFINANCE(銘柄コード)を設定する事で、対象銘柄の株価を取得して自動的に更新し続けるセルになります。
米国APPLEの銘柄コードは「AAPL」なので、=GOOGLEFINANCE("AAPL") と入力すればAPPLEの株価を取得し続けるセルになります。
=GOOGLEFINANCE("銘柄コード") //株価取得
=GOOGLEFINANCE("AAPL") //米国APPLEの株価を取得
下記では、C3セルの銘柄コードを用いて、米国APPLE株の株価を取得しています。
また、=GOOGLEFINANCE("[3桁通貨コードA][3桁通貨コードB]")と設定することで、A/Bの為替レートを取得することもできます。
為替コードは、米ドルはUSD、日本円はJPYなので、=GOOGLEFINANCE("USDJPY")と入力することで米ドル/円の為替レートを取得し続けるセルになります。
=GOOGLEFINANCE("[3桁通貨コードA][3桁通貨コードB]") // A/Bの為替レート取得
=GOOGLEFINANCE("USDJPY") // 米ドル/日本円の為替レート取得
米国株価(米ドル)と、為替レート(米ドル/日本円)をかけ合わせることで、米国株の日本円での時価を算出することが可能です。
GOOGLEFINANCE関数の詳細はGOOGLEFINANCE - Googleドキュメントエディタヘルプを参照ください。
GOOGLEFINANCE関数では、日本株、及び投資信託の株価を取得することはできません。
1.B. 日本株の株価を取得する(GASでスクレイピング)
GOOGLEFINANCE関数では日本株の株価を取得できないということで、Google Apps ScriptでWebスクレイピングを行うことで株価を取得します。
Webスクレイピングは運営サイトが許可しているかを予め確認する必要があります。
また、許可されている場合でも、過度な付加をかけるとアクセスを制限される可能性もありますので、スクレイピングを行う際はご注意ください。
1.B.1. Parserライブラリを追加する
Webスクレイピングの前準備として、Parserライブラリをプロジェクトに追加します。
スクリプトエディタ画面の「ライブラリ」項の右の「+」をクリックすると「ライブラリの追加」ダイアログが表示されます。
「スクリプトID」欄に以下のIDを入力し「検索」をクリック。
1Mc8BthYthXx6CoIz90-JiSzSafVnT6U3t0z_W3hLTAX5ek4w0G_EIrNw
すると「バージョン8」の「Parser」ライブラリが表示されますので「追加」をクリックしてください。
「ライブラリ」項に「Parser」が追加されていれば成功です。
1.B.2. 株価取得関数の作成
スクリプトエディタ上で、株価を取得する関数を作成します。
引数として「銘柄コード」を与えると、GoogleファイナンスのWebサイトで株価をスクレイピングし、返り値として「株価」を返す関数となります。
まずは関数の完成形を以下に示し、その後1行ごとに解説します。
function getStockPriceGoogle(code){
let url = "https://www.google.com/finance/quote/" + code + ":TYO";
let html = UrlFetchApp.fetch(url).getContentText();
let stockPrice = Parser.data(html)
.from("<div class=\"YMlKec fxKbKc\">")
.to("</div>")
.build();
return stockPrice;
}
まず、銘柄の株価を反映するWEBサイトのURLを指定します。
//Googleファイナンスの銘柄のwebサイトのURL
let url = "https://www.google.com/finance/quote/" + code + ":TYO";
次に、UrlFetchAppクラスを用いて、そのWebサイトにFetchしたHTTPレスポンス情報を文字列として取得します。
//URLから表示される情報を文字列で取得
let html = UrlFetchApp.fetch(url).getContentText();
取得した文字列情報から、Parserライブラリを用いて株価部分のみを切り出します。
htmlの<div class=\"YMlKec fxKbKc\">から</div>の間に株価が表示されているため、その部分のみを切り出します。
//テキストから株価部分のみを切り出す
let stockPrice = Parser.data(html)
.from("<div class=\"YMlKec fxKbKc\">")
.to("</div>")
.build();
最後に、切り出した株価を関数の戻り値として返却しています。
//取得した株価の文字列情報を返却
return stockPrice;
実際にこちらの関数をテストしてみます。
myFunction()にテスト用コード(今回はタカラトミー株)を追加し、console.log()で値の取得状況を確認します。
無事に取得できたようです。
Googleファイナンスから取得した株価情報の文字列は、先頭に半角の円記号がついているため、そのままでは数値として計算に使用できません。Parserの.fromで円記号を表す文字コード\xA5まで含めるか、取得した文字列に対し半角の円記号を削除し、数値だけの値に変更する必要があります。
let stockPrice = Parser.data(html)
.from("<div class=\"YMlKec fxKbKc\">\xA5") //半角円記号以降を切り出し
.to("</div>")
.build();
1.B.3 株価取得関数をセルに設定
=getStockPriceGoogle(code)をセルに入力すると、そのセルに対象銘柄の株価を取得し続ける事が可能です。
しかし、このままだと高頻度で更新を続けてしまい、Webサイト自体に付加がかかったり、リクエスト制限の上限に達する可能性もありますので、セルへの直接入力は反映確認だけに止め、一旦削除します。
UrlFetchApp.fetchのHTTPリクエストには制限があります。
無料のGoogleアカウントユーザーでは2万回/日、有料のGoogle Workspaceユーザーでは10万回/日の制限があるため、多数の銘柄の取得関数をセルに直接設定してスクレイピングし続けると、リクエスト回数制限の天井を叩くことになります。
1.C. 投資信託の基準価格の取得(GASでスクレイピング)
1.B. 同様、投資信託の基準価格もParserライブラリを用いたWebスクレイピングで取得可能です。
以下は楽天証券から投資信託の基準価格を取得する関数です。
詳細は日本株の株価取得と同様なので割愛します。
function getPriceToshin(code){
let url = "https://www.rakuten-sec.co.jp/web/fund/detail/?ID=" + code;
let html = UrlFetchApp.fetch(url).getContentText();
let price = Parser.data(html)
.from('<span class="value-01">')
.to('</span>')
.build();
return price;
}
2. 株価をGASからスプレッドシートに反映する関数を作成する
日本株及び投資信託の株価をスプレッドシートの特定のセルに出力する関数を作成します。
証券コードのセルと、株価のセルは以下の通り。
| 銘柄 | 証券コード | 株価 |
|---|---|---|
| 日本株 | C2 | D2 |
| 投資信託 | C4 | D4 |
上記踏まえ、証券コードから株価を取得しセルに戻す処理を作成します。
2.1. セルの値を取得する関数
記述を簡素化するため、セルの値を取得する関数を作成しました。
function getCellValue(cell){
//親スプレッドシートの先頭シートの指定セルの値を取得し返却する
return SpreadsheetApp.getActive().getSheets()[0].getRange(cell).getValue();
}
2.2. セルに値を挿入する関数
同様に、セルに値を挿入する関数を作成しました。
function insertCellValue(cell, value){
//親スプレッドシートの先頭シートの指定セルに値を挿入する
SpreadsheetApp.getActive().getSheets()[0].getRange(cell).setValue(value);
}
2.3. 国内株の株価を反映する
国内株の銘柄コードのセルを渡すと、株価を取得した上で所定のセルに書き戻す処理です。
function updateStockPrices(inputCell, outputCell){
let stockCode = getCellValue(inputCell); //銘柄コードのセルから銘柄コード取得
let stockPrice = getStockPriceGoogle(stockCode); //銘柄コードから株価取得
insertCellValue(outputCell,stockPrice); //株価をセルに挿入
}
2.4. 投資信託の株価を反映する
投資信託の銘柄コードのセルを渡すと、基準価格を取得した上で所定のセルに書き戻す処理です。
function updateToshinPrices(inputCell, outputCell){
let toshinCode = getCellValue(inputCell); //銘柄コードのセルから銘柄コード取得
let toshinPrice = getPriceToshin(toshinCode); //銘柄コードから投資信託の基準価格を取得
insertCellValue(outputCell,toshinPrice); //基準価格をセルに挿入
2.5 国内株の株価と投資信託の基準価格を一括更新する関数
2.3. と 2.4. で作成した関数を順に呼び出す関数を作成します。
function updatePrices(){
//日本株
updateStockPrices("C2","D2")
//投資信託
updateToshinPrices("C4","D4");
}
実行することで、株価と基準価格が反映されました。
3. 一括更新処理の定期実行
更新させたい頻度により、株価を一括更新するスクリプトを定期実行させます。
今回は1時間に1回とします。
3.1. 定期実行の設定
スクリプトエディタ左の目覚まし時計アイコンの「トリガー」をクリック。
右下の「+ トリガーを追加」もしくは「新しいトリガーを作成します。」をクリックすると、トリガー追加ダイアログが現れます。
「株価の一括更新関数」を「1時間毎」に呼び出すため、以下のように設定し「保存」をクリックします。
| 項目 | 設定値 |
|---|---|
| 実行する関数 | updatePrices |
| イベントのソース | 時間主導型 |
| 時間ベースのトリガーのタイプ | 時間ベースのタイマー |
| 時間の間隔 | 1時間おき |
これで、1時間毎に株価と投資信託の基準価格を一括更新する準備が整いました。
3.2. 定期実行の確認
スクリプトエディタ画面左、トリガーの下にある「実行数」をクリックすると、スクリプトの実行数の画面に切り替わります。
「+ フィルタを追加」から「種類」「時間主導型」と選択すると、時間により実行されたスクリプトの一覧が表示されます。今回作成・設定した「updatePrices」が1時間に1回実行されているのが分かります。
4. 株価の定期取得リスト作成
株価が1時間に1回更新されることを確認した上で、更に更新したデータを表として自動的に記録していきます。
取得した株価・基準価格と、保有株数・口数をかけ合わせ、時価評価額を算出したセルをG列に設定しておきます。
4.1. 表シート作成
各銘柄と、更新日時を項目としたリストを、シート名「表」として作成しました。
この表に対し、定期更新される時価評価額を最下段に追加していく関数を作成します。
4.2. 表へのレコード自動挿入関数
これまで作成してきた「株価」シートの「時価評価額」を、「表」シートの各項目欄の最下段に追加していく関数です。
定数enumを使って取得セルの位置と出力カラム位置を設定しています。
また、表の追加行は、getLastRow()でデータが存在する最下行を取得した上で+1として算出しています。
function updateStockPriceList(){
//時価評価額セル
const eDataCell = {
TAKARATOMY : "G2",
APPLE : "G3",
EMS_SP : "G4"
}
//表出力カラム
const eColumn = {
DATE : 1,
TAKARATOMY : 2,
APPLE : 3,
EMS_SP : 4,
}
//株価シートを取得
let sheetStock = SpreadsheetApp.getActive().getSheetByName("株価");
//表シート取得
let sheet = SpreadsheetApp.getActive().getSheetByName("表");
//表シート最終行の次の行を取得
let row = sheet.getLastRow()+1;
//株価定期取得シートから表シートへデータをコピーする
//タカラトミー
let valueTT = sheetStock.getRange(eDataCell.TAKARATOMY).getValue();
sheet.getRange(row, eColumn.TAKARATOMY).setValue(valueTT);
//APPLE
let valueAP = sheetStock.getRange(eDataCell.APPLE).getValue();
sheet.getRange(row, eColumn.APPLE).setValue(valueAP);
//eMAXIS Slim 米国株式(S&P500)
let valueEMS = sheetStock.getRange(eDataCell.EMS_SP).getValue();
sheet.getRange(row, eColumn.EMS_SP).setValue(valueEMS);
//更新日時
let date = Utilities.formatDate(new Date(), 'Asia/Tokyo', 'YYYY/MM/dd HH:mm:ss');
sheet.getRange(row, eColumn.DATE).setValue(date);
}
上記スクリプトを手動で実行すると、以下のようにリストに追加されます。
4.3. 株価のリスト追加関数の定期実行設定
3 での株価の定期取得同様、こちらもトリガーを使って定期実行するように設定します。
こちらも1時間ごとに実行するように設定しました。
1時間毎の定期実行を2回待ってみた結果です。
実行数にupdateStockPriceListの動作ログが記録されました。
実際の「表」シートにも、最新のレコードが追加されたことが確認できました。
おわりに
今回は「株価の定期取得と表への自動追加」をGoogle Apps Scriptで実現しました。
株価のスクレイピングについてはWeb上にノウハウが沢山あるため、それほど苦労はしませんでした。
GASはスプレッドシートとのやり取りや定時バッチ処理が思いの外簡単に作れるので便利ですね。
GoogleアカウントとChromeブラウザ(とネット接続)があれば環境構築の手間がかからないのも大変便利です。
エントリ中ではトリガーを1時間ごとにしていましたが、個人的には1日1回取れれば良いので「毎日0時～1時」で設定しています。
株価の取得とリストの追加は連結してひとつのトリガーで動かすようにしたほうが良いかもしれませんね。
GASによる株価の取得には即時性はない上、スクレイピングの回数などにも制限があるため、1分1秒を争うような状況のためには使えないものと思われます。
以上、よろしくお願いいたします。
参考
作成にあたり、以下のサイト・エントリを参考にさせていただきました。