April 2010 Archives

Excel Optical Illusions #11

| 4 Comments | 0 TrackBacks
This week's Excel Optical Illusion is quite different. It's a Stereokinetic illusion.

How's that for an advanced Excel chart?

The depth of field is totally illusory. The actual chart contains a check box to turn the chart animation on and off. 

This is a bubble chart with a tiny bit of VBA thrown in, just for the animation. Here is the VBA in it's entirety:

Public Sub Animate_Figure()

    Dim ws As Worksheet
    Set ws = ActiveWorkbook.Sheets("1")
    With ws
            If Not .[Animate] Then GoTo exit_here
            If .[i] = 1 Then .[i] = .[n] + 1
            .[i] = .[i] - 1
            DoEvents: DoEvents
    End With
    Set ws = Nothing

End Sub

The actual chart is smoother than the animated GIF displayed here. You can adjust the speed of the animation by changing the number of points in an orbit on the chart worksheet.

It looks much better in Excel 2007 and above, but works for all versions. Let me know what you think of this...

Like most of my Optical Illusions, the inspiration comes from Michael Bach's fantastic site.

More to come. Here's the chart:

Reblog this post [with Zemanta]

Excel Optical Illusions #10

| 2 Comments | 0 TrackBacks
This week's Excel optical illusion is called Shaded Diamonds.

Which row of diamonds is the darkest? Which is the lightest?

Are you sure?

Would you believe they are all the same?

This advanced Excel chart is interactive and has a scroll control that spins a diamond revealing the truth. I think this is one of the more interesting charts that I've shared. Can you figure out how it's done?

Investigate here:

Reblog this post [with Zemanta]

Excel Optical Illusions #9

| 5 Comments | 0 TrackBacks
This week's Excel Optical Illusion is called the Hering Illusion.

So are the red squares, square? Click the Background check box to find out?

Like some others the intensity of this illusion depends on your distance from the screen. Get closer or farther and see what happens...

This is not a chart. I'm simply formatting the worksheet for the illusion. More to come.

Reblog this post [with Zemanta]

Excel Location Mapping

| 5 Comments | 0 TrackBacks
I often need to do geospatial location mapping in my Excel projects. If you can acquire the latitude and longitude coordinates, it's pretty easy.


You'll need an equidistant map like this one, in order to plot the coordinates easily. If anyone knows the mathematics needed to transpose coordinates so they can be plotted on the more common map projections where the border between the USA and Canada is curved, please let me know! I have not figured that out yet.

When using an equidistant map, all you need to do is plot the coordinates on an XY (Scatter) chart. Of course you will need to set the horizontal and vertical axes min and max so that the points are drawn close to accurate. This is easy when your data includes a lot of coastal points (just adjust the axes until those points are barely on land). The included map is calibrated quite well.

For this post I chose to imitate Nathan's outstanding Walmart growth movie. While Excel is not up to the task of the Flash animation in his movie, the results are not half bad. In my version you can step through year by year manually with a scroll control, or you can click the Animate button and watch the movie.

I got the data from here. They did not include the lattitude and longitude coordinates so I ran the CSV through batchgeo.com and in a few minutes I had coordinates for 99% of the locations. The rest I acquired manually from Yahoo Maps. Unfortunately, the data source only lists openings through 2006; it would be nice to have the complete and up to date dataset...

The map image is set as the background to the chart's Plot Area. From this project I learned that background images on charts while useful really slow down the charting engine. To demonstrate I've included a check box to toggle the chart background. The charting is significantly faster with it off.

Here's the chart:

Want to Learn How I Do It?

If you are interested in learning the incredible Excel techniques that I showcase on this blog, you should enroll in the Excel Hero Academy.  There is no other course available anywhere that will teach you how to leverage Excel's power like my course does.  As an alumni of the Excel Hero Academy, you will be massively more productive when working with data.

Excel Acrobat PDF Form Filler

| 11 Comments | 0 TrackBacks
I have a love/hate relationship with Adobe Acrobat. I love the product's ubiquity, but it's always frustratingly difficult to work with it. Have you ever had to fill a PDF form from Excel, like this one:

Since tomorrow is tax day in the USA (April 15), I thought it only appropriate to use a PDF form from the zillions available at IRS.gov.

There's quite a few ways to go about this. Most involve using 3rd party controls, DLLs, or automating Acrobat itself, which will only work with the full (and very expensive) version of Acrobat, not the Reader. But there is a very old, very light weight and very free technology from Adobe that will allow you to populate such forms. It's called FDF, which stands for Forms Data Format.

FDF is lightning fast and was developed for servers to send PDF form data back and forth and to enable the creation of complex workflow routing applications. We will be using it in a basic fashion, because all we need to do is fill a master pdf form with a selected record on a worksheet. Adobe has a free toolkit (DLLs and documentation) you can use to create the FDF files, but the format for our purposes is trivial and as a result we can create the FDF files with straight VBA.

If you download the attached ZIP archive, you will find an Excel workbook (.xls) and the IRS form from above. Just enable macros, click on any record from the Clients worksheet and then click the Make FDF button. Acrobat will open with the IRS form populated with the fields from the record you selected!

The macro creates the .FDF file and writes it to the workbook folder, and then shells out to Windows to open that FDF file. Adobe Acrobat or Acrobat Reader respond and open the PDF file that the contents of the FDF file specify. The FDF file also has the field values and with that information Acrobat or Reader does the magic of merging the field values into the specified PDF, automatically.

The format of the FDF file is simple, with a header and footer and a middle section that specifies all of the fields and their values. Here's the contents of the FDF file that resulted in the IRS form (image) above:

1 0 obj<</FDF<</F(f8655.pdf)/Fields 2 0 R>>>>
2 0 obj[
<</T(f1_01(0))/V(Daniel Ferry)>>
<</T(f1_04(0))/V(Excel Hero)>>
<</T(f1_05(0))/V(123 Excel Dr)>>
<</T(f1_07(0))/V(Highwater, KS 43567)>>
<</T(f1_08(0))/V(Daniel Ferry)>>
<</Root 1 0 R>>

You can specify values for as few or as many form fields as you like, and in any order; just put one line for each in the middle section. There are only two types of Acrobat fields, Text and Check Box. The Check Box values can be Yes or No. It's really that simple.

In the above example, f1_01(0) is the name of the first field on the Acrobat form; f1_02(0) is the second and so forth (hey, the IRS named them, not me!).

One hurdle you will need to pass, if you did not author the PDF form yourself, is to figure out the names of the fields. If the form is not protected you can easily do this from Acrobat by going into form editing. If the form is protected, as the IRS forms are, you need a different approach. I've included an old VB routine to list all of the fields and their types in Module 2 of the workbook. You will need Acrobat and not just Reader for it to work. Once you know the names of the Acrobat form fields, it just takes a little trial and error to see which names go with the actual fields you see displayed. Of course, a more descriptive naming convention (from the form's author) would help!

The macro in Module 1 has been coded for simplicity and ease of understanding, not efficiency. The method could be enhanced in any number of ways, to allow for outputting a PDF for every record in batch mode for example, and many other things. As it stands now, you need to click on one record in the Clients worksheet and then click the button - and that will make one merged PDF.

Let me know how you use it. Here's the archive:

Enhanced by Zemanta

Excel Optical Illusions #8

| 14 Comments | 0 TrackBacks
Here's another take on the Eye Jitter illusion.

Does the central large square with the little squares inside float and dance above the background of circles?

Sometime shaking your screen a little enhances the illusion. Seriously!

This was fairly straightforward from the Excel charting perspective. The large square is constructed from error bars. The little squares and the circles are markers on an XY (Scatter) chart. This week's file is an .xls so it should work for everyone, but for some reason the circles look terrible in versions prior to Excel 2007, and the illusion suffers as a result.

I'll have another next week - and it's completely different than all the previous illusions...

Here's the workbook:

--- UPDATE ---
After Lori's fine work I decided to upload a new workbook that shows the named formula approach alluded to in the discussion below. The workbook contains two charts. The first uses Lori's formulas. The second chart uses my suggestions. Neither is more correct, but it should be instructive looking at both. To do so, you will need to use the Name Manager. The charts are not formatted, so the optical illusion is not there. The Named Formula discussion is simply about preparing the data for the chart, and not the formatting that produces the illusion. Here's the workbook:

California Climate

| 19 Comments | 0 TrackBacks
Ever wonder if global warming is for real? I can tell you categorically from my own research of NASA's Goddard Institute for Space Studies' publicly available data of surface station temperature records that... it all depends!

Taking on the entire world was too daunting, so I limited my analysis to the State of California, where I live.

I downloaded the complete monthly data sets for all 114 surface stations in California. Here is what the mean yearly temperature for that entire dataset looks like:


Notice that the trend over the 120 year period shows an increase for the annual mean temperature by just over one degree Celsius. Also notice that the number of active surface stations has dropped dramatically since 1995.

Some of the stations have been in service for quite some time, while others are relatively new. Any real change in climate is a process taking decades or longer. If we filter NASA's data so that the chart is restricted to stations in service for 100 years or longer our data set shrinks to 50 stations, but still scattered throughout California:


Notice that the trend slope has reduced by half, producing only a half a degree Celsius rise over the entire 120 year period. Also notice that the number of stations is much more stable over the 120 years, but still has a tragic reduction in the number of stations active since 1995.

One concern is that cities themselves may be largely responsible for observed temperature increases. If you walk from a grassy park to a tarmac parking lot on a warm day, you can feel the sharp difference in temperature between the two. When applied to cities, this is called the Urban Heat Island effect. Unfortunately, a large percentage of surface stations are located in cities and of those, a large percentage are located in the worst possible part of a city, i.e. black tops, roof tops, next to brick buildings, heat pumps, etc. If we limit our California list to just the stations where NASA records the surrounding population at 30,000 or less, we get a very different picture:


This reduces the number of stations included to just 26, but look what happens to the slope trend. It's actually slightly negative, which technically shows a cooling over the 120 year period for these rural surface stations. Many of the stations lost since 1995 were long term rural stations, and it is truly a travesty that they have been taken offline. It would have been nice to have a larger long-term rural data set to work with.

So is California experiencing global warming? As you can see it depends on what you look at. I'll say it's not exactly cut and dry. It's a shame that the leaders that are responsible for bankrupting our beautiful state never did their own analysis of these data before spending us into oblivion on global warming counter measures.

At a near point in the future I will be releasing the Excel based tool I developed to do this study. It is called StationLab.
Reblog this post [with Zemanta]

Excel Streamgraph

| 5 Comments | 0 TrackBacks
I want to build a Streamgraph in Excel.

I guess I'd call my first attempt a faux-streamgraph. It's certainly interesting looking, but there is a long way to go to get to Lee Byron's inventive visualization.

I'm not actually sure it can be done in Excel. I have some ideas on how to do the wigglies across the x-axis, round the the jagged area plots with intermediary Bezier curving, and better series ordering, but that will have to wait for the next attempt. If I can work it all out, this might be a good candidate for an Addin.

But there are some cool aspects of this first attempt. Moving the mouse around highlights films and displays a visually nice, semi-transparent chart tip for the film's title and the year to date gross. The default Excel chart tip is turned off. Getting this to work properly is inexcusably difficult in Excel. The chart sheet coordinate system and the screen mouse coordinate system are not related and there are a bunch of variables to take into account. But the biggest difficulty (and this is the inexcusable part) is that there is no way to get the LEFT and TOP screen coordinates of a chart in a chart sheet. Without that crucial bit of information, it's impossible to exactly correlate the two coordinate systems to properly have the chart tip track the mouse pointer for example, or to allow for precise drawing on the chart with the mouse.

My hack around this limitation is to accept this shortcoming of Excel and provide a method for the user to calibrate the mouse pointer. When you first open the chart the chart tip will likely not be anywhere near the mouse pointer, but you can move the chart tip left and right with the "A" and "D" keys, and up and down with the "W" and "S" keys on your keyboard.  Once you have it calibrated the way you like, the workbook will remember your settings and the chart tip will track the mouse even when you reopen the workbook the next time. If you adjust your screen resolution or the Excel Zoom setting, you will need to recalibrate. But it's actually kind of fun doing it.

If anyone knows a way to automatically sync the coordinate systems for the mouse and a chart sheet, please let me know. I spent a couple of days researching it and none of the strategies I found worked.

The other interesting aspect of the chart is that it is NOT a STACKED Area chart. For some reason I could not get the stacked version to work properly with the negative and positive values. So I used some formulas on sheet "2" to aggregate the totals and then plot the chart in reverse series order so the smaller values appear in front of the artificially inflated values - a home grown staked chart if you will. There was no science to which series went above the x-axis and which went below. I just alternated every other one.

All in all, I'm quite happy with how the first attempt turned out.

Here's the workbook:

Excel Optical Illusions #7

| 0 Comments | 0 TrackBacks
This week's Excel Optical Illusion is called Eye Jitter.

The central disk appears to float and jitter above the random background. You can control the density of the background by entering a value in the density field, which tells the chart how many columns to use for the series source. Trying different values from 0 to 100 changes the intensity of the illusion.

I'll have another next week.

Reblog this post [with Zemanta]

About this Archive

This page is an archive of entries from April 2010 listed from newest to oldest.

March 2010 is the previous archive.

May 2010 is the next archive.

Find recent content on the main index or look in the archives to find all content.