Google App Scripts for querying Yahoo! finance API ticker prices

YahooPrices.gs

var _ = Underscore.load();


/* 
 * Queries Yahoo finance API for historical prices for a given list of tickers
 *
 * @param {Array} tickers A list of tickers
 * @param {Date} date Date to query for. Must be a date the markets were open
 * @return A map of ticker to adjusted close prices
 */
function getYahooPrices(tickers, date) {
  if(tickers.length == 0) {
    return {};
  }
  
  Logger.log('Getting prices for %s for date %s', tickers, date)
  var yql_query = _createRangePriceQuery(tickers, getDaysAgo(date, 5), date);
  var url = 'http://query.yahooapis.com/v1/public/yql?q=' + encodeURI(yql_query) + 
    '&format=json' + '&diagnostics=false' + '&env=store://datatables.org/alltableswithkeys' + '&callback=';

  var data = _getUrlData(url)

  if(!data || !data.query.results) {
    Logger.log("Unable to find prices for " + tickers + " and date " + date + ' Got JSON data ' + data);
    return {};
  } 
  
  Utilities.sleep(1000); // avoid Yahoo errors
  
  var priceList = [];
  if(data.query.count == 1) {
    priceList = [data.query.results.quote];
  } else {
    priceList = data.query.results.quote;
  }
  
  Logger.log('Got quotes %s', priceList);

  var priceMap = {};
  
  for(var len = priceList.length, i = 0; i < len; i++) {
    var quote = priceList[i];
    var symbol = quote['Symbol'];
    var date = quote['Date'];
    var price = quote['Close'];
    if(symbol in priceMap) {
      if(date > priceMap[symbol]['date']) {
        priceMap[symbol]['price'] = price;
        priceMap[symbol]['date'] = date;
      }
    } else {
      priceMap[symbol] = {'price': price, 'date': date}; 
    }
  }
  
  // maintain old dict format of symbol -> price
  for(var k in priceMap) {
    priceMap[k] = priceMap[k]['price'];
  }
  
  return priceMap;
}


/**
 * http://stackoverflow.com/questions/25097779/getting-stocks-historical-data
 * @private
 */
function _createRangePriceQuery(tickers, startDate, endDate) {
  endDate = endDate || startDate;

  var tickerStrings = [];
  for(var len = tickers.length, i = 0; i < len; i++) {
    tickerStrings.push('"' + tickers[i] + '"');
  }
  
  // We want actual Close since we have actual historical positions and we're not
  // back calculating a current position historically.
  var yql_query = 'select Symbol, Close, Date from yahoo.finance.historicaldata where symbol in (' + 
    tickerStrings.join(',') + ') and startDate = "' + _dateToString(startDate) + '" and endDate = "' + _dateToString(endDate) + '"';
  Logger.log(yql_query);
  return yql_query;
}



/**
 * Returns the price of a ticker on a given date
 *
 * @param {String} ticker Stock ticker 
 * @return {Number} Price
 * @customfunction
 */
function YAHOO_PRICE(ticker) {
  return +_getCurrentYahooPrices([ticker])[ticker];
}

/**
 * @private
 */
function _getCurrentYahooPrices(tickers) {
  var yql_query = _createCurrentPriceQuery(tickers); 
  
  Utilities.sleep(1000); // avoid Yahoo errors  
  
  var data = _getUrlData(_getYahooQueryUrl(yql_query));
  
  if(!data) { return {}; }
  
  var priceList = [];
  if(data.query.count == 1) {
    priceList = [data.query.results.quote];
  } else {
    priceList = data.query.results.quote;
  }
  Logger.log('Got quotes %s', priceList);
  
  var priceMap = {};
  
  for(var len = priceList.length, i = 0; i < len; i++) {
    var symbol = priceList[i]['Symbol'];
    var ask = priceList[i]['Ask'];
    var previousClose = priceList[i]['PreviousClose'];
    Logger.log('ask %s, previousClose %s', ask, previousClose);
    priceMap[symbol] = ask == null ? previousClose : ask;
    priceMap[symbol] = previousClose;
  }
  
  Logger.log('price map %s',priceMap);
  
  return priceMap;
}


/**
 * @private
 */
function _createCurrentPriceQuery(tickers) { 
  var tickerStrings = [];
  for(var len = tickers.length, i = 0; i < len; i++) {
    tickerStrings.push('"' + tickers[i] + '"');
  }
  var yql_query = 'select Symbol, Ask, PreviousClose from yahoo.finance.quotes where symbol in (' + tickerStrings.join(',') + ')';
  Logger.log(yql_query);
  return yql_query;
}


/**
 * @private
 */
function _getYahooQueryUrl(yql_query) {
  var url = 'http://query.yahooapis.com/v1/public/yql?q=' + encodeURI(yql_query) + 
    '&format=json' + '&diagnostics=false' + '&env=store://datatables.org/alltableswithkeys' + '&callback=';
  return url;
}


/**
 * @private
 */
function _getUrlData(query_url) {
  var response = UrlFetchApp.fetch(query_url);
  var json = response.getContentText();
  return JSON.parse(json);
}


/**
 * @private
 */
function _dateToString(date) {
  var mon = padDate(date.getMonth()+1);
  var day = padDate(date.getDate());  
  return date.getYear() + '-' + mon + '-' + day;
}