Jan 252017

Conditional formatting is a great tool, but after a spreadsheet has a lot of edits, the number of conditional rules spirals out of control.

To solve this I wrote a short VBA macro, which propagates conditional formatting (and only conditional formatting) from the top-most cell in each column. It’s a simplistic implementation, with the following restrictions:

  • It replicates the formatting column-by-column, copying the conditional formatting from the first (top-most) cell which has some. Make sure that the rules you want propagated are topmost in the list and include that topmost cell before running.
  • Corollary: It doesn’t handle multi-column formatting.

Paste the following code into a new module:

Option Explicit
Option Compare Text
Sub Test(xls As String)
    ' Open the workbook with conditional formatting.
    '   Test wookbookname
    ' in the immediate window
    ' this will merge the conditional formatting on the first worksheet of that book
    Dim wb As Workbook
    Set wb = Workbooks(xls)
    Dim ws As Worksheet
    Set ws = wb.Worksheets(1)
    MergeConditional ws
End Sub
Public Sub MergeConditional(ws As Worksheet)
    On Error GoTo FAIL
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Dim lur As Long
    lur = LastUsedRow(ws)
    Dim luc As Long
    luc = LastUsedCol(ws)
    Dim col As Integer
    Dim row As Long
    For col = 1 To luc
        Dim found As Boolean
        found = False
        Dim source As Range
        For row = 1 To lur
            Set source = ws.Cells(row, col)
            If source.FormatConditions.Count > 0 Then
                found = True
                Exit For
            End If
        Next row
        If found And row < lur Then
            ws.Range(ws.Cells(row + 1, col), ws.Cells(1048576, col)).FormatConditions.Delete
            Dim target As Range
            Set target = ws.Range(ws.Cells(row, col), ws.Cells(lur, col))
            Dim i As Integer
            For i = 1 To source.FormatConditions.Count
                source.FormatConditions(i).ModifyAppliesToRange target
            Next i
        End If ' found
    Next col
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub
Private Function LastUsedCol(ws As Worksheet) As Long
    On Error Resume Next
    LastUsedCol = 1
    LastUsedCol = ws.Cells.Find("*", SearchOrder:=xlByColumns, LookIn:=xlValues, SearchDirection:=xlPrevious).Column
End Function
Private Function LastUsedRow(ws As Worksheet) As Long
    On Error Resume Next
    LastUsedRow = 1
    LastUsedRow = ws.Cells.Find("*", SearchOrder:=xlByRows, LookIn:=xlValues, SearchDirection:=xlPrevious).row
End Function
Aug 312016

According to Wikipedia, as of 2010, some 1’200’000 people died in traffic-related accidents. Here’s how that looks geographically:


The take-away is fairly simple: you’re better-off driving in (interesting how these same countries come back every time): Norway, Sweden, Denmark, Switzerland and, unexpectedly, Israel. The rest of Europe, if you don’t stray too far east, is relatively harmless, along with Canada and Australia, which are only twice as dangerous as the ‘model’ countries. In the USA, your chances of dying are quadrupled. From there, the multiplier is on a bender: Mexico 5, Bolivia 6, Kenya 7, Iran 8, Ecuador 9, Oman 10, Nigeria 11, Thailand 12 and the winner if Erithrea, where you are 16 times more likely to die on the road.

Why did I go to all this trouble for victims of car accidents, you may well ask? Because it represents about 1’200’000 who die every year for no good reason, yet we find it perfectly acceptable. In contrast we wallow in grief for one death:

A while back, Pakistan hung Shafqat Hussain, found guilty of having killed a 7-year-old child, and the whole twitosphere is in uproar because he was too young to be executed. This suits journalists, who can use said tweets to justify their perception of ‘public opinion’. A false premise that even a tapeworm could understand, because tweets are not votes, carefully counted in a ballot-box; they are tweets, and as their name implies, they are mindless drivel, most likely generated by a computer.

What’s interesting is that nobody questions that Shafqat did actually kill a 7-year-old, but nobody, especially journalists, gives a shit because the life of an innocent young boy is much less news-grabbing than the sob-story of a supposedly-persecuted adolescent. I’m not sure whose testicles I’d prefer to crush in a vice, Shafqat’s or the journalists. From a Darwinian perspective, all those testicles seems the most sensible approach.

You’re not reading/listening anymore because I questioned the reality of the twittosphere? Your single-digit IQ brings forth my greatest compassion.

May 292016

I was quite surprised how much the choice of anti-aliaser affects a render. Here I’ve rendered a scene with all the 3DS anti-aliasers, to compare how they fare. The scene is a ring (which you might wear on a finger), using Neil Blevins beautiful gold material. The subject is 5 intersecting toruses, scanline renderer, with an HDR skylight based on Desk_Sm_Blurry.hdr. I discovered that all the anti-aliasers are very susceptible to the radiosity quality, there is a huge difference in the artifacts between the 85% default and 99% radiosity that I used.

Area, nice at first glance, but a bit cartoony


Blend, not suitable for this kind of texture


Blackman, a little blurry but quite realistic


Catmull-Rom. Convincing, but a little too much


Cook Variable. Very crisp in the highlights, a bit blurred elsewhere


Cubic. Poor unlit areas


Mitchell-Natravali. Crisp compromise, my favourite


Quadratic. A little surrealist and over-smoothed


Sharp Quadratic. A little better than the basic quadratic, but still too smoothed


Apr 292016

Disclaimer: I have never flown a model aircraft in my life. I unboxed RF7.5 at 23:30 and crashed my first trainer at 23:50.

They were an instructive 20 minutes and I’ll detail them, hopefully you might find it useful.

RF is a trivial install; pop in the DVD and run setup.exe. The only thing you might like to change is the install directory; the RF install is 6.5GB, if you’re tight on space on your C: drive you might like to put it on another disk. Otherwise, just be patient, it takes several minutes but couldn’t be easier.

If you’re in America, skip this paragraph. We Europeans will launch RF and get “Invalid or corrupt version of WMVCORE.DLL”. This is par for the course, ‘N’ versions of Windows don’t have the correct components of Windows Media Player. With a little sleight of hand you’ll find the reason for your OS and from there (for me Windows 7 N or KN editions) get the Media Feature Pack. Once you’ve installed that, re-booted your PC and uttered a spray of profanities, you’re ready to start flying. Double-click RF on the desktop and you should see something like this:


This might not seem very impressive. Well, take off and you’ll see this:


I played with my first flight simulator on an IBM PC with 2 5.5″ disks back in 1988 and I’ve seen many in-between. Here I was gob-stopped by the simulation; the specular lighting and level of detail is truly astonishing and the aircraft behaviour is incredibly realistic.

Now, a purist 3D-modeller might argue that the perspective of the fence-posts doesn’t change with the viewing angle, nonetheless when you drive the plane gently into the fence, you hear the propeller tapping the wires:


The simulation could hardly be more perfect.

I’ll be back with more when I’ve got a bit more experience with what appears to be a very polished piece of software.

Mar 232016

Another few dozen dead in Brussels, apparently the work of islamic suicide bombers. Almost customary news-of-the-day, followed by the customary heart-rending of politicians, eager to gain media-time for their own self-glorification. After ‘nous sommes Charlie’, we have ‘nous sommes Paris’ and now, predictably, ‘nous sommes Bruxelles’. Trite, self-serving and an utter waste of media bandwidth.

The drivel served up to us, ad nauseum, wallows between indignation and glib sound-bites about tolerance and the necessity of doing something for the down-trodden poor. These poor souls apparently are (almost) justified in resorting to violence because they can’t find employment, whilst clamouring for equal treatment despite the djellabas, thick beards and a thick misogynistic streak that doesn’t quite jig with our society.

Whether it was Voltaire or Rousseau who said “I disapprove of what you say, but I will defend to the death your right to say it” makes no difference; you have the absolute right to worship who/whatsoever you choose. However, your rights end where my rights start, and vice-versa. Having respected your right to worship, you have the logical obligation to respect my right to do something else.

The moderate, respectable muslim will object that the suicide bombers don’t represent his views. The problem is that the suicide bomber claims to represent islam. If the moderate muslim doesn’t take steps to stop the suicide bombers, he shouldn’t be surprised to find himself bundled with them.

A muslim, you have decided to come and live in Europe. You’re more than welcome, many of us came from elsewhere and we thrive on diversity. However, it’s not a one-way street; when we visit a mosque, I take off my shoes and my wife wears a veil; quaint to our culture, but I respect yours. When you come to Europe, your daughter at school has the obligation to learn how to swim. We don’t do this to look at her breasts, we do it because in Europe there are many lakes and rivers and we don’t want our children to drown.

Is all this really so hard to understand? Or, as I suspect, are you doing it deliberately?

Jun 242015

So Tsarnaev apologises for his crime. Predictable, as in the USA, showing remorse seems to infer some kind of leniency, on the American model “You’ll always be forgiven your fuckups and given a second chance”.

In business, you create a venture, it goes tits-up, you go bankrupt, you get a second chance. This is eminently fair, after all, your backers were venture capitalists who were fully aware that they might lose their shirt.

Tsarnaev’s case is very different, he maimed and killed. Purportedly in the name of a religion. Said religion does not, in its majority, condone maiming and killing, although it’s a fine line, given the lack of condemnation by the muslim community.

Let us be perfectly clear, the death penalty has little dissuasive effect (on psychotics, who by definition aren’t in ‘our’ world). The death penalty serves a simple purpose: the culprit cannot do it again and he cannot propagate his genes. There is no credible argument which would allow the remote possibility that Tsarnaev could reproduce; his crime is so ignominious that he must be eliminated.

Apr 262015

in 1959, at the time of the first Sputniks, René Char wrote

L’homme de l’espace dont c’est le jour natal sera un milliard de fois moins lumineux et révélera un milliard de fois moins de choses cachées que l’homme granité, reclus et recouché de Lascaux, au dur membre débourbé de la mort.

Which renders, roughly, as

The spaceman born today will be a billion times fainter and will reveal a billion times less things than Lascaux’s stone-age Neanderthal recluse, whose strength was dredged from death.

Feb 222015

Recent events have weighed heavily on the notion of tolerance, which Voltaire described nicely in his prayer to God in 1763, some 250 years ago. Despite being a dyed-in-the-wool atheist, I have to admit his text was premonitory:

No longer then do I address myself to men, but to you, God of all beings, of all worlds, and of all ages; if it may be permitted weak creatures lost in immensity and imperceptible to the rest of the universe, to dare to ask something of you, you who have given everything, and whose decrees are immutable as they are eternal. Deign to look with pity on the errors attached to our nature; let not these errors prove ruinous to us. You have not given us hearts to hate ourselves with, and hands to kill one another. Grant then that we may mutually aid each other to support the burden of a painful and transitory life; that the trifling differences in the garments that cover our frail bodies, in our insufficient languages, in our ridiculous customs, in our imperfect laws, in our idle opinions, in all our conditions so disproportionate in our eyes, and so equal in yours, that all the little variations that differentiate the atoms called men not be signs of hatred and persecution; that those who light candles in broad daylight to worship you bear with those who content themselves with the light of your sun; that those who dress themselves in a white robe to say that we must love you do not detest those who say the same thing in cloaks of black wool; that it may be all the same to adore you in a dialect formed from an ancient or a modern language; that those whose coat is colored red or violet, who rule over a little parcel of a little heap of mud of this world, and who possess a few round fragments of a certain metal, enjoy without pride what they call grandeur and riches, and may others look on them without envy: for you know that there is nothing in all these vanities to inspire envy or pride.

May all men remember that they are brothers! May they hold in horror tyranny exerted over souls, just as they do the violence which forcibly seizes the products of peaceful industry! And if the scourge of war is inevitable, let us not hate one another, let us not destroy one another in the midst of peace, and let us use the moment of our existence to bless, in a thousand different languages, from Siam to California, your goodness which has given us this moment.

Jan 162015

Yesterday evening I was fortunate to be amongst a select few who had the privilege to be invited to hear the investment advice of the chief strategist of a major Swiss bank. (I should mention at the outset that the vast majority of the audience’s reference currency is the Swiss franc.)

The speaker was clearly an erudite man and his delivery was flawless. The underlying theme of his speech was divergence in today’s economy: in interest rates, in GDP, in unemployment, in central banks’ strategies, and so forth. Render unto Caesar, his presentation was concise, well-researched and extremely persuasive.

The conclusion of his argument was the bank’s investment strategy. In a nutshell, Swiss and European equities present few opportunites. On the other hand, the USA appears to have left recession and is showing strong growth perspectives, which he estimated at around 6%. The bank’s strategy is thus to move their clients’ assets to Swiss and European gilt of the highest quality for portfolio protection and to US blue-chips for revenue.

When questioned on the Swiss National Bank’s strategy of locking the Swiss franc to a floor of 1.20 CHF/EUR, he was of the opinion that the floor to the Euro would be replaced by a floor against a basket of currencies, probably EUR/USD/JPY, sometime in 2015.

Were I to have followed his advice, this morning, first thing, I would have moved my investments into US blue-chips, the stocks that participate in the Dow-Jones Index: American Express, Boeing, Caterpillar, etc. in the hope of a return of some 6% instead of the measly percent or two that I get on Swiss franc holdings.

With the most tragic timing for our strategist, some 14 hours later the Swiss National Bank announced that it had abandoned the 1.20 floor. The effect was instantaneous: the EUR and the USD crashed against the CHF, settling at day-end to ~0.86CHF/USD and CHF/EUR at a similar rate.

Had I bought those American shares some hours previously, I would have taken a 15% hit on my portfolio in so-many hours; even if the strategist’s predictions come true, I’ll have to wait two and a half years before breaking even.

There are several lessons to be learned from this story:

  1. Despite everything that your banker, hand-on-heart, assures you, he has no interest whatsoever in your financial well-being. His sole aim in life is to persuade you to trade your holdings as often as possible so that he can gather a maximum commission. If, despite the commissions, you make money, he’ll congratulate himself on having helped you. If you lose money, he’ll appear heart-broken at your misfortune, which was entirely due to the unfavourable market.
  2. If your reference currency is the CHF and you invest in higher-yield USD-labelled assets, you’re exposed to the CHF/USD exchange risk. Momentarily you might get away with it; as this example shows, you are likely to take a caning.
  3. Inversely, if your reference currency is the EUR and you buy CHF assets, your yield will be much more frugal… but your capital will be better preserved.

The take-away from all this is that in today’s liquid markets there is no free lunch. If your ambition is to receive the rate of inflation plus a whisker on your investments, there’s a strong likelihood that you’ll get it. If your ambition is get 6%, be prepared to lose 15% momentarily when things go tits-up.

P.S. For those whose mother-tongue is not English, the title is a pun