currency map

app.js

// NODE.JS app
// Harvest Yahoo Finance and update CartoDB
var express = require('express');
var app = express();
var http = require('http'),
    https = require('https'),
    querystring = require('querystring'),
    Step = require('step'),
    fs = require('fs');

    
var config = {}
try {
    var config = require('./config')
} catch(e) { }


String.prototype.format = (function (i, safe, arg) {
    function format() {
        var str = this,
            len = arguments.length + 1;

        for (i = 0; i < len; arg = arguments[i++]) {
            safe = typeof arg === 'object' ? JSON.stringify(arg) : arg;
            str = str.replace(RegExp('\\{' + (i - 1) + '\\}', 'g'), safe);
        }
        return str;
    }
    return format;
})();

function callYahoo(values) {
  // console.log(sql)
    values = values.join('", "USD')
    var query = 'select * from yahoo.finance.xchange where pair in ("USD{0}")'.format(values)
    var post_data = querystring.stringify({
          'q': query,
          'format': 'json',
          'env': 'store://datatables.org/alltableswithkeys'
    });
    var post_options = {
            host: 'query.yahooapis.com',
            port: '80',
            path: '/v1/public/yql',
            method: 'POST',
            headers: {
                'Content-Type': 'application/x-www-form-urlencoded',
                'Content-Length': post_data.length
            }
    };  
    var post_req = http.request(post_options, function(res) {
          res.setEncoding('utf8');
          res.on('data', function (chunk) {

            var data = JSON.parse(chunk);
            var sql = "";
            for (i in data['query']['results']['rate']){
              var cur = data['query']['results']['rate'][i]['id'].replace('USD','');
              var val = data['query']['results']['rate'][i]['Rate'];
              var tim = data['query']['results']['rate'][i]['Date'] + ' ' + data['query']['results']['rate'][i]['Time'] ;
              if (cur && val){
                sql += "UPDATE currencies_list SET latest = {0}, change = case when latest=0 then 999.999 else 100.0*({0} - latest)/latest end, quote_time = '{2}'::timestamp WHERE code = '{1}'; ".format(val,cur,tim);
                // console.log(sql)
              }
            }
            // console.log(sql)
            callCartoDB(sql)
          });
          res.on('end', function(code) {
           // console.log('done')
          });
      });
    // console.log(post_data)
    post_req.write(post_data);
    post_req.end();
}


function callCartoDB(sql) {
  // console.log(sql)
    var post_data = querystring.stringify({
          'api_key' : config.cartodb.api_key,
          'q': sql
    });
    var post_options = {
            host: config.cartodb.username+'.cartodb.com',
            port: '80',
            path: '/api/v2/sql',
            method: 'POST',
            headers: {
                'Content-Type': 'application/x-www-form-urlencoded',
                'Content-Length': post_data.length
            }
    };  
    var post_req = http.request(post_options, function(res) {
          res.setEncoding('utf8');
          res.on('data', function (chunk) {
              process.stdout.write(".");
          });
      });
    // console.log(post_data)
    post_req.write(post_data);
    post_req.end();
}

var total_inserts = 0;
var run = function(){
  // var a = YOUR_ARRAY;
  var minutes = 10, the_interval = minutes * 60 * 1000;
  setInterval(function() {
    var recur = ['XCD', 'ETB', 'HUF', 'CZK', 'JMD', 'BZD', 'SZL', 'CAD', 'LBP', 'XOF', 'EGP', 'BMD', 'AFN', 'STD', 'AMD', 'NOK', 'GEL', 'BIF', 'RUB', 'MKD', 'ALL', 'CNY', 'ISK', 'PEN', 'SLL', 'LSL', 'DOP', 'CVE', 'BTN', 'IRR', 'XPF', 'CHF', 'FKP', 'XAF', 'NZD', 'KGS', 'SRD', 'SDG', 'LKR', 'BWP', 'RON', 'NIO', 'VUV', 'BAM', 'INR', 'COP', 'IDR', 'SSP', 'ILS', 'CRC', 'BBD', 'SHP', 'YER', 'BRL', 'KYD', 'TND', 'ZMW', 'UZS', 'QTQ', 'UGX', 'AOA', 'KWD', 'PLN', 'GMD', 'CUP', 'BSD', 'AED', 'SVC', 'HNL', 'ANG', 'DKK', 'GIP', 'KES', 'ECS', 'SOS', 'PAB', 'TRY', 'AWG', 'MUR', 'MXN', 'BGN', 'GYD', 'THB', 'EUR', 'LTL', 'LYD', 'ZWD', 'USD', 'BHD', 'TTD', 'AUD', 'OMR', 'SEK', 'KRW', 'VND', 'UAH', 'RSD', 'MOP', 'KPW', 'AZN', 'PKR', 'MVR', 'MRO', 'PHP', 'CLP', 'KHR', 'GWP', 'MGF', 'SAR', 'MNT', 'MYR', 'GHS', 'NAD', 'MWK', 'HTG', 'WST', 'RWF', 'PGK', 'GGP', 'BND', 'QAR', 'VEF', 'CDF', 'TOP', 'HRK', 'DZD', 'BOB', 'PYG', 'IQD', 'NGN', 'JOD', 'SCR', 'MZN', 'ZAR', 'KMF', 'BDT', 'SGD', 'BYR', 'MAD', 'SBD', 'UYU', 'DJF', 'TWD', 'LAK', 'JPY', 'TMT', 'SYP', 'GNF', 'TJS', 'LRD', 'MMK', 'ARS', 'MDL', 'ERN', 'KZT', 'HKD', 'LVL', 'FJD', 'TZS', 'GBP', 'NPR'];
    while(recur.length) {
      callYahoo(recur.splice(0,7));
    }
    total_inserts += 1;
  }, the_interval);
}

app.set('port', (process.env.PORT || 5000))
app.use(express.static(__dirname + '/public'))

app.get('/', function(request, response) {
  response.send('Your app has made {0} total inserts</br>Writing to {1}</br>andrew'.format(total_inserts, config.cartodb.username))
})

app.listen(app.get('port'), function() {
  console.log("Node app is running at localhost:" + app.get('port'));
  run();
})

index.html

<!DOCTYPE html>
<html lang="en">
  <head>
    <meta charset="utf-8">
    <!--Edit the title of the page-->
    <title>Live currency map</title>
    <meta name="description" content="">
    <meta name="author" content="">
    <meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1">
    <link rel="stylesheet" href="http://libs.cartocdn.com/cartodb.js/v3/themes/css/cartodb.css" />
    <!--[if lte IE 8]>
      <link rel="stylesheet" href="http://libs.cartocdn.com/cartodb.js/v3/themes/css/cartodb.ie.css" />
    <![endif]-->
    <!--Switch between the different themes changing the stylesheet below - light-theme.css |dark-theme.css -->
    <style type="text/css">
    body, html, #map {width: 100%; height: 100%; margin: none; padding: none;}
    #time { position: absolute; bottom: 40px; left: 20px; z-index: 1000; color: white;}
    </style>
  </head>
  <body>

    <div id="map"></div>
    <div id="time"></div>


    <script src="http://libs.cartocdn.com/cartodb.js/v3/cartodb.js"></script>

    <script type="text/javascript">
      var map;

      function main() {
        map = L.map('map', { 
          zoomControl: true,
          center: [31.2034, -29.1797],
          zoom: 2
        })
        var layer = L.tileLayer('http://{s}.basemaps.cartocdn.com/dark_nolabels/{z}/{x}/{y}.png',{
          attribution: '&copy; <a href="http://www.openstreetmap.org/copyright">OpenStreetMap</a> contributors, &copy; <a href="http://cartodb.com/attributions">CartoDB</a>'
        });
        map.addLayer(layer);

        var sql = cartodb.SQL({ user: 'andrew-free' });
        // add cartodb layer with one sublayer
        cartodb.createLayer(map, "https://andrew-free.cartodb.com/api/v2/viz/49d67aca-fe7e-11e4-a5dc-7054d21a95e5/viz.json", {refreshTime: 1000*60*1.5})
        .addTo(map)
        .done(function(layer) {
          sql.execute("select extract('minutes' from (now()+INTERVAL '1 hour') - max(quote_time))::text minutes FROM currencies_list").done(function(data) {
            $('#time').html("Data last updated "+data.rows[0].minutes + " minutes ago")
          })

        });


      }
      window.onload = main; 
    </script>

  </body>
      </html>