Querying RESTful webservices into Emacs orgmode tables

In this post, I’ll show you how you can use Emacs and orgmode to query live data from any RESTful webservice, and then use that data in orgmode tables, a really great way to get live table-based calculation and display functionality in your rich orgmode-based documentation.

As an example, we will query live ticker data from the Kraken cryptocurrency exchange, and then use the current trading values of two different cryptocurrencies to calculate a fictitious investor’s position.

There’s a short YouTube video accompanying this post that demonstrates how the whole business works. Read on for more details!

RESTful webservice: Kraken ticker info

Ticker info can be easily and freely pulled from the Kraken API. For example, if you go to https://api.kraken.com/0/public/Ticker?pair=ETHEUR,XBTEUR using your browser, you should see returned JSON looking something like the following extremely redacted example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

{
   "error" : [],
   "result" : {
      "XXBTZEUR" : {
         "c" : [
            "2239.99000",
            "0.01870867"
         ],
         "a" : [
            "2239.99000",
            "3",
            "3.000"
         ],
      },
      "XETHZEUR" : {
         "c" : [
            "196.40030",
            "2.70918471"
         ],
         "a" : [
            "196.89291",
            "6",
            "6.000"
         ],
      }
   }
}

I can specify any number of pairs, but for the sake of exposition we’re going to work with Bitcoin in Euro, and Ethereum in Euro.

Query the ticker webservice using emacs-lisp

Next we’ll write some emacs-lisp code to embed in our orgmode file. In this case, this blog post is actually an orgmode file which I shall later export to wordpress.

The code makes use of TKF’s great request.el package, installable from MELPA. I started with one of the examples on the request.el github, and then used the shiny new let-alist macro to extract the first element of the c key (the last traded price) of the result - PAIR hierarchy.

The eth-eur and btc-eur pairs are stored in the cpb-kraken-etheur and cpb-kraken-xbteur variables respectively.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

(require 'request)

(defun timestamp ()
  (format-time-string "%Y-%m-%dT%H:%M:%S"))

(request
 "https://api.kraken.com/0/public/Ticker"
 :params '(("pair" . "ETHEUR,XBTEUR"))
 :parser 'json-read
 :success (cl-function
           (lambda (&key data &allow-other-keys)
             ;; get out the last successful trade "c"
             (let-alist data
               (setq cpb-kraken-etheur
                     (string-to-number (aref .result.XETHZEUR.c 0)))
               (setq cpb-kraken-xbteur
                     (string-to-number (aref .result.XXBTZEUR.c 0)))
               (setq cpb-kraken-timestamp (timestamp))
               )
             (org-table-iterate-buffer-tables)
             (message "Retrieved Kraken ticker values at %s. ETHEUR: %f XBTEUR: %f"
                      cpb-kraken-timestamp cpb-kraken-etheur cpb-kraken-xbteur)
             )))

The code is embedded in this orgmode document using org-babel, i.e. in an emacs-lisp source code block:

1
2
3

#+BEGIN_SRC emacs-lisp :results none
(pretty lisp (code (that you see above ok?)))
#+END_SRC

Whenever I press C-c C-c with my cursor anywhere over the code, it will retrieve the current values into emacs-lisp variables, and the recalculate the table shown below.

Use the ticker data in a spreadsheet-like table

Next, we construct the orgmode table. If you have never done this with Emacs orgmode, you should try it. The UX for quickly making and maintaining text-mode tables is breathtaking. The table as you see it below is the HTML representation (generated by Emacs and orgmode) of the plaintext table as it lives in this org file:

Everything in column 3 and to the right, and in the last row, is calculated based on the ticker values we have pulled in using the emacs-lisp code above. The table will update whenever I press C-c C-c on the embedded code block, as it ends with (org-table-iterate-buffer-tables), meaning to recalculate all tables in this file, until convergence.

The orgmode formula editor (shortcut C-c ‘), enables you to edit all cell formulas with live highlighting of the references. The editor interface looks like this:

# Field and Range Formulas
@2$3 = '(format "%f" cpb-kraken-etheur)
@2$4 = $2*$3
@2$5 = $4/@II$4;%.2f
@3$3 = '(format "%f" cpb-kraken-xbteur)
@3$4 = $2*$3
@3$5 = $4/@II$4;%.2f
@4$1 = '(format "%s" cpb-kraken-timestamp)
@4$4 = vsum(@I..@II)

As you can see, I pull in the three variables retrieved and calculated by the emacs-lisp code using snippets of lisp. The other formulas are the standard spreadsheet fair with an orgmode flavour. @2$3 for example refers to the third column in the second row.

I can make any number of tables in this same file, depending on values from either the lisp code, or other tables. As per usual, I can export the file to PDF, HTML, ODF or even to a wordpress site, as I’m doing right now.