To do all this via VBA requires a bit of code as you need to parse an
HTML string from the web site to gain the data you are looking for. I
would love to send you an attachment so you can see the process first
hand, but attachments cannot be sent to the group. I work for a book
distributor so we are constantly checking ISBN'S on our client's web
sites, such as Amazon or Overstock. However, I do everything via VBA due
to how the process is handled over here.
There are two things you need to do since you are new to this.
1. Loop through your spreadsheet to get each stock quote and send
it's value to the Bloomberg website
2. Parse the returned HTML string for the data you want, in this
case closing prices.
Loop through the spreadsheet.
Dim lRange as Long
Dim l as Long
Dim sQuote as String
lRangeCount = Cells(Rows.Count, "A").End(xlUp).Row 'Finds last row with
data in it
'Assuming your quotes are in column A, other wise identify a different
column
Range("A1").Select 'Identify first cell with quote symbol
sQuote = ActiveCell.Value 'Get quote
For l = 0 To lRange
"Add other code here"
Debug.Print sQuote
ActiveCell.Offset(1,0).Select 'Moves to next row
Next
Okay, this is a basic structure of your For Loop statement. It
identifies how many rows of quotes you have on the spreadsheet, and
loops through each row grabbing each quote. Test this first until it
works the way you want it before moving onto parsing HTML data. You may
need to change the count at the beginning of the For Loop structure. To
test data such as this within VBA use the Debug.Print statement, and
show your immediate window by selecting "View" - "Immediate Window" from
your menu bar.
Sending data to a web site is surprisingly simple, it's pulling the
precise data that involves some thought. Before doing anything you need
to set a reference. Select "Tools" - "References" from the menu bar, and
select the "Microsoft XML, 2.0" reference. Now you can send data to the
web.
After testing the loop statement above, add the following to your code.
Dim X as MSXML.XMLHTTPRequest
Dim strURL as String
Dim strResults as String
Set X = New MSXML.XMLHTTPRequest
strURL = "http://www.Address.com//product/product.asp?sku=" & sISBN
Call X.Open("GET", strURL, False)
X.Send (Null)
strResults = X.responseText
'Here's the trick, manually go to the web page that returns the data you
are looking for. The one after you send the stock quote. Look at the
return URL, you should see the symbol within the URL. Use this final URL
within your code, as shown, and enter the symbol this way. By placing
this statement within your "For Loop" you will send each symbol to their
server and be able to get the return data.
The rest involves parsing the string data (strResults). Once again, go
to the final URL manually within your web browser. Select "View" -
"Source", this will bring up a notepad window of the HTML content. Here
you need to find key words or phrases for where the data is situated.
Such as "Closing Price" or whatever word they use that brings you to the
final number. You then need to use the "INSTR" function, use help to
learn how to use this, find the starting and ending position of the
price, and finally bring that out to your spreadsheet. When you look up
the InStr function you will understand more about returning the position
within a string.