Normalize Excel tables

Business data is quite often expressed across many dimensions. The profitability equation in a company is very simple in concept, but in practice those revenues come across regions, product lines, products etc., making them in fact multidimensional data.

Users of OLAP systems are very aware of multidimensional data. However, many spreadsheet users are not, so they manage to flatten the data the best they can, using pages or subtotals for dimensions beyond the second one.

Modern spreadsheets have “PivotTable” capabilities, which makes easier to deal with multidimensional data. To feed these pivot tables, the information has to be normalized. This post explains how to normalize data from non-normal representation, using a Visual Basic macro

If you are familiar with OLAP and normalized data, skip to the code. I’ll show samples of non-normalized data, how the same data would look normalized, and why this recipe is useful.

(more…)

Adding a script menu to XCode to reformat code using astyle

Although XCode has its own “code beautifier” built-in function (Format->Re-indent), it didn’t make it for me. It seems it is more tuned up for Objective C, but with a lot of my code being C++, these are among the biggest annoyances:

• The first line after private/public/protected were indented one more tab from what it should be
  
• Some of the Qt macros were also improperly indented
• It didn’t do anything to other elements of code beautification, like spaces between operators, breaking up complex statementes, etc.

So, I reverted to the trusted astyle which I use in Linux. The integration with the XCode IDE works well. Here’s what I did:

1) Installed astyle. Strange enough not Fink or Darwinports have it at this time available on their default trees. So I had to compile it from source and install it on /usr/local/bin

2) On ~/Library/Application Support/Apple/Developer Tools/Scripts/ created a new script with the code shown below. On my particular setup, I had previously copied the scripts from /Library/Application Support/Apple/Developer Tools/Scripts/ into my personal directory, so it has the default structure Apple uses. You may have a different organization. In my case, the script is under 10-User Scripts/40-Code/30-astyle.sh

#! /bin/sh
#
# astyle.sh - Reformats code using astyle
#
# -- PB User Script Info --

# %%%{PBXName=Reformat with astyle}%%%
# %%%{PBXInput=AllText}%%%
# %%%{PBXOutput=ReplaceAllText}%%%
# %%%{PBXKeyEquivalent=}%%%

echo -n "%%%{PBXSelection}%%%"
/usr/local/bin/astyle --style=kr -s4SKNap < &0 2>/dev/null
echo -n "%%%{PBXSelection}%%%"

3) Reloaded the script menus in XCode. All set!

The script is taking all the text of the front window when it is called, running it through astyle and replacing all the text with the result. The settings –style=kr -s4SKNap are my personal preferences. You may want something different. However, don’t forget the 2>/dev/null, otherwise the greeting messages that astyle sends to stderr will mix into your code.

astyle can read a .astylerc on your home directory, which perhaps is nicer than specifying the switches as I did, but I’m lazy.

Happy reformatting!

Linked del.icio.us bookmarks to my pages to avoid entries that are just a bookmark

Wordpress was an easy option to “bookmark” something while browsing the web, since I usually had one browser open on my blog. However, it’s quite annoying to sort through many little blog entries that are no less than a bookmark with minimal extra comment. Now I linked my del.icio.us bookmarks on the right sidebar, getting the best of both worlds: the links are accessible on the blog, and getting rid of the annoying entries.