使用Yahoo!将股票报价导入AdWords脚本财务API

最近有人在Twitter上问我是否见过 使用股市表现来调整出价的脚本。老实说我从来没有，但是我多次被问到这种能力。所以我认为我会为此做些事情。

为股票数据找到可靠且免费的API有点困难，但是每个人似乎都在指出某种程度上隐藏的Yahoo!。财务API。尽管事实是围绕它构建了多个库，但是除了 StackOverflow发布 谈论它。长话短说，此API可能会随时停止运行，因此，后果自负。

这是一些示例代码，可帮助您开始使用此代码。下面的代码仅查找了一些引号（一个来自比特币），并将它们加载到您选择的Google Spreadsheet中。非常简单。令人困惑的一件事是您需要传递给API的“ f =“参数。它记录在 此博客文章 但还是很混乱。它是一两个或两个字符代码的字符串，用于定义要返回的列。对于大多数人来说，符号，名称和当前价格应该足够了。随时根据需要自定义它。

谢谢，
拉斯

/******************************************
* Yahoo Finance API Class Example
* Version 1.0 
* Created By: 拉斯 Savage
* FreeAdWordsScripts.com
******************************************/
function main() {
  var sheetUrl = 'ENTER A GOOGLE SHEET URL HERE';
  
  var yfa = new YahooFinanceAPI({
    symbols: ['^GSPC','VTI','^IXIC','BTCUSD=X'],
    f: 'snl1' // or something longer like this 'sl1abb2b3d1t1c1ohgv'
  });
  for(var key in yfa.results) {
    Logger.log(Utilities.formatString('Name: "%s", Symbol: "%s", Last Trade Price: $%s', 
                                      yfa.results[key].name,
                                      key,
                                      yfa.results[key].last_trade_price_only));
  }
  
  var includeColumnHeaders = true;
  var sheetData = yfa.toGoogleSheet(includeColumnHeaders);
  var ss = SpreadsheetApp.openByUrl(sheetUrl).getActiveSheet();
  for(var i in sheetData) {
    ss.appendRow(sheetData[i]);
  }
}

只需将以下代码复制到AdWords脚本的底部即可，您应该一切顺利。

/******************************************
* Yahoo Finance API Class
* Use this to pull 股市 quotes from Yahoo Finance
* Version 1.0 
* Created By: 拉斯 Savage
* FreeAdWordsScripts.com
******************************************/
function YahooFinanceAPI(configVars) {
  var QUERY_URL_BASE = '//query.yahooapis.com/v1/public/yql';
  var FINANCE_URL_BASE = 'http://download.finance.yahoo.com/d/quotes.csv';
  this.configVars = configVars;
  
  /*************
   * The results are stored here in a 
   * map where the key is the ticker symbol
   * { 'AAPL' : { ... }, 'GOOG' : { ... }
   *************/
  this.results = {};
  
  /************
   * Function used to refresh the results
   * from Yahoo! Finance API. Called automatically
   * during object reaction.
   ************/
  this.refresh = function() {
    var queryUrl = getQueryUrl(this.configVars);
    var resp = UrlFetchApp.fetch(queryUrl,{muteHttpExceptions:true});
    if(resp.getResponseCode() == 200) {
      var jsonResp = JSON.parse(resp.getContentText());
      if(jsonResp.query.count == 1) {
        var row = jsonResp.query.results.row;
        this.results[row.symbol] = row;
      } else if(jsonResp.query.count > 1) {
        for(var i in jsonResp.query.results.row) {
          var row = jsonResp.query.results.row[i];
          this.results[row.symbol] = row;
        }
      }
    } else {
      throw resp.getContentText();
    }
  }
  
  /************
   * Translates the results into a 2d array
   * to make it easier to 广告 d into a Google Sheet.
   * includeColumnHeaders - true or false if you want
   *   headers returned in the results.
   ************/
  this.toGoogleSheet = function(includeColumnHeaders) {
    if(!this.results) { return [[]]; }
    var retVal = [];
    var headers = null;
    for(var key in this.results) {
      if(!headers) {
        headers = Object.keys(this.results[key]).sort();
      }
      var row = [];
      for(var i in headers) {
        row.push(this.results[key][headers[i]]);
      }
      retVal.push(row);
    }
    if(includeColumnHeaders) {
      return [headers].concat(retVal);
    } else {
      return retVal;
    }
  }
  
  // Perform a refresh on object creation.
  this.refresh();
  
  // Private functions
  
  /************
   * Builds Yahoo Finance Url
   ************/
  function getFinanceUrl(configVars) {
    var financeUrlParams = {
      s : configVars.symbols.join(','),
      f : configVars.f,
      e : '.json'
    }
    return FINANCE_URL_BASE + serialize(financeUrlParams);
  }
  
  /************
   * Builds Yahoo Query Url
   ************/
  function getQueryUrl(configVars) {
    var financeUrl = getFinanceUrl(configVars);
    var cols = fToCols(configVars.f);
    var queryTemplate = "select * from CSV where url='%s' and columns='%s'";
    var query = Utilities.formatString(queryTemplate, financeUrl,cols.join(','));
    var params = {
      q : query,
      format : 'json'
    }
    var finalRestUrl = QUERY_URL_BASE + serialize(params);
    return finalRestUrl;
  }

  /************
   * This function translates the f parameter
   * into actual field names to use for columns
   ************/
  function fToCols(f) {
    var cols = [];
    var chunk = '';
    var fBits = f.split('').reverse();
    for(var i in fBits) {
      chunk = (fBits[i] + chunk);
      if(fLookup(chunk)) {
        cols.push(fLookup(chunk));
        chunk = '';
      }
    }
    return cols.reverse();
  }
  
  /************
   * Copied from: http://stackoverflow.com/a/18116302
   * This function 兑换s a hash into 
   * a url encoded query string.
   ************/
  function serialize( obj ) {
    return '?'+
      Object.keys(obj).reduce(
        function(a,k) { 
          a.push(k+'='+encodeURIComponent(obj[k]));
          return a
        },
        []).join('&');
  }
  
  /************
   * Adapted from http://www.jarloo.com/yahoo_finance/
   * This function maps f codes into 
   * friendly column names.
   ************/
  function fLookup(f){
    return{
      a:'ask',b:'bid',b2:'ask realtime',b3:'bid realtime',p:'previous close',o:'open',
      y:'dividend yield',d:'dividend per share',r1:'dividend pay date',
      q:'ex-dividend date',c1:'change',c:'change & percent change',c6:'change realtime',
      k2:'change percent realtime',p2:'change in percent',d1:'last trade date',
      d2:'trade date',t1:'last trade time',c8:'after hours change realtime',
      c3:'commission',g:'days low',h:'days high',k1:'last trade realtime with time',
      l:'last trade with time',l1:'last trade price only',t8:'1 yr target price',
      m5:'change from 200 day moving average',m6:'percent change from 200 day moving average',
      m7:'change from 50 day moving average',m8:'percent change from 50 day moving average',
      m3:'50 day moving average',m4:'200 day moving average',w1:'days value change',
      w4:'days value change realtime',p1:'price paid',m:'days range',m2:'days range realtime',
      g1:'holdings gain percent',g3:'annualized gain',g4:'holdings gain',
      g5:'holdings gain percent realtime',g6:'holdings gain realtime',t7:'ticker trend',
      t6:'trade links',i5:'order book realtime',l2:'high limit',l3:'low limit',
      v1:'holdings value',v7:'holdings value realtime',s6: 'revenue',k:'52 week high',
      j:'52 week low',j5:'change from 52 week low',k4:'change from 52 week high',
      j6:'percent change from 52 week low',k5:'percent change from 52 week high',
      w:'52 week range',v:'more info',j1:'market 大写',j3:'market cap realtime',
      f6:'float shares',n:'name',n4:'notes',s:'symbol',s1:'shares owned',x:'stock exchange',
      j2:'shares outstanding',v:'volume',a5:'ask size',b6:'bid size',k3:'last trade size',
      a2:'average daily volume',e:'earnings per share',e7:'eps estimate current year',
      e8:'eps estimate next year',e9:'eps estimate next quarter',b4:'book value',j4:'ebitda',
      p5:'price sales',p6:'price book',r:'pe ratio',r2:'pe ratio realtime',r5:'peg ratio',
      r6:'price eps estimate current year',r7:'price eps estimate next year',s7:'short ratio'
    }[f];
  }
}