Excel VBA: alter url based on cell value

Question

Parent post: VBA: Selecting from dropdown menu to reload page and scraping data

Apparently I don't know how to use stackoverflow: I deleted my profile, thinking I was 'muting' the email updates. Just started coding VBA today, not quite sure what I'm doing. With the help of the awesome user SIM, the code is working.

I was trying to further tinker the code, to alter the web url address such that it would insert whatever ticker symbol I put in cell J1. In this case, I'm trying to lookup more than just jpm.

The goal here is to put in whatever ticker symbol in J1, and it would be reflected on the query url. For example: J1 would hold AAPL, and the .Open command would be

.Open "POST", "https://www.nasdaq.com/symbol/jpm/historical", False

Or J1 would hold WFC, and the .Open command would be

.Open "POST", "https://www.nasdaq.com/symbol/WFC/historical", False

However, my attempts are not working so hot. Here's what I have so far.

Sub Get_Data()
    Dim tabd As Object, trow As Object, r&, c&
    Dim QueryString$, S$

    QueryString = "10y|false|" & Range("J1").Value & "" ''change here the "year" and the "ticker" name as necessary
    ''Set web_url = "https://www.nasdaq.com/symbol/" & Range("J1").Value & "/historical"

    Range("A:F").ClearContents

    With New XMLHTTP
        .Open "POST", "https://www.nasdaq.com/symbol/jpm/historical", False
        ''.Open "POST", "web_url", False
        .setRequestHeader "User-Agent", "IE"
        .setRequestHeader "Content-Type", "application/json"
        .send QueryString
        S = .responseText
    End With
    With New HTMLDocument
        .body.innerHTML = S
        For Each tabd In .getElementById("quotes_content_left_pnlAJAX").getElementsByTagName("table")(0).Rows
            For Each trow In tabd.Cells
                c = c + 1: Cells(r + 1, c) = trow.innerText
            Next trow
            c = 0: r = r + 1
        Next tabd
    End With

End Sub

I commented out the section that did not work.

drec4s · Accepted Answer · 2018-04-30 02:12:10Z

1

Actually, your commented part is almost right. You can't use Set since you are creating a string, and the ticker inside the url must be lower case. Also, you were passing "web_url" as string literal in the Open method.

This is how you would do it:

Sub Get_Data()
    Dim tabd As Object, trow As Object, r&, c&
    Dim QueryString$, S$

    QueryString = "10y|false|" & Range("J1").Value & "" ''change here the "year" and the "ticker" name as necessary
    web_url = "https://www.nasdaq.com/symbol/" & LCase(Range("J1").Value) & "/historical"

    Range("A:F").ClearContents

    With New XMLHTTP
        .Open "POST", web_url, False
        .setRequestHeader "User-Agent", "IE"
        .setRequestHeader "Content-Type", "application/json"
        .send QueryString
        S = .responseText
    End With
    With New HTMLDocument
        .body.innerHTML = S
        For Each tabd In .getElementById("quotes_content_left_pnlAJAX").getElementsByTagName("table")(0).Rows
            For Each trow In tabd.Cells
                c = c + 1: Cells(r + 1, c) = trow.innerText
            Next trow
            c = 0: r = r + 1
        Next tabd
    End With

End Sub

answered Apr 30, 2018 at 2:12

drec4s

8,1178 gold badges35 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

EKC Over a year ago

wow, got it! So to make sure I got this right, I don't need to make web_url a string by using the quotation marks "" because it is already a string? In addition, url must all be lowercase, so a uppercase messes things up, thus needing a LCase(stuff) to correct for that?

PatricK Over a year ago

@EKC As you didn't Dim web_url, it is by default a Variant type which can pretty much anything. As a good practise in VBA, you should Dim web_url As String or Dim web_url$. The $,&, etc are just short forms. Not necessary true for all lowercase website text.

Collectives™ on Stack Overflow

Excel VBA: alter url based on cell value

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related