corsasport.co.uk
 

Corsa Sport » Message Board » Off Day » Geek Day » Excel Delivery Diary?


New Topic

New Poll
  <<  1    2  >> Subscribe | Add to Favourites

You are not logged in and may not post or reply to messages. Please log in or create a new account or mail us about fixing an existing one - register@corsasport.co.uk

There are also many more features available when you are logged in such as private messages, buddy list, location services, post search and more.


Author Excel Delivery Diary?
Pip308
Member

Registered: 25th Oct 07
Location: Basingstoke Drives: Audi A4 Avant, Mk1 Caddy
User status: Offline
21st Feb 13 at 09:42   View User's Profile U2U Member Reply With Quote

At my work we have a delivery diary - its a A4 piece of paper with the date going down the side and the deliveries next to it in pencil...

I'd like to bring us up to date with some sort of electronic delivery diary, but I don't have a clue where to start.

I need the date, customer, what their load (delivery) is, their address and possibly a cell for if they've paid or not.

Is all this possible and easy, would like it as clean as possible.

We can do a maximum of two deliveries a day as its just one driver and one lorry so if I could make it so I can easily put a post code and it say "cant do that" due to time limits/distance. if you know what I mean?

Phil
A2H GO
Member

Registered: 14th Sep 04
Location: Stoke
User status: Offline
21st Feb 13 at 10:35   View User's Profile U2U Member Reply With Quote

I'm guessing this is not as simple as:

Pip308
Member

Registered: 25th Oct 07
Location: Basingstoke Drives: Audi A4 Avant, Mk1 Caddy
User status: Offline
21st Feb 13 at 11:56   View User's Profile U2U Member Reply With Quote

not really, thanks tho, i've searched google etc but cant find what i'm looking for
Budgie
Member

Registered: 2nd Dec 09
Location: Basingstoke
User status: Offline
21st Feb 13 at 12:20   View User's Profile U2U Member Reply With Quote

Are the deliveries on a pdf or separate file? If so I'd hyperlink it instead of filling out a cell.
Can you not use google maps and just fill a cell out with the time of how long it will take to get there and back?
Are they repeat deliveries btw?

edit:
something like this?


[Edited on 21-02-2013 by Budgie]
Dom
Member

Registered: 13th Sep 03
User status: Offline
21st Feb 13 at 14:39   View User's Profile U2U Member Reply With Quote

The postcode bit sounds overly messy for Excel and unless you want to do the tracking manually (essentially it'd be a digital paper copy of what you do now) then you'd be better off getting in some decent logistics software.
evilrob
Premium Member

Avatar

Registered: 16th Mar 12
Location: Your mum's house
User status: Offline
21st Feb 13 at 14:55   View Garage View User's Profile U2U Member Reply With Quote

Postcode Anywhere have a ready-built solution for distances/directions, but you will get charged up to 16.7p per check depending on how much credit you buy upfront:

http://www.postcodeanywhere.co.uk/route-planner-services/how-to-use/

Or you could naughtily use Google Maps API via a macro to get the distances between postcodes.
Pip308
Member

Registered: 25th Oct 07
Location: Basingstoke Drives: Audi A4 Avant, Mk1 Caddy
User status: Offline
21st Feb 13 at 15:20   View User's Profile U2U Member Reply With Quote

The problem with postcode/direction/times on google maps or other is that lorries take longer and driver must take a break after certian amount of time
evilrob
Premium Member

Avatar

Registered: 16th Mar 12
Location: Your mum's house
User status: Offline
21st Feb 13 at 15:29   View Garage View User's Profile U2U Member Reply With Quote

How do you currently estimate how long a delivery will take?

Is it as simple as:

GoogleMapsTime + n% lorry goes a bit slower factor

+

If (GoogleMapsTime + n% lorry goes a bit slower factor) is greater than x minutes then add y minutes break
Dom
Member

Registered: 13th Sep 03
User status: Offline
21st Feb 13 at 15:31   View User's Profile U2U Member Reply With Quote

quote:
Originally posted by Pip308
The problem with postcode/direction/times on google maps or other is that lorries take longer and driver must take a break after certian amount of time


Postcode Anywhere does allow you to create vehicle profiles (sure it did the last time i looked at the API) that it can reference when getting you distances/time/directions etc But even then it's far from a walk in a park to 'throw' something together.

Either way, it sounds like you want a logistic package and it's not something you're going to knock up in Excel over a lunch-break. Better of trialling a few packages, get some prices and attempt to get your MD to sign off on it.
evilrob
Premium Member

Avatar

Registered: 16th Mar 12
Location: Your mum's house
User status: Offline
21st Feb 13 at 15:39   View Garage View User's Profile U2U Member Reply With Quote

As I see it you have the following variables:

DP = Depot Postcode
D1P = Delivery 1 Postcode
D2P = Delivery 2 Postcode

LF = Lorry factor (e.g. 150% - i.e. you're making the assumption it will take 50% longer again than a regular car journey)

RTMIL = Round trip miles
RTMIN = Round trip minutes

MMIL = Maximum number of miles a driver can travel before they are required to take a break
BRMIL = Break required when maximum miles reached (e.g. 30 minutes)
MMIN = Maximum number of minutes a driver can drive for before they are required to take a break
BRMIN = Break required when maximum minutes reached (e.g. 15 minutes)

From this you would calculate your round trip of DP -> D1P -> D2P -> DP by generating a Google Maps URL and parsing the returned XML.

e.g. RTMIL = 120 miles, RTMIN = 240 minutes

However, let's say MMIL = 50, so we would calculate how many whole MMIL's go into RTMIL; in this case it would be two, so we would add 2 x BRMIL = 60 minutes
And MMIN = 100, so we need to apply 2 x BRMIN as well = 30 minutes

So your total estimated time would be:
RTMIN * LF +
(ROUND(RTMIL/MMIL,0) x BRMIL) +
(ROUND(RTMIN/MMIN,0) x BRMIN)

240 * 1.5 + 60 + 30 = 450 minutes

If you're happy with that logic, I can upload something in about half an hour.

[Edited on 21-02-2013 by evilrob]
evilrob
Premium Member

Avatar

Registered: 16th Mar 12
Location: Your mum's house
User status: Offline
21st Feb 13 at 15:46   View Garage View User's Profile U2U Member Reply With Quote

Your next issue will be that your estimated mileage won't take into account having to take a different route because of low bridges or weight restricted road etc.

I'd be inclined to agree with Dom on getting a logistics package, but it's an awful lot of expense for 2 deliveries a day!

I have some experience in fleet management - and it aint cheap!
Dom
Member

Registered: 13th Sep 03
User status: Offline
21st Feb 13 at 15:53   View User's Profile U2U Member Reply With Quote

quote:
Originally posted by evilrob
Your next issue will be that your estimated mileage won't take into account having to take a different route because of low bridges or weight restricted road etc.


As said, Postcode Anywhere could sort that as the API (just had a look now; function 'DistancesAndDirections/Interactive/FreightDirectionsAndLines') takes into account weight/height/length/width of the vehicle. They also do route optimisation which is something a lot of the bigger logistic packages will do.

Still not something you're going to knock up unless you're handy in a programming language (still going to need to know VB/VBA to some degree to get it going in Excel).
evilrob
Premium Member

Avatar

Registered: 16th Mar 12
Location: Your mum's house
User status: Offline
21st Feb 13 at 15:56   View Garage View User's Profile U2U Member Reply With Quote

quote:
Originally posted by Dom
quote:
Originally posted by evilrob
Your next issue will be that your estimated mileage won't take into account having to take a different route because of low bridges or weight restricted road etc.


As said, Postcode Anywhere could sort that as the API (just had a look now; function 'DistancesAndDirections/Interactive/FreightDirectionsAndLines') takes into account weight/height/length/width of the vehicle. They also do route optimisation which is something a lot of the bigger logistic packages will do.

Still not something you're going to knock up unless you're handy in a programming language (still going to need to know VB/VBA to some degree to get it going in Excel).

Yes, I know that, Dom - it was me that suggested Postcode Anywhere in the first place.

I'm offering to build a solution based on Google Maps on the understanding it can't do freight routes but won't cost 16p per query.
Dom
Member

Registered: 13th Sep 03
User status: Offline
21st Feb 13 at 16:23   View User's Profile U2U Member Reply With Quote

quote:
Originally posted by evilrob
quote:
Originally posted by Dom
quote:
Originally posted by evilrob
Your next issue will be that your estimated mileage won't take into account having to take a different route because of low bridges or weight restricted road etc.


As said, Postcode Anywhere could sort that as the API (just had a look now; function 'DistancesAndDirections/Interactive/FreightDirectionsAndLines') takes into account weight/height/length/width of the vehicle. They also do route optimisation which is something a lot of the bigger logistic packages will do.

Still not something you're going to knock up unless you're handy in a programming language (still going to need to know VB/VBA to some degree to get it going in Excel).

Yes, I know that, Dom - it was me that suggested Postcode Anywhere in the first place.

I'm offering to build a solution based on Google Maps on the understanding it can't do freight routes but won't cost 16p per query.


Was more of an informative reply to Pip rather than you.

Although im not sure how you'd implement Google Maps API into Excel as it requires the map to be shown (also part of their licensing that you must show the map if using the 'free' version).
Ian
Site Administrator

Avatar

Registered: 28th Aug 99
Location: Liverpool
User status: Online
21st Feb 13 at 16:41   View Garage View User's Profile U2U Member Reply With Quote

Wouldn't even use the API, just open another window and plan a route, then put the distance in to the spreadsheet.

Automating it is a massive job and not all that good an approach anyway as highlighted, the driver will probably not stick to the route you come up with, so your measurements are rough anyway.

Also that figure of two deliveries may be subject to change, I'd read up on the tacho rules and work backwards from there. The combined distances, driving time and speed of the truck are what dictates the number of deliveries you can do, not the other way around.
evilrob
Premium Member

Avatar

Registered: 16th Mar 12
Location: Your mum's house
User status: Offline
21st Feb 13 at 16:45   View Garage View User's Profile U2U Member Reply With Quote

quote:
Originally posted by Dom
im not sure how you'd implement Google Maps API into Excel as it requires the map to be shown (also part of their licensing that you must show the map if using the 'free' version).

quote:
Originally posted by evilrob
Or you could naughtily use Google Maps API via a macro to get the distances between postcodes.

By blatantly breaking the license terms... or by showing a map in a web browser control. For the volumes the OP is doing, Google won't give a flying fuck either way.
evilrob
Premium Member

Avatar

Registered: 16th Mar 12
Location: Your mum's house
User status: Offline
21st Feb 13 at 16:50   View Garage View User's Profile U2U Member Reply With Quote

quote:
Originally posted by Ian
Wouldn't even use the API, just open another window and plan a route, then put the distance in to the spreadsheet.

Automating it is a massive job and not all that good an approach anyway as highlighted, the driver will probably not stick to the route you come up with, so your measurements are rough anyway.

Also that figure of two deliveries may be subject to change, I'd read up on the tacho rules and work backwards from there. The combined distances, driving time and speed of the truck are what dictates the number of deliveries you can do, not the other way around.

As per discussion the other day, you know I can automate IE from Excel; Google isn't going to know whether or not that IE instance is visible.

All I was trying to do was help out with the OP's requirements; I agree it's not a good way of doing it, but it does seem like it's a 'back of a fag packet' setup anyway at the moment - was just offering to mash up a spreadsheet equivalent of the existing paper system, with known limitations, but free.

There are any number of better, paid-for, solutions - but as you say they're complex, and they're also expensive to buy or implement.

On the basis that their process is exactly as described, a wonky spreadsheet will for the most part 'do the job' for up to two deliveries a day.

[Edited on 21-02-2013 by evilrob]
evilrob
Premium Member

Avatar

Registered: 16th Mar 12
Location: Your mum's house
User status: Offline
21st Feb 13 at 16:53   View Garage View User's Profile U2U Member Reply With Quote

I once wrote some software for the PHS Group Clinical Waste division with similar requirements (though more of a 'travelling salesman' problem) - they had about 80 depots across the UK, a number of wagons (I forget how many) and thousands of lifts a month.

Back then, before we had Google Maps, we used MapPoint.

It doesn't matter what route you give a driver; they're going to do whatever they want anyway.

[Edited on 21-02-2013 by evilrob]
Ian
Site Administrator

Avatar

Registered: 28th Aug 99
Location: Liverpool
User status: Online
21st Feb 13 at 17:09   View Garage View User's Profile U2U Member Reply With Quote

The API does optimise the way points, I've had a bit of a play for some other software I was thinking about.

In fact, I might run that by you at some point, I got nowhere with it because alongside the Google API and another one that integrates in to it, I couldn't get something resembling a good UI.
evilrob
Premium Member

Avatar

Registered: 16th Mar 12
Location: Your mum's house
User status: Offline
21st Feb 13 at 20:35   View Garage View User's Profile U2U Member Reply With Quote

Had a spare moment this evening so whipped this up:
https://www.youtube.com/watch?v=tH7hjPgsjuY

In the video I am manually doing things in Google Maps alongside the spreadsheet only to verify/demonstrate it is working.

In this example I have 4 deliveries that need to be made and I want to find out which two I can do in a day.

There is a parameters worksheet where you can specify the 'slow lorry factor', maximum number of minutes a driver can do in a day, the depot postcode, and set criteria for when breaks should be taken and for how long.

To find out the round trip for one delivery (i.e. depot -> delivery address -> return to depot), you set column G to TRUE for the appropriate row. Column J will return the miles from Google Maps, Column K will return an adjusted number of minutes including 'slow lorry factor' and any required breaks.

If you want to see the miles / minutes for a second delivery on the round, you put the delivery ID of the second delivery in column H.

Column M will return TRUE or FALSE depending on whether the total minutes required to do the round is less than the maximum number of minutes a driver can do as set in the Parameters worksheet.

edit: edited due to incorrect column specified above.


[Edited on 21-02-2013 by evilrob]
dannymccann
Member

Registered: 9th Aug 06
Location: Doddington, Lincolnshire
User status: Offline
21st Feb 13 at 20:47   View User's Profile U2U Member Reply With Quote

I have nothing to add to this discussion apart from to ask Rob, can you send me that spreadsheet so I can see how you've done it, I'm a bit of a geek when it comes to spreadsheets but am still a novice
evilrob
Premium Member

Avatar

Registered: 16th Mar 12
Location: Your mum's house
User status: Offline
21st Feb 13 at 20:50   View Garage View User's Profile U2U Member Reply With Quote

No probs - what operating system / version of Office are you using? (so I can send you an appropriate version that doesn't have any wanky conversion bollocks when you open it)
Dom
Member

Registered: 13th Sep 03
User status: Offline
21st Feb 13 at 21:10   View User's Profile U2U Member Reply With Quote

Rob, you clearly love Excel

Is that scraping Google Maps for the mileage then or are you directly linking to the API?
evilrob
Premium Member

Avatar

Registered: 16th Mar 12
Location: Your mum's house
User status: Offline
21st Feb 13 at 21:17   View Garage View User's Profile U2U Member Reply With Quote

It comes from years of working for companies who want stuff automated but don't want to pay for the appropriate software or infrastructure to do it properly.

"We've got Excel - can't you do something with that?"





This one is calling the API, but I could just as easily scrape it. Path of least resistance for proof of concept was to use the API cos then you get lovely XML back.

If this does what the OP wants and they're worried the Google feds are going to come knocking for breaking the rules, I can scrape the results instead.
Ian
Site Administrator

Avatar

Registered: 28th Aug 99
Location: Liverpool
User status: Online
21st Feb 13 at 23:58   View Garage View User's Profile U2U Member Reply With Quote

I should install 7 at some point, just can't get a liking for that task bar.

  <<  1    2  >>
New Topic

New Poll

  Related Threads Author Forum Replies Views Last Post
is there a program that...... chris_uk Geek Day 6 1812
16th Jun 05 at 00:47
by Dan B
 
Excel Help AndyKent Geek Day 3 1384
6th Oct 07 at 10:22
by aPk
 
x2 MCN Bike show Tickets FS - Excel London Daimo B Parts Offered 2 421
29th Jan 08 at 11:44
by VXR
 
need an excel like programme (for free) Shane Geek Day 12 1686
13th May 08 at 14:55
by pow
 
Microsoft Excel strick206 Geek Day 10 1399
6th Jul 11 at 14:46
by Sam
 

Corsa Sport » Message Board » Off Day » Geek Day » Excel Delivery Diary? 28 database queries in 0.1782451 seconds