Import Amazon order reports to create transaction splits

I buy a great deal of goods from Amazon for both business and the home, and while I have separate business and personal Amazon accounts there is still no way to categorize my purchases (especially orders with multiple items) without a manual review of the order and manually making splits and assigning categories. This doesn’t require any collaboration with Amazon. This can be achieved using the daA they provide their customers freely! :)

When viewing your user account data on the Amazon site, there is an option where you download complete reports for any time period in CSV format. There are two types of reports. Both are required to itemize your orders, but together they contain all of the data necessary to fully automate this process with little to now user intervention.

(1) All of the individual Amazon orders with order number along with the transaction details such as date, payment method, and order total. It also breaks the order total into total COG, sales tax, “Subscribe-and-Save” discount, gift card balance, and credit card reward points.

(2) All of the items ordered from Amazon along with their order number, quantity, item price, and some redundant info like payment method.

The goal here is to use this imported data to create complete item splits out of existing transaction that match the date and order total from report (1). Next, append the Amazon order number to the transaction somewhere so it can be referenced later. Now split the transaction and add lines for sales tax and other items (credits, discounts, etc...) from report (1) Finally, parse report (2) and match each item to its respective Amazon order number and add each item to the existing split lines for that transactions.

Once the transaction’s splits are completed (or more likely as each split line is added) Quicken can assign categories per the user’s existing rules so that all a user needs to do is categorize any items which haven’t been done so already. Also, with the Amazon order number saved in each Quicken transaction’s info, it can avoid duplicates as well!

Presently the only way to do this is manually. While I’m grateful Amazon has provided the data in an easy-to-use format, the process of correlating it with transactions is the kind of task an algorithm really should be doing, not a human being trying to manage their cash flow.

Please consider this!
3 votes

New · Last Updated


  • volvogirl
    volvogirl SuperUser ✭✭✭✭
    Do you have Amazon set up as a separate Account like a credit card?  Or are you splitting your payment with the individual purchases?  It might help to have it as an Account.  
  • hinder90
    hinder90 Member ✭✭
    > @volvogirl said:
    > Do you have Amazon set up as a separate Account like a credit card? Or are you splitting your payment with the individual purchases?

    At this point I am just manually copying and pasting data that I am manually clipping from BOTH of the Amazon reports to create splits for the existing credit card transactions that Quicken pulls from my credit card account. Manually doing this is as laborious and tedious as it sounds.

    >  It might help to have it as an Account.  

    I have considered this as well, but could you please explain your idea? It seems to hinge upon being able “map” columns from a CSV file into the appropriate Quicken fields. Since Quicken appears to just “do it” without any ability to change change the mapping I am not sure how I could customize this.

    Ideally, were I to create accounts, I would want to first create an account for Amazon orders and import Amazon’s report of orders into that account where taxes, discounts, etc... were made into splits for each transaction. These orders could be matched by way of a transfer to the credit card transactions using the date and order total amount. Each order’s “Total cost of goods” would not be imported but rather left as a discrepancy that would be resoled when I Imported the Amazon items report. Then, each line of the items report could be put into second account for Amazon items and would be keyed on the Amazon orders account thus creating the splits for each item of an order, resolving the previously mentioned discrepancy.

    This all could be made far less obtuse if I could write a script that would batch process the two Amazon reports to create a single CSV file that is imported into Quicken to populate an Amazon orders database. I could regularly import that processed file for, say, each month. Then, all I would need to do is manually link the Amazon orders to their associated credit card transactions by way of a transfer. I just don’t know what format that file needs to be either as a CSV or QIF.

    Anyway, sorry for the long response and thanks for your suggestions @volvogirl as they were helpful.
  • hinder90
    hinder90 Member ✭✭
    @volvogirl Quick questions: for Amazon orders and item breakdown what kind of account would you use. I think you said Credit Card for Amazon orders but what do you think would make sense for Amazon Items?
  • volvogirl
    volvogirl SuperUser ✭✭✭✭
    You lost me.  What is the difference with Amazon orders and Amazon items?
Sign In or Register to comment.