【GAS】約定内容をスプレッドシートに追記(1)

はじめに

株取引を行っていると、約定内容のリストを確認したいことはよくあると思います。

証券会社のサイトにログインし、約定リストを抽出する期間を指定し、抽出させることでやっと必要とする約定リストを手に入れることができます。

確かに必要な情報は得られますが、解析の前段階の作業としては結構面倒ではありませんか？

常に最新の約定リストがすぐに見ることのできる環境が欲しいと思いませんか？

これを解決するために、約定メールをもとに約定リストを自動作成する仕組みを紹介します。

Google App Scriptを使って実現しますので、Google Apps Scriptの概要については以下の記事を参照ください。

関谷さんのブログ

2021.10.25

【GAS】Google App Scriptを使って自動化しよう

https://sekiya-san.com/automate-with-google-app-script

Google App ScriptとはGoogle App ScriptはGoogleがJavaScriptをベースとして開発したプログラミング言語です。といった説明よりも、何ができるのかを知りたいと思うので、どのように便利になるのかということにフォーカスして説明します。みなさんは、Gmail、Googleカレンダー、スプレッドシート、ドキュメントなどを連携させたいと思ったことはありませんか。例えば、注文メールを受信後、Googleカレンダーに記録し、スプレッドシートに内容追加。受注メール受信後、注文受領メールを返信。緊急のメールを受信したらLINEに通知。証券...

ここで紹介する内容は、以下の条件を満たす方が対象となります。

対象読者

楽天証券の口座を保有している
楽天証券の米国株式約定通知メール設定において「配信希望」としている
楽天証券からの配信メールにGmailを指定している
米国株式約定内容をスプレッドシートに自動登録させたい

今回は、まず約定内容をスプレッドシートに追記するためのライブラリを作成します。

スプレッドシートへの約定内容追加処理

約定内容を追加する関数の処理の流れは以下のようになります。

スプレッドシートファイルを作成する
作成したスプレッドシートを開く
初めて開く場合は項目ヘッダをつける
約定内容を追加する

これらをひとつづつ見ていきます。

また、全てのコードは最後に提示します。

スプレッドシート作成

ファイル名とフォルダ名を渡し、新規スプレッドシートを作成します。

/*
 * 新規スプレッドシート作成
 */
function createSpreadSheet_(fileName, folderName) {

  // 新規スプレッドシート作成
  var ss = SpreadsheetApp.create(fileName);
  var org = DriveApp.getFileById(ss.getId());

  return org;
}

フォルダ名を渡していますが使っていません。そのため、この関数を使うと常にルートにfilenameのスプレッドシートが作られます。

スプレッドシートを開く

作成したスプレッドシートを開き、最初のシートオブジェクトを返します。

/*
 * スプレッドシートファイルを開き、最初のシートを選択する
 */
function openSpreadSheet_(ss){

    var ssId = ss.getId();
    var ssFile = SpreadsheetApp.openById(ssId);
    var activeSpreadSheet = SpreadsheetApp.setActiveSpreadsheet(ssFile);
    var masterSheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];

    return masterSheet;
}

スプレッドシートの最初のシートに注文内容を追記することとします。

項目ヘッダをつける

初めてスプレッドシートを作成する場合は何も書かれていないので、項目ヘッダを出力します。

/*
 * スプレッドシートにヘッダをつける
 */
function initializeSpreadSheet_(masterSheet){
  masterSheet.appendRow([
      'No', '銘柄名（コード）', '口座・売買', '決済方法', '約定単価', '約定数量', '約定日時']);
}

値の取得と設定

値の取得と設定は以下のgetValue_()、setValue_()関数で行います。

sheetで指定されるシートの行(row)と列(col)を指定し、値の取得と設定が行えます。

/*
 * スプレッドシートsheetのrow, colから値取得
 */
function getValue_(sheet,row,col) {
  var value = sheet.getRange(row, col).getValue();
  return value;
}

/*
 * スプレッドシートsheetのrow, colに値設定
 */
function setValue_(sheet,row,col,value){
  var cell = sheet.getRange(row,col);
  cell.setValue(value);
}

項目追加

項目を追加する際には、以下のようにスプレッドシートを開いた後に、

  if(fileExists_(fileName, folderName)){
    var files = DriveApp.getRootFolder().getFilesByName(fileName); 
    ss = files.next();
    masterSheet = openSpreadSheet_(ss);
  }
  else{
    ss = createSpreadSheet_(fileName, folderName);
    masterSheet = openSpreadSheet_(ss);
    initializeSpreadSheet_(masterSheet);  
  }

以下のようにappendRow()に追加したいデータを渡すと、最終行の次の行にデータが書き込まれます。

  // スプレッドシートの最終行に追加
  masterSheet.appendRow([
        no-1, ticker_name, type, pay_type, unit_price, parseInt(amount), date_time]);

例として、スプレッドシートに注文内容が追加された表示内容を以下に示します。

全てのコード

以下のコードを「libAddStockOrderToList」という名前で保存しましょう。

/*
 * libAddStockOrderToList
 * 
 * 注文情報をスプレッドシートに追記するライブラリ
 * 
 * 2022/1/13 created by N.Sekiya
 */

var FOLDER_NAME = '40_仕事関係';
var FILE_NAME = 'test';

function test(){
  registToSpreadSheet(
    FILE_NAME, FOLDER_NAME,
    'プルデンシャル・ファイナンシャル（PRU）',
    '特定・買付',
    '外貨',
    '105.14',
    '2',
    '2021/12/14 23:30'
  );
}

function registToSpreadSheet(fileName, folderName,
  ticker_name, type, pay_type, unit_price, amount, date_time){

  // Logger.log('ticker_name:' + ticker_name);
  // Logger.log('type:' + type);
  // Logger.log('pay_type:' + pay_type);
  // Logger.log('unit_price:' + unit_price);
  // Logger.log('amount:' + amount);
  // Logger.log('date time:' + date_time);

  var ss = null;
  if(fileExists_(fileName, folderName)){
    var files = DriveApp.getRootFolder().getFilesByName(fileName); 
    ss = files.next();
    masterSheet = openSpreadSheet_(ss);
  }
  else{
    ss = createSpreadSheet_(fileName, folderName);
    masterSheet = openSpreadSheet_(ss);
    initializeSpreadSheet_(masterSheet);  
  }

  var no = 1;
  var val = getValue_(masterSheet, no, 1);
  while(val != ''){
    no++;
    val = getValue_(masterSheet, no, 1);
  }

  // スプレッドシートの最終行に追加
  masterSheet.appendRow([
        no-1, ticker_name, type, pay_type, unit_price, parseInt(amount), date_time]);
}

/*
 * 新規スプレッドシート作成
 */
function createSpreadSheet_(fileName, folderName) {

  // 新規スプレッドシート作成
  var ss = SpreadsheetApp.create(fileName);
  var org = DriveApp.getFileById(ss.getId());

  // 指定フォルダに移動
//  const id = getFolder(folderName).getId();
//  DriveApp.getFolderById(id).addFile(org);
//  DriveApp.getRootFolder().removeFile(org);

  return org;
}

/*
 * スプレッドシートファイルを開き、最初のシートを選択する
 */
function openSpreadSheet_(ss){

    var ssId = ss.getId();
    var ssFile = SpreadsheetApp.openById(ssId);
    var activeSpreadSheet = SpreadsheetApp.setActiveSpreadsheet(ssFile);
    var masterSheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];

    return masterSheet;
}

/*
 * スプレッドシートにヘッダをつける
 */
function initializeSpreadSheet_(masterSheet){
  masterSheet.appendRow([
      'No', '銘柄名（コード）', '口座・売買', '決済方法', '約定単価', '約定数量', '約定日時']);
}

/*
 * ファイル存在チェック
 */
function fileExists_(fileName, folderName) {
//  var folder = getFolder(folderName);
//  var files = folder.getFilesByName(fileName);
  var files = DriveApp.getRootFolder().getFilesByName(fileName);
  
  while (files.hasNext()) {
    var file = files.next();
    if(fileName == file.getName()) {
      return true;
    }
  }
  return false;
}

/*
 * フォルダ名からフォルダ取得
 */
function getFolder_(folderName) {
  var folders = DriveApp.getFoldersByName(folderName);
  while(folders.hasNext()) {
    var folder = folders.next();
    if(folder.getName() == folderName){
      break;
    }
  }
  return folder;
}

/*
 * スプレッドシートsheetのrow, colから値取得
 */
function getValue_(sheet,row,col) {
  var value = sheet.getRange(row, col).getValue();
  return value;
}

/*
 * スプレッドシートsheetのrow, colに値設定
 */
function setValue_(sheet,row,col,value){
  var cell = sheet.getRange(row,col);
  cell.setValue(value);
}

上記コードの「test()」を実行すると、「test」というスプレッドシートが作られて、1行約定内容のサンプルが記載されます。