Example on how to get Yahoo Finance Data via YQL

[Saxo_]

A friend of mine told me about Yahoo's YQL. I found an example here:

http://www.jarloo.com/get-yahoo-finance-api-data-via-yql/

- that I have moved into a NT indicator (called GetYahooFinanceDataViaYql). It is just a basic example. I'm fairly new to YQL, so I don't know, if there are any limitations to be aware of in terms of how many and how big requests you can fire off during the day.

Although I haven't gone further with the example I think it can be expanded and further build upon, so that it be used to enrich charts with fundamental data or scan large number of symbols for fundamental data or e.g. price gaps on open. To loop over an instrument list see here:

http://www.ninjatrader.com/support/forum/showthread.php?t=37579&highlight=instrumentlist

Note that I during the export tried to remove unneeded references following this thread:

http://www.ninjatrader.com/support/forum/showthread.php?t=40919

So if you have problems importing the script, then I might have to give it another try.

[Updated: I had to give it another try, based on the feedback I got from NinjaTrader_JC. Attached file was updated]

[NinjaTrader_JC]

Hello Saxo_,

Thanks posting this, but the it appears that the packaging is missing a few things. The ".zip" is correct but the folder structure is not the way it is suppose to be. There is an extra folder so it is looking for a "GetYahooFinanceDataViaYql" instead of the Indicators folder so you would want to zip it one folder down.

Also, the Additional Reference has not been added like the "Linq". You may see our Help Guide at the following about adding the Additional References.

http://www.ninjatrader.com/support/helpGuides/nt7/index.html?distribution_procedure.htm

[Saxo_]

Thanks JC. Extra folder removed and missing reference to Linq added. The zip-file in the original post was replaced with the updated version.

/ S.

[Saxo_]

I'm now trying to extract option chains from Yahoo Finance using YQL. I got the inspiration to do so from here:

http://www.mathworks.com/matlabcentral/fileexchange/37981-get-stock-option-chains/content/getOptionChainYQL.m

- although I have not attempted to translate the code line by line. Furthermore I found this useful page, that enables you to build your query statements and see the result (as xml or in a tree structure ... just press the 'Test'-button on the page):

http://developer.yahoo.com/yql/console/?q=select%20%2a%20from%20yahoo.finance.options&env=store://datatables.org/alltableswithkeys#h=select%20option%0Afrom%20yahoo.finance.options%0Awhere%20symbol%20in%20%28%20%22AAPL%22%2C%20%22MSFT%22%29%20%0A%20%20and%20option.type%20%3D%20%22C%22%20%0A

The core part of the code I have made so far looks like this (full code has been attached to the post):

protected override void OnStartUp()
{
ObservableCollection<OptionQuote> OptionQuotes = new ObservableCollection<OptionQuote>();
string[] symbols = new string[] { "AAPL" };
//string[] tickers = new string[] { "AAPL", "MSFT" }; // Q1: How to handle more than one symbol?
string tickers = String.Join("%2C", symbols);
string url = "http://query.yahooapis.com/v1/public/yql?q=select%20*%20from%20yahoo.finance.options%20where%20symbol=%22";
url = url + tickers + "%22"; 
url = url + "AND%20expiration%20in%20(select%20contract%20from%20yahoo.finance.option_contracts%20where%20symbol=%22";
url = url + tickers + "%22)";
url = url + "%0A%09%09&diagnostics=true&env=http%3A%2F%2Fdatatables.org%2Falltables.env";
XDocument doc = XDocument.Load(url);
var optionDictionary = doc.Descendants("option")
// .Descendants("type")
// .Where(e => e.Value == "C") // Q2: How to filter for e.g. 'calls' here?
.ToDictionary( // Q3: How to store in a list with strikePrice, open int ...
w => w.Attribute("symbol").Value,
// w => w.Attribute("strikePrice").Value, // Q4: How to get the strikePrice?
w => w.Attribute("type").Value );
 
foreach(KeyValuePair<String,String> entry in optionDictionary)
{
var optionquote = new OptionQuote(entry.Key.ToString());
optionquote.Type = entry.Value.ToString();
OptionQuotes.Add(optionquote);
}
 
foreach(OptionQuote w in OptionQuotes)
{
Print(w.Symbol);
Print(w.Type);
}
}

I have managed to extract the option symbols (e.g. AAPL130316C00255000) and the option types ('C' or 'P') - but I have the following problem/question (the Q#'s below refers to the Q#'s in the out commented code lines above):

Q1: I would like to query for many symbols in one go (which is supported) - but it seems that the xml returned looks different when I call with one symbol than when I query with more than one symbol (it has one more level when calling with more than one symbol). How can the code be written to overcome this difference?

Q2: In the query it is possible to filter the values returned - but I would also like to be able to use Linq to query the data returned - i.e. so that I can search for 'calls' only or open interest above a certain level.

Q3: How can I store all the information per option symbol in a dictionary - or is there a better way to capture the information (currently I can only store option symbol and option type)?

Q4: I have not succeeded in getting the strike price, last price, change, bid, ask or open interest (I get an error when I try to access these like I do with 'type'). Can anybody show me how I can access these values in the xml?

/ S.

[Saxo_]

Managed to solve the issues:

[SIZE=2][FONT=Courier New][COLOR=#0000ff][SIZE=2][FONT=Courier New][COLOR=#0000ff][SIZE=2][FONT=Courier New][COLOR=#0000ff]protected[/COLOR][/FONT][/SIZE][/COLOR][/FONT][/SIZE][/COLOR][/FONT][/SIZE][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff]override[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff]void[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2][COLOR=#000000] OnStartUp()[/COLOR][/SIZE][/FONT]
[SIZE=2][FONT=Courier New]{[/FONT][/SIZE]
[SIZE=2][FONT=Courier New]List<[/FONT][/SIZE][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff]string[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2]> symbollist = [/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff]new[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2] List<[/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff]string[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2]>(); [/SIZE][/FONT]
[SIZE=2][FONT=Courier New]symbollist.Add([/FONT][/SIZE][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#800000][FONT=Courier New][SIZE=2][COLOR=#800000][FONT=Courier New][SIZE=2][COLOR=#800000]"AAPL"[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2]);[/SIZE][/FONT]
[SIZE=2][FONT=Courier New]symbollist.Add([/FONT][/SIZE][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#800000][FONT=Courier New][SIZE=2][COLOR=#800000][FONT=Courier New][SIZE=2][COLOR=#800000]"MSFT"[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2]);[/SIZE][/FONT]
[/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff]string[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2] tickers = String.Join([/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#800000][FONT=Courier New][SIZE=2][COLOR=#800000][FONT=Courier New][SIZE=2][COLOR=#800000]"%2C"[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2], symbollist.Select(w => [/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#800000][FONT=Courier New][SIZE=2][COLOR=#800000][FONT=Courier New][SIZE=2][COLOR=#800000]"%22"[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2] + w + [/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#800000][FONT=Courier New][SIZE=2][COLOR=#800000][FONT=Courier New][SIZE=2][COLOR=#800000]"%22"[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2]).ToArray());[/SIZE][/FONT]
[SIZE=2][FONT=Courier New]tickers = [/FONT][/SIZE][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#800000][FONT=Courier New][SIZE=2][COLOR=#800000][FONT=Courier New][SIZE=2][COLOR=#800000]"("[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2] + tickers + [/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#800000][FONT=Courier New][SIZE=2][COLOR=#800000][FONT=Courier New][SIZE=2][COLOR=#800000]")"[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2];[/SIZE][/FONT]
[/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff]string[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2] url = [/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#800000][FONT=Courier New][SIZE=2][COLOR=#800000][FONT=Courier New][SIZE=2][COLOR=#800000]"http://query.yahooapis.com/v1/public/yql?q=select%20option%0Afrom%20yahoo.finance.options%0Awhere%20symbol%20in"[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2];[/SIZE][/FONT]
[SIZE=2][FONT=Courier New]url = url + tickers;[/FONT][/SIZE]
[SIZE=2][FONT=Courier New]url = url + [/FONT][/SIZE][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#800000][FONT=Courier New][SIZE=2][COLOR=#800000][FONT=Courier New][SIZE=2][COLOR=#800000]"%20%0A%0A&diagnostics=true&env=store%3A%2F%2Fdatatables.org%2Falltableswithkeys"[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2];[/SIZE][/FONT]
[SIZE=2][FONT=Courier New]XDocument doc = XDocument.Load(url);[/FONT][/SIZE]
 
[SIZE=2][FONT=Courier New]ObservableCollection<OptionQuote> OptionQuotes = [/FONT][/SIZE][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff]new[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2] ObservableCollection<OptionQuote>();[/SIZE][/FONT]
[/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff]var[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2] options = doc.Descendants([/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#800000][FONT=Courier New][SIZE=2][COLOR=#800000][FONT=Courier New][SIZE=2][COLOR=#800000]"option"[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2]);[/SIZE][/FONT]
[/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff]foreach[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2]([/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff]var[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2] option [/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff]in[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2] options)[/SIZE][/FONT]
[SIZE=2][FONT=Courier New]{[/FONT][/SIZE]
[/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff]var[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2] optionquote = [/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff]new[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2] OptionQuote(option.Attribute([/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#800000][FONT=Courier New][SIZE=2][COLOR=#800000][FONT=Courier New][SIZE=2][COLOR=#800000]"symbol"[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2]).Value);[/SIZE][/FONT]
[SIZE=2][FONT=Courier New]optionquote.Type = option.Attribute([/FONT][/SIZE][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#800000][FONT=Courier New][SIZE=2][COLOR=#800000][FONT=Courier New][SIZE=2][COLOR=#800000]"type"[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2]).Value;[/SIZE][/FONT]
[SIZE=2][FONT=Courier New]optionquote.StrikePrice = GetDecimal(option.Element([/FONT][/SIZE][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#800000][FONT=Courier New][SIZE=2][COLOR=#800000][FONT=Courier New][SIZE=2][COLOR=#800000]"strikePrice"[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2]).Value);[/SIZE][/FONT]
[/SIZE][/FONT][FONT=Courier New][SIZE=2][SIZE=2][FONT=Courier New]optionquote.LastPrice = GetDecimal(option.Element([/FONT][/SIZE][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#800000][FONT=Courier New][SIZE=2][COLOR=#800000][FONT=Courier New][SIZE=2][COLOR=#800000]"lastPrice"[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2]).Value);[/SIZE][/FONT]
[/SIZE][/FONT][FONT=Courier New][SIZE=2][SIZE=2][FONT=Courier New]optionquote.OpenInt = GetDecimal(option.Element([/FONT][/SIZE][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#800000][FONT=Courier New][SIZE=2][COLOR=#800000][FONT=Courier New][SIZE=2][COLOR=#800000]"openInt"[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2]).Value);[/SIZE][/FONT]
[SIZE=2][FONT=Courier New]optionquote.LastUpdate = DateTime.Now;[/FONT][/SIZE]
[SIZE=2][FONT=Courier New]OptionQuotes.Add(optionquote);[/FONT][/SIZE]
[SIZE=2][FONT=Courier New]}[/FONT][/SIZE]
[/SIZE][/FONT][FONT=Courier New][SIZE=2][SIZE=2][FONT=Courier New]}[/FONT][/SIZE]
[/SIZE][/FONT]

/ S.

[aleoca333]

Hi newbie here. How do I import and make use of .zip file in the original post?
If I go utilities > import ninjascript archive file > select the downloaded .zip, I get a message saying
'The assemblies 'Microsoft.Office.Interop.Excel, System.Xml.Linq have been added to use the products in the imported assembly' and another messages immediately after 'Import Failed. The Ninjascript Archive file may contain duplicate method names that already exist on your PC or there is a required file missing on either your PC or in the input archive.”

or is there any other way of getting the very useful fundamental data from yahoo? Ninjatrader currently only support stuff like P/E ratio. Is there a way to get the rest of the data like e.g Price/Book ratio and Earnings growth from yahoo finance?

[shanpalaniram]

Not fetching options data

I used the same code and trying to fetch options data. XDocument doc = XDocument.Load(url); is executing successfully but it is not fetching any data. Is there any specific timings at which the data comes? Anything else i need to add in the code? Please assist me on this.

[NinjaTrader_ZacharyG]

Hello shanpalaniram,

Thank you for writing in and welcome to the NinjaTrader Support Forum!

NinjaTrader platform support would not provide support for Yahoo Query Language.

This thread will be left open for those who have experience with working with YQL and NinjaScript to provide any input, however.