# Best way to web scrape using excel as database



## DianaBanana (Mar 18, 2014)

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 (Mar 18, 2014)

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 (Mar 18, 2014)

AngelJ said:


> 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 (Mar 18, 2014)

DianaBanana said:


> 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.



DianaBanana said:


> 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 (Mar 18, 2014)

AngelJ said:


> 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.
> ...



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 (Mar 18, 2014)

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 & FridgeADA Accessible Two Queen Beds w/Microwave & FridgeTwo Queen BedsTwo Double BedsADA accessible two double beds(Standard) Two Full BedsADA accessible w/2 full bedsTwo Full Beds w/pool viewTwo Full Beds w/Microwave & FridgeOne King Bed w/Microwave & FridgeExecutive One King Bed w/Microwave & FridgeADA Accessible One King Bed w/Microwave & Fridge & SofaOne King BedADA Accessible One King Bed One King Pool View w/Microwave & FridgeOne King Bed w/ReclinerOne King Bed w/SofaTwo Queen Executive (Suite) w/Sofa SleeperOne King Executive (Suite) w/Sofa SleeperOne King Suite w/Sofa SleeperAccessible One King Executive Suite w/Sofa SleeperOne King Suite w/microwave & fridgeOne King Bed Jetted Bath Suite w/ Microwave and fridgeExecutive King Pillowtop Bed/ Sofa sleeper 32 inch HGTVApartment One King Suite w/Private PatioTwo Room One King Suite w/Sofa SleeperADA Accessible Two Room One King Suite w/Sofa SleeperTwo Room Two King (Beds) Suite w/Microwave & FridgeTwo Room Two Queen Suite w/Sofa SleeperTwo Room Two Queen Family Suite w/Sofa Sleeper


----------



## Norie (Mar 18, 2014)

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


----------



## DianaBanana (Mar 18, 2014)

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 (Mar 19, 2014)

DianaBanana said:


> 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 (Mar 19, 2014)

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


----------



## DianaBanana (Mar 18, 2014)

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.


----------



## DianaBanana (Mar 19, 2014)

only one night stay for the moment (There's just so much I want to do that I think it would be too hard to also look at multiple nights)


----------



## DianaBanana (Mar 19, 2014)

Norie said:


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


Are you still working on this? I'm trying to get started and if I can be of any help too... Thank you so much.


----------



## DianaBanana (Mar 20, 2014)

DianaBanana said:


> Are you still working on this? I'm trying to get started and if I can be of any help too... Thank you so much.


Hi all, is anyone still working on trying to figure this out? Or gave up? Just let me know. Thank you so much.


----------



## AngelJ (Mar 20, 2014)

I wasn't working on this, but it sounded like Norie might have been.

If you can post the work you've done so far and the troubles you've been having, then I'd be happy to help.


----------



## DianaBanana (Mar 20, 2014)

Everything I've done is manual, I haven't been able to automate it. I have been reading up online and I'm just too new to this. Is there a way for you to start the code and I keep working on it? Or a way to "record" doing what I have been doing manually so I can go in and see the code? Can you help me start it?


----------



## AngelJ (Mar 20, 2014)

There's no easy way to do this that I'm aware of.  I think you might have just underestimated what's involved.  This isn't the best project to start learning VBA, but here's a sample that may get you started.


```
Option Explicit


' Requires references to: Microsoft Internet Controls
'                         Microsoft HTML Object Library
'                         Microsoft VBScript Regular Expressions #
Sub DianaBanana()
    Dim ie As InternetExplorer
    Dim ie2 As InternetExplorer
    Dim lnk As HTMLLinkElement
    Dim mch As Match
    Dim regex As RegExp
    
    Set ie = New InternetExplorer
    Set ie2 = New InternetExplorer
    Set regex = New RegExp
    regex.Global = True
    
    ie.Navigate "http://www.lq.com/lq/about/ourhotels/hotel_listings/"
    
    While ie.readyState <> READYSTATE_COMPLETE
        DoEvents
    Wend
    
    For Each lnk In ie.Document.Links
        If lnk.innerText Like "La Quinta*" Then
            ie2.Navigate lnk
            While ie2.readyState <> READYSTATE_COMPLETE
                DoEvents
            Wend
            regex.Pattern = "(Floors|Rooms): \d+"
            For Each mch In regex.Execute(ie2.Document.DocumentElement.innerHTML)
                If MsgBox(ie2.Document.Title & vbCr & mch & vbCr & vbCr & "Continue?", vbYesNo) <> vbYes Then
                    GoTo Quit
                End If
            Next
        End If
    Next
    
Quit:
    ie.Quit
    ie2.Quit
End Sub
```


----------



## Norie (Mar 20, 2014)

I was going to have a look but didn't find the time, especially as I realised how much was involved.

Even on the first page we are dealing with 100s of locations.

Each of those will lead to multiple hotels and then we need to get data for each of those hotels.

That would involve getting the data for multiple dates/room categories.


----------



## DianaBanana (Mar 20, 2014)

AngelJ said:


> There's no easy way to do this that I'm aware of.  I think you might have just underestimated what's involved.  This isn't the best project to start learning VBA, but here's a sample that may get you started.
> 
> 
> ```
> ...


  I put this in my workbook - in a new macro - and I'm getting an error message. "Compile error: User defined type not defined" -- this is highlighted in yellow: Dim ie As InternetExplorer    .  How do I fix this?


----------



## DianaBanana (Mar 20, 2014)

Norie said:


> I was going to have a look but didn't find the time, especially as I realised how much was involved.
> 
> Even on the first page we are dealing with 100s of locations.
> 
> ...


   Norie, If I simplified the project and just chose the "best available price/lowest price" which would involve just clicking on the one page into each hotel - does it seem more doable then? (Without having to get all the pricing by room type?   Would I be able to record a macro for each hotel (800 times) and then put them all into one macro? Is that possible?


----------



## AngelJ (Mar 20, 2014)

DianaBanana said:


> I put this in my workbook - in a new macro - and I'm getting an error message. "Compile error: User defined type not defined" -- this is highlighted in yellow: Dim ie As InternetExplorer    .  How do I fix this?



Follow these instructions from my earlier post to set a reference to each of the libraries listed at the top of the code:


AngelJ said:


> 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...):


----------



## DianaBanana (Mar 18, 2014)

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.


----------



## DianaBanana (Mar 20, 2014)

AngelJ said:


> Follow these instructions from my earlier post to set a reference to each of the libraries listed at the top of the code:


Ok, I'm sorry I missed that, I enabled that. Now, I'm getting an error message on the next line - Dim lnk As HTMLLinkElement - same error message as before


----------



## AngelJ (Mar 20, 2014)

DianaBanana said:


> Ok, I'm sorry I missed that, I enabled that. Now, I'm getting an error message on the next line - Dim lnk As HTMLLinkElement - same error message as before


lol, yup, you have to do the same thing for the other two libraries: Microsoft HTML Object Library, and Microsoft VBScript Regular Expressions #.  Just choose the regular expressions one with the highest number.  Mine said 5.5.


----------



## DianaBanana (Mar 20, 2014)

OK I did that now.  It started running, but for some reason between each hotel it sent me a form with an "OK" button message - like La QUinta Inn Hotel in AZ with 2 floors Con't?   
Is there a way for it to run w/out these forms?  And I could not see the output because I cancelled..is there a way to see output after say 5 hotels (just to see what it's doing)?


----------



## AngelJ (Mar 20, 2014)

That's correct.  There is no output besides those message boxes.  The purpose of that sample was to show you how to navigate through the pages and retrieve information from them.  The rest is up to you or anyone nice enough to take on the entire project for you.


----------



## DianaBanana (Mar 20, 2014)

AngelJ said:


> That's correct.  There is no output besides those message boxes.  The purpose of that sample was to show you how to navigate through the pages and retrieve information from them.  The rest is up to you or anyone nice enough to take on the entire project for you.


 Oh man - so that's not you?    Ok, I am going to try to work on this but can you help me with some questions, which of these words indicate where exactly it's looking? Is it possible for me to "record" a macro from excel into html for one hotel and then just repeat it 800+ times?   -  Wend
            regex.Pattern = "(Floors|Rooms): \d+"
            For Each mch In regex.Execute(ie2.Document.DocumentElement.innerHTML)
                If MsgBox(ie2.Document.Title & vbCr & mch & vbCr & vbCr & "Continue?", vbYesNo) <> vbYes Then
                    GoTo Quit


----------



## AngelJ (Mar 21, 2014)

DianaBanana said:


> Oh man - so that's not you?


Nope, sorry.  I actually thought the code might scare you into doing this manually until you learned VBA, which wouldn't be such a bad idea as this is not a great project to start VBA on.  But, I'll never discourage someone who wants to learn.  In fact, I think your desire to get this project done will only drive your learning.

I've tried teaching people VBA before and one common problem was that they didn't have a project like this to challenge them.



DianaBanana said:


> ...which of these words indicate where exactly it's looking?


This pattern tells it what to look for:

```
regex.Pattern = "(Floors|Rooms): \d+"
```
Look here for a visual demonstration of how that works: Regex Tester (Permalink)
And research Regular Expressions for more info.  I used regular expressions here because the elements that contained the info didn't have id's.  When they do have id's you can use this method: getElementById method (Internet Explorer)



DianaBanana said:


> Is it possible for me to "record" a macro from excel into html for one hotel and then just repeat it 800+ times?


No, not that easy, unfortunately.


----------



## DianaBanana (Mar 24, 2014)

AngelJ said:


> Nope, sorry.  I actually thought the code might scare you into doing this manually until you learned VBA, which wouldn't be such a bad idea as this is not a great project to start VBA on.  But, I'll never discourage someone who wants to learn.  In fact, I think your desire to get this project done will only drive your learning.
> 
> I've tried teaching people VBA before and one common problem was that they didn't have a project like this to challenge them.
> 
> ...



Angel, 
1) How does it know to go from the hotel listings to click on each link? Is it the "navigate lnk"?
2) I am simplifying the process. I am just going to pull up the best available rate that is on the first page along with the room #.  Where it says on the page "enjoy rates from". I cannot seem to replicate the regex test. 
3) Once it can identify those two items (number of rooms and lowest rate), what tells it to bring it back into excel?


----------



## DianaBanana (Mar 25, 2014)

JasonAlbert said:


> Well If you know HTML and VBA, one way to do it is with the Microsoft company On-line collection. I'm sure you will discover some examples by searching on the 'screen' or something like that.



I just started learning vba so when I google for codes  not following it that well...


----------



## AngelJ (Mar 25, 2014)

DianaBanana said:


> Angel,
> 1) How does it know to go from the hotel listings to click on each link? Is it the "navigate lnk"?


Yes, the Navigate method follows the links.  This code uses a For Each loop to iterate through the links.  Look into VBA loops for a better understanding of that.



DianaBanana said:


> 2) I am simplifying the process. I am just going to pull up the best available rate that is on the first page along with the room #.  Where it says on the page "enjoy rates from". I cannot seem to replicate the regex test.


The pattern for this gets much more complex.  This one should work: (remember you're testing against the actual source HTML, not what you see in your web browser)

```
Enjoy Rates From:(?:.|\n)+\$((?:\d+,+)*\d+\.\d+)
```
You can use the SubMatches property to extract just the dollar amount like so:

```
regex.Pattern = "Enjoy Rates From:(?:.|\n)+\$((?:\d+,+)*\d+\.\d+)"
            For Each mch In regex.Execute(ie2.Document.DocumentElement.innerHTML)
                If MsgBox(ie2.Document.Title & vbCr & mch.SubMatches(0) & vbCr & vbCr & "Continue?", vbYesNo) <> vbYes Then
```



DianaBanana said:


> 3) Once it can identify those two items (number of rooms and lowest rate), what tells it to bring it back into excel?


Currently nothing does.  Getting the data onto a spreadsheet should be one of the easier parts of this project though.  Look at some samples and give it a shot.  Then, report back here and we'll help you out.



DianaBanana said:


> I just started learning vba so when I google for codes  not following it that well...


That post, which looked like a mangled version of my first post was made by a spam bot.  Forget about it, lol.


----------



## DianaBanana (Mar 27, 2014)

AngelJ said:


> Yes, the Navigate method follows the links.  This code uses a For Each loop to iterate through the links.  Look into VBA loops for a better understanding of that.
> 
> 
> The pattern for this gets much more complex.  This one should work: (remember you're testing against the actual source HTML, not what you see in your web browser)
> ...


  im trying to run the macro and it says the macros in this workbook are disabled?


----------

