VBA IBAN Validator

From February 2014 onwards, IBAN will be the new European standard for bank account numbers. This standardization is part of the SEPA initiative to improve the efficiency of cross-border euro payments. I wrote an IBAN validator in VBA for an MS Access database I’m maintaining, but you can also use it in Excel or any other application that supports VBA.

The function ValidateIban takes an IBAN bank account number and returns True if the number is valid and False otherwise. It checks if the length is correct as per country and if, after rearranging and converting the IBAN code to an integer, the remainder of the modulo 97 operation equals 1. See wikipedia for a description of how to validate an IBAN number.

Updated eduroam settings for the nokia 5800

In my previous blog entry I posted the settings for connecting to the eduroam network at the University of Groningen. Since yesterday however the security standard and the authentication method of the wireless network have been updated. To continue being able to access the eduroam network using your Nokia 5800 (and similar models) the settings as shown in the screenshots must be entered.

Continue reading

Eduroam settings for the Nokia 5800 at the University of Groningen

EDIT: This post is no longer relevant. You may find an up-to-date version here.

It took me a while to figure out how to connect my Nokia 5800 phone to the University of Groningen’s eduroam WiFi network. I’m posting the settings here for anyone interested.

First of all you need to install a certificate file onto your phone. Just copy it to your phone’s SD card and open it using the file manager, or browse to this webpage and download the file directly. Say yes if your phone asks if it should install the certificate. You can grab the file from here.

Continue reading

Web scraping using XPath and Visual Basic

More and more web services are popping up. Getting the weather in Paris or the price of a book on Amazon is only a REST, SOAP or XML-RPC call away. Unfortunately, most of the web content being delivered is still intended to be rendered in a browser and the providers of that content haven’t included a nice web service API for automated extraction.

Web scraping is a technique for extracting information from websites that don’t provide web services. One common way to find the required info on a webpage is to use regular expressions. A much better approach however, is to make use of XPath. XPath is a XML query language that can be used to locate a specific part of an XML document. One approach taken by some web scrapers is to parse an HTML document into an XML document and then use XPath to query the document. The problem with this approach is that most HTML out there won’t parse into XML too well. I will explain how to circumvent this problem below.

Let’s consider the following scenario. We want to automatically get a certain stock quote from the web, in this case the stock quote for AEGON, which happens to be my current employer. This data is published on the following website: http://www.marketwatch.com/quotes/aeg. We want to put the stock price in a cell in an Excel worksheet, and we’ll use VBA and the Microsoft Internet Control to navigate to the mentioned webpage and scrape the needed data.

First, we start by firing up Firefox and navigate to the webpage. Make sure you have the Firebug add-on installed and select the required data. Right-click and choose “Inspect element”.

Then hover over the required content in the Firebug pane, right click and select “Copy XPath”.

The XPath is now copied to the clipboard and looks like this: /html/body/form/div/div/div[7]/div[3]/div[3]/div/h2/span[2]/span.

Now we open a new Excel document, place a Button on the first worksheet and write the following code in it’s _Click() event.

Make sure you set references to the Microsoft Internet Controls and the Microsoft HTML Object Library. The XPath we got using Firefox/Firebug is used as an argument to the function getXPathElement. I wrote this getXPathElement function to be able to address locations in a DOM document as one would normally do in an XML document.

This function doesn’t support the full XPath syntax, but it serves our purposes. Clicking the button opens an Internet Explorer window and get’s the stock price value in cell A1. Download the full project here.

Countdown clock in Excel

My work buddy Jurre is drastically changing his career path for the better by relocating to Ireland this week. To help him keep track of the time he has left in his current job I made him a countdown clock in Excel. It shows the number of days, hours, minutes and seconds left both in separate cells of a sheet and in the titlebar of the Excel window. It demonstrates the use of the scarcely documented Excel function DATEDIF and the Application.OnTime method to update the clock at one second intervals. The workbook you can download here counts down to the new year 2010, but you can easily change the date and time it counts down to.

How to circumvent corporate Internet filters

The company I’m currently working for uses an Internet filter to prevent people from accessing certain websites. Not only porn sites are blocked. Also game sites, forums, most blogs, or anything ‘controversial’ are prohibited. I can’t even visit my own website.

When they first installed this preposterous filter I could still make use of anonymous proxies. Later they also blocked these proxy sites. After that I used the Google cache to view certain sites. Which also only worked temporarily. But this week I found another trick. I surf to http://babelfish.av.com and simply translate the website I want to view. Dexterous trick, if I may say so myself! I wonder how long it takes before they block this possibility also.

Printing from EXTRA! Basic.

Attachmate EXTRA! is terminal emulation software to connect to mainframes. Part of this software is a Basic interpreter that allows you to write automation macros that do screenscraping on terminal screens. The scripting language used is called Extra Basic. Scripts have the extension .ebm, which stands for Extra Basic Macro.

This scripting language is a lot like Visual Basic, or QBasic and most code you can simply Copy & Paste. But there are differences.

At work, when extending an old macro (made by a now retired colleague) with printing capabilities I ran into some limitations of Extra Basic.

In QBasic there used to be a command LPRINT to send text to a line printer. Neither Visual Basic or Extra Basic have this command. To print from Visual Basic you use the Printer object, but to print from Extra Basic you need another approach.

Continue reading