Logo 
Search:

MS Office Answers

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds
  Question Asked By: Klarissa Schmidt   on Sep 13 In MS Office Category.

  
Question Answered By: Ty Thompson   on Sep 13

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.

Share: 

 

This Question has 10 more answer(s). View Complete Question Thread

 
Didn't find what you were looking for? Find more on Extracting Closing Prices Or get search suggestion and latest updates.


Tagged: