Boopis Media

Bitcoin Arbitrage Visualization with Google Sheets

david

·

·

,

If you have access to international bitcoin exchanges, you might be able to take advantage of bitcoin arbitrage opportunities. Google Sheets has a great Scripts features that allows you to program requests to APIs in Javascript and I will show you how to store all of that data in Google Sheets and even receive email alerts at a predefined threshold.

We’ll be using Coinbase and Indodax as our two exchanges with arbitrage between USD and IDR.

First let’s create a function to fetch data from Coinbase:

function getCoinbaseData() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Coinbase');
  var today = new Date();
  var time = Utilities.formatDate(today, 'UTC', 'yyyy-MM-dd HH:mm:ss z');

  var response = UrlFetchApp.fetch('https://api.coinbase.com/v2/prices/spot?currency=USD');  
  var responseObject = JSON.parse(response.getContentText());
      
  var amount = responseObject["data"]["amount"];
  
  sheet.getRange(sheet.getLastRow() + 1, 1, 1, 2).setValues([[time,amount]]);
}

Next let’s build our function to fetch data from Indodax:

function getIndodaxData() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Indodax');
  var today = new Date();
  var time = Utilities.formatDate(today, 'UTC', 'yyyy-MM-dd HH:mm:ss z');

  var response = UrlFetchApp.fetch('https://vip.bitcoin.co.id/api/btc_idr/ticker');  
  var responseObject = JSON.parse(response.getContentText());
      
  var amount = responseObject["ticker"]["last"];
  
  sheet.getRange(sheet.getLastRow() + 1, 1, 1, 2).setValues([[time,amount]]);
  
}

Lastly, let’s get currency exchange data from USD to IDR:

function getForexData() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Arbitrage');
  var today = new Date();
  var time = Utilities.formatDate(today, 'UTC', 'yyyy-MM-dd HH:mm:ss z');

  var response = UrlFetchApp.fetch('https://free.currencyconverterapi.com/api/v5/convert?q=USD_IDR&compact=y',{muteHttpExceptions: true}); 
  
  var responseCode = response.getResponseCode()

  if (responseCode === 200) {
    var responseObject = JSON.parse(response.getContentText());
    var amount = responseObject["USD_IDR"]["val"];
  } else {
    var amount = sheet.getRange(sheet.getLastRow(), 5).getValue();
  }
  
  var sheetCoinbase = ss.getSheetByName('Coinbase');
  var btc_usd = sheetCoinbase.getRange(sheetCoinbase.getLastRow(), 2).getValue();
  var btc_usd_idr = btc_usd * amount;
  
  var sheetIndodax = ss.getSheetByName('Indodax');
  var btc_idr = sheetIndodax.getRange(sheetIndodax.getLastRow(), 2).getValue();
  
  var spread = (1 - (btc_usd_idr / btc_idr)).toFixed(3);

  sheet.getRange(sheet.getLastRow() + 1, 1, 1, 6).setValues([[time,btc_usd,btc_idr,btc_usd_idr,amount,spread]]);

  if ( spread > 0.05 || spread < -0.05 ) {
    MailApp.sendEmail({
     to: "[email protected]",
     subject: "BTC/USD/IDR Spread",
     body: "BTC/USD: " + btc_usd + "\r\n" +
      "USD/IDR: " + btc_idr + "\r\n" +
      "BTC/USD/IDR: " + btc_usd_idr + "\r\n" +
      "SPREAD: " + spread
    });
  }
}

Above we merely fetch exchange data and read the data that we fetched from Coinbase and Indodax and run some simple calculations to determine what the spread is. We added a 5% threshold to send an email alert when arbitrage opportunities exist.

Now all that remains is to create triggers for the functions to run at the intervals you want.

And as you can see in the image below, we have opportunities for arbitrage between 3-6% every few weeks.

BTCUSD, BTCIDR, BTCUSDIDR, USDIDR and MAX SPREAD
,

david