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