Matt's TipTopBlog

Counting Sort in C and Python: Operation Algorithm 2

This is the second in my series of algorithm posts in trying to become a better programmer. Counting sort is possibly my favorite of the sorting algorithms because it looks so complicated at first glance, but its actually very simple and graceful.

The principle behind counting sort is to store a count of each value that appears in the array and use that as a way to place the original value in the resulting array. It's generally used for fairly low numbered positive integer values, and the running time of the algorithm depends on both the number of elements, and the range of values within the array. For example, a simple array with the values (1, 6, 1000000000) will take a LONG time to be sorted, even though there are only three elements because at some point there will be a loop from 0 to 1000000000. Also note that the sort method requires a parameter k, which is the maximum value contained in the array.

If you don't understand the algorithm, it would probably be better to read the wikipedia article instead of trying to rely on my brief and badly written version.

Language Choice

I decided to do a version in Python because I've never really used the language much and I figured it was a good way to give it a try. My first impressions are pretty positive, and I love how it uses indenting as part of the language semantics.

My C Implementation:

My Python Implementation

Quicksort in C and Scheme : Operation Algorithm 1

This is the first post in my quest to be a better programmer. Quicksort is one of the 'standard' sorting algorithms. Its fairly easy to implement, and normally runs fairly quickly. I've implemented quicksort in both C and Scheme, although I think my Scheme implementation is more of a pseudo-quicksort due to a reluctance to break from the functional style encouraged by Scheme syntax.

Quicksort Overview

The 'Partition' algorithm is at the core of a quicksort implementation, and this partitions an array into groups of 'less than x' and 'more than x', with x itself being placed inbetween. The final position of x is the value returned by the partition function. In my simple implementation, the choice of x remains constant (either the first or last element). Randomizing this is the main way to increase the worst case speed of this algorithm.

After partitioning the initial input, quicksort recursivly calls itself on the 'less-than' and 'more-than' groups returned from partition. The result is a chain of recursive calls which continues until quicksort is trying to sort a single number.

Check out Wikipedia to get a more in-depth understanding of quicksort.

Special Property

Quicksort is good because it sorts 'in-place'. In other words it doesn't use a secondary array to sort the elements into. This can save a lot of memory, especially if you are sorting a list of complicated data structures which are a few KB each.

My C Implementation

My Scheme Implementation

I think its worth noting that the scheme implementation doesn't exactly sort 'in-place'. Instead it recursivly builds up secondary lists then joins them together at the end as the return value instead of moving the elements around in a single list. However the amount of memory used should still be in the order of the number of elements in the input array unlike other sorting algorithms (counting-sort, and bucket-sort come to mind) which use secondary arrays which do not form part of the solution.

(define pHelper (lambda (all chk l m)
                  (cond ((null? all) (cons l (cons chk (cons m '()))))
                        (else
                        (let ((x (car all)))
                          (if (<= x chk) 
                              (pHelper (cdr all) chk (cons x l) m)
                              (pHelper (cdr all) chk l (cons x m))))))))

(define partition (lambda (l)
                      (pHelper (cdr l) (car l) '() '())))



(define quicksort (lambda (l)
                    (cond ((null? l) l)
                          (else
                          (let ((lx (partition l)))
                            (append (quicksort (car lx)) (cons (cadr lx) (quicksort (caddr lx)))))))))

Operation Algorithm: Introduction

In an attempt to be a better programmer, and even better student, I'm undertaking a new mini-project: I will make a series of blog posts whereby I describe a particular (well known) algorithm, then provide an implementation in 2 different programming languages.

Skills++

Doing this should help with two things.

1) It should make me remember these algorithms in more detail. Most of these algorithms are so very clever that it will certainly help if I know them by heart.

2) At a time when I don't need to use anything other than Java it will help to keep me comfortable with other languages.

Languages

I'm certainly going to do every algorithm in C, probably not with the best C code, but certainly C code that works. Also I'll probably switch in and out of a functional language such as Scheme and something like VBA to keep it old-school.

Algorithms and Links

I will update this post with links when I make each post. Also check out the Operation-Algorithm tag in my tag cloud for easy access.

Quicksort
Counting Sort
BucketSort (love this one)
Heapsort (including the making of a Max-heap)
Binary Search Tree creation and traversal

Stay tuned!

What Is Twitter? The New Forum for Pyramid Schemes Apparently

So I was casually browsing blogs and twitter posts this morning and came across a bunch of links to a new twitter application called Tweeter Getter. Essentially this is a twitter pyramid scheme to get you *thousands* of followers.

Pyramids

Here is an apt description of the pyramid business model from Wikipedia:

The essential idea is that the mark, Mr. X, makes only one payment. To start earning, Mr. X has to recruit others like him who will also make one payment each. Mr. X gets paid out of receipts from those new recruits. They then go on to recruit others. As each new recruit makes a payment, Mr. X gets a cut. He is thus promised exponential benefits as the "business" expands.

Twitter + Pyramid

In our case the business is twitter followers, and the payments are follows.

By following the first 6 users on the list, and retweeting your own link, you start your own little pyramid coming in under the 6 people above you. They get a new follower, and on the assumption that people ACTUALLY follow your link you could get the same.

Each subsequent person that joins the pyramid from either your link, or a derivative of your link will add you as another follower.

Lots of People

Of course this model is not sustainable in the long term, and the more people join the lower the 'returns' are likely to be. By the time the pyramid is 13 layers deep, it will need more than the entire population of the globe to participate in order to continue.

Moving Countries is not so fun

I move to New York City in July with my soon-to-be fiance and while we're very excited, the amount work required in order to make it happen is staggering

I'm doing a masters in computer science, and its already taken me about 9 months to pull that off, but we still have to:

Get the university documentation I need for a visa
Get a Visa
Work out if shipping all our stuff to the USA is cost effective
Realize it isn't
Sell everything we can't fit into 4 (large) suitcases
Sell more stuff when we realize the suitcases weigh three times the allowed limit
Cancel all our phone bills / electricitiy bills / internet bills / etc
Sell the car (already done!)
Move out of our London flat
Find somewhere to stay until our flights (Thanks Rob)
Fly out
Move in
Realize we forgot something important
Realize we're going to have about 1/2 the space we had in London

Costs

We thought it would be pretty cost effective to ship all our stuff by freight, you know because it takes 2-3 months and your stuff could get covered in sea water, but we were quoted £400 just to ship 5 regular moving boxes. Thats insane.

Bearing in mind that its costing us $30,000 a year for me to do a masters we need every penny we can get.

Crazy

You'd also think that a university such as NYU (which has 45% international students) would have a process in place for registering with various national governments as a provider of education. Apparently they missed that lecture, and instead I have to deal with administration offices who have absolutely no idea.

I'm trying to get them to register with the UK Learning and skills council (so the UK government will give me an interest-paid loan). Its already taken me a month of talking to one office after another, and they still haven't managed to fill in the 2 page form. I keep getting responses like "we don't think we can participate in that scheme" and "we don't understand the requirements", but what they really mean is "We can't be bothered".

Excited Regardless

All this stuff will go away in a few months so I'm not going to let myself get worked up. Instead I think I'll just dig in the the final push, because this next year could well be the best year of my life.

All Quiet on the Western Front

I've been working on a personal project: http://www.tiptoptweet.com which has taken up a whole lot of my time. Because of that I've been neglecting this site somewhat. Well, the site is now finished and I could do with some feedback, but equally I should be able to pick up the blogging slack a bit too.

What Is TipTopTweet?

Its a twitter application that lets you vote for other @users or URL's by simply adding a #hashtag to a message.

By adding either #voteup or #votedown to a message which contains a user or a link, tiptoptweet will pick up your vote and record it. The website then shows top ranked links and users over a week / a day / a month in any vote category.

You could say that its basically a Digg or Reddit for twitter.

Why?

I was getting a little fed up of all the sites which track a user's popularity on twitter by simply measuring how many other people are following them. I want to know who people actually think are interesting, letting users vote is an obvious way to do that.

Technically speaking I also wanted to get some good experience with ASP.NET MVC, which the whole site is built in. I've learned an aweful lot about MVC actually, so I'm glad I did it.

What's Next?

I need feedback! I don't really care whether or not it becomes super-popular, but I do want to know if its useful, if it works properly, and how I could improve it.

If you're reading this and it sounds even vaguely interesting, then please check it out and let me know what you think!

Sending Emails using VBA in Excel: Outlook, Lotus Notes, and SMTP

Love it or hate it, email is everywhere, so the demand for integrating email into simple applications can be pretty strong. I thought I'd share three simple methods by which you can send emails using VBA in any Microsoft office product. Thus allowing your users can annoy everyone with lovingly crafted emails at the push of a button.

I will outline two approaches to sending emails, advise as to best practice, and provide full code for implementation. Its all very simple, so all skill-levels should find it easy enough to follow (despite the horribly confusing articles on other websites).

The Two Approaches to Sending Email

The two approaches are: 1. Communicating directly with a mail server (such as Microsoft Exchange Server), or 2. using the API of a client application (such as Lotus Notes or Outlook).

Using the client API binds your code to the email application running on a user's machine. The danger of this is that if you move from Lotus Notes to Microsoft Outlook the emailing routine is no longer going to work!

The benefit of going directly to the server is that how you communicate with the server doesn't change regardless of whether you're using Microsoft Exchange, Google Apps, or Lotus Domino. However, some companies heavily lock-down their mail servers as a way to increase security, so this type of communication is not always an option (although it is in most cases if you ask the right person).

Clearly the ideal way to send an email is to communicate directly with the mail server. SMTP is the ideal protocol for doing this (Simple Mail Transfer Protocol), so that is what I'll show you.

For the sake of completeness I will also demonstrate how to send an email with both the Microsoft Outlook and IBM Lotus Notes APIs. Although let me stress again that these should NOT be used by default.

SMTP (The Best Way)

All windows distributions from Windows 2000 onwards include a COM API component called Collaboration Data Objects (CDO). This api wraps the underlying messaging API and allows us to very simply manipulate email objects.

Here is the code:

Public Sub EmailViaCDO()


Dim sentFrom        As String
Dim serverAddress   As String
Dim messageBody     As String
Dim sendTo          As String
Dim userName        As String
Dim password        As String
Dim subject         As String

'This stuff would normally be passed into the method as parameters
sentFrom = "matthew.rathbone@example.com"
serverAddress = "server.example.com"
sendTo = "example@email.com"
messageBody = "This is a test email"
subject = "message subject"

Dim message As New CDO.message
Dim config As New CDO.Configuration

'set up the configuration for the email server
config.Fields(cdoSendUsingMethod).value = cdoSendUsingPort
config.Fields(cdoSMTPServer).value = serverAddress
config.Fields(cdoSMTPUseSSL).value = False
config.Fields(cdoSMTPConnectionTimeout).value = 10


'Only use these if the server requires authentication
' config.Fields(cdoSMTPAuthenticate).Value = cdoBasic
' config.Fields(cdoSendUserName).value = userName
' config.Fields(cdoSendPassword).value = password
'---------------

'update the config before using it
config.Fields.Update



Set message.Configuration = config
message.To = sendTo
message.from = sentFrom
message.subject = subject
message.TextBody = messageBody
message.AddAttachment ("C:\attachment.txt")

'Away goes our message!
message.Send


End Sub

Microsoft Outlook

Because Outlook also has VBA, using outlook from any VBA-enabled application is actually very easy, see below:

Public Sub EmailViaOutlook()

' requires a reference to the Microsoft Outlook 8.0 Object Library

' creates and sends a new e-mail message with Outlook
Dim outlookFolder As Outlook.MAPIFolder
Dim recipient As Outlook.recipient
Dim mailItem As Outlook.mailItem

'this gets an existing outlook
Set outlookFolder = GetObject("", _
        "Outlook.Application").GetNamespace("MAPI").GetDefaultFolder(olFolderInbox)
    
    ' lets create a new email message
    Set mailItem = outlookFolder.Items.Add
    
    mailItem.subject = "this is the subject"
    mailItem.Body = "This is the message I'm sending!"
    mailItem.Attachments.Add "C:\attachment.txt"
    
    'SOME OPTIONAL STUFF
    mailItem.OriginatorDeliveryReportRequested = True ' delivery receipt
    mailItem.ReadReceiptRequested = True ' read receipt
    
    
    'simply add a recipient
    mailItem.recipients.Add "someoneelse@example.com"
    
    'or add one then set some properties
    Set recipient = mailItem.recipients.Add("someone@example.com")
    recipient.Type = olCC
    
    
    'Finally, send our email
    mailItem.Send
    
    'or save it for later
    'mailItem.Save
End Sub

Lotus Notes

After using SMTP and Outlook, using the lotus API is not quite as pretty. Lets be honest, its down-right horrible. Regardless of this, here is the code:


Sub SendMailViaLotusNotes()
   
    'Method:
    ' open a lotus notes session
    ' open the notes database
    ' use the db to create a new mail document
    ' assign the key properties to the mail document
    ' create a rich-text item for the mail body (this allows us to have nicer emails)
    ' write our email body
    ' send the document
   
   'Requires reference to: "Lotus Domino Objects"
   
    Dim dominoSession As New NotesSession
    Dim mailDatabase As NotesDatabase
    Dim newMemo As NotesDocument
    Dim richText As NotesRichTextItem
    Dim strAttachment As String
    dominoSession.Initialize ("")    'Prompts user for Password
    
    Set mailDatabase = dominoSession.GetDatabase("", "names.nsf")
    Set newMemo = mailDatabase.CreateDocument
   
   
    newMemo.AppendItemValue "Form", "Memo"
    newMemo.AppendItemValue "SendTo", "matthew.rathbone@example.co.uk"
    newMemo.AppendItemValue "Subject", "This is the subject"
    
    Set richText = newMemo.CreateRichTextItem("Body")
   
    richText.AppendText "This is my email text"
    
    newMemo.Send False

End Sub

There's probably a better way to do this using the lotus API, or at least I hope there is!

Hopefully these snippets of code have proven useful, feel free to use them in anyting you wish, although whenever possible please credit me with a link. If you have any questions, feel free to contact me.

A List of Useful Things to Know When Dealing With Bloomberg as a Developer

As the snappy title implies, the following items I consider to be useful pieces of information when working with Bloomberg.

1. Don't store any data retrieved via the Bloomberg Client Terminal API

Don't put it in a database, pass it around on excel sheets, use it as a data source for internal systems, or anything like this at all. Doing so will land you in a lot of trouble with Bloomberg for breaking your license agreement, something they take very seriously (can you say 'lawsuit'?)

2. Get end users to upgrade their aging terminal software to take advantage of Bloomberg's real-time Excel formulas.

If your users are still using spreadsheets filled with =BLP() formulas then a huge portion of CPU power on their machines is dedicated to continually refreshing 'static' data. Bloomberg had to use a series of macros and elaborate hacks to simulate real-time data feeds to excel before Microsoft included support for real-time data streaming ( =RTD() ).

Newer versions of Bloomberg software include the =BDP() function which is simply a nice wrapper for Excel's build-in data streaming functionality. Using these new formulas will significantly improve the performance of Excel, and the PC as a whole. Plus sheets with the legacy =BLP formula will still work if you are not going to be able to migrate them.

3. Get a Bloomberg Developer License

Bloomberg offers a special cut-down license for IT professionals who work a lot with the Bloomberg Client Terminal API. This provides you with a limited amount of data through the API and some basic terminal software functionality. It's far from the ideal development environment (I hit my daily data limit by about 11am normally), but it's good enough to make sure what you're doing actually works.

It costs around 1/3 as much as a 'real' terminal license, so you can save a lot of dough.

4. Really understand what fields you're looking at.

Get yourself on a bloomberg terminal, navigate to the main page of an instrument (use "GOOG EQUITY" for a good example) then type "FLDS". You can now search for any field you want and see what value is assigned to it for the current instrument.

If you don't have access to the terminal (hi to fellow developers with restricted licenses) just go to the bloomberg menu in excel and use the 'field search' functionality. It basically does the same thing, except you don't get instrument values, merely a text description of the field.

5. You can use the API for 2 or 3 Days after last logging-in to the Terminal

So you closed your bloomberg Terminal window? Never mind, you can still get data via the API! This data connection stays active until a few days have passed or you log on somewhere else (if you've got an anywhere license).

This means you don't have to have a Bloomberg loaded to use bloomberg-enriched Excel sheets or applications using the API, saving memory, desktop space, and processing power.

6. The bloomberg buttons map to the function keys

Don't put up with a clunky Bloomberg Keyboard if you don't have to, as a developer you can just use your regular keyboard and ask Bloomberg to send you some keyboard stickers. These stickers go around your keys to show you the special bloomberg keys.

The Importance of UI Prototyping

User interface prototyping is a powerful tool, the effective use of which can save hundreds of man-hours, increase the quality of applications and enable better application architecture. The importance of prototyping should not be understated, I've worked on several projects where we wasted a lot of time 'fixing' and 'improving' the look and feel of the application during user testing, this could easily have been avoided if we'd simply PROTOTYPED the user interface first.

In one of these projects our prototyping phase was axed due to tight project deadlines and a perceived user confidence about what they wanted to see and how they wanted it to act. Of course the project ran well over its deadline, and the users had many (many) UI changes once they got their hands on a working copy. These changes were not only to do with application usability, but the organization of key elements, things they now realized were missing, and sometimes fundamental changes to the way things needed to work. Instead of sitting on user confidence at the project outset we should have ignored them completely, pushed back our deadlines and designed the interface properly.

The benefits of prototyping an application are well documented. Jakob Nielson notes that even simple paper based prototyping can dramatically increase ROI, it can also solicit feedback, meaning you can fix problems early, adapt your architecture, or even switch to a different technology.

An argument against prototyping would be that it can take a lot of time or is an unnecessary cost. But think about it this way, if you do simple prototyping it will cost you the time of 1 analyst with basic developer input, alterations are easy to make as you don't have to change any code. In contrast, if UI issues come out in testing you need to document what's wrong, create time-consuming code changes, and retest. In my example, this happened 3 or 4 times before the users were even slightly happy. We could have made an awful lot of paper prototypes with that kind of time investment!

I guess my point is, PROTOTYPE THE UI BEFORE TOUCHING YOUR CODE. The UI is what the user sees, the UI represents your final app. If your UI is terrible, the user won't care how intricate or well made it is underneath the surface and you'll waste a lot of time setting it right. I've learnt the hard way, hopefully you won't have to!

5 Useful Free Windows Power User / Developer Tools

Lots of tools are useful, but here's my favourite 5 tools which I install on every pc I use regularly.

1. Notepad ++

LINK

An extremely useful application which replaces the regular notepad. It has tonnes of language support so that you get colored code in everything from perl to caml to xml. It's VERY quick to load, has a tabbed interface, and is generally lovely to use.

2. Tail

LINK

I'm jealous of a whole bunch of UNIX stuff I don't get access to on a daily basis and tail is one of those things. This windows app lets you 'tail' text files (such as log files), updating the view every time the file changes and always scrolling to the bottom. Debugging through log files is made 100x easier!

3. Winmerge

LINK

Winmerge is the open-source version of 'Beyond Compare' style applications. It allows you to easily compare and merge documents. It's not as feature-rich as beyond compare, but for what this type of tool is typically used for it's absolutely perfect.

4. Google Desktop

LINK

Stay with me on this one! This has improved my productivity 300%, being able to instantly open applications and common files is fantastic. I guess you could also use Windows Live Desktop, or switch to Vista. Personally, I found Google Desktop much faster than the Microsoft alternative.

The most useful bit: Simply press CTRL twice and type:

5. TortoiseSVN

LINK

This is a no-brainer. TortoiseSVN provides incredible Subversion integration into the Windows Shell, it changes folder icons based on item status' handles conflict resolution well, allows you to compare files (even better with Winmerge(see above)), and even enables comparison of Microsoft Office Documents (which is the REAL killer feature. Imagine lengthy functional requirements documents).

BONUS: PuTTY

LINK

SSH into a networked / remote Linux box with ease, you can even do it over t'internet with some help from a dynamic DNS Provider. Not only can you control your 'nix machine, but if you set up some Tunnels you can use it as a http proxy, access the machine's local drives, access shared music and more.

Plus you can make the Windows command prompt extremely jealous.

Be Careful of Data Obtained From Bloomberg using RTD or BDP Functions

The RTD / BDP functions provide real-time data from bloomberg directly to an excel sheet. However if you're subscribing to fields which Bloomberg hasn't designated to be 'real-time enabled' you have to manually refresh bloomberg data for it to have any effect.

Let's look at an example

FUT_CTD_PX (Cheapest to deliver price) is a static FIELD for a real-time VALUE. IE, if you check in the BB terminal this value will be constantly changing, but excel sees it as a static field.

To get the CTD price for a future we can use the following formula:

=BDP("SOME FUTURE'S TICKER", "FUT_CTD_PX")

This field will never update, it will stay static with it's initial value. If the spreadsheet has been open for 5 days, it's the price from 5 days ago which will be displayed.

Microsoft's RTD server model is event driven, in that the data-source notifies the RTD server when a new value is available and that value is picked up by a spreadsheet when excel calls it's RTD.RefreshData method (which it does every 2 seconds by default). In our example, Bloomberg's data-source never notifies the RTD server of a change in value for any of it's STATIC fields, and they therefore remain the same indefinitely.

This also explains why calling RTD.RefreshData from VBA code has no effect, as the RTD server has no new value to pass forwards.

Solutions

The only REAL solution is for Bloomberg to provide such key fields as real-time. Static fields should be reserved for values which do not change on a regular basis, such as instrument name, maturity date, ISIN, and so forth.

As a manual solution, Bloomberg does provide a Excel menu containing data-refresh commands. Clicking 'refresh entire workbook' will eventually update all static values (it can take up to 10 seconds), but only for the =BDP formula. If you're using =RTD you'll have to re-open your workbook or delete and re-type the formulas.

"Can I do this Programatically?" I hear you cry.... Why yes you can!

It's not a graceful solution by any means but you can use one of the following to activate the respective bloomberg command:

Application.Run ("bloombergui.xla!RefreshData") [Default]

Application.Run ("bloombergui.xla!RefreshCurrentSelection")

Application.Run ("bloombergui.xla!RefreshEntireWorksheet")

Application.Run ("bloombergui.xla!RefreshEntireWorkbook")

Application.Run ("bloombergui.xla!RefreshAllWorkbooks")

be sure to wrap any use of these in good error checking to avoid Excel getting mad at the user.

Summary

It's important to be aware that some bloomberg fields are not real-time. If you're using one of these fields for a key calculation you'll have to make sure that you manually refresh regularly, or that you have integrated a crude automation into your code.

Some Easy Ways to Reduce Excel File Size and Increase Reliability

If you have an excel file over 10mb in size excel could become unstable and it will take forever to save, backup, or pretty much do anything with. It will also use significantly more memory, degrading the performance of your machine as a whole.

I've worked in several offices dealing with excel files pushing 30mb. The reality is that these files need only be around 3 or 4mb. Regardless of how many rows of data you may be storing.

Here are my key tips to reducing the size of your excel file:

1. Remove pictures

Shadows, images, and drawings all increase file size, drawing objects created by adding 3d effects or shadows have the worst effect as they're just stored as (large) pictures. If you've added shadows to some cells, remove them.

Coloring cells also has a negative effect on file size, although more limited.

2. Delete unwanted Rows

This sounds silly, but it has a massive effect on file size.

Here's a really simple example:
I've created a workbook with 3 sheets, each with 1000 records covering 18 columns. if I write some simple text to cell 65000 in each sheet the file size jumps by 500kb.

Excel sometimes seems to extend the lower boundry of worksheets without any real reason, so if you're working with large files I suggest you check to see if there are reams of unused rows.
I've reduced the size of one particular file from 24mb to 1.5mb using only this tactic.

This also leads me on to my third point

3. Don't let macro's loop over every cell in a row / column

There are plenty of macros that will perform an assessment on every single cell in a particular column, all the way down to row 65k

Not only will this make it take about 20 minutes to run, but it can easily trick excel into thinking that it needs to save the contents of every single row (see point 2).
Here's a simple loop which will only iterate over cells if they're occupied (your data will have to be in a proper table-structure):

dim usefulCell as Range 

set usefulCell = SampleSheet.Range(col1_header_range_name)

Do until usefulCell.Value = ""

	'Do some stuff here, for example:

	CustomerObject.Name = usefulcell.Value

	CustomerObject.Address = usefulcell.offset(0, 1).Value

	CustomerObject.Age = usefulcell.offset(0, 2).Value

	'etc

	set usefulCell = usefulCell.offset(1, 0)

Loop

4. Don't do things twice

Don't have more formulae than you have to. If you're relying on a specific calculation multiple times, only calculate it once, then refer to that calculation in subsequent cells.

Don't replicate data, there's no point copying huge wads of data from one worksheet to another, reorganize so that you only need it in one place

Use static values wherever possible: If data comes from some external source, milk that source to the max. Anything you are calculating which could be imported SHOULD be imported, calculations increase file size and reduce performance.

I hope these tips help to make your excel user experience a better one. Please email me with any questions or comments.

Dependency Injection / Inversion of Control with VBA

Regardless of how much time Microsoft invests into developing Visual Studio Tools for Office there’s still going to be a market for VBA, but that doesn’t mean we have to compromise our methodologies or best practices.

Dependency injection in VBA is not only possible, but it’s not as much work to implement as you might think. Sure VB6’s system for Interface implementation leaves a lot to be desired, and it’s a whole lot less flexible than modern languages, but you can still add a massive amount of flexibility and control to your code through a little thought and planning.

There are a few key things we need for a basic IoC implementation.

1. Interfaces for key dependencies that we want to ‘inject’ (for example a data-repository)
2. A centralized way to change what concrete object is mapped to that interface by default
3. A way to inject that concrete object upon creation of a dependant object (as VBA does not allow you to pass parameters on construction)

Let’s address how to do each of these things in turn:

Interfaces

Interfaces in VBA are created by making a class and populating it with stub methods and properties which don’t do anything, that’s it. You don’t have to declare it as an interface, but it would probably make sense to prefix the name with an ‘I’.
For example, here’s an interface for IDataSource:


Option Explicit
Public Function GetOrders(customerID As String) As collection
End Function
Public Function GetAllOrders() As collection

End Function

Easy!

And here’s a basic example implementation of that interface:

Option Explicit
Implements IDatasource
Public Function IDatasource_GetOrders(customerID As String) As collection
	Dim returnCollection As New collection

	returnCollection.Add ("Item1")
	'Collection logic here
	Set IDatasource_GetOrders = returnCollection
End Function

Public Function IDatasource_GetAllOrders() As collection
	Dim returnCollection As New collection

	returnCollection.Add ("Item1")
	returnCollection.Add ("Item2")
	'Collection logic here
	Set IDatasource_GetAllOrders = returnCollection

End Function

Object Mapping

Because we don’t have use of Unity, Spring.net, or any other IoC framework, the simplest thing to do is to completely dedicate a module to the job of being an ‘object provider’

Because VBA doesn’t need to be compiled, it’s almost as easy as using a configuration file as you can change it on the fly without having to recompile and redeploy.

Additionally, if you want to externalize the assignment of concrete objects to a configuration file, you can just extend this basic object.

Here is an example of a simple object provider:

Option Explicit
'set your bindings here
Private mDataSource As New SqlDataSource
Public Property Get DataSource() As IDatasource
Set DataSource = mDataSource
End Property

All you have to do is chage the type of the private members to switch the default concrete class.

Dependency Injection

VBA doesn’t allow us to have non-default class constructors, which means we cannot construct objects like below:

Dim dataService as new OrderService(SqlRepository)

Instead we simple have to be a little more creative with our objects, I tend to add a method called Initialize which takes a IDataRepository class. If you haven’t initialized the class, no methods that rely on this private member will work, which acts as a failsafe:

Dim repository as IOrderRepository
Dim dataService as new ObjectService
Set repository = ObjectProvider.OrderRepository
dataService.Initialize repository

Summary

Coding VBA can be frustrating and confusing when one is used to more elegant languages and frameworks, but that doesn’t mean it’s impossible to import modern programming strategies and methodologies.

Using inversion of control in any framework promotes code reuse, separates concerns, and makes for more manageable maintenance going forwards. Working with an outdated framework does not mean one has to write code like it’s 1995.

Stack Overflow is Fantastic

I've just started delving into StackOverflow.com, and I think it's great! Good job all involved!

I know it's made by mr Coding Horror himself, so it always had a lot of promise, but it's easy to use, responsive, well thought out, and ADDICTIVE!

For those who don't know, it's basically the Yahoo Answers for programmers, except that it has extra addictive features, it's very fast and some of the answers that come out of it are just pure quality.

The idea of the 'badges' is great, makes you almost feel like you're in a game trying to score points, you earn a badge when you accomplish something, it's like being in a MMORPG but instead of killing 3000 lizards you're letting someone know how to write graceful REGEX patterns.

I suggest everyone check it out

Using Range.offset in VSTO for Excel

Range.Offset[a, b].value , when using VBA is a great way to add values to adjacent cells without having to actually select every cell you write to (which is very slow as it requires the UI). When I started using VSTO I could only find the Offset method attached to the strongly typed Named_Range object.

Luckily, it does exist for a regular range, except the method is called get_Offset (obviously).

So there you have it. Sounds simple, but this has been frustrating me for a couple of weeks now.

rangeObject.get_Offset[1, 2];

Why You Should Use Recordsets and Object Orientation in VBA Instead of Arrays

When I starting working with VBA 4 years ago arrays were my best friends, they’re easy to use, easy to understand, and getting data to / from an Excel sheet is a piece of cake as cell (0, 1) can map nicely to array position 0, 1. However I’ve developed the opinion that arrays should be avoided in favour of other forms of data lists / collections.

What’s Wrong With Arrays

It’s not that arrays are particularly wrong, it’s more that in comparison to the alternatives they’re limited and frustrating to use and support;

· Size declaration – You have to declare how big you’re array is going to be, even if you don’t know yet. This is a key limitation regardless of the fact you can ‘re-declare’ to increase the size.

· No in-built sorting / filters / search functions. An array is an array, it doesn’t have any methods associated with it. It doesn’t even have it’s only object type.

· It can only hold strings, numeric values, characters etc.

· Moving through an array is arduous with large amounts of data and there’s no standard ‘structure’ an array must take (I can declare a 0 based array, a co-worker may declare a 1 based array, and there’s no standard slot for each piece of information etc)

So What are the Alternatives?

ADODB.RECORDSETS

Instead of using arrays to store string and numerical values I’ve recently been using ADODB.recordsets (recordsets).

Recordsets are slightly more object oriented than arrays, as in essence they are collections of record objects, although they can be manipulated without even using or referring to the record object.

Benefits of Recordsets:

· No size declaration, you can type myRecordset.AddNew to create a new record and populate it with values.

· You define fields to specify what data is where, instead of having to remember which position in the array the address is you can just move to a record and use myRecordset.Fields(“myField”).value to retrieve it.

· You also don’t have to iterate through a recordset to output the contents to a worksheet:

‘ Move to the beginning of the recordset

myRecordset.MoveFirst

‘output the contents to a worksheet starting at cell A1

someWorksheet.Range(“A1”).value = myRecordset.GetRows()

· They have inbuilt search functions

· They’re easier to iterate over if you need to, or you can do bulk updates

· The list goes on....

There are of course limitations to recordsets, they’re frustrating to work out at times, and can be counter intuitive, but I’ve found the benefits of having a more structured set of data are fantastic.

Add Microsoft ActiveX Data Objects Library 2.8 to your VBA references and have a play.

OBJECTS AND COLLECTIONS

If you’ve declared a customer object and given that customer properties such as FirstName, LastName and CustomerID there’s no confusion on how to find that information.

If you combine that benefit with the use of the Collection object you make your code so much easier to understand, use and support.

Collections and object lists figure more in strongly typed object oriented languages such as C# than in VBA, but we can still take advantage of VBA’s generic collection object.

Dim c as New Collection

The more you object orient your code in VBA the more you’ll use collections, and the easier your life will become, you won’t have to pass around huge arrays and remember between procedures which attribute is where, as you’ll have strongly typed objects to work from and the re-use and quality of your code will vastly improve.

In the near future I’ll post a getting started guide to object orientation in VBA so that these benefits can reveal themselves more clearly.

Overall object orientation is the best path to take for storing data, however, even the use of recordsets can drastically improve the speed, reliability, supportability and expandability of your code.

ASP.NET rejects the google crawler with 'NETWORK UNAVAILABLE' error

This has happened to me with this site, and seems to be a common problem, but fear not, help is at hand.

According to Brendan Kowitz this is due to ASP.NET being unable to match the googlebot's browser signiture and thus defaulting it to the 'MOZILLA 1.0' setting.

He's posted a solution to the problem, and you know what, it works! The solution is a simple genericMozilla.browser file which you need to include in your site. It also solves similar problems with other search engines.

You can check what response google will receive from your site with this tool.

Through the tool I can see that my site now returns the correct HTTP/1.1 200 OK status message, whereas before it was returning a 500 error.

Hope this helps someone.

Comparing the contents of two tables from two different databases in SQL Server

When you don't have any sql server analysis tools to hand comparing the data from two tables can be a pain, especially when those tables are in different databases, or even completely different servers. I've found myself in this position several times so eventually I decided to just write myself a small VBScript which can extract the data for me and load up my favourite diff tool to compare the contents.

Using the VB Script:

First you need to set the constants at the top of the vbs file to tell it where to get the data and where to find the compare engine exe:

'Table 2--------------------------------------------------------------------------------------------

const t2Server          = "ExampleSqlServer2"

const t2Database        = "MyExampleDatabase2"

const t2Table           = "ReplicatedCustomerOrders"

const t2Columns         = "OrderID, ProductID, OriginalQty"

const t2WhereClause     = "ProductID is not null"

    ' comment out one or the other of below depending on how you plan to connect. Default is windows authentication

const t2Security        = "Trusted_Connection=Yes"

'const t2Security       = "User ID =enterusernamehere; Password=mypassword "

'------------------------------------------------------------------------------------------------

'Export Options

const defaultExportLocation = "C:\databasecompareexports\"

'Comparison Application-------------------------------------------------------------

const comparisonEngine = "C:\windiff.exe"

'-------------------------------------------------------------------------------------------

Secondly you need to make sure the execution string for the comparison tool is correct, I’ve tested it working with Windiff.

If you need to change the execution string it’s on line 115:

sCompare = comparisonEngine & " " & defaultExportLocation & "1.output " & defaultExportLocation & "2.output"

wShell.Run(sCompare)

That's it! If you're using it with WinDiff it will work using only step 1!

Debugging

Throughout the script there are calls to a logging function which is present at the bottom of the script. Currently all this does is write a line to the windows application event log.

sub LogMyEvent(eventType, eventMessage)

wShell.LogEvent eventType, eventMessage

end sub

You can find the event log by right clicking my computer, going to 'Manage' then goto 'Event Viewer' 'Application'. Because the script calls this procedure and not the shell logger directly you can replace this with whatever process you wish! Hooray!

Remember that VB scripts have different error handling capabilities than regular VB6 or VBA, in other words you can't GOTO anywhere on error, it either breaks or carries on. So you have to manually check for errors at every important point. You can see this pattern throughout the script.

The File

I'm storing the file as a .txt file, don't ever trust .vbs files you download from the internet, they can do some nasty things to your computer! While you can trust my script, I'd recommend checking ALL VB scripts out first with notepad++ before using them.

HERE IS THE FILE

If you’re not familiar with comparison tools check out Windiff (free), Compare It! (not free, but the best) and WinMerge (also free).

Enjoy!

Creating COM Compatible Events in C Sharp For Use in VB6 or VBA

Creating C# DLL's for use with COM is not too hard, however event exposure can be a little messy especially when trying to subscribe to events in vb6 or vba.

Here's an example of an everyday COM exposed class:

ComVisible(true)
ClassInterface(ClassInterfaceType.AutoDual)
public class CComInterop

We've integrated automatic interface generation so we don't have to make our own. However, this interface type does not expose events.

The solution is to create a separate interface to expose the events but no other properties / methods of the class. Using some jiggery pokery it is also possible to integrate the contents of this interface into the class itself so that you don't have to deal with multiple objects on the COM side.

METHOD:

this is our event, and the event's delegate:

public event SomeEventHandler SomeEvent;

public delegate void SomeEventHandler(string teststring);

To expose this event we create the following interface:

ComVisible(true)
InterfaceTypeAttribute(ComInterfaceType.InterfaceIsIDispatch)
public interface IComInteropEvents
{
void SomeEvent(string teststring);
}

The important part is that we specify the interface type as IDispatch.

But we don't want to have to declare a separate object for events, so we add a 'ComSourceInterfacesAttribute' referencing this interface, like so:

ComVisible(true)
ComSourceInterfacesAttribute("Tam.Common.LzLibrary.IComInteropEvents")
ClassInterface(ClassInterfaceType.AutoDual)
public class CComInterop

Now we can use the object's events in vb6 or vba without having to declare separate objects!

Public WithEvents InteropObject As Some_Library.CComInterop

and you can still use the methods and properties of the object normally with all intellisense intact.

Full sample Class and interface shown below:

ComVisible(true)
InterfaceTypeAttribute(ComInterfaceType.InterfaceIsIDispatch)
public interface IComInteropEvents
{
void SomeEvent(string teststring);
}

public delegate void SomeEventHandler(string teststring);

ComVisible(true)
ComSourceInterfacesAttribute("Tam.Common.LzLibrary.IComInteropEvents")
ClassInterface(ClassInterfaceType.AutoDual)
public class CComInterop
{
private int m_nSomeProperty = 0;
public event SomeEventHandler SomeEvent;

public void SomeMethod(int nValue)
{
m_nSomeProperty = nValue;
// Raises the event
SomeEvent("Hello");
}

public int SomeProperty
{
get { return m_nSomeProperty; }
set { m_nSomeProperty = value; }
}
}

Enjoy!

Converting Bloomberg data from an array to a recordset using VBA in Excel

I have found whilst working in financial institutions that I am continually called upon to integrate Bloomberg market data into spreadsheets using VBA in Excel. While the Bloomberg API returns data in an array, I tend to work with recordsets when dealing with large sets of data. Because of this I have some standard code which I reuse in all my Bloomberg projects to fairly efficiently convert bloomberg results arrays into recordsets.

ADODB.Recordsets have advantages over arrays for storing larger volumes of data, such as in built sorting and searching methods, and while converting an array into a recordset may seem like a waste of time it can make working with large volumes of Bloomberg data a lot easier.

Implementing this function is very simple, you simply loop through the array and write the relevent values to the recordset after jigging about with the format of the original array.

Here is the code complete with an example of retrieving data from the Bloomberg API:

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Function: BBArrayToRecordset
' Required Reference to: Microsoft ActiveX DataObjects
' Author: Matthew Rathbone
'
' Description: This function takes a results array as output by the Bloomberg API and converts it
' into a ADODB.recordset
'
' Copyright: Matthew Rathbone: Free for commercial and non-commercial use.
' Links to www.matthewrathbone.com are always appreciated
''''''''''''''''''''''''''''''''''''''''''''''''''''''
Public Function BBArrayToRecordset(fieldList As Variant, dataArray As Variant) As ADODB.Recordset

    'declare our return object
    Dim returnSet As ADODB.Recordset
   
    ' string to hold our list of fields
    Dim fieldString As String
   
    'string to hold our list of records
    Dim recordString As String
   
    Dim rDataArray As Variant
    ReDim rDataArray(0 To UBound(dataArray, 2))
   
    'convert the fields array to a string
    fieldString = Strings.Join(fieldList, ", ")
   
    'construct our return object
    Set returnSet = New ADODB.Recordset
   
    ' lets make our recordset! Looping and adding fields
    For i = 0 To UBound(fieldList, 1)
        returnSet.Fields.Append fieldList(i), adVarChar, 500
    Next
   
    'open the recordset
    returnSet.Open
        ' lets fill our recordset!
        For i = 0 To UBound(dataArray, 1)
            For ii = 0 To UBound(dataArray, 2)
                rDataArray(ii) = dataArray(i, ii)
            Next
            returnSet.AddNew fieldList, rDataArray
        Next
       
   
    'return the recordset
    Set BBArrayToRecordset = returnSet
   
End Function

'Example of it's use
'THIS EXAMPLE REQUIRES THE BLOOMBERG DATA TYPE LIBRARY REFERENCE
Public Sub exampleExecution()

    Dim oBlp                    As BlpData
    Dim aResult                 As Variant
    Dim aTickerList             As Variant
    Dim aFieldList              As Variant
    Dim exampleRecordset        As ADODB.Recordset
   
    'defining the securities we want data for
    ReDim aTickerList(0 To 1)
    aTickerList(0) = "AMGN EQUITY"
    aTickerList(1) = "GOOG EQUITY"
   
    'defining the fields we want to retrieve
    ReDim aFieldList(0 To 1)
    aFieldList(0) = "NAME"
    aFieldList(1) = "PX_CLOSE"
   
        'initialize our bloomberg data object
        Set oBlp = New BlpData
       
        'we want a single subscription to bloomberg (will not fire events ongoing instead populates our result array)
        With oBlp
            .Timeout = 40000
            .SubscriptionMode = ByRequest
            .Subscribe aTickerList, 3, aFieldList, , , aResult
        End With
   
    Set oBlp = Nothing
    Set exampleRecordset = BBArrayToRecordset(aFieldList, aResult)
       
   
End Sub

Link to the actual code module

Site Launched

It's sunday the 3rd August. MatthewRathbone.com is now live.

It looks pretty sparse at the moment, that's because I'm going to add features and content as and when I get time.

The site has been made from scratch (blogging engine included), and has been deliberately overengineered as a way for me to practice my asp.net skills. It will continue to serve this purpose going forwads with new features chosen due to what I can learn through making them.

In terms of this blog, I plan to post useful tips & tricks on linux / VBA / ASP.net / C# etc based on things I found difficult to work out, or which I found little writing on.

I hope whoever reads this enjoys it, there's no commenting enabled for the blog posts yet, so if you need to let me know that I've said something incorrect, or that I'm just plain wrong about something please email me through the contact page.

Thanks all,

Matthew Rathbone