Tribute to Ravi Shankar RIP

A word cloud tribute to Ravi Shankar built of the Ravi Shankar Wiki discography.

Nelson Mandela Word Cloud generated from Wikipedia

nelson mandela wikipedia free president south africa may – june klerk state general september july age political national congress johannesburg london external nelsonmandela org apartheid peace people first election anti umkhonto sizwe anc sabotage life years prison robben island release february led party negotiations father nobel prize rivonia lockerbie trial family aids foreign ayob one became later children guardian two university support during time campaign freedom gandhi january december black leadership white government group did struggle rights united article august link country court october leaders long april international march november times world day song health public land act order bush john foundation history birthday web independent stories iraq film story walk biography affairs bbc http html retrieved archive pdf htm aspx books isbn news stm index php dead original ancdocs york v t e

Word Cloud generated from Shakespeare's Sonnet 116

South Africa Census 2011

South Africa Census 2011 results were published today and I examined some of the data from official Statssa website.  Follow the link to the official report:  Census 2011 Results.
To illustrate the relative movement in population sizes by provinces I decided to use Bubble charts in MS Excel. Size of bubble reflects the population in each province, the height (Y-axis) reflects the increase in population size within each province and the X-axis reflects the change in Provinces population as contributor to the total population of SA.

KZN (Kwa-Zulu Natal) and EC (Eastern Cape) population stagnation reflects some of the effects of AIDS related deaths whilst Gauteng gains from economic migration. I found the bubble chart to be a striking illustration of the population changes. Using relative measures for the X and Y axis makes the most effective use of Bubble charts such that it models the actual movements and the sizes of the provincial populations. You will notice that I also added in a small map of South Africa's provinces and data legend for quick reference.

Financial Services Survey in Kuwait

As part of ongoing research and development, audata.blogspot.com is conducting this informal and independent survey. Your participation in a  financial survey that looks at the use of, and demand for, financial services, including informal products, will be highly appreciated.  This survey is designed to identify the factors that both impede and facilitate effective access to financial services. Surveys also encompass attitudes, behavior and quality-of-life factors.

Main target of this survey:  Residents of Kuwait including citizens and expats.

As a token of appreciation the 51st survey participant will receive a special gift of a valuable handcrafted silver pendant (provided contact details of an email address is submitted).

• All survey data will be treated in confidence.
• This survey and subsequent analysis is not for profit.
• High level results of this survey will be shared by the 15 November 2012 on the blog:  audata.blogspot.com

Take this 5 min. survey by clicking on the secure link below:

https://www.surveymonkey.com/s/audata_blogspot_com_FSMsurvey1

Geo Mapping of Birthplace of Complete List of Nobel Prize Winners

Incredibly simple and at no cost (using the demo versions) you can create fantastic Geo-maps, location intelligence, heat maps etc.

My first step was to locate some publicly available and some interesting data (free of charge). Downloading the data  was easy courtesy of www.aggdata.com who are data suppliers with a section Free-data-sets section of Aggdata.  Of course I selected the most interesting data set which is Complete list of Nobel Prize winners. which I intended to geo-code and then map. Its a good list of interesting people from all over the globe and would present a nice challenge.

Typically one would expect to have to use a complex and expensive mapping tools to first geo code then map the list Nobel laureates.  However there are simpler and accessible solutions available freely.

Download Mapcite which is availabe as an add-in for MS Excel. You can watch a little  video on Mapcite.   Follow the instructions by website to download and install the add-in.  You must note that with Bing Maps you must have a Windows Live log-in.  Most important is to obtain a BingMaps key to be used in the Mapcite.  Should you have any difficulty, mail me for assistance.

Once Mapcite Add-in was installed, I set about geo-coding the location data of the birthplace of each of the Noble laurates. This is relatively simple using an interactive menu with drop-downs for you to identify the fields to match city,country or street address (if you have it).  Here is link to the MS Excel data file with the geo coding completed. I hope you will enjoy filtering by year, type of prize and country and refreshing the map each time.  In the coming weeks I am hoping to learn how to make this available online!

 Below are some of the maps I generated from the Nobel Laureates birthplace list.  You will note that I color coded the pins for each of the different categories of Nobel Prize. You can find out more about theNobel Prize and its history which includes Barack Obama as a winner.

Compare Historical Price of US New Home Prices in USD versus Gold oz.

With all the currency manipulation by the Fed and the market hysteria one wonders what is the measure of the value of houses.  I used publicly available sources of data to create a Comparison of the Historical Price of US Home Prices in USD versus ounces of Gold.

Links to Data Sources:
Median and Average Sales Prices of New Homes Sold in United States

HISTORICAL GOLD PRICES- 1833 to Present

Link to Data file and chart in MS Excel:
Excel Data file of Median Sales Prices (USD vs. Gold) of New Homes Sold in United States

An amazing view of the value of US New Home Prices evaluated in ounces of gold compared to USD shows how you would pay 646 ounces of gold for a new House in 2001 but less than 30% of that at 181 ounces of gold in 2010.  Whilst in 2001 you would pay $213K for a new house versus $273K in 2010.  It's a striking illustration of not only the housing bubble, the financial crisis, dollar devaluation and gold as a store of value. Clearly there are more questions and uncertainty on the outlook but one should remain optimistic that at least some commodities stand the test of time.

What are your thoughts on historical valuing other assets in ounces of gold? Feel free to use the above data file to do your own comparisons and indexing of the USD and the New house prices.

Yale Economist Robert J. Shiller created an Index of American housing prices based on standard existing houses, not new construction.  See graphic below and take note of the credits for the updates by Steve Barry.

TreeMaps to Monitor Application scorecards

When I started working with monitoring lending products application scorecards in 2003, I grappled with understanding and presenting the complex decision trees.  After some deliberation, paper and pencil I proposed a waterfall style diagram which evolved into different styles as we went along.

As an update or an improvement Tree Maps do seem an ideal step up from the old waterfall style diagrams mainly because they illustrate hierarchy, relative size and scorecard decisions may be color coded. 

Firstly here is a link again to where you can download the Add-in for MS Excel which is called Tree Map.  It is quite straightforward to install using the Help function if necessary.
   Link to Excel Add-in Tree Map
 Secondly here is a sample data file with dummy data from a lending product application scorecard monthly cycle. Sample Application Scorecard Data
I have presented both styles in this data file waterfall style and the TreeMapp style.  
 
Select the data in the Sample file to create a Tree Map as below.  You will notice how I have spent time on setting up the data such that the hiearchy is created from Scoring to Policy to Decision.  Color coding was based upon shades of Green for accept and shades of Red for decline.


Here if the JPEG format output of the TreeMap below.

Useful Tree Maps examples

Here is a link to an interactive Tree Map that I found impressive.

Smart Money Map of the Market

You will notice that it has a drop down box that may be used to change the reference period on share price changes.  Color and size of the rectangle gives a wonderful perspective of the market whilst there is the added dimension of changes over time with color.  Mouse over the rectangles and you get detail on the individual rectangles enabling exploration. A commentary on the side with references to the respective rectangles (stocks) make for an attention grabbing dashboard that entices the user to get involved.

Sharenet does a great HeatMap of the Johannesburg Stock Exchange.
Sharenet Heat Map of JSE (you need Java installed)


Another brilliant use of Tree Maps to illustrate market relative market positioning of Chrysler and othe car manufacturers:
New York Times Article on Chrysler's positioning in the car market 

Using Tree Maps for tracking Personal Consumption Expenditure

Tree Maps are an under-utilised form of data visualization.  I felt compelled to apply another publicly available data-set to a tree map.  So I found suitable the Table 2.3.5U. Personal Consumption Expenditures by Major Type of Product and by Major Function from the US Bureau of Economic Analysis Table 2.3.5U

Data was manipulated in Excel for the Tree Map Excel data file
Expenditure in Q1 2012 is used for the area of blocks which are classified under Services, Goods- Durable and Nondurable. Color of the blocks is derived from the percentage growth in expenditire from Q1 2011 to Q1 2012. Amazingly simple data visualization that you can talk to easily. The color coding gives a heat map of expenditure growth, while the area illustrates the dominant expenditure.

Tree Maps for Data Visualization

A few days ago I read a Peppers and Rogers report  that made intriguing use of a tree map. Tree Maps as a 2-dimensional representation, use rectangles with an area proportional to a specific dimension of the data, while colors can be used to represent another dimension of the data.  So I scratched around and found out more about tree maps and heat maps. I found that an add-in for Excel was available. It downloads quite easily from:  Link to Excel Add-in Tree Map
I am sure your will be able to install the add-in quite easily. Use the Excel Options by clicking on Office menu button from Excel.  Tree Map also made be used independently of Excel.

Finding topical data to test our the TreeMap add-in was easy, I used results of May poll on the Greek elections by the BBC News -Greek election poll

I converted the elections data into a suitable format for Excel Tree Map Add-in. Here is the file

Greek May 2012 Elections Poll Data

Output from the Tree Map add-in can be saved in a variety of formats. Here is my output saved as a JPEG format.

Using relatively simple data set, the visualization is much more exciting. As the data sets get larger and more complex, I expect the emerging patterns will be of more interest. I hope this saves you some time and you share my fascination with the Tree Maps.

And now the results a few days later:

A picture is worth a thousand words.  A colorful graphic depicts the swing in vote quite dramatically.

Greek Election Results - BBC News