Pulling in Data from Website Table

[alexm215]

Hi,

Long time reader but just registered to ask my first question.

I am trying to use VBA to pull in historical data from a table on a website, which I have managed to do (see Code 1). However there is a date range that needs to be filled in to state which days the table should show. It is currently limited to the last ~3 months of information. I want to be able to pull in all historical information available via VBA.

To solve this I looked into navigating through the web browser via VBA. I have code that is able to open the URL and then I tried to use the ID to change the attribute value of the beginning of the date range but have limited HTML/web developing experience. Has anyone done this before?





Code1
Pulls in the data from the chart located here:

http://quotes.wsj.com/fx/USDCAD/historical-prices

<tbody>
</tbody><colgroup><col></colgroup>

Sub SearchSite()
strsearch = Range("M5")
theurl = Range("E1")

With ActiveSheet.QueryTables.Add(Connection:="URL;" & theurl, Destination:=Range("I1"))

.Name = "NewsQuery"
.AdjustColumnWidth = False
.TablesOnlyFromHTML = False
.Refresh BackgroundQuery:=True
End With

Debug.Print "DONE"

End Sub



Code 2

Attempts to navigate website and change the 'selectDateFrom' element. Unable to get it to work. Once I get the beginning date to change I think it will be easy to integrate Code 1 into this.

Sub HistoricCADUSA()
Dim ie As Object
Set ie = CreateObject("internetexplorer.application")
With ie
.Visible = True
.navigate "http://quotes.wsj.com/fx/USDCAD/historical-prices"
'wait for loading
Do While .busy
DoEvents
Loop
Do While .readystate <> 4
DoEvents
Loop
End With

Call ie.document.getelementbyID("{actionform.selectDateFrom}").SetAttribute("Value", "01/01/1990")


End Sub

 

[alexm215]

UPDATE

I am now able to set the start date element but am having difficulty getting the table to be brought into excel... below is my code

I wanted to use the code right below this to call the table in but cannot figure out how to name the active webpage as the connection. Does anyone have an idea how to do this?

With ActiveSheet.QueryTables.Add(Connection:="URL;" & theurl, Destination:=Range("I1"))

.Name = "NewsQuery"
.AdjustColumnWidth = False
.TablesOnlyFromHTML = False
.Refresh BackgroundQuery:=True
End With

Sub HistoricCADUSA()

Dim ie As Object
Set ie = CreateObject("internetexplorer.application")
With ie
.Visible = True
.navigate "http://quotes.wsj.com/fx/USDCAD/historical-prices"
'wait for loading
Do While .busy
DoEvents
Loop
Do While .readystate <> 4
DoEvents
Loop
End With


Set StartDateRange = ie.document.getElementbyId("selectDateFrom")
StartDateRange.Value = "1/1/1990"

End With
End Sub

 

[John_w]

I don't think it is possible to combine a web query and IE automation.

You can import the data by running the text import wizard to open the following URL:

http://quotes.wsj.com/fx/USDCAD/his...ys=90&startDate=06/17/2017&endDate=09/15/2017

Here is a macro which specifies the start and end dates (a range of 90 days from today's date) and imports the data into the first worksheet. Just modify the startDate and endDate variables to the dates you require.

Public Sub Import_WSJ_Data()
    
    Dim startDate As Date, endDate As Date
    Dim numDays As Integer
    Dim destinationCell As Range
    Dim URL As String
    
    endDate = Date
    startDate = endDate - 90 + 1
    
    With Worksheets(1)
        .Cells.ClearContents
        Set destinationCell = .Range("A1")
    End With
    
    numDays = endDate - startDate + 1
    
    URL = "http://quotes.wsj.com/fx/USDCAD/historical-prices/download?MOD_VIEW=page&num_rows=" & numDays & "&range_days=" & numDays & _
            "&startDate=" & Format(startDate, "mm/dd/yyyy") & "&endDate=" & Format(endDate, "mm/dd/yyyy")
   
    With destinationCell.Worksheet.QueryTables.Add(Connection:="TEXT;" & URL, Destination:=destinationCell)
        .Name = "2017_1"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 850
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(3, 1, 1, 1, 1)  '3=MDY date; 1=General
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    destinationCell.Worksheet.QueryTables(1).Delete
    
End Sub