Little help - Quotes in VBA
 

Hey guys,

Was wondering if anyone knew how to load a webpage into memory and
pull a specific quote using a function.

For example I would want to write a function =BBQuote(CLLRUIB,LX) or
=BBQuote(CSCOMPU,SW)

to get the NAV from these webpages


http://www.bloomberg.com/apps/quote?ticker=CLLRUIB%3ALX
http://www.bloomberg.com/apps/quote?ticker=CSCOMPU%3ASW

Little help - Quotes in VBA
 

One way is to write a looping macro that gets the data from an external
query and then copies to the right of the ticker symbol requested.
CLLRUIB,LX 134.15
CSCOMPU,SW 7.25

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Fidel" wrote in message
...
Hey guys,

Was wondering if anyone knew how to load a webpage into memory and
pull a specific quote using a function.

For example I would want to write a function =BBQuote(CLLRUIB,LX) or
=BBQuote(CSCOMPU,SW)

to get the NAV from these webpages


http://www.bloomberg.com/apps/quote?ticker=CLLRUIB%3ALX
http://www.bloomberg.com/apps/quote?ticker=CSCOMPU%3ASW

Little help - Quotes in VBA
 

I brought back every thing fro the website. Lots of people have done this
before. chenge Ticker as required.

Sub Bloomberg()

URL = "http://www.bloomberg.com/apps/quote?ticker="
Ticker = "CLLRUIB%3ALX"


Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True


'get web page
IE.Navigate2 URL & Ticker
Do While IE.readystate < 4 Or _
IE.busy = True

DoEvents
Loop


Colcount = 1
RowCount = 1
Set Table = IE.document.getelementsbytagname("Table")
For TableCount = 2 To 7
For Each Row In Table(TableCount).Rows
'Colcount = 1
For Each cell In Row.Cells
'MyStr = cell.innertext
'For i = 1 To Len(MyStr)
'Range("A" & RowCount) = Mid(MyStr, i, 1)
'Range("B" & RowCount) = Asc(Mid(MyStr, i, 1))
'RowCount = RowCount + 1
'Next i

Cells(RowCount, Colcount) = cell.innertext
Colcount = Colcount + 1
Next cell
Next Row
Next TableCount
IE.Quit
End Sub

"Fidel" wrote:

Hey guys,

Was wondering if anyone knew how to load a webpage into memory and
pull a specific quote using a function.

For example I would want to write a function =BBQuote(CLLRUIB,LX) or
=BBQuote(CSCOMPU,SW)

to get the NAV from these webpages


http://www.bloomberg.com/apps/quote?ticker=CLLRUIB%3ALX
http://www.bloomberg.com/apps/quote?ticker=CSCOMPU%3ASW
.

Little help - Quotes in VBA
 

On Nov 15, 2:40*pm, Fidel wrote:
Hey guys,

Was wondering if anyone knew how to load a webpage into memory and
pull a specific quote using a function.

For example I would want to write a function *=BBQuote(CLLRUIB,LX) or
=BBQuote(CSCOMPU,SW)

to get the NAV from these webpages

http://www.bloomberg.com/apps/quote?...r=CSCOMPU%3ASW

Fidel...This is fast (good if you're doing a lot of these) and
simple...Ron

Sub quotes()
my_url = "http://www.bloomberg.com/apps/quote?ticker=CLLRUIB%3ALX"
Set my_obj = CreateObject("MSXML2.XMLHTTP")
my_obj.Open "GET", my_url, False
my_obj.send
my_var = my_obj.responsetext
Set my_obj = Nothing

pos_1 = InStr(1, my_var, "QuoteTableData", vbTextCompare)
pos_2 = InStr(pos_1, my_var, "", vbTextCompare)
pos_3 = InStr(pos_2, my_var, "<", vbTextCompare)
my_NAV = Mid(my_var, 1 + pos_2, pos_3 - (1 + pos_2))

' now do what you want with my_NAV

End Sub