Importing Predictwise data

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))
OutcomePredictWiseDerived Betfair PriceBetfair BackBetfair Lay
Italy18 %$ 0.1875.305.40
Estonia5 %$ 0.05018.5022.00
Finland2 %$ 0.01840.0090.00
Sweden29 %$ 0.2923.403.45
Norway1 %$ 0.01465.0075.00
Australia11 %$ 0.1109.009.20
United Kingdom0 %$ 0.004270.00300.00
Germany0 %$ 0.001780.00980.00
Azerbaijan1 %$ 0.01660.0065.00
Slovenia1 %$ 0.01465.0075.00
Armenia0 %$ 0.002420.00730.00
Russia24 %$ 0.2414.004.30
Spain0 %$ 0.003340.00390.00
Belgium1 %$ 0.01465.0075.00
Serbia1 %$ 0.01660.0070.00
Greece0 %$ 0.003260.00490.00
Latvia0 %$ 0.005190.00220.00
Romania0 %$ 0.002460.00690.00
Lithuania0 %$ 0.001660.00980.00
Poland0 %$ 0.001800.00920.00
Hungary0 %$ 0.002460.00490.00
Georgia0 %$ 0.003210.00940.00
Iceland0 %$ 0.004240.00280.00
Montenegro0 %$ 0.0011,000.000.00
Albania0 %$ 0.002500.00990.00
Ireland0 %$ 0.001760.00970.00
Switzerland0 %$ 0.0011,000.000.00
Netherlands0 %$ 0.004170.00690.00
Malta0 %$ 0.001900.001,000.00
Czech Republic0 %$ 0.0011,000.000.00
Cyprus1 %$ 0.006140.00190.00
Denmark0 %$ 0.002430.00990.00
Moldova0 %$ 0.001950.000.00
Austria0 %$ 0.002600.00650.00
San Marino0 %$ 0.0011,000.000.00
Israel0 %$ 0.003380.00430.00
France0 %$ 0.006140.00220.00
Portugal0 %$ 0.0011,000.000.00
Belarus0 %$ 0.002400.00990.00
FYR Macedonia0 %$ 0.001820.000.00

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

Potential CandidatePredictWiseBetfairPredictIt
Hillary Clinton46 %46.1 %56.5 %
Marco Rubio10 %9.6 %22.0 %
Jeb Bush19 %19.2 %29.5 %
Chris Christie1 %1.1 %6.5 %
Paul Ryan0 %0.4 %N/A
Rob Portman0 %0.1 %N/A
Andrew Cuomo0 %0.1 %N/A
Joe Biden1 %1.4 %7.0 %
Condoleezza Rice0 %0.1 %N/A
Martin OMalley1 %1.1 %6.0 %
Michael Bloomberg0 %0.3 %5.0 %
Susana Martinez0 %0.1 %N/A
Rahm Emanuel0 %0.1 %N/A
Elizabeth Warren4 %4.1 %6.5 %
Bobby Jindal1 %1.3 %N/A
Bob McDonnell0 %0.1 %N/A
Deval Patrick0 %0.1 %N/A
Scott Walker8 %8.2 %14.5 %
Jon Huntsman0 %0.4 %N/A
Sarah Palin0 %0.3 %3.0 %
Al Gore1 %1.3 %N/A
Rand Paul5 %5.1 %12.5 %
Rick Perry1 %0.5 %N/A
Cory Booker0 %0.1 %N/A
Ben Carson1 %0.8 %N/A
Joe Manchin0 %0.1 %N/A
Ted Cruz1 %0.8 %9.0 %
Mitt Romney1 %0.6 %2.5 %

For more like this, see  Google Apps Scripts snippets. Why not join our community , follow the blog, twitter, G+  .

You want to learn Google Apps Script?
Learning Apps Script, (and transitioning from VBA) are covered comprehensively in my my book, Going Gas - from VBA to Apps script, available All formats are available now from O'Reilly,Amazon and all good bookshops. You can also read a preview on O'Reilly. 
If you prefer Video style learning I also have two courses available. also published by O'Reilly.
 Google Apps Script for Developers and Google Apps Script for Beginners.


Comments