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
Need to know how to select a radio button on a webpage with two radio options as TA and NA.
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