Scrape Data Automatically from Websites into Excel

You can scrape, pull or get data from websites into Excel by performing a few simple steps.
1. record a macro to find out how one or many tables or data can be scraped from the website
2. Study the code carefully
3. Create an Excel sheet containing the links that get you the data from the appropriate web pages
4. Automate the process using a loop that creates a) New worksheets b) Automatically changes the link to the web pages that have the required data

Study the Excel VBA code and watch the training video below to see how the automation of scraping of websites is implemented:

Sub adds()
For x = 1 To 5
Worksheets(“states”).Select
Worksheets(“states”).Activate
mystr = “URL;http://quickfacts.census.gov/qfd/states/01000.html”
mystr = Cells(x, 1)
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = x
With ActiveSheet.QueryTables.Add(Connection:= mystr, Destination:=Range( “$A$2”))
‘CommandType = 0
.Name = “01000_1”
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = “3,4,5” ‘—> Note: many tables have been selected for import from the website
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
Next x
End Sub


Watch the video on YouTube

Latest developments: The website has been totally redesigned. Now to extract the data for Alabama from tables numbers 2, 3, 4 and 5 available on the first page, we would use the following VBA code:

sub get_data_from_web()

Activity Wrist Band

Activity Wrist Band

With ActiveSheet.QueryTables.Add(Connection:= _
“URL;http://quickfacts.census.gov/qfd/states/01000.html”, Destination:=Range( _
“$A$2”))
.CommandType = 0
.Name = “01000”
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = “2,3,4,5”
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With

End sub

14 thoughts on “Scrape Data Automatically from Websites into Excel

  1. Spyros

    You are the Kindest Man I have ever met.

    Thank you.

    Thank you for your website and what you teach. For the Information you share so openly. You are amazing.

    Shakri … Namaste … Om mani padme hum … Seriously, THANK YOU SIR YOU HUMBLE HUMBLE GOOD MAN and GENIUS … Friend. Oh My God. I will look at your site more often.

    OMG! Euxaristw. Euharistw Para Poli

    Thank you

    Oh My God … Thank you … I Love You

    Reply
    1. baufinanzierung zinsen

      Make sure you response both concerns entirely:. . Is there a easiest method to ensure that basically copyright laws some thing, that it’s my own without having need to copyright laws each one report or perhaps blog professionally?. . As well: . . I’m a poet i choose to copyright my personal perform without having be forced to pay “per poem” * how can I make it happen, and also be able to get issues copyrighted on a regular basis?.

      Reply
  2. Adam G.

    Hi!

    I’ve learned some stuff from the videos you have in youtube. They are great! and no one has been making those like you do. – not that close as far as the technicality in automation can go…
    i have a small problem that might challenge you…
    The “Get external data from web” option available in excel has helped so much, but the website i need the data from requires a credential and a password. I have my own Username and Password, but I’m totally not that smart enough to figure out a way around that. I had to enter my credentials everytime and it becomes cumbersome if i had to redo this about 30 times to complete a report… would you be able to demonstrate a video that can pull a data from web that requires a password? I am sure more people out there has the same problem, but literally no one has demonstrated a way… I’ve searched the web and found none… Help… please…

    Reply
  3. Javed Ashraf Khan

    Sir I am applying nested if function on a column to extract FSc Grades(A+,A1,A,B,C etc) ,many of the cells in the column are returning the results as desired but randomly in scattered cells are not responding to the excel function rather they appear blank or with the FALSE error,
    Kindly guide me What would be the possible reasons that I should take into consideration.
    Thanks and Regards
    Javed from Umerzai Pakistan

    Reply
  4. Martin

    Hello Dr, Dinesh Kumar Takyar,

    thanks for the detailed description. This worked perfectly fine for me.

    I have a question regarding the “get external data from web” process: this works on one computer, but when I try to do the same on one of my other machines it doesn’t work anymore – no data is being extracted. Do you have any idea? It doesn’t seem to be firewall-related…

    Best regards,
    Martin, Cologne, Germany

    Reply
  5. Greg

    Hi Dinesh

    You have a wonderful website and fantastic videos – thank you.

    I’m interested in automatically scraping multiple balance sheet data from a Yahoo website using a macro.

    Here is the website in question:

    https://uk.finance.yahoo.com/q/bs?s=acm.l&ql=1

    As you can see at the top, they have a tab for both Quarterly and Annual data and I’d like to get data from both forms for each share.

    Creating multiple sheets would be fine, however, I’d probably want to scrape the data from about 500 different shares (!) so simply putting the data into rows and columns with each share or ticker symbol listed on the left would seem to be the best idea!

    Would something like that be possible? I’ve managed to pull data to a spreadsheet as per your video, but VBA is something I’m unfamiliar with though I am trying to understand it!

    Hope you can help or advise!

    Many thanks
    Greg

    Reply
  6. Shahnawaz Shaikh

    Hi,
    Thank You very much to creating very helpful website and helpful tips, i have only one problem the WEB Query doesn’t work on some website, can u tell me what’s the reason and how should i tackle it.

    lots of thank in advance

    Regards
    Shahnawaz Shaikh

    Reply
  7. Sanjeev

    Hi Sir,

    Is it possible to save a webpage and send that webpage as outlook email attachment using VBA?

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *