保有銘柄の情報管理 スプレッドシート&GAS

1. はじめに

皆さまは保有銘柄管理はどのようにされてますでしょうか？
わたしはその昔、保有銘柄の管理を怠った結果、気づいたら某航空銘柄が紙くずになっていた苦い経験があります。

そこで、保有銘柄の情報をカスタマイズして管理できるツール作成を試みました。
はじめの一歩の段階ですので、完成度はご容赦いただければ幸いです。

具体的にはGoogleスプレッドシートを使用して、下記情報を自動取得できるようにします。
今回は、認証不要で取得できるデータで構成しました。

【入力】
・銘柄コード
【出力】
・銘柄名、33業種区分、市場区分名
・株価
・銘柄名をキーワードとしたGoogle News RSSフィード

生成したコードはChatGPTの仰せのままに。すべてはChatGPTのおかげです。

2. GASコード

2.1. 銘柄名、33業種区分、市場区分名

J-QuantsのAPIを使用すれば取得可能ですが、登録認証の手順が必要になるので今回はローカルな方法で代用します。

まずは日本証券取引所から、東証上場銘柄一覧のエクセルファイルをダウンロードします。
https://www.jpx.co.jp/markets/statistics-equities/misc/01.html
内容を全コピーして、スプレッドシートのシート2に貼り付けます。

下記コードでは、シート1に入力した銘柄コードをシート2で照合して、合致するものをシート1に返します。
ついでに、株探へのリンクも生成します。

data

function updateSheetWithErrorHandling() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet1 = ss.getSheetByName('シート1');
  var sheet2 = ss.getSheetByName('シート2');

  // シート2からデータを取得
  var dataSheet2 = sheet2.getDataRange().getValues();
  var lookup = new Map();
  for (var i = 0; i < dataSheet2.length; i++) {
    var stockCode = dataSheet2[i][1];
    if (stockCode && !lookup.has(stockCode)) {
      lookup.set(stockCode, {
        name: dataSheet2[i][2],
        market: dataSheet2[i][3],
        industry: dataSheet2[i][5]
      });
    } else if (stockCode) {
      Logger.log('Duplicate stock code found: ' + stockCode);
    }
  }

  // A列のデータがある最終行を正確に取得
  var columnAValues = sheet1.getRange('A6:A' + sheet1.getLastRow()).getValues();
  var lastRow = 5 + columnAValues.filter(function(row) { return row[0] !== ""; }).length;

  // シート1の銘柄コードを更新
  for (var j = 6; j <= lastRow; j++) {
    var stockCodeSheet1 = sheet1.getRange(j, 1).getValue();
    if (lookup.has(stockCodeSheet1)) {
      var data = lookup.get(stockCodeSheet1);
      sheet1.getRange(j, 2).setValue(data.name || 'データなし');
      sheet1.getRange(j, 3).setValue(data.industry || 'データなし');
      sheet1.getRange(j, 4).setValue(data.market || 'データなし');
      sheet1.getRange(j, 6).setFormula('=HYPERLINK("https://kabutan.jp/stock/?code=' + stockCodeSheet1 + '", "株探")');
    } else {
      sheet1.getRange(j, 2).setValue('データなし');
      sheet1.getRange(j, 3).setValue('データなし');
      sheet1.getRange(j, 4).setValue('データなし');
      sheet1.getRange(j, 6).setValue('リンクなし');
    }
  }
}

2.2. 株価

Google financeのサイトより取得します。
事前にParserライブラリの取得が必要です。

なお、同時取得の制限、およびGoogleサービスの上限確認の処理を入れています。
https://developers.google.com/apps-script/guides/services/quotas?hl=ja

Google finance

// 日次URLフェッチ制限
var DAILY_URL_FETCH_LIMIT = 20000;

function getStockPrices() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var codes = sheet.getRange("A6:A" + sheet.getLastRow()).getValues().flat();
  var batchSize = 100; // 同時に処理するリクエストの最大数
  var properties = PropertiesService.getScriptProperties();
  var today = new Date().toDateString();
  var fetchCount = Number(properties.getProperty(today)) || 0;

  // 日次制限に達しているかチェック
  if (fetchCount >= DAILY_URL_FETCH_LIMIT) {
    Logger.log('URLフェッチの日次制限に達しました。');
    return; // 制限に達していれば処理を終了
  }

  // コードのリストをバッチ処理で取り扱う
  for (var i = 0; i < codes.length; i += batchSize) {
    var codeSlice = codes.slice(i, i + batchSize);
    var urls = codeSlice.map(function(code) {
      return code ? "https://www.google.com/finance/quote/" + code + ":TYO" : null;
    }).filter(Boolean); // nullまたは空の項目を除外

    // このバッチを処理すると日次制限を超えるかどうかをチェック
    if (fetchCount + urls.length > DAILY_URL_FETCH_LIMIT) {
      Logger.log('このバッチを処理すると日次制限を超えます。');
      break; // ループを抜けて処理を終了
    }

    var requests = urls.map(function(url) {
      return { "url": url, "muteHttpExceptions": true };
    });

    try {
      var responses = UrlFetchApp.fetchAll(requests);
      fetchCount += responses.length; // フェッチの回数を加算
      properties.setProperty(today, fetchCount.toString()); // 更新されたカウントを保存

      // 各レスポンスから株価を解析し、それをスプレッドシートの適切な場所に出力
      var stockPrices = [];
      responses.forEach(function(response, index) {
        var stockPrice = parseStockPriceFromResponse(response); // 株価を解析する関数を使用
        stockPrices.push([stockPrice]); // 2次元配列として追加
      });
      sheet.getRange(i + 6, 5, stockPrices.length, 1).setValues(stockPrices);
    } catch (e) {
      Logger.log('フェッチ中にエラーが発生しました: ' + e.toString());
    }

    // APIの制限を尊重するために待機時間を設ける
    Utilities.sleep(1000);
  }
}

function parseStockPriceFromResponse(response) {
  var stockPrice = "Error getting the stock price."; // 初期エラーメッセージ
  if (response.getResponseCode() == 200) {
    var html = response.getContentText();
    try {
      var priceString = html.match(/<div class="YMlKec fxKbKc">([^<]+)<\/div>/);
      if (priceString && priceString[1]) {
        stockPrice = parseFloat(priceString[1].replace('¥', '').replace(',', ''));
      }
    } catch (e) {
      Logger.log('Error parsing stock price: ' + e.toString());
    }
  }
  return stockPrice;
}

2.3. 銘柄名をキーワードとしたGoogle News RSSフィード

Google NewsのRSSフィードに2.1.で取得した銘柄名をキーワードとして入力します。
帰ってきたRSSフィードより、一番上の記事のタイトルとリンクを出力します。
取得制限の考え方は2.2.と同様です。
なお、連続で取得しようとするとすぐにレスポンスエラー（Response Code 503）が返ってきます。1日1回程度の取得頻度が望ましいです。

Google news RSS

// 日次URLフェッチ制限
var DAILY_URL_FETCH_LIMIT = 20000;

function fetchAndWriteNewsTitles() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var data = sheet.getRange("B6:B" + sheet.getLastRow()).getValues();
  var batchSize = 100; // 同時に処理するリクエストの最大数
  var fetchRequests = [];
  var properties = PropertiesService.getScriptProperties();
  var today = new Date().toDateString();
  var fetchCount = Number(properties.getProperty(today)) || 0;

  // クエリごとにリクエスト情報を構築
  for (var i = 0; i < data.length; i++) {
    if (data[i][0]) {
      var query = data[i][0];
      var url = "https://news.google.com/rss/search?q=" + encodeURIComponent(query) + "&hl=ja&gl=JP&ceid=JP:ja";
      fetchRequests.push({
        url: url, 
        muteHttpExceptions: true, 
        headers: {"User-Agent": "Mozilla/5.0"}, 
        rowIndex: i + 6, // 行番号は6行目から始まる
        query: query
      });
    }
  }

  // バッチ処理
  for (var j = 0; j < fetchRequests.length; j += batchSize) {
    // 日次制限に達しているかチェック
    if (fetchCount >= DAILY_URL_FETCH_LIMIT) {
      Logger.log('URLフェッチの日次制限に達しました。');
      break; // 制限に達していれば処理を終了
    }

    var remainingLimit = DAILY_URL_FETCH_LIMIT - fetchCount;
    var currentBatchSize = Math.min(batchSize, remainingLimit, fetchRequests.length - j);
    var batchRequests = fetchRequests.slice(j, j + currentBatchSize);
    
    // URLフェッチとレスポンス処理
    var responses = UrlFetchApp.fetchAll(batchRequests.map(function(request) {
      return {url: request.url, muteHttpExceptions: request.muteHttpExceptions, headers: request.headers};
    }));

    // レスポンスを処理し、結果をシートに出力
    responses.forEach(function(response, index) {
      var request = batchRequests[index];
      if (response.getResponseCode() == 200) {
        var xml = response.getContentText();
        var document = XmlService.parse(xml);
        var root = document.getRootElement();
        var entries = root.getChild("channel").getChildren("item");
        if (entries.length > 0) {
          var title = entries[0].getChild("title").getText();
          var link = entries[0].getChild("link").getText();
          sheet.getRange(request.rowIndex, 7).setValue(title); // G列への出力
          sheet.getRange(request.rowIndex, 8).setValue(link);  // H列への出力
        }
      } else {
        Logger.log('Error fetching RSS feed for query "' + request.query + '": Response Code ' + response.getResponseCode());
      }
    });

    // 実行されたフェッチの回数をカウント
    fetchCount += currentBatchSize;
    properties.setProperty(today, fetchCount.toString());

    // 必要に応じて待機
    if (fetchCount < DAILY_URL_FETCH_LIMIT && j + batchSize < fetchRequests.length) {
      Utilities.sleep(1000); // 1秒待機
    }
  }
}

3. 結果

上記のGASスクリプトを、シート1のオブジェクトにそれぞれ割り当てます。

A列に銘柄コードを入力してスクリプトを実行すると、それぞれ情報が取得できます。

実際には、銘柄情報はコード入力した初回だけ取得します。
また、株価とニュースはトリガー機能を利用して1日1回実行する程度の運用が良いと思います。

スプレッドシートを開くのも億劫なら、情報を厳選してメルマガするという手段もありますね。
このようなカスタマイズが簡単にできるのが、Googleサービスの良いところと感じています。

また、J-QuantsのAPIから取得できる財務情報を組み合わせれば、様々な指標も表示できますね。
https://qiita.com/hoppeta/items/c4a41803ce0be6257bf9

以上、少しでも参考になれば幸いです。