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.

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];
  }
}

37 comments:

  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.


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

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

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

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

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

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

    ReplyDelete
  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

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

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






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

    ReplyDelete
  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

    ReplyDelete
  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

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

    ReplyDelete
  15. Thank you for the valuable post. It seems very informative!
    business listing USA

    ReplyDelete
  16. Very informative post! There is a lot of information here that can help any business get started with a successful social networking campaign. leadpages alternatives

    ReplyDelete
  17. I appreciate your post thanks for sharing the information.
    small CBD boxes
    E Liquid boxes New castle

    ReplyDelete
  18. 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.
    https://nj-sanitaerdienst.de/

    ReplyDelete
  19. Professionelle Schädlingsbekämpfung und Kammerjäger in München und Umgebung sowie in allen Teilen der Stadt und Umgebung.
    Wir bekämpfen kompetent, effektiv und vor allem diskret jede Art von Schädlingsbefall.
    Wir von Kammerjäger München 24 sind Kammerjäger München.
    https://kammerjaeger24-muenchen.de/

    ReplyDelete
  20. It'd be the same amount of time THC stays in your system. It doesn't change because of it entering the body
    via vape versus a joint or a concentrate.
    How long does one hit of a wax pen stay in your system

    ReplyDelete
  21. best GPS Car tracker started with investigating top-rated location trackers to see which consistently performed best. We then d
    id our own deep-dig research to see what users were saying about each device so we could choose the best of the best.
    Tracker companies in pakistan

    ReplyDelete
  22. 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

    ReplyDelete
  23. Good pictures and great post very informative thanks for sharing with us.
    Hair Extension boxes manufacturer
    Handover Boxes new york

    ReplyDelete