Use Emacs Org Mode and REST APIs for an up-to-date stock portfolio15285078238697...

A couple of weeks ago, I started to work with Emacs , and I grow fonder of it every day. During a very short time period, it has become my go-to editor for nearly everything I do on my computer, including (but not limited to)

• planning my Todos (in org-mode , to be precise),
• setting up my agenda (org-mode again),
• taking memos during meetings
• writing my (longer) e-mails
• play around with new stuff
• write blog posts (this is the first of these...)

It is difficult to pin down exactly why Emacs is taking over so much. My main influences for starting with EMACS were blogposts, the first describing a general EMACS setup, the second detailing how to implement GTD, i.e. Getting Things Done in Emacs org-mode.

In this post, I will demonsrate the strengths of using Emacs in a very specific use case: Getting up-to-date financial data to use in a spread-sheet including all your financial data. Applications like these are usually provided by online banks themselves, so that I don't show you anything particularly new or shiny . However, the ability to customize every step of the way brings with a number of advantages.

Finding a REST API for Stock Quotes

First, we need to get up-to-date financial data from a REST API. I decided to use Alpha Vantage, a site I first stumbled upon after reading this blog post. The other APIs listed on that page had various issues, either being deprecated in the near future (google, yahoo, stooq) or not having a number of symbols (IEX). The API of Alphavantage is rather easy to understand, even though the naming convention is terrible. Try for instance this link using the demo API key, yielding the following result for the Microsoft stock:

{
    "Meta Data": {
        "1. Information": "Intraday (1min) prices and volumes",
        "2. Symbol": "MSFT",
        "3. Last Refreshed": "2018-05-11 16:00:00",
        "4. Interval": "1min",
        "5. Output Size": "Compact",
        "6. Time Zone": "US/Eastern"
    },
    "Time Series (1min)": {
        "2018-05-11 16:00:00": {
            "1. open": "97.5900",
            "2. high": "97.7300",
            "3. low": "97.5750",
            "4. close": "97.7000",
            "5. volume": "3776187"
        },
        "2018-05-11 15:59:00": {
            "1. open": "97.4800",
            "2. high": "97.5900",
            "3. low": "97.4700",
            "4. close": "97.5900",
            "5. volume": "257615"
        },...

Reading API Requests into Emacs Variables

Having located the data out in the internet was a good first step, but now we need to figure out a way how to use this information. Luckily, most of the work needed for this can be found in various places on the net, for instance in this blog post . I decided to follow their general setup, using the following packages:

• org
• org-babel
• request
• json

The API used in their scenario gave different results with a much cleaner nomenclature. For the Alphavantage API, I had to become a little creative with the eLisp code.

(require 'request)
(require 'json)
(require 'cl)

(request
 "https://www.alphavantage.co/query"
 :params `(("function" . "TIME_SERIES_INTRADAY")
           ("symbol" . "SC0J")
           ("interval" . "1min")
           ("apikey" . "..."))
 :parser 'json-read
 :success (function*
           (lambda (&key data &allow-other-keys)
             (setq open_sc0j (string-to-number (cdr (elt (elt (elt data 1) 1) 1)))))))

The variable open_sc0j is evaluated as follows: From the received json, take the second entry of the second element of the second element. Not very nice, but it works...

I encountered a second difficulty in my portfolio: I have both European stocks (in EUR) and American stocks (traded in USD). In order to keep my balances comparable, I added yet another variable rate_usd_eur , which receives up-to-date exchange rates from USD to EUR from the appropriate query. All in all, my requests to Alphavantage look like this:

(request
 "https://www.alphavantage.co/query"
 :params `(("function" . "TIME_SERIES_INTRADAY")
           ("symbol" . "SC0J")
           ("interval" . "1min")
           ("apikey" . "..."))
 :parser 'json-read
 :success (function*
           (lambda (&key data &allow-other-keys)
             (setq open_sc0j (string-to-number (cdr (elt (elt (elt data 1) 1) 1)))))))


(request
 "https://www.alphavantage.co/query"
 :params `(("function" . "CURRENCY_EXCHANGE_RATE")
           ("from_currency" . "USD")
           ("to_currency" . "EUR")
           ("apikey" . "..."))
 :parser 'json-read
 :success (function*
           (lambda (&key data &allow-other-keys)
             (setq rate_usd_eur (string-to-number (cdr (elt (elt data 0) 5)))))))

(request
 "https://www.alphavantage.co/query"
 :params `(("function" . "TIME_SERIES_INTRADAY")
           ("symbol" . "PG")
           ("interval" . "1min")
           ("apikey" . "..."))
 :parser 'json-read
 :success (function*
           (lambda (&key data &allow-other-keys)
             (org-table-iterate-buffer-tables)
             (setq open_prg (* rate_usd_eur (string-to-number (cdr (elt (elt (elt data 1) 1) 1))))))))

Putting this code inside an org-mode file, bracketing it by code blocks #+BEGIN_SRC emacs-lisp :results none and #+END_SRC , and hitting "C-c C-c" inside it leads to the evaluation of the code block and thus the filling of the variables open_sc0j , rate_usd_eur and open_prg . Since we included the wonderful little function org-table-iterate-buffer-tables , the evaluation also repeats until all the columns in the table below are calculated correctly. This neat little trick I also copied from here.

Setting up a Custom Stock Portfolio Org Table

After these steps, we now set up an org-table to give us a customizable overview of how our stocks are doing. That means setting up an org-table with columns for historic data, such as the date of the buy. Additionally, we use the #+TBLFM function to calculate appropriate performance indicators. An example for such functions:

| Stock         | Symbol | Amt. |    Buy | Date Bought     |  Fees | Dividends | Close |   Gain | Gain Perc | Gain per Day |
|---------------+--------+------+--------+-----------------+-------+-----------+-------+--------+-----------+--------------|
| MSCI ETF      | SC0J   |   10 |  47.11 | [2018-04-16 Mo] | 12.35 |         0 |       |        |           |              |
| ProcterGamble | PG     |    5 | 65.014 | [2015-10-01 Do] | 10.61 |     72.03 |       |        |           |              |
#+TBLFM: $9=(-$4 + $8)*$3 - $6 + $7;%0.3f::$10=100*$9/($4*$3)::$11=$9/(now() - $5)::@2$8='(format "%f" open_sc0j)::@3$7=17.78 + 17.70 + 17.94 + 18.61::@3$8='(format "%f" open_prg)

This code leads to the following result:

Stock Symbol Amt. Buy Date Bought Fees Dividends Close Gain Gain Perc Gain per Day MSCI ETF SC0J 10 47.11 [2018-04-16 Mo] 12.35 0 49.290000 9.450 2.0059435 0.35552367 ProcterGamble PG 5 65.014 [2015-10-01 Do] 10.61 72.03 61.342605 43.063 13.247301 0.045111962

In this example, Gain is first calculated by multiplying -$4 + $8 , i.e. the difference between Buy and (today's) Close by the amount of stocks bought. Additionally, any dividends are added and any fees are subtracted, yielding a "net gain" for the stock. In column Gain per Day , this number is broken down per day since I bought the stock, highlighting my most efficient assets.

There is no limit to what types of functions one can use, and no limit on the sophistication of analysis. And all of this within a very light-weight, easy-to-use interface, without any unnecessary over-head. It is not only convenient, but also educational: while writing this article, I learned a lot about REST APIs and financial data.

Let me conclude this article by picking up a picture from one of the blog posts that got me into Emacs in the first place : Emacs is like a classical steel frame road bike, reliant, robust, nothing fancy but easy to repair. It is the ideal tool to explore the wilderness of the internet. And I can only invite everybody else to come along for the ride.