Blog - Full posts

How to easily & perfectly align your Stem

I don't know about your but I've always had trouble aligning my stem perfectly parallel to my front wheel. It's even more infuriating when out on the road or tail sensing even the tiniest misalignment which niggles you for weeks or even months.

The trouble is that it's really hard to quantify if it is actually out of kilter or just in your head! In the past I've tried many whacky solutions of either triangulating a measurement between the grips and a point on the tyre or using a straight edge between the saddle headset and tyre.

There are two issues at play here, the margin of error that you can detect is so small that it's virtually impossible to measure accurately. You also can't make tiny adjustments to the stem as you have to nip up the bolts a little to stop it moving whilst trying to align it and it's pretty grippy so it takes quite a nudge to move it meaning you pretty much have to fluke it with a bit of trial and error.

Well it turns out that it's actually very easy when you know how and it doesn't even need any tools other than your eyes!

Just drop out your front wheel and eyeball the angle of your axle dropouts with the stem faceplate.

Easy. Only issues you might have is if you've got a chunky front mudguard and you'll come up against not being how to focus on both your dropouts and faceplate at the same time but the effect is no worse than you can see from my photos but it is super quick to get dialled in. Should work with any bike and you can get the alignment perfect!

What's your Robot name?

I'm usually rubbish at amusing inventive word-play - ask me to come up with a pub quiz name and I'm utterly stumped.

This one was easy for me, though I wasn't asked to think of one, the thought just randomly came up in my head

R2-Brew2

genius!

How to remove bicycle tyre marks from painted walls

This is probably the bain of a lot of cyclist's lives, well the ones that keep their bikes indoors anyhow, despite how careful you are it is just a matter of time before you inadvertantly catch a tyre on the wall as you wheel your bike around.

No probs you would think, bit of Cif and the mark'd come right off. No such luck here, for some reason the stain refuses to shift. Sure you can rub at it hard but all that acheives is the removal of the paint.

Magic sponges

After pondering the situation for a couple of years and in conjunction with another household cleaning problem that I decided to buy some magic sponges. Pretty cheap really when you buy them off ebay - a 10 pack was less than a couple of quid delivered first class

Dampen them up and a bit of rubbing later - hey presto, marks gone, and when it's dry it is like they were never there.

Install apache php7 mySQL and phpmyadmin on Raspbian Stretch on a RaspberryPi

I've been eagerly awaiting the fourth version of the RaspberryPi for a while, but when it passed the point where I had expected it to be released I googled it only to find that the next incarnation may be a while.

Darn it, I have to get anothe RaspberryPi3. So the time has come to commission it and I find the lastest version of Raspian called stretch is out and theres been a few other updates. PHP5 has finally been updated and now is now officially available in the 7th flavour having missed out 6 completely - only 7.0 though, no 7.1. MySQL is gone instead being replaced by MariaDB (think it is just a rebrand though. Still on Apache version 2 though so not everything has changed.

No biggie really except all those handy internet how-tos are all out of date so I thought I'd bring you the way I takled it.

First off, update everything, you'll need to be running this in bash or over SSH:

sudo apt-get update -y && sudo apt-get dist-upgrade -y && sudo apt-get autoclean && sudo apt-get autoremove

next up install LAMP:

sudo apt-get install apache2 php7.0 mariadb-server-10.1 

Then PHPmyAdmin:

sudo apt-get install phpmyadmin

Just follow the prompts and teir default setting, I've split this part out as I was getting error when I ran it all as one:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2 "No such file or directory").

Then type

ifconfig

to get the IP address of the raspberrypi

wlan0: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
	        inet 192.168.0.39  netmask 255.255.255.0  broadcast 192.168.0.255

After we've completed these steps we can head over to a browser on a computer running of the network of you rapberrypi and type and type the following URL out chaning out the IP address you th one you are seeing:

192.168.0.39/phpmyadmin

this should send you over to a page looking something like this:

Bang in the details you entered during setup and Bob's your uncle.

How to clean the lid and nozzle of your Camelbak Podium bottle

My first encounters with Camelbak Podium bidons were not great, I borrowed a bottle from Ali for use on quick ride but but I was soon struggling with it!

What I hadn't experienced before in my two decades of cycling was an increase in nozzle length, every time I wen't for a sip I ended up punching myself in the face - I'd have to relearn how to drink my go-juice on the go though as a couple of days later I received a gift from Ali of two brank spanking new GCN bidons.

I was a bit reluctant to margin:10px; use them initially as my experience had shown me that the aforemoentioned drinking outlet had the habit of skaning up quite readily so much so that I'd caught Ali a short while later stooping on her bike, uscrewing the lid and drinking out of the opening in preference to the water having to pass through the bacterial infestation.

I had a go at rmoving it but was getting no-where just with the use of my hands. We'd considered asking someone with a dishwasher to have a go in that for us but didn't get round to it.

It wasn't till I bought her an new one in advance of our Girona training camp that I could up the braveness without worrying if I busted it.

So brave pills taken and the first attempt I wanted was with something blunt. I was in the kitchen so I reached for a teaspoon and proceeded to jam it up the spout as far as it would go, it was then just a case of levering it a bit and the fella popped off.

The worringing thin here is that once you get it open you really see how grimed up it is - the translucent nozzle manages to hide the worst of it, eergh!

It actually comes apart into three pieces with a bit of tug.

You'll be glad to know that from this point it is a super easy to get spangly clean then just pops back together and you are ready to go.

Happy riding and enjoy your good-s-new bottle.

How to enable and change the Remote IR GPIO pin in OSMC

So you want to rig up your Raspberry Pi OSMC to be controlled by your favourite remote control, easy peasy - it is just a matter of plugging the IR receiver diode in and swith GPIO on in the settings.

Dont like the standard pins 17 or 18, no problem - you can change them at teh same time, just follow these instructions.

Go to 'My OSMC,

Select 'Pi Config'

Scroll down to 'Hardware support' and enable LIRC GPIO:

Then select the alternative pins as required

I prefer GPIO 4 for the receiver due to it's proximity to the 3.3v and the GND pins.

Split and extract the first 1000 (N) rows from a text-csv-data file in Windows

One of the major annoyances for me working with very large datasets you get from someone else is that you often don't know the format the file is coming in, not a problem really just take a peak in Notepad++ or Excel and you are away. But quite often these files are millions of rows in length and it is just not possible to work it out.

So I've been trying to chip away at the problem for a while and after I finally got past the first stumbling block of having a computer so lock down I couldn't do a thing with it I was still coming up a little short.

Looking through the defacto bible for any such problem, Stackoverflow, it looks like powershell is what were after running it from a command prompt:

powershell -command "& {get-content input.txt|select-object -first 10}" >output.txt

However there are few failings here:

• we can't be bothered with all the faff of getting the correct path
• it actually outputs it through the cmd console before writing it to the file thereby wrapping it the lines at the default width of 80 lines
• if you have a very large file it will read through the whole serveral Gbs when it doesnt have to

Drag 'n' Drop

We can solve the first problem by creating a drag and drop batch file with the the output file the same as the input but with a predefined suffix, just paste the following code into a file called first_1000_rows.bat:

powershell -command "& {get-content %1|select-object -first 1000}" >%~n1"_1000"%~x1

then all we need to do is to drag the file we want to split and drop it over the batch file and hey presto.

No-wrap

Next task is we have to solve the wrapping issue, a little tweak will see us outputting it as part of the powershell process not through the command console using the set-content command:

powershell -command "& {get-content %1|select-object -first 1000|set-content -path %~n1"_1000"%~x1}"

Optimisation

Finally we'll make a little tweak to stop after the first end rows, easy when you know how, just set the row limit on the get-content command:

powershell -command "& {get-content %1 -TotalCount 1000|set-content -path %~n1"_1000"%~x1}"

Job's a good-un as the saying goes and now we have an extract of the file we can easily have a butchers at.

Dealing with spaces in paths

Unfortunately the Powershell - DOS interaction doesn't play nicely with spaces so we have to deal with them somehow as well:

powershell -command "& {get-content '%~1' -TotalCount 1000|set-content -path '%~dp1%~n1_1000%~x1'}"

Finished

Job's a good-un as the saying goes and now we have an extract of the file we can easily have a butchers at.

My new scale for measuring oldness

Well I suppose it was inevitable, when you suddenly realise you are getting older by the things you catch yourself doing/saying thinking getting more frequent.

The latest thing I've noticed is that my propensity to fart in public has increased exponentially! It used to be the case that I'd internalise the pain of holding in a ripper for hours on end rather than suffer the embarassment of being discovered as letting one go. Oh so I've changed, nowdays I'd quite freely let one rip on the basis it's much better for me that I've dealt it out.

Fair enough I hear you say, so long as you slip away unnoticed that fair enough, but these days I seem to be freely parping with little care who either smells or hears it. It was this which has led to the developement of a scale for measuring oldness based on a measurement of PPP - public parping propensity. I'm still working out the details but it is a relative scale, I'll have to do a survey though to clibrate it as I'm not in the advanced stages of life so I'm not sure at which point letting rip at formal social occasions and trumping in the office feature on the scale.

So stay tuned and I'll update you shortly.

A datedif function for excel that calculates correctly

This is one of those little quirks in excel that's annoyed me for a while, there must be some method in their madness but I've yet to come across anybody that thinks it is logically the way forward; so I've written one for you to use.

The way the in-built function works is that it reports back the answer as how many months apart are the two dates so regardless of the day in the month they fall, any date in May vs any date in June the answer will be one.

Can't actually think of any real world use of this as always want to know how many full months are bewteen the two dates. For example 23-June vs 1-July is less than one month away so 0; 23-June vs 27-July would give 1. Contrast this to the excel way and you'd get the answer of 1 for both.

So explanation over we'll give give you the code, head over to the VBA editor (Alt-F11), insert...module then paste the code in:

Function DateDifm(Date1 As Date, Date2 As Date) As Date

' =====================================================================================
' Author:      Martin Childs
' Create date: 2017-03-08
' Description: Returns correct date difference in months for given inputs
' =====================================================================================

    DateDifm = DateDiff("m", Date1 - Day(Date1) + 1, Date2 - Day(Date1) + 1)
    
End Function

When you completed that head back into excel and you can use the formula:

=DateDifm(A1,B1)

Without VBA option

If you don't want to use VBA, heres how you can do it straight in an excel formula, in this example the two dates we are comparing are in A1 and A2:

= DATEDIF( EOMONTH(A1,-1)+1, B1-DAY(A1)+1, "m")

I've slightly re-written it compared to the VBA function above to have less linking within the formula, but you can do it this way also:

= DATEDIF( A1-DAY(A1)+1, B1-DAY(A1)+1, "m")

SQL equivalent

The correct way of calculating it came about when I was performing a critical calculation in SQL and in the back of my mind it was nagging me whether, as they are both from Microsoft, the DATEDIFF() function in T-SQL had the same flaw. My suspisions were correct - both calculate in the same way, but from the same post on StackOverflow I had noticed a very efficient craft bit of code which was so much shorter than everything else and from here went on to adapt it into VBA.

Here the SQL function if you are also interested:

IF OBJECT_ID (N'dbo.DateDiffm', N'FN') IS NOT NULL  
    DROP FUNCTION dbo.DateDiffm;  
GO  

-- ==================================================================================
-- Author:	Martin Childs
-- Create date: 2017-01-23
-- Description:	Returns correct date difference in months for given inputs
-- Version:	v001
-- ==================================================================================

CREATE FUNCTION dbo.DateDiffm( @Date1 as datetime, @Date2 as datetime )  
RETURNS int
AS  
BEGIN

Return (
	datediff(month, dateadd(day,-day(@Date1)+1,@Date1),dateadd(day,-day(@Date1)+1,@Date2))
       )
END;

Raspberry Pi 3 HDMI overscan settings and how to correct them

If you've ever plugged a Raspberry Pi into a tv you've probably come across this issue - the displayed image either doesn't fill the screen or over-runs it.

The usual bit of googling reveals that all you have to do is tweak a couple of overscan settings and you are good to go. It sounds easy but three Raspberry Pis in and I've only just found the technique that lets me fix it before my patience runs out.

You've probably been persevering down this road quite a while now so I'll spare you the basics and I will skip to a trick that may save you all the hassle

Change to a different HDMI input on your TV

Simple I know, but it actually works a treat, my classic but still great by today's standards Samsung UE32EH5000 has two HDMI port with one designated as DVI. My computer has always occupied this slot for no other reason that it uses ATi's special DVI to HDMI converter that magically passes through the sound as well and I've never had reason to change it - fit and forget.

It was only since I've been testing the dual HDMI capabilities of my graphics card that I've had reason to try the other port and in doing so I noticed that the connection through the non DVI designated connector actually overscanned. Hang on a minute, maybe not all HDMI ports are made the same.

On hooking my Raspberry Pi up into this socket everything ran swimmingly and and the edge of the screen lined up - magic.

Try my settings

If you don't have a fangled HDMI(DVI) port then you could have a crack at my settings. I actually came up with these before I knew about the trick above (but I am not bitter). In your terminal/SSH window type

sudo nano /boot/config.txt

then uncomment/add/change the below lines:

disable_overscan=1

overscan_left=39
overscan_right=39
overscan_top=25
overscan_bottom=23
framebuffer_width=1920
framebuffer_height=1080
hdmi_group=1
hdmi_mode=16

save the file with ctrl+o then exit with ctrl+x and reboot

sudo reboot now

to see if it has solved your woes. My settings above are actually a bit of an overkill and I'm sure I don't need all of them but I'm sticking with them at this moment because I done with fiddling with them for the moment.

How to tweak it yourself with this foolproof method

If my settings don't help you out here is my foolproof guide to getting it sorted. First up fire up PiXel of X with this command if you are not already in the desktop:

startx

Next we are going you'll need a bit of underscan going on - can you see all of the screen in your TV/monitor - you can tell this by observing your cursor.Take a close look at the on screen pointer, but default it is white surrounded by a black border then another white outline of pixels. Move the pointer to the edges of your screen.

Left most and upper screen edges you should always be able to see the whole of pointer, to the left you should only see a vertical line of white pixels and on the lower edge you should only see a single dot of the tip.

If you can't see em go back to your config.txt and fiddle with the settings until you can (I'll skip this step as you've probably very up on how to get the wrong settings by now!).

Now all you have to do it reboot and re-tweak the settings until the edges of the pointer just creep off the screen and then bring them back one pixel.

I know it is tedious but it is the only reliable way I've found to resolve and your OCD will be put at ease.

Create custom Excel function using an array/range input

My new adage of

"You only know what you know how to google"
- Martin Childs, 2016

stood true for this little puzzler, the answer wasn't apparent from simply googling it! All I wanted was to do something to a range input in a custom Excel function, easy you'd think - not in this case. I toiled for a bit of time before conceding that I'd just have to work it out from first principles.

First up it is properly referred to as an array rather than a range - range refers just to the cell reference rather than the contents of the range. To be able to use inputted range you need to determine a variable as an array of some type and then read all the possible cells in the range into it:

Dim MyArray() as Variant
MyArray = InputRange

Now we are rocking, next up is referencing it - LBound & UBound are your friends here, used quite universally in a lot of programming languages - they find the outer extremities of your array and allow you to loop through all the values. The next bit also foxed my for a short time - I could only read in one row with

for i = LBound(MyAr1, 2) To UBound(MyAr1, 2)

But why was it it two - well to get a two dimensional array working you have to loop both the 1 and the 2. Now we just have to hook it all together and loop through all the references and read the values, in this example I just complete a simple sum with the function:

Function mySum(input1 As Range) As Variant
'
' www.childs.be, 2017-02-04
'
    Dim MyAr1() As Variant
    MyAr1 = input1
   
    For j = LBound(MyAr1, 1) To UBound(MyAr1, 1)
    For i = LBound(MyAr1, 2) To UBound(MyAr1, 2)
        If IsNumeric(input1(j,i)) = True Then
                N = N + input1(j, i)
        End If
    Next i
    Next j
   
    mySum = N

End Function

SSL on a RaspberryPi - does it slow your site down?

First up I'm sorry about bathering on about this SSH thing but I to be honest I'm padding out the blog posts a little bit as there's one article I've written that I'm just not sure I want to publish, It's nothing bad, just not in keeping with the tone of this site so I've racked it up at the end of the post ready to go and keep adding posts in front of it.

Not that this is not an unimportant poin, it was a question I was pondered for a while - I wasn't sure if I wanted to invest in the purchase of a SSL certificate when all I'd end up doing was to slow the serving of my pages down.

Well, now I've taken the plunge, read about how to install a free SSL certificate on a RaspberryPi here:

What I can confirm (I should have tested load times before or after but I didn't think about it), it that I actually think my server runs faster, and I'm using one of the original RaspberryPi Model B!

Remove Real VNC from RaspberryPi Pixel and install TightVNC

I came across my old shortcut on my windows machine to vnc onto the Pi which prompted me to have a go connecting to my new RaspberryPi3.

I duly fired it up only to receive an error message along the lines of 'No configured security type is supported'. After a bit of research it turns out that RealVNC has upped their security protocols and TightVNC is no longer cross-compatible.

No probs I thought - juust install the RealVNC viewer on my windows pc and give it a crack that way. I've never really been a fan of RealVNC, but you know it's been over a decade now and I'm not that set in my ways that I can't change so I installed it to see whats new.

Turns out there a lot new! Some crappy licensing scheme whereby you have to sign into an account through their website before you vnc onto anything. This is not a grteat user experience and it's not in the RaspberryPi's way of doing things eother. So it has to go.

Remove RealVNC

If you want to do away with it also, just follow these instructions, in terminal or over SSH:

dpkg --get-selections | grep vnc

this will show you what the name of the package is you need to install, then un-install it:

sudo apt-get remove realvnc-vnc-server

Install TightVNC

Then we can move on to getting tightvnc installed

sudo apt-get install tightvncserver

then run it to configure it

tightvncserver

once you've set up some options/passwords it will then start an instance for you, we'll go ahead and kill it then start up a new one with a better/your own resolution

vncserver -kill :1
vncserver :1 -geometry 1024x1024 -depth 24

Connect

you can now connect using the hostname/ip of your pi from your viewer

raspberrypi3:1

Autostart

to start the server everytime you boot your pi, you can do the following:

sudo nano /etc/rc.local

then type in

echo "Starting VNC server on port 5901"
sudo -u pi tightvncserver :1

ctrl+o, enter and ctrl+x to save and exit.

Running a SSL certificated/authenticated HTTPS website over dynamic DNS

One of reasons why I originally held back on buying a SSL certificate for my website was that I wasn't sure that it'd work using a dynamic IP routing service - cos I didn't want to waste $15!

Well you don't need to fret about it because I can confirm it works after I eventually got round to setting up a free LetsEncrypt.org one on my RaspberryPi.

Now you've got the answer, why don't you head over to my tutorial of how it's done.

Free LetsEncrypt secure SSL server certificate install tutorial for Raspberry Pi

Excel: How to calculate distances between longitude / latitude co-ordinates

Here's a blast from excel past, it was actually one of the more interesting projects I worked on. We were designing food waste recycling plants for the food manufacture and retail industries. The task was to calculate the most efficient solution ofhow many recycling plants to build and where to build them based on the waste arising of each shop and factory vs the cost to transport between the sites.

The finished algorythm wasn't that complicated, and I ran it for one, two, three four and five plant solutions. One of the bits of information that the work was predicated on was the sitings of each shop/factory and although I had the postcode my presumption was that converting them to x and y co-ordinates would be easy. Well, to cut a long story short it wasn't - I quite quickly found a lookup list of postcodes to latitude and longitude and after that it would just be a simple bit of triganometry and pythag right? Wrong!

Turns out converting lat/long to east/north is a complete PITA! Thankfully the guys at Ordnance Survey have the answer in their everything you need to know guide:

A guide to coordinate calculations (mirror)

Did that clear it up for you? Me neither,, the formula is a whopper:

Fortunately they make our lives a little easier by providing an Excel spreadsheet showing us how to calculate it and a whole lot of other stuff, yeah!

Coordinate calculation sheet(XLS) (mirror)

It's a little detailed so if you open it and head over to the [lat,long to E,N] tab you see the custom function helpfully spitting out the answer.

=Lat_Long_to_East(DecimalLat,DecimalLong,a,b,E0,F0,DecimalPHI0,DecimalLAM0)
=Lat_Long_to_North(DecimalLat,DecimalLong,a,b,E0,N0,F0,DecimalPHI0,DecimalLAM0)

Fortunately it is not all that complicated, most of the arguments are constant so we are just left with:

=Lat_Long_to_East(DecimalLat,DecimalLong,6377563.396,6356256.909,400000,0.9996012717,49,-2)
=Lat_Long_to_North(DecimalLat,DecimalLong,6377563.396,6356256.909,400000,-100000,0.9996012717,49,-2)

Simples, now all you need to do is copy the macro module over to your spreadsheet and you are good to go.

Musing of the day 22-Feb-2017

I love an analogy, there's nothing better than pushing your explanation across the line with an unequivocal corresponding example in everyday life.

I think I've crossed the line this time though, I'd been asked to rate the presentation I'd just been given on a subject outside of my specific skill set; I responded that it was tricking given how left-field the idea was and that I was struggling with a point of reference, I likened the experience to that of Simon Cowell just being given a rendition of a song written and performed a X-Factor contestant and that without a performance of a professional to compare against he couldn't give it a just review.

Unsurprisingly I was called out immediately by even colleagues far across the room - I thought it was apt but apparently it's an analogy too far!

Free LetsEncrypt secure SSL server certificate install tutorial for Raspberry Pi

For me being SSL secured has always been the benchmark as having a bonafide website. That little green padlock next to your websites URL doesn't really mean much for a blog, but I always wanted one. At the start of last year I started thing about trying to get one.

Ever being a skinflint I wanted to pay nothing for it, but logic overcame this thinking as all the free ones either had massive catches that you had to manually renew them every thirty days or they were so free that browsers just didn't rattify them as being legit.

I'd decided at this point to drop the princely sum of $15 on a three year Comodo certificate from these guys. There was a possible stitch up of evorbadent fees come renewal time but my limited research showed up that like car insurance you just go somewhere else to pick up a bargain price again.

Roll on one year and I still hadn't got round to doing it but in my quest to re-find where I'd intended to buy when I came across LetsEncrypt.org, free, auto-renewing and recognised by all the major browers - cool!

So I'll crack on with how to get it on your machine in not time at all....

Instructions

So before we start, check the version of the operating system you are running as the install instructions do vary:

cat /etc/os-release

Certbot

My RaspberryPi Model B is running wheezy so the install instructions from the Certbot wesite gives this this (check the rest of the page in the link for instructions for your OS if different):

wget https://dl.eff.org/certbot-auto
chmod a+x certbot-auto

Authentification

There are two ways for you to prove ownership of the website:

• let Certbot automatically authenticate it by launching a standalone server temporarily  
• or setting up a directory for it to place some files temporarily

I did try the former byrunning the command

./certbot-auto

But for some reason I got an error back that there was already a server running on port 443 - fairly obvious really, and I would have thought that it could automatically stop the apache server for a moment to enable it to complete seeing as it'd have to in the future to renew your licences. Having read back the instruction in writing this post perhaps I should have added a --apache switch, but too late now...

Webroot

As I said, the other method requires use of an available folder to serve up some files, as my website runs some pretty comprehensive Rewrites chances of it working were slim so after a quick google showed up this set-up in Apache's generic.conf - should be handy for me to be able to quickly get certificates to my other hosted sites later if all goes well. Here is the code you'll need to add for a multisite webroot. To add it, fire up the config with:

sudo nano /etc/apache2/conf.d/generic.conf

and add this code:

Alias /.well-known/acme-challenge/ 
/var/www/html/.well-known/acme-challenge/ 
<Directory "/var/www/html/.well-known/acme-challenge/">
     Options None
     AllowOverride None
     ForceType text/plain
     RedirectMatch 404 "^(?!/\\\\.well-known/acme-challenge/[\\\\w-]{43}$)" 
</Directory>

To save and exit ctrl+o, enter then ctrl+x. Make sure to change the path to wherever your website is served from in then above then you'll have to reload to apache config at this point to go ahead and and run:

sudo service apache reload

then we can get on with running Certbot and following the instructions:

./certbot-auto certonly

all was reported back as having successfully completed after a short time so good to go.

Certificate install

This is the bit of the process that took the longest for me - I'll admit most of it was user error! I have been runnig a server on port 443 with a snakeoil certificate for my admin login portal for a while and it has to be said the the default apache host script for it is rather lengthly so it took a bit of head scratching before I realised that I was still getting 'get me out of here' messages from my browser was that I hadn't removed all of the code from my previous config.

To add it to apache's default config, : 

sudo nano /etc/apache2/sites-available/default-ssl

update with the locations of your certificates:

SSLEngine on
SSLCertificateFile    /etc/letsencrypt/live/yourdomain.com/cert.pem
SSLCertificateKeyFile /etc/letsencrypt/live/yourdomain.com/privkey.pem
SSLCertificateChainFile /etc/letsencrypt/live/yourdomain.com/fullchain.pem

reload apache's config:

sudo service apache reload

And head over to your site to see if it working. If it is not working at this point then check your site at this handy website for diagnostics on your auth status:

https://www.digicert.com/help/

Renewal

Add cron job to auto renew

crontab -e

then add this

# Renew letsencrypt certificate 31 3 * * * /home/pi/bin/certbot/certbot-auto renew --quiet --no-self-upgrade --post-hook "sudo service apache2 reload"

then we are all done, I've still got a couple of months to wait to see of it actually workks, but stay posted and I'll let you know.

Additional domains

I was actually so pleased with the results that I went straight ahead and added my other domain philfish.co.uk to the certificate as well, I can report that the multiple webroot worked a charm and the whole process was finished in a couple of minutes just with the --expand switch with this bit of code.

How to make an clock for a raspberry pi with an OLED display

My dream RaspberryPi project that I am slowly chipping away at is to create the ultimate alarm clock. It is a slow burner though as at it's inception I had none of the skills I needed to pull it off.

I've now amassed all of the technical skills to pull it off and I'm just left with finalising the design and finding the time and the parts (not a lot then!).

An interim project along the way is whipping together the first prototype of the time display re-using some of the parts from an old Arduino project.

What you need

Pretty easy, you don't not a lot is required, obviously you'll need a:

• OLED display

There is a range of available units in different colours and with different pin-outs. This tutorial is based on these are the instructions for the four pin variety - vin, sda, scl & gnd.

Some have five or more including rst but they'll require different libraries to the ones we'll be using here so I wouldn't go for one of those. Just head over to ebay and search for 'oled i2c' and for between £2 and £7 you'll be in business. I'd go for one with the header pins already soldiered for you as if you haven't got a soldering iron it will be a massive inconvenience.

You will also need either:

• a bread board and 4 male/female breadboard wires;
• or 4 female/female breadboard wires.

Install the software

First up we'll update you RaspberryPi with the required packages, hopefully you are familiar with either the terminal window or SSH, I won't go through them here we will just crack straight on with it

we'll be downloading packages to download projects from Github, Python and I2C:

sudo apt-get install git-core
sudo apt-get install python python-dev python-setuptools
sudo easy_install Pillow
sudo apt-get install python3 python3-dev python3-setuptools
sudo easy_install3 Pillow
sudo apt-get install python-imaging python-smbus i2c-tools

I've included both Python 2 & 3 here but you only really need one of them if you are short on space.

Libraries

Here we are going to create a folder in you'r home directory and download the python libraries we'll need to operate the display, these are the main display calls, a funky font which looks just like an LCD display and some code so we can run the scripts in the background as a system service (as I describe in this tutorial)

cd ~
mkdir oled
cd oled
git clone https://github.com/BLavery/lib_oled96.git
git clone https://github.com/keshikan/DSEG.git
git clone https://github.com/metachris/python-posix-daemon.git
git clone https://github.com/mc3k/OLED-Clock.git
cp 

Wire up the pi

Probably the most daunting bit, but in the big scheme of GPIO this is about as simple as it gets - just refer to the 

Obviously the usual disclaimer applies - if you electrocute yourself, blow up you RPI or burn your house down - don't come to me with your law suit; you need consult a someone who is fully trained up in electronics to guide you through it if you are unsure about any aspect of what you are doing regardless of what I've written!

Legalities out of the way, you just need to connect the four pins of your display to their corresponding pins on the pi - consult the header labels on your OLED with the pinout diagram. It is pretty straightforward in this case as they are all grouped together at the end of the header. The VCC pin can usually be connected to either 3.3v of 5v in the case of this component but check your specs to make sure.

Wiring diagram

Pinout diagram

For the RPI2 & 3, if you have a different version consult the relevant diagram for your model.

After you have checked and 

Finalise the Pi setup

sudo raspi-config

and enable i2c in the advanced menu, then reboot and see if we can detect the screen:

sudo reboot
sudo i2cdetect -y 1

after the last command you will get a readout telling you that your pi has picked up your display:

     0  1  2  3  4  5  6  7  8  9  a  b  c  d  e  f
00:          -- -- -- -- -- -- -- -- -- -- -- -- --
10: -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
20: -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
30: -- -- -- -- -- -- -- -- -- -- -- -- 3c -- -- --
40: -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
50: -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
60: -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
70: -- -- -- -- -- -- -- --

You can see mine here popping up a 3c

Lets start the coding

So now we've got all the pre-requisites out of the way we can get on with firing up the display and getting coding. The way we'll go about it is to grab the various files we need from the libraries and copy them into the working directory.

I'm doing it this way to keep it simple as there's an overwelming amount of files knocking about in the subfolders.

First up is the OLED library and some bare bones code to get us started

cd /home/pi/oled
cp lib_oled96/lib_oled96.py .
cp OLED-Clock/python/clock-min.py .

then we can fire the script up to see if it works

python clock-min.py

hopefully you should be seeing the display pumping out the time and date along with a the time scrolling up on the command prompt. Jobs a good-un.

To cancel just hit ctrl+c.

If you want to have a look at the code just use the following command:

nano clock-min.py

Next up is a more fancy looking script:

cp lib_oled96/FreeSans.ttf .
cp OLED_Clock/python/clock.py .
python clock.py

The third is has a more traditional look of an LCD clock, this one also only updates once a minute instead of once per second - I've not test the performance overhead of these script but it may have less overhead: 

cp DSEG/fonts/DSEG7-Modern/DSEG7Modern-Bold.ttf .
cp OLED-Clock/python/clock-dseg.py .
python clock-dseg.py .

Running as a background service

As good as these scripts are they are pretty useless in their current state  as they tie up the terminal/SSH session stopping you working and quitting the python job if you exit them. So what we need is to run them as a background service, fortunately this is pretty easy when you know how, first off copy the files:

cp python-posix-daemon/src/daemon2x.py .
cp OLED-Clock/python/clock-dseg-d.py .
cp OLED-Clock/python/clock-d.py .

We can start and stop the clock with these commands:

python clock-d.py start
python clock-d.py stop

python clock-dseg-d.py start
python clock-dseg-d.py stop

As you see you it doesn't hog the bash prompt and you can even quit your terminal / SSH session.

Starting at boot

if you want to run the script at boot you'll need to

sudo nano /etc/rc.local

then type in

# Start the OLED Clock
if [ 'i2cdetect -y 1 | grep 3c' ];  then
        echo "Starting python logging daemon\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n"
        sudo python /home/pi/oled/clock-d.py start;
else
        echo "no";
fi;

Further reading

there are some bits of code to embelish your project here adding the ability to display a various number of different system parameters, I've programmed a number of them here:

OLED-Clock/system.py

How to run a python script as a service / in background / as a daemon

So you've written a killer python script and you want to run it in the background as a service because when you close the terminal window/SSH session it kills it. I originally worked it out when I wrote my Twitterbot app, and was going to write it up seperately then but it took me so long to get it running in the background and I tried so may things that I couldn't actually tell you how I eventually managed it!

It was only as I was working on my new project - an OLED clock for my new mediacentre pi that I finally worked out how easy it actually was; here are the instructions.

First we'll get the code using a terminal window or SSH:

cd ~
git clone https://github.com/metachris/python-posix-daemon.git
cd python-posix-daemon/scr
ls

so you'll see the three files which do the business:

daemon2x.py
daemon3x.py
daemon-example.py

you'll obviously need python at this point tp do anything so I'll assume you have this, we can try the provided example file to see if everything is working:

python daemon-example.py start

and 

python daemon-example.py stop

to stop it.

At this point you'll notice that nothing really happened - thats because the sample dosen't actually do anything, we can have a look by openeing the file

nano daemon-example.py

it just sleeps for 1 second and then repeats.

If you want to see an example of a script that does something you can use my example:

It creates a log file ant writes the time to it every two seconds. Download the script:

wget https://gist.githubusercontent.com/mc3k/0d0f19d6a92387d2b564cbaec3cdcc54/raw/80a8a462de179dc7a0f359ac1a860a4d2e39b2d0/daemon-example2.py

and start it:

sudo python daemon-exampl2.py start

leave it for and few seconds then stop it:

sudo python daemon-example2.py stop

we can see the results using:

cat /var/log/daemon-example.log

and there you have it, everything is easy when you know how. If you want to put it in you library then you just need to put it in a python sys path.

if you want to run the script at boot you'll need to

sudo nano /etc/rc.local

then type in

echo "Starting python logging daemon"
sudo python /home/pi/python-posix-daemon/scr/daemon-example2.py start

but I wouldn't recommend it for this script.

Using OR in SUMIF in Excel

Things have moved on since the days of DSUM, we now have the likes of SUMIFS from Excel 2007 onwards. Can't even remember life before SUMIFS having never being a fan on DSUM.

But with all things in life everything moves on, everything is never good enough and now SUMIFS's shortcomings are beginning to pop up.

The other week the need came up for a handy way to integrate OR logic into the SUMIFS, saves on some extra column calculations and makes us all look a bit cleverer. It is possible via an array formula however it does have its limitations. The biggy being that the OR variables have to be hard coded in - not so good.

Any how, it is simple enough to code up, just put the OR parameters in curly brackets and surround the SUMIFS with a SUM and you are away:

= SUM ( SUMIFS ( $C$2:$C$16 , $B$2:$B$16 , {1,2,3,4} ) )

works with COUNTIFS also:

= SUM( COUNTIFS( $B$2:$B$16 , {1,2,3,4} ) )

Geocache Log Sheets download

One of the highlights of my summer was the adoption of a supplementary passtime, having recently picked up a copy of the National Trust's 50 things to do before you are 11 3/4 to inspire fun activities to be enjoyed with my niece and nephew I proceeded to introduce them to it.

Unfortunately despite them falling very short of the cut-off turns out the active pair had already completed 47 of them. Oh well, suppose I can't complain however of the three remaining two were specifically Autumnal - catching a falling leaf and playing conkers. Was quite surprised and the conkers one to be honest - given that my father was world conker champion in 1977 you'd have thought that one had already been covered, but I suppose I'd better leave that to him.

The sole remainder item was geocaching, I'd vaguely heard about it but the topic still needed the requisite googling, concept looked great and on mentioning it to Ali I got instant full approval. Turns out our esteemed hero Jens Voigt is a big advocate so thumbs up all round. I'll leave it to you to look up if you haven't heard of it, and discover the pleasures for yourself but it is indeed a great pass time if you are the sort of person who finds themselves outside at random locations out and about.

Armed with app in hand the first attempt went beautifully with several caches spread around Draycote water all accessible via bicycle / scooter and we've been hooked ever since.

The next obvious step was to site my own geocache, I've not actually found anywhere to place one yet but I sure am ready with my home made log sheets you can also download and enjoy yourself.

 5 Column

 4 Column

 Micro

Musing of the day 09-Jan-2017

Had a right good chuckle to my self whilst on holiday in Cambodia, in the spirit of travelling light I'd left my electric toothbrush at home favoring the good ol' fashioned way of doing things.

I've never been a fan of the manual way - always found it a bit too awkward and unusually for me being ambidextrous I suddenly realized that I'd never actually tried it with the other hand.

Great, I immediately gave it a go, swapping from my usual left handed technique to the right hand. The results were pretty close in all respects apart from one - the previously most tricky cleaning spot was with the left outer teeth using my left hand (left-outer/left). Whilst my right-outer/left performance compared to left-outer/right was equal, there was a significant gain in ability to articulate my wrist in right-outer/right mode than left-outer/left. We were looking at about a 25% proficiency gain. Nice one I thought.

It was at this point my emotions changed, as I relayed my latest findings to Ali the onset of disappointment encroached me. For someone who seeks pleasure in proficiency of ambidextrousity and have a constant strive for  efficiency gains - realisation I'd never thought of trying it was vexing!

Cracking ice-cream flavour combo: Coconut and chocolate

On yet another fun Saturday night out at National Cycling Centre to watch the Revolution Cycling Series I set off in search of a snack! I had often seen the ice cream stand at the Sapporo noodle bar, but had never tried the ice cream.

I joined the queue to get myself an ice cream, deciding to get two scoops (despite declining the offer of his own ice cream, I knew Marty would tuck into mine!).  Normally I am a mint choc chip girl but the flavours on offer were chocolate, coconut, strawberry or green tea (???).  So what to do? Two chocolate, chocolate and strawberry, or chocolate and coconut. Hmmmm, that could work....

Opting for the choco-coconut combo I had to sample it.  The ice cream tasted just like a bounty ice cream. I reluctantly went to find Marty (secretly wanting to walk slowly and keep my new find to myself), and told him he had to try it. He assumed it was chocolate and vanilla, so got a spoon of chocolate, then a spoon of the "vanilla". When he realised it was coconut and tried a spoon of both flavours together I knew that I had lost the rest of my pudding!!!

However, as it got a massive thumbs up from us both I have no need to be sad. I think a cheeky choco-coconut ice cream will now become part of our Saturday night cycling. Roll on the next round - I need more ice cream!!!!

"You only know what you know how to google"

"You only know what you know how to google"
- Martin Childs, 2016

This post was supposed to be about how create a custom Excel VBA function, the origination of the idea was despite my lifelong career of tinkering with excel I'd never come across or had the requirement to have a function cycle through the cell you input as a range to perform a calculalation on them.

Nice, just the sort of Excel work I like, I firstly gave it a bash myself - I usually am able to wing the answer using induction. [Induction, if you didn't know is the actual method Sherlock Holmes uses rather than the often misquoted deductive reasoning.] No luck winging it so I turned to every programmers best friend Google.

There's pretty much nothing in the coding world that is invented or revolutionary these days, everything has been done before, anything new is just an incremental improvement and it is all documented on the internet. So it was off to google to what was what.

In hindsight I don't know whether I was having a bad day or I had lost my mind but for the life of me I couldn't find the simple answer I was looking for - everything solution was so convoluted I couldn't believe it. In fact I battled with it for a time much in excess of what it would usually take me.

I eventually found a solution which was acceptable - through trial and error more than anything. So a while later when I got round to writing the method up I thought I'd give it another google as you know how the saying goes:

You only know what you know

Well, would you believe it, this time around google was almost straight in there with the answer, damn it. Reflecting on the above adage it occurred to me that it is no longer true and have come up with a more up to date phrase:

"You only know what you know how to google"
- Martin Childs, 2016

Hopefully I'm the first one to coin it, but it probably like every app idea I come up with - one search on Google Play and it's already been done 20 times over!

How to pivot data by month in SQL and output to a temporary table

There are some things that aren't easy to do in SQL as you'd like. I don't know why it has to make the seemingly simple things to complete in Excel so annoyingly tricky.

Dynamic pivots is one of those things that is overcomplicated, sure there's plenty of tutorials on the t'internet , but what they all seem to lack is instructions of how to write them to a temporary table, I'm a big fan of temp tables in my workflows and on this occasion google has been drawing blanks.

So if you are struggling to work it out you have to use a global temporary table as EXEC command you run actually uses a different process so your process won't see it after it has run. I also had a couple of attemps to get it in the right place - SQL isn't really helpful in this respect either - the code highlighting doesn't extend that far.

Anyway, I'll get to the example so you can see for yourself:

Print 'Pivot transactions'

begin try drop table ##mcTemp1 end try begin catch end catch

DECLARE @ColumnName AS NVARCHAR(MAX)
DECLARE @Qry AS NVARCHAR(MAX)
DECLARE @FromDate AS NVARCHAR(MAX)
DECLARE @ToDate AS NVARCHAR(MAX)

Set @ToDate='2015-10-31'
Set @FromDate=DateAdd(MONTH,-24,@ToDate)+1

SELECT @ColumnName= ISNULL(@ColumnName + ',','') 
       + QUOTENAME(Month)
FROM (SELECT DISTINCT Convert(Date,[PAY_DATE]-DAY([PAY_DATE])+1) AS 'Month' FROM [WHS_PAYMENT_HISTORY] 
WHERE PAY_DATE between @FromDate and @ToDate) AS Months
ORDER BY MONTH


Set @Qry = N'SELECT ACCOUNT_NUMBER,'+@ColumnName +'
INTO ##mcTemp1
from
(
SELECT ACCOUNT_NUMBER,
	[PAY_DATE]-DAY([PAY_DATE])+1 AS ''Month''
    ,[PAY_VAL] As Payment
  FROM [WHS_PAYMENT_HISTORY]
  WITH(NOLOCK)
  WHERE PAY_DATE between ''' + @FromDate + ''' and ''' + @ToDate + '''
) pd
pivot
(
SUM([Payment])
FOR [Month] in (' + @ColumnName+')
) As Pvt'

EXEC sp_executesql @Qry

SELECT * FROM ##mcTemp1

The great Indian restaurant lamb scandal?

Whilst we were out in Tanzania on Safari something wasn't quite adding up, every night at the hotels there was several Lamb dishes to be found on the menu. The thing was vexing me was that despite being in the country for nearly a week and spending at least eight hours a day driving through various types of countryside I'd yet to lay eyes on either a sheep let alone a lamb...

With this puzzling me I broached the subject with the safari guide, MacDonald. He reluctantly pointed out that I may well have been served goat. 'May' may have been an understatement, if there was any livestock I had seen in abundance it was indeed goat and thinking back I'd actually commented to myself on several occasions how desipte its looks, the meat I was served didn't taste very lamby and was a fair bit chewier.

So that begs the question, does this ever happen in this country?

Armed with this information I've been keen to conduct a thorough survey, said investigation has taken place over year where I've been sampling Lamb dishes as often as would allow. So far my conclusion is that I've yet to suspect anything other than bonefide lamb although I fear that I've only been frequenting a couple of different establishments which are not renowned for there low quality dishes so may have introduced some selection bias.

In the coming year I'm looking to some more vigarous testing!

How to convert a text DDMMMYYY date in SQL

As much as I love Excel there are some circumstances where it just doesn't cut it - more than a millions rows and computer says no; some rediculouse vlookup and you could be there for hours. So step in SQL, it can do those aforementioned tasks in a matter of seconds.

There are a couple of occasions though where SQL is not king, one of these is it's cleverness at recognising dates especially with the good old British notations.

Want to import a date in the format 'DDMMYYYY' and you might come up a bit short as annoyingly this date format isn't in the convert functions pre-defined list. You could use some Excel stylee of MID() or SUBSRTING(), but here's a quicker method - stick a few spaces in it and pass it off as 'DD MMMM YY' and you are quids in:

convert(date, stuff(stuff([txt_dte_1],6,0,' '),3,0,' '), 113)

You can check out Stuff() here.

How to set up your Dell E310dw printer the really easy, bloat free way on windows

One of the common themes of the computer industry is to try to install a load of bloat on your computer under the guise of making it easier for you, well when I first got my Dell E310dw and had a flick through the setup guide to head off any such shinangans I noticed they had some sort of 'universal' installer.

Trying not to fall for that I steamed ahead to wing it myself, turned out to be pretty easy really, just follow these steps:

1. Unbox
2. plug in ethernet cable from printer to router
3. turn on

That's it, seriously! I went to the 'Devices and Pirnters' screen in Windows 10 to go and try to install it as a network printer and it was the already waiting to go.

I didn't actually believe it so I fired of a test page and it printed - magic!

One of the option I was missing though was the booklet print option, so if you want to get to all the advanced settings you do need to install the dell drivers. You can do that easily 

1. Go to the Dell download page for the printer
2. Download the latest Windows driver and unzip it
3. Search for device 'Device Manager' in the start menu
4. Expand the printer option and right click on 'Dell Printer E310dw'
5. Click update Driver Software
6. then 'Browse my computer for driver software'
7. then 'Let me pick from a list of drivers on my computer'
8. then the 'Have Disk' button
9. Then navigate to where you unpacked the divers and follow it through Drivers/PCL/32_64 and select DLOHL13A.INF

Then you are good to go, fire up the printer properties pages and you get:

For extra enjoyment, fire up you web browser and make your way over to the IP address of your printer and you'll find yourself up to you neck every option and bit of info you could think of including Google's Cloud print gubbings.

How to install Dell Printer E310dw on your Raspberry Pi

I'm always one for saving a bit of money and many years ago I came up with the plan that I could save a lot of money in printing costs if I didn't own a printer. It is a great idea, you'd be suprised at how much unnecessary printing you get up to and there's always the kick in the teeth of inkjet printer ink being more expensive than gold!

However, ever since I learned how you could refill some laser toner cartridges for next to nothing, I've always wanted one of Brother's epic little mono laser printers. Over the years I've been watching them add features such as the fully enclosed paper cartridge, ethernet conectivity and even WiFi.

So when it came to our trip to Cambodia I had to bite the bullet as we were travelling around a bit we had far too many hotel, bus, tours, boat rides and flights that it was going to be a logistical nightmare to beg, steal and borrow printing capbility.

I was initially going to get on of the aforementioned Brother HL-L2XXX models, but a quick perusal on ebuyer.com sees that the ethernet version is the thick end of £80. They did however have a seemingly better, wireless enabled Dell E310dw for a measly £30. The reviews looked good and a new Dell high yield toner cartrtidge was only the same price as the extra the Brother would cost to buy + the cost of a refill kit. So if I couldn't refill the cartrigde I would be no worse off.

So I took the plunge and couldn't be happier, I can print my favoured booklet print and the printer was super easy to set up without installing any bloatware.

When it came to installing the printer on the raspberry pi it got a little trickier, Dell does list it as being linux compatible but only actually supplies it with drivers for Ubuntu. I tried a few of the tutorials of installing generic PCL drivers, i386 drivers and a bunch of other stuff but to no avail.

It was during my scratching around the internet that I actually discovered that my Dell E310dw was actually a rebranded Brother HL-L2360, magic. Unfortunatley there ain't no Brother Raspberry Pi either driver either but a bit of uncovering later led me to another option:

• Install CUPS on your Pi using these instructions

             http://www.howtogeek.com/169679/how-to-add-a-printer-to-your-raspberry-pi-or-other-linux-computer/

• Add printer by clicking the button on the administration webpage and when you get to the diver selection list, go for:

                    Brother 'HL-7050N-CUPS+Gutenprint'

Et voila.

To test it out, set it as your default printer in the administration tab and then try this bit of code:

echo "test" | lp

Excel: convert Postcode in x,y grid co-ordinates

This is a cracking blast from the past, I was digging through my old personal macro excel backups when I came across this beauty.

It dates back to my recycling plant development days, I had to devise an algorithm to work out the most economical site placements for food waste recycling facilities for a given highly dispersion of waste arisings.

The data I had to work with was in the form of a list of postcode locations along with annual waste tonnages. I had originally intended to lookup the addresses against my longitude/latitude list however turns out the maths to convert long/lat into something usable in excel was rather complex.

I've since worked this long/lat conversion out and have been meaning to write it up into a blog post but it is frankly pretty boring even by my standards.

Screen scraper

So back then my next angle of attack was to use a screen scraper - a bit of code I could fire the postcode off into the interweb and get back the result I needed. I already knew the site I was going to leverage, Streetmap.co.uk, as I'd been using it for a while for all things MTB as it was it was the only service at the time where you could view Ordnance Survey maps.

The code

Paste this code into your VBA Project:

Then all you need to do is place you cursor in the cell next to the postcode you want to look up and run the macro.

If you have a list of postcodes the code will happily rattle down the list for you.

Uses

But what use is it to me I hear you ask? Plenty, I wasn't expecting you to need a recycling plant location algorythm, it has lots of uses:

• with a bit of Pythagoras you can determine the distances between two locations
• You can use it to plot locations (on a graph)
• (well two, I'm sue there must be more!)

How to assign a shortcut key to the Explode Excel addin

Sorry to bore you with yet another Explode addin post, but I think this is worth it on a couple of fronts. Assingning a shortcut key to the explode plug-in makes more awesome - being able to devle into those dependants even quicker.

Also seeing as as my initial explode post is one of my most read posts, I know you'll all lap it up.

There are two ways of doing this, either to assign a shortcut in the ribbon macro window this is by far the easiest method, or to code it in VBA.

First Method

The trick here is that you need to know the name of the Sub which calls the main prog, however since the VBA project is locked for viewing you'll have to trust me in knowing what it is called.....

First of all you have to enable the developer ribbon - instructions here.

Then its just a matter of clicking the Developer tab, selecting the Macros button and type the following in the top box:

Explode.xla!ShowExplodeForm

If you spelled it correctly the options button should make itself available where you can then input your shortcut of choice. You'll need to try to avoid an already take key combo (so stick to Ctrl-Shift-something), my preference is Ctrl-Shift-X. To enter this just hold down shift and type X in the box then hit OK. You can then cancel the dialogue and merrily save a few seconds a day!

Second method

The next method is to insert some code into the ThisWorkbook object in the addin's VBA project, however since we've already established it is locked - I have a workaround - we'll put it in our Personal Macro workbook.

First off we'll have to enable the addins references to call from our code, from the menu bar go to Tools then References and check the VBA_Explode box.

Then all we need to do is paste the following code in your personal macro book's ThisWorkbook object:

Option Explicit

Private Sub Workbook_Open()
       
    Application.OnKey "^+X", "VBA_Explode.ShowExplodeForm"
End Sub

Hit save up at the top and reopen Excel and test it out - Ctrl-Shift-X should get you rockin'.

Create your own custom formatting shortcut in excel

Like with most things in life, the longer you do something for the more you get set in your ways. Excel is no exception and if I'm a sucker for anything it's number formatting.

Maybe it's the accountant in me, but I think negative numbers are easier to read and look far more stylish wiht a bracket around them. Then if you are going to use a bracket you must sort the alignment out.

Next is the zeros, it's gotta be just a dash, trust me. If you are looking at a model and you've go a sheet full of zeros you need to know if they are really a zero or just a small enough number to be rounded as zero.

Here's it in it's custom formating code along with the percentage style also:

#,##0_);(#,##0);-_)
0.00%_);(0.00%);-_)

All we need is the following bit of code hooked up to assigning a shortcut (in this case Ctrl-Shift-M), using the shortcut cycles through the formats, done!

Paste this code into a macro module in you personal macro workbook:

Sub mcFormattoggle()
'
' MC_Format Macro
'
  numformat = Cells(Selection.Row, Selection.Column).NumberFormat

  Select Case numformat
    Case "General"
      Selection.NumberFormat = "#,##0_);(#,##0);-_)"
    Case "#,##0_);(#,##0);-_)"
      Selection.NumberFormat = "0.00%_);(0.00%);-_)"
    Case Else
      Selection.NumberFormat = "General"
  End Select
End Sub

Then in the ThisWorkbook object paste this code which will assign you shortcut. In this instance it is Ctrl-Shift-M

Option Explicit

Private Sub Workbook_Open()
       
    Application.OnKey "^+M", "mcToggleFormat"
End Sub

Save then restart Excel and you are away!

Explode Excel Addin - download Latest 2008 version

After many months struggling with my favorite excel explode addin, I've finally discovered the solution to my issues.

The problems started after I arrived at a new job - the computers were super locked down and they had Office 2010. I had problems installing the addin and even when it was working there was no 'Explode..' option on the right-click context menu - my main way of accessing it. I also couldn't click outside the window when it was running, most inconvenient. I eventually got used to using the ribbon shortcut but then I ran into more trouble.

I got myself a new laptop, this one was a vast improvement on the brick I had before - everything ran super quick, however it had one major issue - it had Office 2015 64bit installed. I could get the addin installed but it wouldn't run. I spend an age tweaking the security and trusted location settings to no avail.

It was then I got thinking, I know that it had been discontinued some years ago but did I have the latest version installed, the quick answer was NO! My mistake - not following my own blog posting, Checking against the version I had on my home PC I had downloaded it from a different website and had the 2002 edition not the 'latest' 2008 version. Dumb-ass.

So to sum up:

The easy way to find and delete duplicates in SQL

Next in my series of helpful SQL code is duplicates, I seem to always be having issues with duplicates. This annoys me immensely as all of the issues comes from other peoples data I have to work with.

No problem I hear you say, google is awash with ways of how to delete them. Trouble is these are all based on blindly deleting superfluous rows without care of how they got there and any specific criteria on the order of how to delete them.

Identifying duplicates

First up is you have to identify the duplicates in order to understand why they are duplicated in the first place, using this nufty bit of code we can pull up a table only containing the duplicates:

select * 
from [#Table1]
where [Person] in (
    SELECT [Person]
    FROM [#Table1]
    group by [Person]
    HAVING (COUNT(*) > 1)
)

Deleting rows

Having checked the duplicates we can proceed to getting rid of them. If the entries are pure duplicates and we just want to delete the extra we have two ways of proceeding. We can either

• delete the duplicates in situe or
• or filter out the duplicates in a query

Delete duplicates from a table

Firstly we add an automatically incrementing number field to the table, then delete the record that is not the first occurrence of item in the selected field:

ALTER TABLE [#Table1] ADD AUTOID INT IDENTITY(1,1)
DELETE FROM [#Table1] WHERE AUTOID NOT IN (SELECT MIN(AUTOID) FROM [#Table1] GROUP BY [Person])

Nice and straight forward.

Filter out duplicates in a query

The trick with this one is that you just have to group by all the fields you want to un-duplicate, though this is invariably all of them!

select AccNo, Person, Bikes from #table1 group by AccNo, Person, Bikes

What you have to remember is that you need to know which fields are duplicated in the first place.

Smart deletion of duplicate records

This is the scenario where we've looked at the duplicates and decided the course of action needed to weed out the duplicates. This is usually to route out the oldest date or the lowest values, this is the solution you need - sort the dataset into a new table then go ahead and delete the superfluous rows:

select * into #Table2 from #Table1 order by [Date1] desc, [Field1] asc
ALTER TABLE #Table2 ADD AUTOID INT IDENTITY(1,1)
DELETE FROM #Table2 WHERE AUTOID NOT IN (SELECT MIN(AUTOID) FROM #Table2 GROUP BY [Person])
ALTER TABLE #Table1 drop column AUTOID

Now all we need to do is get people to check their data for duplicates before they send it to save us hours of work!

Best case for Sony a5000 a5100 with 16-50mm lens

After a serious amount of pestering from Ali over the last six months it seems I have caved and bought her the camera that she's been after for a while now to pursue her newly found hobby.

I'd been looking at Sony's offerings of their a5/6xxx cameras for a while and whilst I was confident the base a5000 model would more than suffice Ali's needs I just couldn't get over that it didn't have a physical dial to quickly select camera modes.

I was also getting majorly sucked into to the up-sell - each model up have more and more temping features. That is, all with one major drawback - the a6xxxs don't have a 180 degree flip screen. A deal breaker in out eyes as Ali's incessant need to take selfies everywhere soon gets tiring when we have multiple retakes cos I aint looking at the camera and all the shots are never quite perfectly framed in the background for my liking.

The clincher for me to buy the base model was the release of their newest and greatest a6300 - seemingly excellent, that is until you see the price tag: £1100, OMG! This is not the camera for me seeing as the a5000 is a quarter of the price - one quick trip to John Lewis and I'm sorted, throw in 3 years accident cover for £20 and I'm done.

This had been decided the weekend before our Berlin trip so there was not enough postage days for me to order a suitable case online. I had taken the case from my camcorder to test out but it didn't fit. So in an 11th hour I perused the bag section of John Lewis and happened upon this bad boy - the Lowepro Dashpoint 20 bag.

So perfect fitting you'd swear it was made for it. Nice snug fit, plenty of protection, removable strap and a tiny pocket to take a memory card. Best still, the blue one had 1/3rd off, done.

So off to Germany it is then. I'll report back any issues but all is looking good.

Rivington MTB ride

I'd been itching to ride Rivvy ever since I realised that it was here that they held the Manchester Commonwealth games.

I'd had a couple of attempts which ended up not being that great but it is when I happened across this route that the trails really hotted up.

This route takes in the (obligatory for me) broken road descent and a lap around the Pike followed by the climb to the top of Winter Hill and the San Marino descent (as ever, please check legal rights of way before cycling).

Next a bit of road work up to Belmont then over Rivington Road followed by a loop around the north of the Reservoir and a loop of Healey Nab.

How to add an auto incrementing ID field in SQL server

So it turns out that SQL is really quick to pick up, once you have the (very) basics down everything you need to do fall intuitively into place as it is all based on the first principals of a select statement. And even if you do get stuck all answers are a very rapid google search.

The biggest trouble I have with starting off programming any new languages is that I expect to hit the ground running and just jump into the complex stuff without going through the basic tutorials. THe problem with this is it can them be tricky to actually google the right term to get the answer to your problem. So in light of this I've decided to log all the little bits and bobs which fall into this category as I come across them.

So stay tuned for further posts in the series under tag SQL.

One of the first things which fell into this category were row numbers. The normal way people tell you to deal with this it is to append a table with a new column with the row numbers in. Nice and easy:

ALTER TABLE #Table ADD AUTOID INT IDENTITY(1,1)

This is all well and good but what I need most regularly is to add row numbers into outputs of queries as I run them, I don't want to have to write the results to a table then append the row number on. Fortunately the code is simple and it will also let you order the results at the same time by using the OVER function:

Print'Add row numbers to an output'
select 
     ROW_NUMBER() over (order by [FirstColumn]) as AUTOID
    , *
from #Table

Pass the Pigs Ultimate Travel Edition with scorecard download

We are great fans of travel games in our household, they literally travel the world with us.

I'm not saying that the original casing for pass the pigs is enormous but when you are only taking a messenger sized bag for a week away every inch counts.

The system we run with is to use my custom printed scorecards (see download link below) and simply carry the pigs around in an old camera battery case. There are many more scorecards than you'll need on one trip on just a couple of sheets, and as you can see from the picture, ours are plenty stained and dog-eared.

 Download Scorecard

Be sure to check back regularly for my next must have travel game.

How to add better comments into SQL server

Whilst I'm firmly in the 'Tried and Test' camp for most things in life and I may only be new to SQL programming but there are just sometimes when I have to buck the mainstream trend and do it my way.

One of these occasions is comments in SQL, the widely adopted way would be to use the prescribed double dash -- or the widely adopted across multiple languages slash-star:

-- comment here
/* another type of comment */

However in this instance I'm going to declare my way is best! I put my comments into a print statement (not in the middle of the code obviously)

Print'Comment here'
Then code

Not only does it stand out better in your code editor's syntax highlighter but it makes the messages log more verbose when you are debugging or reconciling record numbers throughout your scripts.

How to connect a photoresistor or light dependant resistor to an ESP8266-12E

Having purchased my first Raspberry Pi a couple of years ago now I've still not got round to implementing some Internet of Things (IoT). The trouble was that I wasn't really convinced it was suitable for the job as I wanted something able to be battery operated to work in remote situations as the power draw was just too great.

I liked the idea of the Arduino stuff but it was as expensive as RaspberryPis once you've hooked it up with wifi and the like.

A couple of weeks ago I stumbled across the awesome ESP8266 boards - just what I was looking for. Small, inexpensive and had wifi and an antenna built in. When I say inexpensive I really do mean it you can get going for just a couple of quid.

For my first foray into the scene I opted for a NodeMCU v1.0 development board as it has a USB connection for uploading firmware and power and if you buy a slim one you can connect it to a breadboard for prototyping.

I already own the CamJam EduKit 2 - Sensors lying around as I hadn't yet got round to hooking it up the Pi but I wanted to try it out on the  ESP8266-12E. Trouble is there doesn't seem to be any decent instructions on the internet of how to hook up the light dependant resistor. After a bit of scratching around I found out the required resistor (10k ohm) and a circuit diagram from here and I was away.

What you need

List of parts required:

• ESP8266
• LDR / photoresistor
• 10k ohm resistor
• breadboard
• computer with Arduino IDE with ESP8266 library installed
• USB cable
• Male-Male Dupont/breadboard wires

Circuit Diagram

The LDR actually has a an analogue output so it gets connected to the A0 pin. Quite simple really, just wire your prototype up like this:

The code

Simple really, we will just use the example code in the Arduino IDE - go to

File/Examples/1.0Basics/ReadAnalogueVoltage

// the setup routine runs once when you press reset:
void setup() {
  // initialize serial communication at 9600 bits per second:
  Serial.begin(9600);
}

// the loop routine runs over and over again forever:
void loop() {
  // read the input on analog pin 0:
  int sensorValue = analogRead(A0);
  // Convert the analog reading (which goes from 0 - 1023) to a voltage (0 - 5V):
  float voltage = sensorValue * (5.0 / 1023.0);
  // print out the value you read:
  Serial.println(voltage);
}

The output

All we need to do next is to fire up the serial monitor, select 9600 baud and watch away:

2.16
2.13
2.09
2.07
2.06
2.04
2.03
2.02
2.01
2.00
2.03
2.09
2.15
2.18

You can also use the serial plotter to see a graph of your results:

Next...

You are then ready to integrate it into your own project.

Write letters that create impact - use a personal letterhead

Like most people nowdays in the modern technology filled society I rarely feel the need to write a letter, but on the rare occasion I do I want it to look professional.

It came as a suprise to me that none of my peers shared the same passion as a quick poll showed that nobody had their own personal letterhead.

As I mentioned before my snail mail correspondence is hardly prolific - usually limited to posting a cheque to the bank a couple of times a year because I live so far away from my nearest branch it's laughable.

I must admit I can probably see why, its taken a fair bit of creativity and Word skills to knock it out along with a chunk of time to get it to where it is. It's pretty old now though and maybe showing its age, but it had an update a few years back where I added a text box where you can enter the recipients address which will handily line up with the window on a envelope should you have such paper stock.

Anyway, enough of my blathering, the purpose of this post was to share my letterhead template so we can all add a bit of punch to our correspondence. You can download the word version below and update for your own details.

Enjoy.

 Download template 

Musing of the day 20-Nov-2015

I'm currently in Ghent for the famous 6 day race at the velodrome. The atmosphere is awesome the best inner circle is packed with beer drinking spectators enjoying the spectacle and dancing to the music the DJ is pumping out in time to the racing. All this beer drinking has its downside though, every toilet in Belgium seems to be chargeable, even in the velodrome. So today’s tickling is the fact that a 500ml bottle of water will cost 27 euro cents to drink, but it’ll cost you 50cents to pee it out again!!

Best way to optimise images and pictures to display on the web

Conscious that hosting a website from a home internet connection from the Raspberry Pi may lead to slower load time than you'd want I've been working on speeding up its response.

I've always been focused on server speed throughout its whole development in a bid to speed up load times. I think I've got it cracked – but the only thing I'm lacking is a decent workflow for optimising the file size of the images I'm serving up.

I played around with a few web and windows tools but the process all seemed to be time consuming and cumbersome.

IrfanVIew

After a while I settled upon an add in from a tried and tested bit of software that I've been using since I can remember - IrfanView using the 'Save for web' plugin. It is easy, quick and also tune-able whilst also providing a live view of what the output would look like.

It is actually really quick if you've associated it to automatically open image files, my workflow was to drop the required image into the folder on the web-server, hit enter, select the save for web - the GUI pops up and you just hit save to overwrite the file with the optimsed settings.

The results were good, I was getting some good file size reductions. Although it doesn't take too long what is really needed is some automated way to transform your image in a single click.

ImageMagik

I've been a user of imagemagick for a while now so this was my first port of call in testing. After a bit of playing around I soon realized that all I needed was to to just change the quality to 70. Easy peasy, just need to run the following code and you are done:

convert sample.jpg -quality 70 output.jpg

Note for windows users, if you try and run convert.exe without specifying a path the the executable it won't work, this is because there's another convert.exe system file which it uses in preference. To fix this I simply rename it to img_convert.exe and dump it into my windows directory. The code would now be

convert sample.jpg -quality 70 output.jpg

Exiftool

The next trick is to remove all of the EXIF metadata attached to the image, you wouldn't probably think it isn't many bytes worth but a) they all add up and b) you'd be suprised how much info the modern digital camera now spits out.

So go ahead and install a Exiftool on you system and run the following code:

exiftool -all= output.jpg -overwrite_original -P

Bringing it all together

Now all we have to do is clobber the code together and we are away.

Linux

go ahead and create a new file:

nano optimiser.sh

and paste the following code in:

convert $1 -quality 70 $2 && exiftool -all= $2 -overwrite_original -q

we then have to make it executable with:

chmod +x optimiser.sh

and now we can run it using:

optimser.sh input.jpg output.jpg

Windows

It is a similar story for windows, just create a file called

optimser.bat

open in notepad and paste the following:

img_convert %1 -quality 70 %2
exiftool -all= %1 -overwrite_original -P 

Drag & Drop

With windows we can add an extra flourish - you can set up the batch file so that you can optimise your jpg by just dragging the image on the .bat and it will convert it automatically in the GUI without going near a command prompt:

Create a new file:

optimiser_DD.bat

and enter this code:

@echo off
copy %1 %~n1"_"%~x1
img_convert %1 -quality 70 %1
exiftool -all= %1 -overwrite_original -P

What the code does here is to copy the input.jpg to input_.jpg then optimise the original file.

The results

I was quite impressed by the results, I tried it on a plethera of files. The most notable reductions come from digicam images, here's a typical example

Original digicam jpg: 2843 kb
Reduced to quality 70: 650kb
then exif info removed: 595kb

Extra credit

Head over to GitHub for the extended versions of the scripts with multiple command line options which gives you control of the quality parameters and lets you also resize the image:

Web image optimiser on GitHub

Musing of the day 15-Nov-2015

Another 'never thought I'd say it' musing of the day, it turns out I actually found a legitimate use for facebook!

It's not that I'm against faffing on the internet - hell I'm probably one of the worst proponents, it is just beyond me why you'd want to depress yourself by endlessly scrolling through facebook as the numerous studies have proven. You should follow my lead and stick to spending hours perusing ebay (mostly for torches) or anything generally cycling or Raspberry Pi related.

So it turns out I forgot to rate the last film I watched on my filmography: Ted 2. I'd mean't to do it the next day but obviously forgot. Having just remembered I'd yet to record it's rating I asked Ali when we'd seen it, it was then she reached for her phone, fired up Facebook and rewound her chat log back a couple of weeks to locate when she had told her niece we'd seen it.

I'm not sure this recall ability justifies the endless lost hours spent on the app or the privacy issues of surrounding it, but it did solve my dilemma and its a first for me being grateful to FB!

TF2 Extreme Wet Lubricant

How to create real time graphs of CPU temperature on a Raspberry Pi

Probably heavily linked with my inner analyst, for a while now I've wanted to create some real-time graphing of some of my raspberry pi stats. I've had a couple of stabs at it but getting it to actually work has eluded me until now.

The below example guides you through how to plot your CPU temp as a graph to an image file, the code is in my github repository where I'll hopefully add further functionality in the future.

How to guide

First off you need to install Python3

sudo apt-get install python3
sudo apt-get install python-setuptools
sudo apt-get install python3-pip

Next install GNUPlot

sudo apt-get install gnuplot-x11

Then we need to setup a working directory:

cd ~
mkdir plottemp
cd plottemp

create the necessary files:

nano datagen.py

Paste in the following code:

#!/usr/bin/env python

import sys, os, logging, urllib, datetime

def fetchtemp():
	cmd = '/opt/vc/bin/vcgencmd measure_temp'
	line = os.popen(cmd).readline().strip()
	output = line.split('=')[1].split("'")[0]#+' C'
	return output 

format = "%Y-%m-%d,%H:%M:%S" 
today = datetime.datetime.today()
s = today.strftime(format)
output = s+' '+fetchtemp()+'\\n'

print(output)

with open('/home/pi/plottemp/tempdata.dat', 'a') as f:
	f.write(output)

Create this also

nano plottemp.sh

and paste the following:

#!/bin/bash
echo "set terminal png size 900, 300
set xdata time
set xrange [ time(0) - 86400 : time(0) ]    # 86400 sec = 1 day
set timefmt '%Y-%m-%d,%H:%M:%S'
set format x '%H:%M'
set output '/home/pi/plottemp/tempplot.png'
plot '/home/pi/plottemp/tempdata.dat' using 1:2 with lines" | gnuplot

make it executable

chmod +x plottemp.sh

next all we have to do is to run the script every 5 minutes (or whatever interval you choose) with cron:

crontab -e

and add this line:

*/5 * * * * python3 /home/pi/plottemp/datagen.py && /home/pi/plottemp/plottemp.sh &> /dev/null

and there we have it, it will generate a file called:

plottemp.png

The result

If you have successfully implemented the instructions you should get something like this:

How it works

I'll very briefly walk you through how it works - there's a cron job running every five minutes which firstly runs a command line function which reports the CPU temp and saves it to a log file. Upon completion it then runs a GNUPlot command to generate a graph of the last 24 hours logging.

For extra credit

There will be a few formatting tweaks to the graph to come and I hope to include more stats so stay tuned folks!

Its thanks to this post at raspberrypi-spy which eventually got me on my way. Thanks Matt

The ultimate travel wine glass

It was whilst perusing my local Quality Save where I happened upon an old skool jam jar for sale that my mind caught upon something but I couldn't quite figure it out....

The mason jar in question was a heavy old thing which had an integrated drinking handle, it was this that provoked my attention - I am regularly in the need of on-the-go drinking vessels and am always on the lookout for new ways to transport liquids.

This particular item whilst igniting my attention didn't quite gel as a proposition. With being bundled with a pair of socks and priced at £1.49 it was within my value for money parameter. The trouble was it was just a bit too heavy and cumbersome, the handle turns out to being largely superfluous as you wouldn't use it to carry any hot liquids as you'd use a proper travel mug or flask.

The main problem for me was that it just doesn't sit right paying money for a glass jar when I'm regularly amassing them through my regular course of eating at home. And with that my analysis of the idea was parked and I left the jar on the shelf whilst I could work on the idea further and would revisit when I'd fully formulated.

It didn't take long for further inspiration to arrive. It was whilst watching the new series of The Walking Dead a couple of days later there was a scene in a barn that had been made into a makeshift bar. The drinking vessels of choice for their ingestion of hooch was in fact jam jars, this demonstrated to me that such a use was more than just possible - it was somewhat preferable!

At this point I was still struggling with the idea, I'd not yet cemented the usage protocol so continued to hold off on any purchases.

Fast forward to the next weekend when I was eagerly packing for the second round of Revolution at the Manchester Velodrome that the final inspiration struck...

It is not that I am tight (well I am but not in this instance) but I do enjoy a drink whilst watching the track racing. The problem is that there is always a massive queue for the bar  and there is just not convenient time to visit it without missing any of the cycling action. Combine this with the fact that the booze is actually a rip-off as none of the beer is actually on draught - they open a can from the fridge and pour it into a plastic glass!

If you start off with the concept that you are allowed to bring your own picnic into the Velodrome, you can quite quickly make the leap that you can also bring in your own booze.

Queue the final piece of the puzzle, it was my long overdue turn to be the designated driver for the evening so I was collating the appropriate beverages for Ali to consume. Turns out we didn't have much in which would tickle her fancy, I managed to rustle up a can of pear cider and a large glass of Rioja left over from the previous evening.

This was my eureka moment, grabbing a jam jar from the cupboard I poured the wine in and screwed on the lid -genius. The hardest part was actually convincing Ali that it was a perfectly sane and practical idea and it wasn't till I threw in the Walking Dead reference that she even started to take me seriously. 

Turned out to be a great idea, not only did she enjoy the vino, we had a disposible recepticle that we wouldn't feel guilty that we were destroying the planet should we chuck it away on the go. Don't panic though, we didn't throw it away - it came back home with us and it has had a succession of re-uses including several picnic wine outings, home made chutney and several batches of kimchi.

Custom sprocket ratios for 9 speed Campagnolo 12-26

One of the great thing Campagnolo pays particular attention to that other manufacturers often overlook is gear ratios.

A regular rider might only notice jumps between sprockets when they are excessive such as on super wide blocks, the reason for this is that we automatically compensate for a non optimal cadence by slowing down or speeding up. The racers among us will note that in a 

The compromise Campy makes in retaining minimal jumps between ratios is that they sometimes sacrifice top and bottom end gears. It's an acceptable trade off, keeping the most used middle gears in preference to the rarely used extremities.

You particularly notice this phenomenon the fewer numbers of gears you have. In a modern 11 speed setup Shimano now offers an 11-28 block which is now the default choice for even pro riders. If you convert this spread into a 9 speed cassette you'd lop off the top and bottom gears leaving you with a 12-28 spread. Unfortunately for Campagnolo users this is deemed to have too many jumps still and the widest they will supply you with is a 13-28.

Luckily for us old skool Campy users we can easily knock our own ratios up using Veloce level components. If you consult the Campy spare parts catalogues and tech specs you will note that the ramp patterns  mean you can mix and match most of the ratios and preseve the finely tuned shifting characteristics.

If you take a look at the chart (full pdf here) we can see in order to get a 12-26 block all we is to lose the 13 and 16 sprockets from a 13-26 and slot on a 12 and 13 from a 12-23.

The genius of it is that if you study the sprockets model numbers you will see that the ramps betweens the ratios stay perfectly synced as Campy intended them to be.

As an additional bonus it needn't be an overly expensive procedure - nearly new 12-23 cassettes can be pocked up uber-cheaply from ebay as nobody really uses them today. 

Head over to the support section of the Campagnolo website and indulge youself in all the spare parts catalogues and technical manuals you could ever dream of!

How to reset the default cell formatting in an Excel spreadsheet

One of the more irritating things you can come across in someones elses spreadsheet (on the basis that I've never managed to reproduce it myself) is when somehow the default style has been changed to spit out a daft format of 00:00:00 for seemingly any number.

This is especially apparant of you are a regular user of the clear formats function. It is a staple of mine in a few different circumstances, notably in clearing other peoples gash formatting & when you want to quickly see more significant figures regarless of the current formatting you have.

There are two ways to do this, you can put it in a macro or manually

Sub ResetNormalStyle()
    ActiveWorkbook.Styles("Normal").NumberFormat = "General"
End Sub

or my favorite personal style

Sub ResetNormalStyleMC()
    ActiveWorkbook.Styles("Normal").NumberFormat = "#,##0,_);(#,##0,);-_)"
End Sub

Removing tissue residue on clothes from washing machine with asprin

We've all done it, left a tissue or some paper in a pocket of an item of clothing and then washed it only to find all of your favourite black clothes come out covered in white flecks.

Even after you've washed it again the problem persists. The timing is probably unfortunate as well - it annoyingly happened to my faithful Tour de France T-shirt just before the grand depart in Yorkshire in 2014.

On this occasion to try and resolve the issue I grabbed a roll of packing tape and tried to use its stickiness to get it off. Unravelling long lengths of tape and sticking them to the tshirt. It did have some effect, but the whiteness was still noticeable.

As my Tour de France T-Shirt (sadly) only gets an outing during one month of each year, I had forgotten about the problem until I retrieved it from the bottom of my T-shirt pile come next July - so had to suffer the indignity of the unsightly blemishes for another year!

Not to be caught out for next year, I'm getting ahead of the game and trying to get it fixed asap though, if you've ever googled this problem you'll see that the only real solution would seem to be soaking it in an aspirin solution. Though any significant amount of references to it actually working were sparse as the whole www seems to be just regurgitate the same urban myth.

Given a packet of dispersible aspirin costs less than 30p and the likelyhood of it damaging my clothing was low I though I'd give it a shot. It was just as I was doing my final wash in preperation for going on my recent safari holiday that I gave it a shot on a pair of black shorts which had suffered the same fate.

Method

Put a couple of disposible aspirins in a bowl of warm water, let them disolve and give it a bit of a mix. Then all you need to do is to let soak overnight and then I put them through the wash again.

Result

As you can see from the picture above, it did absolutely nothing, what a waste of time!

Musing of the Day 9-Sep-2015

I think that my work colleagues have finally worked me out!

Yesterday I was quetly working away at my desk when I felt a sneeze coming on. Sure enough it arrived and was promptly followed by the usual exchange of excusses and blessing. After which I remarked about my satisfaction of the preceeding event - it was one of those sneezes that you enjoy, that strange enjoyable sensation with no bodily fluid issues.

It was at this point that somebody asked me the full analysis of the sneeze. Further remarking that in line with my usual attention to detail they required feedback of the rating, rank and category of the event .

This, I'm afraid stumped me, it turns out that sneezing is the one thing I haven't applied my usual full analysis to!

How to make and set up a twitter bot on your own server / Raspberry Pi

As a follow up from my ealier post introducing my twitter bot I thought I'd tell you how I did it and how to set one up on you own RaspberryPi or other Linux server.

How it works

It's not that tricky when you think it through, it is just hooking a few existing processes together to create your working bot. The only real tricky part for me was that I hadn't used Python before and had to learn it from scatch!

Here's a quick run through of what I used:

Twitter API - stating the obvious really but you need to use the api to do anything on twitter!

Twython - a Python wrapper for the the twitter API, it takes all the hard work out of posting and reading tweets

Python3 - you'll need to do all your programming in python to make use of the Twython

Python daemon - for running you script as in the background as a service

Wolfram|Alpha API - to answer questions

Wikipedia API - to get more answers

What you need to do

There is quite a lot of things to cover here so I'll not go into much detail about those tasks which are easy to complete with a little googling and I'll concentrate on the things which I struggled with.

Twitter

First off you'll need a twitter account, I started a new one just for my RaspberryPi to tweet from. Then you'll need to go ahead and create an app within your twitter account and generate some keys for use with the API.

Wolfram|Alpha

Same here, open an account and set up an app to get an API key

Set up your RPI

Folder

You'll need a folder to put all your code in, I set mine up in a directory in my home drive, something like:

/home/usr/pi/bin/tweeter

Use these commands to get you going:

cd ~
md bin
cd bin
md tweeter
cd tweeter

Daemon service

Next up you need to download the Daemon class suitable for Python 3.x - daemon3x.py script and save it in the directory. You can do this in command prompt:

wget http://www.jejik.com/files/examples/daemon3x.py

Install Python3

run the following in the command prompt:

sudo apt-get update
sudo apt-get install python3

Install Twython

run this from the command prompt:

sudo apt-get update
sudo apt-get install python-setuptools
sudo easy_install pip
sudo pip install twython

Install my tweetbot script

Download tweetbot-d.py and save it to your directory.

wget https://www.childs.be/data/uploads/tweetbot-d.py

Or you can get hold of the code over at GitHub:

tweetbot code GitHub

Open it up in your favourite text editor and add in your API keys for Twitter and Wolfram|Alpha, then change the twitter ID of the stream you want to follow and additionally the superuser id which will enable you to turn the tweetbot off remotely should you need to:

nano tweetbod-d.py

Save and exit:

Ctrl+O
Ctrl+X

Fire up your tweetbot

We should now be ready to start it up, you can use the following commands to start, stop and restart the bot

python3 tweetbot-d.py start
python3 tweetbot-d.py stop
python3 tweetbot-d.py restart

Logging

By default the script will log all activity from the bot to a file named

tweetbot.log

you can check on everything your tweetbot has been up to in this file:

nano tweetbot.log

Start the tweetbot when your Pi boots up

If you want to start the bot up automatically when the server boots up follow these commands:

sudo nano /etc/rc.local

then add these lines (obviously change the directory to the one you have saved your script in)

# Start tweetbot service
echo "Starting tweetbot script"
python3 /home/pi/bin/tweeter/tweetbot-d.py start

Using the tweetbot

The script runs in the background monitoring any tweet containing your @twitter_handle, it then kicks in to analyse the content of the tweet, on discovery of the following hashtags:

#cputemp
#uptime

it will run a run some command line code to reply back to the user with the appropriate answer. On detection of:

#question

it will run a search through Wolfram|Alpha of the rest of the text in the tweet and reply with the answer. If Wolfram doesn't come back with anything it will try wikipedia as well.

Should you need to turn the twitterbot off for some reason when you can't access the Pi by usual means there is a kill switch built in. Simply tweet:

#stoptweeting

from your specified superuser account and the tweetbot will shut itself down.

Best case for Canon Legria HF M506 camcorder

So I got myself a new bit of tech a couple of months back, a new camcorder, primarily for our upcoming safari. This thing is genius, 50 times zoom, HD recording and a cracking optical image stabilisation.

I've been putting it through it's paces in the mean time on bird watching trips with Ali and it has been a brilliant tool for post trip analysis and identification as theres a lot of birds which look pretty similar to my eye.

I've also used it to good effect to record and photo the recent eclipse.

After I got hold of it I was pretty eager to take it on it's first outing so I was hunting around the flat for something I could use as a makeshift case so I could just bung it in my messenger bag and not worry abouting bashing up against things whilst roving about in the countryside.

In a stroke of genuis I though I'd try the new saddle bag I'd bought myself for Christmas. I'd purchased it from Decathlon for use on my on my full-susser to be able to carry the tubes/levers/inflation bits and bobs I'd need when I was travelling 'light' without my hydration pack.

I hadn't put it to use yet as it was still super wet and muddy out on the natural trails and I simply didn't have enough room under my saddle with the mudguard and lights mounted so I was good to try it out as the camcorder case.

Great fitting or what! If I told you that it was a custom made case for the camcorder you'd have never have doubted me. Couple of details that some might think is missing but that I have no use for are a pouch for a spare memory card and a more secure way to attach it to belts and other stuff.

There is an official unit available: here, but the fella is so good I've already bought another so I can use it for its original purpose as a saddle bag.

Rating 5 out of 5.

An enhanced name manager for Microsoft Excel

Named ranges are one of those marmite things in Excel, either you use them or you don't. I sit between the two camps and my utilisation depends on two things:

Firstly who the end user will be and whether they'll need to interact with them.

The other is a timesaving thing - their use will depend on if it will save me time over the long run.

My actual use of named ranges was pretty minimal as Excel's built in Name Manager is pretty basic being just a list.

However I after coming across this enhanced add-in has driven me to use them more and more.

I'll give you the blurb:

I'm not sure if there is much I can add, that is a pretty comprehensive list of its functions. I'd encourage everybody to give it a go. It is definately an invaluable resource for me as there are a load of spreadheets I come across that have been infected with hundreds of broken and external named ranges that are of no use to the function of the spreadsheet that can be banished quickly.

Install instructions

Download the zip from here. Unpack the files in the zip file to a folder of your liking and open the file called "Setup Name Manager.xls". Press the button in that file to install. I did have trouble with the latest version though, had to go back to an older version I had lying about.

Malaga to Manchester flight GPX tracklog - aborted landing

This flight was a cracker, made extra special not only for the fact it was the first I was able to fully record on my GPX logger for later analysis - but for the added drama of the flight.

First off the skies that day were a bit congested so we had to do an extra loop over Warrington whist we were waiting, but next there was great excitement.

As we were just about to touch down the pilot had to make an emergency abort. Cool. It was like a roller coaster ride - we took in a nice hit of G. Our sharp ascent was then combined with a steep banking turn - it felt like I we were aboard a fighter jet.

The excitement didn't stop there, on our second approach I was eagerly watching out of the window trying to figure out what went wrong when I spotted that the whole of the Manchester Airport fire and rescue fleet had mustered and were rapidly chasing us down the landing strip - awesome!

After we had safely parked up the other side of the airfield the pilot came on the intercom to explain what had happened. Apparently there was an aircraft on the runway which had lost radio contact with the tower, under such a circumstance they shut the strip down until they can get back in touch. We just happened to be in the best place for that to happen - just about to touch down. Then standard protocol dictates that the emergency vehicles are then mobilised as a precaution if an aircraft aborts a landing.

Extract a Surname from a string in Excel

There comes a point in every Excel users workflow that they've got to work with a list of names. Usually the data you get is in the wrong format, or you have to compare two dataset of different formats. This can be frustrating but here is a quick guide of how to manipulate them.

This tutorial is based around extracting the surname / family name from a variety of different formats, if you need to get to the title or firstname then it should be easily enough to adapt the workarounds.

Surname, Forename

Possibly the best format to get the data in, it is quick and easy to extract the surname, like everything in excel all you have to do is break the task into logical steps. Lets go with this example:

Fish, Phil the

Breaking it down, the portion of the name we want to take are all the letters before the comma. Using the FIND function and assuming the string is in cell A1 we'd use this formula:

=FIND(",",A1,1)-1

This results in the expected answer of 4. All we need to do next is use the LEFT formula to extract the letters:

=LEFT(A1,4)

We can then nest these formulas into one compact equation:

=LEFT(A1,FIND(",",A1,1)-1)

Et voila

Fish

Title Forename Initial Surname

It wouldn't be so bad to extract the Surname from a Forename Surname configuration, you just repeat the above steps but instead look for a space and use the RIGHT function along with LEN - which tells you how many characters are in the string so you can extract the space to last letter. E.g. for

Phil Fish

=RIGHT(A1,LEN(A1)-FIND(" ",A1))

Again giving you

Fish

the trouble is that the data rarely comes through like this. There are several permitations which will come through including middle name/no middle name/multiple middle names and title:

Phil Fish
Phil the Fish
Lord Phil Fish
Phil Overloard of All Fish

Back to our human logic, all we really have to do here is take the letters after the last space, unfortunately this is trickier than it sounds!

To accomplish our task we have to break this down into futher steps, first off:

Count the number of spaces

Counting how many occurances a string has can be calculated by counting the total length of the string minus the length of the string without the text you are trying to find. Pretty cunning really, you'll have needed a bit of out of the box thinking for that one. Here's the formula to use:

=SUM(LEN(<range>)-LEN(SUBSTITUTE(<range>,"text","")))/LEN("text")

or in the context of our example:

Phil the Fish

this formula:

=SUM(LEN(A1)-LEN(SUBSTITUTE(A1," ","")))/LEN(" ")

will yield the answer of 2.

Find the last occurance

Now we have found how many times the space appears we have to locate the last occurance, we do this by replacing the last space with something unique which we know won't appear for this we'll use a special system charachter Null and call it using the CHAR function which retreives is using its ASCII code:

CHAR(1)

So as a formula we'll substitute the space in the text string and find it:

=FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),2))

This give us the position of 9,

Extract the surname

Next all we have to do is proceed to grab the last 9 letters of the string as described earlier and we are away:

=RIGHT(A1,9)

Condense into one formula

Finally once we've figured out all the steps we can bung all the code into one formula:

=RIGHT(A1,LEN(A1)-FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),SUM(LEN(A1)-LEN(SUBSTITUTE(A1," ","")))/LEN(" "))))

And we are away. I told you it wasn't as easy as you first imagined!

The blog has now reached 50 posts!

Well, I didn't think I had it in me to write so much, I'm generally not a person with much to say (this is a lot for me!).

My general interest in all things tech had always since the early days compelled me to want to make my own website, trouble is you actually need to have some content to put on it.

After a while toiling I'd decided that I'd like to use Blogger and write posts about excel. Great, I thought I was onto something here. I knocked my first two posts out on the same day in 2008 (I've re-published them on this site), but my third took another 6 months to appear then nothing after that.

It was then another five years before I blogged again, this is when I had the idea that I could blog about my favorite bike rides. Great as this also gave me the green light to get a RaspberryPi - I had always wanted one but couldn't really justify it as I had really got a use for it.

Now I was on, hosting my own web server and knocking up my own site. Ali weren't so happy in the early days - apart from cycling, eating and working I did little else (not much sleep either) as I had a lot on my plate. I'd previously only lightly tinkered with Linux and I'd yet to write a line of php so there was a lot to learn.

It actually turns out that I had much more than bike rides to share (its all relative - much for me isn't that much), and hopefully I've another 50 more posts in me. I don't think I've a novel or even a short story in me, and I think there's zero hope of an autobiography!

Amsterdam Schiphol to Manchester flight GPX tracklog

My next blog entry from a string of flight inspired posts is another flight GPX tracklog.

This time from my weekend city break in Amsterdam. I initially thought this info might be a bit unique and interesting, but in the search to find some of the other flight details I came across Flightaware.com. It has some great info there, including to my amazement a log of the flight! Oh well, guess this will be ending my run of this type of post then.

If you want to compare my data to that of Flightaware's here is the link: EZY1836

The childs.be twitterbot is alive

My latest project is now live, the @childs_be twitterbot is a go.

The original idea was to have the pi tweet pictures from the barrelcam, at regular intervals. I had that running quite quickly and my attentions turned to adding functionality.

I came up with being able to tweet the server's uptime and cpu temperature, but it seemed a bit lame really. So some more thinking later I decided that it would have to be interactive.

So the idea of the twitterbot was born (OK, it's not the most original idea but hey). A few late nights and head scrathing later I had it up and running.

In the initial version I'd integrated all the functionality to automatically reply to a mentioned tweet, you can try it yourself by tweeting the Pi with these:

@childs_be #cputemp
@childs_be #uptime
@childs_be #barrelcam

or you can even string them altogether:

@childs_be #cputemp #uptime #barrelcam

Heres an example output.

Ask a question

Wanting to build on the functionality my thoughts turned to being able to respond to anything, so by hooking into to both Wolfram|Alpha & Wikipedia's APIs I'm now able to answer questions about a broad range of topics, you can try it youyself:

@childs_be #question what is a bicycle

Take a look at my post of how I did it and how you can run your own tweetbot.

Free water at Manchester Airport Terminal 3

Another bug bear here, one of several about Manchester Airport! The biggest issue with them is pickups - the sheer cheek of charging for you to go and collect your friends and familys from the termial is bordering on criminal.

Not content with charging an arm and a leg for airlines to use the airport they impose additional pain of 'just' £2.90 for those 30 seconds you need to chuck the luggage in the boot and get seated. It's not even like they are taking the lead from other airports as at Stanstead parking is free for the first hour, and even Manchester Piccadilly train station is free for the first 20mins.

The next is the overzealous security measures, whilst I admit that it is a necessary evil, there are some times where I don't feel it is as proportionate response especially the policy concerning liquids. Whilst I accept there is some level of threat im even more annoyed of having to buy bottles of water at airports. I never pay for water except if i get stitched up at a posh restaurant preferring to take my own drinks whereever i go. Its not that i am tight (well i am actually!) Its bad for the environment bottling transporting and selling the stuff when we all pay for it to be directly piped to our doors, then there's the issue of all the waste it creates.

Even so, if they are making you throw away your water before security they should at least provide a tap the otherside to let you refill.

It was on the solitary occasion that we were departing Manchester from terminal 1 on our way to Amsterdam when i spotted a couple filling up their sports bottles from a water fountain immediately at the exit to security. This has set me off and my new mission for holidays now where I have to avail myself of free water from every airport.

So when our next trip was to Paris departing Manchester terminal 3, my initial thinking was they must have to supply free water some where so the hunt was on. Much to Alison's relief we managaed to locate it pretty quickly, it was by the toilets in the food area right next toTrattoria Milano. Mission complete!

There are no such worries at Charles de Gaulle airport, water fountains are everywhere. Only speculating here but probably due to the fact that a) water in Paris is safe to drink, b) there are public water fountains dotted around the city and c) unlike the Brits, Parisiens would probably revolt or blockade the airport at the mere whiff of any shenanigans!

The secrets of Alex Dowsett's hour record bike

Had an awesome time watching Alex Dowsett's excellent showing at todays world hour record attempt.

First off some stats about the bike:

• Canyon Speedmax WHR custom built with track drop outs;
• 114inch gearing (55x11)
• Continental Tempo 22mm tubulars (13-15bar pressure)
• Campagnolo Pista disc wheels
• Weight 7.71kg

He had a total of four frames made and had them all built ready so he had threee spares on the day.

During his post effort interview he was answering a question about how far he could go he gave aways a couple of interesting snippets. He was saying that he wasn't sure of how he would do as as the developement of the bike was evolving he was getting faster.

One of the developements to his custom he didn't name but eluded to:

Some of the modifications will only last a hour

This interesting comment got me thinking, what kind of tweak would only be temporary? I immediately  was thinking that it may be some kind of  spray on / liquid based enhancement? I'll have to look into this one further...

Manchester to Paris Charles de Gaulle flight GPX tracklog

I don't fly that often so it took me a while to perfect the technique of logging my aeroplane journeys, but I've now cracked it and am getting good results so long as I've got a window seat.

Back in the early days of my Windows mobile powered HTC phones I had GPS available to me but unfortunately this was so early on in the smartphone era that use of all electronic equipment was banned during take-off and landing and use of a mobile phone was prohibited at any stage. Whilst the phones did have flight modes this obviously switched off the GPS.

Fast forward in time five years and after a long search I acquired the perfect GPS logger which I could use to geotag my photos - a i-gotU BT120 Travel & Sports logger. Whilst my little box of magic had a very sensitive GPS chipset, it was still having problems picking up a signal from inside the fusalage of the plane which is effectively a metal box / faraday cage. One flight I had the idea of balancing it on the window ledge, I was little nervous at first of resting a flashing brightly coloured device so much on display during the flight but as time went on and as the accptance of the use of electronic devices grew I was much more comfortable.

I soon started hitting another snag, aeroplanes suddenly stopped being designed with window ledges and having to hold it up to the window which was pretty inconvienient on any length of flight and ended up being a show stopper.

I've recently solved this issue with a stroke of genius whilst on a flight to Amsterdam - on this occasion I was testing out my Aldi GPS watch as it has has the best GPS reception of all my devices - I'd been thinking how I could suspend the reciever at window height when I realised I had an inbuilt mount, my shoulder! Problem solved, I stuffed the watch inside my clothing on top of my shoulder - I was now getting great reception descreetly. I tried it with my i-gotU and we are ggod to go there as well which is what I recorded this flight on.

I've another couple of flight traces to post; the pick of the bunch being a flight with an aborted landing due to another aircraft on the runway which had lost radio contact with the tower.

Display your server uptime on your website

If you host your own server and website like myself, you'll probably want to distplay your current uptime somewhere on your site. You are in luck, it is easy....

It uses the Linux command uptime and strips the unecessary gubbins from the output string:

$ uptime

This results contains some useful info including number of users logged on and loads as well as the uptime:

18:10:45 up 39 days, 22:04,  1 user,  load average: 0.07, 0.06, 0.08

All you have to do is to insert the following php code into your page and you are away.

<?php 
$data = shell_exec('uptime'); 
$first = strpos($data, 'up'); 
$data = substr($data,$first); 
$first = strpos($data, ','); 
$data = substr($data,0,$first); 
$data = str_replace('up','Powered by Raspberry Pi, ',$data); 
echo $data . ' uptime'
?>

You can see the results from my site at the footer of the page.

Powered by Raspberry Pi, 39 days uptime

How to log your own IP

Call me paranoid but ever since the antics of ACS:Law in the noughties I've been logging my IP address, it is not because I'm am an illegal downloader, my intrigue came about from the thought of how you would prove that you didn't if I was wrongly accused.

Stay with me here, but think of the things that can go wrong in the chain of collecting torrentors IPs, requesting the info from the ISP and collating and issuing all the thousands of law suits - there are any number of times a transposition of an IP address or a time and date could happen. Hell, even the music industry didn't like the way some people were doing it!

So my solution was to log my IP address, I've been doing it for five years now and even though my RaspberryPi is also logging my IP in a couple of different ways: through Syslog-ng by logging all of my router activity and through running a cron job to dynamically update its IP address to the DNS server.

It is trickier than you think to get your external ip address, ipconfig will only tell you your internal ip:

This is because of NAT Loopback so we'll need to find another way. Google will tell you your IP address if you google it, but it will be much simpler to get it from another source that returns just the IP address in plain text so we don't have to clean up the html. For this we are going to use ip.changeip.com, I'm not abusing their service here as I already use them as my DNS.

Instructions

These instructions are for windows users only, this is on the basis that if you are a linux user you'll either a) already know how to do this or b) know how to modify this for your own requirements.

Set up your folder

Create a new directory somewhere safe on your computer, I called mine logmyip, if you are using Windows 7 you may come into a few security issues when trying to change the contents of some of the files after you've created so you may as well go ahead and get it all working in another directory within your documents for example.

wget

Wget is a software package for retrieving files using HTTP, HTTPS and FTP, it is run in the command line and comes as standard with any Linux distro. For windows its a little harder to get hold of because you are expected to compile it yourself from the source code but you can download the windows binary from gnuwin32 at sourceforge, direct link (currently 1.11.4). Extract wget.exe from the zip to your newly created folder.

logmyip.bat

Next all we need is to set up the logging script, we'll be using a batch command. Open Notepad (or similar) then paste the following text in then save as logmyip.bat. Make sure you do not save it as a text document as it won't work, choose 'All files' in the save dialogue.

echo off
CHOICE /T 10 /C ync /CS /D y
wget http://ip.changeip.com -q -O %temp%\\\\ip.txt
set /p var1=<%temp%\\\\ip.txt
echo %date% %time% %var1%>>iplog.txt
del %temp%\\\\ip.txt
pause

logmyip.vbs

This next step is not compulsory but I prefer it as it will run the script 'silently', i.e. won't flash up any annoying boxes when it runs - we'll be using a bit of vbs for this. Same as above, fire up Notepad and get the code saved down:

dim fso
dim curDir
dim WinScriptHost
set fso = CreateObject("Scripting.FileSystemObject")
curDir = fso.GetAbsolutePathName(".")
set fso = nothing
Set WinScriptHost = CreateObject("WScript.Shell")
WinScriptHost.Run chr(34) & curDir & "\\\\logmyip.bat" & chr(34), 0
set WinScriptHost = nothing

Scheduling

There are a couple of choices here, depends how often you want to log you IP address. My Sky router is pretty stable and doesn't reset itself very often resulting in a change of IP - I only run it once a day. I've scheduled this but just getting it to run at startup. This is easy to setup - right-click the vbs file you created above and copy, then right-click somewhere in your folder and paste a shortcut. Then all you do is drag this shortcut over to the start menu, then keeping the left click on navigate through 'All programs' then into the startup folder. Job done  - it will then run every time you boot up.

If you need to log more often than this you can schedule it in Task Scheduler.

Running a my ip script on your own server

If you want to host your own IP revealer, just use this php code below:

<?php
    $filepath=$_SERVER['QUERY_STRING'];
    parse_str($filepath);
    if ($td=="y") {
        echo date("d/m/Y H:i:s", time())." - ".$_SERVER['REMOTE_ADDR'];
        }
    else {
        echo $_SERVER['REMOTE_ADDR'];
        }
?>

It is also coded that if you run the query string ?td=y then it will also spit out the server time and date, you can check it out here.

How to display Flickr images on your own website

I've struggled for a long time trying to find a way to elegantly and simply display my flickr photos on my site. Up 'til now I've had to stick with slideshow apps from other websites like flickriver.

This is until I found this blueimp Gallery when I was looking for some lightbox coding for my website. I didn't end up using the blueimp lightbox because I went with fancybox as it was already built into getsimple I just had to enable it with a tiny plugin. I did however utilise the code to gather a list of photos from Flickr.

The code is great; it calls the Flikr API people.getPhotos from ajax and neatly writes the thumbnail html into the blank div of your choosing.

It took me a while to tweak it to show my own photostream from its default interingness list, but now I have it I'm pretty pleased how simple, easy and effective it is.

Instructions

Add the page code

In the place you want the thumbnails to appear insert the following div & also place the link to the script in your html code:

Install the JS file

Then save the javascript file down in the corresponding place (you can use the download link at the bottom), then change the user_id in the script to that of the one you want to display the images of.

Download JS

You can also tweak the thumbnail sizes by changing the underscored suffix in line 35, see the different settings here. _q denotes 150x150px image. You can also change the size of picture the photo links to by changing the _b on line 36.

Additionally you can set the number of photos to get, the default is 100 but you can have up to 500 as required.

jQuery

Next you need to ensure you have a jQuery installed on your page, you can either do this via a link to a CDN or host the script file yourself.

Enjoy

If all went well, when you fire up your webpage you should see the thumbnails of your Flikr phhotostream.

How to add a copyright tag to your photos quickly

One of the first things I do in my photography workflow is to add a copyright to it.

My favourite and easy way of doing this is with a simple self created drag and drop file. It uses Exiftool which if you haven't heard of before is a massively useful command line tool for working with the meta data of your photos.

In an upcoming post I'll be taking you through the way I geotag my photos with it but as an initial article I'm going start of really easy.

What we will be updating

The following tags will be added:

• By-Line = "Marty Childs"
• CopyrightNotice = "(c)2015 Marty Childs, all rights reserved"
• Copyright = "(c)2015 Marty Childs, all rights reserved"
• usageterms = "For consideration only, no reproduction without prior permission"

One if the useful things with this method is that it reads the year the photo was taken and adds this in automatically to the notice so no need to worry about updating/backdating the script.

I'm not actually using the © symbol here as although it is possible to add it in some programs/websites don't handle it well so its best to steer clear.

Exiftool

So before you commence be sure to download and install exiftool. The instructions here are based on you using windows and having installed the .exe into your windows folder.

Decide where you want the shortcut

We don't need anything other than notepad complete but first you'll have to think about the folder structure you use and where you want to place it. All my photos are stored on a secondary hard drive in a folder called Digicam. From there all of my individual photographic outings/sessions are saved in this root folder simply named NNN - Desritpion [YYMMDD].

Since all you have to do to add a copyright tag to a whole folders worth of photos is to drag the folder onto a file we'll created I save all my tools in this root folder. Choose where you want yours created intially and head over to it in windows explorer. Don't worry if you want to change your mind later or need it in multiple places as the position won't be fixed and you'll be able to move it/copy it anywhere you like when you've created the first one.

Create the tag link

When you've decided right-click in a bit a white space within the folder you want it then select New > Text Document. Then double-click the icon you've just created then paste in the following:

exiftool %1 -By-Line="Marty Childs" -d %%Y "-CopyrightNotice<(c)$createdate Marty Childs, all rights reserved" "-Copyright<(c)$createdate Marty Childs, all rights reserved" -usageterms="For consideration only, no reproduction without prior permission" -r -P -k

Next File | Save As... the call it Add-copyright.bat then in the dropdown box below the name (assuming you are using notepad) select All Files (*.*), then Save and you can go ahead and close Notepad. Then delete the New Text Document.txt file you created first.

Using the tagger

All we have to do it just drag your a file or folder onto the bat file we just created and hey presto. I'd wait for the process to finish before doing any other interatons to those files but that is essentially it. To check it all we have to do is select a photo and right-click it, select properties and go to the details tab (in Windows 7) and you should see the tags.

Removal

If you want to remove the tags, create a file with the following instead:

exiftool %1 -By-Line= -CopyrightNotice= -Copyright= -usageterms= -r -P -k

Slow cooker goulash

How to make a Shimano disc brake bleed spacer block

The official bleed spacer is both really expensive to buy for what it is and hard to come by.

Have a look at Shimano tech docs site, here you can see the official tech sheet detailing bleeding spacer you need. You may get lucky at your local bike shop, if you ask nicely they may have one to spare. If you want to save a few pounds and make one yourself I'll take you through what you need.

The Park Tool site details the thickness of the spacer required - 10mm should do it, now all we need to do is to find something we can use which is the appropriate width. I racked my brain for a while trying to come up with something.

Having spent many an hour inspecting and measuring bits and bobs I came up with using one pence coins - they are the correct diameter and stacking them comes to the required thickness.

It is best to have a selection of ages of 1 pences available as they tend to loose thickness as they wear out. According to the Royal Mint, brand new pennies come in two different sizes, either 1.52mm or 1.65mm thick dependant on the material used.

Be aware though this is not an exact science as I tested six older looking coins against 6 newer ones - the result was 9.20mm vs 10.20mm so there must be another element in there also a bit of dirt and a bit of wearing thin.

This is great, we can use it to tune the tickness of bleed block you want. People rave about Shimano brakes as they aren't draggy. Whilst I admit all of my Avid brakes have a little bit if disc rub most of the time they have an excellent automatic bite point adjustment.

Shimano on the other hand do not have such a feature and as the pad wears you have to keep winding the reach of the lever outwards so the lever doesn't bottom out on the bars so you can actually stop (some higher end Shimano levers do have a manual bite point tweak but this still doesn't give enough adjustment). This isn't a problem initially but it soon makes the levers so far out that I have ungrip the bars to fetch them back in preperation to use them! I'm glad I only have them on XC bike.

I've read somewhere on the internet (I'll supply a linky when I find it again) that as the pad wears down you can use a thinner bleed block to keep on top of the reach issues.

What you'll need:

• a selection of pennies
• some glue
• vernier caliper

Glue it together

Then all you need to do is to select the combination of coins you require to make up your disired thickness (I went with 9.80mm) as measured by your vernier guage, glue them together (I used some E6000 I had lying about after I was gluing my shoe back togetther). Once the glue has set you are away.

In use

I found in use that the spacer did fall out a couple of times, not during critical points of the bleed process though. Other than this it performed admirably and despite it's slight drawback it is great to be able to slightly tweak the exact bleed you are getting out of your calipers - I found that 9.80mm was spot on for my current amount of pad wear.

Creating hyperlinks in Excel that are relative & actually work

If you've ever tried to create a hyperlinks in excel you'll know what a pain they are.

The hyperlink tool on the Insert ribbon is quite useful, but it is a pain to create a lot of them. The big problem however is that they are not relative - if you add more columns or rows suddenly your hyperlink is pointing to the wrong place.

You could use the custom hyperlink formula, this will let you create a lot of entries more quickly, however it is still not relative and has more issues.

=HYPERLINK("[Budget]June!E56", E56)

This is because the address is entered as text so is not a cell address. Also we now have the problem that the spreadsheet name is now also hardcoded so if you change the spreadsheet name you've now scuppered youself.

Pretty crappy really seeing as this is Micro$ofts own tutorial! So in the next evolution of its usuage would be to add the filename in dynamically using the following function:

=CELL("filename",C9)

However to be able to use it in the formula we need to extract just the filename so we have to use:

=LEFT(CELL("filename",C10),FIND("]",CELL("filename",C10)))

This is also very clumsy as we are still harcoded - not relative - and trying to dig ourselves out of the predicament we are going to make it even more complicated.

The solution

What we need is a subtle tweak, this is the answer:

=HYPERLINK("#"&CELL("address",M235),"Actuals Summary")

Nice, simple and hitting all the buttons.

Raspberry Pi - record uptime

Well, i had pre-written a post celebrating a whole year of uptime for my RaspberryPi - guess I shouldn't have jinxed it! On Friday I came home to find the Pi unresponsive to web access, putty & vnc.

There was noting for it and I had to suck up the loss and kill the power, oh well I shouldn't be to disappointed, 345 days is a respectable acheivement. Looking on the brightside, it gave me a window to swap to a larger SD card to allow me to do some software updates.

Well done RaspberryPi!

Ali goes viral

Well almost.... After Ali's niece has been addicted to watching unwrapping videos on YouTube, she decided to blog her birthday presents opening. Turns out she's quite a good blogger, admittedly I'all have to up my camera skills but you can judge for yourself on Youtube...

Bluetooth Selfie Remote button mapping (AB Shutter 3)

So I've got myself a one of those bluetooth selfie buttons, but before you start slating me for being one of those plonkers - I am using it for other remote control purposes...

The off road satnav loaded with OS Maps which I use for navigation on my MTB sits in a waterproof case attached to my stem. The whole setup is great, I don't have to faff about with maps and it lets me spend the maximum amount of time riding as fast as I can. Plus it is much cheaper than a Garmin 800/810 and I can get far better real time stats during my ride.

The only drawback is that the screen drains the battery too much to have it on all the time, no problem really as you can just flick it on when you need to see the map. But phones these days have their power buttons on the top edge of the phone and are quite tricky to press, so I've been using old & cheap smartphones with hardware home/back/menu buttons on the front like the ZTE Blade and previously T-Mobile Pulse. Sadly reasonably priced  phones with these buttons that don't have a rubbish screen resolution are no longer available and I'm looking for an alternative solution.

So I've been looking for some sort of remote I can use. I initially experimented with a cheap bluetooth headset I was hoping to remap a button on it to wake the phone only however it appears the cheap ones only allow you to redial the last number you've dialled and won't accept remapping.

Given the latest craze of the selfie button I thought I'd give it a bash, having found no details of how they exactly work from the internet I took the plunge and bought one from ebay for a very cheap £1.99 and thought I'd post my findings.

How it works

It work quite simply really, the Android button simulates a Return and the iOS/Camera 360 button sends a Volume Up command. Additionally the Android button has the ability to handily wake the phone up.

Key mapping:

Android button  ENTER      Keycode 28
iOS button      VOLUME_UP  Keycode 115

Key layout:

hid-keyboard

Hope this will be of use, I'll post up my findings in relation to controlling Oruxmaps with it at some point.

Best place to sledge in Manchester

The only real source of places to sledge I've found is this article from the Manchester Evening News. There's a couple of good spots there but there appears to be a glaring omission - there's nothing in the Peak District, but you are probably not going to find a great hill without a bit of local knowledge!

Lyme Park comes close to it obviously but what we need to consider is somewhere high enough up that if there's even a sniff of snow in the forecast anywhere in the North West you are guaranteed to get some good powder.

Well, Tegg's Nose Country Park fits the bill. Location is perfect, just off Snake Pass - this road is always closed when there is even a hint of snow - that's what we are after: snow! Although not too far up it that we are trying to drive through the snow drifts.

Facilities are also good: cafe, toilets & a car park (£1.70 for three hours). For directions from the car park just follow the other people or use the Strava map below.

When you get to the slope you'll be presented with two fields seperated by a dry stone wall. You can string a good 200m run at an average gradient of 15% if you can combine them both via a gap in the wall, nice. When we were there there had been a number of jumps built for added fun, the car park was full but there's plenty of room for everyone.

B&M Travel Mug review

Decathlon / B'Twin tyre lever review

Perfect Corn on the Cob

Excel function to list worksheet names

Ever wanted to list all the tabs in a workbook or reference a specific sheet by its placing, look no further.....

This funky little custom VBA function can be used simply as a contents sheet, This is a particularily useful tool tool when used with the indirect function, it will allow you to soft-link to any cell within your worksheet.

There are a few things you should note though, it sometimes won't update after a sheet move/copy/delete/rename so a good old calc all - Ctrl-Alt-F9 won't go amiss.

Function WorksheetName(SheetNo As Integer)
	    'Function to return worksheet name for its given number order
	    'Copyright Marty Childs 2008
	    'Usage =WorksheetName(1) will return "Sheet1" in a blank workbook
	   
	    WorksheetName = ThisWorkbook.Sheets(SheetNo).Name
End Function

Follow these instructions to create the macro for Excel 2003 or 2007. Also the function needs to be saved into workbook itself so it is available to everyone and for 2007 users and above dont forget to save the file as a macro-enabled workbook.

Explode - the must have excel add in

Excel's built in formula auditing tools is and always has been rubbish, sure you may have got used to how they work and feel quite competent/happy with it - but the Explode excel addin will blow your mind!

If you work with excel a lot and you don't use the add in Explode you're in one of two camps:

• not a power user, or
• both amazed at how you've gone without it thus far in life; and super annoyed that it's taken this long to find it!

Explode is pretty old now (but as useful as ever) and the great peops over at XL-Logic.com who authored it have long since taken their website down, but you can still access it from their download page from here, (my mirror, another mirror).

Update 9-Jun-2016:  see my blog post, don't download the addin from google's first hit of Clubfinance Project Finance as is a really old version.

To install:
Extract explode.xla from the zip to any folder, then this (for Excel 2007) go to Excel Options, Add-Ins, then 'Manage: Excel Add-Ins', 'Go...'. Broswe to the file you just extracted and you are away.

To use:
Just right click a cell containing a formula and select 'Explode...' and you are away. You can then trace both the pecedents and dependants with ease.

Stay tuned for some more excel goodness, I've got a blog post about another super usefull util, Name Manager, coming up.

Lake Vyrnwy Kingfisher Spotting!

Spent a second fantastic weekend camping in Shropshire in mid June, having previously visited in 2011.

Marty opted for a day mountain biking around the Long Mynd area which I did enjoy thanks to the spectacular views of the Shropshire Hills.   But the highlight of my weekend was a trip to RSPB Lake Vyrnwy on the Sunday.  We packed up the tent and headed off into Wales with plans to cycle around the lake.  On arrival we headed to the main Coed Y Capel hide by the car park.  I was disappointed to see that there were no bird feeders out, previously these have given great views of small birds, however we got chatting to the RSPB volunteers who informed us that the absence of feeders was an attempt to protect the bird population from a devastating infection, trichomoniasis.

We then set off around the lake towards the centenary and lakeside hides.  I wasn't hoping to see much when we arrived at the centenary hide, on our previous visit all the action had been at Coed Y Capel.  We had just made our way into the hide when out of the corner of my eye I saw a flash of blue streak past! I instantly knew I had seen a kingfisher, and was so excited as this was my first ever sighting!  My luck continued when the bird flew back into sight and perched on top of a post amid the reeds, giving us perfect views, but sadly did not stay long enough for a photo opportunity!

After all the excitement we decided to have a pit stop and opted for the Lake View tea room, a short climb up from the main road around the lake.  The tea room is well located, with excellent views over the lake from the front terrace.  We had a pot of tea and Marty had a scone, but sadly for me there were no gluten free cakes!  However the fantastic bird spotting continued from our table with sightings of siskins, housemartins, chaffinches, jays, a mistle thrush, pheasant and a great spotted woodpecker!

Fully fuelled we set off out our "flying lap" - an attempt to get the all important Strava segment!  Considering we were on our mountain bikes we flew around, clearly boosted by a fantastic few days!

Roasted Monkey Nuts review

Make a cell look like a button in Excel

There a sometimes in excel where you want to create a button but you don't want to introduce VB into your workbook (there are many reasons not to) - heres how.

I'm a great advocate of not using VBA / macros when you don't have to - they are not widely understood, easy to break when you alter the workbook and introduce a plethora of security issues. And with the newer versions of excel they can prove troublsome with users not enabling macros and saving the workbook in a non macro-enabled format (i.e. .xlsm).

Here is how to:

• first pick the main coulour of the button and fill the cell. To make your first attempt simply select a colour in the middle of a shade range;
• the open up the 'Format Cells' dialog, the easiest way; to do this is right-clicking the cell where you will see it in the context menu;
• switch to the 'Border' tab;
• in the Color dropdown box pick the shade lighter than your main fill colour;
• select the middle weight solid line from the style selection;
• in the border box click the top line and the left line;
• repeat the previous three steps with a darker shade and the bottom and left lines;
• click OK to see your creation;

You may want to go back at this point to tweak your colours and/or line thickness to get the best results.

This trick is best used with a hyperlink to open another document/webpage; link to a specified  place within the workbook or even to lanch a script or other program. See my blog post about how to properly set up a hyperlink.

How to check the calibration of your kitchen scales

Want to quickly and easily check if your scales are accurate it's easy, read on....

Across the whole of the cycling world everybody is obsessed with weight, it is pretty rediculous really that people stress about grams and spend a disproportionate of money trying to save it on the things.

There is even a certain group of people who have it so bad they have a special name: weight weenies, it is not a slur on their character really as these people tend to have their head screwed on. Whilst they definately have a problem at least they go about it scientifically, they calculate the cost per gram saved then go for the cheapest gains first.

Weight Weenies also acknowledge that the first thing you should look is yourself and the stuff you carry - shaving 1kg from your bike is probably going to cost you £1,000; loosing 1kg from your belly will probably save you £100 in beer!

I'm not a weight weenie but if I need to replace a part on my bike I do take it into consideration, the other half used to rip me for it. She's getting over it now since I keep pointing to her that she is a scientist and her whole profession is based around measurement.

There has always been some talk in the various circles about using an accurate calibrated scale but I' not dropping £30 on a fancy Park Tools number when I can get a funky colour cheap one from Argos. From my testing this thing is spot on - check out how I know below.

Anyways getting to the point of the blog post - how to test its calibration - easy just use coins. The Royal Mint website lists their weight, simply zero your scales and bung on some coins, if they weigh what they are supposed to you are good to go! If you want to get super technical weigh your item then get enough coins so that you have approx the same weight then check the calibration for that amount, simples.

Heres a list of coin weights per the Royal Mint:

Top tip for accurate weighing

The geeks amoung you will note that my scales are over-reading in the picture by 1g this is because the surface of the desk I was using to take the photo isn't even - it has a very slight bow in it. This can have a dramatic effect on accuracy - you should always choose the most level and flattest surface you can and make sure to clean it and the feet of your scales.

You should also note that you can get different readings depending on where on the scales you place the object so you should always try to use the centre of the pad.

Continental GP4000S - correct install orientation/rotation direction

Want to know in which direction to install yor new Continental GP4000S tyres? read on....

Not that the sipings have any effect on performace (I did read an article somewhere in which the representative from Continental said that customers expect them so thats why they are on there), but it is nice to know you've installed them in the correct orientation.

I was in a rush installing my first tyre as I had a split in the carcass of my Giant P-R3 tyre and in artificial light I couldn't find any markings or rotation travel at all so I made a judgement call and, as it turns out, put them on the wrong way.

When it came to installing the other tyre the sun was shining brightly and I could just make it out (and I mean very just) so I thought I'd share it with the world and post a picture up. It may seem unclear in the picture - it is worse than this in real life! I've put some post-processing on the image to bring the markings up better. You can't actually make out the point of the arrow but the end of the shaft is just about visible. If you look very hard you can just determine the shoulders of the arrows as well.

Good riding.

How to shift the time in your GPX file

Here are the intructions to shift the time of you GPX file, especially useful if you have just bought an Aldi GPS watch:

Step one

Download and install GPS Babel.

Step two

In the folder where you store your gpx files create a new text document and rename it

GPX minus 1hr.bat

making sure you change the whole file name including the file extension

Step three

Edit the file by opening it in notepad

Step four

Paste this code in and save:

SET file=%1.old
IF NOT EXIST %file% copy %1 %1.old
"C:\\Program Files (x86)\\GPSBabel\\gpsbabel.exe" -t -r -w -i gpx -f %file% -x track,move=-1h -o gpx -F %1
pause

This is for Windows Vista/7 installations

Step five

Drag and drop the file you want to change the time on. It will create a backup of your file and shift the time for you.

Alternatively you can download the file here.

Aldi GPS watch review

Musing of the day 23-June-2014

Well I'd never thought I'd say it but today I was greatly tickled by an anagram - surely a sign of getting older!

The amusement came as I was looking through the expenses of an operating unit at work, there was a bunch of costs to repair some damages to hire cars, each narrative had the reg. number of the car in question - one of them being:

FX13 UED

lol!

If you didn't get it straight away:

FIXE3D U

jumped into my head even before I'd seen it was a repair bill.

Giant P-R3 Tyre Review

Wilko Performance Lubricant cycle oil review

Update 24-Jul-2014: I've been using this for a couple of months now and apart from the thin-ness all I can report is the smell - its a bit like engine oil, but you can't really complain at that.

Quick & Tasty Coleslaw

My first run ever!

After a couple of years of pestering (or as Ali would have it encouragement) I've taken up running. Having never been on a run voluntarily in my life (not sure you could class my activities during my school years as 'running') I decided to ease myself in steadily.

It's when I was perusing the running asile at Decathlon when the pace started to pick up.

I'm a big fan of Decathlon - they are firmly in the low margin/high volume market but their goods are quality. Another bonus is that their merchandise is all on the shelves so I was able to try as many different varieties and sizes as Iliked without the supervision of an assistant and the waiting time to get your size.

Starting at the bottom of the price range the first couple I tried on were not a good fit and were definately for pronators, moving up the range I landed on these fellas: adidas Furano Quest, perfect in a size 44. They duly made it to my Amazon wishlist, and ended up on my present pile come my birthday.

So fast forward a few weeks later and I've been thinking about going for a run for a few days, the weather was nice so I thought I'd bash out a couple of laps of the park. Not that bad really, 1.8km and 6:30/km with not that much effort. Suffice to say - it may take a while to build up my run specific muscles.

Here's my strava upload, not much to see really as its all in my privacy zone. All I have to work out now is how many kms I have to run in my new shoes to have got enough use out of them before I can retire!

Chorizo and Pepper Pasta

Chilterns Red Kite road ride

Following a visit to Windsor castle we stopped off for a road ride in the Chilterns. We had read that you can see red kites in the area so I was particularly excited to combine a bicycle ride with wildlife watching!

I had hoped to see at least one red kite and I was not disappointed! We spied many red kites on the drive to Watlington and upon arrival at the car park 4 were circling overhead! A great opportunity to get the binoculars out and get a closer look at these beautiful birds.

I eventually managed to tear myself away and we begun the ride, straight away we were onto the first of five climbs of the day.  The first hill of the day is always a struggle and today was no exception but once we reached Christmas Common we began the main loop and the countryside opened up and we could really appreciate the views.

We saw red kites all around the route, it was amazing to see such a healthy population of a bird whose numbers had been reduced to a handful of pairs at the start of the 20th century. A real conservation success story!

Around the halfway point we came across a nice pub, The Prince Albert, and had a pit stop. Mugs of tea whilst we enjoyed watching more red kites circling over nearby fields.

This was a wonderful ride and I hope that we will be able to further explore the Chilterns soon!

It's probably best to leave your super fast race tyres at home for this - some of the roads are a bit agricultural so you are best off using a more robust piece item.

There are plenty of different routes to choose from, if you want to plan your own route here is a compilation of a load of sportive routes I found:

 See all routes

Gower Peninsula Circular road ride

This was a landmark ride in a way for me as usually when we go camping I leap upon the opportunity to take the MTB somewhere new, but somehow I persuaded myself into taking the road bikes instead.

Maybe it was the lack of some really decent trails or the fact as we'd be in my sister's tent there'd be enough room to bring my retro roadie indoors to protect it from rust should the heavens open.

Well it turns out I shouldn't have worried, the weather was great and we headed out for the first ride of the long weekend on Saturday morning. Unfortunately for me as we approached Llanmadoc to soak up the views of Broughton Bay the missing-link from my chain decided to go missing!

As a matter of strict protocol I always have a pretty comprehensive tool kit upon my person at all times when MTBing, however this does not extend when on my road bike as I'm never really going to be very far from civilisation should the worst happen. So after some time of futile search for it I headed to a shop I'd passed and scrounged a plastic bag to put my chain in to continue back.

It wasn't too much of a bind I was in as it was only 15km into the ride from our start point at Horton and I'd sent Alison on ahead to go and fetch the car to meet me on the way back. I'm so grateful that I use my MTB shoes and SPDs on the road bike as the scoot home was quite fun.

As I said we had scheduled two rides for the weekend so we dicided to give it another crack on the Monday. So glad we did, the route is a cracker - it covers all the bases of terrain - from the beach front from Swansea to the mumbles, cliffs & coasts, country lanes and tops of moors. For good measure you even take in some traffic free tree lined disused railways and the Marshes of Llanrhidian Sands,

philfish.co.uk is launched - Phil Cam is live!

The momentous day for Phil has been reached, his tank hosted server has launched!

His Samsung Galaxy S2 server is live and pumping out his very own website.

Phil is said to be delighted with his web 2.0 upgrade  and is looking forward share the images of the tank to the whole world. He says he's not finished yet and is working in the background to bring you some enhancements but in the meantime you can enjoy the wonders of webcam and Flappy Phil game.

He also notes that he has a number of vacanies available to become a friend of Phil and is currently accepting applications.

Set up a Wireless IP Webcam for free

This a hack for repurposing an old android handset as an ip webcam - all you need is your old unused android smartphone and the free app IP Webcam.

Heres a rundown own the steps you need to go through, I'll take you through more of the detail below.

Steps

1. Find the old android device you'll be using and connect it to your wireless network;
2. Download the free IP Webcam app from the Play store;
3. Fire up the app, you'll see a few configuration options but you can skip these for now and scroll to the bottom and hit start server;
4. You phone's camera should start up and on the screen you'll see the URL that you can view from your browser;
5. Go to the browser on a computer on the same newtork and type in the address (probably something like 192.168.XX.XX:8080);
6. Enjoy!

Further steps

If you want to be able to view your webcam from the internet outside your home network, there are a couple of other steps you'll need to take:

1. Configure a static IP address for your phone (either through the router config or on the phone itself);
2. Configure your router to route (by default port 8080) traffic from the internet to your phone;
3. Set up static access to your home network if your broadband link has a dynamically assigned IP address.

Port 80 android limitation

Theres a limitation with the android OS we have to get around round first though. All ports below 100 are restricted so by default you'll have to serve your site through port 8080. To get around this you'll either have to

• root your device and install iptables (I've not found a way to do this yet);
• use some router configuration to pass port 80 requests onto port 8080;
• or you can use another server as a proxy.

Router configuration

Most routers don't have much in the way of configuration options so unless you are using dd-wrt or similar, its unlikely that you'll be able to redirect traffic to port 8080. I have a guide here on how to set-up a domain name with your router, but unfortunately you'll probably be stuck with an address to access you webcam like:

yoursite.com:8080

unless you can find a service to do this for you or you have access to another server to do this.

Apache Proxy

If you have access to an apache server you can easily set up a ProxyPass to forward traffic to your webcam. You can capture the requests via a subdomain and then forward them on to your webcams network IP address (e.g. 192.168.0.23):

<VirtualHost *:80>
    ProxyPass / http://Internal-IP:8080/ 
    ProxyPassReverse / http://Internal-IP:8080/ 

    ServerName webcam.yoursite.com
</VirtualHost>

Once this has all been setup you will be seamlessly served your webcam images through your sub-domain URL.

Hayfield figure of eight route - MTB ride

This route is a goto classic of mine, the north loop ridden clockwise, the climbing is pretty easy but there are some cracking descents.

The south section is ridden anti-clockwise - it is not so imperveous to being wet and sloppy in the winter months, but give it a bit of frost and it'll be good to go.

The highlight of the ride is the decent from Lantern Pike, its a cracker.

It finishes off with the 'legendary' campsite descent but to be honest its pretty rubbish as descents in the Peaks go. The ride uses the very pleasant Sett Valley Trail to link the sections which enables you to ride either loop first.

There's a little detour you can make to extend the south loop - carry on past Shedyard Fm towards Cloughhead then take the 'next left' back up towards New Allotments.

Setting up your own domain name with a dynamic IP address on Sky Broadband

Most broadband connections will nowdays dynamically assign you with an IP address. This means that every few days or every time you restart your router you will be assigned a new IP address. Consequently if you want to host a website using your domain name then you are going to encounter a problem.

What you need to do is get a static IP address (typically an ISP will charge for this if they even allow it), or find some way of updating your name server with every change of IP address. Your typical domain name seller doesn't want to offer this service as they want to sell you a hosting package so you will have to find another way around.

Dynamic IP woes

Most routers will have some sort of option to update a dynamic DNS service with your ip address and assign you a CNAME/URL that you can use, its not very pretty but it is one step closer closer to the goal.

For Sky Broadband customers the only configuration option here is to use DynDNS, unfortunately this service is no longer free and at $30 per year is too much for this tight-wad. [Note: they do offer a free option but the hostname expires every 30days so its of no real use]

Get a static hostname

One free dynamic DNS service is changeip.com, they try to hide the free option but it is there and it does work. So after you have signed up, select your sub-domain name and head over to the DNS Manager page select you new domain and manually update it with your IP address you are away. Just bang in your new URL into a web browser and you are one step closer:

http://yournewsubdomain.dynamic-dns.net

they even helpfully provide a shell script you can set as a cron job to automate the updating of your IP address, it works a treat and helpfully keeps a log of your IP address as it changes also.

Pointing your domain name

Now the final step is to change to nameserver entry on your own domain name, your service provider should have the option in your account settings somewhere to change your DNS entries. You have to take care here as you could screw things up. I started out by testing the waters by setting up a subdomain of childs.be first to test that out.

What you need to do is change your DNS record for your selected [sub]domain to CNAME and then set the value as your new yournewsubdomain.dynamic-dns.net. Thats it, you are away, your domain name URL will now always be pointed at your dynamic ip.

I've been using this method for several months now and I rate the service as excellent.

Another sneaky way

If you've googled the subject enough you may have discovered that while DynDNS isn't free, D-Link DNS is. It is only for D-Link router users only and once you have created an account you can log into both D-Link DNS and DynDNS with the same details.

This makes it very easy as setting up your router to automatically update your dynamic IP in this way and is a doddle!

I must stress though, using this service without a D-Link router is against its TOS, but it is reported that does work in this way.

Edit 24-July-2014: Looks like Dyn.com are making D-Link users verfiy their ownership to continue use of their service, see here, still you could always buy a used on from ebay to keep you going but that might only get you another 6 months before you have to pay.

Edit 22-Oct-2014: Dyn.com have now deleted unverified accounts.

Moel Famau Double - MTB route

The Clwydian Mountain range is and Area of Outstanding natural beauty. Once you gain a bit of height anywhere the views are stunning and on a clear day you can see over to Merseyside and Snowdon.

I seem to ride here a lot in winter as most of the going is on well engineered paths and fireroad so is enjoyable even through the roughest of winters.

This route climbs to the top of Moel Famau on the first loop, the climb is do-able, although a little steep in places but the path I take is the longest possible lessen the slope. The folly which caps the hill makes for a great 360 degree view point and theres often a smattering snow in winter which makes it all the more picturesque.

The second lap takes follows the Coed Moel Famau Loop - on its own a great blue route for the beginner, I originally saw it on Ride the Clywds website which is no longer in existance, but you can see the route on mbwales.com.

Ride details

Distance 22.3km

Elevation gain 730m

Start: Coed Moel Famau car park | £2

Toilet: at bus stop by entrance

Coffee stop: there's usually a van in the car park serving brews and food

As another bonus, the car park barrier seems to be always broken saving you the £1 fee!

Edit 8-Feb-2015: The barrier has been fixed for a while now and they've put the charge up to £2.

You can view a webcam here to check for snow!

Hosting your own webserver on port 80 with Sky broadband

One of the first things I needed to check before ordering my Raspberry Pi was if it was technically feasable to host my own webserver from my Sky Broadband. In the past its been known by ISPs to block certain ports which makes it difficult and usually adds extra cost.

One of the ways round the issue of your desired ports are blocked would be to use some sort of service which relays incoming port 80 traffic to another port to your server. Its messy and is probably going to cost for this service  and given one of the reasons why I've got a RPi was to have practically free hosting.

It seems there is no information on the internet as to whether Sky blocks port 80 for their broadband users. I'll cut to the chase if you can be bothered to read the rest of the article to say that:

Now we have established this, its just a case of setting up your router to forward internet traffic to your Pi webserver. There's a helpful page on Sky's support website explaining how to do this:

Sky Hub : How to Configure Port Forwarding

Well thats the easy part, next you've got to get around that whole dynamic DNS thing, but luckily for you I've got a tutorial for that:

Setting up your own domain name with a dynamic IP address on Sky Broadband

Alison and Martin's Carbonara

Peaks road quicky (aka the Sarah Storey ride)

For some reason given that I ride offroad way more than I do my road bike, my first ride post is fully on-road...

This route developed from the want of a ride encompassing some great climbs of the peaks without the boredom of the 30km journey there and 30km back! So the hunt was on for a place to park up - as usual for me it needed to be preferably free and have a toilet for the inevitable pit-stop after the large brew downed in the on the way there!

The search was concluded with the discovery that the lovely village of Prestbury I'd cycled through many times has both free parking and a toilet so capacious that you can take your bike in with you! Its also only 20-30mins drive from my house.

Using Prestbury as a base meant the first leg of the ride was a no-brainer, up the classics Brickworks climb. Next I'd always fancied riding through Errwood reservoirs and up the singletrack valley climb to the Cat & Fiddle ever since I'd had a day out walking around there. What finally sealed the deal was that Alison forwarded me a tweet from Dame Sarah Storey of her favourite ride which included most of my prospective roads.

From there is a another easy choice to make - the descent down the Cat & Fiddle to Macclesfield. All in, a great ride especially when the sun is shining, the views and the countryside are excellent. I've included another variant, it swaps Long Hill for Windgather Rocks and descends down the A54.

There's many other options too which can be considered - past Lamaload Reservoir, through Macc Forest past Trentabank Reservoir or around Macc Forest on cycle route 70 going through Wildboarclough. As for cafe stops, the Cat & Fiddle in does a reasonably priced mug of tea with a nice selection of snacks; another bonus is that you can usually pick up a free motorcycle newspaper to stuff inside your jersey to take the edge off the wind chill on the way down,

Route 1 » Route 2 » Route 3 »

New childs.be website launched

The new website has now gone live. With the acquisition of my Raspberry Pi comes a whole host of possibilities.

My Pi is currently running as the server for this website. Unfortunately the Pi is not powerful engough to run Wordpress or Joomla (who'd of thought they were processor intensive) - it does run, but a four to six second delay in spitting out a page is way too long.

What I've gone for on my first crack is a flat-file CMS with blogging functionality as a plugin running stock Bootstrap and Bootstrap-theme CSS. I'm pretty pleased with the first real attempt. It needed some mods out of the box but I'm happy with it for the moment.

Here's a rundown of the software specs it's rocking:

I'm hoping to post some more articles on the trickier things I've come across when setting up the RPi.

As a bonus in the mean time, I've reposted some posts from Blogger from a long while ago when I first though I'd start a blog but quickly ran out of stuff to blog about!

You can check out the Raspberry Pi's specs here

the Raspberry Pi is alive

The new server has now gone live, the Rasberry Pi is sitting quietly in the corner of my living room serving up my website to the internet.

I know I've backdated this post, but it is to mark the moment in history - I'll give the Pi one final good luck reboot and it is ready to go with the aim of surviving a whole year of uptime.

For many years I've been wanting more functionality from my paid hosting company but being too tight to fork out the hundreds of pounds a year it would cost as I wasn't quite sure if I'd actually get my monies worth out of it.

I've been keeping an eye on the Raspberry Pi project for a few years now, I even had an invite to buy one from the very early batches however I really wasn't sure I'd ever put it to good use. As time has gone on and my skills have improved as I dabble ever more with WAMP, I decided to take the plunge.

At only £30 with a bundled case from ebay it couldn't have been cheaper. A couple of days and a few internet tutorials later and I'm in business.

You can check out the Raspberry Pi's technical specs here

It is said that the Pi is great for kids and newbs, however you've got to take that with a pich of salt. Complete beginners might have a tricky time with the various processes and while it is true you can have its running as a webserver in no time, actually implementing it running your own site is a fair bit trickier.

What you get with the Pi initially is very minimal - just a circuit board! So you will have to invest or scrounge a couple of things to get you started:

Excel - Add comment directly into formulas

Ever known anybody who hardcodes numbers into formulas? Does it drives you mad because they leave no explanation of why? Well this may be the function you should tell them about.

You can use the N() function to add a comment inline with a formula. What it actually is mean't for is to try to convert a string into a number, but if you just put words in there the output of the formula is zero.

Although come to think of it, you've probably already told this person to explain their actions and they haven't listened to you so they probably won't now! Best to keep it to yourself for explaining stuff you don't want to use a comment for.

See example below:

=A1+A2*0.85+N("your comment")

Excel - Calculating Pi

If you are finding the inbuilt excel function for =Pi() is just a bit too dull, there a handy function to calculate Pi on the fly.

You may want to use it sparingly though, too many times in one spreadsheet or to many decimal places and you'll grind to a halt as it is a bit processor hungry!:

Function myPi(places)
'Places is the number of decimal place accuracy you want
x = 1
a = 4
b = -1
Do While Round(c, places) <> Round(a, places)
c = a
a = a + (4 / (x * 2 + 1)) * b
b = b * -1
x = x + 1
Loop
myPi = Round(a, places)
End Function

Excel - Unhide all sheets

This little sub quickly unhides all hidden sheets (including xlVeryHidden). I use it frequently as people tend to hide worksheets which are no longer needed rather than just delete them, also especially useful when trying to dissect other peoples creations.

It also has some sneaky uses, I regularily come across people who like to protect the content of their macros and spreadsheet from prying eyes. Usually they make the fatal mistake of making the visibility of tabs they don't want people to see as xlSheetVeryHidden, then password protect the macros. What a mistake indeed, the function below gets straight around this:

Sub unhide_all_sheets()
Dim wksht As Worksheet
For Each wksht In ActiveWorkbook.Worksheets
Sheets(wksht.Name).Visible = True
Next wksht
End Sub

I'm not even using an exploit for this, its just plain old user error - whist password protecting the macros blocks UI access to the sheet properties in Visual Basic, it does not lock access to the field as a command.

To properly protect the required tabs the user must Protect Workbook. Not that even this will stop someone who really wants to gain access ;-)