Over the last few days, I’ve been working on a little side project, mainly to get familiar with some things that you’d find on a job advertisement.  PHP frameworks, Javascript, API development, the usual types of things. One of the things that I wanted to do is to use some financial API’s to collect some basic stock data. Biographical information, a brief stock performance history, a week or two at the most. Nothing that would be either immensely detailed, nor useful for any sort of data mining purpose. Figured there had to be some free to use API’s out there that had the usual traffic restrictions, but were otherwise well documented and useable.

Ah optimism; you’ve failed me again.

First the good side of the coin. There are two free to use APIs that only have a limitation on traffic, which for anyone thats’ used most API’s out there is a typical restriction. Yahoo Finance API, and Googles Finance API. Both have their strenghts, their weaknesses, and why neither can meet my need even when combined together.

My first choice on doing research was Google Financial API. Most of the documentation seems to be geared more towards portforlio management, but digging deep within the bowels, I found how I could retrieve stock data just by passing it a stock symbol. have a look at this URL for getting data about Google’s stock.

http://www.google.com/ig/api?stock=GOOG

now compare that with the novella that is yahoo’s API call to get today’s data.

http://query.yahooapis.com/v1/public/yql?q=select%20*%20from%20yahoo.finance.quotes%20where%20symbol%20in%20(%22GOOG%22)&diagnostics=true&env=http%3A%2F%2Fdatatables.org%2Falltables.env

Stark contrast no? Both provide a plethora of information for today’s events, although Google’s is more straight forward I find for the stock market novice. Both deliver their data in xml, which makes it nice and easy to parse with PHP.  So getting today’s events and pricing data? Not a problem.

However, the problems start showing up when you attempt to get historical data. The URL that I was using for current prices doesn’t work for historical data, or at least so I’ve found. Again as it’s geared towards protfolio management, it’s a bit of a hack to get data. However I did find one possibility with this URL

www.google.com/finance/historical?q=AAPL&startdate=2011-03-01&enddate=2011-03-11&output=csv

That csv parameter? It’s required. In fact It seems to be the only way to retrieve data. NO xml, no json, no easy enjoyable way to parse data. While I could sort of see the reason is say you were grabbing a year at a time…who does that with an API enough times to where this would save time and bandwidth? Basically it makes it a much more cumbersome chore then just using the same xml data parser code.

Move over to yahoo’s implementation and while they offer both xml or json (hooray!) their syntax for url building uses YQL, which is a mixed blessing. On one hand, YQL is an awful lot like SQL, so those that have used that should be all right with converting that skillset over. However it makes the query string extremely cumbersome, especially if you just want to do something simple like pass a symbol and dates. A good example is above, where you can see the massive bulk that is needed just to get symbol day for the day. That being said, yahoo has a great query string builder for their financial and well….everything else on their site.

In the end, I’ll probably stick with Yahoo Finance, just because of the ability to deliver historical data as xml or json instead of csv. It’s not a perfect solution, I still have to mash two different systems together to get the data I needed, but it’s better then nothing. That being said, if anyone from Yahoo Finance, Google finance, or someone that has access to stock market data streams and wants to become quite popular with developers that don’t have the resources of your typical Bloombergs to purchase stock history (and would be like putting a hammer to kill an ant regardless), a free, open source API for financial data would be absolutely super. At least one where you dont’ need to hack two together and still have features needed to boot. Plus such a thing would have this developer not looking like the guy in this articles thumbnail picture. :D