Did you notice? www.excelgeek.com!

Anyone notice that as of yesterday, I'm now blogging on my own, shiny, new domain: excelgeek.com! I'd like to thank Rick for being so generous and cooperative in transferring this domain to me, and I'd like to let you all know that I plan to test out my new-found-but-still-lousy web design skills to create a "real website". I'll be keeping the blog, too, but I have some plans for other stuff. That's why I'm redirecting from www.excelgeek.com to blog.excelgeek.com for the blog currently.

Well, I'd better get busy on that new website...

Later,

Excel_Geek

Heat Charts, Another Creative In-Cell Charting Technique

I recently completed a $50 Project for a client who wanted to create visual "heat charts" from data in a matrix. What's a "heat chart" you ask? Well, basically, it's a color-coded representation of the relative data values contained in a data set. What?

Ok, so say you've got four columns of data in four rows: The columns represent the four seasons of the year, and the rows represent four different cities around the world. The data in the matrix might be the average midday temperature in each of those cities over each of the four seasons. A heat chart would represent this data showing the hottest (highest) value as bright red, and the coldest (lowest) value as deep blue, and everything in between would fall on that spectrum from red to orange to yellow to green to blue. Now, obviously, a heat chart can be used to represent any data, not just temperatures...

Here's a picture so you can see what the heck I mean:


(Click on the image to open it up larger in a new window.)

How's it work? Well, basically I just use some formulas to convert the values in the matrix into an "index" from 0 to 100, where 0 is the lowest value in the chart (or another user-specified minimum value) and 100 is the highest value in the chart (or another user-specified maximum value), and all other values in the chart fall somewhere, proportionately, in between.

Using the values in this "index" version of the chart, (using VLOOKUP) I reference each the RED, GREEN, and BLUE values for the RGB color representing that index value from a predefined COLOR SPECTRUM table.

Then, with a macro in the ThisWorkbook object, I automatically set the background color (using the RGB property in VBA -- i.e. "cel.Interior.Color = RGB(RED_VALUE, GREEN_VALUE, BLUE_VALUE") every time the sheet is changed.

This together with some general cell formatting, data validation, and some other macros to help automate adding and subtracting rows or columns, and there you have it.

Pretty cool if you ask me.

Later,

Excel_Geek

The Excel_Geek's Super College Bowl Game Pool Sheet

Yesterday a buddy of mine requested a project. He's a college football (pro football, any football, any sports, really) nut, and so he's involved in setting up a bowl game pool. What he wanted was a simple spreadsheet to keep track of everyone's picks, as well as how many points they allocated to those picks. Then as the games are played and winners are known, the sheet could be updated to see how the points standings are going.

He said he looked all over the internet, and there are tons of them out there, but everyone wants too much for them.

Being in the Christmas spirit, I decided to bang one out for him at a $50 discount. That's right...free.

The sheet is fairly straightforward. I've used sheet protection and data validation to ensure users don't "break" the sheet, and I'm using a bunch of conditional formatting to show which games are over, which picks were good ones, etc.

I also included a chart summarizing the current standings at any given point.

The real bonus (to me)is another sheet in the workbook which contains an in-cell tornado chart of each participant's ongoing results. The visual depiction helps you easily understand which picks gave you the most points versus those which did the most damage. Here's an image of those charts:



Merry Christmas, Jason, and merry Christmas to all of you Insiders subscribers who are getting this one, too!

Good Bye Excel_Geek Blog_Mail, Hello Feedblitz

I'm about to do something risky...

...

...

...


Has anyone ever told you that a bird in hand is worth two in the bush?

For some time now I've offered this service called Excel_Geek Blog_Mail. It was simple: sometimes people would rather read my posts in their email than visit my blog, so I started offering to email subscribers my posts manually (actually using the "Email this post to a friend" feature built into Blogger). You see, atom feeds, XML, RSS, etc., tended to be complicated for many people (me too, I'll admit it). More complicated than many readers were willing to deal with simply to get an email (or equivalent) from little old me.

Over time, I've built up quite a following via Excel_Geek Blog_Mail. Some impressive loyalty, if I can compliment myself by complimenting my subscribers.

Now I'm going to change everything. Excel_Geek Blog_Mail is going away. It's simply too much work for me to do every time I post. I'm sorry. I appreciate all of you dearly, but for a solid hour after each post, I'm clicking and sending. Seriously.

But wait, don't leave. I'm now on Feedblitz, which I learned about through Feedburner. All you have to do to receive all of my posts automatically in your email is type your email address into the little box near the top of the left column of my blog and click the "Subscribe me!" button. Done. That's it. That's even easier than it was to sign up for Blog_Mail!

But what about current Blog_Mail subscribers? Herein lies the risk.

I certainly will not take the liberty with my precious subscribers' emails and sign them up for this myself without their permission. So...I'm asking you to visit my blog again (http://excelgeek.blogspot.com) and sign yourself up via Feedblitz if you want to continue to receive Excel_Geek in your email.

Oh, and if you're not already a Blog_Mail subscriber...you can sign up using Feedblitz anyway. ;)

Later,

Excel_Geek

More uses for In-cell Charting - Gantt Charts & Graphic Equalizer Display

I've been playing around for the past few days with other creative ways to utilize the in-cell charting technique I've posted about. Here are two of my current favorites:

1) You can use in-cell charts to create Gantt charts. Let me just say that creating Gantt charts in Excel is hardly breaking any new ground. John Peltier has shown us advanced techniques for doing this using Excel's built in charting feature, and Mr. Excel has shown us how to build Gantt charts using simple conditional formatting. I'm passing this along as just another way to accomplish a similar visual appearance. Below is a small image of what I've done (click on it to open a new window with a larger image):



To create the bars in the chart I use the following formula:

=REPT(" ",D5-G$4)&REPT("█",E5-D5+1)

You can see to accomplish what we need, we first repeat a space (" ") a number of times equal to the start date of the task minus the start of the timeline. Then we repeat ASCII code 219 (hold < ALT > while typing 2 then 1 then 9) a number of times equal to the end date of the task minus the start date of the task + 1. I've also done this one to use conditional formatting to color-code the bars based upon their STATUS. Pretty straightforward, I think.

2) You can use in-cell charting to create graphic displays, such as a graphic equalizer display. Below is a small image of what I've done (click on it to open a new window with a larger image):



In order to accomplish this look, I have used in-cell charting, only this time I've aligned the "text" (the bar symbols we're using) vertically. Also, since I wanted to have the first 5 bars be green, the next 3 be gold, and the last 2 be red, I've actually split the "bars" into three stacked cells, and adjusted the height of those rows accordingly.

In the lowest segment of each "bar" here's the formula I've used:

=IF(D$8>5,REPT("█",5),REPT("█",D$8))

In the middle segment, here's the formula:

=IF(D$8<6,"",IF(D$8-5>3,REPT("█",3),REPT("█",D$8-5)))

In the topmost segment, here's the formula:

=IF(D$8<9,"",IF(D$8-8>2,REPT("█",2),REPT("█",D$8-8)))

Add to this a little standard formatting with a black background, grey border with diagonal borders drawn on the corners for a 3D effect, and you've got yourself a nice looking graphic equalizer display. The possibilities from here are endless. You could feed this display the bar values dynamically from some other application using VBA, etc.

Additionally, this sort of chart could be used just as well as a regular bar chart in which you want to color-code segments.

I'll be packing up these two sample files and sending them off to my Excel_Geek Insiders subscribers, so you can play around with them and get your own great ideas. Please post a comment on this post if you come up with more cool ideas for in-cell charting.

Later,

Excel_Geek

More In-Cell Charting - Handy Little Add-In Called SparkMaker

I guess you could say the Excel_Geek's on a bit of an in-cell charting kick.

A couple weeks back, though, after reading one of my recent posts on in-cell charting, I reader shot me some information about a handy little Excel add-in called SparkMaker from a German company called Bissantz & Company GmbH.

Now, I think I've said it before, but I'll say it again: one of the single most useful applications for in-cell charting is dashboard reporting -- you know, cramming a lot of trend-type data onto a single page view to give users a quick glance at their overall operations. One thing I hadn't figured out how to do using the in-cell charting techniques I've described previously is how to get an entire trend series of data represented in a chart in a single cell. I mean, each of the "bars" you create in that way "lives" in its own cell. How can I get them all into one cell?

Meet SparkMaker. By using its own set of true type fonts and special functions, SparkMaker can do this in a heartbeat. Check out the image below (click it to open it larger in a new window):



I have an entire series of data that is important to me for trend analysis, though on my dashboard report, I really only need to see the current value, but it would be nice to also get an idea as to what the trend has been. Using SparkMaker, I create an in-cell line chart of the whole series of values. So I can see, "Nearly $49 million in revenues...nice...and it looks like we're on the upswing." All in two cells rather than having to derive it from a series of ten data points or using a big, clunky traditional Excel line chart.

Sparkmaker also does pie charts, which I really like for the same reason. How hard is it to get a traditional Excel pie chart small enough to fit nicely into a dashboard report? Hard. But with SparkMaker, it's a simple formula:

=SparkPie(C5)

Change the font size and color as you like, even dynamically, using conditional formatting, like this (click it to open it larger in a new window):



As I'm typing this I'm thinking of other ways I'd use these pie charts...

Now, at 199.95 (USD) for the "Pro" edition and 59.95 (USD) for the "Basic", SparkMaker is not free, but they do offer a try-it-before-you-buy-it free trial, and I recommend giving it a spin.

Later,

Excel_Geek

Advanced Excel + VBA Web Queries & Parsing Tips

Those of you who've been reading my blog for some time know that one of the techniques I utilize quite often in completing projects is directly querying web pages using the "MSXML.ServerXMLHTTP" object.

Example:

Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
       URL = "http://www.somedomain.com"
       objHTTP.Open "GET", URL, False
       objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
       objHTTP.send ("")

This works great for many applications. By simply returning the source HTML code for the web page we've requested...

SourceHTMLText = objHTTP.responseText

...and then using regular expressions to identify specify strings to parse out, you can return specific pieces of text or numeric values from the page.

Say you want to create a spreadsheet containing all of the pertinent data for a specific stock, say Google, from Yahoo Finance. You can simply set the URL you wish to parse to "http://finance.yahoo.com/q?s=goog". Then return the source HTML and parse out the current price, daily change, volume, high, low, etc., returning each of these values to specific cells in the spreadsheet.

Extending this, say you have an entire invenstment portfolio in a spreadsheet and you want to run a script that runs each hour (or day or week or every ten minuntes) and updates each stocks' data. You can simply run multiple queries, making the URL you request a dynamic variable, changing out the ticker symbol in the URL's query string.

But what if you encounter a website that doesn't use URLs with query strings? What if it instead does its database queries "in the background" and then returns the results on a more generic URL?

Example: A website, say http://www.some-fake-gas-price-info-website.com, has a search function that allows visitors to enter a zip code and returns all gas stations in that zip code and their respective fuel prices. Instead of using URLs with query strings that we could access directly -- say, http://www.s-f-g-p-i-w.com?zipcode=12345 -- it returns results on a URL like this: http://www.s-f-g-p-i-w.com/zipcodesearchresults.asp. What do we do? How did we get here?

Take a look at the source HTML for the page on which the search form is located, and look for the code relating specifically to the seach form (where you type in the zip code and click the "search" or "submit" button), which could look something like this:

< form action="zipcodesearch.asp" method="POST" name="zcsearch" id="zcsearch" >
...
< input type="text" size="7" maxlength="5" name="zipcode" value="" >
...
< input type="submit" value="Search" name="btntype" >
...
< / form >

(Note that I aded extra spaces after the "<"s and before the ">"s above to avoid errors when posting this article on this Blogger blog.)

The file specified in the "form action" tag -- "zipcodesearch.asp" -- is the specific page of this website that your zip code is "posted" to in order to generate the http://www.s-f-g-p-i-w.com/zipcodesearchresults.asp page. So what can we do with it?

We can use VBA to post a zip code value to this URL, which will generate the results page, then parse it. It might look something like this:

Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
       URL = "http://www.s-f-g-p-i-w.com/zipcodesearch.asp"
       ZipcodeToPost = "zipcode=12345"
       objHTTP.Open "POST", URL, False
       objHTTP.setRequestHeader "lastCached", Now()
       objHTTP.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
       objHTTP.send ZipcodeToPost

Now you can simply return the source HTML for the resulting page, and parse out the strings containing the data you're after, again using regular expressions.

Easy right?

There are countless ways this sort of technique can be used. Compiling stock price data, weather data, product pricing data, even replicating a portion of a website's underlying database, regardless of what sort of data it contains. I've used this technique to pull down data relating to thousands of U.S. patents, for example. If you know of a website that contains data you're interested in, this technique can likely be used to automatically pull that data down into Excel for you.

Questions? Comments? Concerns? Trying it out but getting stuck? Use my Meembo Me chat feature and shoot me a quick question. And remember, you can always just ask me to do it as a $50 Project.

Later,

Excel_geek