This tutorial shows how to link your application to Yahoo Finance API via Yahoo! Query Language (YQL). Yahoo has lots of stock market data, and you can get full access to it by following this tutorial!
YQL stands for Yahoo! Query Language and like SQL it’s a nice way to select data from a repository. Yahoo! describes it as:
The Yahoo! Query Language is an expressive SQL-like language that lets you query, filter, and join data across Web services. With YQL, apps run faster with fewer lines of code and a smaller network footprint.Yahoo! and other websites across the Internet make much of their structured data available to developers, primarily through Web services. To access and query these services, developers traditionally endure the pain of locating the right URLs and documentation to access and query each Web service.With YQL, developers can access and shape data across the Internet through one simple language, eliminating the need to learn how to call different APIs.
Yahoo has quite a nice set of stock data on Yahoo Finance. If you go look at a quote such as AAPL (Apple) you can see all the data that is available. And the great news is that all that data is available via the Yahoo Finance API: YQL. (well ok YQL is great for many things but this one is sure nice.)
When you write a YQL statement it looks similiar to this:
select * from yahoo.finance.stocks where symbol=”aapl”
That query gets all the stock information for Apple (AAPL).
YQL works through a REST API. You pass the REST API your query and other optional parameters such as the format to return your query in: XML or JSON.
So the full URL looks like so:
You can see that the query is passed in there as the QueryString “q” and the query is HTML encoded. XML is the default format so I didn’t specify it, but you could add &format=json to get JSON data if you prefer it to XML.
The full source code for a c# stock application I call CardStock is available, so grab it and follow along!
Download the Source CodeYQL does all the hard work for us, so the app isn’t complicated and very easy to follow. The most interesting part is the YahooStockEngine class. This class calls the Yahoo Finance API (YQL) and gets the result:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
|
using System;
using System.Collections.ObjectModel;
using System.Linq;
using System.Xml.Linq;
using Jarloo.CardStock.Models;
namespace Jarloo.CardStock.Helpers
{
public class YahooStockEngine
{
private const string BASE_URL = "http://query.yahooapis.com/v1/public/yql?q=" +
"select%20*%20from%20yahoo.finance.quotes%20where%20symbol%20in%20({0})" +
"&env=store%3A%2F%2Fdatatables.org%2Falltableswithkeys";
public static void Fetch(ObservableCollection<Quote> quotes)
{
string symbolList = String.Join("%2C", quotes.Select(w => "%22" + w.Symbol + "%22").ToArray());
string url = string.Format(BASE_URL,symbolList);
XDocument doc = XDocument.Load(url);
Parse(quotes,doc);
}
private static void Parse(ObservableCollection<Quote> quotes, XDocument doc)
{
XElement results = doc.Root.Element("results");
foreach (Quote quote in quotes)
{
XElement q = results.Elements("quote").First(w => w.Attribute("symbol").Value == quote.Symbol);
quote.Ask = GetDecimal(q.Element("Ask").Value);
quote.Bid = GetDecimal(q.Element("Bid").Value);
quote.AverageDailyVolume = GetDecimal(q.Element("AverageDailyVolume").Value);
quote.BookValue = GetDecimal(q.Element("BookValue").Value);
quote.Change = GetDecimal(q.Element("Change").Value);
quote.DividendShare = GetDecimal(q.Element("DividendShare").Value);
quote.LastTradeDate = GetDateTime(q.Element("LastTradeDate").Value + " " + q.Element("LastTradeTime").Value);
quote.EarningsShare = GetDecimal(q.Element("EarningsShare").Value);
quote.EpsEstimateCurrentYear = GetDecimal(q.Element("EPSEstimateCurrentYear").Value);
quote.EpsEstimateNextYear = GetDecimal(q.Element("EPSEstimateNextYear").Value);
quote.EpsEstimateNextQuarter = GetDecimal(q.Element("EPSEstimateNextQuarter").Value);
quote.DailyLow = GetDecimal(q.Element("DaysLow").Value);
quote.DailyHigh = GetDecimal(q.Element("DaysHigh").Value);
quote.YearlyLow = GetDecimal(q.Element("YearLow").Value);
quote.YearlyHigh = GetDecimal(q.Element("YearHigh").Value);
quote.MarketCapitalization = GetDecimal(q.Element("MarketCapitalization").Value);
quote.Ebitda = GetDecimal(q.Element("EBITDA").Value);
quote.ChangeFromYearLow = GetDecimal(q.Element("ChangeFromYearLow").Value);
quote.PercentChangeFromYearLow = GetDecimal(q.Element("PercentChangeFromYearLow").Value);
quote.ChangeFromYearHigh = GetDecimal(q.Element("ChangeFromYearHigh").Value);
quote.LastTradePrice = GetDecimal(q.Element("LastTradePriceOnly").Value);
quote.PercentChangeFromYearHigh = GetDecimal(q.Element("PercebtChangeFromYearHigh").Value); //missspelling in yahoo for field name
quote.FiftyDayMovingAverage = GetDecimal(q.Element("FiftydayMovingAverage").Value);
quote.TwoHunderedDayMovingAverage = GetDecimal(q.Element("TwoHundreddayMovingAverage").Value);
quote.ChangeFromTwoHundredDayMovingAverage = GetDecimal(q.Element("ChangeFromTwoHundreddayMovingAverage").Value);
quote.PercentChangeFromTwoHundredDayMovingAverage = GetDecimal(q.Element("PercentChangeFromTwoHundreddayMovingAverage").Value);
quote.PercentChangeFromFiftyDayMovingAverage = GetDecimal(q.Element("PercentChangeFromFiftydayMovingAverage").Value);
quote.Name = q.Element("Name").Value;
quote.Open = GetDecimal(q.Element("Open").Value);
quote.PreviousClose = GetDecimal(q.Element("PreviousClose").Value);
quote.ChangeInPercent = GetDecimal(q.Element("ChangeinPercent").Value);
quote.PriceSales = GetDecimal(q.Element("PriceSales").Value);
quote.PriceBook = GetDecimal(q.Element("PriceBook").Value);
quote.ExDividendDate = GetDateTime(q.Element("ExDividendDate").Value);
quote.PeRatio = GetDecimal(q.Element("PERatio").Value);
quote.DividendPayDate = GetDateTime(q.Element("DividendPayDate").Value);
quote.PegRatio = GetDecimal(q.Element("PEGRatio").Value);
quote.PriceEpsEstimateCurrentYear = GetDecimal(q.Element("PriceEPSEstimateCurrentYear").Value);
quote.PriceEpsEstimateNextYear = GetDecimal(q.Element("PriceEPSEstimateNextYear").Value);
quote.ShortRatio = GetDecimal(q.Element("ShortRatio").Value);
quote.OneYearPriceTarget = GetDecimal(q.Element("OneyrTargetPrice").Value);
quote.Volume = GetDecimal(q.Element("Volume").Value);
quote.StockExchange = q.Element("StockExchange").Value;
quote.LastUpdate = DateTime.Now;
}
}
private static decimal? GetDecimal(string input)
{
if (input == null) return null;
input = input.Replace("%", "");
decimal value;
if (Decimal.TryParse(input, out value)) return value;
return null;
}
private static DateTime? GetDateTime(string input)
{
if (input == null) return null;
DateTime value;
if (DateTime.TryParse(input, out value)) return value;
return null;
}
}
}
|
The Quote class shown in the above code is the Model or state-bag. It is just a container for the data that implements INotifyPropertyChanged so it can be bound to the UI easily.
This tutorial doesn’t really do YQL justice, it’s an amazing tool that seems to have gone rather unnoticed by the development community. Whats interesting to note is that the YQL table were using in this query was created by other developers like you and not Yahoo! You can make your own queries for a wide variety of things.
Yahoo has some great info on YQL on the Yahoo Developer Network. Go check out the console and be sure to check “Show community tables”, it’s a small link on the right sidebar, checking that will let you see so much more.
If you liked this tutorial please leave a comment to let me know.