Anatomy of an Amazon Lookup Program for Online Arbitrage

I designed and coded a program that queries the Amazon Product Advertising API. It records the prices and sends an alert if the price meets a certain threshold. This might sound like a pretty simple idea, but there is actually a lot to consider.

Program Requirements

  • Retrieves the price data for thousands of items as frequently as allowed
  •  Sends a mobile alert and updates a website when a price match is made
  •  Allow for easy removal, addition and editing of “watched” products
  •  Displays graphs of historical price data, as well as sales rank
  • Program needs to start up automatically and run for long periods of time without any stability issues.

Additional Challenges (these were “discovered” later)

  •  Amazon changes their database, by redirecting ASINs, and they do not provide this information via their Amazon Product Advertising API
  •  The need to ignore certain sellers became apparent, and this also is not available through the Amazon Product Advertising API.
  •  Text data can take up a lot of space and tables become too large.

A Look at the Program I Developed

I solved all of these challenges.  I wrote the program first in Java and then re-wrote it in PHP and Javascript with more features.  You can write a program that achieves these tasks in pretty much any language.  A popular choice these days would probably be Python or Ruby on Rails or Node.js.  Since I’m going to be discussing general design & implementation here, this article aims to be helpful irregardless of which language you choose.

Key Design Ideas

  • I chose to separate the background worker thread, which looks up the price data, from the user-facing interface.
  • I used the Model-View-Controller (MVC) architectural design pattern.  My model was a MySQL database.  My controller was a PHP script.  My view was implemented primarily with Javascript and HTML/CSS (using the Bootstrap framework).
  • I created a visual dashboard that gives feedback as to the program’s current status, most recent matches, and recent error messages
  • Exceptions are logged but the program is able to recover from them and continue running.
  • I wrote an algorithm that throws away the least important data to save space.  I created summary tables that save just the daily low and high prices and I used these for my graphs.

The Dashboard

Screenshot of Amazon Lookup Program for Online Arbitrage
Screenshot of the Amazon Lookup Program for Online Arbitrage
  1. Green status indicator.  This will turn to a red X if the lookup program is not running correctly (for example, no internet connection or MySQL connection error)
  2. Link to the Status page, which displays errors and information about recent queries
  3. Input field to lookup an ASIN. If it’s in the database, a price history chart is displayed.
  4. The user can star a match that they are considering.
  5. Clicking on the product’s name links to the price history page, which also has a link to Amazon for easy purchasing.


The Amazon Search Portal

Screenshot of the Amazon Lookup Program - Search Portal
Screenshot of the Amazon Lookup Program – Search Portal

The Amazon Search Portal is the part of my program that allows for easy addition, editing, or removal of products from the database.   Products that are checkmarked are already in the database.  The details badge is a link to a popup that allows for editing the desired price and notes.  The update button allows for easy addition or removal of products.  I used Boostrap.js for the design elements.  It’s a really easy way to make a web app responsive and less ugly!

Thinking about doing something like this?  Already did it?

I’d love to hear your experience.  You can definitely make money buying low and selling high — that’s not new.  But with programming skills and a lot of time, you can certainly write programs that do a lot of the work for you!  Automation is definitely one of my favorite programming tasks to take on.


Sanitizing & Validating User Input for Amazon Product Advertising API

Summary:  Remove apostrophes to prevent Amazon from returning “No Results”

As a security-conscious web programmer, I always sanitize user input. It’s something you have to do to prevent attacks like SQL Injection and Cross-Site Scripting.  Basically it means that you never trust user input to be safe, and you always filter or sanitize it to remove potentially dangerous input.   PHP has lots of filters for sanitization, so that part is easy.  For Amazon item search, my PHP code filters the user input like this:

if(isset($_POST['title']) && !empty($_POST['title'])) {
     $search_values['Title']= filter_var($_POST['title'], FILTER_SANITIZE_STRING);

But there’s a second step.   In addition to sanitizing data, you also want to validate it to make sure that it’s in the expected format.  But, in order to validate correctly, you have to understand the rules about what’s expected. Sometimes the rules are obvious, but other times you’re working with a bit of a black box.

Third party APIs are like black boxes.  You can’t read the code.   You only have the documentation to rely upon.   Giving the wrong input will get you errors or no results, and you might not know what you’re doing wrong.  So, you need to experiment and see if you can figure out what’s allowed and what’s not.

This post is specific to the Amazon Product Advertising API, which is an Amazon service that allows you to look up product information, like availability, pricing, keyword search, etc.

I use the Amazon Product Advertising API for several different projects, and for one of them, my application uses the ItemSearch operation. Specifically, I often search the Books category by Title.

Amazon’s API behaves differently than the search function on their site.  On their site, if you put in an apostrophe, the site just ignores it.  When using the API, you get no results.

So, the solution is to always strip the user input of all apostrophes. Or, if you’re a fellow programmer, you might call them single quotes. If you leave the apostrophe in, you’ll get no results. If you remove it, you hopefully get lots of results!

In my program, I am sending the user input via an HTML Form using Ajax.  My browser actually encodes the user input before sending it to the server.  It encodes the apostrophe character as its html entity which is '.

So, in my back end PHP code, I remove the apostrophe with this:

str_replace("'", "", $my_search_string);

Problem solved!   In actuality, I have combined that function with my code for sanitization, and the whole thing looks like:


if(isset($_POST['title']) && !empty($_POST['title'])) {
     $search_values['Title'] = str_replace("'", "", filter_var($_POST['title'], FILTER_SANITIZE_STRING));