Table of Contents


Background

First Things First

The Process

Download the Sample Rate and Zone Files from ShipWorks

Copy Your Rates into the ShipWorks_LocalRatesFile.xlsx Worksheet

Copy Your Rates into the ShipWorks_ZoneFile.xlsx Worksheet

What's Next

Still Need Help


Background


ShipWorks version 5.14 or later allows you to optionally import your UPS account's rates from an Excel file and store those rates locally in the ShipWorks database. With this feature enabled, ShipWorks displays the imported rates on screen when you configure UPS shipments. This can help to improve performance when processing UPS shipments and also gives you the ability to compare rates across your shipping providers (including UPS) when using ShipWorks’ Best Rate feature. Your actual cost for processing a UPS shipment is not affected by using this feature.


ShipWorks requires that your UPS account rates and zones be in a specific file format prior to being imported into the ShipWorks software. This article will guide you through how to download the ShipWorks Sample Rate and Sample Zone files and how to copy your UPS account specific rates and zones into the downloaded ShipWorks files.  Once your rates and zones are copied into the ShipWorks sample files, the files can be used to import your rates and zones into ShipWorks. The files can also be saved as templates for importing updated rates and zones into ShipWorks. 


First Things First

There are a few things you will need prior to configuring the ShipWorks sample files:

  • Please be sure that you have ShipWorks version 5.14 or later installed.  You can check your version of ShipWorks by clicking on the Help tab and then on the About ShipWorks button.  For assistance with upgrading your version of ShipWorks, please see this article.
  • You will need to have your UPS account specific zone file, rates file, the current UPS DASZip file and several UPS Pricing Guides available.  If you do not have these available, please follow the steps in this article before continuing.
  • You will need to be logged into the ShipWorks software as a user with administrator rights.


The Process


Download the Sample Rate and Zone Files from ShipWorks


1.  In ShipWorks, click on the Manage tab and then on the Shipping Setting button.



2.  Select UPS from the list of shipping providers on the left of the Shipping Setting  screen.



3.  Select the UPS account for which you would like to import your rates and zones from the UPS Accounts section of the Settings tab.  Then, click Edit.



3.  Select the Local Rating tab.



4.  On the Local Rating tab, click on the sample rate file link in step 1 to download the ShipWorks sample rate file.  Save the file to a folder on your computer.




Please Note:

The file will is named UPSLocalRatesSample.xlsx by default.  To make things a little easier and to avoid confusion later in this article, we are going to rename ours to ShipWorks_LocalRatesFile.xlsx and will refer to the file by this name in the remainder of this article. 



5.  On the Local Rating tab, click on the sample zone file link in step 4 to download the ShipWorks sample zone file.  Save the file to a folder on your computer.




Please Note:

The file will is named UPSZoneSample.xlsx by default.  To make things a little easier and to avoid confusion later in this article, we are going to rename ours to ShipWorks_ZoneFile.xlsx and will refer to the file by this name in the remainder of this article.



Copy Your Rates into the ShipWorks_LocalRatesFile.xlsx Worksheet


To ensure that the rate information is in a format that ShipWorks can import, you will need to copy your UPS account specific rate information from the rates file provided to you from your UPS representative (or from the sample daily rates file downloaded from UPS.com) into the ShipWorks_LocalRatesFile.xlsx worksheet you saved in the above steps.  


In addition to your UPS account specific rates, you will also need the UPS Guides that you downloaded in the previous article.  You can also access these documents by clicking on the below links.


Regarding Multiple UPS Accounts in ShipWorks: Rates are imported into ShipWorks for each UPS account individually. If you have more than one UPS account attached to ShipWorks, each having different rates, and you intend to use Local Rating with each of your UPS accounts, you will need to follow the steps in this section to create an individual ShipWorks_LocalRatesFile.xlsx worksheet for each of your UPS accounts. If, however, all of your UPS accounts share the same rates, you can import the same ShipWorks_LocalRatesFile.xlsx worksheet into ShipWorks for each of your UPS accounts.


1.  Open both your UPS account specific rate file and the ShipWorks_LocalRatesFile.xlsx worksheet so that you can easily copy and paste data between them.  Notice that at the bottom of both workbooks there are multiple tabs to select from.  These tabs will be named similarly in both workbooks.


 

2.  Select the UPS NDA Early tab in your UPS account's rate file and the NDA Early tab in the ShipWorks_LocalRatesFile.xlsx worksheet.



3.  The goal is to copy all of your UPS rates from the tab in your UPS account specific rates file into the corresponding tab in the ShipWorks_LocalRatesFile.xlsx worksheet.  The tabs should be formatted the same so that you can just copy the data from your rate file into the ShipWorks sample file.


Things to consider prior to copying your rate information:

  • If there is existing rate data on the NDA Early tab of your ShipWorks_LocalRatesFile.xlsx worksheet, delete the sample rate information prior to copying your rates.
  • If the tab in your UPS account specific rates file is broken into sections, omit any blank rows, section headings, footnotes, etc. from your rates file when copying the rates into the ShipWorks_LocalRatesFile.xlsx worksheet.
  • Be sure to copy all of the rates from your file into the ShipWorks_LocalRatesFile.xlsx worksheet.
  • Be sure to save your work often.  The easiest way is to simply press CTRL + S on your keyboard while in the ShipWorks_LocalRatesFile.xlsx worksheet.



4.  Continue copying the rates from each tab of your UPS account specific rate file into the corresponding tabs in the ShipWorks_LocalRatesFile.xlsx worksheet.  It is important to note that you will only be copying rates for the tabs listed in the below table. (There may not be a tab in the ShipWorks_LocalRatesFile.xlsx worksheet for all of the tabs in your UPS account specific rates file.)


COPY FROM HERE           
TO HERE
UPS NDA Early
NDA Early
UPS NDA    
NDA
UPS NDA Saver
NDA Saver                                      
UPS 2DA A.M.
2DA AM
UPS 2DA
2DA
UPS 3DA Select
3DA Select
UPS Ground
Ground
see step 5
Value Added
see step 6
Surcharges


5.  Select the Value Added tab in the ShipWorks_LocalRatesFile.xlsx worksheet.  The data for this tab will be copied from the UPS Value Added Services Guide.  Next, verify that the Rate for each Value Added Service is correct according to the UPS Value Added Services Guide.  If the rate is incorrect in the ShipWorks_LocalRatesFile.xlsx worksheet, correct it with the rate listed in the UPS Value Added Services Guide.

  • To assist with locating the Value Added Service rates, the below chart shows on which page of the 2017 UPS Value-Added Services Guide to find the associated rate.



6.  Select the Surcharges tab in the ShipWorks_LocalRatesFile.xlsx worksheet.  The data for this tab will be copied from the 2017 UPS Other Charges Guide and from the UPS Fuel Surcharges webpage.  Verify that each of the Rates for the Surcharges are correct.  If not, update the rate in the ShipWorks_LocalRatesFile.xlsx worksheet according to the rates in the UPS Other Charges Guide or on the UPS Fuel Surcharges webpage.


  • To assist with locating the Surcharge rates, the below chart shows on which page of the 2017 UPS Other Charges Guide to find the associated rate.
  • The Fuel Air and Fuel Ground surcharges are located on the UPS Fuel Surcharge webpage.  You will find the information you need in the 90-Day Fuel Surcharge History section.  Enter the most recent date's surcharge. (see the below screenshot)

    Note that UPS updates the surcharges on a regular basis.  This article guides you through how to import and how to update the rate tables in ShipWorks as needed.


Find it here


The UPS Fuel Surcharges Webpage



7.  Now that you have updated all of the rates in the ShipWorks_LocalRatesFile.xlsx worksheet, save the file to an easy accessible location.  You will need to access the file in the next article when importing the rates into ShipWorks.



Note:  It is imperative that you save a backup copy of the ShipWorks_LocalRatesFile.xlsx worksheet as a template to make it easy to update your rates in ShipWorks when necessary.  Just click File > Save As while in the ShipWorks_LocalRatesFile.xlsx worksheet, give it a new name like ShipWorks Rates Import Template and save it to a secure location on your computer.



Copy Your Zones into the ShipWorks_ZoneFile.xlsx Worksheet


Next, you will need to copy your UPS account specific zones from the zone file you downloaded from UPS.com in the previous article into the ShipWorks_ZoneFile.xlsx worksheet.  


Regarding Multiple UPS Accounts in ShipWorks: Unlike needing an individual ShipWorks_LocalRatesFile.xlsx worksheets for each UPS account, only one ShipWorks_ZoneFile.xlsx worksheet is needed. If you have multiple UPS accounts with separate origin Zip Codes you will add the zones for each UPS account to individual tabs within the worksheet. Then, you will only need to import the ShipWorks_LocalRatesFile.xlsx one time in ShipWorks. (We will show you how later in this guide.)


1.  Open both your UPS account specific zone file (the Zone File.xls downloaded from UPS.com) and the ShipWorks_ZoneFile.xlsx worksheet so that you can easily copy and paste data between them.  


2.  In your UPS account specific zone file, locate the line For shipments originating in ZIP Codes XXX-XX to XXX-XX near the top of the spreadsheet (where XXX-XX is your postal code range) and make note of the range of Zip Codes.


In our example, our UPS accounts Zip Code range is 630-01 to 633-99.



3.  Right click on the first tab in the ShipWorks_ZoneFile.xlsx worksheet and select Rename from the list of options.



4.  Rename the tab with the range of Zip Codes from step 3 in this format XXXXX-XXXXX.

For example, since our range of Zip Codes is 630-01 to 633-99 we will name our tab 63001-63399.



5.  On the tab you just renamed in step 4, delete all of the existing zone data on the spreadsheet from row 2 down.  Be sure not to delete the column headings.




6. Copy the columns from your UPS account specific Zone File.xls into the columns on the tab you just renamed according to the table below.  

Note:  Do not copy the zone information for Alaska or Hawaii yet.  We will do this in the next steps.


COPY FROM THIS COLUMN
INTO THIS COLUMN
Dest. Zip
Next Day
Groun
Groun
3 Day
3 Day
2nd Day
2nd Day
2nd Day Air
2nd Day Air
Next Day Air
Next Day Air
Next Day
Next Day
Next Day
Next Day Air Early





PLEASE NOTE:  If you ship from more than one originating Zip Code, you will need to create a new tab in the ShipWorks_ZoneFile.xlsx worksheet for each UPS account.  Here's how:


a.  In Excel, click on the New Sheet button to create a new worksheet.


b.  Repeat steps 1 - 6 in the Copy Your Zones into the ShipWorks_ZoneFile.xlsx worksheet above.  Then, continue with step 7 below.



7.  In your UPS account specific Zone File.xls file, scroll down and locate the sections for Hawaii.  There should be two sections which will look like the below screenshot.



8.  Select the HI tab in the ShipWorks_ZoneFile.xlsx worksheet.  Copy the Zone and Zip Code information from your UPS account specific file into the HI tab in the ShipWorks_ZoneFile.xlsx worksheet.  If there is already data on the HI tab, replace all of the data that may already exist with the data from your UPS account's Zone file.


  • The below image is color coded to indicate where the information in your UPS account's zone file goes into the ShipWorks_ZoneFile.xlsx worksheet.




9.  Select the AK tab in the ShipWorks_ZoneFile.xlsx worksheet.  Copy the Alaska Zone and Zip Code information from your UPS account specific file into the AK tab in the ShipWorks_ZoneFile.xlsx worksheet. If there is already data on the AK tab, replace all of the data that may already exist with the data from your UPS account's Zone file.

  • The AK tab is formatted just like the HI tab.  See the above color coded image for an example.


10.  Open the DASZips.xls file you downloaded from UPS in the previous article.  Then, select the DASZips tab in the ShipWorks_ZoneFile.xlsx worksheet.


11.  Copy the Zip Codes from the UPS DASZips.xls file into the corresponding columns of the DASZips tab in the ShipWorks_ZoneFile.xlsx worksheet.  If there is already data on the DASZips tab, replace all of the data that may already exist with the data from your UPS DASZip.xls file. 



12.  Now that you have updated all of the Zone information in the ShipWorks_ZoneFile.xlsx worksheet, save the file to an easy accessible location.  You will need to access the file in the next article when importing the rates into ShipWorks.


Note:  It is imperative that you save a backup copy of the ShipWorks_ZoneFile.xlsx worksheet as a template so that you can easily update your UPS zones in the future as necessary.  Just click File > Save As while in the ShipWorks_ZoneFile.xlsx worksheet, give it a new name like ShipWorks Zone Import Template and save it to a secure location on your computer.



What's Next

Now that you have imported your rates and zone information into the ShipWorks sample files, you are ready to import both files into the ShipWorks software.


Importing UPS Rates and Zones into ShipWorks


Related Articles

UPS Local Rating User's Guide / List of Articles


Still Need Help?

Please feel free to reach out to one of our awesome Customer Care Representatives in St. Louis. We are happy to help.