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: Note: If the layout of the website changes, we may need to update our code.
The data we are after (currency exchange rates) will appear in the cells B120 to B123:
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)