2016年12月28日 星期三

Google Spreadsheet自動產生台指結算日

前一篇 Excel VBA自動產生台指結算日，現在筆者將同樣的觀念在 Google Spreadsheet上使用Google Apps Script、JavaScript寫一個同樣功能程式，以方便套用在 Google Spredsheet 抓 TXF 台指未平倉量與台指次月契約開倉成本 文章上。
台指結算日規則相較於摩台結算日規則簡單。
  1. 每月的第三週星期三。
  2. 年節封關提早、農曆年節提早、7月~8月颱風延後，需手動調整。
以上2個原則，第2種規則為不可抗拒因素，需人工調整。
function CreateTXFSettlementdate()
{
  var nCount = 0;
  var nGap = 15;
  var Datelist = new Array(2 * nGap +1);  
  var dBasedate, dCurrentdate, dPastdate;

  dPastdate = DateAdd("m", (-1) * nGap, new Date());
  for(var i = 0 ; i <= 2 * nGap + 1 ; i++)
  {
    dBasedate = DateAdd("m", i, new Date(dPastdate.getYear(), dPastdate.getMonth(), 1));    
    for(var j = 14 ; j <= 24 ; j++)
    {
      dCurrentdate = new Date(dBasedate.getYear(), dBasedate.getMonth(), j);
      if(checkTXFDayofWeek(dCurrentdate))
      {
        Datelist[nCount] = dCurrentdate.getFullYear() + "/" + padLeft(parseInt(dCurrentdate.getMonth()+1), 2) + "/" + padLeft(dCurrentdate.getDate(), 2);
        nCount++;
      }
    }    
  }
  
  /* 清除"結算日"工作表內容 */
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("台指結算日");  
  var Row = getLastRowOfColumn("台指結算日", "A:A");
  if (Row == 1) Row++;
  ss.getSheetByName("台指結算日").getRange("A2:A" + Row).clear();
  
  Row = getLastRowOfColumn("台指結算日", "B:B"); 
  for(var i = 0 ; i < nCount ; i++)
  {  
    for(var j = 2 ; j < Row ; j++)
    {        
      if(Datelist[i].substring(0, 8) == sheet.getRange(j, 2).getValue().substring(0, 8))
      {
        Datelist[i] = sheet.getRange(j, 2).getValue();
      }
    }
    sheet.getRange(i + 2, 1).setValue("'" + Datelist[i]);
  }
}

/* 確認日期為每月第三週星期三 */
function checkTXFDayofWeek(date)
{
  if(isNaN(date)) return 0;

  var weekofMonth = DatePart("wm", date);  
  if(DatePart("w", new Date(date.getYear(), date.getMonth(), 1)) > 3)
    weekofMonth = weekofMonth - 1;
  
  return ((DatePart("w", date) == 3) && (weekofMonth == 3)) ? true : false;
}

/* 取得資料欄的最後一個位置 */
function getLastRowOfColumn(sheetName, sheetRange)
{
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var Rowdata = ss.getSheetByName(sheetName).getRange(sheetRange).getValues();
  var row = 0;
  while ( Rowdata[row].join("") )  row++;  
  //while ( Rowdata[row][0] != "" )  row++;
  return row;
}

/* 補0 */
function padLeft(str, len) {
   str = '' + str;
   return (str.length >= len) ? str : padLeft("0" + str, len);
}

/* 取得日期資訊 */
function DatePart(interval, date)
{
  switch (interval.toLowerCase()) {
      case "q":
        dayIndex  = Math.floor((date.getMonth() + 1) / 3) + ((date.getMonth() + 1) % 3 ? 1 : 0); 
        break;
      case "wm":
        dayIndex  = date.getWeekOfMonth();
        break;      
      case "w":
        dayIndex  = date.getDay();
        break;
      case "h":
        dayIndex  = date.getHours();
        break;
      case "m":
        dayIndex  = date.getMinutes();
        break;
      case "s":
        dayIndex  = date.getSeconds();
        break;
    }
   return dayIndex 
}

/* 日期時間加減 */
function DateAdd(interval, number, date) {
  switch (interval.toLowerCase()) {
    case "y": return new Date(date.setFullYear(date.getFullYear() + number));  
    case "m": return new Date(date.setMonth(date.getMonth() + number));  
    case "d": return new Date(date.setDate(date.getDate() + number));  
    case "w": return new Date(date.setDate(date.getDate() + 7 * number));  
    case "h": return new Date(date.setHours(date.getHours() + number));  
    case "n": return new Date(date.setMinutes(date.getMinutes() + number));  
    case "s": return new Date(date.setSeconds(date.getSeconds() + number));  
    case "l": return new Date(date.setMilliseconds(date.getMilliseconds() + number)); 
  }
}

/* 計算當前日期在本月份的周數 */
Date.prototype.getWeekOfMonth = function(weekStart) {
 weekStart = (weekStart || 0) - 0;
 if(isNaN(weekStart) || weekStart > 6)
  weekStart = 0;

 var dayOfWeek = this.getDay();
 var day = this.getDate();
 return Math.ceil((day - dayOfWeek - 1) / 7) + ((dayOfWeek >= weekStart) ? 1 : 0);
}


於