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.

Thanks,
Russ

/******************************************
* Yahoo Finance API Class Example
* Version 1.0 
* Created By: Russ 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]);
  }
}

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
* FreeAdWordsScripts.com
******************************************/
function YahooFinanceAPI(configVars) {
  var QUERY_URL_BASE = 'https://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 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) {
        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 converts 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 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'
    }[f];
  }
}

executeInParallel on More than 50 Accounts Using Labels

One of the biggest limitations for people running MCC level scripts is the 50 account limit imposed by the executeInParallel function. Until recently, one of the ways to get around this limit was to store the processed accounts list on Google Drive and update the file when needed.

Recently, the AdWords Scripts team enabled the ability to apply labels to each account from the MccApp object. With this, we gain the ability to write a much cleaner version of the script that uses labels to indicate when each account has been processed. Using this method and running this script every hour, you could process up to 1,200 accounts per day.

The following code is meant to provide a framework for you to substitute your own MccApp code into. You can then schedule this code to run every hour, and it will continue processing the accounts in your MCC until each one of them is finished.

It will also attempt to notify you when there are accounts that return an error so that you can investigate. The one caveat about this script is that if you run into timeout limits on the Mcc level, namely in the results function, you might not apply the label to each successfully completed account. You can solve this by replacing line 76 with the following and removing line 89:

applyLabelsToCompletedAccounts([result.getCustomerId()]);

Anyway, I hope this helps and let me know if you run into any issues in the comments.

Thanks,
Russ

/******************************************
* MccApp Generic Runner Framework for any number of acounts
* Version 1.1
* Changelog v1.1 - fixed issue with selector in yesterdays label function
* Created By: Russ Savage (@russellsavage)
* FreeAdWordsScripts.com
******************************************/
// The name of the script you are running
// Used in error email subject line and label name
var SCRIPT_NAME = 'Generic MCC App';
// Since timezones are not available at the MCC level
// you need to set it here. You can use the local timezone
// of each account in the function processing each account
var TIMEZONE = 'PST';
// The date for today based on the timezone set above
var TODAY_STR = Utilities.formatDate(new Date(), TIMEZONE, 'yyyy-MM-dd');
// The label prefix which is used to figure out today's label and
// yesterday's label
var LABEL_PREFIX = SCRIPT_NAME + ' - Completed - ';
// This is the label that will be applied to each account
// when it is successfully processed
var FINISHED_LABEL_NAME = LABEL_PREFIX + TODAY_STR;
// This is a list of email addresses to notify when
// one of the accounts returns an error and is not processed.
var NOTIFY = ['your_email@your_domain.com'];

function main() {
  // Warning: if running in preview mode, this function will fail
  // and the selector that follows will also fail with "cannot read from AdWords"
  createLabelIfNeeded();
  removeYesterdaysLabel(); // This might not exist, but try to remove it
  // Find all the accounts that have not been processed
  var accountIter = MccApp.accounts()
    .withCondition("LabelNames DOES_NOT_CONTAIN '"+FINISHED_LABEL_NAME+"'")
    .withLimit(50)
    .get();
  // Add them to a list for the executeInParallel later
  var accountList = [];
  while(accountIter.hasNext()) {
    var account = accountIter.next();
    accountList.push(account.getCustomerId());
  }
  // If there are unprocessed accounts, process them
  if(accountList.length > 0) {
    MccApp.accounts()
      .withIds(accountList)
      .executeInParallel('doSomethingInEachAccount', 'reportOnResults');
  }
}

// This function is called from executeInParallel and contains the
// business logic for each account. Right now, it just has some 
// dummy logic to illustrate how this works.
function doSomethingInEachAccount() {
  /**************
   * Replace this function with what 
   * you want to do on each account
   **************/
  Logger.log("In account: "+AdWordsApp.currentAccount().getName()+
                        " "+AdWordsApp.currentAccount().getCustomerId());
  // This function must return a string so we use JSON.stringify() to
  // turn almost any object into a string quickly.
  return JSON.stringify({something:'else'});
}

// This function will be called as soon as the function above
// has been run on each account. The results object is an array
// of the results returned by the function run in each account.
function reportOnResults(results) {
  var completedAccounts = [];
  var erroredAccounts = [];
  for(var i in results) {
    var result = results[i];
    // If the account function returns success
    if(result.getStatus() == 'OK') {
      // Add it to the list to apply the label to
      completedAccounts.push(result.getCustomerId());
      /**********************
       * Fill in the code to process the results from 
       * each account just below this.
       **********************/
      var returnedValue = JSON.parse(result.getReturnValue());
    } else {
      // In case of an error, we should notify someone so they can
      // check it out.
      erroredAccounts.push({customerId:result.getCustomerId(), error: result.getError()});
    }
  }
  // Finally we apply the labels to each account
  applyLabelsToCompletedAccounts(completedAccounts);
  // And send an email with any errors
  notifyOfAccountsWithErrors(erroredAccounts);
}


/*******************************
 * Do not edit code below unless you know
 * what you are doing.
 *******************************/
// This function creates the required label to apply
// to completed accounts. You can change the label name
// by editing the FINISHED_LABEL_NAME variable at the top
// of this script.
function createLabelIfNeeded() {
  try {
    var labelIter = MccApp.accountLabels()
      .withCondition("LabelNames CONTAINS '"+FINISHED_LABEL_NAME+"'")
      .get();
  } catch(e) {
    MccApp.createAccountLabel(FINISHED_LABEL_NAME);
  }
}

// This function applies FINISHED_LABEL_NAME to each completed account
function applyLabelsToCompletedAccounts(completedAccounts) {
  var finishedAccountsIter = MccApp.accounts().withIds(completedAccounts).get();
  while(finishedAccountsIter.hasNext()) {
    var account = finishedAccountsIter.next();
    account.applyLabel(FINISHED_LABEL_NAME);
  }
}

// This function attempts to remove yesterday's label if it exists.
// If it doesn't exist, it does nothing.
function removeYesterdaysLabel() {
  var yesterday = new Date();
  yesterday.setDate(yesterday.getDate() - 1);
  var yesterdayStr = Utilities.formatDate(yesterday, TIMEZONE, 'yyyy-MM-dd');
  var yesterdayLabel = LABEL_PREFIX + yesterdayStr;
  Logger.log("Attempting to remove label: "+yesterdayLabel);
  try {
    var labelIter = MccApp.accountLabels().withCondition("Name CONTAINS '"+yesterdayLabel+"'").get();
    while(labelIter.hasNext()) {
      labelIter.next().remove();
    }
  } catch(e) { 
    // do nothing
  }
}

// This function will send an email to each email in the
// NOTIFY list from the top of the script with the specific error
function notifyOfAccountsWithErrors(erroredAccounts) {
  if(!erroredAccounts || erroredAccounts.length == 0) { return; }
  if(typeof NOTIFY == 'undefined') { throw 'NOTIFY is not defined.'; }
  var subject = SCRIPT_NAME+' - Accounts with Errors - '+TODAY_STR;
  
  var htmlBody = 'The following Accounts had errors on the last run.<br / >';
  htmlBody += 'Log in to AdWords: http://goo.gl/7mS6A';
  var body = htmlBody;
  htmlBody += '<br / ><br / >';
  htmlBody += '<table border="1" width="95%" style="border-collapse:collapse;">' +
              '<tr><td>Account Id</td><td>Error</td></tr>';
  for(var i in erroredAccounts) {
    htmlBody += '<tr><td>'+ erroredAccounts[i].customerId +
      '</td><td>' + erroredAccounts[i].error + '</td></tr>';
  }
  htmlBody += '</table>';
  // Remove this line to get rid of the link back to this site.
  htmlBody += '<br / ><br / ><a href = "http://www.freeadwordsscripts.com" >FreeAdWordsScripts.com</a>';
  var options = { htmlBody : htmlBody };
  for(var i in NOTIFY) {
    Logger.log('Sending email to: '+NOTIFY[i]+' with subject: '+subject);
    MailApp.sendEmail(NOTIFY[i], subject, body, options);
  }
}

Use Google Feed API to Convert RSS to JSON

Here is a quick tip for anyone sick of working with the xml in RSS feeds. You can use the Google Feed API in your scripts to automatically convert the XML in the RSS feed to JSON which makes working with them much easier. For example, there is an RSS Feed from the Consumer Product Safety Commission and it has all the government recalls in the United States. Using this simple trick, you could easily pull the fresh feed on a regular basis and parse out information about recalls to use in your account. You can also configure Google Alerts to generate RSS feeds based on specific keywords you are interested in. For example, here is an RSS feed for the keyword "smx".

Thanks,
Russ

/******************************************
* Use Google Feed API to convert RSS to json
* Version 1.0 
* Created By: Russ Savage
* FreeAdWordsScripts.com
******************************************/
// Usage: var jsonData = convertRssToJson('http://www.cpsc.gov/en/Newsroom/CPSC-RSS-Feed/Recalls-RSS/');
function convertRssToJson(rssUrl) {
  var FEED_API_URL = "https://ajax.googleapis.com/ajax/services/feed/load?v=1.0&q="
  var url = FEED_API_URL+encodeURIComponent(rssUrl);
  var resp = UrlFetchApp.fetch(url);
  if(resp.getResponseCode() == 200) {
    return JSON.parse(resp.getContentText());
  } else {
    throw "An error occured while trying to parse: "+rssUrl;
  }
}

Pull Salesforce Data into AdWords Using Scripts

After my post about importing Zoho CRM data into AdWords, I received a lot of comments about doing something similar for Salesforce. I finally had some time to build a simple class that allows you to query data and objects from your Salesforce instance and use it in your scripts.

First, we will need to set you up with some OAuth credentials, and for that, you need to set up a new connected app in Salesforce. It is in slightly different places in each version, but using the developer version, I was able to find it under Setup > Build > Create > Apps. From there, all the way at the bottom, you can see a section for Connected Apps.

If for some reason you can't find it in your Salesforce instance, your admin may not have given you access to it. Hopefully, they can help you.

After clicking the new button, you will need to fill out a few required fields and then select the option to "Enable Oauth Settings." You will need to enter a callback url but we won't be using it so you can enter any url that starts with https. For "Scopes", I just said "Full Access" but you may have to talk to your Salesforce Admin about that one. We will only be reading from Salesforce so it shouldn't be an issue.

That's all you need to fill out and you should have a new app created. The important thing here is the "Consumer Key" and the "Consumer Secret" that you will need for the script to connect to your Salesforce instance.

The last thing you will need from your Salesforce instance is a security token. You may already have one in which case, you can skip this. But if not, you can reset it under My Settings > Personal > Reset my Security Token. It will email you a new token.

Ok, now we are finally ready to get to the AdWords Scripts code. The following code will set up a new SalesforceAPI object and query the most recent Opportunities that were Close Won so that you can use that revenue in your AdWords account.

It's that simple. If you want to get all the information about a particular object after you query for it, you can use the function getObjectByUrl() and send it the url from the query results. To learn more about the query syntax, check out the Salesforce SOQL documentation.

There are a few caveats for this code. Every Salesforce installation is unique so there really is no way for me to really troubleshoot issues with your specific install. This code was tested on a fresh Salesforce for Developers account so your results may vary. You will probably have more luck contacting your Salesforce Admin than leaving a comment here. Also, you may notice that the code it using the least secure option to log into Salesforce. This code with your username and password will be accessible to all users of your AdWords account, so be careful. It might be better to create a special Salesforce user with very limited permissions for something like this.

If you think this is useful, come let me know at SMX East this week.

Thanks,
Russ