Tracking UPS Packages in Excel?

May 29, 2006 6:35 AM

Is there a way to import UPS tracking information into an MS Excel document?

I have about 500 tracking numbers in an Excel spreadsheet. What's the best way to find out the status of the packages in a bulk way?

posted by dripdripdrop to computers & internet (6 comments total)

I know that you can download csv's of tracking info from ups.com, but I'm still hoping for a way to import directly into Excel (so that it will update whenever the file is opened)
posted by dripdripdrop at 7:05 AM on May 29, 2006

Are you wanting your Excel file to "call" the website when opened? I don't believe that is an option. You might be able to tweak Quantum View Data to make your CSVs more useful, but that won't address your specific want.

Your best option is probably UPS's Quantum View Manage. The service is very powerful and customizable. Best of all, it is free. An online demo is available.
posted by grabbingsand at 7:57 AM on May 29, 2006

Here's an introduction to bringing data from the web into Excel...it's from 2001, so it's old, but the tool they point to (Data...Import External Data...New Web Query) is still there.
posted by bachelor#3 at 11:08 AM on May 29, 2006

Unfortunately I don't have a UPS account and these were all shipped by a someone else.

What I ended up doing was downloading the csv's (you can only do 100 at a time, so I had paste them all together), then used vlookup() to import the tracking info into the original file. Not so real-time, but it doesn't really take that long.
posted by dripdripdrop at 9:03 PM on May 31, 2006

bachelor: I tried out the Web Query as well, but I can't get UPS to display a page of all the tracking numbers, and can't get the Web Query to "grab" the tracking ID from a cell.

Didn't know how to use the Web Query before though, so thanks.
posted by dripdripdrop at 9:28 PM on May 31, 2006

dripdripdrop, here's a page explains how to do dynamic Web Queries. According to this article, the key is getting the URL from the website that Web Query would use to retrieve the data, in this case, the URL used to track packages. I tried entering "1234567890" as a tracking number on UPS's home page, and got back this URL:

http://wwwapps.ups.com/WebTracking/processInputRequest? 
HTMLVersion=5.0&loc=en_CA&Requester=UPSHome&tracknum=1234567890 
&AgreeToTermsAndConditions=yes&ignore=&track.x=22&track.y=6 

...this would all be on one line, of course. In that URL, there's a parameter, tracknum, that holds the tracking number. By taking the address of a cell with the tracking number and placing it here, you've got the makings of a dynamic web query. Since I don't have an actual tracking number, I can't see what the resulting page looks like, but you should be able to select the relevant information on the resulting page via Web Query and have it placed in a cell.
posted by bachelor#3 at 10:45 AM on June 3, 2006