Search API Connector Documentation

Print

Import Kraken Data to Google Sheets

In this guide, we’ll walk through how to pull public market and cryptocurrency data from the Kraken API directly into Google Sheets, using the API Connector add-on for Sheets. Kraken is an trading exchange for Bitcoin and other cryptocurrency.

The output for Kraken isn’t very well structured for a tabular format like Sheets, and their naming conventions are (in my opinion) unnecessarily cryptic, so you will need to restructure the data after you pull it into Sheets. But with that said, it can still be a nice alternative to more popular crypto APIs like CoinGecko, since it is more reliable and provides a lot of detailed data points.

CONTENTS

BEFORE YOU BEGIN

Click here to install the API Connector add-on from the Google Marketplace.

PART 1: CREATE YOUR KRAKEN API REQUEST URL

We’ll follow the Kraken API documentation to access the latest Bitcoin-USD crypto quotes.

  • API root: https://api.kraken.com/
  • Endpoint: /0/public/Ticker
  • Query Strings: ?pair=xbtusd

Putting it together, we get the full API Request URL:

https://api.kraken.com/0/public/Ticker?pair=xbtusd

Note that these market data endpoints are publicly available (a Kraken account is not required).

PART 2: PULL KRAKEN API DATA INTO SHEETS

We can now enter our values into API Connector and import Kraken API data into Google Sheets.

  1. Open up Google Sheets and click Add-ons > API Connector > Open.
  2. In the Create screen, enter the Request URL we just created
    kraken-img1  
  3. This is a free, public API so we don’t need to enter any headers. Just leave this section blank. We don’t need any authentication either, so just skip that section.
  4. Create a new tab and click ‘Set current’ to use that tab as your data destination.
  5. Under ‘Output Options’, select Compact mode. This isn’t strictly necessary, but makes this report easier to read. More on Report Styles here: Report Styles
    kraken-img2
  6. Name your request and click Run. A moment later you’ll see the latest ticker data for XBT (Bitcoin) and USD in your Google Sheet:
    kraken-img3
  7. The output here is pretty confusing, so we need to refer to the documentation to know what it means:
    kraken-img4Basically, the output contains the currency pair you selected, and then letters representing the fields you see above (a = ask array, b = bid array, c = last trade closed aray, v = volume array, and so on).
  8. Since Kraken’s API field names use individual letters instead of names, we need to rename those fields somehow to make it meaningful. There are 2 potential approaches:
    • Create a summary sheet with proper labels, and then use a VLOOKUP or QUERY function to pull in the data you need.
    • Alternatively, if you’re using the paid version of API Connector (or are still in your trial period), enter the following JMESPath expression: result.*.{ask_price:a[0],ask_whole_lot_volume:a[1],ask_lot_volume:a[2], bid_price:b[0],bid_whole_lot_volume:b[1],ask_lot_volume:b[2], last_trade_price:c[0],last_trade_volume:c[1], volume_today:v[0],volume_last_24_hrs:c[1], volume_weighted_avg_price_today:p[0],volume_weighted_avg_price_last24hrs:p number_trades_today:t[0],number_trades_last24hrs:t[1], low_today:l[0],low_last24hrs:l[1], high_today:h[0],high_last24hrs:h[1], opening_price_today:o[0]}
      This will restructure and rename the data in one go so that it makes a lot more sense.
      kraken-img5

PART 3: MORE EXAMPLE API URLS

Check the documentation for a full list of endpoints, but if you just want to jump in you can try the following URLs, one at a time:

  • Depth (Order Book)
    https://api.kraken.com/0/public/Depth?pair=xbteur&count=4
  • Trades (Time and Sales)
    https://api.kraken.com/0/public/Trades?pair=ltcusd
  • Assets
    https://api.kraken.com/0/public/Assets
Previous Import KoBoToolbox Data to Google Sheets
Next Import LinkedIn Ads Data to Google Sheets

7 thoughts on “Import Kraken Data to Google Sheets”

  1. Can you explain the 6 data outputs that come through with this API and which is the actual current price of the coin? Trying to pull the current price into another sheet and am an amateur at this.

    Reply
    • Hey there, I’m not sure which 6 data outputs you mean, can you please let me know which endpoint you’re using? I just added more information regarding the /ticker endpoint, so maybe that will also help answer your question. In the ticker endpoint, the current price is returned as the first value in the “a” array.

      Reply

Leave a Comment