MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Extracting Closing Prices

  Asked By: Ernesto    Date: Feb 19    Category: MS Office    Views: 1037

I have about 800 lines of securities in a spreadsheets and would like
to extract daily mid and closing prices of stocks quotes from Reuters
or Bloomberg using Excel?

Pls advise is this possible? Appreciate that you can point me to this



10 Answers Found

Answer #1    Answered By: Vidhya Iyer     Answered On: Feb 19

Yes, this is possible and though it can be done via VBA you may be able
to achieve this with the use of Excel's Web Query. This is under the
Data - Import External Data menu.

Answer #2    Answered By: Cyka Jansen     Answered On: Feb 19

Thank for your response, I will try that.
But is it possible to schedule the download at a certain, let say at
12.00 mid night, excel will automatically do the download of the prices?

Answer #3    Answered By: Robin Hayes     Answered On: Feb 19

It's probably simplest to put the program into Windows Scheduler, and
have it execute upon opening.

Answer #4    Answered By: Ibtihaj Akhtar     Answered On: Feb 19

How do I do that. appreciate that you can advise.

Answer #5    Answered By: Leonardo Costa     Answered On: Feb 19

In Control Panel there is a folder called Scheduled Tasks, within it is
a wizard to add a program to execute at a certain time. The down side is
your computer must be left on for it to run.

Answer #6    Answered By: Camille Garrett     Answered On: Feb 19

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

Answer #7    Answered By: Olivia Campbell     Answered On: Feb 19

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

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


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

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


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.

Answer #8    Answered By: Hariz Burki     Answered On: Feb 19

Thank you very much for the info and guidance, I will try on the code.

Answer #9    Answered By: Bien Nguyen     Answered On: Feb 19

> I have tried the code, I am stuck at Dim X As MSXML.XMLHTTPRequest
which states User defined type not define, I have set References in
the 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 learn
how to do this.

Thanks for your patience.

Answer #10    Answered By: Freddie Evans     Answered On: Feb 19

Here's an extract of how I use the XMLHTTP library in the SMF add-in:


sURL = "http://some.url.com"
oHTTP.Open "GET", sURL, False
If oHTTP.Status = "200" Then
sData = oHTTP.responseText
sData = "Error"

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

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