Extracting Real Time Quote from Google Finance

Can anyone help me

Originally Posted by MarvinP

Perhaps?

http://investexcel.net/google-financ...otes-in-excel/
http://www.mrexcel.com/forum/excel-q...el-2007-a.html

Google finance only provides CSV for stocks listed in US , but my friend need to search for Japanese and Korean Stocks. Therefore, I need to scrap its HTML

You need to include the URL and StockTicker.
Google has many pages on finance, from many countries, hence the need to be specific. Which value you wish to scrape?

Originally Posted by JasperD

If you just want to get the latest stock price based on the stock name, use the code below (adjusted from what you had, just so you can understand more easily - I personally wouldn't put this in a function):
Set reference to Microsoft HTML object library.

Please Login or Register  to view this content.

Please click the * below if this helps

Thanks for your help. However, I encounter errors for reading this "http As New MSXML2.XMLHTTP, stock As New HTMLDocument"
How could I solve it?

Originally Posted by AB33

You need to include the URL and StockTicker.
Google has many pages on finance, from many countries, hence the need to be specific. Which value you wish to scrape?

"https://www.google.com/finance/historical?q=" is the URL, just need to put the stockticker or number after "q=".
For example,
https://www.google.com/finance?q=0005 is the URL of HSBC which listed in HK.
https://www.google.com/finance?q=005930 is the URL of Samsung Electronics which listed in Korea.

I wish t scrape the current price of the stock.

Jasper's code seems to work.
You need to tick two boxes on the tools-reference before you run the code: Microsoft HTML object library and Microsoft XML, V.5 or what ever the latest version is.
For e.g if you do a sub,

Please Login or Register  to view this content.

Originally Posted by AB33

Jasper's code seems to work.
You need to tick two boxes on the tools-reference before you run the code: Microsoft HTML object library and Microsoft XML, V.5 or what ever the latest version is.
For e.g if you do a sub,

Please Login or Register  to view this content.

I had ticked those boxes (Microsoft HTML object library and Microsoft XML, V.6.0 ), but I still encountered an error with "stock As New HTMLDocument"

VBA.jpg
I selected the above items in the refereneces

Originally Posted by cpfrona

Attachment 392827
I selected the above items in the refereneces

I have ticked the "Microsoft HTML Object Library" and it works till "stock.body.innerHTML = http.responseText" with error 91.

I think it is wrong to use early binding as you found out that not many people know where to find objects in the library. For this reason, I avoid using it, but for some unknown reason to me, one of the object is not working. If you want to get the entire price, you need to comment the first line, instead use the second line.

Please Login or Register  to view this content.

Originally Posted by AB33

I think it is wrong to use early binding as you found out that not many people know where to find objects in the library. For this reason, I avoid using it, but for some unknown reason to me, one of the object is not working. If you want to get the entire price, you need to comment the first line, instead use the second line.

Please Login or Register  to view this content.

Thanks very much and it works for me now.
Could you tell me more about the difference between the latest one and the previous one? I am new to XML and they look similar to me.

It is the same code, but Jasper used early binding, which means you need to connect to the object (Tick the boxes in the library) before you run the code, while late binding does not require you need to tick any box, just run it. However, I still used one of the object as early binding-stock As New HTMLDocument. If I change the stock as object, the code errors. I do not know the reason (Will find out tomorrow). The code uses a mixture of early binding (stock As New HTMLDocument), so you need to tick the box in the library, but you do not need to tick, Microsoft XML, V.5 since I have changed that part of the code in to late binding.

Thanks for your guidance

Usually when early binding like this fails, I still use it but split it over two lines, like:

Dim stock as htmldocument
Set stock = new htmldocument

That usually fixes problems. Anyway, glad to hear you got it to work!

Please make sure you click add rep to one of AB33's replies. It's the proper way to show gratitude :-)

Thanks for your kindly remind.
Really appreciate you guys.