raymelon · November 10, 2024 11:45 · May 10, 2016
diff --git a/YahooPrices.gs b/YahooPrices.gs
@@ -0,0 +1,176 @@
+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;
+}