Since it’s almost time for Eurovision again, I thought I’d see if I could find something to do with predicting the result, rather than analyzing the results afterwards (as in this Eurovision results with crossfilter and dc.js example).

Turns out that Predictwise have lots of predictions, including the likely winners of the Eurovision. They don’t seem to actually have an API, but they do have some tables with results in them. I also couldn’t find any copyright info, so this is intended purely as a demo exercise. You’ll need to look into the copyright yourself if you plan to use this data.

A little scraping with Apps Script showed me what they had, which I put in the index tab in this sheet.

// do a bit of digging to see what tables exist
function scrape() {

    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sh = ss.getSheetByName("index");
    var hits = [];
    sh.getDataRange().clearContent();
    sh.getRange(1,1,1,4).setValues ([["id","name","notes","timestamp"]]);

    for (var i = 1; i <5000 ; i++ ) {
        var result = getData (i);
        if (result) {
            hits.push ([result.id,result.name,result.notes,result.timestamp]);
        }
    }

    if (hits.length) {
        sh.getRange (2,1,hits.length,hits[0].length).setValues(hits);
    }
}

function getData (tableNumber) {

  var URL = "http://table-cache1.predictwise.com/latest/table_";
  var result = UrlFetchApp.fetch(URL+(tableNumber||321).toString()+".json",{
    muteHttpExceptions:true
  });

  if (result.getResponseCode() !== 404 && result.getResponseCode() !== 200) {
    throw 'error ' + result.getResponseCode() + ' ' + result.getContentText();
  }
  return result.getResponseCode() === 404 ? null : JSON.parse(result.getContentText());
}

So with this snippet

// can be called from sheet - just supply the table number
function importPredictWise(tableNumber) {

  var table = getData (tableNumber) ;
  var data = [table.header];
  Array.prototype.push.apply(data,table.table);
  return data;
  
}


function getData (tableNumber) {
  var URL = "http://table-cache1.predictwise.com/latest/table_";
  var result = UrlFetchApp.fetch(URL+(tableNumber||321).toString()+".json",{
    muteHttpExceptions:true
  });
  
  if (result.getResponseCode() !== 404 && result.getResponseCode() !== 200) {
       throw 'error ' + result.getResponseCode() + ' ' + result.getContentText();
  }
  return result.getResponseCode() === 404 ? null : JSON.parse(result.getContentText());
  }

You can do this on your sheet

=importPredictWise(some-id-from-the-index-sheet)

and pull in any of the 000’s of predictions I found.

..such as the eurovision 2015 result (=importPredictWise(1036)

Outcome PredictWise Derived Betfair Price Betfair Back Betfair Lay
Italy 18 % $ 0.187 5.30 5.40
Estonia 5 % $ 0.050 18.50 22.00
Finland 2 % $ 0.018 40.00 90.00
Sweden 29 % $ 0.292 3.40 3.45
Norway 1 % $ 0.014 65.00 75.00
Australia 11 % $ 0.110 9.00 9.20
United Kingdom 0 % $ 0.004 270.00 300.00
Germany 0 % $ 0.001 780.00 980.00
Azerbaijan 1 % $ 0.016 60.00 65.00
Slovenia 1 % $ 0.014 65.00 75.00
Armenia 0 % $ 0.002 420.00 730.00
Russia 24 % $ 0.241 4.00 4.30
Spain 0 % $ 0.003 340.00 390.00
Belgium 1 % $ 0.014 65.00 75.00
Serbia 1 % $ 0.016 60.00 70.00
Greece 0 % $ 0.003 260.00 490.00
Latvia 0 % $ 0.005 190.00 220.00
Romania 0 % $ 0.002 460.00 690.00
Lithuania 0 % $ 0.001 660.00 980.00
Poland 0 % $ 0.001 800.00 920.00
Hungary 0 % $ 0.002 460.00 490.00
Georgia 0 % $ 0.003 210.00 940.00
Iceland 0 % $ 0.004 240.00 280.00
Montenegro 0 % $ 0.001 1,000.00 0.00
Albania 0 % $ 0.002 500.00 990.00
Ireland 0 % $ 0.001 760.00 970.00
Switzerland 0 % $ 0.001 1,000.00 0.00
Netherlands 0 % $ 0.004 170.00 690.00
Malta 0 % $ 0.001 900.00 1,000.00
Czech Republic 0 % $ 0.001 1,000.00 0.00
Cyprus 1 % $ 0.006 140.00 190.00
Denmark 0 % $ 0.002 430.00 990.00
Moldova 0 % $ 0.001 950.00 0.00
Austria 0 % $ 0.002 600.00 650.00
San Marino 0 % $ 0.001 1,000.00 0.00
Israel 0 % $ 0.003 380.00 430.00
France 0 % $ 0.006 140.00 220.00
Portugal 0 % $ 0.001 1,000.00 0.00
Belarus 0 % $ 0.002 400.00 990.00
FYR Macedonia 0 % $ 0.001 820.00 0.00

..or the likely 2016 presidential winner (=importPredictWise(321))

Potential Candidate PredictWise Betfair PredictIt
Hillary Clinton 46 % 46.1 % 56.5 %
Marco Rubio 10 % 9.6 % 22.0 %
Jeb Bush 19 % 19.2 % 29.5 %
Chris Christie 1 % 1.1 % 6.5 %
Paul Ryan 0 % 0.4 % N/A
Rob Portman 0 % 0.1 % N/A
Andrew Cuomo 0 % 0.1 % N/A
Joe Biden 1 % 1.4 % 7.0 %
Condoleezza Rice 0 % 0.1 % N/A
Martin OMalley 1 % 1.1 % 6.0 %
Michael Bloomberg 0 % 0.3 % 5.0 %
Susana Martinez 0 % 0.1 % N/A
Rahm Emanuel 0 % 0.1 % N/A
Elizabeth Warren 4 % 4.1 % 6.5 %
Bobby Jindal 1 % 1.3 % N/A
Bob McDonnell 0 % 0.1 % N/A
Deval Patrick 0 % 0.1 % N/A
Scott Walker 8 % 8.2 % 14.5 %
Jon Huntsman 0 % 0.4 % N/A
Sarah Palin 0 % 0.3 % 3.0 %
Al Gore 1 % 1.3 % N/A
Rand Paul 5 % 5.1 % 12.5 %
Rick Perry 1 % 0.5 % N/A
Cory Booker 0 % 0.1 % N/A
Ben Carson 1 % 0.8 % N/A
Joe Manchin 0 % 0.1 % N/A
Ted Cruz 1 % 0.8 % 9.0 %
Mitt Romney 1 % 0.6 % 2.5 %
For more like this see Google Apps Scripts Snippets
For more like this, see Google Apps Scripts snippets. Why not join our community , follow the blog, twitter, G+ .