Text to columns

As part of my new gig, I’m delivering excel classes to the wider business department. It’s amazing the differing levels of exposure some staff have to excel and data as a whole. Things as simple as Ctrl+C have passed some staff by, whilst other staff members are teaching me things. One of the simplest, yet palm-to-face inducing thing is “text to columns”. I wrote a macro to split out some columns data! (it’s the .split function kids)


The moral of the story – CLICK EVERY BUTTON, even the big red one.

It’s quite intuitive:

  • Select your column and how you’d like to split the text – by fixed width that you specify or by delimiter.
  • You can select multiple delimiters too.
  • Then select a cell you want the data to end up in (careful not to overwrite your existing data!)



Using Microsoft Outlook to AutoFil Database

This week I experienced that joy you have when you finally unpick something you’ve been trying to crack for ages. I’m so glad I kept persevering with it!

As part of a beta test for a database I have now launched, some feedback was given that I could auto fill information from emails to complete the database entry, rather than allow the user to manually provide the information and then upload the email. It’s great to work with creative people that come up with new ideas. However, this idea was a very hard nut to crack. Most of the material online is about creating emails, rather than reading emails. But eventually, after lots of reading and trawling, I managed to find the commands that meant I could read these emails.

Dim myOlApp As New Outlook.Application
Dim myItem As Object
Set myItem = myOlApp.ActiveInspector.currentItem
With myItem

The third line, ActiveInspector.CurrentItem works if the user has one email open in it’s own window, then you can use “With myItem” and it’s parameters (Subject, To, MessageBody….) to get the data from the email!

There’s still a couple of things left to understand surrounding this problem:

  • How do I export/import the email from the outlook .ost file to the folder I’m storing it in the database?
  • What pop ups or displays do I need to make sure the staff open the right emails?

Game ON!

So, as part of data scientists toolbox, I got introduced to Scratch, an online programming tool, that’s targeted for kids, but fun for all. As any adult who’s been to a museum and seen the boring dry placs placed by phenomenonally important artefacts and compared them to the fun worksheets and interactive displays made for kids will know, stuff created for kids is FUN! For those of us who already find coding fun, scrach just makes it more enjoyable. So go ahead and have a go – make a game and check out the hall of fame of games, such as a copy of dance dance revolution!!

My contribution combines programming with another hobby of mine Korfball. It’s a Dutch sport hardly anyone’s every heard of, that is a cross between netball and basketball and is said to be the only sport free of gender inequality as both girls and boys get to shoot, defend, assist and most importantly, run around having fun!

Check out my game here!

shoot the korf

Dashing off a Dashboard

My top five reasons:

1. When you’ve got a data table that no-one is able to digest. Big tables with lots of rows, columns. Tables containing lots of repeated values, or values that have high noise levels.

2. When you want none technical mangers to understand the importance of technical values – this allows you to present really complicated technical values in simple, almost vector form. e.g. “this is too big/small” OR “these values are getting bigger – that’s good”

3. When you want to spark discussion and analysis – especially if the team are somewhat distanced from the data. This has benefits as it allows staff to come outside their rigorous box of rules and think creatively.

4. For allowing those staff in your team who lack confidence in their mathematical skills – the power of being able to quickly comprehend lots of data can boost self perception.

5. To break out the big number crunchers. Magnificently satisfying to really dig through the data and determine underlying causes, correlations, insights and conclusions. Work that data baby.

And always; remember your argument, clarify your point, declutter!


As you might know, from my recent posts, I’ve been taking the Data Science Specialisation through Coursera with Johns Hopkins University. I’m pleased to say that I’ve passed the first module “Data Scientists Toolbox”, with distinction! I can be smug for five minutes until I think about how this was the easy introductory module and the next one “R Programming” is already taking me for 6!

Who needs sleep anyway?

Having spent a while getting to grips with VBA and generally speaking being able to run up some nifty bits of code, I’ve been wondering what next? I’m always keen to learn new things, be it bsl, ciaxa samba patterns, knitting topologically cool scarf’s or how to run. There are so much online these days, in the super connected land I call home, it is only time and my personal capacity limiting me. Super modelling geeks at work use comsol and mathlab both of which yield gems, but you need expensive packages to run. In the data scientist world, it looks like R and python might be the most useful tools. Edx, Khan Academy and Coursea all offer structured programs from excellent universities across the globe. Last year I tried introduction to computer science through Edx. The first lecture was a breeze, the second harder, giving me opportunity to run up a game in scratch (so much fun!!!). The third lecture was unintelligible, even the walk troughs were baffling. This year sees me undertaking data scientists toolbox through Coursera. They’ve got a data scientists specialisation program and the courses are all split into really manageable chunks that run repeatedly all year.
So, here goes – pass me the coffee and let’s get started!!!

What I’m Reading Today

In a bid to beef up my data knowledge, today I’m reading:

Thank you Microsoft

Microsoft yesterday released a whole bunch of updates to various things. For the first time ever, these updates have caused me no end of headaches.

I’m currently working on a database which uses ODBC storage, SQL Backend and Access front end. Aside from retrospectively finding out access is a bit of a pain and missing the wonderful functionality of excel nested formulas, it’s coming together nicely.

Up until now, my own mistakes in Macros and the delicate web of tables has been my biggest problem. But today I struggled for hours with a problem that didn’t exist last night only to find out that it was a data type issue in an odbc table. The default date changed from the British to the American, as a result my data wouldn’t save and my macros went through error handling. Thanks Microsoft, thanks.

In actual fact, I had no idea it was even Microsoft’s fault until I was moaning about the sheer futility of it to a friend who suggested it could be the updates. Yet another reason why having a good team around you and good communication is invaluable in problem solving.

The Mathematics of Music

A Review of a Masters Thesis:

It’s widely known that mathematics and music hold commonalities; this thesis aims to have a gander at how, the use of series is found in music and whether or not mathematical operations can be used to generate series of notes that can happily be called music. Along with the premise that music could also be represented mathematically.

The author investigates, using trial and error, easily found in a mathematicians and composers waste bins, how a set of notes can be transformed using mathematical operators. The work is set out clearly, giving readers good overviews of the musical and mathematical theories to be used.

The author succeeds in producing a little ditty of merit. The real gem for me however, is the section on representing music in mathematical graphical functions. As readers of this site will come to learn, I LOVE graphs. They have the power to unlock insights in data that may otherwise be lost in tables of statistical analysis. Although I agree with Mr T that a table is often the best way to present data.
This section of the thesis aims to use graphical structures to represent the complex nature of music. However, the author rightly concludes that this is difficult as a musical note has so many variables (volume, pitch, tone, length – not to say anything for the more complex workings of music). But the lightbulb moment for me was when I realised that the musical score is in fact structural representation of music! The stave is a graph!!

The fact that something as complicated as a piece of music can easily and effectively be communicated by the use of shapes, lines and symbols, which distinctly each have separate purposes to indicate ideas further iterates how fantastic graphs, maths and music really are!

Excel Problem: Unreadable Content in Workbook

Recently, I created a really nifty excel file that allows staff to easily look up and visualise data that previously had really complicated automatic file names. Then I came to finesse the beast. I wanted it locked down, read only and singing like a bird. So my VB macro grew – remembering to keep up my good habits, (See previous post about ways to speed up your VB Code).

But in my aim for brilliance, I forgot that excel has some evil little niggles and my excel spreadsheet stumbled and fell.

        Problem: Error pop-up box followed by error report


<removedRecords summary=”Following is a list of removed records:”><removedRecord>Removed Records: Sorting from /xl/tables/table1.xml part (Table)</removedRecord>

The problem is that my macro sorts a table in the file – easy enough. Weird when you consider that this is a standard VB function. For some reason Microsoft doesn’t have a solution to this yet, not even listing it as an issue on their site. I’ve found a good number of questions about it online, but the only suggested solution I’ve found is to use a filter instead of a sort – which only really works in a few instances (not for mine). I managed to stop it trowing up errors by disabling the sort. All well and good except I need to sort my data!

Microsoft has released a number of hotfix patches for the same error with different causes, fingers crossed they get round to understanding the bug in the system and fix it for the next release.
What’s the biggest issue you’re having with vba at the moment? Have you found any problems microsoft haven’t fixed?