Archive for the ‘Tools’ Category

Excel Blackbelt

Monday, July 28th, 2008

ChristmasStoryPoster

Just last week, I was reminded of the scene from The Christmas Story where the father (Darren McGavin) finally gets his "contest prize" delivered to the house.  There was so much excitement and mystery about what he had won.  Well, I shared that same type of excitement when the big brown truck delivered my copy of Excel Basics to Blackbelt: An Accelerated Guide to Decision Support Designs directly from the publisher.  This book, which reminds of a college text book, just hit the book stores on July 7th.  There are a few Excel books that I wouldn’t do without, but this book is different from the ones in my library.  This book explores the opportunities in Excel for decision support instead of Excel functions.  Throughout my career, I have created many decision support spreadsheets that helped management make more informed decisions.  I think this book will help build upon that résumé and explore new ideas or design methods.

As I get into the book, I will be sure to share my thoughts here on DSA Insights.  Some sections that caught my attention upon a quick flip through the pages were:

4.1 Value of Data Visualization

4.3 Visualizing Constraints

6.0 The Analytics of Optimization

7.0 Complex Optimization

10.0 Visualizing Complex Analytical Dynamics

Excel

 

Note: there is no payment or quid pro quo arrangement for these comments or review.

Share/Save/Bookmark

DSA Insights Adopts WordPress 2.6

Saturday, July 19th, 2008

In the world of technology and software, since when does a release happen ahead of schedule?  Not often.  Well, WordPress has version 2.6 available for download about a month ahead of schedule.  The upgrade was pretty painless except for one minor issue.  For some reason, the maintenance mode plug-in needed to be deleted to run the upgrade from my browser.

This video describes the new features for WordPress 2.6.  One of the best new features is the image caption feature to help with citing sources or describing the image.

As with the last upgrade back in May, please let me know (using this form) if you find any bugs with this blog.  Who knows, there may just be a prize involved!

Related:

Share/Save/Bookmark

Happy Independence Day!

Friday, July 4th, 2008

DC Fireworks 

Happy Independence Day / 4th of July!  Not only is today a great day to celebrate our independence, but it’s also my wife’s birthday.  Happy Birthday!  I hope you take some time and relax from the norm and do something fun!  We will be celebrating the 4th on our new deck with a family cookout and cocktails!

Have a happy and safe 4th and enjoy your long weekend!

Share/Save/Bookmark

Excel Add-in: Export Chart Feature

Thursday, June 12th, 2008

Jon Peliter recently wrote about the differences and advantages between saving charts as .jpg or .png files.  To test Jon’s add-in and see the difference, I included two charts from my last post saved as .png and .jpg respectively.  The Excel add-in is amazing easy to use and is going to save me so much time from having to use an intermediate graphics program.

Click here to get Jon’s export chart add-in (scroll to the bottom of his post).

.PNG

.PNG

.JPG

.JPG 

Can you see a difference?

Related:

Excel 2007 Bible

Excel 2003 Bible

Share/Save/Bookmark

Visualize Convicted Felons in your Neighborhood

Wednesday, June 4th, 2008

*** Update ***

This site, though interesting for locating alleged felons in your neighborhood, appears to be a hoax per the comments section.

Do you want to know which house to skip come trick-or-treating on Halloween?  Check out this site to see where the convicted felons live in your neighborhood.  I entered the address of the house I grew up in and it showed me all of the felons in my old neighborhood (see below).

The website has a pretty funny [scary] introduction and FAQ:

“Safety starts with good information, even if it ends with a loaded .44 caliber pistol. While FelonSpy.com can’t help you get a gun, we can certainly help you figure out which direction to point it in.”  Seriously?

“I want to kill someone, should I use this database as a guideline?

No you shouldn’t, mostly. Killing would make you a criminal as well and there are other sites similar to ours that also track everyone who has even been accused of a crime. If that was okay actions to take, people would be hunting you before you know it, and that doesn’t help anyone. Yelling obscenities isn’t a bad idea though, that’s just a misdemeanor in most places.” 

Best of all, you can use their contact page to submit the name of a criminal in your neighborhood that may not be listed.  Hopefully, there is some validation surrounding these submissions.  This can be a powerful tool if you are looking to buy a house.  I wouldn’t want to live next door to a convicted assailant or murderer, would you?

The site gives: First Name, Last Name, Age and the type of convictions

Check it out here.

Screen Shot

Related:

Intelius - Background checks, reverse phone lookup, etc. (Fee for service)

Familywatchdog - National sex offender registry list

Trulia - Real estate search visualization

Zillow - Real estate search

Share/Save/Bookmark

Graduate Student Visualization

Tuesday, May 13th, 2008

From phdcomics.com, Graduate Student Motivation Level - Visualization.

grades 

I love the steep decline immediately after, "realizing just gave 10-minute talk on 6 years of work."

Share/Save/Bookmark

DSA Insights Ups the Ante - Signed Copy of Smart (Enough) Systems Up For Grabs!

Thursday, May 8th, 2008

Authors James Taylor and Neil Raden are sponsoring a signed copy of their book, Smart (Enough) Systems: How to Deliver Competitive Advantage by Automating Hidden Decisions ($39 value) that will be added to the DSA Insights Post Project prize list.  This is an excellent book, which is featured in the DSA Amazon Store because of its tremendous value.  Find out more about the book here

Don’t wait, send in your blog post now to be eligible for one of the prizes below.  If you don’t know about the DSA Insights Post Project yet, click here to find out more.

Smart Book

The list of available prizes are now:

1st Price (Choice of one)

  • The Visual Display of Quantitative Information, Edward Tufte
  • Show Me the Numbers (Signed Copy), Stephen Few
  • Million Dollar Consulting (TM) Toolkit, Alan Weiss, Ph D.
  • Smart (Enough) Systems (Signed Copy), James Taylor & Neil Raden
  • $25 Amazon Gift Card - If, by some slim chance, you don’t want a signed book 

    2nd Prize

  • $20 Amazon Gift Card

    Coming soon is a list of submissions to-date for the project

  • Share/Save/Bookmark

    Microsoft: Office 14 and Windows 7

    Monday, March 17th, 2008

    Microsoft Office

    It’s been just over a year since the release of Microsoft Office™ 2007 (code named Office 12). The next version, code named Office 14, is rumored to be released in the first half of 2009. With only two years between the releases, how much can really change? Office 2007 had quite a few changes, especially in the look and feel of the user interface. The beta release is expected soon (first half of 2008).

    According to Paul Thurrott’s site, Microsoft is estimating a 20% increase in dollars spent on research and development compared to Office 2007. I doubt they will increase the price from $400 to $480, which would be a 20% increase. In fact, I think you will find a less than stellar revenue stream from the next release. We all know the corporate world drives the sales for Office. In my opinion, without the vast corporate entrenchment, other free products would prevail.

    What I found interesting is that they are going to try to bring online access to some of the Office products, like Excel in the Office 14 version. This enhancement would seem logical when compared to a competitor like Google Spreadsheets, who already provides this feature. They need this enhancement just to stay competitive. It will be interesting to see if they enhance any of the charting tools that Excel has consistently lacked.

    Some free alternatives to Microsoft are: Google Docs & Spreadsheets, Zoho and Open Office.

    My personal opinion is for Microsoft to spend a little more time on research and development and deliver a superior product instead of pushing out a new version every few years. Bugs like, in-cell charting should never have made it to market. Here at Support Analytics, we use the Office 2003 suite every day (mostly Excel, Word, Project, Access, PowerPoint and Outlook).

    google logogvz zohologonew

    Windows™

    Windows™ 7, the next version of Microsoft’s operating system (codename - Vienna ) has been rumored to be out sometime between 2009 and 2011. At first, it was leaked that Windows™ 7 could be out as early as next year. The next rumor was, don’t hold your breath until sometime in the 2011 vicinity. It’s no secret that Windows™ Vista was the second coming of the disastrous Windows™ ME, which I owned a while back. We all know that Windows™ XP will be around until the next operating system is released. I am no OS expert, but I don’t see companies throwing XP by the wayside and loading up Vista. images

    What’s pretty entertaining is that there is a downgrade (a.k.a. revert to an older version) to go from Vista to XP.

    I have never owned a MAC computer in the many years I have been a user dating back to the ’80s when my parents bought a Commodore 64. Ah, 64kbs of memory and the old dot-matrix printer, those were the days.

    I think we are starting to see Apple really capitalize on Vista’s low adoption, which brings me to my dilemma. Is now the time to go with a MAC OS? If it wasn’t for all of the software I own being PC only, I just might. Maybe a new MAC Air laptop would suppress my appetite…

    Commodore 64

    320px-Commodore64

    Share/Save/Bookmark

    Health Insurance Cost Data Visualization

    Wednesday, December 19th, 2007

    In the December 10, 2007 copy of Businessweek, I came across the horizontal bar chart below. Fundamentally, it depicts employee versus employer annual percent change in health insurance costs from 2004 to 2007. As you can quickly see, the horizontal bar chart is lacking in effectiveness unless you tilt your head ninety degrees to your right. So, I added a few of my own that really only took a few minutes to create.

    Businessweek version:

    employeecosts

     

     

     

     

     

     

     

    Here is my Excel version using approximate values.Annual costs Excel

     

     

     

     

     

     

     

     

    Here is my Xcelsius version with approximate values.

    Annual costs Xcelsius 2

     

     

     

     

     

     

     

     

     

    What I found interesting is that when you adjust the size of the graph, you get a more dramatic slope in both lines between 2006 and 2007. I wouldn’t recommend changing the size to maximize your theory or objective. Also, I do not like that the y-axis starts at 2% instead of 0% in the Xcelsius version. (Recommended reading: How to Lie With Statistics)

    Below is my version using Open Office , which is an open-source (free) project that contains most of the products commonly found in the Microsoft Office Suite.

    Annual costs Open Office

     

     

     

     

     

     

     

     

    Finally, below is my version using Google Docs, which had minimal formatting options that I could find. I’m not even going to post the Many-Eyes version due to its lack of formatting. If you want to see it click here .

    Annual costs Google

     

     

     

     

     

     

     

     

    My preference is either the Excel or Xcelsius version, which illustrate the 4-year trend much better than the horizontal bar chart. Both took about the same amount of time to fine-tune. If I was limited on budget and didn’t want to spend any money, I would go with Open Office over Google Docs. There are many more formatting options in Open Office that help to create an effective data visualization. The only drawback is the amount of manipulation it takes to get from the default graph to the ones shown above. Both Excel and Xcelsius default to a horrid looking graph that I wouldn’t recommend using (both shown below).

    Default Excel Chart:Annual costs Default Excel

     

     

     

     

     

     

     

     

    Default Xcelsius Chart:Annual costs Default Xcelsius

     

     

     

     

     

     

     

    Which one(s) do you like the best? Would you suggest another option not found here?

    Share/Save/Bookmark

    Qualitative Comparison

    Tuesday, December 11th, 2007

    As I have stated is previous posts, I am not a big fan of pie charts. This is one use of a derivative of pies that I do consider effective in some cases. Most folks know the pictogram below as Harvey Balls, which can be used to indicate the degree (5 step scale) an item meets some standard or criteria. Even Edward Tufte stated in The Visual Display of Quantitative Information, “This is a particular ingenious mix of table and graphic, portraying a complex set of comparison…” A great use of Harvey Balls is to compare products, cars, software, etc where concrete numbers do not exist.

    Harvey Balls Small

    Consumer Reports created a variation, shown below, of Harvey Balls and popularized them in their magazine. Their version uses a small dot for the excellent rating to further differentiate it from poor if printed in black and white. Also, if done in color, you can quickly pick out those rated in black (fair or poor) from those in red (excellent or very good).

    Additionally, Consumer Reports includes a horizontal bar chart, sorted in descending order, to show the overall score each item received. This puts those items with the best score on top and lowest on the bottom. Another great element they have is listing the critical features with a check mark if that one is present in the item. Anyone using Consumer Reports would probably agree that this rating system is very effective and comprehensive. Now, whether the ratings are fair or accurate is a different topic.

    consumer reports sa8 sm

    Finally, the last figure below shows one item’s (vehicle) rating in each category over time. This gives the reader a view into what were problem areas in the past versus now. Whenever I have researched a new vehicle or significant item purchase, I have utilized Consumer Reports.

    A great application of Harvey Balls could be to compare your company against its competitors in different strategic areas such as: procurement, services, products, technology, customer service, billing, financials, etc.

    balls mcar

     

     

     

     

     

     

     

     

     

    If you are interested in adding Harvey Balls to your “tool kit”, the font can be downloaded here (provided by Sonia Coleman). Just download the font and add it to the font folder. While using PowerPoint, Word, Excel, etc, when you click Insert -> Symbol there should be an option for a font called Pie Charts for Maps, which gives you every option needed to create Harvey Balls on your own. Also, you can modify the color or size just like any other font. I found this method the fastest after downloading the font.

    To learn more about adding fonts to a MAC Operating System click here .

    To learn more about adding fonts to a Windows Operating System click here .

    If you have a more efficient way to create Harvey Balls or additional application, please share it.

    Share/Save/Bookmark