Thursday, June 8, 2017

Pull Stock Quotes Into AdWords Scripts Using Yahoo! Finance API

I was recently asked on Twitter if I had ever seen a script that used stock market performance to adjust bids. Honestly I never have, but I have been asked about this ability multiple times. So I thought I'd build something to do just that.

Finding a reliable and free API for stock data is a little difficult, but everyone seems to point to a somewhat hidden Yahoo! Finance API. Despite the fact that there are multiple libraries built around it, I couldn't find much in the way of documentation other than a StackOverflow post that talks about it. So long story short, this API could stop working at anytime, so use at your own risk.

Here is some sample code to get you started using this. The code below simply looks up a few quotes (one from Bitcoin) and loads them into a Google Spreadsheet of your choosing. Pretty straightforward. The one confusing thing is the "f=" parameter that you need to pass to the API. It is documented a little bit in this blog post but is still pretty confusing. It is a string of one or two character codes that is used to define the columns you want to return. For most people, the symbol, name, and current price should be enough. Feel free to customize it as needed.

* Yahoo Finance API Class Example
* Version 1.0 
* Created By: Russ Savage
function main() {
  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', 
  var includeColumnHeaders = true;
  var sheetData = yfa.toGoogleSheet(includeColumnHeaders);
  var ss = SpreadsheetApp.openByUrl(sheetUrl).getActiveSheet();
  for(var i in sheetData) {

Just copy the follow code into the bottom of your AdWords script and you should be good to go.
* Yahoo Finance API Class
* Use this to pull stock market quotes from Yahoo Finance
* Version 1.0 
* Created By: Russ Savage
function YahooFinanceAPI(configVars) {
  var QUERY_URL_BASE = '';
  var FINANCE_URL_BASE = '';
  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 add 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) {
    if(includeColumnHeaders) {
      return [headers].concat(retVal);
    } else {
      return retVal;
  // Perform a refresh on object creation.
  // 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)) {
        chunk = '';
    return cols.reverse();
   * Copied from:
   * This function converts a hash into 
   * a url encoded query string.
  function serialize( obj ) {
    return '?'+
        function(a,k) { 
          return a
   * Adapted from
   * This function maps f codes into 
   * friendly column names.
  function fLookup(f){
      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 capitalization',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'


  1. Funded Trader was founded for one reason and one reason only. To FINANCIALLY EMPOWER Traders and help them to make an INCOME doing what they LOVE. The firm was established by Traders for Traders who understand that a lot of people spend tons of money on expensive Trading courses and perfecting their skills but unfortunately lack the funds to actually take their newly acquired skills and actually make a living from it for more information click Common Aspects of Major Forex Traders
    and get quality results.

  2. Hi, I like, the material you have presented. Moreover, I'm sure this script will be useful as a good sample of hungry 40s task solution.

  3. Download update Garmin Map express ,Get the latest street maps and points of interest for all Garmin product categories: automotive, golf, marine, aviation, outdoor and cycling.

  4. Coding is important for every programmer If you are newbie so you will definitely get the error so this is the right time to enhance your Skills. As web designer in Dubai faces lots of coding error but it give me the confidence to code fearless and make the new productive website.

  5. Hold solid gold in your IRA. Find out how to roll your 401k or IRA into pure gold...TAX FREE See our 10 best Gold IRA's + get your FREE Gold Kit 10 Best Gold IRA Companies for 2020.

  6. Great Content, Just loved the way you presented itÖ Keep up the good work Happy New Year Wishes

  7. NuggPlug is the world's most convenient 420-platform that offers our users access to hundreds of the top Cannabis, hemp brands in the world for more about us visit the website nuggplug.

  8. Quickbooks File Doctor is a usefull tool which fixes any kind of error that occurs in quick books accounting software, if you prefer Quickbooks for your daily accounting needs, then you would really like to know about this tool

  9. Your blogs are great.Are you also searching for Pico assignment? we are the best solution for you. We are best known for delivering nursing writing services to students without having to break the bank.

  10. Amazing post! i like it this is very usefull for me also check how much does it cost to build a soccer field

  11. Thank you for the valuable post. It seems very informative! free url submission sites list

  12. Bei stark bis extrem inkrustierten, das heißt verengten Rohrleitungen, kommt die hydro­mecha­nische Rohrreinigung zum Einsatz. Auch hierbei verwenden die Fach­leute von Kuchler Service weder Säuren, Laugen noch um­welt­be­lastende Spül­mittel. Und den­noch er­rei­chen sie zügig das Ziel: eine saubere Leitung – durch eine rund­herum saubere Arbeit, die mehr Effizienz bringt als Kosten nach sich zieht.

    klempner deggendorf

  13. Machen Sie sich keine Sorgen. Durch den Schlüsseldienst Regensburg müssen Sie nicht auf einen schönen Kinoabend mit Freunden verzichten. Er ist Ihr kompetenter Ansprechpartner rund um Türöffnungen. Zum Fachgebiet gehören aber nicht nur Haus- und Wohnungstüren. Unsere Partner helfen Ihnen gerne bei allen Arten von Türöffnungen, wie bei der Autoöffnung Regensburg oder dem Öffnen einer Tresortür.

    Schlüsseldienst regensburg

  14. Brown paper bags wholesale at Half Price Packaging is a useful carrier for packaging all sorts of daily products. By ordering from us, get free delivery.