Sharing files that use SFE formulas

Lee asked me the following : "Several people on my team share Excel files and it seems the only way Sparklines will work is if the file is run from the same directory on each computer.  Is there a way to set up the application so this is not required?"


My initial answer was to use the "Find & Replace" option :
Select all the cells of the sheet containing Sparklines formulas.
Do a "Ctrl+H" and delete all text between "=" and the SFE formula.

Instead, I updated the add-in (at least the XL2007 so far) with the excellent solution suggested by Jan Karel Pieterse here.


On opening a file, the add-in now checks is there is any existing link with a Sparklines.xla or Sparklines.xlam in a cell on the sheet.
If any Sparklines link is found, it is automatically updated and the local add-in is used instead.
Voila !
The add-in is available here. Let me know if it works !

Another Treemap sample file

Another Tremap sample file is available here.

This file shows how you can highlight a given category of sectors in an SfE Treemap and how to select parameters for color coding.

 

Using the stock quote symbol for sectors labels would make it more readable, but it shows that a lot of things can be achieved with this UDF and a few Vlookup().

Treemap. How does it work ?

Inspiration
I did some research on the net and mainly used Jonathan Hodgson's post on codeproject.com, dating from june 2004, that describes the Squarified treemap technique.
The  Squarified treemap algorithm is explained in an academic paper related to Human Computer Interaction (HCI) by Ben Shneiderman of the University of Maryland.
You can find here his excellent explanation that I followed to write this User Defined Function for Excel.
A great Treemap summary post on Juice Analytics' blog also helped me to provide the right features.

Parameters
First of all, remember that your data must be sorted with highest values at the top of the column (I know, this could easily be integrated in the UDF... sometime maybe).

The syntax of the UDF is :
TREEMAP(DataRange, DestinationRange, HeightPixel, WidthPixel, ColorRange, ColorScale, LabelRange, TooltipRange, HighlightPosition).

Only DataRange and DestinationRange are compulsory parameters. Others are optionals.

Lets describe each parameter :
- DataRange : Range of data containing the values used for the Size of the sectors. Must be 1 column by X rows.
As clearly explained on Juice Analytics blog : "Size of the boxes should be a quantity measure. 
The measures should sum up along the hierarchical structure of the data. 
The sum of all the elements in one branch need to sum to the value of the branch as a whole. 
Therefore, you can’t use ratios or dates or any other measure you wouldn’t use in a pie chart."

- DestinationRange : Range of cells that will contain the Treemap or 1 single cell...
If DestinationRange is a single cell, we have 2 cases with the following parameters :

• HeightPixel or WidthPixel are not specified or both  = 0 :

Treemap will be displayed inside this unique cell (you should better make it big, or merge several cells)

• HeightPixel AND WidthPixel are specified (integer number, lets say between 100 and 500):

Treemap will be displayed inside a rectangle of corresponding height and width (in Pixel or points, I am not sure).

This is especially important when comparing 2 Treemaps and you want to have exact proportionnality of their respective total surface (lets say representing my stock portfolio's value before and after the crisis... just a painful example).

- ColorRange : contains the data (number or text !) used to define Color of the individual sectors. If this parameter is left blank, all sectors will be grey... clear and simple (Hi Robert !)
Again Juice Analytics : "Color of the boxes is best suited to a measure of performance or change such as growth over time, average conversion rate, or customer satisfaction."
Color can also be used to identify categories.
ColorRange must have the same size as DataRange : One color per sector, so logically organize your data in identically sized columns.

- ColorScale : No flexibility here. This has to be a range of 2 columns by Y rows, like for the Heatmap() UDF.

The first columns contains intervals or categories, in line with the values of the ColorRange area.
In front of each interval value, the 2nd column contains the RBG color code that will fill the sectors.
Color codes are available here and from Cynthia Brewer's ColorBrewer.
The color scale must be organized so that the lowest value is at the top, like in the screenshot from the previous post.

- LabelRange : Staightforward, range of cells (same size as DataRange again), containing the text for labels to be applyed to each sector.
I tried to maximize the size of the text inside the sector, like for tag-clouds, it emphasises the importance of big sectors in the treemap.
Labels will be displayed in black or in white, always maximizing the contrast against the background sector color.

- ToolTipRange : same as LabeRange, but this text will be displayed in a floating tooltip when hovering the mouse over the treemap.
Defenitely usefull to provide more detailed information than the label could.

- HighlightPosition : Integer number. Basically, it's the rank in the data range of the sector that you want to highlight. A red frame will be displayed around the corresponding sector... long to explain, easy to understand if you have come so far (Hopefuly!)

Regarding the VBA code.
1) it works reasonnably well. I tested it with 1 500 sectors with no trouble and great resolution in the smallest sectors
2) it is damn slow due to numerous iterations and loops, that could certainly be avoided by using collection or dictionnary objects instead of arrays.
3) Unlike C#, Silverlight or Java implementations, this code is not recursive... might be a reason why it's soooo slow.

Actually, this UDF is not a Treemap, rather a Branchmap... A treemap would have a fractal structure, with each sector including at least one embeded treemap, like real  tree-trunk with branches and leaves.

I you are interested to go further with Treemap, specific Treemaping softwares are listed here and more information available from the University of Maryland.

Last and not least.
The permantly-beta version of the add-in is available here (for XL2007 only)
An example file is available here.

Now you can create an interactive version of this nice Treemap from the NY Times... with Excel !

Felices fiestas !

Christmas Treemap !!

Right on time for Xmas delivery : a Treemap UDF joins the SfE toolbar.
Here goes the screenshot, explanations will follow !

Works more or less like the Heatmap() UDF and has contextual tooltip when hovering on a sector.
Ok, not a Sparkline anymore, but a usefull feature for dashboards.

I will come back after some debugging.

Cascade() v2

Just like for Pareto(), I updated the code of Cascade() so the entire chart is displayed with one formula only, located in the top cell of the chart.

The parameters used in this UDf are :

• Points : range of values to be displayed - Mandatory
• LegendRange : range of cells containing the text to be displayed next to each segment - Optional
• Minimum : Value - Specifies the minimum value  of the chart - Optional
• Maximum : Value - Specifies the maximum value of the chart - Optional 
• ColorPositive : RGB color code for positive segments - Optional
• ColorNegative : RGB color code for negative segments - Optional

In this screenshot, I use 3 overlapping Cascade() charts in order to visualize intermediary totals.

• Chart 1 from Net sales to Operating Profit
• Chart 2 from Operating Profit to Profit before tax
• Chart 3 from  Profit before tax to Net profit

Successive charts share a commun value and identical scale (min and max values)

The chart is automatically drawn horizontally or vertically, and aligned with source values, according to the data layout.

I am however still not satisfied with the way the legend is handled.

The beta version of the add-in for XL2007 can be found here.
(again, if the file extension happens to be *.xlam.zip, just remove the ".zip")

9 small charts = 1 clear view

For testing purpose, I created this chart.

It is "built" around the central ScatterChart(), and flanked by StripeChart() to show the X and Y distribution of both series of Red + Black points, including their respective averages (black line).

X and Y scales are ScaleXY(), including a line that highlights the median of  the red + black series.
The median is shown also with the grey area in the lower left corner  : this rectangle contains 50% of all the dots on the chart. 50% of the dots fall to the left of the vertical median line, and 50% fall below the horizontal median line (As commented by Jon Peltier)

It took me 3 or 4 minutes to create this combination of 9 SFE charts... completely meaningless, but good looking at least !

Pareto() v.2

Pareto() is named after the Pareto principle that states that, for many events, roughly 80% of the effects come from 20% of the causes.

- Business management thinker Joseph M. Juran suggested the principle and named it after Italian economist Vilfredo Pareto, who observed in 1906 that 80% of the land in Italy was owned by 20% of the population.
- Pareto principle serves as a baseline for ABC-analysis, widely used in logistics and procurement for the purpose of optimizing stock of goods, as well as costs of keeping.
- Pareto charts commonly display an ordered column chart and a line chart showing the cumulated percentage on top.

I decided to combine both charts in one :

• Each segment represent the individual weight of each item within the total. 
• Segments are stacked one after the other, simulating the cumulated percentage line.  

In order to provide a reference for analysis, 2 lines can be displayed on top of the segments.
In ABC Analysis for example :

• "A class" inventory will typically contain items that account for 80% of total value, or 20% of total items. 
• "B class" inventory will have around 15% of total value, or 30% of total items. 
• "C class" inventory will account for the remaining 5%, or 50% of total items. 

The lines would materialize the A and B threshold.

Unlike the previous version of Pareto(), this release requires only one function in the upper cell (left cell if data is organized in one row x several columns) to display the entire chart.

The reference lines are now continuous and the chart is more readable.

Pareto() UDF uses the following parameters :

• Points : Range of values - Mandatory 
• ColorRange : Range of colors values. Allows one color per segment - Optional 
• Target : Percentage - Red line - Optional 
• Target2 : Percentage - Green line - Optional 
• HighlightPosition : Position - integer value - Optional 
• Legend : True / False - Display cumulated % - Optional 

- The function will identify wether the data is organized in columns or rows and draw the chart accordingly.
- ColorRange is a range containing RGB color codes (provided in the manual), so each individual segment can have a specific color. ColorRange and Points must have identical number of values.
This can be usefull to group items by family or, as show in the picture, to better visualize the A, B and C product groups.

Sparklines for Excel 2007 v. 3.6 beta available here. (Box.net might rename the file into "sparkline.xlam.zip"... so please remove the ".zip")
Demo file for Pareto available here.

PS : To provide a scale to Pareto(), use ScaleXY() ...

ScaleLine and ScaleXY upgrade

Scale Lines UDF now comes in 2 flavors :

• ScaleLine() : used next to LineChart() and Barchart(), with the ticks aligned under each "Position".
• ScaleXY() : Used under or above ScatterChart(), with the tick aligned with given "Values".

The distinction between Position and Value is key here.

Basically, I would say that :

• Scaleline() starts with 1 , that is with the first item, date, or in general Position. Ticks are aligned with the dots of LineChart() and the center of the columns of BarChart().
• ScaleXY() starts with 0, as any xy chart, where X and Y axis cross at 0. Ticks are aligned with the points of ScatterChart().

Both UDF also include 2 additional parameters :

• MarkPosition and MarkLegend for ScaleLine()
• MarkValue and MarkLegend for ScaleXY()

This allows you to draw a vertical line under a given position or value and to add some text next to it.

The space available for the text is half the width of the cell.

The text will automatically be displayed left of the line if the line is in the right half of the cell... look at the image above, you'll get it faster.


A beta version of the add-in for XL2007 is available HERE if you want to give it a try and give me some feedback.
A sample file illustrating this post is available here.
Note that Pareto() and Cascade() now have different behavior (Still have to update the manual...).
Be sure to backup the previous version of SFE before testing.