Hi, there! Long time no see, am I right? Well, most of you have realized that my major focus this last year or so has been developing Excel.TV. And boy, what a I ride it’s been. Excel.TV now boasts tons of content. And, for the first time ever, I’m proud to introduce my first video course!!!!!! (So many exclamations!)
I’m excited to let you all know about Excel Dashboard Pro. This course was a lot of fun to make and so I hope you have fun watching it. It brings together a lot of the stuff I’ve talked about over the years.
Now, listen, audience. You all know how I role. This video course isn’t like others. A lot of Excel classes focus on VBA and macro coding. But my whole shtick in this video series is getting you up to speed quickly. So I’ll be showing you some crazy awesome dashboard techniques without touching a line of code. I’ll show you how you can use formulas to always keep your dashboards showing the most up to date information. And I’ll show you how to develop well, so that your colleagues can figure out what you did.
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’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
Let’s take a look. On the left side, I’ve create a button list. This is just a place to store my option buttons. Notice I’ve placed them within a single cell each—this makes it easy to take a snapshot with the camera tool (since the camera will always only reference one cell). The image below shows how this works. Notice option buttons are all linked to cell B8, which I’ve named “SelectedIndex.”
Again, following my previous tip, we can assign each image created by the camera tool to a macro. That macro would change the Selected Index depending upon which button was selected
Let’s take a look at that macro:
Public Sub OptionButtonHandler()
Dim Index As Integer
Index = CInt(Replace(Application.Caller, "Button", ""))
[SelectedIndex].Value = Index
You maybe wondering about that last line in the subroutine. Camera tool images can be a little funky. In the previous screenshot, you can see that these option buttons images are on the same tab as their form control counterparts. Presumably, you’ll place the option button images on a differentworksheet tab and the original button list will live on a hidden tab. For whatever reason, whenever you place these form control images on another tab, the image won’t always sync with the right value. So making a call to recalculate will ensure that it does. The download file provided at the end of this post demonstrates a setup where the images are on a different tab.
Note: I’ve only noticed this problem in Excel 2010. Excel 2013+ does not seem to have this issue requiring a call to calculate. I’ve included it here so it’s backward compatible with previous versions of Excel. It’s also a bit slower, so feel free to remove it completely if it’s not an issue on your end.
Look, I’m probably not going to use these buttons anytime soon. I’m not saying you shouldn’t, but I don’t have a lot of use for them. I’ll still prefer my method of using shapes. However, the camera tool when combined with my earlier tip does allow you to create functionality not native to Excel. For instance, if you type a new font in one of the cells on the button list, you can even change the format of the radio button in the way it’s presented. In the image below, I’ve deleted the original caption to the option button. In its place I wrote another caption into the cell and picked a cheesy font. Yes, it doesn’t look great, but you can play with the formats and make it look better. This should give you an idea of what’s available.
My hope with this article is that we keep the creative juices flowing and see what other novel ways it can be applied. Again, I probably won’t use this, but hey, maybe you will, and that’s enough for me.
I’m going to Dublin, Ireland
From December 23-30, I’ll be in Dublin, Ireland on holiday with my wife. If you are in the Dublin area and would like to say hi, let me know! Otherwise, this is likely my last blog post before the end of the year, so have happy holiday and terrific new year!