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.
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 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.
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:
- 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.
- ‘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.
- Outputting the information into a Excel spreadsheet – create line or bar charts that references this table to build your dashboard.
- Tell Excel to repeat the process and replace the old data with new data – for this we will write some VBA code.
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 TimeToRunPublic Sub on_open() Call BitcoinUpdate End SubSub 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 SubPublic Sub LoopSub() Call BitcoinUpdate End SubSub 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.