Automate Gathering Web Data…

Topic

New Reply

At work, we have many products that change price frequently but not regularly.  We try to be slightly cheaper than some of the ‘big boys’ in our industry but it is much too time consuming to look up the products on a daily basis.  I want to create a spreadsheet that will look up each product on the competitors web site and let me know if the price has gone up, down, or remained the same.  I believe I can create the macro to compare the current price to the previous price and indicate any change and make a recommendation as to the price change I should make on our web site.  I’m thinking I need a for next VBA loop for a list of products – one on each row.  The macro would need to go to their site, search for the product (typically each product has a page), and then somehow scrape the price.  It is easy to do that when I pull up the page but I need the process to be automated so I could just run the macro each morning. I’ve tried to use the Data…From Web tool in Excel but it looks in tables and the information I’m looking for is not in a table.  I believe I can write the VBA code if I can figure out how to get the info.  Any ideas?

Reply | Quote

Replies

[woody]

Posting for Rick Corbett, who’s having trouble posting…

 

Have a look at using AutoHotkey for web automation.

One chap – Joe Glines aka the-Automator – in particular springs to mind… ‘cos he specialises in this type of automation of web scraping.

Check out his website, particularly the section on web scraping…

Hope this helps…

• This reply was modified 4 years, 9 months ago by woody.

Reply | Quote

Many thanks, Woody, for your intervention.

I didn’t get the chance to mention earlier but AutoHotkey understands/handles COM directly so can pump data into Excel spreadsheets if that’s what you need… although it sounds like you just need an event timer that checks online prices at regular intervals… which doesn’t need a spreadsheet (or log) unless you want to record a history of such changes.

Hope this helps…

Reply | Quote

I solved my problem by doing a little research on the web.  Not all that complicated once you know what you are doing. Here is my code (edited).

Sub getBlowoutPrices()
Dim browser As New InternetExplorer
Set browser = CreateObject(“InternetExplorer.Application”)
browser.Visible = False
‘loop through the products in the spreadsheet
Dim prods As Range, prod As Range
Set prods = Sheets(“Sheet1”).Range(“A2:A52″)
For Each prod In prods
Dim searchProd As String
‘Prepare the search URL
searchProd = LCase(prod.Value)
searchProd = Replace(prod.Value, ” “, “-“)
browser.navigate “https://www.xxxxx.com/” & searchProd & “.html”
‘code to wait until the browswer finds the page.
Do
DoEvents
Loop Until browser.readyState = READYSTATE_COMPLETE
‘get the value from the web page and put it in column F
Set Doc = browser.document
Set priceclass = Doc.getElementsByClassName(“price”)
prod.Offset(0, 5).Value = priceclass.Item(0).innerText
Next
browser.Quit
Set browser = Nothing
End Sub

Reply | Quote