Trouble with Web Query in Excel

Question

I´m having some trouble with two different web queries in Excel.

1) Website: http://www.danielsoper.com/statcalc3/calc.aspx?id=44

Trouble: I can´t seem to obtain the result value of the query. Basically, I´m inputting the parameters in an excel spreadsheet and I want the result. I don´t know if is related, but the result only exists after you press calculate. Seems easy, but I having some trouble...

2) Website: http://www.iea.org/statistics/statisticssearch/report/?&country=USA&year=2011&product=Balances

Trouble: I believe that maybe this one is not related to the query itself, since I do get data, but the results seems to be encrypted.. I simply can´t tell if it is something I'm doing wrong or if there is some way to overcome this.

From my perspective, even though the first one SEEMS to be easier to fix, the second one would be extremely helpful for my research.

I appreciate any help you can give me.

Thanks,

As for 1), is your calculation set to manual? Try changing this to Automatic. As for 2), you're not giving us much to work with here. What do you mean with 'seems to be encrypted'? What results do you get, and what do you expect it to output? — Michael, Nov 30 '13 at 11:18

score 1 · Answer 1 · answered Nov 30 '13 at 15:00

1

For the iea, they want you to subscribe to their 'online data services' so they encode all of the search result data.

For danielsoper, the results are generated via ASP, so you won't be able to get them form a web query. You can get them by using VBA and MSXML, but you'd be better off just replicating the algorithm in VBA or fining a different website.

answered Nov 30 '13 at 15:00

Dick Kusleika

32,673
4
52
73

1) So it is pointless to keep it up, right? Is so, thanks for your time! 2) That's great actually... I can work around it, but I was hoping to make it easier, bur that´s ok! – PedroRochedo Dec 01 '13 at 03:05
Yeah, I think so. At least I don't know any way to get the correct data. – Dick Kusleika Dec 01 '13 at 17:18

score 0 · Answer 2 · answered Dec 01 '13 at 17:53

The following code works for me at your first website. Place the inputs in cells "A1" and "A2" and the results are placed in "A3" and "A4". Modify to fit your worksheet.

' Open IE, navigate to the website of interest and loop until fully loaded
Set ie = CreateObject("InternetExplorer.Application")

With ie
    .Visible = True
    .navigate "http://www.danielsoper.com/statcalc3/calc.aspx?id=44"
    .Top = 50
    .Left = 530
    .Height = 400
    .Width = 400

Do Until Not ie.Busy And ie.ReadyState = 4
    DoEvents
Loop

End With

' Insert data from cells "A1" and "A2" into the webpage and click "Calculate!"
ie.Document.getElementById("pageContent_gridParameters_txtParameterValue_0").Value = Range("A1")
ie.Document.getElementById("pageContent_gridParameters_txtParameterValue_1").Value = Range("A2")
ie.Document.getElementById("pageContent_btnCalc").Click

' Collect the results and place them on the activesheet
my_var = ie.Document.body.innerhtml

pos_1 = InStr(1, my_var, "Result_0", vbTextCompare)
pos_2 = InStr(pos_1, my_var, ">", vbTextCompare)
pos_3 = InStr(pos_1, my_var, "<", vbTextCompare)
One_Tailed = Mid(my_var, 1 + pos_2, pos_3 - (1 + pos_2))

pos_4 = InStr(pos_3, my_var, "Result_1", vbTextCompare)
pos_5 = InStr(pos_4, my_var, ">", vbTextCompare)
pos_6 = InStr(pos_5, my_var, "<", vbTextCompare)
Two_Tailed = Mid(my_var, 1 + pos_5, pos_6 - (1 + pos_5))

Range("A3") = One_Tailed
Range("A4") = Two_Tailed

Trouble with Web Query in Excel

2 Answers2