Wednesday, 28 September 2011

Visualizing Our News Sources

While browsing the other evening, I came across a presently expired visualization challenge on the changing sources of present-day news. It talks of Internet gaining on Television as public’s main news source as more young people cite Internet than TV. You can read about the challenge and the article here

Among many who replied was Ole Østring, who submitted this unique chart/table in response. Here is my reproduction of it in Excel.

Saturday, 24 September 2011

Process Quality Dashboard

Looking back isn't always a bad thing. Pleasant memories aside, it also allows one to review earlier works in the light and knowledge of current experience and to realize how much path we have journeyed on since.

I found a folder in my old computer the other evening and it was password-protected. Utterly curious as to what it might contain, I opened it to find copies of monthly dashboards I made in my earlier organization. Thereafter, it was mostly reliving old memories as I browsed through them all, looking at them and thinking of making them better.

One of my favorite dashboards to make then was the Quality Dashboard. 10+ teams, 10+ performance parameters, 100+ associates, it was some ordeal to get all of them in a series of PowerPoint slides. Some dashboard too! Over 15 slides and in excess of 20 charts!

I copied the data to a fresh Excel worksheet, altered and changed some of the names, and started a dashboard which in 3 simple tables will allow the user to review the overall program, the performance of the teams and finally, the performance of every associate.

Remembering the extra hours we needed to put in afterwards to salvage the names of top performers and outliers for R&R and feedbacks. I designed the table to highlight such names automatically. For a few hours of effort, a lifetime of reduced stress in office.

Annie Lausier's Calendar Approach

As I've raved about in my earlier post, Annie Lausier' Calendar Approach in response's "10 Visualizations for Number of Days to Pay Your Taxes" was so far beyond peoples expectations that it received comments almost bordering on adulation, prompting one to comment, "This was definitely one of my favorites, partially because of the smooth interaction and partially probably because I wasn't expecting it."

Her work is available here. It features a very intuitive approach, and a two-way interaction between the dates and the states. In my attempt to see how far I can progress in two hours of working with the data, I came up with the following. 

I'm happy to leave it at the stage it is now. Clicked on any of the pink-shaded dates colors the states for whom it is the Tax Freedom Day.

Thursday, 15 September 2011

Tax Freedom Chart

I bumped into yet another old but beautiful visualization challenge this evening.'s "10 Visualizations for Number of Days to Pay Your Taxes" may have ran in 2009, but it still features some wonderful approaches to data visualization and I had a great time exploring them in some detail, but not before I had made my version of the chart as shown in the picture below.

While exploring, my attention was immediately drawn towards a very unusual "Calendar Interface" designed by Annie Lausier. It was a standout even among some very unique others. I know it would be mighty difficult to replicate it in Excel. For, even with VBA, it will be a tiresome effort. But, I'm willing to give it a couple of hours and see what I can etch out.

Saturday, 10 September 2011

US Unemployment

Replicating a newspaper chart is always a lot of fun, and it was to be no different with this one. The Sep 8, 2011 article in Guardian includes a  chloropleth map to display US unemployment percentage by state. While data is available for every month between November '08 - July '11, the Guardian drop-down box only allows us to pick four months out of 33!

Chloropleth maps are thematic maps in which areas are shaded or patterned in which shows to the measurement of the statistical variable being displayed on the map, such as population density or per-capita income, and post Excel 2002, are mighty hard to create in Excel. I've saved a Jorge Camoes tutorial for years on how to make one, and now, in my last week of recuperation, I've finally had the time and the patience to attempt and complete one.

The finished map and table looks as below.

Moving the scroll bar changes the colors for the states.  The table to the right categorizes unemployment percentage into  4 groups of Critical, Dangerous, Threatening and Consistent- and lists the names of the states under each group.

Thursday, 8 September 2011

US Census

The other evening I came across the US Census data for 1910-2010. Even though I have worked with population data before, this one was hard to pass by. The data is available in site under "Eyeo Data Visualization Challenge" and it does include some nice flash charts to review, compare and analyze the Decennial Census Data.

I've excluded Puerto Rico from my list of State, Regions and outlying territories as it doesn't belong to any of the four census regions of USA.

For Pick A State, I've used a data validation list. Just didn't want a combo box sitting amid my nicely formatted dynamic labels.

My visualization of the census data looks as shown below.

Tabbed Dashboard

I get to work with Google docs a lot. While its spreadsheet is relatively rudimentary and only offers a basic level of customization, I've really grown to love the various gadgets which helps the user quickly build up Gantt charts, Time series with annotations, Heatmap, Spider charts and Word clouds. I was very charmed by the tabbed feature of the Heatmap and wondered if anyway, that feature could be "visually replicated" in an Excel worksheet without using VBA.

I still had the data from The Pac-Man Dashboard handy, so I started off without any definite schema in mind. Ready to use regular Excel features like conditional-formatting, form element and embedded objects to aid me achieve my goal. Sure enough, in three hours, the following was laid out.

Tabbed Business Overview Dashboard (Region)

Tabbed Business Overview Dashboard (Sales Person)

Tabbed Business Overview Dashboard (Product)

And finally, Tabbed Business Overview Dashboard (Customer)

The Pac-Man Dashboard

Over this weekend, in a random conversation, A friend asked if I can build a dashboard entirely of pie charts. Now that is a serious challenge! Pie-charts do not rank among my favorites, and to build a dashboard entirely of them is definitely not a fantasy of mine.

I looked into my collection of excel spreadsheets for a data table. I found one, but unfortunately do not remember where I've downloaded it from. It contains month-wise performance of 4 sales representatives, for 3 products, across 3 customer types, 4 regions and over 2 years.

After adding couple of columns to the original data and creating some much needed tables, and I was ready to set up my pies; - Oops, pie-charts. In about two hours, my dashboard is done. Can't help but feel all the pie charts give it a "Pac Man" look.

The top section of my "Pac Man" dashboard,

And the lower section of it.

Overall, it has been some time well spent and some valuable lessons learnt. This far and no further with pie charts!

Quality of Living Index 2010

I came across this data-set while reading about different visualization challenges in An intriguing table where 194 countries are ranked and rated in diverse parameters to reveal the best place to live.

Strangely, there is no data for Sweden under the heading Cost of Living. Certainly, the data was either left out or not available for some obscure reason. It just seems to defy belief that Sweden will score a "duck" in that category. That none of the Scandinavian countries made it to the top list is yet another surprise for me. Overall, the "Top 10" places to live, in descending order, are: France, Australia, Switzerland, Germany, New Zealand, Luxembourg, USA, Belgium, Canada and Italy.

Another idle afternoon spent in sweet excel company to create the following.

Some parting words, The scroll-bar and spin-button controls are active-X control elements, not form control elements. Given the inability to specify a dynamic maximum value for the scroll-bar, as is necessitated by the varying number of countries listed under different continents, this change was inevitable. Also, it meant macros needs to be enabled to allow the execution of a single line of code.

USSR: After 20 years

Delighted to be back after three weeks of treatment, care and medication. I rarely fall ill, so this afforded a nice break from my regular activities and also the chance to browse several websites which deals with data-analysis and presentation.

My data-set for this week is from the Guardian Datablog, dated Aug 17th, titled "End of the USSR". The piece is accompanied by an image which uses a map to rate the former soviet provinces on their performance since the collapse.

The data includes yearly figures of GDP, GDP per Capita, Population, Life expectancy and Mortality among other interesting indicators like Intentional homicides, Prison population rate, Prevalence of HIV by gender, Unemployment, Happiness index score, Global peace index and Number of McDonalds!

Given the large difference in figures between Russia and other republics, I was curious to see how the presenter would design the population and the GDP charts for comparison.

I was massively disappointed. The population chart is simply a column chart representing the percentage change of population in 2009 from 1991, and a label to display the current population - very much an easy way out. The GDP chart can be found nowhere, although there is a clustered column chart which displays the GDP per Capita for 3 years out of 20. 1991, 2000 and 2009.

I spent an idle afternoon working on the Population and Life expectancy data, and came up with the following after 2 hours.

US Cancer Stats 2010

I do habitually trawl a lot of websites. Partly because of my interests, partly due to my commitments, and some because I'm concerned. One among the last is The American Cancer Society Inc. A comprehensive website which incorporates A-Z of cancer. From learning about the disease to its treatment and from all relevant data to a chance to volunteer and make a difference, it includes all. The data is from the section Cancer Facts and Figures 2010. I know a similar page exists for the year 2011, but I'd rather wait for the year to be over and the numbers updated, so I can then not only look at the data, but also perform a meaningful comparison against the previous year.

The dataset is taken from page 3 of the PDF file, titled "Estimated new cancer cases and deaths by sex for all sites, US, 2010". Sadly, there can be no correlation between new cases and deaths, so, my main focus was to find the best way possible to represent this data in a dashboard. To make this more challenging, I desisted from using macros, and decided to use Pie-charts!

To keep the dashboard simple, I've used a pair of List Boxes, and the type selected in one will populate the related sub-types in the second.

After about 30 minutes of playing with the data, the dashboard came out as below:

World Education Ranking

This data was published in the Guardian issue of December 7th, 2010. It mentions the  Organisation for Economic Co-operation and Development's comprehensive world education ranking report, ranking each country in three disciplines of Reading, Maths and Science.

The  OCED's idea of using a Bump Chart (learn how to make one here) didn't quiet appeal to me for two reasons. First, given the length of the chart, it not only required scrolling, it demanded it! Secondly, it was a visual overload of colours which scarcely provided any comparative assistance.

Since I gathered this data from an UK newspaper, it only seemed judicious to keep UK as the standard country to compare others against.

I decided to use a Box-and-Whiskers Panel Chart to display this data, plus a couple of dynamic boxes to include relevant data for the comparing countries.

After about an hour of work, I was happy to achieve the following.

The Cricket Graphs

Everyone in India is crazy about cricket. International cricket to be exact. This passion was triggered by a freak World Cup Championship win in 1983, and its dominance in India has continued unabated since. Of late, successful triumphs in T20 World Championship, The ICC Test Rankings, IPL and most recently, the ICC World Cup 2011 has taken the support for the game in India through the roof.

However, this post is not about the ascendancy of cricket in India. It takes to the various types of graphs we see in the telly to help us further analyze the game. Commentators have given them colorful names. The two most notable and common are "The Manhattan", which is the column graph, and "Worm" - which is the line chart.

I didn't had the time to look up official India and Australian sides, neither the details of an official match between them. I knew a 50 over per-side match will be too bulky to create as a dashboard chart, so I zeroed in on a 20 over per-side format.

After an hour of restructuring the data, and working on them, the charts are made as follows:

First, "The Worm"

Then, "The Manhattan"

And finally, a comparative runs per over and wickets chart.

Product Sales Data Visualization

I've had plenty to read these last couple of weeks. I use Google Reader to receive my daily/weekly dose of  reading and I found this fascinating (presently expired) challenge in Chandoo's Blog.

Chandoo has generously provided us with a well-laid out table which I wanted to keep in the dashboard. Option buttons at the end of the rows will allow the user their pick of the product and once clicked, it will shade the row selected and update two charts with the necessary values.

The first chart will plot quantity sold and per unit revenue for each month, while the second will portray the total revenue earned each month for the product and the percentage change in revenue from the previous month. The file contains no macro.

After some playing around, the dashboard is prepared as shown in the picture below.

The African Urban Agglomeration Chart

In the Guardian datablog of 23rd Nov, 2010, a report was published predicting the population of Africa's cities is to rise rapidly. I sat down to see how the historically established african  metropoleis will expand over time and whether any relatively smaller city or an yet unknown settlement will become the focal point of african settlement in the years to come.

The Guardian data can be viewed and downloaded from here. In my excel workbook, I've added additional columns to help me with the location of these cities, and to abbreviate their names as some, though exotic and wonderful, are too expansive to fit it my chart.

Every dataset can be analyzed and represented in several ways using tables, charts or a combination of them. Here the cities can either be represented singularly showing the increase/decrease in population over the years, or be compared against each other. I tried to be innovative and combine my two distinct thoughts into one masterful combination of table and chart.

Apparently, this had to be a dynamic table as well as a dynamic chart, since a static one just wouldn't suffice. Since the data is for 20 cities and five surveys between 2005-2025 at five year intervals, I chose to include option buttons, plus a scroll bar to keep the table length limited and a spin button to determine the sort order.

My final table and chart looks like this:

Wednesday, 7 September 2011

Me and Excel

I never fail to mention myself as an individual who has always been very enthusiastic about Microsoft Excel. My fascination with excel goes back a long way, to the year 1996, to be precise. With few excel guide books available back then, my initial knowledge was mostly acquired using the time-tested "trial-and-error" method, all meticulously noted down in two still carefully preserved, heavy, spiral folders.

Familiarity with Excel grew more intimate once I began my professional journey. Data analysis, Charting, Forecasting, Dashboards exposed me to several propetries of the software of which I was partially aware or completely unaware of. With time, and promotion, my expertise on Excel was narrowed down to two very dynamic and interesting aspects. Charting and VBA.

With time I've watched excel grow to be more robust, and the most preferred among all financial applications. Easily learned, highly customizable, Excel jumped from one version to another with me in tow, keeping a tab on its development, learning the new tricks and remembering the old ones.

The interest continued to increase exponentially with every new version released. Newer things to learn, newer models to adapt to, all made learning Excel an extremely enjoyable adventure - something which I cherish still.

Sunday, 4 September 2011

The poem

In my "Welcome Home!" page, I mentioned the name of this site is a titular translation of the poem "Distantes y nunca tan próximos" by the late great peruvian poet Blanca Varela. It is only proper that I include the original poem in Spanish, and the English translation. 

The Original Poem in Spanish:

distantes y nunca tan próximos
caminamos sobre una tierra que zozobra
acostados en ella o simplemente de pie
sentimos el corcoveo del tiempo

no se trata de llamas temibles
ni de mares ingobernables
en esta tierra la mente y el cuerpo
tienen el mismo vaivén
en el aire que carece de peso
ya que nada es diferente en la memoria
de lo que hemos visto o imaginado

soñamos como vivimos
esperando sin certeza ni ciencia
lo único que sospechamos definitivo
el acorde final en esta vaga música
que nos encierra

a veces la duda
explícita como una flor
con pétalos y señales nos induce
a girar en nuestros ejes
a tener sed
a beber entintado labios imaginados
en el odre más viejo y mortal

lugar oscuro sitio de luz
sería el cielo en el ojo que se mira
en la mano que se cierra
para asirse a sí misma
en lo inmensamente abierto

a la postre como quien cierra un ataúd
o una carta
un rayo de sol
como una espada asomará para cegarnos
y abrir de par en par la oscuridad
como una fruta asombrosamente herida
como una puerta que nada oculta
y sólo guarda lo mismo.

The English Translation:

distant yet never so close
we walk a sinking earth
lying down on her or simply standing
we feel the bucking of time

it's not about fearful flames
nor ungovernable seas
on this earth mind and body
have the same ebb and flow
in the air that lacks weight
since nothing differs in memory
from what we have seen or imagined 
we dream as we live
waiting without certainty or science

the only thing we suspect beyond question
the last chord in this vague music
which envelops us
sometimes doubt
explicit as a flower
persuades us with petals and signs
to swirl on our axis
to thirst
stained with ink to drink imagined lips
from the oldest and most mortal wineskin

the sky would be a dark place
a space of light
in the eye that looks at itself
in the hand that closes
to clutch hold of itself
out in the immense open

when all's said and done like the one who closes the coffin
or a letter
a ray of sunlight
will rise up like a sword to blind us
and gradually open the darkness
like an unexpectedly wounded fruit
like a door which hides nothing
and guards nothing more.

(Poem and Translation Courtesy: