“This 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!
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.
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:
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):
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>:
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.
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’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!
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.
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.
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!
I’m writing this from my car on my way to visit family so forgive the brevity and potential spelling errors. Thanks to fellow Apress author, Debra of Contextures.com, for letting me know! You can get my first book in ebook/pdf format for $10.00. Follow this link to get your copy: http://www.apress.com/9781484207352