Solved: VBA Web Query to google finance/reuters

[Sebbl99]

Hi Guys,

I am working on a Portfolio in Excel and basically wanna set up an overview of a couple of key figures.

Basically, I wanna import data from google finance or reuters (not from yahoo finance). I don t wanna do it every day so I need to write a VBA and make a module button, so I can update the information whenever I want. However, I know the VBA code has to be more sophisticated as I only wanna import, lets say the stock price and not all the other stuff along with it. So for instance my code has to know that he should pull only the stock price from google finance to cell J4 in Excel, P/E in J5, Yield in J6 and so on.

Can anybody help me out on this one? My VBA coding skills are so poor and I can`t even do it manually as google finance doesn t provide any html tables.

Thanks in Advance

[Shred Dude]

This should get you started. Uses a quick xmlhttp retrieve of the page's source code into a string variable. From there you can parse out what you need.

The example below retrieves the last traded price for a symbol.

So in you cell J4 you could put
=getGoogPrice(J3).
assuming the stock symbol was in J3.

Public Function getGoogPrice(symbol As String) As Variant
Dim xmlhttp As Object
Dim strURL As String
Dim CompanyID As String
Dim x As String
Dim sSearch As String

strURL = "http://www.google.com/finance?q=" & symbol
Set xmlhttp = CreateObject("msxml2.xmlhttp")
With xmlhttp
.Open "get", strURL, False
.send
x = .responsetext
End With
Set xmlhttp = Nothing
'find goolge's "Company ID" they assign to a symbol
sSearch = "_companyId ="
CompanyID = Mid(x, InStr(1, x, sSearch) + Len(sSearch))
CompanyID = Trim(Mid(CompanyID, 1, InStr(1, CompanyID, ";") - 1))
'Use the company ID to retrieve data needed
'Here is an example of the last price:
'example: <span id="ref_14135_l">15.79</span>
sSearch = "ref_" & CompanyID & "_l"">"
getGoogPrice = Mid(x, InStr(1, x, sSearch) + Len(sSearch))
getGoogPrice = Left(getGoogPrice, InStr(1, getGoogPrice, "<") - 1)
'Examine the Page Source to find the Span ID's for the other bits you want
'They all seem to use the company id

End Function

[Sebbl99]

Sorry for the late reply-I was working on different projects.

This code is awesome, it worked super. But everytime I try to put in my information retrieved form the respective source code it showed me an error message. Here your code with the information of the EON company. What am I missing here.

Public Function getGoogPrice(symbol As String) As Variant
Dim xmlhttp As Object
Dim strURL As String
Dim CompanyID As String
Dim x As String
Dim sSearch As String

strURL = "EON Link-google finance"
'am not allowed to post links as I ony have 2 posts so far:-)
Set xmlhttp = CreateObject("msxml2.xmlhttp")
With xmlhttp
.Open "get", strURL, False
.send
x = .responsetext
End With
Set xmlhttp = Nothing
'find goolge's "Company ID" they assign to a symbol
sSearch = "_673059" = ""
CompanyID = Mid(x, InStr(1, x, sSearch) + Len(sSearch))
CompanyID = Trim(Mid(CompanyID, 1, InStr(1, CompanyID, ";") - 1))
'Use the company ID to retrieve data needed
'Here is an example of the last price:
'example: <span id="ref_14135_l">15.79</span>
sSearch =<span id="ref_673059_l">22.32</span> getGoogPrice = Mid(x, InStr(1, x, sSearch) + Len(sSearch))
getGoogPrice = Left(getGoogPrice, InStr(1, getGoogPrice, "<") - 1)
'Examine the Page Source to find the Span ID's for the other bits you want
'They all seem to use the company id

End Function

[Sebbl99]

in google finance, type in EOAN to get to EON and its source code (the EOAN in the middle of the three options)

[Sebbl99]

I got it:-) :-)

its working perfectly fine now hurraaaay, thanks so much for your effort

[Sebbl99]

is so nice, I am stoked right now, I can pull just every price I want from google finance, you are a real genius

[Sebbl99]

if I wanna pull the share price from reuters, how exactly do I have to rearrange the code

lets say I want to pull the stock price from here http://www.reuters.com/finance/stocks/overview?symbol=NESN.VX

I can`t figure out the proper company id (there are some confusing lines in the source code) and the proper "ssearch.." command

Could you give me a showcase VBA for Nestle?

[Shred Dude]

Thie code below will do the Reuters site.

I added an important piece that I left out of the Google code. You need to clear your cache before each attempt to pull the quote, or you'll ge the same answer each time. by default it'lluse the cached version of the last itme you pulled the data.

The DelteURLCacheEntry function does this for you. Notice the extra Public Function and the insetion of one line of code after establishing the URL. You can put that same line of code in the getGooglePrice function.

Public Declare Function DeleteUrlCacheEntry Lib "Wininet.dll" _
Alias "DeleteUrlCacheEntryA" _
(ByVal lpszUrlName As String) As Long

Public Function getReutersPrice(symbol As String) As Variant
Dim xmlhttp As Object
Dim strURL As String
Dim CompanyID As String
Dim x As String
Dim sSearch As String, myDIV As String, myPrice As String

strURL = "http://www.reuters.com/finance/stocks/overview?symbol=" & symbol 'NESN.VX"
DeleteUrlCacheEntry (strURL)
Set xmlhttp = CreateObject("msxml2.xmlhttp")
With xmlhttp
.Open "get", strURL, False
.send
x = .responsetext
End With
Set xmlhttp = Nothing
'find sectionQuoteDetail Div
'eg:
'<div id="headerQuoteContainer">
' <div class="sectionQuote nasdaqChange">
' <div class="sectionQuoteDetail">
' <span class="nasdaqChangeHeader">NESN.VX on Virt-X Level 1</span>
' <br class="clear"><br class="clear">
' <span style="font-size: 23px;">
' 53.40</span><span>CHF</span><br />
' <span class="nasdaqChangeTime">10:08am EDT</span>
' </div>
sSearch = "sectionQuoteDetail"
myDIV = Mid(x, InStr(1, x, sSearch) + Len(sSearch))
myDIV = Trim(Mid(myDIV, 1, InStr(1, myDIV, "</div>") - 1))
'Split out the Spans
y = Split(myDIV, "</span>")
'y(0) = Name
'y(1) = Price
'y(2) = Time

'clean up the price
myPrice = Mid(y(1), InStrRev(y(1), ">") + 1)
myPrice = Replace(myPrice, Chr(13), "")
myPrice = Trim(Replace(myPrice, Chr(9), ""))

getReutersPrice = myPrice

End Function

[Sebbl99]

thanks so much for your help and support. All my prices are up and running and my PC doesn`t even have to calculate that much..:beerchug:

[Sebbl99]

Hi There,

I am working with your codes for a while now and those functions are just amazing

Currently, I am watching some Romanian stocks (in particular SIF3-a financial stock) via the stock exchange in Bucharest (BSE)

I tried to set up a similar code which you ve wrote for me in order to pull the stock price from the bucharest stock exchange but to no avail

I wondered whether you could help me out once more and give me a code with which I can pull the price from the BSE website

http://www.bvb.ro/ListedCompanies/SecurityDetail.aspx?s=SIF3

the above link shows the SIF3 stockprice on the BSE

I d be grateful loads and loads

[Shred Dude]

If you'll post the HTML Source for the page I'll have a look and point you in the right direction. (Right-Click the page and select View Source.)

[Sebbl99]

[ajn946946]

Apologies to ask, but I am very new to VBA and I am attempting to use the above code provided. However, I am not getting it to work.

I am doing the following:

1. Create new module with above google code
2. Cell A1 = stock code = CSCO
3. Cell A2 = getGoogPrice(A1)

What am I missing?

Thanks in advance.

[Crocus Crow]

ajn946946,

The code works for me - Shred's, not Sebbl99's version which doesn't compile.
Put CSCO in A1.
Put =getGoogPrice(A1) in A2.

[ajn946946]

Thanks Crocus,

I was trying this using the Mac 2011 version. It works fine on Windows version but not Mac.. I dont suppose you have any idea why?

Cheers

[ajn946946]

Would anyone know how to add the company name and also the daily % change into the above code for google as I am having a little difficulty?

[ajn946946]

Anyone willing to lend a hand?

[sanjal2325]

I want to your below codes to retrive data from google finance. But I want to use for multiple Span Id.

Could you please let me know how I can modify the below codes for multiple span ID?

[sanjal2325]

I want to use below codes to retrive data from google finance. But I want to use for multiple Span Ids.

Could you please let me know how I can modify the below codes for multiple span IDs?

[shivsharma]

GR8 JOB!

IT WORKS FINE. BUT I WANT TO KNOW HOW TO GET OPEN, HIGH LOW AND PREVIOUS CLOSE.

DO THE NEEDFUL

THANKS IN ADVANCE

[smoosh]

hi all.

i cant modify the existing code for currency converter.
i want to get rates for selected 10 currencies in excel file.
please helpp.

tack.

[ba47]

Gives error on excel 2011 for Mac.