Covering the Crypto craziness with Google Sheets and Tableau

Snag_3d1270f

If you’re like most people, you’ve probably dabbled a little in the crypto craziness and now need a nice neat way to see how much money you’re…losing…making

Most Crypto currency exchanges and trading sites have a way to access their data via an API.  Independent Reserve in Australia is one such Crypto-currency exchange platform that provides an API to access their  feeds and they provide both a publicly accessible API and a private one that will require obtaining an API key before it returns any results.

Here is an example of their “GetTradeHistorySummary” API:

Snag_21fe06.png

The data is returned in JSON format, for example:

Snag_3ab39f4.png

So the challenge is to get this into Tableau and have it automatically update and track the changes.  Their “…API is rate limited to 1 call per second…” which is way more than what we need 🙂   They already provide good info if you’re into hardcore trading and prepared to sit in front of your screen day in day out… However, for most of us, we don’t need that.  Every few hours or even updated a couple of times a day is fine for us mere mortals 🙂

Although there are many methods to get data into Tableau (e.g. a Web Data Connector would be cool! ), the method I’ll cover is to use Google Sheets to periodically pull the data in on a schedule.

  1. Open Google sheets , sign-in and start a new spreadsheet
  2. Go to Tools -> Script Editor
  3. Using this importJSON script  simply copy and paste over the current content.
  4. Rename “Code.gs” to something like “importJSON.gs”
  5. Save.

Back in the Spreadsheet, you can use the “=importJSON()” function in a cell with the API as a parameter.  For example:

=importJSON("https://api.independentreserve.com/Public/GetTradeHistorySummary?primaryCurrencyCode=xbt&secondaryCurrencyCode=aud&numberOfHoursInThePastToRetrieve=240")

The above will bring in the last 240 hours or 10 days (currently the maximum supported by Independent Reserve) worth of trade transactions for Bitcoin (XBT) in AUD format.

Snag_385340.png

Independent Reserve currently support three Crypto-currencies, Bitcoin, Bitcoin Cash and Ethereum and the codes are XBT, BCH and ETH respectivley.  These codes are referenced in the API call:

Snag_38c247f.png

Note the entry for “XBT” to indicate Bitcoin.  You will need to copy and modify this entry for each of the sheets.

Create another 2x sheets (3 in total) pulling in the last 10 days (240 hours) worth of transactions for the other currencies.  I named the sheet-tabs as XBT Trades, ETH Trades and BCH Trades.

Snag_34e2b2.png

=importJSON("https://api.independentreserve.com/Public/GetTradeHistorySummary?primaryCurrencyCode=xbt&secondaryCurrencyCode=aud&numberOfHoursInThePastToRetrieve=240")
=importJSON("https://api.independentreserve.com/Public/GetTradeHistorySummary?primaryCurrencyCode=bch&secondaryCurrencyCode=aud&numberOfHoursInThePastToRetrieve=240")
=importJSON("https://api.independentreserve.com/Public/GetTradeHistorySummary?primaryCurrencyCode=eth&secondaryCurrencyCode=aud&numberOfHoursInThePastToRetrieve=240")

Once done, you should now have 3x sheet-tabs with data being pulled from Independent Reserve into your Google Sheets.

This is all good and well, but doesn’t automatically update…

Automatically updating Google Sheets data

The previous example, gets the JSON data into a nice neat format in Google Sheets…

Next thing to do is to automate obtaining the data via Independent Reserve’s API on a schedule.  There are probably a couple of different ways to do this, I’ve settled on using a Google Script and Google’s Triggers to automate this process.

In Google Sheets…

  1. Go to Tools -> Script Editor
  2. Copy and Paste the following:
function getDataIReth() {

// Set the active sheet
var ss = SpreadsheetApp.getActiveSpreadsheet();
var active = ss.getSheetByName("ETH Trades");
active.activate();

// Refresh the table
var queryString = Math.random();
var cellFunction = '=importJSON("https://api.independentreserve.com/Public/GetTradeHistorySummary?primaryCurrencyCode=eth&secondaryCurrencyCode=aud&numberOfHoursInThePastToRetrieve=240")';

SpreadsheetApp.getActiveSheet().getRange('A1').setValue(cellFunction);
}

Snag_15dde19.png

Save, give it a name and create a couple more or how ever many you require.  I’m using 3 sheets in total to cover the 3 currencies that Independent Reserve support.

In my setup I created 3 separate Google Scripts for the 3 different crypto currencies which are feeding into the 3 different sheet-tabs.  I just simply copy and pasted them into separate scripts, saved as a different name and adjusted the variables as shown above and below:

  1. function getDataIRxbt()
  2. function getDataIReth()
  3. function getDataIRbch()

..and the 3x sheet tabs (as per above) are referenced as:

  1. var active = ss.getSheetByName(“XBT Trades“);
  2. var active = ss.getSheetByName(“ETH Trades“);
  3. var active = ss.getSheetByName(“BCH Trades“);

Next, we need to set up the triggers to tell Google sheets how often to retrieve the data.

Snag_5b160d.png

  1. In the Script Editor view, click on the “Current Projects Triggers” button:  Snag_16469e1.png
  2. In the drop down, choose the function you created from above (e.g. getDataIReth)
  3. …and then choose the appropriate timers and frequencies that are approriate.
  4. For example, Time-Driven -> Hour Timer -> Every Hour

Snag_1672b44.png

Tableau-ize it

The initial instinct is to try to get the data shaped (pivoted, calculations etc.) in Google Sheets, however we’ll do what we need in Tableau.  Google Sheets is simply just to hold our data.

Fire up Tableau and use the Google Sheets Connector, a browser window will open asking you for your Google Account to allow Tableau to login and access your Google Sheets.  Select the Sheet, mine is creatively called “Testing”:

Snag_4908a7.png

Click Connect and choose New Union.

Snag_1727017.png

Drag all 3 worksheets into the dialog box and then click on OK.

If you get a series of F1, F2, F3 etc. then check the “Use Data Interpreter” to have Tableau automatically clean the data and assign the first row as column headers.  Pretty cool.

Snag_174d000.png

Tableau’s union makes it easy for us to combine the sheet tabs for easy analysis.  The other great feature is that Tableau creates additional meta-data such as the Sheet Name and Table Name.  So we now know which entries are for Bitcoin, Bitcoin Cash and Ethereum – and the main reason I decided to use 3 separate worksheets.

Snag_4e1263.png

Change “Sheet” to “Currency” and hide “Table Name”.

The data is hourly  as per the Independent Reserve API.

Snag_177d10b.png

Adjust the data types for the date fields as “Date & Time”.  It’s also a good idea to rename some of these fields as well.

Tableau will autodetect the date for you.  If however, it doesn’t pick it up you can use a Table Calculation such as the one I used below:

DATEPARSE("yyyy-MM-dd'T'hh:mm:ss'Z'", [Start Time Stamp])

Snag_394b7ee.png

The DATEPARSE function I’ve used matches the literal structure of the date format being returned by Independent Reserve’s API (below) so that Tableau can translate it.  Depending on your source, yours may vary…

Snag_397ec1f.png

We now have a correctly formatted date column titled “Start Date” that we can work with.

Snag_399378b.png

From here, you can start to build your analysis…

 

Since we’re connected Live to Google Sheets, the data should update regularly so that once you open up your workbook (or better yet, publish it to your Tableau Server or to Tableau Public and embed your credentials) you should get the latest updated results when people view your dashboard.

Final Dashboard shown below (hosted on Tableau Public).

Snag_3a01d69.png

Finally, a quick shout-out to a couple of great resources:

Enjoy…

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s