Category Archives: Tips

banner-image

A Dashboard Video Course

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.

Click here to join the wait list!

Or, go directly to Excel.TV/Training

Why? Um, because:

  • you’ll be the first to learn about the course opening, and
  • you’ll also get access to free exclusive video content on the best tips and worst dashboard mistakes.

Also, because, it’s my first video course, and I’d love if you joined me.

Until next time… keep on Excel’n.

 

 

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

 

 

image_thumb.png

Enlarging Form Control Option Buttons

In a previous tip, I had complained about form control option buttons. I had argued they suffer because you can’t change the font size nor the “punch circle.” But then an idea dawned on me that builds off of that previous tip. We could use the same dynamic described in that earlier article and combine it with the camera tool.

Click here to learn more about the Camera tool.

Say Cheese!

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.”

image

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

Click here to read that previous tip to understand it in full.

Let’s take a look at that macro:

Public Sub OptionButtonHandler()
    Dim Index As Integer
    Index = CInt(Replace(Application.Caller, "Button", ""))
    [SelectedIndex].Value = Index
    [SelectedIndex].Calculate
End Sub

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 different worksheet 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. 

Discussion

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.

image

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

download

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!

Download file: Large Option Buttons.xlsm

Also, buy my new book! Makes a great stocking stuffer!