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.
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:
Awesome Benefits of Building Dashboards With Excel
5 Rookie Dashboard Mistakes That Even the Experts Miss
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.
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>: