Hacking eBay: one more 'R programming is fun!'

How about scoring an iphone 30% less than average selling price on eBay?

Plenty of strategies could be found online. However, here is a fun programmatic way of doing it (Thanks to my friends Frank and Paul for inspiration).

It is possible by using 'saved search' + frequently refresh the search!

There are tons of deals on eBay if you can find it. It takes a lot of time to get a bargain though. 'saved search' is a nice feature which could be used to filter out garbage and narrow down to certain price range. For example, searching 'iphone 5 black 16gb' would return a lot of garbage such as accessory or damaged phones. Advanced search provide some options to clean the returned listings (see attached screen shots end of the post. Make sure putting a few exclusion tokens.)

After a nice 'saved search' is created for a price range, the key is to get the deal a few seconds after someone listed the product within the price range. One could keep push the 'saved search' on My eBay link to refresh the search, or one could automate the process. The fun part is the the automation. I guess automation is more fun for me than actually scoring a deal.

The URL looks like following for a saved search:
http://www.ebay.com/sch/Cell-Phones-Accessories-/15032/i.html?_sadis=200&LH_SALE_CURRENCY=0&_from=R40&_samihi=&_fpos=&_oexkw=&LH_Time=1&_sop=12&LH_PayPal=1&_okw=&_fsct=&_ipg=50&LH_BIN=1&LH_ItemCondition=4&_samilow=&_udhi=400&_ftrt=903&_sabdhi=&_udlo=300&_ftrv=1&_sabdlo=&_adv=1&_dmd=1&_mPrRngCbx=1&_nkw=iphone+5+16gb+black+-sprint+-t-mobile+-bad+-water&saveon=2

You can see the keyword, price range, filtering criteria, etc. in the URL.
Here is a simple program to automate the process. One could keep it running every second and when deal shows up. An email alert would tell one to rush to eBay and buy it. If no deals, the program should not alert with email.

Steps in R code. It could be easily scheduled through windows scheduler. Note I have email feature set up to email. You may not have same set up. I am sure you can use alternative set up as I mentioned in another post on email script.

url='xxxx'   #copy URL here
x<-paste(readLines(myurl, warn=F), collapse="\n")   # get the eBay search result page
n=gsub("^.*\\s+(\\d+)\\s+active listings.*?$", "\\1", gsub("\\n|<[^>]+>", " ", x, perl=T), perl=T) # identify items found

if (n>0) {
 write(x, file='test.html')
 mycmd<-paste("email.exe -s ", "found", " -html -tls -c email.gmail  xxxx@gmail.com < test.html", sep="")
 shell(mycmd)

}

Happy shopping!


Snapshot of how 'Saved Search' could be configured.

Product is specified here!

Exclusion is here!


















Price range should be specified 30% below the average selling price for the item.
'Buy it Now' is the best format for this kind of deal.








Only show newly listed items.

Another complex sql operation example

postgresql array function

transpose a list of ids into a flat delimited row. the rOw could serve as a filter for subsequent queries:

SELECT

array_to_string(array_agg(trim(id)),’,')

FROM terms

Let’s say terms table has a list of ids:

id

1

2

3

after query we get:

1,2,3

Oracle way of doing this from my friend Darrin.

Here is the Oracle way to do this…

Select                     listagg(account_id,’,') within group (order by account_id asc)

From                      gg_account

You can also do

Select                      portfolio_id,

listagg(account_id,’,') within group (order by account_id asc)

From                      gg_account

Group by                portfolio_id