Best way to web scrape using excel as database

[DianaBanana]

Does anyone have a recommended way to scrape data off the web and get it to populate into an excel document? For example, I am going through a hotel site and I'd like to scrape all the different hotel room rates for that particular hotel location and store it in an excel document. I'd like to be able to re-run this (macro or code) weekly and see the different rates? I'm manually doing it and putting it into excel but there must be a better way.

 

[AngelJ]

If you know HTML and VBA, one way to do it is with the Microsoft Internet Controls library. I'm sure you can find some samples by googling 'screen scraping with vba' or something like that.

I've never been a huge fan of screen scraping, so I'd look into doing this with a travel site's API first: Travel/Hotel API's? - Stack Overflow

 

[DianaBanana]

If you know HTML and VBA, one way to do it is with the Microsoft Internet Controls library. I'm sure you can find some samples by googling 'screen scraping with vba' or something like that.

I've never been a huge fan of screen scraping, so I'd look into doing this with a travel site's API first: Travel/Hotel API's? - Stack Overflow

Is there someone you can recommend who could help me start writing this code? Show me how to use the Microsoft internet controls library? If I show you what I'm doing manually - can you help me start the code and I can google the rest?

 

[AngelJ]

Is there someone you can recommend who could help me start writing this code?

Sorry if my post didn't seem very helpful, but I had to make assumptions about your level of VBA experience. I'm sure there are several people on these forums who can help you.

Show me how to use the Microsoft internet controls library? If I show you what I'm doing manually - can you help me start the code and I can google the rest?

Here is a sample that uses that library to load a web page. If you give me more specific information about what you're doing I can definitely help.
In order to run this you must set a reference to 'Microsoft Internet Controls' by checking the box next to it in the References dialog (Tools > References...):

Sub DianaBanana()
    Dim ie As InternetExplorer
    
    Set ie = New InternetExplorer
    
    ie.Navigate "http://google.com"
    
    While ie.readyState <> READYSTATE_COMPLETE
        DoEvents
    Wend
    
    MsgBox "Finished loading '" & ie.Document.Title & "'!", vbInformation


    ie.Visible = True
End Sub

 

[DianaBanana]

Sorry if my post didn't seem very helpful, but I had to make assumptions about your level of VBA experience. I'm sure there are several people on these forums who can help you.


Here is a sample that uses that library to load a web page. If you give me more specific information about what you're doing I can definitely help.
In order to run this you must set a reference to 'Microsoft Internet Controls' by checking the box next to it in the References dialog (Tools > References...):

Sub DianaBanana()
    Dim ie As InternetExplorer
    
    Set ie = New InternetExplorer
    
    ie.Navigate "http://google.com"
    
    While ie.readyState <> READYSTATE_COMPLETE
        DoEvents
    Wend
    
    MsgBox "Finished loading '" & ie.Document.Title & "'!", vbInformation


    ie.Visible = True
End Sub

Thank you, yes, I am a super beginner with any code but I will hopefully learn quickly. Is there any way for me to send you a file? If not, I will try to explain this. It's just hard without just showing it. I started doing it manually, so I have it in a file. Thank you.

 

[DianaBanana]

Here is my attempt to explain what I want to do without showing the file, if you can help me that would be so much appreciated. First I am scraping off of this site: 1) La Quinta Inns & Suites - Hotel Directory, Hotel Locations by State 2) I am going into every single entry and creating tabs in excel for those specific states. 3) I started with Texas, for example. 4) In excel on the tab I copy and pasted in every hotel. 5) On the column headers on top I have the different room types (below). I have been inputting by hand the different prices for each room type. 6) I also have columns going across the top for the address, number of rooms in hotel, and number of suites. Can you help me automate this process such that any day I choose to run this, it creates a new excel workbook with the different states on each tab (or if there's a more efficient way, I'm open to hear that too). (For the next big scraping related project and I'm mentioning it because it's the reason the address is in the column -- I want to look at competitors sites and pull up their information too - for nearby hotels to these specific locations).

(Standard) Two Queen Bed w/Microwave & Fridge

ADA Accessible Two Queen Beds w/Microwave & Fridge

Two Queen Beds

Two Double Beds

ADA accessible two double beds

(Standard) Two Full Beds

ADA accessible w/2 full beds

Two Full Beds w/pool view

Two Full Beds w/Microwave & Fridge

One King Bed w/Microwave & Fridge

Executive One King Bed w/Microwave & Fridge

ADA Accessible One King Bed w/Microwave & Fridge & Sofa

One King Bed

ADA Accessible One King Bed

One King Pool View w/Microwave & Fridge

One King Bed w/Recliner

One King Bed w/Sofa

Two Queen Executive (Suite) w/Sofa Sleeper

One King Executive (Suite) w/Sofa Sleeper

One King Suite w/Sofa Sleeper

Accessible One King Executive Suite w/Sofa Sleeper

One King Suite w/microwave & fridge

One King Bed Jetted Bath Suite w/ Microwave and fridge

Executive King Pillowtop Bed/ Sofa sleeper 32 inch HGTV

Apartment One King Suite w/Private Patio

Two Room One King Suite w/Sofa Sleeper

ADA Accessible Two Room One King Suite w/Sofa Sleeper

Two Room Two King (Beds) Suite w/Microwave & Fridge

Two Room Two Queen Suite w/Sofa Sleeper

Two Room Two Queen Family Suite w/Sofa Sleeper

 

[Norie]

The best way to do this sort of thing is almost always dependent on the site you want to get the data from.

 

[DianaBanana]

So the first hotel site I want to scrape and do most of these steps from is: La Quinta Inns & Suites - Hotel Directory, Hotel Locations by State Then when you click on an individual hotel like the first one: La Quinta Inn & Suites Birmingham Homewood, Hotel, AL - Alabama Hotels On that page you see the address, and under hotel features the rooms (129) and suites (8) which I want to capture. Then I click on left under "your search" and "check rates" (I use the current settings - to check in today, check out tomorrow, one room, and see the rates. Then I fill in those rates for all the room types per the columns selected above. Another project after this is completed (but to keep in mind for room code) is i'd like to try to figure out a way to see occupancy - if any room is sold out or only certain rooms available and track that.. maybe there's "hidden" info on the page where it would be easy to capture that..

 

[DianaBanana]

So the first hotel site I want to scrape and do most of these steps from is: La Quinta Inns & Suites - Hotel Directory, Hotel Locations by State Then when you click on an individual hotel like the first one: La Quinta Inn & Suites Birmingham Homewood, Hotel, AL - Alabama Hotels On that page you see the address, and under hotel features the rooms (129) and suites (8) which I want to capture. Then I click on left under "your search" and "check rates" (I use the current settings - to check in today, check out tomorrow, one room, and see the rates. Then I fill in those rates for all the room types per the columns selected above. Another project after this is completed (but to keep in mind for room code) is i'd like to try to figure out a way to see occupancy - if any room is sold out or only certain rooms available and track that.. maybe there's "hidden" info on the page where it would be easy to capture that..

Hi - is there anything you can provide to start me off working on this in the AM? choose a list of locations, for example, and to start with, Book Early & Save at La Quinta Inns & Suites Hotels
2) follow each location, follow each hotel in the result list
As I see it, it uses in-page Javascript to display this list -- so that seems harder
For each hotel, click the "Check Rooms and Rates" button
scrape the list of prices for different room categories, and probably increment the date and do the same, until an upper date limit (to be defined)
Apparently, that's not Javascript so it should be simpler. Any help to start?

 

[Norie]

Only one nights stay or should the room rates for various no of nights stay be checked out?