Excel sheet to grab Newegg (and others) prices?

K

KuJaX

[H]F Junkie
Joined
Jan 8, 2001
Messages
15,778
Anyone know how this could be done as long as you have Newegg's (and other e-tailers) product numbers?

What I was thinking of doing is listing a bunch of Newegg Product Numbers and then in the column next to it after I refresh or something it will display Newegg's price, shipping, and finally total. Is this something that can be easily done or is doing like php a better solution?
 
Now that's an interesting idea.

How do sites like Froogle and PriceWatch get their pricing information? Are they scraping it from the site and parsing the HMTL and trying to find the pricing information?

Or are they going to newegg.com and querying prices and inventory from a web service that gives easy-to-parse, consistent XML results?

NewEgg doesn't appear to bet that hard to parse. If you want a price for item N82E16824187064, you go to GET the URL http://www.newegg.com/Product/Product.asp?Item=N82E16824187064

You'll find it has a convenient span around the price, so parsing is pretty easy:
Code: 
<span class="showPrice"><h3>$1,449.99</h3></span>

Now, doing that in Excel is a little harder. I don't know of a VBScript object that will let me do a web request...
 
I'd definately be interested in figuring this out also....... If you go to the web page in my sig., you can see what I've been doing by hand for hte last year and a half or so..... it'd be much easier to have that done automatically.... my knowledge of anything outside of C and Excel is VERY limited however.......


Keep on Folding!! For the [H]orde!!

 
Goto www.informationweek.com search for newegg. The Nov. 21st issue about website re-designs is the one you want. Page 2 of the article has newegg.

Here is the newegg part of the article

Newegg: Feeding Customers Prices
In reaching its level of E-commerce success, Newegg.com has built a reputation for aggressive price competition. The site is expected to have revenue of $1.3 billion this year, after just four years in business as an online computer retailer. But the latest site redesign brought in a level of price transparency that's daring, even for Newegg.


With the relaunch of Newegg.com earlier this year, the company took a number of steps--such as beefing up its personalization capabilities and simplifying site navigation--with the goal of capturing a larger share of customers' computer-equipment spending. It also has taken a particularly unusual step in E-retailing by introducing Really Simple Syndication feeds for customers who want to be updated constantly on pricing changes, an important feature for a site that has built a reputation for aggressive discount pricing. "Buyers for corporations can benefit from not having to browse for updated pricing," VP of marketing Howard Tong says. "On the negative side, it also makes it easier for the competition to stay on top of what we're doing. But we were willing to make that sacrifice."

Newegg delivers pricing feeds to four RSS readers: FeedDemon, NewsGator (which in May acquired Bradbury Software, maker of FeedDemon), FeedReader, and Pluck. The company hasn't measured the impact of the feeds; it doesn't know how many people have added its feed to their readers or whether the feeds are leading directly to additional sales. But Newegg has gotten dozens of E-mails from pleased customers since the feeds were introduced, Tong says

The biggest challenges are determining products for which it's worth setting up RSS updates (there were 46 updated feeds on a recent afternoon) and educating customers on how to use RSS, Tong says. Newegg's instructions are limited to a paragraph in a help section of the site.
Click to expand...

{edited for spelling)
 
mngl1200 said:
Goto www.informationweek.com search for newegg. The Nov. 21st issue about website re-designs is the one you want. Page 2 of the article has newegg.

Here is the newegg part of the article



{edited for spelling)
Click to expand...


Doesnt this just work for their RSS feed that they provide? How can I create my own for the specific products I want along with their price (and shipping)?
 
mikeblas said:
Now, doing that in Excel is a little harder. I don't know of a VBScript object that will let me do a web request...
Click to expand...

Mike, you don't know about the HTTPRequest Object? ;)

HTML: 
http://www.devguru.com/Technologies/xmldom/quickref/obj_httpRequest.html

There is are docs on MSDN somewhere, I don't remember where, I spent last week reading the while debugging some classic ASP code at work. I was concerned about the server side piece of it, but I distinctly remember saying that there was a client side piece too.

HTH.
Rick
 
KuJaX said:
Doesnt this just work for their RSS feed that they provide? How can I create my own for the specific products I want along with their price (and shipping)?
Click to expand...

well you get an rss file right? just simple XML. just parse that for the items/fields you need. it's gonna be way more straight forward than trying to parse the HTML.
 
Except that the RSS feed only includes selected items, not their whole catalog.

Bohica69 said:
Mike, you don't know about the HTTPRequest Object?
Click to expand...
Nope. I'm not a VB[script] programmer.
 
mikeblas said:
Except that the RSS feed only includes selected items, not their whole catalog.

Nope. I'm not a VB[script] programmer.
Click to expand...


Exactly what I was thinking. The RSS feed only includes selected items that Newegg chooses. I need to specify and get up to date information pertaining to the items I list.
 
Code: 
Function GetEggPrice(part)
    
    ' get the HTTP client
    Dim http As Object
    Set http = CreateObject("Microsoft.XMLHTTP")
    
    ' ask for our page given the part number
    http.Open "GET", "http://www.newegg.com/Product/Product.asp?Item=" + part, False
    http.send

    ' we're looking for a span named "showPrice"
    strTarget = "<span class=" + Chr$(34) + "showPrice" + Chr$(34) + ">"
    
    ' find it?
    nLocation = InStr(http.ResponseText, strTarget)
    If nLocation = -1 Then
        GetEggPrice = "Not Found"
    Else
        ' inside that span is an <h3> tag surounding the price
        ' well get an arbitrarily sized substring from the tag
        strPricePart = Mid(http.ResponseText, nLocation + Len(strTarget), 50)
        nStart = InStr(strPricePart, ">")
        nEnd = InStr(nStart + 1, strPricePart, "<")
        
        ' return that substring, finally
        GetEggPrice = Mid(strPricePart, nStart + 1, nEnd - nStart - 1)
    End If
End Function

This post is made without warranty and confers no rights.
 
mikeblas said:
Code: 
Function GetEggPrice(part)
    
    ' get the HTTP client
    Dim http As Object
    Set http = CreateObject("Microsoft.XMLHTTP")
    
    ' ask for our page given the part number
    http.Open "GET", "http://www.newegg.com/Product/Product.asp?Item=" + part, False
    http.send

    ' we're looking for a span named "showPrice"
    strTarget = "<span class=" + Chr$(34) + "showPrice" + Chr$(34) + ">"
    
    ' find it?
    nLocation = InStr(http.ResponseText, strTarget)
    If nLocation = -1 Then
        GetEggPrice = "Not Found"
    Else
        ' inside that span is an <h3> tag surounding the price
        ' well get an arbitrarily sized substring from the tag
        strPricePart = Mid(http.ResponseText, nLocation + Len(strTarget), 50)
        nStart = InStr(strPricePart, ">")
        nEnd = InStr(nStart + 1, strPricePart, "<")
        
        ' return that substring, finally
        GetEggPrice = Mid(strPricePart, nStart + 1, nEnd - nStart - 1)
    End If
End Function

This post is made without warranty and confers no rights.
Click to expand...

Thank you very much for taking the time to program something together. I am not a programmer, so I did what I figured I sohuld do and got a "#VALUE!" error.

I pasted your coding in tools>macros>visual basic editor. I then closed that and went back into Excel and punched in a cell:

=GetEggPrice(N82E16828112035)
Click to expand...

In that Cell I got "#VALUE!" Anything I missed?

edit: I also edited something else in the Visual Basic Editor for this workbook and now I get "#NAME?"
 
You get #VALUE with your invocation because Excel doesn't know the name N82E16828112035. You want to pass a string into the function:

Code: 
=GetEggPrice("N82E16828112035")

If you have the part number as a string in cell C5, you can just pass a reference to the cell:

Code: 
=GetEggPrice(C5)

and you're all set.
 
mikeblas said:
You get #VALUE with your invocation because Excel doesn't know the name N82E16828112035. You want to pass a string into the function:

Code: 
=GetEggPrice("N82E16828112035")

If you have the part number as a string in cell C5, you can just pass a reference to the cell:

Code: 
=GetEggPrice(C5)

and you're all set.
Click to expand...

Awesome Mike, adding the quotes worked! I will try to figure out how to grab the shipping also and do a few formula's to add them together. Thanks a bunch.
 
I'm glad you got it working. It wasn't hard to write, once Bohica69 pointed out that HTTP object.
 
You know what... for some reason it isnt working anymore. I just get "#NAME?" now.

I have tried lowering the security level, and also clicked "enable macros" when trying to access the XLS that it worked with last time. Any clue as to why it would show "#NAME?" now instead of "#VALUE?"

edit: It is strange, because I just went and crated a whole new Excel document, put in the coding in the Visual Basic editor and it worked like a charm. Saved the file, got back in, tried a few other product numbers and still worked. Don't know what went hay-wire with the last file to get "#VALUE?".......

Could you create one to check the shipping cost also or is it very much different?
 
I don't think I can spare the time to write a script for everything you'd like to check. Parsing the shipping cost out of the page isn't very difficult, given the code you've already got -- maybe you can use this as an opportunity to learn to do a little programming.

Sorry that I can't support you through it.
 
No problemo. I'll dink around with it I suppose. The layout seems logical, but some of the periods, "chr" plus signs etc make no sense to me. Obviously as I have absolutely no programming background what-so-ever.
 
Playing around with the code that was posted above (and knowing less than nothing about VB), I fumbled around until I figured out how to get the shipping price....

Code: 
Function GetEggShip(part)
    
    ' get the HTTP client
    Dim http As Object
    Set http = CreateObject("Microsoft.XMLHTTP")
    
    ' ask for our page given the part number
    http.Open "GET", "http://www.newegg.com/Product/Product.asp?Item=" + part, False
    http.send

    ' we're looking for a span named "notesavings"
    strTarget = "<li><span class=" + Chr$(34) + "noteSavings" + Chr$(34)
   
    
    ' find it?
    nLocation = InStr(http.ResponseText, strTarget)
    If nLocation = -1 Then
        GetEggShip = "Not Found"
    Else
        
        'Isolate the Shipping Price
        strPricePart = Mid(http.ResponseText, nLocation + Len(strTarget), 50)
        nStart = InStr(strPricePart, ">")
        nEnd = InStr(nStart + 1, strPricePart, " ")
        
        'Return the Shipping Price
        GetEggShip = Mid(strPricePart, nStart + 1, nEnd - nStart - 1)
        
    End If
End Function

Now I can update my spreadsheet to do these prices automatically! Thanks for all the help above! hopefully my contribution helps just as much!


Keep on Folding!! For the [H]orde!!

 
I keep getting the #NAME? error as well, and it continues even with a new document. any suggestions?
 
Here are some suggestions:

  1. Look at the error message in the little circle I (I) popup thingie.
  2. Debug the macro to see if it's actually getting called.
  3. Show the exact contents of the cell to us.
  4. Post the exact spreadsheet so we can do 1) or 2) for you.
 
mikeblas said:
Here are some suggestions:

  1. Look at the error message in the little circle I (I) popup thingie.
  2. Debug the macro to see if it's actually getting called.
  3. Show the exact contents of the cell to us.
  4. Post the exact spreadsheet so we can do 1) or 2) for you.
Click to expand...
1) it says "Invalid Name Error"
2) When I do Debug>Compile VBAProject, it flashes through and doesn't pop up any errors.
3) I put N82E16828112035 in cell A1, and =GetEggPrice(A1) in another cell. I double checked for things like spelling errors, etc.

I really don't know whats going on, i posted my spreadsheet at http://www.seektheeout.com/test.xls, you can try looking at it there. Thanks!!!
 
I can't open your file. I get an error that says "The document is corrupt and cannot be opened. To try and repair it, use the Open and Repair command in the Open dialog (File menu) and select Extract Data when prompted."

I then get an error that the damage was too extensive. I don't see any macros in the sheet when it finally opens, but I can't figure out if that's because you didn't add the macro or the macro fell out of the sheet because of the corruption.

Correctly executing Step #2 would mean placing a breakpoint someplace in the GetEggPrice macro near the beginning, then causing the spreadsheet to reevaluate the cell you've got the call to GetEggPrice() in. Then, single-step through the macro to find out where it fails.

Code: 
Microsoft Office Excel File Repair Log

Errors were detected in file 'C:\test.xls'
The following is a list of repairs:

Damage to the file was so extensive that repairs were not possible. Excel attempted to recover your formulas and values, but some data may have been lost or corrupted.
 
mikeblas, I did make one change since I posted that code...... I changed:

Code: 
If nLocation = -1 Then
        GetEggShip = "Not Found"

to:
Code: 
If nLocation = 0 Then
        GetEggShip = "Not Found"

The way I have things set up, I just update these prices every week or so.... and sometimes Newegg has removed the item.... when the item number didn't exist, the code would give some odd answers..... figured out that it returned 0 for every url it didn't know what to do with (since Newegg redirects you to a search page)....

Thanks again for the help/nudge/etc


Keep on Folding!! For the [H]orde!!

 
Dagatech:
Try this ( from Excel Help of all places ;) ):

1. Open a new workbook
2. Click Tools | Macro -> Visual Basic Editor
3. In the VBE, click Insert | Module
4. In the new window, type the code (the GetEggPrice function)
5. Click File | Close and Return to Microsoft Excel
6. Click on a cell and use the function as you normally would.
 
Sorry; I'm a C/C++ guy, so I always forget VB is going to be 1-based instead of 0-based.
 
mikeblas said:
Sorry; I'm a C/C++ guy
Click to expand...

I'm a C guy also, but no real formal training.... I'm well past "Hello World", but far from what can be done with it.... Hey, I'm a civil engineer, there are only a few cases where you'd actually NEED to write some code..... VB is like Spanish to me, I understand some of it, but when I read it, it might as well look like: "ASDakwonaizgbn wooiuanagk woiwahagnlakm vamzozoizvlwkmajh"......

As for the above code, if you don't understand the syntax, you tend to just putz around with it and pray it works... "crap, that broke it.... what'd I break???" "how about this?....... Nope, still broken" ;)


Keep on Folding!! For the [H]orde!!

 
mikeblas said:
Now that's an interesting idea.

How do sites like Froogle and PriceWatch get their pricing information? Are they scraping it from the site and parsing the HMTL and trying to find the pricing information?

Or are they going to newegg.com and querying prices and inventory from a web service that gives easy-to-parse, consistent XML results?

NewEgg doesn't appear to bet that hard to parse. If you want a price for item N82E16824187064, you go to GET the URL http://www.newegg.com/Product/Product.asp?Item=N82E16824187064

You'll find it has a convenient span around the price, so parsing is pretty easy:
Code: 
<span class="showPrice"><h3>$1,449.99</h3></span>

Now, doing that in Excel is a little harder. I don't know of a VBScript object that will let me do a web request...
Click to expand...

This may not be related to the solution, but since someone asked the question....
I can't speak for PriceWatch, but as for Froogle:

You have to upload a tab-delimited text file to Google in order to have your products appear on the site. Uploading is free, after you register for a Google account (also free). I know because I had to develop a solution in order to convert from a catalog table on the database to a .txt tab delimited file.
 
Javi said:
I can't speak for PriceWatch, but as for Froogle:
Click to expand...
Thanks! That's interesting; I thought it would have been a pull model, but it's a push.
 
mikeblas said:
Now that's an interesting idea.

How do sites like Froogle and PriceWatch get their pricing information? Are they scraping it from the site and parsing the HMTL and trying to find the pricing information?

Or are they going to newegg.com and querying prices and inventory from a web service that gives easy-to-parse, consistent XML results?

NewEgg doesn't appear to bet that hard to parse. If you want a price for item N82E16824187064, you go to GET the URL http://www.newegg.com/Product/Product.asp?Item=N82E16824187064

You'll find it has a convenient span around the price, so parsing is pretty easy:
Code: 
<span class="showPrice"><h3>$1,449.99</h3></span>

Now, doing that in Excel is a little harder. I don't know of a VBScript object that will let me do a web request...
Click to expand...
just messing with froogle the other day,
seems you have to submit your product image, keyword/description and price. then they verify the price.
so i dont know how that would reflect your actual shopping cart where the price is always changing, i was told you have to manually resubmit the product with new price
 
Is their a way to have this setup for zipzoomfly.com also? I was wanting something along those same lines and that the spreadsheet would output to a column which one is cheaper at that time to buy. The current one for newegg shipping and price works great.
 
I've been working on trying to get a smiliar thing to work for ZZF.... they're not as nice as Newegg and their "showprice".... I'm not too great with VB, so feel free to throw out any suggestions......... here's what I have so far:


Code: 
Function GetZZFPrice(part)
    
    ' get the HTTP client
    Dim http As Object
    Set http = CreateObject("Microsoft.XMLHTTP")
    
    ' ask for our page given the Product Code
    http.Open "GET", "http://www.zipzoomfly.com/jsp/ProductDetail.jsp?ProductCode=" + part, False
    http.send

    ' Look for the Price
    strtarget = Chr$(62) + "$"
    
    ' find it?
    nlocation = InStr(http.ResponseText, strtarget)
    If nlocation = 0 Then
        GetZZFPrice = "Not Found"
       
    Else
        ' well get an arbitrarily sized substring
        strpricepart = Mid(http.ResponseText, nlocation - 1 + Len(strtarget), 50)
        nStart = InStr(strpricepart, "$")
        nEnd = InStr(nStart, strpricepart, "<")
        
        ' return that substring, finally
        GetZZFPrice = Mid(strpricepart, nStart + 1, nEnd - nStart - 1) * 1
    End If
End Function

The only problem I ran into was that if the product doesn't exist any more, there is a redirect to the home page and it #value's out....

http://www.zipzoomfly.com/jsp/ProductDetail.jsp?ProductCode=23423 Here is a madeup product code as an example..... Any ideas on how to handle the redirect??

edit--Well... I take that back... 2 problems.... the other problem is that it also #value's out if there are more than 5 digits in the price..... so anything over $1000.00 is a no go........ any advice on how to tweak the code to make it work for all?


Keep on Folding!! For the [H]orde!!

 
A BIG THANK YOU TO ALL OF YOU!!!

I used to check prices every day of some things, this makes life so much better!

Once again THANK YOU!!!!!!! :)
 
Just an update on the code... I figured out one problem with the ZZF prices, and the code returning a #value..... Some of the ZZF product numbers are true integers ("83904") while some have other characters ("82039-R").... For whatever reason, the http.Open wouldn't work with "83904" as an integer.... so you had to define "part" as a string in the beginning of the function..... Once I did that it worked for all the Item numbers I've tried so far... But, I still haven't solved the issue of the redirects.... Anyone know how to deal with that?

Code: 
Function GetZZFPrice(part As String)
    
    ' get the HTTP client
    Dim http As Object
    Set http = CreateObject("Microsoft.XMLHTTP")
    
    ' ask for our page given the Product Code
    http.Open "GET", "http://www.zipzoomfly.com/jsp/ProductDetail.jsp?ProductCode=" + part, False
    http.send

    ' Look for the Price
    strtarget = Chr$(62) + "$"
    
    ' find it?
    nlocation = InStr(http.ResponseText, strtarget)
    If nlocation = 0 Then
        GetZZFPrice = "-"
       
    Else
        ' well get an arbitrarily sized substring
        strpricepart = Mid(http.ResponseText, nlocation - 1 + Len(strtarget), 50)
        nStart = InStr(strpricepart, "$")
        nEnd = InStr(nStart, strpricepart, "<")
        
        ' return that substring, finally
        GetZZFPrice = Mid(strpricepart, nStart + 1, nEnd - nStart - 1) * 1
    End If
End Function


Keep on Folding!! For the [H]orde!!

 
You must log in or register to reply here.
Back
Top