« Basic Functions: HLOOKUP | Main | Webquery: Scraping FOREX quotes from Yahoo »
November 05, 2004
Webquery: Scraping stock quotes from MSN
In a previous entry we imported data from an HTML file on the local disk. In this example, we will extract stock quotes from an MSN site and import the data into OOo Calc spreadsheet.We will import the stock quote information from this MSN webpage. A screen shot is shown below.
For this example, we import the data into the spreadsheet as before with Insert - External Data . The URL is http://moneycentral.msn.com/scripts/webquote.dll?ipage=qd&Symbol=INTC and the table we need to select is HTML_15.
You also need to make sure your proxy server settings are correct under Tools - Options (Internet)
Now, we can choose to have OOo Calc import this data at predefined regular intervals. This is useful - given the constantly changing nature of the stock prices. Also, each time that the user opens the document, OOo Calc will prompt to update the external links. Basically, what we have created is a 'window' to an external document. It can be a snapshot - or it can regularly monitor the target - updating accordingly.
Having extracted the data from the external website in it's'raw' form, we can use VLOOKUP to grab any piece of data we want. This is shown below.
Posted by Dave at November 5, 2004 03:30 AM
Comments
Hi Dave
Thanks for sharing your insight and experience with OOo with the rest of us. I was able to make this Webquery example work on my computer with very little effort.
Norm
Posted by: Norm Hall at November 11, 2004 04:46 PM
In Excel you can paramaterize a web query, so that for example you could have Excel prompt you for the stock symbol. You would just change the appropriate part of the URL, i.e. INTC to ["enter stock symbol"] and voila. Furthermore you can have it pick up the parameter value from a cell, which you could arrange to have a drop-down list of different stock symbols. Now, I really want to be able to do that in OO. Any ideas??
Posted by: David Chown at December 8, 2004 08:29 PM
In case You want to delete the link to the external datasource:
Highlight the cell (or find it with the Navigator), "Edit" -> "Links", than select the link and "Break" it.
Posted by: sessy at February 8, 2005 01:42 PM
I tried this method with the MSN Currency exchange rates pages, such as:
http://moneycentral.msn.com/investor/market/crncconverter.asp?dAmt=1&iCurIdFrom=2&iCurIdTo=1
I am using OOffice Calc 2.0
I can enter the URL, but when I press Enter, the only table it offers me is called Top of Page.
When i look at the page source I see other tables listed, but the OOffice Calc Insert - Link to External Data dialog does not offer these to me.
What else could I try?
Posted by: Maurice Hilarius at October 30, 2005 10:59 AM
Wouldn't work with MSN money,but when I tried it with IWON Money, after pasting the link, I got a long list of tables/ranges. I tried the HTML_15 but after clicking okay, no info was inserted. Cell was still blank.
Posted by: Charles Morel at January 7, 2006 09:39 PM
I am able to put in the URL as stated in the tutorial but I'm not able to specify HTML_15
When I hit the enter key I get "error reading from the internet.Server error message:Could not read status line: Connection was closed by server"
And I can't click in the "Available tables/ranges" form
Posted by: joe at February 18, 2006 08:09 PM