Category Archives: Development

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

 

 

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!

Custom Formats for Dashboards and Spreadsheet Applications

Conditional formats are volatile. If you have too many, you see the effects of this volatility in serious calculations slowdown. As the function guru Charles Williams puts it, “…conditional formats seem to be super-volatile: they are evaluated each time the cell that contains them is repainted on the screen, even in Manual calculation mode….” But even too many non volatile functions can slow things down.

The good news is you have alternatives. For some specific instances you can get away with an Excel feature called Custom Formats, which is faster than conditional formatting and non volatile. It can also replace the IF and other conditional formulas.

However, you’ll deal with some major trade-offs. Custom formats as you’ll see can be confusing. And, because they’re not commonly used, others may not understand the work you’ve completed — and you yourself might even forget since custom format code is obscured away from the spreadsheet in a dialog box.

We’ll talk about those trade-offs and go through a few examples in this article.

Introduction to Custom Formats

Excel comes with an array of built-in custom formats. For instance, in the image below you can see I’ve placed a simple date on the worksheet. The representation of the date as month/date/year is in fact a custom format that Excel applies by default. You can see the format applied to any cell by selecting the cell and selecting More Number Formats from the format dropdown.

image

The image below shows custom code applied to the date on the spreadsheet. Notice initials like d and m stand in for things like day and month. These are part of the custom format codes that can be used to represent dates.

image

Conditional Expressions with Custom Formats

It’s the combination of these letters, numbers, and codes that allow you to make all sorts of customized formats. The codes in the previous image feature no conditional expressions; however, you can write conditions as formatting codes. The basic prototype for conditions is as follows: [Condition 1] ; [Condition 2]; [Else Condition]

Let’s say you apply the following condition code to a cell: [>10] “high”; [>5] “medium”; “low”.  The image below shows this code applied to the cells in column C. Column B simply equals the values in column C. Because the custom format is applied to column C, the values appear as the text we’ve outline in our formatting code. In C3, the value is 15; our custom format code tells Excel to represent this number as  “high.”

image

Notice cell B5 is simply set to be equal to cell C5 with a formula. In fact, all the cells shown in column B simply refer to the adjacent cells in C. The custom format acts as a mask for the actual underlying values. If you were to write =C3+5, the result value would be 20 (assuming you’ve switched the new cell to a General format) even as what’s being shown in C3 is text and not a value. Dates work in the same way: Excel stores dates as discrete whole numbers but they’re represented in a common date format. Custom formats only change how values are represented on a the spreadsheet; they does not affect underlying numerical values.

Custom Formats for Dashboards

Let’s apply what we’ve learned to a dashboard. Let’s say you want to create an alert where a colored dot character, ●, is shown next to over-budget project. The image below shows this effect. You would like a softer orange alert for projects $10,000 over budget and a harsher orange for projects even more over budget than that. The following image shows this effect achieved through custom formats.

image

To create this on your own, you would first figure out which projects are over budget in Column A. The image below shows we can do this easily by subtracting the amount spent from the budgeted amount.

image

Once this calculation is completed we can apply the following custom format to column A:

[Color46][>20000]●;[Color45][>=10000]●;[Color2]

So let’s break this custom format down. First, you might be wondering how to get the dot character. Well, you can copy and paste it from in this blog post. (That’s probably the easiest way!) Alternatively, you can insert it (or any other character) by going to the Insert tab and selecting Symbol. The dot resides in the Geometric Shapes subset. I usually insert the character to a random cell and then copy it from there, pasting it into the custom format as needed.

The next item to point out is the [ColorXX] code. Excel allows you to change the font colors depending upon the conditions satisfied. The numbers 45, 46, and 2 refer to color codes within Excel’s internal colors. Unfortunately, there are only 56 colors available, which is admittedly very constraining.  Click here to see the entire color list. (You might want to bookmark this link so you have the color table handy at all time.) For reference, Colors 46 and 45 are the dark and light orange respectively; Color2 is white.

Once you have the values in Column A as shown above, it’s a simple matter of selecting the values and applying the custom format. This is shown below with some resizing of column A to reflect how I want to see the dots. I’ve also increased the font size. Notice the custom format dropdown box on the ribbon now says “Custom” to reflect the custom format code we’ve created and applied.

image

Discussion

There’s much more to custom formats. For instance, the [>10] “high”; [>5] “medium”; “low” code from above could replace the following nested IF formula =IF(A1 > 10, “high”, IF(A1 > 5, “medium”, “low”)). The greatest advantage to custom formats however is that you can dynamically change how values are represented on the spreadsheet without the weight of additional calculation and volatility. Excel can instantly change how it represents an underlying value much faster than it can recalculate.

But as you can see custom formats are also pretty limited in formatting options; they’re sometimes hard to understand; and how they work isn’t always obvious (their code is hidden away in a dialog box). Indeed, for ninety-five percent of cases, the difference in calculation speed of an IF formula and the dynamic representation of a custom format is nominal.

Still custom formats present many interesting options for developers. For dashboards and models featuring heavy calculation, they prove to be a useful alternative.

Dashboards for Excel

My book Dashboards for Excel uses examples like this and so much more. Buy it today! (The book is out and shipping. Amazon is fixing the listing.)

A method for quickly importing and working with CSV files (part 2)

This is part 2 in which I explain how I solved the problem. Make sure to read the previous blog posts. Taken together, these show I though through and ultimately solved the problem.

Query Tables to the Rescue!

To solve this problem, I stumbled on something I rarely use, and that’s our good ol’ friend Query Tables. I don’t think they go by Query Tables anymore, but that’s what what they’re called in the code, so that’s the terminology I’m going to use here.

You might have used Query Tables as a manual feature before. For instance, to import a CSV file into Excel using Query Tables you would simply select From Text in the Get External Data Group from on the Data tab. See the snapshot below. These are Query Tables.

image

This functionality is really meant to create a connection between Excel and some other data source. The idea being you can manipulate and analyze data using Excel as your business logic and presentation layer.

But I didn’t need any of that here. I never expected the data in the underlying CSV file to change, so a connection that could refresh wasn’t necessary. Furthermore, unless you’re using these connections with PowerPivot, Excel has a bad history linking to other files.

But what Query Tables brings to the table (pun intended) is that they can dump an entire table’s worth of data rather quickly into a worksheet tab in one fell swoop. Try it for yourself manually to see how quick it is.

In fact, I was able to automate the entire process using the code below.

   1: Private Sub UploadFiles(FilePath As String, WorksheetTabName As String)

   2:     On Error Resume Next

   3:

   4:     Dim CurrentWorksheet As Excel.Worksheet

   5:     Set CurrentWorksheet = Worksheets(WorksheetTabName)

   6:

   7:     With CurrentWorksheet.QueryTables.Add(Connection:= _

   8:         "TEXT;" & FilePath _

   9:         , Destination:=CurrentWorksheet.Range("$A$1"))

  10:         .CommandType = 0

  11:         .Name = "DeleteMe"

  12:         .FieldNames = True

  13:         .RefreshOnFileOpen = False

  14:         .RefreshStyle = xlOverwriteCells

  15:         .AdjustColumnWidth = True

  16:         .RefreshPeriod = 0

  17:         .TextFilePlatform = 437

  18:         .TextFileStartRow = 1

  19:         .TextFileParseType = xlDelimited

  20:         .TextFileTextQualifier = xlTextQualifierDoubleQuote

  21:         .TextFileConsecutiveDelimiter = False

  22:         .TextFileCommaDelimiter = True

  23:         .TextFileTrailingMinusNumbers = True

  24:         .Refresh BackgroundQuery:=False

  25:     End With

  26:

  27:     CurrentWorksheet.QueryTables(1).Delete

  28:     ThisWorkbook.Names(WorksheetTabName & "!DeleteMe_1").Delete

  29: End Sub

So let’s go through how this works.

First, the way my program works is that the user supplies a folder containing the CSV files. Then, when they hit a button that says “Run,” I have Excel iterate through the contents of the folder. When I’ve verified a file in the folder is a correct file that will work with my application, I call the UploadFiles method just shown. The FilePath parameter contains to the direct path to the CSV file whose contents I’d like to upload; and the WorksheetTabName parameter refers to the worksheet where’d I’d like the contents to be dumped.

As you can see, on line 8, FilePath is supplied to the connection string when the query table is created. CurrentWorksheet is then used to tell Excel where the data will be placed (line 9).

On line 11, you see I named this query “DeleteMe.” Whenever Excel creates a new Query Table, it also creates a new named range in the background. There’s a default name Excel uses, but you can overwrite it with your own name. In this case, I use “DeleteMe” in place of the default as a reminder that the range shouldn’t exist (if my code errors out, I will see a DeleteMe named range in the name manager). You can see this happening below. It really helps with debugging.

image

The reason there are so many repeats is that each name created by the code above is scoped locally to the sheet in which it was created. Excel also adds that “_1” to the end. I imagine if I had multiple connections on the same sheet it would increment that last number. But maybe someone with more experience could confirm that for me?

In any event, we want to ensure there are no traces of the connection settings. So the BackgroundQuery setting to FALSE on line 24 ensures there are no direct updates from the backend data. Line 27 deletes the QueryTable we just created. Don’t worry this just deletes the connection behind the scenes, but it keeps the data that’s been dumped. Finally, line 28 removes the range that was just created. Since it’s local to the worksheet, we can refer to it as “WorksheetName!DeleteMe_1” and then delete it.

So let’s recap. I’ve created a mechanism to quickly import the data from CSV files. I used the functionality incorporated into Query Tables to bypass other solutions that either were not as good or weren’t feasible. In particular, I was  able to get around two solutions I particularly hate: (1) the iterative algorithm; and (2) copying and pasting from other files.

All told, this solution works great! Right now, Excel will iterate through a folder of about eight CSV files and place the data from each CSV file rather quickly. In fact, it happens so quickly, I had to add a completion msgbox just to ensure the code was actually being run! Neither the iterative or copying/pasting solution can boast these results.

Remember, whenever possible, do more with less.