Import Amazon order reports to create transaction splits

hinder90

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!

volvogirl

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

> @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

@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

You lost me.  What is the difference with Amazon orders and Amazon items?

Quicken Anja

Hello All,

The Community Support team regularly reviews long-standing posts and Ideas for relevancy and current interest. This Idea seems to have stalled and we would like to gauge the current interest in this request. 

If you would like to see this idea implemented, please add your vote and a comment explaining how this idea would be beneficial for you. More information, including steps to vote and how to submit your own Ideas for future product features/improvements, is also available here.

Thank you,

Quicken Community Support Team

JBL 2002

This would be very helpful if there was a way to easily input Amazon order reports into quicken in automated format. Amazon is a large % of my “shopping” expense, but I’d like to more easily be able to distinguish between groceries, clothing, furnishings, office supplies etc. for tracking purposes.

jacobs

@JBL 2002 This would require (a) Amazon to encode everything they sell with standardized categories, (b) Amazon build a server to allow third-party programs such as Quicken to log in on behalf of users to download order information, (c) Intuit and/or Quicken to build a new import service (independent of financial transactions with Amazon from all credit card companies) to pull in this data and map it to standard Quicken categories (d) Quicken to revamp Quicken Mac, Quicken Windows and Quicken mobile to be able to import transactions with multiple split lines for different categories. 

As a result of the complexity involved, I think its unlikely both Amazon Quicken would develop something like this. Perhaps if Amazon undertook such a system for all third-party personal finance programs and commercial accounting programs to use, Quicken could tie into it, but the effort would need to begin on Amazon's end.

I understand the desirability of such a feature, and people should definitely vote for it if they'd find it useful; I'm only trying to offer an explanation about the complexity involved and why it doesn't seem too likely. 

NotACPA

Also note that ONE order on Amazon might be shipped as several orders, with a separate charge for the amounts in each shipment.

SO, what to you might be a single purchase could actually be downloaded as several purchases.  I.E., a programming nightmare. (BTW, this one order, several charges scenario has happened to my wife on multiple occasions).

S A Rice

Came here looking for help with this. Upvoted!

I don't think it matters how difficult the programming would be for Amazon and Quicken. That's their job.

Almost all of my spending goes through Amazon and PayPal now. Maybe that's dumb, but it is currently making quicken less useful than it used to be for understanding my spending.

I don't actually need a split transaction for every individual item. I would be happy enough if they were grouped by major category in the Amazon store. So, e.g. Whole Foods, Books, Clothing. If these could appear as part of the payee, I could do my own mapping to suitable categories.

It would be treating Amazon like a big shopping center full of little stores. In the past, it was enough to see what store I made a purchase from, to know how to categorize it to a large degree. Of course I could tweak myself manually in some rare circumstance. But today, that rare circumstance now hits endlessly.

jacobs

S A Rice said:

I don't think it matters how difficult the programming would be for Amazon and Quicken. That's their job.

@S A Rice Well, you'd need to convince Amazon of that, since the bulk of this work would fall on them. Would they consider this worth developing for the small fraction of their customers who use Quicken? I'd think not, but who knows.

And then Amazon would need to work with Quicken and other makers of personal finance software to develop an entirely new standard for transmission of this type of data, since it wouldn't work within the current OFX/QFX standard. And they would need to set up servers for Quicken and others to log into to download this data. That's certainly not impossible -- we know Amazon has the technical chops to do it -- but it seems very unlikely to me.

S A Rice said:

I don't actually need a split transaction for every individual item. I would be happy enough if they were grouped by major category in the Amazon store. So, e.g. Whole Foods, Books, Clothing. If these could appear as part of the payee, I could do my own mapping to suitable categories.

But you have to think of them developing this for all Amazon/Quicken users. You might be happy with a handful of major categories, but someone else would complain it's not specific enough.

More importantly, you say you don't need a split for every item, but I don't see how this would work. If I place an order for a toaster oven, a computer hard drive, a book, a pair of jeans, and a candle, how would you expect Amazon or Quicken to categorize that with one category? It would have to be split by item. 

And then there's the issue raised in the post above from @NotACPA : single orders often ship in multiple shipments which are charged separately. Quicken's goal is to capture those credit card charges so you can reconcile what's in Quicken with your credit card statement, so there would need to be data available for each invoice, not each order. 

Don't misunderstand: I see the appeal of what you're asking for. I'm only trying to explain why it seems impractical and unlikely for it to come to pass. 

S A Rice

@jacobs some clarification -
I'm hoping Amazon can annotate somewhere their existing major store category that the item is listed under on their website. If a purchase consists of multiple categories I'm agreeing a split transaction is needed. Just no need to itemize eggs, milk, ham, tomatoes, ...
why am I suggesting that may be generally useful for folks as a default? The shopping center analogy. If somebody doesn't find the grouping granular enough, they are in the same boat as shopping center purchases, and manual editing is required.

Laird B

> @jacobs said:
> This would require (a) Amazon to encode everything they sell with standardized categories, (b) Amazon build a server to allow third-party programs such as Quicken to log in on behalf of users to download order information, (c) Intuit and/or Quicken to build a new import service (independent of financial transactions with Amazon from all credit card companies) to pull in this data and map it to standard Quicken categories (d) Quicken to revamp Quicken Mac, Quicken Windows and Quicken mobile to be able to import transactions with multiple split lines for different categories. 
[...]
> I understand the desirability of such a feature, and people should definitely vote for it if they'd find it useful; I'm only trying to offer an explanation about the complexity involved and why it doesn't seem too likely. 

Well laid out -- but I think a semi-automated process could be achieved. What follows is a 'thinking out loud":

- Download the Amazon item-level detail CSV that includes detailed description, detailed category, UNSPSC, etc.
- Transform the CSV into a QIF, yielding a journal row per item, rather than one per "purchase".
- Import the QIF into an AmazonDetail account
- Hand categorize, but at least you have it item by item and the full description available.

Enhancements:
- The transform could use the Amazon category data (e.g, "CHEWING_GUM") to assign a category ("groceries")
- The transform could use the UNSPSC (e.g, "50161800") to assign a category ("groceries")
- The user could categorize their Amazon payments as transfers to the AmazonDetail account, and reconcile them to balance the Detail account with the payment accounts.

[some experimentation.]

For example, I downloaded some data from Amazon, and hand-created this QIF content using that data.

/*
!Type:Cash
D8/16'21
T-13.40
PDavidson's Tea Tulsi Pure Leaves, 100-Count Tea Bags
MTEA
^
D8/16'21
T-6.36
PDentyne Fire Spicy Cinnamon Sugar Free Gum, School Lunch Box Snacks, 9 Packs of 16 Pieces (144 Total Pieces)
MCHEWING_GUM
^
*/

This imports into Quicken as (attachment screenshot), which would be a LOT easier to hand-categorize.

Laird B

(Second post attempt -- not sure if I inadvertently triggered a censorship bot, or what. This will be a shorter version, without attachments or links.)

> @jacobs said:
> This would require (a) Amazon to encode everything they sell with standardized categories, (b) Amazon build a server to allow third-party programs such as Quicken to log in on behalf of users to download order information, (c) Intuit and/or Quicken to build a new import service (independent of financial transactions with Amazon from all credit card companies) to pull in this data and map it to standard Quicken categories (d) Quicken to revamp Quicken Mac, Quicken Windows and Quicken mobile to be able to import transactions with multiple split lines for different categories. 

Individual users can automate it, or at least semi-automate it; I just finished a proof-of-concept.

- One time: create an "AmazonDetail" cash account in Quicken.

- Download the line-item-detail erport from Amazon in CSV format.

- Text-transform the Amazon item CSV into a QIF, retaining date and amount, description ("Davidson's Tea Tulsi Pure Leaves, 100-Count Tea Bags") into the Payee field, and Amazon category ("TEA") into the Memo field. (Can be done in Excel.)

- Optionally, during the text transform, use the Amazon category or the UNSPSC to auto-assign categories. (Complex Excel, or code.)

- Import resulting QIF into your AmazonDetail account.

- Assign categories by hand (much easier with "Davidson's Tea Tulsi Pure Leaves, 100-Count Tea Bags" than with "Amzn Mktp US*2A2Z999M0".)

- Optionally, when you pay Amazon, record it as a transfer into the AmazonDetail account, and then reconcile payments against line-items.

I just imported 3 months (about 200 line items) and it took less 10 minutes to categorize them. (Multi-select, right click, assign category helps a lot.)

EvErywhere_Sometimes

While I agree this is a complex algorithm, and the process of catagorizing really drives me nuts, it seems as though the information is already being made available by Amazon, because this feature is available via a 3rd party add in to QuickBooks Online.

After 25 years of using Quicken I am really unimpressed with QuickBooks Online, but this one App almost makes it worth it

https://quickbooks.intuit.com/app/apps/appdetails/AmazonBusinessPurchases/en-us/

EvErywhere_Sometimes

The other workaround I have (which also requires an Amazing Business Account) is much more manual, but only slightly more time consuming. When you have an Amazon business account each shipment comes with paper receipt. The key thing here is the receipt corresponds not to the order you made or even the shipment that have in front of you but what is being charged to your card. So you have a receipt that matches a line item which has been imported into Quicken.

The receipt also has a customized note on the top that is entered at the time of purchase. Process I've used his to put the name of the company I'm ordering for as well as the date.

That's an example I can order 10 items on January 1st. A note I will make this "MyCompany 2021-01-01"

This might be charged my credit card as to purchases which will be imported automatically by Quicken

It might show up in 4 boxes, but when I look at the receipts there will be two unique receipts his totals match the two line items in Quicken

So when it's time for me to reconcile I just organize my paper receipts by date, then sort my transactions by Company, and go down the list.

silverface

A related problem, and I have NO clue how to solve it.

I'm disabled and 90% of my non-food/non-medical purchases are through Amazon. I don't "input" the order charges into Quicken - they are automatically entered when I import my banking records - which I don't do daily.

But I HAVE test-entered them manually, and I end up with a second set of transactions - and they rarely can be added up to match (so I would be able to delete the order total from Quicken). Because they don't break out tax on each item on the order I have to calculate it for each item - and the sales tax charged on an item only matches the tax if I bought the same thing in a local store.

Return credits seem to be levied against items in random amounts, further complicating things.

So can either assign categories based on a rough date and amount that's close or just leave any imported charge not matching up with an Amazon invoice left as "uncategorized" - or whatever category Amazon or Quicken decides to use for some unpredictable time period.

I don't know which one randomly assigns categories, but they seem to be based on some item I recently purchased, changing at nonsensical/unpredictable intervals.

I buy some items for only TWO rental units I own(repairs, maintenance, various supplies) that I need to enter as tax-deductible expenses, and Amazon makes accuracy virtually impossible. Even if I buy those items on a single order, rarely is there a single invoice total (or group of charges) that match any imported amount in Quicken.

If I entered every invoice and credit manually and deleted ALL imported Amazon transactions after the fact 1) the TOTAL amount of Amazon charges shown on the invoices doesn't match the imported total, and 2) takes such an absurd amount of time I'd be better off using a ledger and doing accounting by hand .

And because I use Quicken for my accounting and importing into Turbotax, using BOTH Quicken and Turbotax would essentially be a part-time job. I actually have a life outside dealing with this stuff and have to find a solution - because NONE of my Amazon accounting data is anything but a wild a** guess.

I've called Amazon, called my bank (even though all they do is let me download whatever Amazon gives them), called Quicken support and nobody has a clue. It's absolutely absurd that Amazon and/or Quicken didn't anticipate normal humans needing EXACT charges for accounting purposes.

Unless someone knows of something I've missed that neither one knows about it seems logical that Amazon and Quicken (and other accounting software vendors) would get together and FIX this.

Solutions, anyone? Because what should be making personal accounting easier makes it (and the import into tax software) an absolutely dreaded task.

jacobs

Amazon would need to build reporting specifically for Quicken (and similar personal finance programs), and since only a small percentage of their customers likely use such software, I'd guess it's unlikely they'll do so. 

There are multiple obstacles. First, you place an order for four things, and Amazon may charge your credit card for the entire order, or break the charges into one, two, three or four charges, depending on the timing of availability/shipping as well as the selling platform (e.g. Amazon sales versus a third-party seller on Amazon's platform). Only the actual credit card charges are posted to your credit card account, and those charges are what download into Quicken, using the industry-standard OFX protocol which defines what information can be downloaded about each transaction. There is no way Quicken can parse out from a credit card charge whether it's for one item or five times, and what the category breakdown(s) should be for the item(s) on each charge.

Amazon would need to build an infrastructure such that Quicken could log into Amazon, and download transactional data by credit card charge, not by order/invoice, breaking down eery item purchased into a standard list of categories. Quicken can't do this if Amazon doesn't built the platform. I just don't see it being likely Amazon would develop this software, host the secure servers with all the data, just as a customer service for the small percentage of its customers who use Quicken. Maybe I'll be proven wrong some day.

Meanwhile, although I don't order as much from Amazon as you do, here's what I do to make this relatively simple for my record-keeping. Every time I order from Amazon, I print a copy of the Invoice Amazon generates. (You can print to a PDF if you don't want lots of paper.) I wait until the order ships, in case Amazon splits the items and charges them separately. I then enter these charges manually in my credit card account. This allows me to categorize each purchase appropriately, including creating splits for purchases which have multiple items in different categories, and applying sales tax to each split when needed. (Anyone who came from the legacy Quicken 2007 for Mac still pines for the QuickMath feature which made this single-keystroke easy… and I'm still holding out hope the Quicken Mac developers will get around to adding this feature someday.) When the credit card charges download, Quicken should automatically match the download to the manual transaction, and if it doesn't, you can drag on on the other to merge them. I think it's easier to do the manual entry work up front, rather than trying to reconstruct it weeks later when you're trying to reconcile your credit card charges.

I don't find doing the manual entries and splits to take a lot of time — and it insures things are categorized the way I think think should be categorized, not the way Amazon or Quicken might think they should be categorized.

Laird B

> @silverface said:
> I'm disabled and 90% of my non-food/non-medical purchases are through Amazon. I don't "input" the order charges into Quicken - they are automatically entered when I import my banking records - which I don't do daily.
[...]
> I buy some items for only TWO rental units I own(repairs, maintenance, various supplies) that I need to enter as tax-deductible expenses, and Amazon makes accuracy virtually impossible. Even if I buy those items on a single order, rarely is there a single invoice total (or group of charges) that match any imported amount in Quicken.



It sounds as though you are commingling the personal purchases and the business (rental unit) purchases in the same Amazon account or in the same payment card or both. I'm not sure about the rest of it, but that can't be helping the clarity.

silverface

I don't order single items for the duplex rental and a separate one for myself in the other unit - that's absurd! I use splits in Quicken to break expenses up, but purchases through Amazon would have to be made in single units for the rental and another purchase for me, with each ITEM on a separate order!

Amazon runs the charges as single items including tax sometimes; at other times they split items up because of THEIR changes in shipping dates; or combine the charges for items shipped on the same day.

There is virtually no way to track costs unless each product is ordered separately. That would be incredibly time consuming, and maintenance supplies are not purchased that way.

But even then, buying more than a single item causes Amazon to combine shipping costs (if any), taxes and the item cost - then bill it/them on a date that doesn't always match with the order.

If they simply billed each order complete (as placed) charges would match up - but they seem to bill single or combined items randomly. And there is no way for the buyer to go by "shipping dates" as their shipping has gotten bizarre, with "next day" sometimes meaning 2-3 days - but billing not matching THOSE days!

tehrhard

I want to upvote this discussion for Quicken and Amazon to work on this in future enhancements. Having used Quicken for many years and having to reconcile hundreds of Amazon transactions is a nightmare!

Amazon does categorize all their items which could be added as a note or label. Since they break things down by item and shipping tracking number, this too could be how Quicken would group items to sub-categorize.

Undoubtedly manual changes will be required to make your personal categories match those from Amazon, yet if Quicken could merge credit card transactions with like Amazon shipping and items shipped then we would at least be closer to a solution. As it is now I am challenged with a length task of comparing and manually matching each of these in Quicken, or painfully downloading Amazon data and merging it with downloaded Quicken data to reconcile OUTSIDE of Quicken....neither of these being a good or sustainable approach to reconciling the household books. This is only compounded with multiple adults on an Amazon Prime account with different sets of transactions.

Quicken...are you listening? As a literal lifelong customer (and IT professional), please work on this!

jacobs

tehrhard said:

Quicken...are you listening?

Quicken can't do anything to make this happen unless Amazon decides to come up with a way to make this happen. Are you bugging Amazon to make this happen?

It's pretty complicated, as discussed above in this thread, because an order you place with Amazon can be billed as multiple separate transactions, so they'd need to export item-by-item categorized amounts for each invoice, not each order. If they created such a system and allowed third-party applications to log into to download this data — which they might not want to do for security reasons — then an only then can Quicken attempt to come up with a way to download and properly import such data. 

Personally, I'd guess that the very small percentage of Quicken users among Amazon's customer base makes this highly unlikely to happen. (And if they did something, Amazon would need to come up with a solution which works for all third-party personal finance programs, not just Quicken.)

Eric Therrien

Personally, allI would like it's to have the charge from Amazon log into my credit card account so that when I sync with the bank, it get recognized and linked. So that if anything get charged extra, I can investigate it...

veramilo

I upvoted. Yes, nightmarish is a fair way to describe reconciling Amazon orders in Quicken!!! As of 2020, we've become more heavily into online shopping than ever so it's time for banking apps to address this.

I'm not an actual programmer, but I know that simple importing of data is one of the easier things to program. I would love it if Quicken could create a way for users to assign the columns they care about from any itemized CSV report to the fields Quicken has available and import these into a discrete account (so glitches don't disrupt existing transactions) with each CSV item becoming a separate Quicken transaction. With a user-customizable import setup like this in place, it can be adapted to any online shop that offers downloadable itemized CSV reports. You still have to maintain the original payment transactions from providers for reconciling, but I would gladly welcome this as compromise to make categorizing easier.

The tricky part is separating the sales tax (if that's even necessary, because it's in a different column in Amazon -- maybe it can be programmed as a split) and categorizing shipping charges and digital orders (because those are different reports) but anything is possible.

My own stopgap is using a separate spreadsheet where once a year I categorize the CSV Item reports I import from Amazon. I then subtotal the categories and MANUALLY enter a single transaction for each category total (all dated December 31 of the tax year) in a separate "Amazon Categorized" account I setup in Quicken. If I have items that were paid with a gift card, I also have to account for those with a standard transaction in the "Amazon Gift Card" account I set up in Quicken (this is for reconciling -- see next step).

I then search for all Amazon transactions in Quicken EXCLUDING the "Amazon Categorized" account and categorize them all as "Amazon Original" which is a category I exclude from expense reports. I reconcile this category total against the sum of all the categories in my "Amazon Categorized" account and cross my fingers that they match! When they don't, it's usually because some of my original orders are digital or had shipping charges (most of my shipping is free). If there are only a handful, I manually check the Transaction or Digital Download CSVs for those charges, add them to the appropriate account manually, and check again. If there are a lot, I generate another spreadsheet for categorizing.

As I mentioned, I only do this once a year which is adequate for my needs although I should do it more often. It's still far more tedious than it needs to be but I tolerate it.

whitenack

This is an old thread but I would like to upvote this feature as well. Quicken could kill a lot of birds with one stone if they would allow importing transactions from a csv file. Many financial institutions and vendors (including Amazon) will allow you to export their transactions into a csv file. From there, it would be easy for Quicken to create an importing tool that would allow you to map the columns to what quicken needs. They already have an importing tool for QIF and QXF, so it wouldn't take much tweaking to make it work with csv.

Chris_QPW

whitenack said:

They already have an importing tool for QIF and QXF, so it wouldn't take much tweaking to make it work with csv.

Both QXF and to some degree QIF have known formats.  CSV on the other only says "Comma Separated Values", nothing about those values.  So, the conversion is not nearly as straightforward and would require the users to map columns to fields and such.  Most likely they don't want to have support all the questions they might get on it from the users.

Unknown

This content has been removed.

Unknown

This content has been removed.

MAnthony42

Agreed with comment and suggestions above. This would have the single largest impact on my usage and value from Quicken. In other words, this is a Pain Point, Quicken product managers!

NotACPA

Not gonna happen. Quicken derives income from the licensing arrangements with the Financial Institutions that provide downloads into Q.

Allowing CSV transaction import would significantly reduce that income.

jacobs

@NotACPA said: Quicken derives income from the licensing arrangements with the Financial Institutions that provide downloads into Q.

I know that's been the accepted conventional wisdom for many years — but I wonder if it's true. First, this originated when Quicken was part of Intuit. My understanding from some Quicken folks was that being a participating financial institution was free for most, and only a small number with "enhanced branding" in Quicken, which included Direct Connect participants, paid fees. Once Quicken split from Intuit, I'd imagine Intuit kept most or all of those fees, since Intuit's doing the connectivity work. And as Direct Connect slowly fades, the fees from participating financial institutions are also likely on the wane. Financial institutions cite "improved security" when switching away from Direct Connect, but I wonder if ditching the hefty fees they pay to Intuit is a significant part of it, too.

Allowing CSV transaction import would significantly reduce that income.

This feature request would not affect that income, if it exists, because it wouldn't replace connectivity to a financial institution; it would add connectivity to Amazon to properly break apart and categorize Amazon purchases.