CODEWITHSUNDEEP
htmlexcelvbaweb-scraping
healey
healey

Reputation: 314

VBA script to navigate webpage and download CSVs

I'm looking for help in developing a VBA script to run in excel.

At this site, CSV files can be downloaded for each fund. The VBA script I'm after will:

This process will need to be repeated for each item in the 'Fund provider' drop down.

I have little experience in using IE to navigate websites, so any pointers would be appreciated. My existing code is below. It allows me to access the Fund Type button but I'm not sure how to change it's values.

Option Explicit
Sub FidelityCSV()
    ' Create Internet Explorer object.
    Dim BaseURL As String
    Dim IE As Object
    Set IE = CreateObject("InternetExplorer.Application")

    BaseURL = "https://www.fidelity.co.uk/fund-prices/"
    IE.Visible = True     ' Keep this hidden.
    IE.navigate BaseURL

    Do While IE.Busy Or IE.readyState <> READYSTATE_COMPLETE
        DoEvents
    Loop

    'Wait for it to really finish loading
    Application.Wait (Now + TimeValue("0:00:15"))

    Dim oHEle 'As IHTMLElementCollection
    Dim oHDoc As HTMLDocument

    Set oHDoc = IE.document

    Set oHEle = oHDoc.getElementById("fund_type")

    ' Clean up.
    IE.Quit
    Set IE = Nothing
    Set oHEle = Nothing
    Set oHDoc = Nothing
End Sub

Upvotes: 1

Views: 309

Answers (1)

QHarr
QHarr

Reputation: 84465

XMLHTTP request:

You could avoid using a browser and mimic the pages POST request

enter image description here

Option Explicit
Public Sub GetData()
    Dim sResponse As String, body As String
    body = "appliedFilters=*/INVESTMENT_COMPANY/NAME|Allianz"
    body = body & "&idolQueryParam=fund_prices"
    body = body & "&orderedUIFields=officialName,priceUpdatedDate,buy,sell,priceChange,currency"
    body = body & "&mode=all"
    Dim http As Object
    Set http = CreateObject("MSXML2.XMLHTTP")
    With http
        .Open "POST", "https://www.fidelity.co.uk/product/securities/service/funds/download-funds", False
        .setRequestHeader "If-Modified-Since", "Sat, 1 Jan 2000 00:00:00 GMT"
        .send body
        With CreateObject("ADODB.Stream")
            .Open
            .Type = 1
            .write http.responseBody
            .SaveToFile "C:\Users\User\Desktop\Data.csv", 2 
            .Close
        End With
    End With
End Sub

Using Internet Explorer:

The following shows you how to perform each of those actions and set for one provider. It should be clear how you can set up a loop to select other providers. Please refer to the many answers on StackOverflow regarding clicking the Save/Open Dialog.

Option Explicit    
Public Sub Download()
    Dim ie As New InternetExplorer
    With ie
        .Visible = True
        .Navigate2 "https://www.fidelity.co.uk/fund-prices/"

        While .Busy Or .readyState < 4: DoEvents: Wend

        On Error Resume Next
        .document.querySelector(".button--accept").Click '<==Cookies
        On Error GoTo 0

        With .document
            .querySelector("#fund_type").selectedIndex = 0 '<== All.
            .querySelector("#allfundsAvailability").Click '<== All
            .querySelector("#fund_provider [value='AXA']").Selected = True '<== Select provider
            .querySelector("#filterBtn").Click '<== Apply filter
            .querySelector("#ofPrint").Click ' <==Download
        End With
        Stop
        '.Quit
    End With
End Sub

Upvotes: 1

Related Questions