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!

10 hours left to get My Excel Dashboard Pro Course

Hi, all. I was debating doing a post on this, but the books say to do it–so I will! (Who am I to second guess the wisdom of online marketing eBooks?) Hopefully, many of you have already signed for my free 3-part video series. (Once you put in your email, you’ll be emailed a page with the three videos. No purchase necessary, though, if I’m being honest, I will encourage it. )

As you might recall, the Excel Dashboard Pro cart opened last week. Today is the final day it’s open. So if you would like to learn how to take the stuff I talk about Dashboards for Excel and see it with visual instruction–with new and updated material–this is your chance.

Click here to purchase my Excel Dashboard Pro course. I’ll also throw in the coupon code, VLOOKUP2017 for a 20 percent discount. There are a limited number of these coupons, and I’ve given them out at my speaking gigs. Once there done, they’re done. So grab yours sooner rather than later.

Thanks to everyone who has already signed up to the free video series and has given us terrific feedback. You rock!

And hopefully, after this launch, I can get back to writing.

FREE 3-part Dashboard Video Series

logo

Hi there, friends. In celebration of my new course for Excel.TV, I’ve released a free 3-part video series around building dashboards. The videos are as follows:

  1. Awesome Benefits of Building Dashboards With Excel
  2. 5 Rookie Dashboard Mistakes That Even the Experts Miss
  3. Excel Dashboard Questions Answered

All you need to do is go to excel.tv/training/ to sign up. Video 1 and 2 have already been released, and we’ve had some awesome comments on both. They say I’m too honest for my own good, but of both videos so far, video 2, 5 Rookie Dashboard Mistakes, is a clear favorite–so definitely check it out. (Plus, you know, it’s free.)

After almost a year of working on this material, I’m incredibly excited to release it. I did a podcast with John Michoaloudis earlier today  (make sure to check out his podcasts), and I told him that for so long I had wanted to write a book! Turns out, I think I might enjoy doing video, too. With videos–and online courses, in general–you have the benefit of community. And sometimes it’s just easier to explain things in and learn from pre-recorded video.

The course will be available for purchase from January 28th, 2017 until February 7th, 2017. And because we’re just opening it up for first-time sales, I’ll offer you, dear readers, a handsome discount.

Hopefully, after the course is over, I can get back to blogging! Yes, I know I said I love video, but I also love blogging. So thanks for your patience.

And, I’d love to see you in my course.

 

 

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