The auto-import feature uses Internet Explorer to literally go into Seller Central, request the reports, and download them. In some cases we get the data directly from the page (like shipping data). This process requires that you have Internet Explorer (at least v11), and a stable internet connection. The auto-import feature will not work with Excel for Mac.
There are a few things that could, and sometimes do, go wrong:
- When saving a file we have to use what is called ‘SendKeys’ to click some buttons. If this process is interrupted when a key is pressed, or an IE window goes out of focus, it can trip up the macro. There is no easy way to recover from this other then skipping the file, and moving on. It is best to not use your computer when the import is running.
- Seller Central failed to load within 10 seconds, and data was skipped. Check your internet connection to be sure.
- Amazon changed something on a page that we were looking for. This happens from time to time. Recently Amazon put a limit on the amount of sales data that could be downloaded per report to 180 days.
If 1 or 2 happens, simply run the auto-import again, and the missing data will try to download. If an item simply refuses to download, open a support ticket so we can take a look.
Yes, absolutely! A new sheet will have to be created since most of our spreadsheets are locked for editing to prevent things from breaking. You can still pull in data from any cell by referencing it in your formula.
Just follow these steps:
- At the bottom, middle of the spreadsheet, click the + next to the right of the sheets tabs to add a new sheet
- Click the triangle in the upper left next to cell A1
- Right Click and select “Format Cells”
- Select the Protection tab and uncheck “Locked” (this prevents the new sheet from being password protected when updating data)
- Add whatever you want to the new sheet.
Be sure to uncheck “Locked” before clicking update on the dashboard. Otherwise, the cells will be locked on the new sheet and you won’t be able to see the Protection tab under Format Cells.
Yes! As long as you’ve imported all of your FBA shipment data on the FBA Shipping Data sheet, there is a report on FBA Shipping Sum, “Shipments With Discrepancies” that will show shipments that had differences in received quantities with what you shipped to Amazon. A positive Qty Difference are ones you should be concerned about. You can simply copy the Shipment ID into the search on the Manage FBA Shipments page
This can happen if the Amazon data is in one format, but your regional settings on your computer, which Excel uses, are in a different format. The dates in the Amazon data need to match the date format on your computer.
For example, you have US Amazon data, so dates will be converted so Excel can use them to m/d/yyyy, but you are in the UK, so your computer is setup with dates in the format d/m/yyyy. This confuses Excel.
Here’s how to fix it: (via How to change date format in Excel and create custom formatting)
- On your computer, Go to Control panel and click Region and Language. If in your Control panel opens in Category view, then click Clock, Language, and Region > Region and Language > Change the date, time, or number format.
- On the Formats tab, choose the region under Format, and then set the date and time formatting by clicking on an arrow next to the format you want to change and selecting the desired one from the drop-down list:
If you are in the UK, loading US Amazon data, choose “English (United States)” at the top, and click Apply.
You may need to download a fresh copy of the spreadsheet after applying these settings! Things may have broken on the back end!
Dates should now be in the correct format on your spreadsheet.
There are several reasons the inbound shipping cost could not be calculating correctly:
- On the FBA Shipping Data sheet – check to make sure the Ship Cost Calculated column is populating. It is only populated if the Date Shipped is the previous day that you were charged for the shipping (or if you fill in Actual Ship Cost). This data is only available in the sales data and shows up as “FBA Amazon-Partnered Carrier Shipment Fee”. If there was more than one shipment on the same day, the total shipping cost is added together, and the sum of the Qty Received (total qty for that date) is averaged to get Avg Ship Cost/Item.
- The dates in “Import Shipping SKU Lists” are missing or incorrect. This could be because the dates were removed from the shipment name when the shipment was created, or the dates are in a format that the spreadsheet does not recognize. (it will recognize US and UK dates, and dates in the format 1/1/17, 1.1.17, and 1-1-17)Normally the Name of a shipment looks like this: FBA (1/1/17 12:00 AM) We recommend, when creating a shipment in Seller Central, to leave the date in tact.
If you do not have dates in your shipment SKU lists (tsv files), the only way to fix this is to edit the files and enter the date manually next to “Name” in the .tsv files. Unfortunately, there is no other way to get this date from Amazon data. Worst case, your “Days in Transit” will be incorrect.
*Update for v2.23 – If you are using the auto-import & dates are not formatted correctly in the TSV files, the spreadsheet will use the dates directly from the Amazon FBA Shipments page.
To automatically get the Actual Ship Cost:
The Actual Ship Cost will populate automatically with the auto-importer when clicking to update all data, just the FBA shipping data, or by clicking “Auto Import Shipping Costs” on the FBA Shipping Data sheet. The manual processing fees and labeling & prep fees are added into the total shipping cost.
To manually enter Actual Ship Cost:
“Actual Ship Cost” can be manually entered on the FBA Shipping Data sheet if you want more accurate shipping cost per item. Cells that are red in column BD need a value filled in. The shipping cost can be viewed by copy/pasting the URL in column BF into a browser, then see Service Fees (at the top), “Amazon Partnered Carrier estimated shipping cost:” Be sure to add any manual processing fees and Labeling & Prep fees into the total.
OR by going to Inventory > Manage FBA Shipments, search for the fba-shipment-id
When the Actual Shipping Cost is filled in, it should look something like this:
This is because the spreadsheet is using styles that you may not have or there is something else that you version of Excel does not recognize.
Choose “Open and Repair”
If the entire spreadsheet is does not have any formatting and you use Excel for Mac, please make sure that there are no conflicting fonts in Font Book.
This may be because of the charts, or you need to update to the latest version of Excel for Mac.
Choose “Open as Read-Only”. After doing so, save a copy of the spreadsheet.
The Ultimate Sales Spreadsheet will work properly on Amazon properties that output their reports in English.
Currently the spreadsheet will work with:
- Amazon.com
- Amazon.ca
- Amazon.co.uk (separate version)
The spreadsheet will NOT work with:
- Amazon.com.mx
- Amazon.de
- Amazon.fr
- Amazon.it
- Amazon.es
- Amazon.co.jp
- Amazon.cn
- Amazon.in
If you attempt to use the spreadsheet with non-English reports, it will not calculate Returns, and detailed fees properly because it is looking for specific English phrases in the reports to categorize those fees.
You must have separate spreadsheets between properties unless you combine reports before entering them into the spreadsheet.
This warning comes up because the Spreadsheet connects to local Amazon reports (that you’ve previously connected to the spreadsheet), and because macros are used. You’ll want to click Enable Content. Otherwise, the spreadsheet will not update correctly after data is added.
The lite version of the Reimbursement Checker, Inventory Value Calculator, and Tax Summary spreadsheets will work with LibreOffice or OpenOffice, with the exception that you’ll have to manually paste in the reports that are downloaded from Amazon.
Unfortunately, the macros and pivot tables that are used in all other spreadsheets will not run properly in LibreOffice, OpenOffice, or Google Sheets.
