Helicopter Flight over Hong Kong

When I was visiting New York City in 2008, I looked into helicopter tours of the city. At the time, though, they seemed too expensive, and I discarded the idea. I have regretted that decision ever since. Because seeing a city – but particularly one with so many high-rise buildings such as New York City or Hong Kong – from the air gives one a totally different perspective compared to just seeing it from ground-level.

So when going to Hong Kong (see pictures [1] and [2]), I decided that I was going to see it from above (almost) regardless the cost. From what I found, there aren’t many such tours in Hong Kong if you don’t intend on renting out an entire helicopter. The one I used was Heliservices Hong Kong. Trying to book online, they seem to require you to purchase at least four seats. I emailed them about that and they were super nice and gave me the remaining seat on one of their flights.

Below is my raw footage from that flight. It was shot handheld, so it’s a bit shaky, though the camera did a pretty good job of steadying it most of the time. There are couple of scenes where I tried to zoom in, but these are just too shaky to really see anything. Also there is some glare with the sun reflecting in the window. Apologies for that.

Downloading Stock Quotes into an Excel Spreadsheet

I have an Excel spreadsheet that I use to manage my finances. Over the years, it has grown in complexity and I had automated it more and more with VBA macros. The one thing I still did manually was input the current values of stocks and mutual funds I own.

That is until a couple of days ago, when I saw this Tweet with a one-line PowerShell statement to extract a quote from a website:

((wget http://www.nasdaq.com/symbol/msft).AllElements | where id -eq "qwidget_lastsale").innerText

This sparked my interest to automate this part of my spreadsheet as well.

Data Source

Poking around the source code of my favorite financial news website, I noticed that the current price of the financial instruments I was interested in could be identified by a certain CSS class that was applied to a span element surrounding them.

Using PowerShell works

The first version I came up with was basically just executing the above statement (with some modifications to filter by class) on the command line.

Sub UpdateQuoteWithPowershell(sUrl As String, sClass As String, sTargetCell As String)

    Dim sCommand As String
    sCommand = "powershell ((wget " & sUrl & ").AllElements | where class -eq " & sClass & " | Select-Object -first 1).innerText"
    
    Set objShell = CreateObject("WScript.Shell")
    Set objExecObject = objShell.Exec(sCommand)
    
    Dim sQuote As String
    sQuote = objExecObject.StdOut.ReadAll()
    CleanAndWriteQuote sQuote, sTargetCell

End Sub

It’s straight forward (all I had to do was search online how to execute a command line from VBA) and works. Unfortunately, it’s also rather slow, taking a couple of seconds for each quote. Furthermore, it had the ugly side-effect of showing a command window while the script was running.

Using MSXML is faster

So I did some more online research, eventually combining the approaches from multiple sources. I came across a couple of people using the Microsoft XML Parser (MSXML) to download and parse webpages. My first instinct was to download the webpage, and basically do the same kind of filtering for the first element with the correct class-attribute. This ran into problems, as the HTML pages in question aren’t parseable as XML.

Thus I ended up using the XMLHTTP60 object merely as a means to do the download (I know there are probably other ways to do this, e.g. by utilizing IE). Finding the information I need was done using the Split function.

Sub UpdateQuoteWithMSXML(sUrl As String, sClass As String, sTargetCell As String)

    Dim vResult As Variant

    Dim xmlHttpRequest As MSXML2.XMLHTTP60
    Set xmlHttpRequest = New MSXML2.XMLHTTP60

    xmlHttpRequest.Open "GET", sUrl, False
    xmlHttpRequest.send
    
    If xmlHttpRequest.readyState <> 4 Or xmlHttpRequest.Status <> 200 Then
        Sheet1.Range(sTargetCell).Value2 = CVErr(xlErrNA)
    Else
        Dim sQuote As String
        sQuote = Split(Split(xmlHttpRequest.responseText, "span class=""" & sClass & """>", , vbTextCompare)(1), "<")(0)
        CleanAndWriteQuote sQuote, sTargetCell
    End If
       
End Sub

NB: This approach requires you to add a reference to "Microsoft XML, v6.0" to your VBA project via the Tools -> References… menu.

Wrapping up

In the interest of completeness, here’s the helper method CleanAndWriteQuote I use in the snippets above:

Sub CleanAndWriteQuote(sQuote As String, sTargetCell As String)
    sQuote = Replace(sQuote, ",", ".")
    sQuote = Replace(sQuote, "&nbsp;EUR", "")
    Sheet1.Range(sTargetCell).Value2 = Val(sQuote)
End Sub

Any error handling (e.g. when there is an error downloading the webpage or when no span with the given class is found) is left as an exercise to the reader.

I also wrote a wrapper-method that calls the above Update method with the data for each of the financial instrument I own. This method is hooked up to the workbook’s Open event, so whenever I open up my spreadsheet it automatically refreshes. Saves me quite a bit of time every day.

Next, I’m thinking about storing the previous day’s quote, so I can include an indicator whether my holdings went up or down.

More Hong Kong Pictures

I had posted two pictures from my recent trip to Hong Kong last week. After going through all 500 or so of them this weekend, here are a few more.

The first thing to notice about Hong Kong is how densely packed everything is. I’ve been to New York which has a lot of skyscrapers, too, but Hong Kong just feels very differently. Maybe it’s because of how thin many of these high-rise buildings are; and the fact that many of them are residential. It’s also very interesting to see how people utilize these buildings’ roofs: setting up gardens, installing a large number of antenna or even building another little house there.

High-Rise Apartments (2)

High-Rise Apartments (4)

High-Rise Apartments (1)

High-Rise Apartments (3)

Architecturally more interesting are, of course, the high-rise office buildings on Hong Kong Island. It is said that the angular shape of the Bank of China building gives of negative Feng Shui. So to defend against that, two cannons were installed on top of the HSBC Main Building facing the Bank of China building (top left of the second picture).

Bank of China Building

HSBC Main Building

Speaking of HSBC, here’s their logo from way back when (seen at the Hong Kong Museum of History).

HSBC Sign in the Hong Kong Museum of History

When on Hong Kong Island, there are many areas where you can totally forget that you’re actually in China. The people, stores and cars on the streets are just like in any other world city. A few kilometers north, however, up Nathan Street in Kowloon, the scenery is totally different. I’ve never been to China proper, but what I’ve seen from pictures and in documentaries looks very much like Kowloon.

Nathan Street (1)

Nathan Street (2)

The fastest and most convenient way to get from Hong Kong Island to Kowloon is probably the subway (i.e. the MTR). It’s also very inexpensive: a trip from my hotel in Mong Kok to Central was only 12.50 HKD (less than 1.50 EUR). For one trip, however, I took the Star Ferry, which was also really cheap: 3.40 HKD to get from Central to Tsim Sha Tsui. Plus, you get a much nicer view of the skyline as you leave Hong Kong Island behind.

Star Ferry (1)

Star Ferry (2)

Skyline Hong Kong Island at Night

Finally, three miscellaneous pictures: A fun looking statue titled “Recliner with Head in Hands” I came across in a park on Hong Kong Island on my way to Wan Chai.

Recliner with Head in Hands

Something I had never seen before: lines on the ground marking where to queue for which bus. On one bus stop, there was a queue two blocks long; just person after person, orderly waiting for their bus to take them home.

Bus Stop

And last but nor least, a nice view of the roof of the departure hall of Hong Kong International Airport.

Departure Hall Hong Kong Airport

In the end, it has been way too short a stay. I had such a great time and hope to come back some day. I would really like to hike up or around Victoria Peak, for instance.

Trip to Hong Kong: First Pictures

I have been in Hong Kong since Saturday night. But since I had been working I haven’t yet had a chance to see much of the city outside my company’s office buildings. The pictures I did take, are pretty amazing though. It is a pretty amazing city after all.

Arrival in Hong Kong

Looking at the skyline of Hong Kong Island, I was pretty disappointed by how small and insignificant the HSBC building appeared. Inside though, it’s got some pretty amazing looking architectural features.

Skyline Hong Kong Island

Things to Read: Favorite Books

I’ll be traveling to Hong Kong later this month. So as sort of a preparation, I decided to re-read Fragrant Harbour by John Lanchester; one of my favorite books.

This made me think about, what it was about my favorite books that made them stand out from all the others. So here is (in no particular order) a list of some of my other favorite (fiction) books. What they all have in common, is that they consist of multiple, interleaving plot lines, sometimes spanning multiple decades. The story is told from multiple perspectives and the whole is revealed only over time as the different pieces fall into place.

Another Weekend in Frankfurt

More than two years had passed since my last visit to Frankfurt so this past weekend I hopped on a train and went to see M. There isn’t much to say about the weekend other than that is was awesome. Thanks, M.

Below are some pictures; I particularly love the first one. Photoshop Express has done a really nice job of bringing out the best of it.

Ankunft in Frankfurt (1)

The Frankfurt Book Fair:

Buchmesse (1)

Buchmesse (2)

Messehalle Längst Schwarz-Weiß

Skyline at night in the rain:

Skyline im Regen bei Nacht (2)

[26-Oct-2014 Update] Here are two images missing from the original post.

I took these with one of M’s camera: a Sony Alpha 100. Unlike like my Alpha 58, the 100 is a classic DSLR and does not yet have Sony’s Single-Lens Translucent (SLT) technology. Since the 58 was the first real camera I bought, I wasn’t accustomed to the way the 100 works, and I must say, I really missed the electronic viewfinder. It’s so convenient to see the final image after you’ve made the shot without having to remove the camera from your eye and check the screen all the time.

SONY DSC

SONY DSC

Looking back at Year Five at Personal Nexus

Today is the fifth anniversary of the day I started this blog. In the past, I’ve used this occasion to review what I had posted, how many people had visited my blog et cetera.

Lichtpunkte

Going forward, I don’t think I’ll do that anymore. What I will do though, is post a bit more regularly. I have a few things lined up already: the conclusion of the .NET Gadgeteer story and some more photography related posts. And if all goes well, I’ll be able to travel a bit for work and bring back some pictures and stories in the coming months.