HTTP request in VBA

[leslietsz]

Hi, I have recently read the post here about "how to click javascript alert button when using IE"

Link: http://www.excelforum.com/excel-prog...-using-ie.html

Thanks to the contribution of Kyle123, a solution using http request can successfully capture the data


I understand his script is using get and post to retrieve the response and copy it into the responsetext and look up the corresponding token and table.

I have tried his method, but however I found out that the data retrieved are not complete

In the

For Each rw In doc.getelementsbyTagname("table")

Debug.Print rw.innertext

Next rw

After print out the table in intermediate window, i find out that the table is not complete, only those shareholding with 0% are printed out.

I have tried to print the responsetext before filtering tagname "table" and it also shows that the table is not complete

May I ask whats the problem?

Thank you

[Kyle123]

What's the url?

[leslietsz]

http://www.hkexnews.hk/sdw/search/search_sdw.asp

From the last post, you mainly focus on getting the token and retrieve the data, but i have tried today and find out that the table retrieved is not complete

[leslietsz]

Please Login or Register  to view this content.

[Kyle123]

Give me something to work with here, what is a search term you want to input?

[leslietsz]

I want to input the date, stock number , let say 1101 and retrieve the first 20 shareholder and their concentration

[leslietsz]

concentration means % of Issued and/or Tradeable Shares

Many Thanks Kyle, I have tried to delay the code to see whether because the code is running too fast, but I found out that it is still the same

[leslietsz]

Even for the get part, I found out it will not retrieve the whole body html code, but only part of them

[Kyle123]

There's nothing wrong with your code, the immediate window just isn't big enough to show you the values above 0 (they're sorted desc), have you tried outputting to a sheet?

In summary, your code works, you can't see everything in the immediate window.

[leslietsz]

Oh, I see, I thought there is any problem, I try to output to sheet then

Can you teach me how to output, sorry I am very new to VBA, thanks

[Kyle123]

Yes, but please don't pester me. I'll help when I can, please don't send me multiple PMs and visitor messages.

You want:

Please Login or Register  to view this content.

[leslietsz]

Sorry for causing any troubles to you , but thanks for your help

[Kyle123]

No problem

[Doc.AElstein]

Hi leslietsz,
( Thanks for changing the Link )
Regarding the Source Code

.....
I have tried to print the responsetext ...... also shows that the table is not complete
......

I have had this problem a lot. ( As Kyle said, the Immediate Window does not show everything )

Even if you paste out to a Worksheet Cell you will not see in the cell all the source code. BUT, I have found that the text is all there, in the cell, but you just cannot see it all. For example if you change your code a bit like this:_........###

Please Login or Register  to view this content.

_...........Then you will find that the code still works!!!!

I find the following useful:
I paste my source code out to a cell, ( and as you did, to the Immediate Window). Then I can see that it “worked”, even though I do not see it all.

I then split the text of the cell using a carriage return as the delimiter.

I have then returned a 1 Dimensional Array, with each element being a line in my source code.

I then write out each line to a text file. ( I prefer this method, as the resulting .txt File can then be used in place of the URL to check and develop the code without accessing the site _... P.s. 2) http://www.excelforum.com/showthread...t=#post4475902 _.....)

So if you change your code as indicated above_..### -... , and then run this code, you will get a .txt File located in the same folder as the one in which the code is in. In that .txt file you will see your entire source code.

The code I have written for you is a lot more complicated than it needs to be, but..._...

....I am very new to VBA, thanks

_.... it may help you to learn...

Code:

Please Login or Register  to view this content.

Results:

( Image: Leiszlitztesties.JayPeaGee ) Bollox
Attachment 479549


http://listenonrepeat.com/watch/?v=i...I_Remember_You


Edit: changed Write to Print in code to get rid of unwanted quotes that were put in the .txt file

[AB33]

Hi Alan,
I do not want to pester Kyle, but I have been a bit perplexed on this line.

.send generatePost
In one previous post, Kyle said, in some sites, you can find the parameters for the post method are found on the "Network" tab, then body.
I can only find one get method with nothing on the body.
Do you happen to know where did Kyle got these parameters?

[leslietsz]

When you use chrome, you can monitor the network, and you can find the response from there, in the developer tool

[leslietsz]

thank you so much Alan!

[Doc.AElstein]

@ leslietsz, you is welcome !

_.............................

@ AB33
Hi AB33

I am not too sure what you are asking, ( mainly as I do not completely understand Kyle’s latest code here, lol. !! )

But a couple of points:

_1) I assume you know that generatePost is a Function written by Kyle, given in the referenced Post from post # 1 in this Thread
http://www.excelforum.com/excel-prog...ml#post4309750

_2) I thought one just looked for them in the source code. But I am a bit confused with the HTML and java, - I am not quite sure what is going on in the example in this thread. I do not know which code is which and / or which the code is using.. maybe just the HTML stuff is being used ,


- If I use Google chrome right mouse click method in P.s. 2 in my ramblings here http://www.excelforum.com/excel-prog...ml#post4475902 ), ( EDIT 1: and as leslietsz just said )
Then I can see this in what looks like a HTML code section:...


LeslietzGCSrcCode.JPG


_...... so there is the information. .. , I think.. but I do not know of any other way to get at it. ( you can also see it in the .txt file i did and uploaded for leslietsz, ( but there are some annoying extra “” all over the place which are bugging me ?? )

Alan

EDIT 2: Thanks for the rep, leslietsz

[AB33]

Hi leslietsz

Interesting!
I do not have chrome, but I would have expected to get the info in IE or Fox.

Hi Alan,
I know the source file. If you want use the post method, you might need to send the request with the parameters.
These are can easily be found on the Network, not on source file

When you use chrome, you can monitor the network, and you can find the response from there, in the developer tool

[Doc.AElstein]

Hi AB33
I can get the source code from Internet Explorer. _..
_.. -- Wheel Top Right --- Development Tools ------ Script

I expect Kyle will pop by and tell us how to get it directly from the "Network" or whatever

Alan



I.E. Tool
IEWillyWonk.JPG

[AB33]

Hi Alan,
I can now see it on Firefox, but not in IE. It is more likely that I do not know how to find it in IE.
I can see from the screen you have attached., while you are at "HTML" page, click the "Network" (Nezwerk in Germany). You should be able to see list of requests. I think in this case, we are only interested in "Post" method. Once you double clicked the post method, on the top right hand, you can see sub menus like "body, parameters, cookies and etc.". The parametres (If there are) are on the body section

[Doc.AElstein]

.... I see nothing in Netzwork...Chrome or IE

EmptyNetwork.JPG


_______________________

I had a screen shot from Kyle relating to the _.....
.setRequestHeader

http://i.imgur.com/r1Ie7Le.png


_... I could not find that either..

[AB33]

You can only see them after you enter the details on the boxes. Let's enter 1111 on the code and hit the search button. You then follow the steps.

[Doc.AElstein]

You can only see them after you enter the details on the boxes. Let's enter 1111 on the code and hit the search button. You then follow the steps.

I do not understand what to type in

First box is maybe date

Second is maybe 1111 ? or 1101


I do not understand what I should type in the others ?

I have no, idea what tokens are . etc.. or Stock Name or CCASS Participant ID or Name of CCASS Participant

[AB33]

The default date is given. You only need anther one from the options, just type 1111 on the stock code and hit the search button. You should be able to see a new page.

[Doc.AElstein]

? Still Empty


StillEmpty.JPG

[leslietsz]

??1.png

This one is to find token, by simply right click and view source , find the token part


After you get the token then use the generatepost to retrieve next page data

??2.png

You can find in developer tools, network part, when you reload the page, you can see search_sdw.asp , click on it and scroll to the end you can find Form data, and choose view source

[leslietsz]

Hi Kyle， I have encounter another problem, right now, it works perfect for http://www.hkexnews.hk/sdw/search/search_sdw.asp as its language is in english

But However, when I work on the traditional Chinese version, some chinese characters cannot be normally displayed, may I ask is that because it is in BIG5 coding?

[leslietsz]

Finally I can tackle the problem, by simply adding StrConv(.responseBody, vbUnicode)

Thank you guys

Here is the final code for Chinese version

Please Login or Register  to view this content.

[Doc.AElstein]

@ leslietsz
Hi leslietz.
Thanks for adding to the discussion ! But I do not understand everything

I Understand what you wrote here with this:

....one is to find token, by simply right click and view source , find the token part,,,

LeslietzToken.JPG


Now I have the “Token”.
Is the “Token” here 7767.728 ?
_.........................

But I have no idea what to do with it
( I do not know what a “Token” is , or what it is for in this example ? )
This which you wrote - I do not understand :

...After you get the token then use the generatepost to retrieve next page data

What exactly should I do with the Token. ??

I do not understand this: “....generatepost to retrieve next page data.....” ???
_.......................

Anyway: 1)
I can explain how the code is working, in regard to the "Token" bit_...at least partly :-

Consider This line in Kyle’s code

Please Login or Register  to view this content.

It Has 8 arguments. ( But I only see 7 boxes_... ??
_ 3 Dates,
_ Stock Code,
¬ _ Stock Name,
_ CCASS Participant ID,
_ Name of CCASS Participant,
) _ ...

The first 7 arguments fill in the 7 boxes.

Clearly the last argument gets the “Token” by_....

Please Login or Register  to view this content.

_.... The code snippet above I understand as follows, (FWTFIW):
doc.getElementsByName("sessionToken") returns a "DispHTMLElementCollection" thing _.. we Goted ( "did Get like" ) to it from the source code by using the HTML DOM get Element By its Name Method with the argumnet (“sessionToken”), .... which we see in the Image above of the source code is there ( only once ) and to be found, as it was..
(_..._.. BTW. we should not supposed to know about a DispHTMLElementCollection" thing but we can use it through the interface thingy IHTMLElementCollection ... 'cos Kyle said so.... and what a lot og bollox it was_....http://www.excelforum.com/excel-prog...ml#post4446595
_...)
..._.. In this case I DispHTMLElementCollection" returns a "collection" of one element, the “Token”. But it is a "collection" still all be it of only 1. As it is a collection , then we must ( or can ) refer to it by its index number, ( which conventinally starts at 0 ( If we had two elements they would have the index numbers of 0 and 1 ) ) .
So the first ( and only in this case ) element is got at by the direction of ( 0 ) to the first element in the collection.
Further, the value of it is got by applying .Value Propperty of the diverse HTML Object Input Element


_ if you are interested, .open , .setRequestHeader , .send ae given soome Layman's explanations here
http://www.excelforum.com/excel-prog...ml#post4475902
_ I do not know what .abort is ? Possibly it is a java thing so probably out of place in a VBA code, .. like a .Close() is out of place in a VBA code ....
_......................................


So I know roughly what is going on, but i do not know why... Never mind.. ( I cannot remember now if or what i wanted to know anyway .. lol... ) _......._._______________________________________________________

Anyway 2)

[b. I think I can understand this now, thank you so much for the screen shots !!!![/b]

......You can find in developer tools, network part..... click on it and scroll to the end you can find Form data, and choose view source

I redid the screen shots as they were a bit difficult to see_.....
GoogleChrome: _...willy wonk bollox
http://www.excelforum.com/showthread...t=#post4477109

_...... and further i had a go at doing it in Internet Explorer:
Internet Explorer: _.. willy wonk Bollox
http://www.excelforum.com/showthread...t=#post4477139




Thanks again for your input. It was a nice surprise and very helpful
It is refreshing to get a helpful response from the OP, furthering the discussion and contributing to making it a good Thread
Alan


P.s. Edit I changed the code in post #14 . I replces Write with print to get rid of unwanted quotes in the text File produced by that code

[Kyle123]

Alan, because you like to be explicit. You're using the term Java incorrectly, what you're referring to as Java is JavaScript (that has nothing to do with Java). Incidentally, none of this has anything to do with JavaScript.

[Doc.AElstein]

Hi Kyle

... You're using the term Java incorrectly, what you're referring to as Java is JavaScript (that has nothing to do with Java). Incidentally, none of this has anything to do with JavaScript.

Fair enough. There was a bit more javaScript in the source code than I had seen up until now. Also the first things I Googled on .abort came up with something to do with “java stuff”. So I naively put 1 and 1 together and dreamt that "java" stuff script or whatever was coming in.

Thanks for setting me straight on that.

Alan

[leslietsz]

Hi Kyle, there is some problem for running the code, because recently the website has changed its code, and I try to modify the coding, however, it cannot response the table from my post value

The website now is http://www.hkexnews.hk/sdw/search/searchsdw.aspx

I found out that instead of using token value from the past, it has added 3 indicators, which is VIEWSTATE, VIEWSTATEGENERATOR and EVENTVALIDATION , also a combination of string

I have studied the previous code from you and try to extract those values, it can be extracted and i encode the string since it is required during "POST" stage.

But However, I try to load debug.print doc.body.innerhtml to see whether it has retrieved the table or not, it shows that

"The page requested may have been relocated, renamed or removed from <BR>the Hong Kong Exchanges and Clearing Limited"

May I ask what is wrong with my coding? Thank you for your help

Please Login or Register  to view this content.

[leslietsz]

From further investigation, I found out when I GET the page , that 3 verification variables, it will be different after I POST to the website. Thats why I cannot get to the table result

May I ask any method to extract the page before I POST the request to the website?

[AB33]

I am sure Kyle will give you more detailed and correct answer. Here is my take: the 3 parameters are auto-generated as you have yourself noticed it. I suspect this done in purpose to curtail auto-scrapping. What I am not sure is why are you trying to modify these parameters? I thought you need to include them with out modification. This then raise another issue-the length of some of queries is half-page. I do not know how practical it is to parse them in the body of sent method.

[leslietsz]

Thanks AB for your comment, actually I dont want to modify these 3 parameters, they just like the token from the previous version, which are required to include for the POST request

[leslietsz]

If you are talking about why I encode the parameters, because I found out that from the Chrome Developer mode, the POST string sent to the page is encoded, so I was thinking maybe I need to encode those strings before I send to the page