Stock quotes, CSV, XQuery and Web services

Not long ago I could rely on a variety of free Web services to fetch the semi-live quotes of my preferred stock tickers. Unfortunately, one by one all those Web services have either disappeared or started requiring a subscription... and for writing a few demos now and then, the subscription model doesn't work that well for me.

I started thinking about alternatives; one option could be to fetch an HTML page from one of the many Web sites that expose stock quotes; but then extracting the information I'm looking for, reformatting it, or mashing it up or reshaping it in a different XML structure would be too painful. HTML layouts change frequently, and navigating HTML even after converting it to well formed XML (XHTML) is usually error prone.

When I mentioned that to one of our lead engineers, Tony Lavinio pointed out that Yahoo! exposes a service which returns a comma separated value (CSV) file given a list of tickers and a few options (that you can find described in a few places on the Internet). It's as simple as invoking a URL; for example, try this:

http://finance.yahoo.com/d/quotes.csv?s=PRGS,AAPL,JAVA,MSFT&f=snl1

If Microsoft Excel is installed on your computer, you will most likely get the results back in an Excel spreadsheet. The options at the end control how many and what kind of "columns" you get back ("s" is the symbol, "n" is the company name, "l1" is the latest value). There are more options, as I mentioned; the following URL will return you more information for each stock ticker:

http://finance.yahoo.com/d/quotes.csv?s=PRGS,AAPL,JAVA,MSFT&f=snl1chgopvd1t1jkxerba

Well, OK; that's interesting; but how does it help me to create my own HTML mash-ups, or my own XML reports? The Yahoo! service is very nice, but it returns a CSV format, not XML - which, as you can guess, is what I would like to manipulate. Once again XML Converters come to the rescue: open your Stylus Studio IDE, create a new XQuery and copy and paste this:

[cc lang="xquery" lines="2" height="500"]doc("converter:CSV?http://finance.yahoo.com/d/quotes.csv? s=PRGS,AAPL,JAVA,MSFT&f=snl1")[/cc]

This time you'll get an XML document back:

[cc lang="xquery"]

PRGS PROGRESS SOFTWARE 30.07

AAPL APPLE INC 168.16

JAVA SUN MICROSYSTEMS 15.78

MSFT MICROSOFT CP 30.42

[/cc]

That's something I can use to create my own HTML content or my XML report! I can just change the XQuery a bit to get that XML formatted into an HTML table, for example:

[cc lang="xquery"]

{ for $row in doc("converter:CSV?http://finance.yahoo.com/d/quotes.csv?s=PRGS,AAPL,JAVA,MSFT&f=snl1hg")/table/row return

}

{ (:s symbol:) $row/column.0/text() }

{ (:n name:) $row/column.1/text() }

{ (:l1 last value:) $row/column.2/text() }

{ (:h day’s high:) $row/column.3/text() }

{ (:g day’s low:) $row/column.4/text() }

[/cc]

The result will look something like this:

PRGS	PROGRESS SOFTWARE	29.58	29.87	29.58
AAPL	APPLE INC	167.527	168.00	167.05
JAVA	SUN MICROSYSTEMS	15.64	15.70	15.60
MSFT	MICROSOFT CP	30.46	30.64	30.43

Now, what if I want to create my own Web service that returns information about one or more stock tickers? If you have read this blog entry about how to expose and consume Web service with DataDirect XQuery, you will know that creating an XQuery-based stock quote Web service is as easy as creating a simple XQuery with one external variable that accepts a list of stock tickers:

getQuotes.xquery:[cc lang="xquery"] declare variable $tickers as xs:string external; { for $row in doc(concat("converter:CSV?http://finance.yahoo.com/d/quotes.csv?s=", $tickers, "&f=snl1hg"))/table/row return

{ $row/column.0/text() } { $row/column.1/text() } { $row/column.2/text() } { $row/column.3/text() } { $row/column.4/text() }

}[/cc]

Deploying that XQuery in the DataDirect XQuery Web service framework will expose a new WSDL/SOAP operation, and a new REST service that we can use from any environment that supports Web service invocation. Do you want to try it? Point your browser at:

http://examples.xquery.com/stock-quotes

From there you can retrieve the WSDL corresponding to the XQuery above (getQuotes.xquery), or try the service itself through the Web interface itself! To achieve that I didn't do any more coding than writing the XQuery listed above! As you can see, I also added there a second XQuery (getQuotesEx.xquery) which allows you to specify different options:

getQuotesEx.xquery:[cc lang="xquery"] declare variable $tickers as xs:string external; declare variable $options as xs:string external; { for $row in doc(concat("converter:CSV?http://finance.yahoo.com/d/quotes.csv?s=", $tickers, "&f=", $options))/table/row return $row }[/cc]

The XML returned might be formatted in a more pleasant way adding some translation of each option in a reasonable element name returned as result, but I'll leave that exercise to someone else.

Thanks to the power and flexibility of DataDirect XQuery and XML Converters, and thanks to the Yahoo! Finance CSV service, I now have again Web services I can use to retrieve all the information I need about any stock ticker!