Mar 192011
 

You have to hand it to Google’s engineers, even the best GPS will have a hard job beating this: driving directions from Nordkapp in Norway to Pan Saung in Myanmar (Burma). 14’281 kilometres will take just over a week of continuous driving:

How did I do this in Excel? GeodesiX

 Posted by at 12:28 am  Tagged with:
Jan 082011
 

For those who envisage building an Excel RTD server, I have written a tutorial and open-sourced the code on SourceForge.

The following topics are covered:

  • How RTD servers work
  • Architecture
  • Excel, Multithreading and callbacks
  • Providing easy-to-read function names
  • Talking to the GoogleMaps APIs
  • Avoiding Application Domain misery
  • Embedding a GoogleMap page in an Excel Task Pane
  • Creating the Setup project
  • Changing the Excel RTD Throttle Interval
  • Utility functions
  • Building help from the source with Sandcastle

I hope that you will find it useful.

Dec 212010
 

A year ago I wrote an article predicting that the US dollar would be worthless, against the Swiss Franc and the Japanese Yen, by around 2020. A year has passed and the US dollar has been falling in line with my prediction, so I felt it might be interesting to re-visit the subject with a slightly different tack. Also, today seems like an auspicious date for doomsday predictions.

Rather than dealing with absolute exchange rates, I set out to try and show the relative strengths of currencies amongst themselves. To illustrate, let’s suppose that a glass of wine costs 5 USD.

  • On day one, we buy a 100mL glass with a dollar’s worth each of USD, GBP, EUR, JPY and CHF.
  • A month later, we need a refill. The wine still has the same value, but the exchange rates have changed.
  • Suppose GBP has risen by X% (and nothing else has changed). As we’re buying the wine with equal fifths of each currency, the USD’s value must decrease by X%, thus we will to pay more USD and less GBP.
  • These increases and decreases change the relative strengths of the currencies, and it is this that we shall study.

This is nothing more than my own version of the Dollar Index. The maths is simple, as you may surmise from this Excel spreadsheet:  forex2010

A first look over the past decade seems to illustrate some trends (click to enlarge):

The first 2 years show a pronounced strengthening of the USD, in the aftermath of the dot-com boom, but as of 2002 the USD has been declining steadily. The Federal Reserve Bank of Cleveland wrote an eloquent paper suggesting that the decline was due to the US current account deficit. This correlation held for a few years, but the USD continued to fall even when the account deficit started diminishing in 2006. I find it reassuring that the experts’ tea-leaves are no better at forex predictions than mine.

Moving on, if we set the starting point in 2002, the picture becomes much clearer:

Notice that the stock market crash of 2008 occurred on the 16th of September 2008, yet the currencies tumbled 6 weeks earlier. The documented mass buying of USD and JPY started then, not in September. I wonder why?

From 2002 to 2008, the EUR was clearly the strongest currency, even through the crash, and things remained relatively rosy until Greece admitted to cheating in its financial reporting and more than doubled its deficit in late 2009.  The Greeks were followed by the Irish, the Portugese, et al in a landslide where most of Europe had to own up to the vast borrowing over the past decade.

For the USD, its been downhill pretty well all the way. 2005 showed a slight rally, at least in part due to the Chinese severing the dollar peg mid-year. The second rally during the 2008 crisis was due solely to panic selling and Obama’s election in January 2009 created but a short-lived blip. Finally, the first half of 2010 saw the USD gaining some lost ground, but it was quickly reversed once the Fed starting printing money in earnest.

Returning to the glass of wine analogy, if in 2000 you held Swiss Francs, today you’d get a 136mL top-up; if you held US dollars, you’d only have 82mL in your glass. If you see the glass half-full, that’s a 60% loss; if you see it half-empty, it’s a 66% loss, either way it’s a huge difference.

My predictions

The Americans have chosen to get rid of their debt by devaluation. They don’t care, it’s not their loss. I’m very bearish and if the Chinese start selling their T-Bills in the not-so-distant future, the USD will become worthless overnight.

The Brits will take their medicine better than most, but the GBP will continue to lose value steadily for many years to come.

Europe will never really get its act together, simply because a single currency doesn’t make a single mentality. Issuing more and more bonds to buy back yesterday’s borrowing is just a stop-gap solution to delay the inevitable devaluation of the Euro.

The Japanese, industrious and disciplined, will grind on and climb steadily back to a comfortable position, exactly following the slope of the last decade.

For lack of a floating rate, it’s impossible to foresee how the Yuan will progress, but even cave-dwellers can see that it can only be upwards.

Finally, Switzerland, as usual, will remain a haven for both clean and slightly-soiled money. Their relations with their European neighbours will degrade (for a good part due to jealousy), and like the Yen, I predict that the CHF will continue on its current slope.

Graphically, here’s how I see the relative strengths:

which in forex terms, looks like this:

In summary:

  • The GBP will fall more or less in step with the EUR
  • Near the end of 2012, we’ll see 100 JPY = 1 EUR
  • At the end of 2015, the big bang when 1 CHF = 2 USD = 2 EUR.

Remember, you read it here first.

Oct 202010
 

Addin for Microsoft Excel which allows you to perform forward and reverse geocoding, both by address and latitude / longitude, calculate Great Circle Distances using Vincenty’s algorithm, calculate travel distances and durations and verify the results with a Google Maps Task Pane, all inside your comfortable Excel interface.

This is useful for creating GoogleMap applications to find places from a list, like this.

Implements three Excel formulas:

=Geocode(request, location)

Request is the field to return:

  • status The status of the geocode request (Fetching, OK, N matches, etc.)
  • latitude The latitude of ‘location’
  • longitude The longitude of ‘location’
  • and so on: formatted_address, country political,administrative_area_level_1 political, administrative_area_level_2 political, administrative_area_level_3 political, locality political, sublocality political, route, street_number, postal_code, types, location_type, partial_match, point_of_interest, establishment, viewpointne, viewpointsw, airport establishment transit_station, bus_station transit_station, establishment, natural_feature, neighborhood political, postal_town, premise, street_address, subpremise

Location is the name or address of a place or point of interest

=GreatCircleDistance(latitude1, longitude1, latitude2, longitude2)

Calculates the Great Circle Distance using Vincenty’s Formula, with fallback to the Haversine formula when Vincenty’s method doesn’t converge.

=Travel(request, origin, destination, mode)

Calculates the distance or duration to get from origin to destination, according to Google Directions.

Request is the field to return:

  • distance The distance in metres from origin to destination
  • duration The estimated duration from origin to destination

Origin and destination are the names or addresses of the start and finish.

Mode is the mode of transport to use: Driving, Bicycling or Walking

Free download here

Enjoy!

Screenshot, click to enlarge:

May 112010
 

In the process of analysing a client’s existing database, I used Visio’s reverse-engineering tool. It works well, but the resulting diagram was an incomprehensible bowl of spaghetti. Visio does have a “Layout shapes” command, which appears to work by moving shapes with repulsive forces and the result is, not surprisingly, repulsive.

What I wanted was a tool which would unravel the spaghetti, so that I could get a grasp of the relationships, edit and revise them and layout again in an iterative process.
Searching for a solution, I found three layout programs, none of which have a Visio interface:

  1. Microsoft Automatic Graph Layout (MSAGL), formerly known as GLEE The first version, GLEE, is free whereas MSAGL costs between 99 USD and 279 USD depending on where you buy it.
  2. Tom Sawyer Layout is also a graph layout library, the price isn’t disclosed on their website.
  3. Graphviz from AT&T research labs, reputed to have the most sophisticated layout algorithms, is free.

Given that the best quality was to be found in the free library, I made the obvious choice.
It took me over a year and some 11’000 lines of VB to get Visio and Graphviz to co-exist; marrying a Unix-style command-line program with a WYSIWYG interface, both with quirks to numerous to mention, was far more challenging than I initially thought.
The result, unimaginatively called GraphVizio, is available here, I hope you’ll find it useful.

25 May 2011 Version 1.1.5 released. Improvements:

Full support for 64-bit Windows and Visio

No longer makes a Visio document ‘dirty’ when opening

Full support for UTF8. This DOT file:

graph  RootGraph {
  node [width="7.08661417322834", height="0.787401574803148", color="#000000", fillcolor="#FFFFFF", fontname=Calibri, fontsize=24, style=filled, shape=box];
  edge [color="#000000", fillcolor="#FFFFFF"];

  "English: Hello, my name is Maurice\n(and blame Google if the translations are bad)" [pos="283.704566929134,620.932913385827", label="English: Hello, my name is Maurice\n(and blame Google if the translations are bad)"];
  "Russian: Здравствуйте, меня зовут Морис" [pos="283.704566929134,526.932913385827", label="Russian: Здравствуйте, меня зовут Морис"];
  "مرحبا، اسمي موريس : Arabic" [pos="283.704566929134,432.932913385827", label="مرحبا، اسمي موريس : Arabic"];
  "Chinese: 你好,我叫莫里斯" [pos="283.704566929134,338.932913385827", label="Chinese: 你好,我叫莫里斯"];
  "שלום, שמי הוא מוריס : Hebrew" [pos="283.704566929134,244.932913385827", label="שלום, שמי הוא מוריס : Hebrew"];
  "Japanese: こんにちは、私の名前はモーリスです" [pos="283.704566929134,150.932913385826", label="Japanese: こんにちは、私の名前はモーリスです"];
  "Thai: สวัสดีชื่อของฉันคือ Maurice" [pos="283.704566929134,56.9329133858268", label="Thai: สวัสดีชื่อของฉันคือ Maurice"];

  "English: Hello, my name is Maurice\n(and blame Google if the translations are bad)"--"Russian: Здравствуйте, меня зовут Морис";
  "Russian: Здравствуйте, меня зовут Морис"--"مرحبا، اسمي موريس : Arabic";
  "مرحبا، اسمي موريس : Arabic"--"Chinese: 你好,我叫莫里斯";
  "Chinese: 你好,我叫莫里斯"--"שלום, שמי הוא מוריס : Hebrew";
  "שלום, שמי הוא מוריס : Hebrew"--"Japanese: こんにちは、私の名前はモーリスです";
  "Japanese: こんにちは、私の名前はモーリスです"--"Thai: สวัสดีชื่อของฉันคือ Maurice";
}

produces this Visio diagram:

Graphvizio UTF8

Apr 272010
 

The scene :

A late evening in a plush boardroom on the 54th floor in Lower Manhattan. A dozen portly businessmen are seated around the huge table, where the remains of a large dinner have been replaced by a collection of rare whiskies. At the head of the table, a balding, corpulent man interrupts the others:

— Now then Gentlemen, enough banter, we have a problem. Anyone want a splash of this 50 year-old Midleton? I had a case flown in from Dublin last week, it’s awfully rare.

Who’s got the cigars? Richard, we all know what a kike you are, but pass the box along, there’s a good chap.

So, if you’re all comfortably pickled, let’s get down to business. Where’s Peter?

— In the john, I think the oysters disagreed with him.

— I see. Why is that Finance Directors are always absent when you need them?

[he pauses to finish lighting his cigar]

As I was saying, we have a problem, and a serious one: the SEC is on our ass, with a vengeance.

These chocolates really are sublime, are they not?

My assistant has calculated that we’ve ripped off about 50 million investors with these… what are they called? Structured derivations?

— “Derivatives”, Lloyd

— Yes, derivatives, whatever, for billions of dollars, that John-Alfred has siphoned off to Switzerland.

— Fucking Swiss, it’s always their fault, with their banking secrecy.

— Stop kvetching Charles, you bloody hypocrite, we all know where you’ve stashed your booty. I won’t remind you of those stock options that materialized on your Zurich account last year now, shall I?

No, we have to choose somebody who’s going to carry the can.

[a long silence ensues]

— Don’t be silly now, I wasn’t suggesting anybody in the top management, we’ll trim back the bonuses in the lower echelons, not here.

No, we need some miserable klutz, preferably someone that nobody here knows, so that we can plead ignorance, and throw the creep out on the street for a public sacrifice.

Richard, you’re good in the sniveling role, you can go on CNN and cry how ashamed we all are, when the word gets out.

— Yes, Lloyd, but who?

— How should I know? Surely one of you must have an insubordinate little rat that you’re dying to get rid of? You know, the nerdy type, with big glasses, who can’t lace his own shoes, disconnected from reality. Ideal for a little crying with Richard. C’mon, give me a name.

— But Lloyd, we don’t know the employees, we barely speak to them, everything’s done by email. Most of mine, I don’t even know where their offices are.

— So it’s up to me to fix things, as usual. Charles, pass me that PC. Where’s the H.R. page? Ah, here. Let me see now.

God, where do you hire these people? They all look like retards. Hasn’t somebody given H.R. instructions not to hire hicks? OK, I’ll click randomly. Here we are, Jay Ratlivich, a nasty-sounding name and he looks the part. What do you think?

— He’s in facilities management Lloyd. Difficult to pin something on him.

— Which hardly facilitates my task. Alright, how about this one, Mustafa Albariz, terrorist overtones, he’ll do.

— Hardly Lloyd, he’s in agricultural finance. He deals in manure, not credit default swaps.

— Pass back the whiskey would you Richard? Alright, my final offer, this one looks ideal, Fabrice Tourre. Ah, a trader, they’re like arrogant hamsters on methedrine that lot. And he’s French to boot, perfect, the frogs are always fucking things up.

— But Lloyd, he’s only 28, they’ll never believe we let someone so young play with billions.

— Charles, two years ago, you’d never have believed we’d have a black President. It’s simply a question of manipulating the press. We’re doing God’s work, if it’s good enough for Him, it’s good enough for us.

Porter, this is right up your street. Get your I.T. guys to siphon off all his emails and dress them up to incriminate the little bastard completely. Then leak the whole lot to press, you know the ropes. Whilst you’re at it, credit a couple of million to his account, back-dated to last June; we’ll leak his bank statement when the shit hits the fan.

That’s settled then. Fiddle the I.T. records, prepare a press statement, media plan, the usual stuff. Oh, and we’d better announce some layoffs. Charles, make sure the severance payments and restructuring charges are completely tax-deductible. Whilst you’re at it, announce that we won’t be paying a dividend this year, teach those shareholder assholes a lesson. I want all that on my desk first thing in the morning.

Anyone for a drop of this 1984 Dom Perignon to celebrate? It really is the best year in the cellar.

(Adapted from the original French article on www.boursorama.com)

Nov 182009
 

The USD has dropped pretty well back to parity with the Swiss Franc.
Based on the forex rates since 1970:
usforex2
Source: Pacific Exchange Rates

This extrapolation suggests that the USD will be worthless:
– against JPY in 2018
– against CHF in 2020
– against EUR in 2042 (DEM in graph, fixed rate 1.95583 since 31/12/1998)

As the graph also suggests, the GBP, which more or less matches the USD, will become worthless in the mid 2020’s.

Of course, as any investor knows, past performance doesn’t predict anything in the future. Nonetheless it would seem that the trend will not only continue, but is likely to sharpen. The reason for this is simple; the U.S. government is borrowing and printing money at a heretofore unseen rate:
U.S. Public debt Source www.whitehouse.gov
and as every investor also knows, borrowed money has to be re-paid sooner or later.

In my opinion it will be sooner rather than later. Nearly 50% of U.S. debt is held by the Chinese and Japanese:
Foreign holders of United_States treasury securities
Source www.treas.gov
At some point the pain of holding securities whose value is continuously decreasing will exceed the perceived benefits. When that happens, U.S. creditors will start selling and the result can only be further devaluation of the dollar.

Nay-sayers will argue that the Chinese will soon have to devalue the Yuan. Maybe, but once the Yuan were to be floated, there’s no good reason for it’s value to lower, quite the opposite given their balance of payments.
The other argument is that the U.S. could raise the interest rate on the USD. In the short-term, this would certainly produce a buy signal, but in the long run this will fail, simply because the dollar will devalue more or less in step with the increasing interest rate. Let’s face it, a bond that pays, say, 15% p/a has been and always will be a junk bond, even if it’s printed by the U.S. Treasury.

As the Chinese proverb says “We are going to live in interesting times”

Oct 302009
 

Very occasionally, one reads something on a forum that is truly excruciatingly funny.

I found this onVolconvo

Quote by: Vee
How old were you when you first masturbated?About 42…

What method did you use?

The Heinrich Manouver, I think it was called. Didn’t work, though…

Aug 272009
 

Problem: You’re using Visual Studio to write a Browser Helper Object for Internet
Explorer and you want to add some images to the web page being displayed.

Here is an example, taken from my Affine addin. The two images I insert are shown by the arrow:

Affine in action

Finding out how to do this is trickier than expected, so here’s the recipe:

  1. Import the images into the project and create a .RC file which identifies them:

    affinehide.bmp bitmap "affinehide.bmp"
    affinefade.bmp bitmap "affinefade.bmp"

    I called this file images.rc in the images directory in my project.

  2. You’ll need RC.EXE, the resource compiler, which is in the Windows SDK. The
    2008 version for .NET 3.5 is here
  3. In your setup project’s, set the prebuild event to

    "D:Affinerc.exe" /r "d:affineimagesimages.rc"

    I copied RC.exe into my project directory because I sometimes work on a 64-bit box, where Program Files becomes Program Files (X86). Adjust the paths to suit your installation.

  4. Open your project’s .VBPROJ file and insert the three red lines shown:

    <?xml version=”1.0″ encoding="utf-8"?>
    <Project DefaultTargets="Build"
    xmlns="http://schemas.microsoft.com/developer/msbuild/2003"
    ToolsVersion="3.5">
    <PropertyGroup>
    <Win32Resource>imagesimages.res</Win32Resource>
    </PropertyGroup>

    <PropertyGroup>
    <Configuration Condition=" '$(Configuration)' == '' ">Debug</Configuration>

    Thanks to Wouter van Vugt for this!

  5. The HTML to insert the images is straightforward:

    <img src="res://affine.dll/#2/affinehide.bmp"

    where #2 means that the embedded object is an image
    Assuming this string is stored in the variable "buttonhtml" then the code to insert the button on the page is

    a.insertAdjacentHTML(&#34afterEnd&#34, buttonhtml)

  6. Now for the events. You need an event handler for the DHTML event itself:

    Imports mshtml
    Public Delegate Sub DHTMLEvent(ByVal e As IHTMLEventObj)
    _
    Public Class DHTMLEventHandler
    Public Handler As DHTMLEvent
    Private Document As mshtml.IHTMLDocument
    Public Sub New(ByVal doc As mshtml.IHTMLDocument)
    Me.Document = doc
    End Sub
    _
    Public Sub [Call]()
    Handler(CType(Document.parentWindow.event, mshtml.IHTMLEventObj))
    End Sub
    End Class

    Thanks to Rick Strahl

  7. A handler which the above will call, to actually deal with the event:

    Imports mshtml
    Module BrowserEventHandler_
    Public Sub BrowserEventHandler(ByVal e As mshtml.IHTMLEventObj)
    Try
    If e.type = "click"” AndAlso e.srcElement.tagName = "IMG" Then

  8. and finally tghe code to add in in your DocumentComplete event:

    Dim Handler As DHTMLEventHandler = New DHTMLEventHandler(doc)
    Handler.Handler = AddressOf BrowserEventHandler
    doc.onclick = Handler

Happy coding!

Jul 012009
 

From an early age I have practised defensive programming. On a recent project, I was loading data entered on an intranet site to an SQL Server table. Wary, I used SQL’s ISNUMERIC function to validate the numbers users had entered. It worked fine for a few weeks, until somone entered a comma as a decimal separator. It turns out that ISNUMERIC accepts this, whereas casting doesn’t. Here’s the proof:

isnumeric

Doubtless, MS will try and wriggle out by saying that it depends on how your international settings are made. My contention is that if ISNUMERIC says it is then you should be able to CAST it to a number.

Period. 

(sorry for the weak pun)

 Posted by at 11:56 am  Tagged with: