Friday, July 24, 2015

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
* FreeAdWordsScripts.com
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
* 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) {
    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: http://stackoverflow.com/a/18116302
   * This function converts a hash into 
   * a url encoded query string.
  function serialize( obj ) {
    return '?'+
        function(a,k) { 
          return a
   * Adapted from http://www.jarloo.com/yahoo_finance/
   * 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'

Thursday, May 7, 2015

Join Me on Reddit.com/r/PPC Today @ 1pm EST

Thanks for joining. Here's the link to the post in reddit.com/r/ppc if you want to read the answers.

Please join me on reddit.com/r/PPC May 7th at 1pm EST (today) to ask questions about AdWords scripts. If you can't make it, be sure to post in the questions thread and I will try my best to answer during the AMA.


Sunday, November 9, 2014

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:


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


* 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
// 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
// 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"
  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+"'")
  // Add them to a list for the executeInParallel later
  var accountList = [];
  while(accountIter.hasNext()) {
    var account = accountIter.next();
  // If there are unprocessed accounts, process them
  if(accountList.length > 0) {
      .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
       * 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
  // And send an email with any errors

 * 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+"'")
  } catch(e) {

// 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();

// 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()) {
  } 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);

Tuesday, September 30, 2014

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".


* 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;