Reading Office 2007 files with Office 2003

If you receive Excel files from external sources (analyst reports, etc.), chances are that you will start encountering the Excel 2007 format. Those files will end in .xlsx, .xlsb or .xlsm instead of the current .xls

Office 2007 has a significant number of advantages, but some of us will be for a while stuck with Office 2003. My employer, for instance, will not upgrade anytime soon.

Microsoft recently made available an extension to read or write those files with our current version of Office. Once you install it, you can seamlessly open the file or save them as you would with a normal Excel file. Follow this link for the download page.

Screencast composition application

I’ve been recently working on some screencasts to demonstrate data analysis techniques.

Two common features I needed for my screencasts, which I’ve seen many screencasters also use/need, are:

• Overlay of a small frame on top of the screencast. For instance, you may want people to see you while you are talking. With computers taking more and more an active role on activities like interviewing, taking down patient records, etc., you may want to highlight some personal interaction tips while using your software. Perhaps you want to show a piece of hardware associated with the screencast. The limit is your imagination.
• Watermark your screencast You may want to prevent others from using your screencast on other sites without the proper credits.

This image shows basically what I needed. By the way, it is actually created with my program.

I started last weekend trying to do it with Quicktime Pro, and found it quite cumbersome. I can record the screen activity fine on the PC, as well as recording myself talking. Here the easy part ends. Then I have to add a video track on QT Pro, guess the offset entering it with numbers, and recompress. No, I wanted something simpler. Searching on the internet, found people using higher end applications, like Final Cut Pro. Not wanting to fork $1,299 for Final Cut Pro or $299 for Final Cut Express, I coded a simple app to achieve what I need. It works nicely, and with that many people doing screencast these days, I think there may be interest for it. When it’s completely finished, I’ll release it as a $9.99 download, and will set up a permanent page for it so people can purchase it. Meanwhile, I’d like to gather some feedback, so if you are a brave soul that can deal with beta software, and have use for the tool, please contact me. The first 25 beta testers will get a full version of the software when it is done. Sorry, no plans to open source the code so far.
(more…)

Presenting time series of market participation

One very common need in almost any industry is to show a given market, its size, the key participant and how all has evolved over the last few years. I have used since a few years a technique I like, and yesterday I read on Juice Analytics’ weblog an article describing basically the same approach. By the way, these guys have an interesting blog.

In their sample chart, there’s only one bar and one line, so many people may ask “why bother?” — it’s not that confusing to have the overlay in the default way Excel will leave the combination chart.

The chart below is a good example of why the technique is powerful. Click to see full size. Disclaimer: as this is an excerpt from a slide, the sample is missing a lot of must-have elements of a good chart, like units, meaningful title, etc.

When showing a market and how it is partitioned across players, there are some useful elements you’d like to show:

• Is it a growing, stable or shrinking overall market?
• What is our share? Is it growing?
• How are competitors doing? Are there changes that merit closer understanding?

Most common charts I’ve seen people use for the problem are:

• One pie chart with the last year figures. Ok, but you miss the interesting historical perspective
• Two pie charts side by side. That one hurts my eyes! There are many reasons it’s a bad representation. People are not intuitively good at comparing areas. Requires a lot of effort to follow where in each chart is each player, and how is it doing relatively to competition… enough said!
• Stacked column charts. This is a reasonable approach. Shows the trends nicely. It is hard for people to judge the relative percentage of the bar segments, so sometimes people end up with the percentages annotated in each segment, and in that case the chart is very busy
• Lines showing the share. Basically, the bottom part of the chart shown. It is a nice approach, you can follow what happened to the market players along time. In stable markets, may be all what is needed. The chart may be misleading though in growing markets because it loses the overall market size.

The combination of a bar chart to show the overall picture, plus lines to show the individual player trends has the best mix of good features I’ve found. I’d love to hear suggestions on other ways to approach the problem. It is not easy to spot growth in absolute volume for a given player, but in this is not usually a major drawback. If you are happy with your 3% growth and the market has been growing at 45% you’ll be soon out of business. Talk about charting failure

Where is the source file of that data?

On my job I usually have to present data-rich slides that are the result of analysis on sets of data. One of my pet peeves on this types of slides is the omission of the data source. As I always point out to analysts in training, *please* do yourself a favor: put the footnote with the source. You’ll be happy when 5 months later you are asked to support some results of your analysis.

That answers the question “What is the source of your data?”. More often than not, when you have to support your analysis, its also handy to know “Where is the source of my data?”. My recommendation decks are usually in Powerpoint, and I only sparringly use Office’s embedded file feature, so what is on the slide is just a picture of the spreadsheet, chart or table.

My source notes usually look as shown below:

The way I recommend analysts in training to do it is through the CELL function in Excel.

=CELL("filename")

The formula above will print the whole path and filename of the file. If you want only the filename, you can use

MID(LEFT(CELL("filename",A1), FIND("]", CELL("filename", A1))- 1), FIND("[",CELL("filename", A1))+1, 255)

Complex validation in Rails through plugins

I’m writing a portfolio management application in Rails where complex validations are required between fields of instances. The MVC paradigm of Rails is very good to encapsulate all the business logic of the application in a very readable way.

I found an article here describing how to add validations to a model through plugins, making them reusable and semantically clear.

Even if you don’t have in mind reusability, which was my case, the added clarity if this method is worth the additional work of creating a plugin.

Complete information on how to write plugins can be found here

Easy creation of tornado charts in Excel – 5 steps, no add-ins

UPDATE: A newer post is available, with Excel 2007 instructions and a screencast

Tornado diagrams are a classic tool of sensitivity analysis for decision analysis. They are not used as frequently as you would expect, given how clearly they help showing the impact of different variables on a geven outcome. As suggested by Ted Eschenbach on a recent article of Engineering Economist, (issue of 06/22/2006), perhaps this is due to difficulties in constructing them.

Sensitivity analysis is needed to address the inherent uncertainty in engineering economy applications because (1) time horizons are measured in years or decades and (2) much economic analysis is done at the feasibility and preliminary design stages. This is often shown using relative sensitivity analysis charts or spiderplots, which have a long and rich history in practice and texts (they are described in 10 of 18 texts reviewed, including Blank and Tarquin (2002), Canada et al. (1996), Eschenbach (2003), Lang and Merino (1993), Park (2002, 2004), Sullivan et al. (2003), Thuesen and Fabrycky (2001), White et al. (1998), Young (1993). Tornado diagrams are not new, but they have not been used nearly as frequently. Only one of the 18 texts included a tornado diagram (Eschenbach, 2003)–

Searching Google on how to make tornado charts, you’ll get many results, most of them requiring you to download an add-in. Keep reading to see how you can create tornado charts with plain Excel in just 5 steps… very easy and straightforward!!

This is a tornado chart created with the method described below:

(more…)