Get External Data from Web into Excel

The web can be a great source for different kinds of interesting data and therefore let’s learn how to get external data from the web into MS Excel.

Click on the Data tab
From the command group ‘Get External Data’ we’ll click on ‘From Web’
We’ll go the website http://www.google.com/finance
Here we’ll navigate to the area where you can see a black arrow with yellow background next to ‘Dow Jones’ . The arrow indicates that we can click on it and then perform an import of data
Click the arrow – Dow Jones S & P 500 and NASDAQ are selected
Click on the Import button below
A new window opens with the the title ‘Import Data’. Below that you can read – where do you want to put the data? The option Existing Worksheet is selected and in the text below is written =$A$1
Since we have data in cell A1 we select cell A3
We can click on properties and set some interesting parameters like the time to refresh the data. We could refresh it every ten minutes
We click on OK
Data is imported into the Excel worksheet starting at cell A3 as specified by us

You can go to other interesting websites like payscale.com and import data that you may find interesting for data analysis. Not all websites allow import of data.

View the Excel training video below:


Further reading:
Get external data from a Web page

2 thoughts on “Get External Data from Web into Excel

  1. Henri H Barlatier

    I tried your video and what I am trying to do is append content from multiple pages to one spreadsheet and it keeps overwriting the same data from page one even though the iteration increments ok. I was looking for your spreadsheet example to compare. I will paste i my code.

    Sub scrapeDutchessCo()
    Sheets(“Sheet6”).Select
    Range(“a1”).Activate
    urlString = ActiveCell.Value
    For x = 1 To 6775
    Sheets(“Sheet7”).Select
    evar = Range(“a1048576”).End(xlUp).Row
    With ActiveSheet.QueryTables.Add(Connection:= _
    urlString, Destination:=Range(“A” + CStr(evar)))
    .Name = urlString
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertEntireRows ‘xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .WebSelectionType = xlEntirePage
    .WebFormatting = xlWebFormattingNone
    .WebPreFormattedTextToColumns = True
    .WebConsecutiveDelimitersAsOne = True
    .WebSingleBlockTextImport = False
    .WebDisableDateRecognition = False
    .WebDisableRedirections = False
    .Refresh BackgroundQuery:=False
    End With
    ‘ActiveWindow.SmallScroll Down:=33
    ‘ActiveCell.SpecialCells(xlLastCell).Select
    Application.StatusBar = CStr(x + 1) + ” ” + CStr(ActiveCell.Row)
    Sheets(“Sheet6”).Select
    ActiveCell.Offset(1, 0).Select
    urlString = ActiveCell.Value

    Next x
    End Sub

    Reply

Leave a Reply

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