Excel function to count the number of matching words in two cells (‘fuzzy matching’)

I had a requirement to do a ‘fuzzy match’ of two columns of data, based on the number of matching words in each.

Add the following code to your Visual Basic module, and then in your Excel spreadsheet, do something like



Public Function FuzzyMatch(name1 As String, name2 As String) As Integer

    Dim strings1() As String
    Dim strings2() As String

    Dim string1 As Variant
    Dim string2 As Variant

    Dim matches As Integer

    name1 = StrippedName(name1)
    name2 = StrippedName(name2)

    strings1 = Split(name1, " ")
    strings2 = Split(name2, " ")

    matches = 0

    For Each string1 In strings1
        For Each string2 In strings2
            If LCase(string1) = LCase(string2) Then
                matches = matches + 1
            End If

    StoreMatch = matches
End Function

Public Function StrippedName(name As String)
    name = Replace(name, ",", " ")
    name = Replace(name, ".", " ")
    name = Replace(name, "  ", " ")
    name = Replace(name, "  ", " ")

    StrippedName = name
End Function
Posted in Uncategorized | Leave a comment

Installing Windows 10 over Linux (Ubuntu)

I have an Intel NUC that I’d installed Ubuntu on, and now I have decided to install Windows 10 over the top of it so the kids can use it.

I have just spent a merry two hours trying to work this out – so here are some top tips for anyone else trying to sort this. You’ll need another PC to work alongside the one you’re reformatting, and a couple of USB sticks (ideally 3).

  1. Make sure you’ve bought a Windows 10 licence key before you do anything!
  2. You will need to wipe your hard disk completely – this is because the partitions needed by Windows are in a different format.
  3. Follow the instructions at https://msdn.microsoft.com/en-gb/windows/hardware/commercialize/manufacture/desktop/winpe-create-usb-bootable-drive to make a bootable USB drive. Stick this USB into the machine you want to repurpose, and boot from that USB
  4. Follow the instructions at https://msdn.microsoft.com/en-gb/windows/hardware/commercialize/manufacture/desktop/configure-uefigpt-based-hard-drive-partitions#relatedsamplefiles – I found a separate USB stick was good to install this. I also had to use guesswork to find out what drive letter it was on. Also, unplug your Windows PE USB before running the DiskPart script
  5. Make a Windows 10 install USB at https://www.microsoft.com/en-gb/software-download/windows10
  6. Boot the new PC with that
  7. After putting in your licence key, choose ‘Custom Install’ and pick the Windows drive
  8. Oh, and it takes ages
Posted in Uncategorized | Leave a comment

Updating Rails / MySQL database datetime fields

We have a demo site that we show to prospective clients, and we reset it occasionally using a ‘seed database’ that we have created to give realistic reporting data.

The problem is that the timestamps on the seed database get old, which means that when you’re demo-ing, some of the reports and charts look bad.

So .. here is a little Ruby on Rails utility script that can get you an up to date looking database. It is VERY hacky so do be careful. In particular, any datetime fields you’ve created that might represent a future date will need to be excluded (we have one called ‘deadline’, you may have more.

First – back up your live demo database. It is quite easy to have run database migrations since you created your seed database.

Now, load your seed database onto your dev environment and run

rake db:migrate

And then back this up. Next, run

rails c

and paste the following into the rails command line

def update_database_times
puts "SELECT @most_recent_event := FROM_UNIXTIME(0);"
ActiveRecord::Base.descendants.each do |d|
next unless d.table_exists?
d.columns_hash.each do |k,v|
next if k == 'deadline'
puts "SELECT @most_recent_event := GREATEST(IFNULL(MAX(#{k}),@most_recent_event),@most_recent_event) FROM #{d.table_name};" if v.type == :datetime

puts "SELECT @yesterday := SUBDATE(NOW(),1);"
puts "SELECT @days_to_add := GREATEST(0,DATEDIFF(@yesterday,@most_recent_event));"
puts "SELECT @days_to_add := IF(caption='demo',@days_to_add,0) FROM clients;"

ActiveRecord::Base.descendants.each do |d|
next unless d.table_exists?
d.columns_hash.each do |k,v|
puts "UPDATE #{d.table_name} SET #{k} = DATE_ADD(#{k}, INTERVAL @days_to_add DAY);" if v.type == :datetime


Fire up the rails console in the project you are working with. Then paste in the code above, and run it using


This will generate output that will look something like this:

SELECT @most_recent_event := FROM_UNIXTIME(0);
SELECT @most_recent_event := GREATEST(IFNULL(MAX(created_at),@most_recent_event),@most_recent_event) FROM users;
SELECT @most_recent_event := GREATEST(IFNULL(MAX(updated_at),@most_recent_event),@most_recent_event) FROM users;
SELECT @most_recent_event := GREATEST(IFNULL(MAX(created_at),@most_recent_event),@most_recent_event) FROM tasks;
SELECT @most_recent_event := GREATEST(IFNULL(MAX(updated_at),@most_recent_event),@most_recent_event) FROM tasks;
# more lines like these..
SELECT @yesterday := SUBDATE(NOW(),1);
SELECT @days_to_add := GREATEST(0,DATEDIFF(@yesterday,@most_recent_event));
SELECT @days_to_add := IF(caption='demo',@days_to_add,0) FROM clients;
UPDATE users SET created_at = DATE_ADD(created_at, INTERVAL @days_to_add DAY);
UPDATE users SET updated_at = DATE_ADD(updated_at, INTERVAL @days_to_add DAY);
UPDATE tasks SET created_at = DATE_ADD(created_at, INTERVAL @days_to_add DAY);
UPDATE tasks SET updated_at = DATE_ADD(updated_at, INTERVAL @days_to_add DAY);
# more lines like these..

We have put a protection line in where if the caption field in the clients database is not ‘demo’, then don’t change the datetime.

Now – try firing up your dev environment to check the database works. When you’re happy, copy it over to the demo environmnent. Add any new functionality and then back up that database to make it the new seed database.

Be careful; back everything up ahead of times, practice on localhost, and let me know if you’ve got any suggested improvements. Enjoy!

Posted in Uncategorized | Leave a comment

Some tips on getting Windows 10 to run VirtualBox with Ubuntu 14.04 for Rails

Here are some commands I found useful setting up my virtual machine ready for Ruby on Rails – as usual, exercise your own caution!

Host system

  • Windows 10
  • VirtualBox 4.3.30

Virtual Machine

  • Ubuntu 14.04

Set up a new unix user ready for VirtualBox (better than using the default vagrant user):

sudo adduser pete
sudo usermod -aG sudo pete

Shared folders appear on the guest OS in the /media folder with a prefix sf_ and with group vboxsf so set this up

sudo adduser pete vboxsf

Make sure you do this as the user account you’ll want to run Ruby as!

su pete

Get Ubuntu up to date!

sudo apt-get update
sudo apt-get upgrade

Run these again until you don’t get errors!

Get the copy-paste and folder sharing capabilities working

sudo apt-get install virtualbox-guest-dkms

Install ruby:

Instructions from Setup Ruby On Rails on Ubuntu 14.04 Trusty Tahr at https://gorails.com/setup/ubuntu/14.04 are really good.

Only differences:

Where it says

curl -L https://get.rvm.io | bash -s stable

You will get an message – you’ll need to do something like

gpg --keyserver hkp://keys.gnupg.net --recv-keys 409B6B17...2D39DC0E3

Where it says

git config --global user.name "YOUR NAME"

The YOUR NAME bit is your github user name

Installing nodejs has changed – instead of

sudo add-apt-repository ppa:chris-lea/node.js
sudo apt-get update
sudo apt-get install nodejs


curl -sL https://deb.nodesource.com/setup_0.12 | sudo bash -
sudo apt-get install nodejs

Mapping a folder in your home directory to the directory shared via host machine – e.g.

cd ~
ln -s /media/sf_RubymineProjects RubymineProjects
Posted in Techy Stuff | Leave a comment

Owncloud upgrade when data directory is not in standard htdocs folder

When I installed owncloud on our Debian Jessie server I didn’t like the way the data directory was under the htdocs path, so I did a custom install and created an owncloud-specific apache config file (owncloud.conf) in the apache sites-available directory (note we are running Apache 2.4) like this:

<VirtualHost *:443>
        ServerName myowcloud.mydomain.net

        DocumentRoot /var/mysites/owncloud/htdocs

        <Directory "/var/mysites/owncloud/htdocs">
                Options +FollowSymLinks
                AllowOverride All

        SSLEngine On
        SSLCertificateFile /path/to/mydomain.cer
        SSLCertificateChainFile /path/to/optional/rapidsslca.cer
        SSLCertificateKeyFile /path/to/mydomain.key.2014

        ErrorLog ${APACHE_LOG_DIR}/error-mydomain-ssl.log

        LogLevel warn

        CustomLog ${APACHE_LOG_DIR}/access-mydomain-ssl.log combined

Then you can use a2ensite owncloud to enable the site and apache2ctl graceful to restart apache with the new server fired up. I recommend apache2ctl configtest first. And apache2ctl -S tells you if the new config file has been used successfully.

The problem is that this breaks apt-get update owncloud which tries to install in the standard directory – and ends up just installing a fresh copy of owncloud and breaking Apache.

I would recommend you back up your data files first.

Below is the main shell script (which I stress I accept NO warranty for, etc!) that I wrote to automate this. You will need to have some unix skills to adapt this for your own solution, but here it is.


echo "This script will upgrade owncloud if you have it installed in a non-standard directory"
echo "This assumes the data directory is outside of the install folder"

MYSQL_BACKUP_FILENAME=/tmp/owncloud-sqlbkp_`date +"%Y%m%d"`.sql

CheckRunningAsRoot() {
  if [ "$(id -u)" != "0" ]; then
      echo "Error: This script must be run as root"
      exit 2

CheckPathExists() {
  if [ ! -d "$1" ]; then
      echo "Error: Directory $1 does not exist."
      exit 2

CheckPathDoesNotExist() {
  if [ -d "$1" ]; then
      echo "Error: Directory $1 already exists."
      exit 2

CheckFileExists() {
  if [ ! -f "$1" ]; then
      echo "Error: File $1 does not exist."
      exit 2

CheckFileDoesNotExist() {
  if [ -f "$1" ]; then
	echo "Error: File $1 already exists."
	exit 2

CheckRemoteFileExists() {
  if curl --output /dev/null --silent --head --fail "${1}"
      echo "URL ${1} exists"
      echo "Error: URL ${1} does not exist"
      exit 2

CheckDataFileNotInSubfolder() {
  if [ $(grep -c "'datadirectory'\s*=>\s*'" $1 ) -ne 0 ]
    if [ $(grep -c "'datadirectory'\s*=>\s*'$2" $1 ) -ne 0 ]
      echo "Error: it looks like the datadirectory file path is inside $2"
      exit 2
    echo "Error: could not find datadirectory directive in $1"
    exit 2

CallOcc() {
	echo "Calling occ with $@"
	sudo -u www-data php ${HTDOCS_DIRECTORY}/occ $@

CheckPathExists $HTDOCS_DIRECTORY/config
CheckFileExists $HTDOCS_DIRECTORY/occ
CheckRemoteFileExists $DOWNLOAD_URL

echo "Checking data file is not in subfolder of $HTDOCS_DIRECTORY"
CheckFileExists $CONFIG_FILE

echo ""
read -s -p "Enter mysql password for user $MYSQL_USER: " MYSQL_PASSWORD
echo ""
echo "Checking user $MYSQL_USER access to the $MYSQL_DATABASE database"
mysql -u $MYSQL_USER -p$MYSQL_PASSWORD --database=$MYSQL_DATABASE -e "SELECT COUNT(*) FROM oc_users"
if [ $? -ne 0 ]; then
	echo "Error: MySQL problem"
	exit 1

echo ""
echo "Removing any existing temp file and downloading the installation"
rm -r $TMPDIR/owncloud
CheckPathExists $TMPDIR/owncloud

echo ""
echo "Putting Owncloud into maintenance mode"
CallOcc maintenance:mode --on

echo ""
echo "Backing up database"

echo ""
echo "Taking down the Owncloud site"
apache2ctl graceful

echo ""
echo "Backing up the existing htdocs directory and copying over the new scripts"

echo ""
echo "Setting permissions on folders and reinstating the original config.php"
chown -R root:root $HTDOCS_DIRECTORY
chown -R www-data:www-data $HTDOCS_DIRECTORY/apps
chown -R www-data:www-data $HTDOCS_DIRECTORY/config
cp -p $HTDOCS_BACKUP_DIRECTORY/config/config.php $HTDOCS_DIRECTORY/config/config.php

echo ""
echo "Running the upgrade script"
CallOcc upgrade

echo ""
echo "Ending maintenance mode and booting up the Owncloud site"
CallOcc maintenance:mode --off
apache2ctl graceful
Posted in Techy Stuff | Tagged , , , | Leave a comment

Xero bank reconciliation with GoCardless

If you’re using automatic repeating invoice generation with Xero, and a monthly direct debit plan with GoCardless, there’s a ‘problem’ in that the value credited to your bank account by GoCardless already has their transaction fee deducted – so Xero won’t spot the invoice amount to reconcile against.

Here is how to use Xero’s bank reconciliation screen to do it.

Go to your bank reconciliation page in Xero. You’ll see that the incoming cash isn’t matched. Click on the Match tab.


You’ll see a list of all your unmatched invoices. Pick the one that matches and Xero will alert you to say the total is out by (in my case) £2.


Click on the Adjustments button and select ‘Bank fees’. Type in something like the following.


Note: GoCardless fees DO NOT include VAT. I asked them. They said:

In the UK payment processing is VAT exempt. As such, no VAT is paid on any of the GoCardless transaction fees.

Everything should now go green and you should see the OK button appear at the top of the panel:

OK to match

Job done!

Posted in Uncategorized | Tagged , , | Leave a comment

How to get Squirrels Reflector working from iPad to Mac

If you want to record your iPad using Squirrels Reflector software*, but the iPad won’t show your Mac on the list of Airplay devices, here’s a workaround I found that others might find helpful – assuming you’ve got an iPhone, that is…

  • Make sure the Reflector software is running on your Mac
  • Now, on your iPhone, set up a personal hotspot (in Settings > Personal Hotspot)
  • Connect your Mac and your iPad to the personal hotspot
  • Now pull up the Airplay option on your iPad again and.. voilà!

I spent a good hour trying to open up various ports on my BT Home Hub router, shutting down the 5GHz band and trying to open up various ports to get it to work. In the end I had to give up.

* I have version 1.6.6, there is a version 2 that I guess might work off the bat.


Posted in Uncategorized | Leave a comment