Note: these steps are for Tiller powered Google Sheets but a similar workflow can be used for Excel.
Download Your Amazon Order Report
- Log in to your Amazon account at https://www.amazon.com/gp/b2b/reports
- Choose the date range for the orders you’d like to download.
- Click “request report” and a CSV will download onto your computer.
Move Your Amazon Order Data to Your Transactions Sheet
- Move the Amazon order reports CSV to your Google Drive and open it as a Google Sheet.
- Copy the data in the “Order Date” column (Column A) for all your Amazon purchases.
- Navigate to the Transactions sheet in your Tiller spreadsheet and scroll to the bottom of the list (add additional rows if there are no blank rows on your Transactions sheet).
- Right click into the first empty date cell (usually column A) and click Paste Special > Paste Values Only. Choosing Paste Values Only makes sure none of the formatting gets copied over.
- Do the same thing for the “Title” (Column C) from the Amazon CSV and paste (values only) it into the Description column in the Tiller Transactions sheet.
- Insert a column to the right of the “Item Total” column (Column AD) and paste the following formula =0-AD2 to format the amount data for use in your Tiller Transactions sheet. This formula assumes the Item Total data is in column AD, and should then output the value as a negative number rather than a positive number. You can use the quick fill method to fill in this amount number for the rest of the items.
- Finally, copy the correctly formatted amount data (Column AE) from the Amazon download and paste it (values only) into the Amount column in the Tiller Transaction sheet.
Clean Up Your Amazon Order Data
From here you’ll want to categorize your Amazon order details using the categories you’ve set up in your Tiller spreadsheet. You also may want to fill in the Account, Account Number, Institution, Month and Week Data for these transactions. The Amazon CSV includes a Payment Instrument Type (Column O), which includes the last four digits of the card used for the purchase so you could use that to map these transactions to an account, simply use your checking account or whatever account ultimately pays off the card tied to Amazon.
Finally, you can sort the data by date so that the transactions are in chronological order like the rest of your automated financial transactions. Hover over the date column (column A) to display the dropdown arrow you can click and sort the transactions by date from Z to A.
How to Reconcile The Other Transactions Associated with Amazon Purchases
As we mentioned at the beginning of this post, the original Amazon purchase for $157 is going to show up in your Tiller Transactions sheet at some point if you have that account linked to one of your sheets. So what do we do with that original transaction if we’ve got these line item details now that total up to $157? To account for this, and stay on track, we recommend doing an export right after a bulk purchase transaction comes through from Amazon so you can keep those details straight. You can check the order date against the bulk purchase transaction that Tiller was able to automate and make sure the Item Totals are equal to the bulk transaction amount. Note that the order date from Amazon may be one or even two days earlier than the date Tiller records the bulk purchase transaction. Then you can delete the original bulk Amazon purchase transaction Tiller pulled in.
Ideally, we’d love to automate the line item details directly from Amazon into your Google Sheet. But as of yet we aren’t able to do this, so this is the best and quickest solution. Feel free to send us feedback on this workflow and how you’ve improved it for your own financial tracking.