Bitcoin Arbitrage Visualization with Google Sheets

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

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

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: "your@email.com",
     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
    });
  }
}

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

Connecting Google Sheets With Quickbooks Google Sheets is a great tool to interactively work with an assortment of APIs to work as an intermediary database for experiments or small scale data...

Google Apps Script to Extract Emails If you have a generic email that is being sent to you regularly and you happen to be a gmail user, you can easily extract that data into a spreadsheet...

Using Node.js and OpenCV to Count Colonies on a Pe... If you're looking for a relatively simple way to automate routine tasks that involve extracting information from images, using computer vision librari...

Boopis Media

Tips For Web Bootstrapping

Bitcoin Arbitrage Visualization with Google Sheets

Related Posts

Related

Leave a Reply Cancel reply