googleスプレッドシートでCSV(価格データ)を自動取得する(ちょっとした応用まで)

googleスプレッドシートでCSV(価格データ)を自動取得する(ちょっとした応用まで)
googleスプレッドシートを日頃から活用している方はかなり多いと思います。今回は、ググッともう一歩すすんだ使い方として、CSVファイルを自動で取得する方法を解説しようと思います。

googleスプレッドシートでCSV(価格データ)を自動取得する(ちょっとした応用まで)

  1. googleスプレッドシートとは
  2. CSVファイルを自動で取得する方法はいくつかある
    1. googleスプレッドシートの関数をつかう
    2. google apps scriptをつかう
  3. 東京金融取引所(TOCOM)のCSVから取得
  4. 東京商品取引所(TFX)のCSVから取得
  5. 海外サイトのCSVから取得
  6. まとめ

1.googleスプレッドシートとは

まずは「googleスプレッドシートってなんぞや」って方向けに――。
簡単に特徴を列挙すると次の用な感じです。

* はじめての方向けで、以前の記事をコピペしています。知ってる方や、読んだことがある方はスルーしてください。

  • googleが提供
  • いつでもどこからでもアクセス
  • 共同作業ができる(投資には不要かな)
  • 自動保存
  • EXCELに対応
  • googleのサービスとの相性が良い
  • マクロもあるよ(似たことができる)
  • 詳しくはコチラ

こんな感じでしょうか。

僕は大好きで、いつも便利に使わせてもらっています。作成はgoogleドライブにアクセスして下図を参考に、「新規」▷「Google スプレッドシート」の順でクリックしてください(googleのアカウントが必要)。

161018-google-drive-add

目次へ

2.CSVファイルを自動で取得する方法はいくつかある

CSVファイルを取得する方法はいくつかあります。
基本的なものからちょっとした応用まであるので、自分にあったものを使ってみてください。

2-1.googleスプレッドシートの関数をつかう

* 月末になるとCSVのデータが大きくなるので環境によってはパソコンが非常に重たくなります。

* googleドライブにコピーしてそのまま使用することもできます。

上図のように、googleスプレッドシートの関数でCSVのデータを取得することができます。CSVのURLが変わる場合はサンプルのように、関数「today()」などを使用して対応可能です。

2-2.google apps scriptをつかう

さて、この記事のメインに入ります。

google apps scriptはEXCELのマクロのようなもので、さまざまな自動処理をしたり、googleの他のサービスと連携することができます。

以下で詳細な使用方法について解説しますが、このページにあるコードはすべて次の手順で使用することができるようになります。

  • スプレッドシートを作成
  • 「ツール ▷ スクリプトエディタ」でスクリプトエディタを開く
  • 下記コードをコピペ
  • コードを保存(プロジェクトの名前の指定と、権限の承認が必要)
  • 実行(定期的に実行する場合は「リソース ▷ 現在のスクリプトのトリガー」から指定できる)

以下で実際に自動取得する例をいくつか解説してみます。

* google apps script自体の解説は他に良いサイトがたくさんありますので、色々とさがしてみてください。

目次へ

3.東京商品取引所(TOCOM)のCSVから取得

では、さっそくコードから――。

今回はひと通り動作確認をしましたので、動作確認を行ったスプレッドシートも公開します。コピペ等がめんどうな方はgoogleドライブにコピーしてご利用ください。

get csv from tocom

function NEWPRICE_TOCOM(){
  
  /**
   * 設定
   */
  var GV = {
    // 書き込み先 
    SS   : SpreadsheetApp.getActiveSpreadsheet()
    ,ROW : 5 //行数
    ,COL : 1 //列数
    // 銘柄 // NUMは取引所が指定する銘柄コード
    ,SYM_TOCOM     : ["GoldTokyo","PlatinumTokyo","PalladiumTokyo","GasolineTokyo","HeatingOilTokyo","RubberTokyo","CornTokyo","SoybeanTokyo","AzukiRedBeanTokyo"]
    ,SYM_TOCOM_NUM : [11         ,13             ,14              ,31             ,32               ,81           ,201        ,202           ,204                ]
    // 項目 // NUMは取得するCSVの項目に対応する列数
    ,ITEM_TOCOM     : ["Date","Source","Category","SymbolNumber","Symbol","Maturity","StrikePrice","Open","High","Low","Close","SettlementPrice","TradingVolume","OpenInterest"]
    ,ITEM_TOCOM_NUM : [0     ,""      ,1         ,2             ,""      ,3         ,4            ,5     ,6     ,7    ,8      ,9                ,10             ,11            ]
  };
  GV["THIS_MONTH"] = _Now("yyyy-MM");
  GV["TODAY"]      = _Now("yyyy/MM/dd");
  GV["URL"]        = "http://www.tocom.or.jp/data/yakujou/"+GV.THIS_MONTH+".csv";
  GV["SHEET"]      = GV.SS.getSheetByName("シート1");
  
  var
   arraySS = new Array()
  ,filteredCsv1
  ,filteredCsv2
  ,getCsv  = _getCsv(GV.URL)                       // ここでCSVを取得
  ,csvData = _CSVToArray(getCsv.contentString,",") // ここでCSVを配列に変換
  ;
  for(var i=0 ,symLen=GV.SYM_TOCOM.length; i<symLen; i++){
    // 配列の作成
    filteredCsv1 = csvData.filter(function(filt){return(           // 日付と銘柄でフィルター チルダ「~」は「-1」だけを「0」とする(「0」=false)
      ~filt.toString().indexOf(",11,"+GV.SYM_TOCOM_NUM[i]+",")     // カテゴリーコードと銘柄コードを確認
      && !~filt.toString().indexOf(",,,")                          // データがないものは除く
    );});
    filteredCsv2 = (filteredCsv1[filteredCsv1.length-1] || "");    // 最新のデータの期先を抽出
    arraySS[i]   = new Array();
    
    for(var j=0 ,itmLen=GV.ITEM_TOCOM.length; j<itmLen; j++){
      if(j==1){
        arraySS[i][1] = "TOCOM";
      }else if(j==4){
        arraySS[i][4] = GV.SYM_TOCOM[i];
      }else{
        arraySS[i][j] = (filteredCsv2[GV.ITEM_TOCOM_NUM[j]] || "\'\'");
      }
    }
  }//for
  
  GV.SHEET.getRange(GV.ROW ,GV.COL ,symLen ,itmLen).setValues(arraySS);
}



/**
 * --- _Now(format)
 * [機能]現在の日時を文字列で返す
 * [引数]formatフォーマット
 * var format   = 'yyyy/MM/dd HH:mm:ss.fff';  
 * var today_str = _Now(format); // 日付から文字列にする  
 */
function _Now(format){
  var f, rep
     ,result_str = format //yyyy/MM/dd
     ,today = new Date()   
     ,w = ["sun","mon","tue","wed","thu","fri","sat"];
  f = 'yyyy';
  if (~result_str.indexOf(f)){
    rep = today.getFullYear();
    result_str = result_str.replace(/yyyy/, rep); 
  }
  f = 'MM';
  if (~result_str.indexOf(f)){
    rep =_comPadZero(today.getMonth() + 1, 2);
    result_str = result_str.replace(/MM/, rep); 
  }
  f = 'ddd';
  if (~result_str.indexOf(f)){
    rep = w[today.getDay()];
    result_str = result_str.replace(/ddd/, rep); 
  }
  f = 'dd';
  if (~result_str.indexOf(f)){
    rep =_comPadZero(today.getDate(), 2);
    result_str = result_str.replace(/dd/, rep); 
  }
  f = 'HH';
  if (~result_str.indexOf(f)){
    rep =_comPadZero(today.getHours(), 2);
    result_str = result_str.replace(/HH/, rep); 
  }
  f = 'mm';
  if (~result_str.indexOf(f)){
    rep =_comPadZero(today.getMinutes(), 2);
    result_str = result_str.replace(/mm/, rep); 
  }
  f = 'ss';
  if (~result_str.indexOf(f)){
    rep =_comPadZero(today.getSeconds(), 2);
    result_str = result_str.replace(/ss/, rep);
  }
  f = 'fff';
  if (~result_str.indexOf(f)){
    rep =_comPadZero(today.getMilliseconds(), 3);
    result_str = result_str.replace(/fff/, rep); 
  }
  return result_str;
}



/**
 * --- comPadZero_(value, length)
 * [機能]ゼロパディングを行います
 * [引数]value  対象の文字列
 *       length 長さ
 * [戻値]結果文字列
 */
function _comPadZero(value, length){
  return new Array(length - ('' + value).length + 1).join('0') + value;
}



/**
 * get csv files
 * csvを取得してテキストで返す
 */
function _getCsv(URL) {
  var response = UrlFetchApp.fetch(URL)
     ,data = {
       contentString : response.getContentText().toString()   
      ,responseCode : response.getResponseCode() 
     };
  return data;
}



/**
 * Csv to array
 */
function _CSVToArray( strData, strDelimiter ){
  strDelimiter = (strDelimiter || ",");
  var objPattern = new RegExp(
    (
      "(\\" + strDelimiter + "|\\r?\\n|\\r|^)" +
      "(?:\"([^\"]*(?:\"\"[^\"]*)*)\"|" +
      "([^\"\\" + strDelimiter + "\\r\\n]*))"
    ),
    "gi"
  );
  var arrData = [[]];
  var arrMatches = null;
  while (arrMatches = objPattern.exec( strData )){
    var strMatchedDelimiter = arrMatches[ 1 ];
    if (
      strMatchedDelimiter.length &&
      (strMatchedDelimiter != strDelimiter)
      ){
        arrData.push( [] );
      }
    if (arrMatches[ 2 ]){
      var strMatchedValue = arrMatches[ 2 ].replace(
        new RegExp( "\"\"", "g" ),
        "\""
      );
      
    } else {
      var strMatchedValue = arrMatches[ 3 ];
    }
    arrData[ arrData.length - 1 ].push( strMatchedValue );
  }
  return( arrData );
}

* googleドライブにコピーしてそのまま使用することもできます。

さて、なかなか長いコードですね。「NEWPRICE_TOCOM()」についてはコメントアウトで解説しています。それ以外のお役立ちコードについてご質問があればコメント欄で、どしどし、お寄せください!

目次へ

4.東京金融取引所(TFX)のCSVから取得

次は東京金融取引所です。取引所が変われば当然CSVの構成も変わりますのでコードも変わります。最新の価格を取得するのはこっちの方が楽ですね。

get csv from tfx

// ※ 取引所のCSVは日毎のファイルです。日曜、月曜、祝日はこのコードは使えません。
function NEWPRICE_TFX(){
  
  /**
   * 設定
   */
  var GV = {
    // 書き込み先 
    SS   : SpreadsheetApp.getActiveSpreadsheet()
    ,ROW : 5 //行数
    ,COL : 1 //列数
    // 銘柄 // NUMは取引所が指定する銘柄コード
    ,SYM_TFX     : ["USD/JPY","EUR/JPY","GBP/JPY","AUD/JPY","CHF/JPY","EUR/USD","GBP/USD","AUD/USD"]
    ,SYM_TFX_NUM : [1        ,2        ,3        ,4        ,5        ,18       ,19       ,23       ] //TFXのCSVの並び順
    // 項目 // NUMは取得するCSVの項目に対応する列数
    ,ITEM_TFX     : ["Date","Source","Symbol","Open","High","Low","Close","SettlementPrice","SwapPoint","TradingVolume","OpenInterest"]
    ,ITEM_TFX_NUM : [1     ,""      ,3       ,6     ,8     ,10   ,12     ,14               ,16         ,17             ,18            ]
  };
  GV["YEST"]  = _N(-1 ,"yyyyMMdd");
  GV["URL"]   = "http://www.tfx.co.jp/kawase/document/PRT-010-CSV-003-"+GV.YEST+".CSV";
  GV["SHEET"] = GV.SS.getSheetByName("シート1");
  
  var
   arraySS = new Array()
  ,preDate
  ,makeDate
  ,getCsv  = _getCsv(GV.URL)
  ,csvData = _CSVToArray(getCsv.contentString,",")
  ; 
  // 配列の作成
  for(var i=0 ,symLen=GV.SYM_TFX.length; i<symLen; i++){
    arraySS[i]   = new Array();
    
    if(csvData[i].indexOf("�|�|�|")==-1){ // 休場の判別
      for(var j=0 ,itmLen=GV.ITEM_TFX.length; j<itmLen; j++){
        if(j==0){ 
          preDate       = csvData[GV.SYM_TFX_NUM[i]][GV.ITEM_TFX_NUM[j]]
          makeDate      = preDate.slice(0,4)+"/"+preDate.slice(4,6)+"/"+preDate.slice(6);
          arraySS[i][0] = makeDate;
        }else if(j==1){
          arraySS[i][1] = "TFX";
        }else if(j==2){
          arraySS[i][2] = GV.SYM_TFX[i];
        }else{
          arraySS[i][j] = (csvData[GV.SYM_TFX_NUM[i]][GV.ITEM_TFX_NUM[j]] || "\'\'");
        }
      }
    }else{
      arraySS[i][0] = "休場"
    }
  }
  
  GV.SHEET.getRange(GV.ROW ,GV.COL ,symLen ,itmLen).setValues(arraySS);
}



/**
 * --- _N(N,format)
 * [機能]前後N日の日時を文字列で返す
 * [引数]formatフォーマット
 * [戻値]フォーマット後の文字列
 * var format   = 'yyyy/MM/dd ddd';  
 * var Yesterday_str = _N(-1,format); // 日付から文字列にする  
 */
function _N(N,format){
  var f 
     ,rep
     ,result_str = format //yyyy/MM/dd
     ,today = new Date()
     ,yesterday = new Date(today.getFullYear(),today.getMonth(),today.getDate()+N)
     ,w = ["sun","mon","tue","wed","thu","fri","sat"];
  N = N-0;
  f = 'yyyy';
  if (~result_str.indexOf(f)){
    rep = yesterday.getFullYear();
    result_str = result_str.replace(/yyyy/, rep); 
  }
  f = 'MM';
  if (~result_str.indexOf(f)){
    rep =_comPadZero(yesterday.getMonth() + 1, 2);
    result_str = result_str.replace(/MM/, rep); 
  }
  f = 'ddd';
  if (~result_str.indexOf(f)){
    rep = w[yesterday.getDay()];
    result_str = result_str.replace(/ddd/, rep); 
  }
  f = 'dd';
  if (~result_str.indexOf(f)){
    rep =_comPadZero(yesterday.getDate(), 2);
    result_str = result_str.replace(/dd/, rep); 
  }
  return result_str;
}



/**
 * --- comPadZero_(value, length)
 * [機能]ゼロパディングを行います
 * [引数]value  対象の文字列
 *       length 長さ
 * [戻値]結果文字列
 */
function _comPadZero(value, length){
  return new Array(length - ('' + value).length + 1).join('0') + value;
}



/**
 * get csv files
 * csvを取得してテキストで返す
 */
function _getCsv(URL) {
  var response = UrlFetchApp.fetch(URL)
     ,data = {
       contentString : response.getContentText().toString()   
      ,responseCode : response.getResponseCode() 
     };
  return data;
}



/**
 * Csv to array
 */
function _CSVToArray( strData, strDelimiter ){
  strDelimiter = (strDelimiter || ",");
  var objPattern = new RegExp(
    (
      "(\\" + strDelimiter + "|\\r?\\n|\\r|^)" +
      "(?:\"([^\"]*(?:\"\"[^\"]*)*)\"|" +
      "([^\"\\" + strDelimiter + "\\r\\n]*))"
    ),
    "gi"
  );
  var arrData = [[]];
  var arrMatches = null;
  while (arrMatches = objPattern.exec( strData )){
    var strMatchedDelimiter = arrMatches[ 1 ];
    if (
      strMatchedDelimiter.length &&
      (strMatchedDelimiter != strDelimiter)
      ){
        arrData.push( [] );
      }
    if (arrMatches[ 2 ]){
      var strMatchedValue = arrMatches[ 2 ].replace(
        new RegExp( "\"\"", "g" ),
        "\""
      );
      
    } else {
      var strMatchedValue = arrMatches[ 3 ];
    }
    arrData[ arrData.length - 1 ].push( strMatchedValue );
  }
  return( arrData );
}

* googleドライブにコピーしてそのまま使用することもできます。

もとから日毎のCSVファイルになっている分、取得したデータにフィルタリングをかける必要がないので、いくらか楽ですね。

目次へ

5.海外サイトのCSVから取得

ラストはStooqという海外のサイトです。データが豊富で使い勝手も大変よいサイトです。(日本語はないですが)

get csv from stooq

// ※ 前日の価格を取得するコードなので、日曜と月曜は「休場」となります。
function NEWPRICE_Stooq(){
  
  /**
   * 設定
   */
  var GV = {
    // 書き込み先 
    SS   : SpreadsheetApp.getActiveSpreadsheet()
    ,ROW : 5 //行数
    ,COL : 1 //列数
    // 銘柄
    ,SYM_Stooq : ["Nikkei225","NY Dow","Gold"]
    // 項目
    ,ITEM_Stooq : ["Date","Source","Symbol","Open","High","Low","Close","TradingVolume","OpenInterest"]
  };
  
  GV["YEST"]  = _N(-1 ,"yyyyMMdd");
  GV["URL"] = [
    "http://stooq.com/q/d/l/?s=%5Enkx&d1="+GV.YEST+"&d2="+GV.YEST+"&i=d"    //日経225
    ,"http://stooq.com/q/d/l/?s=%5Edji&d1="+GV.YEST+"&d2="+GV.YEST+"&i=d"   //NYダウ
    ,"http://stooq.com/q/d/l/?s=gc.f&d1="+GV.YEST+"&d2="+GV.YEST+"&i=d"     //Gold
  ];
  GV["SHEET"] = GV.SS.getSheetByName("シート1");
  
  var
   arraySS = new Array()
  ,preDate
  ,makeDate
  ,getCsv
  ,csvData
  ; 
  // 配列の作成
  for(var i=0 ,symLen=GV.SYM_Stooq.length; i<symLen; i++){
    getCsv     = _getCsv(GV.URL[i])
    csvData    = _CSVToArray(getCsv.contentString,",")
    arraySS[i] = new Array();
    
    for(var j=0 ,itmLen=GV.ITEM_Stooq.length; j<itmLen; j++){
      if(csvData.toString() != "No data"){
        if (j==0){ 
          arraySS[i][0] = csvData[1][0].replace(/\-/g,"/");
        }else if(j==1){
          arraySS[i][1] = "Stooq";
        }else if(j==2){
          arraySS[i][2] = GV.SYM_Stooq[i];
        }else{
          arraySS[i][j] = (csvData[1][j-2] || "");
        }
      }else{
        if(j==0){
          arraySS[i][0] = "休場";
        }else{
          arraySS[i][j] = "";
        }
      }
    }
  }
  
  GV.SHEET.getRange(GV.ROW ,GV.COL ,symLen ,GV.ITEM_Stooq.length).setValues(arraySS);
}



/**
 * --- _N(N,format)
 * [機能]前後N日の日時を文字列で返す
 * [引数]formatフォーマット
 * [戻値]フォーマット後の文字列
 * var format   = 'yyyy/MM/dd ddd';  
 * var Yesterday_str = _N(-1,format); // 日付から文字列にする  
 */
function _N(N,format){
  var f 
     ,rep
     ,result_str = format //yyyy/MM/dd
     ,today = new Date()
     ,yesterday = new Date(today.getFullYear(),today.getMonth(),today.getDate()+N)
     ,w = ["sun","mon","tue","wed","thu","fri","sat"];
  N = N-0;
  f = 'yyyy';
  if (~result_str.indexOf(f)){
    rep = yesterday.getFullYear();
    result_str = result_str.replace(/yyyy/, rep); 
  }
  f = 'MM';
  if (~result_str.indexOf(f)){
    rep =_comPadZero(yesterday.getMonth() + 1, 2);
    result_str = result_str.replace(/MM/, rep); 
  }
  f = 'ddd';
  if (~result_str.indexOf(f)){
    rep = w[yesterday.getDay()];
    result_str = result_str.replace(/ddd/, rep); 
  }
  f = 'dd';
  if (~result_str.indexOf(f)){
    rep =_comPadZero(yesterday.getDate(), 2);
    result_str = result_str.replace(/dd/, rep); 
  }
  return result_str;
}



/**
 * --- comPadZero_(value, length)
 * [機能]ゼロパディングを行います
 * [引数]value  対象の文字列
 *       length 長さ
 * [戻値]結果文字列
 */
function _comPadZero(value, length){
  return new Array(length - ('' + value).length + 1).join('0') + value;
}



/**
 * get csv files
 * csvを取得してテキストで返す
 */
function _getCsv(URL) {
  var response = UrlFetchApp.fetch(URL)
     ,data = {
       contentString : response.getContentText().toString()   
      ,responseCode : response.getResponseCode() 
     };
  return data;
}



/**
 * Csv to array
 */
function _CSVToArray( strData, strDelimiter ){
  strDelimiter = (strDelimiter || ",");
  var objPattern = new RegExp(
    (
      "(\\" + strDelimiter + "|\\r?\\n|\\r|^)" +
      "(?:\"([^\"]*(?:\"\"[^\"]*)*)\"|" +
      "([^\"\\" + strDelimiter + "\\r\\n]*))"
    ),
    "gi"
  );
  var arrData = [[]];
  var arrMatches = null;
  while (arrMatches = objPattern.exec( strData )){
    var strMatchedDelimiter = arrMatches[ 1 ];
    if (
      strMatchedDelimiter.length &&
      (strMatchedDelimiter != strDelimiter)
      ){
        arrData.push( [] );
      }
    if (arrMatches[ 2 ]){
      var strMatchedValue = arrMatches[ 2 ].replace(
        new RegExp( "\"\"", "g" ),
        "\""
      );
      
    } else {
      var strMatchedValue = arrMatches[ 3 ];
    }
    arrData[ arrData.length - 1 ].push( strMatchedValue );
  }
  return( arrData );
}

* googleドライブにコピーしてそのまま使用することもできます。

銘柄毎のCSVで、日付も自由に指定できるため使い勝手がいいですね。

目次へ

6.まとめ

さて、かなり長い記事になってしまいましたが、ここにある事例を使用または組み合わせれば大体のCSVファイルの取得は問題なくできると思います。

一気に書き上げた記事なので(もちろん確認はしていますが)、足りない部分や間違いがあるかもしれません。もし、わからないことや変な点があればどんどんご質問ください^^!

次回はさらに応用して、毎日自動で価格データを取得してATRを計算するスキームとコードを解説してみようと思います!

目次へ

Back to Top

Investment Tech Hack

Sorry... doesn't support your browser

To get the best possible experience using our site we recommend that you upgrade to a modern web browser. Investment Tech Hackではご利用中のブラウザサポートはしていません。
Internet Explorerのアップグレード行う、もしくはその他のブラウザを使用しての閲覧をお願いします。