The Robservatory

Robservations on everything…

 

Download and track Amazon purchases over time

Recently I was trying to enter our Amazon purchase details into Quicken—the transactions download automatically, but they're generically assigned to "Shopping." I was using Amazon's order history page to match transactions and assign categories, but it was slow and painful going given the layout of the orders page.

I thought there must be a simple way to download your Amazon purchase history—once downloaded, I could use a spreadsheet to search my transactions. As it turns out, there is a way, but it's far from simple. Nonetheless, after way too much work, I now have an Excel workbook that makes it very easy to find any of our historical transactions. I enter a year and amount on my Search sheet, and any matches appear immediately:

Getting from "this should be possible" to "it works!" was (as usual) more of a challenge than I anticipated, but it is now working as I wished.

My adventure started with a search for any official download solution from Amazon. From what I found, the following URL used to be available to everyone…

https://www.amazon.com/gp/b2b/reports

Alas, when I (and a few friends) tried it, that URL just landed on the standard orders page. (If you know of a working link to download your history, plesae share here and then everyone can ignore the rest of this post!)

Never one to be deterred by a lack of official support, I did some more digging, and found the Amazon Order History extension for Chrome. (View it in the Chrome Web Store.)

Using that extension (in Chrome, of course), I was able to download all my historical data—it's a bit time consuming if you've got a long history with Amazon, as you have to do one year at a time. However, it does work.

Once I had all my data, I set to work on the workbook, learning quite a bit about some obscure Excel features along the way. In addition to the search tool, the final product includes a summary of spending over the years—here's how that looks with some sample data…

To use my solution, you'll need to download your Amazon data and install one of the two versions of the Excel workbook. Here's how to do all that…

Download Your Amazon History

The first step is to download your data, and while there may be other methods of doing so, my workbook depends on the format created by the Amazon Order History web extension in Chrome. After installing the extension, login to Amazon and go to your Orders page. Now click the extension's toolbar icon, and you should see a drop-down showing your available history:

Click one of the available years, and your data will load and appear onscreen. At the very top is a link to download the CSV; that's what you need to click. After downloading, rename the file to the year, i.e. 2018.csv. Repeat for each year of history you have. (Amazon may log you out during this process; it happened to me twice. Just log back in and continue.)

Choose a Workbook

Once you're done, it's decision time: Which version of my Excel workbook do you want to use—the macro version or the plain version? (After making that decision, the process is the same, so it's really just a matter of preference.)

How do they differ? The "plain" one is just an Excel workbook; the macro version automatically hides years for which no worksheet exists. As soon as you add a properly-named worksheet, that year magically appears on the summary worksheet:

The non-macro version does offer nice alternating-row colors, which are somewhere between very hard and impossible to do properly when hiding rows, so the macro version lacks that nicety. Personally, I prefer the macro version, but I know some don't like using macro-enabled workbooks, so I made both versions.

These workbooks will not work in Numbers. The macro one will obviously fail (no macros in Numbers), but even the non-macro one uses a number of formulas that aren't supported in Numbers. I imagine it could be made to work, but you're on your own if you're going to attempt that.

Here's how both workbooks appear, loaded with my set of sample data (click for larger image):

You can, of course, manually hide the blank rows in the normal workbook to clean up its look. And yes, if your Amazon history extends back before 2000, you'll have to modify my workbook to handle it—I don't know how far Amazon's order history goes, but mine started in 2003.

Download your preferred workbook: Non-macro versionMacro version

Open the downloaded workbook, and you'll see the structure is quite simple: There's a summary worksheet, then three years with dummy data, just so that there's something to show how it should look when it's working.

How to use the workbook

Regardless of which version you choose, the usage is identical. Here's what you'll need to do after opening the downloaded file:

  1. Erase the data in the 2018, 2019, and 2020 worksheets. (Or if you don't have any Amazon purchase data in some of those years, delete the relevant sheets.)
  2. For each year of history you downloaded, create a new worksheet in the main workbook, and name it for that year in four-digit form, i.e. 2019.
  3. Open one of the downloaded CSV files by dragging and dropping it onto Excel's Dock icon.
  4. In the CSV worksheet, select all rows except for the very last row (a summary). It's also important that you include the top row (column headers), as the workbooks assume the actual data starts on row two.
  5. Press Command-C (or use Edit > Copy) to copy the selected data.
  6. Switch to the main workbook, and activate the worksheet whose year matches the data you copied.
  7. Click on cell A1 and press Command-V (or Edit > Paste).

In theory, that should be everything you need to do. Switch to the Summary worksheet and make sure the newly-added year's total dollars are showing. If they are (hooray!), repeat the process for your other data files (and you can delete the CSV files when done). If they're not, see the "Fix the Totals" section below before proceeding.

With everything copied in, check that the Summary page is accurately showing all your purchases, and save the file. For future updates, you can download the current year's CSV, and then just select any new rows since the last update and paste them at the bottom of the current year's worksheet. (Though it's probably easier to just select everything and replace the existing data.)

Using Search

The Search worksheet, which I originally gave up on due to the complexity of some of the formulas, is now my favorite part of the workbook—it greatly simplifies matching a transaction from Quicken with my history file. Usage is easy: Select the Search worksheet and enter both a year and a dollar value. If there aren't any matches, the first row of results will say as much. But if there are matches, up to five duplicate dollar values will be found and displayed.

Note: Search only searches on the dollar value—there's no ability to search on words in the description. (Excel's built-in search should handle that task.)

Here's how a multi-item search looks; I searched for the typical cost of our cat litter subscription:

With Search, I can have Quicken open in one window, see an Amazon purchase amount, type it into the Search worksheet, and instantly display the details of that purchase. As noted, there is a five-match limit, so if you have a lot of identically-priced purchases, this tool won't find more than the first five matches.

Notes and Caveats
  • The "LOW $" column excludes any purchases that had zero cost.
  • Click the triangular icon next to any year in the summary sheet, and you'll jump to that year's data sheet.
  • The worksheet is only covers 2000 to 2030. If you're still using it after 2030, you'll need to add more rows and copy and paste the formulas from 2030.
  • The workbook tracks "only" 999 purchases per year. If you're buying more times per year than that, you should probably seek help for a shopping addiction. (Or else edit the formulas in column M in the Summary worksheet, changing !E999 to !E2500 or whatever you need.)
  • If nothing seems to work, here's (most likely) the problem: My spreadsheet is built around the US data model. Other countries may have different structures—I know Canada does for sure. If you're not seeing the totals, please see the Fix the Totals section below.
  • Columns L through N on the Summary worksheet are set to zero width to sort-of hide them: They contain the formulas that drive the workbook. Do not change these unless you know what you're doing, or you're following the Fix the Totals instructions below. There is one exception to this rule: If you don't like the small triangular indicator I've used to indicate a link to the year's detailed worksheet, just change the character in cell L38.
  • Nothing is protected or locked, so make a copy of the workbook before you start modifying anything—just in case!
Fix the Totals

These workbooks are built around a basic assumption: The data was scraped from the Amazon USA order page. In the resulting CSV file, column E contains the total order cost, and it's really the only column used by the workbook. If you're not seeing the total dollars on the summary sheet, check your data file and find which column letter holds the total cost of an order.

Once you've found that column, make sure that what's there is a number, and nothing else—my friend's Canadian data file had "CDN $" in front of every number in each cell. If the cell isn't a pure number, use Find and Replace to get rid of anything other than the number itself (make sure you check for leading or trailing spaces).

There are two ways to fix the totals: You can either modify the data files (potentially simpler but will have to be done every time), or you can update the formulas in the Summary worksheet.

To modify the data files, move things around such that column E holds the total order cost column. Do that, and the totals should work. Important note: You need to do this editing in the CSV file prior to copying and pasting into the tracking workbook.

The other solution is to change the formulas, and it's just a bit more work…

If you're using the macro workbook:

Before you proceed, you need to disable the macro that hides blank rows. Right-click on the Summary tab and select View Code. Click in the code editor window and press Command-A to select all the text, then click the Comment Block button in the toolbar; it should look like this after clicking the button:

Once that's done, switch back to the workbook, and unhide the hidden rows—select from row five to some row below the table, then double-click on any row divider to expand the zero-height rows. Now continue with the instructions…

If you're using the non-macro workbook:

Now that you know the column letter, you need to modify the cells I earlier said not to modify. Go to the Summary sheet, and expand columns L through N (highlight K through O and double-click the divider bar between them). Select cell L6, and you'll see a formula there:

=IF(NOT(N6),"","'"&B6&"'!E2")

The only important bit here, and the one you need to change, is the !E2 at the end. Change the E to the column letter of the column that holds your total cost. Once that's done, select cell M6, and do the same edit: Change the !E999 at the end to reflect the proper column letter.

Once those two formulas are changed, copy and paste them into L7 to M36, and that should do it. Check that the totals are there; if they are, hide the columns again (select columns L, M, and N and drag the divider bar left until they have zero width) and save your workbook. If you're using the macro version, go back to the code editor, select all the code again, and click the Uncomment Block button, which is immediately to the right of the one you clicked before.

If that doesn't fix the problem, you can try asking me for help, though I'll need to see a copy of your data file to provide any assistance.

Thanks!

Although these workbooks are conceptually simple, there are some things I wanted to do that seemed quite tricky. Most of those tricky bits were originally figured out by others, and I just modified them for my needs. So a big thank you to all these folks…

  • The slick macro that automatically hides blank rows is thanks to Mike H. in this thread on the Microsoft forums.
  • One of the trickier things to figure out was how to use a formula to link to another worksheet in the current workbook; I thought it'd be trivial, but it wasn't. I finally found the answer (by "Nothing Left to Lose") in this thread on the Microsoft forums.
  • I wanted to leave a given year blank if there was no matching worksheet for that year; I found this nifty formula on Exceljet that returns TRUE only when a worksheet of a given name exists—a very handy little formula!
  • To exclude zero values when calculating minimum purchase price for a given year, I found and lightly modified a formula from this page of the ExtendOffice site.
  • Finding more than one match in Excel is complicated (unless you're using a worksheet set up as a database), and I was struggling with come of the complex references. While I didn't use the exact formula, this page at AnalystCave got me headed on the right track.
Wrap Up

Though it was a fair bit of work to create this spreadsheet, using its Search sheet makes it much simpler to keep track of our Amazon spending going forward. Updating will be relatively easy, too, as only the current year will need to be scraped via Chrome. Please post any questions you may have…

1 Comment

Add a Comment
  1. Hi Rob. I noticed your template used XMATCH, which my copy of Excel 2016 doesn't have, plus there was some steps which could potentially be eliminated. But of course, it took me down a series of rabbit holes -- while most of it took about an hour, recreating the SMALL function took about two!!

    I've reproduced most of the functionality here: https://docs.google.com/spreadsheets/d/1C376Av7raynm_Pj4VPdYJ-nI-Be4OYy2fx147bGzBOA/edit?usp=sharing

    Operation:

    Download the CSV files, and rename them consistently with the year (eg 2018.csv, 2019.csv).
    Open up the Google sheets template and save to your own Drive
    Click on File -> Import, select Upload, then drag in your CSV file. Be sure to select "Insert new sheet(s)" rather than creating a new spreadsheet. Repeat for each year's CSV file.
    Don't bother copying and pasting or deleting rows. Just use the CSV sheet as it is.
    That's it! The summary values will calculate automatically

    The filtering is a bit more clunky -- basically you have to click on the filter symbol and it'll refilter down to just years that have values. Also, I didn't have time to do the hyperlinking (it seems to involve some Javascript, and I haven't written Javascript for about 20 years).

    Thanks for the little project!

Leave a Reply

Your email address will not be published. Required fields are marked *

The Robservatory © 2020 • Privacy Policy Built from the Frontier theme