Excel: Bubble Chart Macro

Apparently the only way to lable XY chart objects sensibly in MS Excel is by writing a macro.  Also the only way to create a Bubble chart without loosing your marbles is also by writing a macro. So I wrote the following macro based on Tom Hollander's blog post.

'The following routine assumes that you have a 5 column data selection with a header column 'Column 5 defines the color category by setting the color value c based on the cell value of column 5 Public Sub CreateMultiSeriesBubbleChart() If (Selection.Columns.Count <> 5 Or Selection.Rows.Count < 3) Then MsgBox "Selection must have 5 columns and at least 2 rows" Exit Sub End If Dim bubbleChart As ChartObject Set bubbleChart = ActiveSheet.ChartObjects.Add(Left:=Selection.Left, Width:=600, Top:=Selection.Top, Height:=400) bubbleChart.Chart.ChartType = xlBubble Dim r As Integer For r = 2 To Selection.Rows.Count Dim c If Selection.Cells(r, 5).Value = "red" Then c = RGB(100, 0, 0) ElseIf Selection.Cells(r, 5).Value = "green" Then c = RGB(0, 100, 0) Else c = RGB(0, 0, 100) End If With bubbleChart.Chart.SeriesCollection.NewSeries .Name = "=" & Selection.Cells(r, 1).Address(External:=True) .XValues = Selection.Cells(r, 2).Address(External:=True) .Values = Selection.Cells(r, 3).Address(External:=True) .BubbleSizes = Selection.Cells(r, 4).Address(External:=True) .Interior.Color = c .ApplyDataLabels .DataLabels.ShowSeriesName = True .DataLabels.ShowValue = False .DataLabels.Position = xlLabelPositionCenter End With Next bubbleChart.Chart.SetElement (msoElementPrimaryCategoryAxisTitleAdjacentToAxis) bubbleChart.Chart.Axes(xlCategory, xlPrimary).AxisTitle.Text = "=" & Selection.Cells(1, 2).Address(External:=True) bubbleChart.Chart.SetElement (msoElementPrimaryValueAxisTitleRotated) bubbleChart.Chart.Axes(xlValue, xlPrimary).AxisTitle.Text = "=" & Selection.Cells(1, 3).Address(External:=True) bubbleChart.Chart.SetElement (msoElementPrimaryCategoryGridLinesMajor) bubbleChart.Chart.Axes(xlCategory).MinimumScale = 0 End Sub

Reading List: Ghost in the Wires

 

This is a late post. I actually read this book last summer. Now a New Year resolution is forcing me to do a quick post on my thoughts.

The book starts of slow and I'm most of the time looking forward to the end, since I loosely know the chase story to be told. But as I continue through the chapters I quickly eat up the next one. This book is well written and an easy read. The author is good at channeling the excitement Mitnick describes during his social engineering hacks.

I've never stolen or swindled anyone in my life and sitting next to Mitnick while he is narrating how he does it really gets the adrenaline running. Thanks for a great read and an exciting story!

 

Posted with Blogsy

Reading List: Steve Jobs

Steve Jobs the currently most read bio written by Walter Isaacson. Nicely written history piece, you're getting a real close up of Jobs and you get a feeling of the reality distortion field just from reading the book. An interesting critical review of the book on Daring Fireball:

Reading List: The Fallible Fiend

The Fallible Fiend by L. Sprague de Camp is a good humoristic Terry Pratchet style book. Filled with beurocracy and logic from his own world, our hero the Demon Zdim, is summoned to the prime plane. The story is quickly pased and with some action. Good witty light read.

Jailbreaking Apple TV and installing Plex

The reason for jailbreaking devices is as always to open the device up for functionality which is possible but made unavailable. Application installation on the Apple TV is interesting from a Plex home cinema setup point of view. The apple TV has great premises to be a great Plex Client.

So this morning I tried to jailbreak my Apple TV. First of all you need to find a micro USB - USB. Impossible to buy in CPH, so better hope you know someone who owns a device using micro USB.

I updated to the iOS to version 4.3 and started on the Seas0nPass jailbreak. I hit the following issues:

• Unzipping IPSW image hanged. Apparently the IPSW file server did not deliver the full file. Just tried to re-download until got the full file.



• creating the ramdisk hanged. Seemed that there was an old virtual windows .bundle file in libraryplugins.. That just needed to be deleted. Machine needed to be rebooted.

After jail breaking I just had to go to the Plex forum where there is posted a great guide to install a Plex client on the apple tv using the Terminal.

Reading list: Inferno

Just finished reading Inferno by Larry Niven & Jerry Pournelle.

The book is about a sci-fi novelist who gets drunk on the side off a skyscraper and falls off the side as he hurls. The fall kills him and he wakes up in Hell where he is guided through Dante's vision of Hell.

The book paints the torments in Dante's circles very well and in a modern light where the main character the sci-fi writer tries to rationalise everything from the 1600 italian vision of Hell.

Good entertaining and fast read.

IPad magazines

I've veen playing around with The iPad for a week now and I have tried to get an overview of which good iPad magazines are available.

I should start by defining 'good':

Let's get it out the way, price. If you're going to charge per issue then I don't want to pay more than 2€ per magazine. You have an infinite space (almost) so get some adds in there. I am used to adds in magazines. News papers creating iPad apps should be either low fee subscription based or totally free, 2€ each day is way too heavy.

Content, do not do not move to a new media if you can't keep up to the previous standard.

Media, feel free to use the new media for user comments. For extra user driven exploration and so on, but do not make it a challenge to read the magazine.

What have I been looking at? Danish newspapers, American magazines, mashup magazines and advertisement collections.

Danish newspaper Ekstrabladet actually have an interesting (at first) model. The first week is free while after
This it follows the model from the rest of the Danish newspapers. 2€ or using your regular subscription number. Price is to high, unless it is an incentive for me to buy a cheaper iPad only subscription. We will have to see when I will be offered that. At least not now and therefore I don't read newspapers on the iPad (sadly).

American tech magazines Wired and Project. Wired is 3€ while the iPad only magazine is only 2€, hey, they got it! Content wise they are much the same, Wired seem to be deeper but they also have a much larger group behind them. I am very impressed with Project doing such a great job at a lower prize point. Well almost a good job, both Wired and Project are still exploring the new media and some times me the user takes the hit. I literally have found places in Project where I can't guet to the next article without going to the navigation menu. Common guys, a magazine where you can't turn the page... Not to good. But I expect to see them do a better and better job. I buy both magazines when they come out.

Mashup magazines. I am currently using Zite and Hitpad, and I love them! Free apps that collects interesting articles and creates a magazine which is constantly changing along time as new stuff is published online. This is the future of magazines. I am looking forward to see the impact on edited magazines from these mashup mags.

The last type of mashup mag I want to mention is a Danish app eTilbudsAvisen collecting the current paper commercials from store websites and makes them Searchable! This is awesome, I can search for offers based on radius from my current position and plan my purchases. I just miss integration with some grocery list web service.

The tablet is here and paper is dying, finally!