This is an excellent reference and tutorial on using dashboards within excel.

Kind of makes me wish I had lots of data and an audience for visualizing it.”

5/5 star Amazon review.

Order Now!

 

 

81ord-D3ajLThis book will change how you approach building models and applications in Excel. Jordan forces you to think outside the box, and makes some strong arguments against ‘standard’ spreadsheet development practices.”

Jon Acampora, Excel MVP.
Owner, ExcelCampus.com
Order Now!

Excel Requests – HTTP for Humans

Written by Bjoern Stiel

APIs are the lifeblood of the internet, providing access to bits and bytes that make our personal and professional worlds work.

For example, Quandl provide a vast amount of financial and economic data through their API, while GitHub and StackOverflow let you pull out all sort of user and repo data through their API.

But how do we communicate with these APIs… from Excel?

Working with HTTP with all its different authentication methods and custom headers can be painful. Plus, Excel doesn’t play nicely with Json. Sure, VBA is always an option (VBA-Web seems a very decent project) and Power Query has been a huge leap forward, but neither of them are ideal if you need something quick or programmatic.

As a seasoned Pythonista, I am a big fan of Kenneth Reitz’ Requests package as it massively simplifies HTTP communication in the Python world. This inspired me to write Excel Requests, a dead simple Excel Addin to make Json over HTTP from Excel a walk in the park.

Installation

Installing Excel Requests is fairly simple, download the PowerShell installation script from the docs and run it (right click, Run with PowerShell).

Making your first Request

Start a new Excel (in case you haven’t already done so after the installation) and let’s make a GET request to get the Facebook share price history from Quandl.

 

>>> =REQUESTS.GET("https://www.quandl.com/api/v3/datasets/WIKI/FB.json")
    "https://www.quandl.com/api/v3/datasets/WIKI/FB.json"

Now, this function simply returns the URL which might be a bit surprising. This URL represents the Quandl server response. It is a (nested) Json dictionary that we cannot represent easily in our two dimensional grid system. So instead we return a “handle” to the cached dictionary, which we can access through two other Excel functions.

Let’s see what data the response dictionary contains:

>>> =REQUESTS.DICT.KEYS("https://www.quandl.com/api/v3/datasets/WIKI/FB.json")
    {"Text";"StatusCode";"StatusDescription";"ContentType";"Method";"Json"}

REQUEST.DICT.KEYS gives us the list of dictionary fields. In this case it is the response dictionary with meta information on StatusCode, StatusDescription, ContentType and Method and the actual response in the “Text” field. And, in the Json case, the actual response parsed as… you guessed it, another dictionary.

If we go back to https://www.quandl.com/api/v3/datasets/WIKI/FB.json we can see, that the timeseries data we are after is in dataset/data, so let’s pull it out (you can see from the Json representation that it is an array, so bear in mind to do it as an Excel array formula):

>>> =REQUESTS.DICT.GET("https://www.quandl.com/api/v3/datasets/WIKI/FB.json", "Json/dataset/data")
    {"2016-09-12",125.96;"2016-09-09",129.71; ...}

Authentication

You might often need to pass authentication. If you happen to have a GitHub account, you need to authenticate to pull information about yourself. A quick look at the GitHub API docs reveals that we can use basic authentication whereby the authentication string is simply <username>:<token>:

>>> =REQUESTS.GET("https://api.github.com/user",,,"<username>:<token>")
    https://api.github.com/user

 

 

Custom Headers

If you’d like to add HTTP headers to a request, simply pass in a two-column range to the headers parameter (column one refers to the header name and column two to the header value).

For example, Appveyor uses a (very slightly) different authentication method, usually referred to as Bearer Token Authentication

>>> =REQUESTS.GET("https://ci.appveyor.com/api/projects",,{"Authorization","Bearer <token>"})
    https://ci.appveyor.com/api/projects
 

 

Excel Requests is an open source Addin for Excel written in C#
To get started, check out the docs – http://excel-requests.pathio.com/en/latest/
If you need help, contact me bjoern.stiel@pathio.com

 

Written by Bjoern Stiel
Minimalist, Ex-Investment Banker, Founder of Pathio – Version control for Excel
Email me bjoern.stiel@pathio.com, follow me @bjoernstiel

 

 

computer-chart-800px

Three Common Dashboard Mistakes

I recently contributed to the article on MRC’s Cup of Joe Blog called 7 harmful business dashboard mistakes. I’ll excerpt a version of what I said below, but you should follow the link to read the whole thing. (I’ll use the original version I sent to blog so as not to compete for search keys.) I’ll also add two more. So here they are, three common dashboard mistakes that organizations make.

1. Data Dumping

This is the process of placing everything on a dashboard because you are either unsure of what stakeholders truly want or because you want to make everyone happy. Either way, the result is usually a confusing mess. Dashboards that attempt to be everything to everyone become nothing to anyone.

2. Bad Data Visualization

BI vendors have convinced us flashy, three-dimensional charts will save our businesses but data visualization research suggests they hurt more than help. Stick with the charts you know and avoid the stuff that looks complicated and self-indulgent. Dashboards aren’t marketing material; they don’t need to flashy, glitzy, over-the-top, or manipulative.

3. It’s Not a Technology Problem

So many business start dashboard projects only to spend 80% of their energy on researching and integrating a new BI platform and 20% on the dashboard itself. Those numbers ought to be the other way around. Dashboards are a business case, not a technology project. When we treat their development like a technology project, we face the budget and schedule overruns so familiar to IT departments.

—–

Going’s On…

It’s been a while since I last posted and there’s some interesting stuff going on.

Two-day Excel analytics short course in Cincinnati, Ohio

I’ll be teaching a two-day short course at the University of Cincinnati on analytics with Excel. This will be my third time teaching at the University of Cincinnati center for Business Analytics. These classes are among my favorite to teach, and I always have a great time. If you are in the area, make sure to sign up.

Click here to register!

PASS Business Analytics Conference Speaker

I’ll be speaking at the PASS Business Analytics Conference again this year. Click here to see my profile and register!

Excel Modeling and Analytics Video Course

I’m currently developing an online video course in Excel modeling and analytics. I haven’t really come up with a catchy name yet. But I’m very excited about this project. I expect to be complete sometime this summer, but if book writing is any indication of my ability to stick a schedule, it might be the end of the year!

Anyway, this will be a great course. Stay tuned!

Oh yeah, buy my new book, Dashboards for Excel.

Happy Holidays 2015

OK, so I know a few days ago I said I had written my last blog posted before the end of the holiday. But then I’ve released holiday cards in both 2013 and 2014, so this year should be no different. So I went with a topical Star Wars theme. The text being shown is  an abridged version of the introduction chapter in Dashboards for Excel.

Recording #3

Speaking of which, if you haven’t gotten your copy yet, you should. The book currently has a 4 out-of 5 on Amazon, with the most recent reviews being the most complimentary. (And I swear, I don’t know the people writing these reviews.) Aside from some complaints about the book not being in color, the book is doing well. For what it’s worth, I inquired about color printing with my publisher long before it was printed, but most tech-trade books are not published in color and the few that are already have a strong history of sales and success. (But also: don’t stop asking! If enough people ask for a color imprint, I can make a stronger case to my publisher for the second edition!) Remember the eBook versions are in color too, if that’s your thing. And, of course, I have all the print materials at the ready, so you can always ask me directly if you have trouble understanding anything.

Download File: Happy Holidays 2015.xlsm

image

Remember to hit the download icon from within Google Drive to get the file.

If you want to change the Star-Wars-text for your own application (aren’t there like a million business uses for this?!?!), simply click the worksheet tab called “Backend” and type new text into the textbox.

Thanks to all of you for a wonderful year. Next year will be even better!

Jordan