Share

Excel VBA, Retrieving Data From a Website Using a Query Table

In this article I will explain how to retrieve data from a website using a query table and VBA.

Previously in the article Excel Getting Data From the Web I’ve explained how you can use Query Tables to import data from the web into an excel worksheet. In this article it is assumed readers are familiar with using query tables.

Lets say similar to the article Excel Getting Data From the Web we want to get the USD to CAD currency exchange rate using a query table. Only this time we want to get it done using VBA. Basically what we want to do is to connect to the website http://finance.yahoo.com/q?s=usdCAd=x using a query table and retrieve the 4 numbers marked below:
CADUSD
Note: If the layout of the website changes, we may need to update our code.


Step 1, Record Macro:

The first step would be to record the steps used in the article Excel Getting Data From the Web using the macro recorder. You will end up with a code similar to the one below:

Sub Macro1()
'
' Macro1 Macro
'

'
ActiveWorkbook.Worksheets.Add
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://finance.yahoo.com/q?s=usdCAd=x", _
Destination:=Range("$A$1"))
.Name = "q?s=usdCAd=x_1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = 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
End Sub


Understanding the Recorded Macro:

The first line of code creates a new worksheet. This is only necessary if you want the Query Table to appear in a new sheet:

ActiveWorkbook.Worksheets.Add

The next 3 lines of code are very important:

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://finance.yahoo.com/q?s=usdCAd=x", _
Destination:=Range("$A$1"))

Insertion Location:

The highlighted parts below define the location the query table will be inserted. The default code inserts the query table in cell A1 in the active sheet:

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://finance.yahoo.com/q?s=usdCAd=x", _
Destination:=Range("$A$1"))

We could change the code above to insert the query table in a different location. For example the code below will insert the query table in cell B2 of sheet3:

With Sheet3.QueryTables.Add(Connection:= _
"URL;http://finance.yahoo.com/q?s=usdCAd=x", _
Destination:=Sheet3.Range("$B$2"))

We could also use the notation below. Assuming the name of sheet3 is “Sheet3′:

With Sheets("Sheet3").QueryTables.Add(Connection:= _
"URL;http://finance.yahoo.com/q?s=usdCAd=x", _
Destination:=Sheet3.Range(Sheet3.Cells(2, 2), _
Sheet3.Cells(2, 2)))

For more information about the different methods of referring sheets in VBA please see the link below:

Website URL:

The highlighted part below defines the URL of the website to connect to:

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://finance.yahoo.com/q?s=usdCAd=x", _
Destination:=Range("$A$1"))

You can change the highlighted text to any other website, for example the code below will connect to the website http://msdn.microsoft.com/en-US/:

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://msdn.microsoft.com/en-US/", _
Destination:=Range("$A$1"))

Note: If you want to use a string variable to define the website address, you must add the string “URL;” to the start of the string. For example:

Dim strAddress As String
strAddress = "http://msdn.microsoft.com/en-US/"
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;" & strAddress, _


Step 2:

In Step 1 we created a data query which gets data from the website http://finance.yahoo.com/q?s=usdCAd=x. As explained in the article Excel Getting Data From the Web we will end up with a sheet full of data. We need to figure out which cell contains the data we are looking for.

Assume we used the code below:

With Sheet1.QueryTables.Add(Connection:= _
"URL;http://finance.yahoo.com/q?s=usdCAd=x", Destination:=Sheet1.Range("$A$1"))
.Name = "q?s=usdCAd=x_1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = 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

The data we are after (currency exchange rates) will appear in the cells B120 to B123:
CADUSD

USD CAD, Data

We could then use a code similar to that below to get the required values:

Dim dblPrevClose As Double
Dim dblOpen As Double
Dim dblBid As Double
Dim dblAsk As Double

dblPrevClose = Sheet1.Cells(120, 2)
dblOpen = Sheet1.Cells(121, 2)
dblBid = Sheet1.Cells(122, 2)
dblAsk = Sheet1.Cells(123, 2)

Also I have provided a complete tutorial on making a FOREX (currency exchange) sheet in the article Excel VBA, Currency Exchange (Forex) Sheet, (Sample Code and Download)

You can download the file and code used in this article from the link below:

See also:

If you need assistance with your code, or you are looking for a VBA programmer to hire feel free to contact me. Also please visit my website www.software-solutions-online.com

4 thoughts on “Excel VBA, Retrieving Data From a Website Using a Query Table”

  1. AnhSuperStar says:

    Hey, I can’t run this example. Please recheck it.
    Thanks.

  2. Naresh says:

    Hi

    In my office to they are using “Z-scalar” to check Virus, to provide security and other thing. After i am running code the output i am getting was wrong one. Macro was picking data Z-scalar web site not from the original site. Can you please help me to resolve the issue as soon as possible.

    Welcome to Zscaler.
    Please wait a moment while we launch our service.

    if you don’t see your destination website in a few seconds.
    Need help? Contact your IT team.
    Your company has selected Zscaler to protect you from internet threats.

    Thank&Regards
    Naresh

  3. Dashrathsinh says:

    Dear Concern,

    Thanks for sharing information but if want to download multiple data from same website.
    Means having same table format but putting range on website page ….
    I have been searching for code but not getting any proper reply.

  4. Sonia says:

    i want to get data from a specific table id. can you help me?

Leave a Reply

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