Thanks for the info, but in the first place would you assist me howto write a program to extract the data (mid and closing prices)fromthe Bloomberg and schedule the downloading at 12.00am. I am new to VBAcode writing.

To do all this via VBA requires a bit of code as you need to parse anHTML string from the web site to gain the data you are looking for. Iwould love to send you an attachment so you can see the process firsthand, but attachments cannot be sent to the group. I work for a bookdistributor so we are constantly checking ISBN'S on our client's websites, such as Amazon or Overstock. However, I do everything via VBA dueto 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 sendit's value to the Bloomberg website2. Parse the returned HTML string for the data you want, in thiscase 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 withdata in it

'Assuming your quotes are in column A, other wise identify a differentcolumn

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. Itidentifies how many rows of quotes you have on the spreadsheet, andloops through each row grabbing each quote. Test this first until itworks the way you want it before moving onto parsing HTML data. You mayneed to change the count at the beginning of the For Loop structure. Totest data such as this within VBA use the Debug.Print statement, andshow your immediate window by selecting "View" - "Immediate Window" fromyour menu bar.

Sending data to a web site is surprisingly simple, it's pulling theprecise data that involves some thought. Before doing anything you needto set a reference. Select "Tools" - "References" from the menu bar, andselect the "Microsoft XML, 2.0" reference. Now you can send data to theweb.

After testing the loop statement above, add the following to your code.

'Here's the trick, manually go to the web page that returns the data youare looking for. The one after you send the stock quote. Look at thereturn URL, you should see the symbol within the URL. Use this final URLwithin your code, as shown, and enter the symbol this way. By placingthis statement within your "For Loop" you will send each symbol to theirserver and be able to get the return data.

The rest involves parsing the string data (strResults). Once again, goto the final URL manually within your web browser. Select "View" -"Source", this will bring up a notepad window of the HTML content. Hereyou 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 thefinal number. You then need to use the "INSTR" function, use help tolearn how to use this, find the starting and ending position of theprice, and finally bring that out to your spreadsheet. When you look upthe InStr function you will understand more about returning the positionwithin a string.

> I have tried the code, I am stuck at Dim X As MSXML.XMLHTTPRequestwhich states User defined type not define, I have set References inthe VBA Editor to Microsoft XML v2.6 as I cant find Microsoft XML v2.0

I am not sure what I have done wrong. Pls assist as I want to learnhow to do this.

I'd use a later version of the XML library if you have it. You should haveversion 3.0 -- that's what I use for the SMF add-in. I used to use version5.0, but found out quite a few people didn't have it (or version 4.0) ontheir machines and rolled back to 3.0.