Stock quote question

[henryvii99]

Hello everyone,

I am trying to learn VB for stock quoting.

I want to quote the index from this site.

https://www.etnet.com.hk/www/eng/stocks/realtime/quote.php?code=0011

I would like to quote Nominal, High, Low and Turnover values in the blue boxes. And P/E ratio in the table. and export it to Excel

I found a thread posted 3 years ago which may be very useful for this purpose. (Thanks Tom Schreiner and everyone else for their efforts.)

Extracting web data from a sub web page http://excel.bigresource.com/Track/excel-6zCXgwED/

It seems that CNBC got some updates from now and I modified them as far as I can understand. I changed a few lines in class module RTDItem.

==========================================
Private Const UrlPart As String = "http://www.cnbc.com/id/15837290?q="

to

Private Const UrlPart As String = "http://data.cnbc.com/quotes/"
==========================================

Set LastSpan = HTMLDoc.getElementById("WSODQ_" & pSymbol & "_LAST_0_" & pSessionID)

to

Set LastSpan = HTMLDoc.getElementById("WSODQSTREAMOFF_" & pSymbol & "_LAST_0_" & pSessionID)

===========================================

I tried to use the UDF, but Excel hang after I enter the symbol MSFT to the function (as in the original post), any ideas? It seems that the original template Excel files are removed. I cannot find the UDF and Realtime Data Sync Example sheets anywhere in the web.

By the way, I will be very happy if someone can actually quote the numbers from the website for Hong Kong stock market

Thanks everyone in advance!!

 

[John_w]

I want to quote the index from this site.

https://www.etnet.com.hk/www/eng/stocks/realtime/quote.php?code=0011

I would like to quote Nominal, High, Low and Turnover values in the blue boxes. And P/E ratio in the table. and export it to Excel

Try a web query.

PS - that old thread is probably specific to getting quotes from cnbc.com. I don't think it would work for your etnet.com.hk without significant investigation and debugging, unless etnet.com.hk uses cnbc.com to get the underlying data.

 

[henryvii99]

Thanks for the reply.

But the problem is that www.etnet.com.hk blocks me from extracting data by Excel web query. In the way very similar to CNBC.

The banner on the top belongs to other frame, and the table below is hosted elsewhere. In that old thread they extracted data by the getElementById trick from the webpage, along with extensive quality checks.

So if I can the old thread work on CNBC and modify it, or I write a completely new one to quote data on the stock site, probably I can get what I wanted.

In fact there are 3 sites that have the real time data I needed. They are:

http://www.etnet.com.hk/www/eng/stocks/realtime/quote.php?code=0011
http://money18.on.cc/eng/info/liveinfo_quote.html?symbol=00011
http://www.aastocks.com/en/ltp/rtquote.aspx?symbol=00011

All of them prevents simple excel web query, but I think some website may be easier to quote the data by macros. Getting the info from either one site is sufficient. Thanks for your help!

 

[henryvii99]

Or maybe I can extract the data by other methods? I am sure that excel web queries aren't working...

 

[henryvii99]

I had tried to modify the old VB script to suit my needs, but it doesn't work.
I maybe able to get the things by Get ElementByClass but it just doesn't work

Can anyone show me how to extract the real time price from either one site please I can try out the rest by myself

http://www.etnet.com.hk/www/eng/stocks/realtime/quote.php?code=0011
http://money18.on.cc/eng/info/liveinfo_quote.html?symbol=00011
http://www.aastocks.com/en/ltp/rtquote.aspx?symbol=00011

 

[John_w]

A web query, selecting the entire page, worked for me with http://www.aastocks.com/en/ltp/rtquote.aspx?symbol=00011.

 

[henryvii99]

A web query, selecting the entire page, worked for me with http://www.aastocks.com/en/ltp/rtquote.aspx?symbol=00011.

Thanks! Stupid me I never thought to quote the entire page...

Thanks John!