Digital CanalOmeter mark II beta test version

PLEASE NOTE: THIS POST HAS BEEN SUPERSEDED.
I'm leaving it here just for the record.
A NEW SIMPLER VERSION HAS BEEN CREATED.

See this post for details


Thanks to the interest in my digital CanalOmeter spreadsheets and help from CanalPlan, we move on to the next phase for someone to test.  If you don’t know what I’m talking about, then read my earlier post  CanalOmeters for Tablets.
I’ve made very minor tweaks to the previous version and have been trying to find the simplest, most fool proof method of extracting the data from CanalPlan and pasting it into a spreadsheet.  If you fancy a go, please see if you can follow these instructions and report back any problems.  It’s really ever so easy, but like all  these things you do have to get it just right, so take care.
1. The first thing is to download a copy of my Digital CanalOmeter Template spreadsheet.  If I were you I would save two copies, one with a backup name in case you inadvertently mess up the original.  I have attempted to saved it as an Excel 93 onwards compatible format so most other versions ought to be able to read it, but holding it in Google docs does funny things so I’m not very sure what format you get it in.
  On my iPad I have been using Sheet²HD which cost me the princely sum of £3.99.  Some free ones might work just as well, I’ll leave it up to you.
2. Load up Canalplan in your browser and go to the Options page.  You need to click the All places button circled in red here, then the Apply button.  No other option settings are required.

3. Now return to CanalPlan’s home page and follow the Plan a journey link to enter your chosen route then click on Calculate route.  When the route shows up, scroll to the foot of the page and click on the button Export this route.
4. Here’s what you should see next

Should you see something different, make sure the Select output format box says CanalPlanAC CSV.  That should put you straight.
5. Now you have to use those arrow buttons I have circled to clear out the Used in output box and refill it by selecting items from the Available columns box so you end up with this

It is important that the right hand box ends up with the four items in the right order, top to bottom as shown.  (At some time in the future CanalPlan have offered to include a ready made output format for CanalOmeters, but of course they are busy with many other tweaks so for now we’ll do it this way.) When you’ve done that, click on Export this route.  You should then see a message Your export file has been generated, click here to open or download it.  Well what are you waiting for?  Click it and depending on what kind of computer/tablet you are using you should notice that a file called something like canalplanAC blah blah .csv is now in your possession.
6. Nearly there now.  Assuming you have some sort of compatible spreadsheet (if you haven’t, you shouldn’t have started all this) you ought to be able to click on the downloaded file and it should display itself in your spreadsheet program /app.  What you have to do now is to carefully Select and Copy (in one big piece) the table of data you see.
7. Last step.  Hang in there.
Into your spreadsheet app, load my CanalOmeter template.

click on cell E:11 and then do a Paste.  Hey presto, it’s done and you are now the proud owner of a digital CanalOmeter for your chosen route.  Before you start messing about with it, quickly save it under a route name e.g Braunston to Brum so it leaves empty template file is till available for future use.  It seems that the template downloads in read only form, for some reason I know not, so you may first need to change it to an editable file.  How you do this will depend on what operating system and software you are using but it should be easy.
BIG WARNING NOTE

• Because of the cheapo (well free with Windows 7 actually)  Excel version I have used to develop this, I have been unable to lock or protect any of the formula cells, so you could over write them and destroy the functionality.  That’s why I told you to make a backup copy.  DO NOT type anything in the green boxes.  The only place you type anything in is the pink column, where you type a F (or f) for the From point and a T (or t) for the To point.  The green boxes will fill automatically.

Once you have your Digital CanalOmeter complete, you can enter Fs and Ts to your heart’s content to find out the times, distances and locks between any two points.  Just remember to delete the previous Fs and Ts before you enter new ones.
Once you have mastered the above procedure you can make spreadsheets for all your favourite journeys.  They only take a few minutes each to do.
Well there it is.  I fully expect I have missed out a vital step or made a mistake somewhere so please regard this as a beta test version.  If anyone out there is kind enough to give it a go, please let me know if it worked and pass on any problems or suggestions.

CanalOmeters for Tablets

A small number of enlightened people have taken up my CanalOmeters in the past and been kind enough to say they were useful.  Certainly I have enjoyed making and using them.  For the uninitiated, they are a simple calculating device for working out long it might take to cruise between two points on a canal journey. They look like this

Follow my link on the right hand side of the blog page to find out more.
Now, having got a nice low tech cheap as chips solution that works, why on earth would I bother to spend time making a digital version?  After all didn’t the data come from CanalPlan in a digital table format?  Well, . . .
a) The cardboard ones are great and I will continue to use the ones I have and maybe make a few more, but the formatting of each one in Excel is something of a pain.  When I am in practice I can do one in about half an hour, but next time I forget and it takes me ages.  Then I get a new computer with a new version of excel and it all goes haywire.  Grrrrr.
b) the cardboard ones are limited in how many place names I can squeeze in
c) If you have a tablet computer with a good internet connection you could use (the rather wonderful) CanalPlan on the move, but bandwidth along the canal is unreliable and using CanalPlan can be fiddly on a tablet.
I did make one for Smartphones a while back, but in truth the small screen size made it a bit too fiddly to use in anger.  So when I got an iPad mini, I thought I’d have another go, and what I have come up with looks like this.

The spreadsheet contains a list of all the places en route (bar individual locks within flights).  All the user has to do, is put a T (for To) in the left hand common next to the name of the place they are going to, and an F (for From) against the start place.  The T and F can be in either order. The Green box at the top then automatically shows both place names and the Locks, Miles and Hours/Min cruising time.  Simples.  Of course if you get there quicker or slower, that would be because your speed is not the same as we use for calculation purposes, which is generally 3mph.
I’ve been using an Excel compatible iPad App called Sheet²HD, which was either free or dead cheap, I can’t remember which, but I think any half decent Excel compatible sheet would do it, either on iPad or Android.
To use one you need hardly any spreadsheet expertise, just how to open it and type the T and F in at the right spot. ( and to delete them ready for the next go). To make one you need a little bit of spreadsheet knowhow, but not a lot.  The data can be cut from CanalPlan and pasted in quite easily once you get the hang of it.  The calculations are of course dead simple, subtracting one number from another.  The only bit that you have to learn is how to deal with the Hh:Mm time format.
So to recap the benefits over the analogue cardboard version
a) Contains all places en route
b) Quick and easy to develop your own for your own routes.
and over CanalPlan, just that you don’t need to be on line, and it’s quicker to use once built.
I suspect that like me, you might think the cardboard ones are still best if you can be bothered to make them.  I will be using both.
Now, would anyone like to help me by downloading a copy and seeing if you can make it work on your device.  This could either be a PC or an iPad or an Android device, just so long as you have an Excel compatible spreadsheet programme or App.  I have been struggling all morning to work out how to link them in a downloadable form to this blog page and I’m interested to see if it downloads successfully.
All you have to do is click this link and it ought to download.  You then need to start it up in your spreadsheet .  If you do, please send in a comment with any feedback.

PS if this one works and anybody wants more, I have several other routes done already.