Stream Live Data in Excel with APIs

#

As big data takes over your business, so too will the need for data-driven decision making.

Smaller companies obviously need to predict the market and make quick decisions based on the best available data. But larger companies, who are handling vast amounts of data (in some cases over 100,000s of data points per second) will need to see their data and discover real insights in real time. You might be monitoring stock prices, processing transactions, or even analysing your own website activity.

Check out this live data stream on the Consolidata Data Science Portal. If you continue to browse the Consolidata website, you will notice that the line is in fact animated and updates with new clicks, transitioning between the old data points and the new data points, entering and removing data as required. My example (which I created with D3.js, an online JavaScript library) does needs sprucing up and uses some fairly crude code, but it demonstrates how data can be streamed and quickly visualized for a particular business need.

Untitled OF live data stream

Part of the solution for our live streaming dashboards were down to Big Consultancy, so special thanks goes to them for helping Consolidata implement this.

APIs

APIs make connectivity between applications and websites possible. If you are unfamiliar with the concept of an Application Program Interface (API) then it might help you to first think about them like eating out at a restaurant.

Imagine a restaurant, where the dining area is a system of users and the kitchen as another separate system like a database, where food is stored and processed. Your waiter (or waitress) is the API, taking requests from one system to another, providing feedback to both systems. When you place your order, you are making a call to the kitchen via your waiter, who will check the availability of your order and return a result (your food) in a particular way when the process is complete.

A REST API is used to request data from a particular website’s database. The majority of sites have a API that is freely available to use, including: Twitter, Facebook, Instagram, YouTube and even TFL. For some APIs you will require some authentication, especially if you want to write a program that tweets from your Twitter account automatically for example (you can also send data to databases using API PULL requests too!) For our example, we will use the Bitcoin trading price API since it does not require any special permissions.

20151102 143719

Using Excel to Stream Data

You can even utilise Excel or Power BI to stream data and visualise it in a dashboard for more clearer data insights. Drill down and filter the variables you want to focus on the most and see the data update as frequently as you’d like it to. My worksheet uses a few lines of VBA code to:

  1. Open up a website – you can often call an API by typing a specific URL into your browser. Try the Bitcoin API call by clicking here.
  2. ‘Scraping’ the important information from the page – extract the important bits of information from the JSON output by first extracting the text from the page’s HTML and then parsing it into a readable format for our Excel tables.
  3. Outputting the information into a Excel spreadsheet – create line or bar charts that references this table to build your dashboard.
  4. Tell Excel to repeat the process and replace the old data with new data – for this we will write some VBA code.

Untitled2 OF live data stream

Don’t be intimidated by the VBA code below: the process to get to this point is explained really well in this video by Brett Batie and only takes a few minutes to construct it. For my example I have added a couple of extra features like a loop that executes the code every three seconds, some lines of code that parse JSON into a readable format, and some calculations that ‘cleans’ the data from the JSON output. I also wrote some extra code that sits in Start and Stop buttons.

Here is the code. Its main jobs including:

  • Calling the API (using IE.Navigate).
  • Scraping the JSON from the body of the HTML and splitting it into multiple strings (using Trim and Split).
  • Extracting the useful information (using a substring function called Mid).
  • And then calculating some values based on the leftover output and placing them into some pre-named cell ranges.

 


Dim TimeToRun

Public Sub on_open()
Call BitcoinUpdate
End Sub

Sub BitcoinUpdate()
TimeToRun = Now + TimeValue("00:00:03")
Dim IE As InternetExplorer
Set IE = New InternetExplorer
IE.Navigate "https://btc-e.com/api/2/eur_usd/ticker"
Do
DoEvents
Loop Until IE.ReadyState = READYSTATE_COMPLETE
Dim Doc As HTMLDocument
Set Doc = IE.Document
Dim Body As String
Body = Trim(Doc.getElementsByTagName("body")(0).innerText)
Dim SomeParse As Variant
SomeParse = Split(Body, ",")
Range("high").Value = Mid(SomeParse(0), 19, 5)
Range("low").Value = Mid(SomeParse(1), 7, 5)
Range("avg").Value = Mid(SomeParse(2), 7, 5)
Range("vol").Value = Mid(SomeParse(3), 7, 10)
Range("vol_cur").Value = Mid(SomeParse(4), 11, 11)
Range("last").Value = Mid(SomeParse(5), 8, 6)
Range("buy").Value = Mid(SomeParse(6), 7, 6)
Range("sell").Value = Mid(SomeParse(7), 8, 7)
Calculate
Range("updated").Value = Mid(SomeParse(8), 11, 10) / 86400
Calculate
Range("server_time").Value = Mid(SomeParse(9), 15, 10) / 86400
IE.Quit
Application.OnTime TimeToRun, "LoopSub"
End Sub

Public Sub LoopSub()
Call BitcoinUpdate
End Sub

Sub EndLoop()
 On Error Resume Next
 Application.OnTime TimeToRun, "LoopSub", , False
End Sub

There are a number of interesting and free APIs to call and use, and there are also plenty of similar projects for you to take some inspiration from when developing your own Excel API.

Latest from this author