Jarloo

Menu

Get Yahoo Finance API Data via YQL

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!

What’s YQL?

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 Finance

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.)

 

YQL 101

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:

http://query.yahooapis.com/v1/public/yql?q=select%20*%20from%20yahoo.finance.quotes%20where%20symbol%20in%20(%22AAPL%22)&env=store://datatables.org/alltableswithkeys

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.

Lets cut some C# code!

The full source code for a c# stock application I call CardStock is available, so grab it and follow along!

[button link=”http://jarloo.com/downloads/CardStock.zip” type=”icon”]Download the Source Code[/button]

YQL 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:

 

YahooStockEngine C# Class

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.

 

More Yahoo Stock API and YQL Info

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.

 

Categories:   Code, Finance

Comments

  • Posted: December 1, 2010 10:16

    Yuriy

    Thanks a lot for this tutorial! It was very helpful for me. Also, please fix code for evaluating LastTradeDate value. Right now it use the whole LastTradeDate XElement string instead of his value. Thank you once again!
  • Posted: December 1, 2010 10:34

    kelias

    Good catch, didn't notice that. It's been fixed thanks!
  • Posted: January 10, 2011 08:10

    R

    Is it possible to publish the java code.
    • Posted: January 10, 2011 21:24

      kelias

      I don't have any Java code available for this just C#. But Since the YQL is REST based it should be easy to build or find code you can use.
  • Posted: January 26, 2011 18:00

    Paul

    is it possible to make the query for a specific date? something like: select * from yahoo.finance.stocks where symbol=”aapl” and date = "01/27/2011" ?
  • Posted: January 26, 2011 23:00

    Mike

    I'm getting an error (400 bad request) when I try to run the Fetch method. Am I missing something?
  • Posted: January 27, 2011 08:15

    kelias

    Mike what happens when you click on the link posted above. Does it work? Because basically that is what the program is doing.
  • Posted: January 27, 2011 08:22

    kelias

    Mike there is another YQL statement you can use to get historical data. (keep in mind these are all open source so you can also make your own, if this doesn't fit your needs). The one I think you want is the yahoo.finance.historicaldata statement. Here is the YQL: select * from yahoo.finance.historicaldata where symbol = "YHOO" and startDate = "2009-09-11" and endDate = "2010-03-10" Here is an example URL using this YQL: http://query.yahooapis.com/v1/public/yql?q=select%20*%20from%20yahoo.finance.historicaldata%20where%20symbol%20%3D %20%22YHOO%22%20and%20startDate%20%3D%20%222009-09-11%22%20and %20endDate%20%3D%20%222010-03-10%22&diagnostics=true&env=store%3A%2F%2Fdatatables.org%2Falltableswithkeys
  • Posted: January 27, 2011 10:27

    Mike

    Kelias, Even when I try to load the URL string into a browser I get the bad request error. I've even tried: USE 'http://www.datatables.org/yahoo/finance/yahoo.finance.quotes.xml' AS yahoo.finance.quotes; SELECT * FROM yahoo.finance.quotes WHERE symbol IN ("GOOG") Oddly, this query does work using the yql console. Just not when I try using it in .NET. Additionally, queries like the sample queries provided on the yql console site do work in .NET (i.e. select * from flickr.photos.search where text="Cat" limit 10.) What I am really trying to accomplish is to get option chain data for specified stocks. Make a gazillion dollars and retire to Costa Rica! :P Any help is greatly appreciated!
    • Posted: January 27, 2011 10:41

      kelias

      Odd. If it works in the browser it should work in .NET. Are you using a proxy? If so you will need to specify that in .NET. If that doesn't work download Fiddler : http://www.fiddler2.com/fiddler2/ . Then run it and launch your browser. Enter the url in the browser that works and see the traffic and where it goes. Then try the same in .NET and note the differences.
  • Posted: January 27, 2011 11:00

    Mike

    No proxy. Fiddler is showing the same traffic routing in ie as .net. I put a post up on the yahoo developer forum. I don't think yahoo is properly resolving the community tables when I run them through my app. Thanks for your code and the help!
  • Posted: January 27, 2011 22:35

    Mike

    So, I did some more meddling and looked under the hood of yahoo.finance.options.xml. At the root of it, all it is really doing is scraping the html to get the options data. The root of the problem I'm having is that yahoo thinks the script is a bot and shuts it down, thus the bad request error I'm receiving. To reproduce, copy and paste the following into your browser: http://query.yahooapis.com/v1/public/yql?q=select%20*%20from%20html%20where%20url%3D%22http%3A%2F%2Ffinance.yahoo.com%2Fq%2Fop%3Fs%3DC%26m%3D2011-02%22%20and%20xpath%3D%22%2F%2Ftable%5B%40class%3D'yfnc_datamodoutline1'%5D%2Ftr%2Ftd%2Ftable%2Ftr%5Btd%5B%40class%3D'yfnc_h'%20or%20%40class%3D'yfnc_tabledata1'%5D%5D%22%0D%0A%0D%0A&diagnostics=true&env=store%3A%2F%2Fdatatables.org%2Falltableswithkeys
  • Posted: January 31, 2011 16:23

    Ninja

    Great post. I spent an hour or so working with this, but I ran out of patience. Not your fault, I am a trader not a web programmer. I have already fought thru Mysql... Maybe a better question, if you happen to know the answer or you can send me in a general direction. I have just finished a MySQL database, but need an efficient way to put data into it. Currently use ms excel -> csv - -> mySql db. Slightly painful but works. Eventually will move to realtime quotes any thought on providers or structure? Thanks in advance.
  • Posted: January 31, 2011 20:13

    kelias

    Hey Ninja, not a problem. My job is working with traders, I write software for commodity trading so I understand what you mean by real-time quotes etc. If your data is coming from stuff you have in Excel either entered by a trader or pulled in by something such as ICE or Bloomberg you will need to write an Excel macro to get it into your MySql. First what you need is the ADO or ODBC driver. These are just connectors that link your excel to your database. You can find them here: http://www.mysql.com/products/connector/ . Download the ODBC one, it should work good with Excel. There are many tutorials that explain how to do it such as this one: http://www.aspfree.com/c/a/Database/Converting-Your-Excel-Worksheet-into-a-Working-MySQL-Database/ Once your connected and you can write data to your MySql Database you should be set. If your looking for ways to send that information to others then you need something more and it gets much more complex. You then need to start looking at messaging. (I can help with this too, but it gets very complex. You will really need an IT guy around if you plan on going into this.)
  • Posted: February 9, 2011 13:08

    Ninja

    Thanks for the info. Just got mysql DB up and running. Will look into ODBC drivers after I take a breather... Was on your site, which lead me to google and the google doc. Google docs has scripts to to download quotes in a spreadsheet. The scripts are rather to use and modify/recode. I was pretty impressed. Thanks for all the help and keep up the good work! :)
  • Posted: March 5, 2011 12:20

    Nick

    Kelias ... I am trying to make a stock analysis program in VB.net. How can I use YQL to download a list of all the symbols in the table? Also, what would be the best way to download historical data, like 6 months or a years worth, for all of those symbols? Thanks for you help. Great article by the way...
  • Posted: March 11, 2011 17:49

    Nick

    Do you monitor these questions? I am still hoping you can help me...
    • Posted: March 11, 2011 20:13

      kelias

      I do. Sorry but I've been very busy lately. Just started two new businesses in the span of two weeks! The answer to your question I figured deserved its own post, so please check out http://jarloo.com/code/api-code/get-historical-stock-data/
  • Posted: April 26, 2011 17:33

    Simon

    Great example! I have gone through the source code but cannot seem to find where it is that you can modify to display volume and other information, other than the last trade price... What am I missing?
  • Posted: April 26, 2011 18:44

    kelias

    Simon the Parse method returns a collection of Quote objects. Those quote objects have the fields your looking for. If you look in the parse method you can see each of the fields I am assigning to the quote object. Try downloading the source code and step through the parse method, and it should become clear.
  • Posted: April 26, 2011 18:52

    Simon

    Kelias, Thanks! I knew the parse method was grabbing all those information, but what I didn't realize was that I needed to modify the xaml file to control what to display. All makes sense now. Thank you!
  • Posted: April 28, 2011 14:41

    Eli

    Great Example!, if I want to get real time data, I understand that I have to pay some 12 $ fee approx to yahoo, but how do I get the account and how is that reflected at the time I'm making the api call?
  • Posted: April 28, 2011 14:59

    Eli

    Soory another question I'm trying to get the proces on options on US Traesury futures, but I haven't been able, using Yahoo I can get the the futures like ZNM11.CBT (Ten-Year US Treasury Note Futur, Chicago Board), do you have any idea how can I get the option prices, even with any other api, google, etc.? Thank so much, Eli
  • Posted: April 28, 2011 15:45

    kelias

    I've never had to deal with treasuries. Does yahoo show them when you look at the option pages? Because there is a nice YQL statement you can use to get option chains from Yahoo. I've only tried it with stocks though.
  • Posted: April 28, 2011 16:06

    Eli

    Thanks Kelly, what about getting the real time data? What should I fo if I want to use this code for getting the real time price? Thansk, Eli
  • Posted: April 29, 2011 07:34

    Eli

    Kelly is there anyway of downloading the chart of astock (the picture itself)? Thanks, Eli
  • Posted: April 30, 2011 23:07

    Eli

    Thanks but: 1) calling that URL from C# where will it left the picture can I "read it" in the xml text file? how? 2) what's the "nice" YQL statement that you mentioned you have used to get option chains from Yahoo wth stocks? Thanks, Eli
  • Posted: May 1, 2011 16:55

    debbie

    Very helpful - A++ in my book.
  • Posted: May 10, 2011 07:00

    OOKUMA

    Many, many thanks for your clear and generous tutorial. It is an example to follow for all programmers.
  • Posted: May 12, 2011 03:47

    Babuli

    Java Code to fetch stock details. import java.io.IOException; import java.io.InputStream; import java.net.MalformedURLException; import java.net.URL; import java.net.URLConnection; import javax.xml.parsers.DocumentBuilder; import javax.xml.parsers.DocumentBuilderFactory; import javax.xml.parsers.ParserConfigurationException; import org.w3c.dom.Document; import org.w3c.dom.Element; import org.w3c.dom.NodeList; import org.xml.sax.SAXException; public class YahooStockPicker { public static void main(String[] args) { YahooStockPicker helper = new YahooStockPicker(); InputStream is = helper.getData("AAPL"); helper.display(is); } public InputStream getData(String stockSymbol) { System.setProperty("http.proxyHost", "proxywest.i-flex.com"); System.setProperty("http.proxyPort", "8080"); String hostName = "http://query.yahooapis.com/v1/public/yql"; String query = "select%20*%20from%20yahoo.finance.quotes%20where%20symbol%20in%20%28%22" + stockSymbol + "%22%29"; String env = "store://datatables.org/alltableswithkeys"; String urlName = hostName + "?q=" + query + "&env=" + env; URL url; InputStream inStrm = null; try { url = new URL(urlName); URLConnection urlCon = url.openConnection(); inStrm = urlCon.getInputStream(); } catch (MalformedURLException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return inStrm; } public void display(InputStream is) { DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance(); DocumentBuilder builder; try { builder = factory.newDocumentBuilder(); Document document = builder.parse(is); NodeList nodes_i = document.getDocumentElement() .getElementsByTagName("quote"); if (nodes_i != null && nodes_i.getLength() > 0) { for (int i = 0; i 0) { for (int i = 0; i < nl.getLength(); i++) { Element el1 = (Element) nl.item(i); if (el1 != null && el1.getFirstChild() != null) { textVal = el1.getFirstChild().getNodeValue(); nodeName = el1.toString().split(":")[0]; System.out.println(nodeName + " : " + textVal); } } } } }
  • Posted: June 28, 2011 07:48

    narendra

    can u give brief details how can get stock details value on my aspx page vth c#... i m new in web services.. how can i implement this code on my website.
  • Posted: June 28, 2011 07:56

    narendra

    give me step by step..instraction..please
  • Posted: June 29, 2011 01:35

    narendra

    i add this stockengin.cs file in my aplication folder app_code... i can access that file using using Jarloo.CardStock.Models; using Jarloo.CardStock.Helpers; on my default.aspx.cs file now can u give me further details for getting stock details......
  • Posted: December 27, 2011 11:30

    Gary

    Great Example. Been looking for something like this for months. Thanks.....
  • Posted: December 28, 2011 17:59

    Kevin Russell

    Can I query Analyst Estimates information? Ex: information on this page: http://finance.yahoo.com/q/ae?s=ALXN Thanks! Kevin
    • Posted: December 29, 2011 07:59

      admin

      As far as I am aware there is no YQL feed for analyst information, but you could always create one.
  • Posted: December 29, 2011 10:23

    Kevin Russell

    I'm new at tryig to pull data from Yahoo. I was looking at the YahooStockEngine.cs class above. Is there a document that lists all items that can be returned? I'm interested in other items as well, like "Growth" and "Surprise" for example. I looked at the web page source view for the PEG Ratio for example: PEG Ratio (avg. for comparison categories)1.54, hoping for a clue to the data element name. Anyway, thanks! Lots of great info here. Kevin
  • Posted: March 3, 2012 16:35

    Wayne D

    Great article!!! It gave me alot of ideas that I can use in creating my personal quotes database. I modified much of the code so that it would reference historical data from Yahoo. Unfortunately things worked a few times then I got an error saying: "The current table 'yahoo.finance.historicaldata' has been blocked. It exceeded the allotted quotas of either time or instructions" My understanding is that this can happen frequently. Guess I'll go back to the CSV method for now.
  • Posted: March 5, 2012 21:39

    Get Stock Data from Google Finance and YQL | Jarloo

    [...] parse the Google Finance API data in C# take a look at the Yahoo Finance version I’ve posted Reading the Yahoo Finance API in C# Share this:EmailPrintFacebookMore stuff you might [...]
  • Posted: March 12, 2012 17:34

    Downloading free financial data « Software Trading

    [...] information in either XML or JSON format, which you can then parse and use however you want. Jarloo has a good [...]
  • Posted: April 7, 2012 18:42

    Naveen

    I am trying to download selected Analyst estimate information for a bunch of stocks. Right now it is tedious and takes hours. Is there a way I could create a table with this information using the YQL ? If so how can i do it. Any help is appreciated. Thanks, Naveen
  • Posted: April 14, 2012 18:35

    Ali

    Hi, I am trying to fetch real time data and insert into the sql database. How can I get tick-tick data during market for a symbol like 'YHOO'? What should be query for this? Is it correct? select * from yahoo.finance.stocks where symbol=”aapl” and datetime > lastReceivedDT Please advise? Ali
  • Posted: April 23, 2012 11:10

    ab

    hi I am trying to retriebe the ticker ST&ER-6B1.MX it includes an ampersand and I am having problems with that Any idea about how to retrieve a Ticker with special characters? I have tried to repleace the special character by its Identity, but it did not work anyway. it sends me the error.. - Query syntax error(s) [line 1:55 mismatched character '' expecting '"'] for the URL http://query.yahooapis.com/v1/public/yql?q=select%20*%20from%20yahoo.finance.quotes%20where%20symbol%20in%20(%22ST&ER-6B1.MX%22)&env=store://datatables.org/alltableswithkeys
  • Posted: May 6, 2012 03:44

    Sushil

    I used the downloaded code but it gives following exception Cannot open 'http://query.yahooapis.com/v1/public/yql?q=select * from yahoo.finance.quotes where symbol in ("AAPL")&env=store://datatables.org/alltableswithkeys'. The Uri parameter must be a relative path pointing to content inside the Silverlight application's XAP package. If you need to load content from an arbitrary Uri, please see the documentation on Loading XML content using WebClient/HttpWebRequest. in YahooStockEngine.cs file. Kindly give me the solution to this problem.
  • Posted: May 26, 2012 07:27

    theniral

    I want to get the real time data from indian stock market and at the same time wanna save it in the sql database... is it possible to do so... as using the yahoo api i m able to get the data in csv format even the live rate which is tick by tick now i wanna directly stream it into my sql database.. some efficient way of doing thanks
    • Posted: May 26, 2012 07:56

      admin

      It is possible but if you stream each tick into the database live it will put a huge strain on your database. Most systems that do this instead batch the data and insert it in blocks. (See bulk uploading) If the data is needed immediately by a client I would suggest using a queuing technology such as RabbitMQ or ZeroMQ to deliver it immediately to your client and then batch the requests to be posted to the database.
      • Posted: May 26, 2012 08:21

        theniral

        okie let me put it this way .. I have developed a software using vb6 in which data for say 30 scrips is there the data that i have is generated through the set of parameters which gives me buying and selling lvls and know when the price has arrived there i need to display the live price in the grid tick by from hwere i comare with my levels and lets the user know if he is suppose to or sell at the current marketprice.
      • Posted: May 26, 2012 09:13

        admin

        Maybe I'm not understanding your system correctly. Is your system a single application that gets the data updates the display and puts the data into the database? Or is it two systems, one that gathers the data and a client that displays the data?
      • Posted: May 26, 2012 09:30

        theniral

        i have a system that reads data from the sql server which data is already present in the database... Now for eg: say SBIN is the stock name and the levels are to buy above 1181 and sell below 1175 now i need the live real time rates to notify or alert when the above mentioned levels are hit or the cmp is near by... with yql if i could display the realtime feed for SBIN in the mshflexgrid the control that dispaly all my above mentioned data then it would be easy for me to do the comparision where the cmp is greater than the above mentioned levels or lower than them... also i have used a time control so there will continuous query to yahoo finance.. hope it makes it gives u a better picture than before... thanks in advance
      • Posted: May 26, 2012 09:43

        admin

        Thanks I believe I understand what your trying to do. Keep in mind the Yahoo feed is not real-time and will be delayed, there is no good real-time feed available for free. The Bats Exchange is your best bet for tick data although it is not free it is probably the most affordable.(<--NOT CORRECT. See my replies above!) That said, I would store the data in memory. When your application first loads query the database and get the historical data, then when your timer fires and you get new information update the flexgrid from memory by adding the new data to the data already stored there that you got earlier from the database. Then once you've updated your display update the database. (would be nice to do this on a separate background thread but VB6 doesn't support multi-threading) This approach means that your application will only read from the database once: at startup. It will keep that snapshot of the data in memory and append to it as new data arrives. All new data will also be written to the database for persistance. This should help limit the number of queries done on your database and will also speed up your application.
      • Posted: May 26, 2012 09:50

        admin

        Actually after digging some more it turns out the BATS Exchange has some high-quality free products for real-time tick data! Take a look here: BATS Exchange Free stock tick data. To be honest I am surprised and excited. The multicast PITCH feed looks great. I'll definitely be trying that one.
      • Posted: May 26, 2012 09:53

        admin

        If you don't need depth of book TOP looks good too and would probably work better for you.
  • Posted: May 26, 2012 09:49

    theniral

    thanks that was really a helpful post from memory usage point of and agree vb6 doesnot support multithreading i do understand thosse are some limitations with vb6.. anyways as u said yahoo doesnt provide real time data it is delayed i probably have to now try from the brokers terminal if i could fetch the data... will post if get any breakthrough.. thanks
  • Posted: June 1, 2012 11:33

    bart

    The problem with Yahoo API is that it breaks constantly. It works in the testing stage or when you're really just messing around. But once you've put the effort into building an application, I would license data from FinancialContent or Xignite
  • Posted: July 3, 2012 12:03

    JIm

    Thank you so much for posting this! Can this same thing be done for yahoo weather ?
    • Posted: July 3, 2012 13:03

      admin

      Yes it can but you need the woeid for each city your looking for. You can get that by going to yahoo weather and selecting the ones you want, and it will be the number at the end of the URL. Then you can call them like this: http://query.yahooapis.com/v1/public/yql?q=select%20*%20from%20weather.forecast%20where%20woeid%3D2502265&diagnostics=true You can see 2502265 is the WOEID for Sunnyvale CA.
      • Posted: July 3, 2012 14:02

        JIm

        Thanks again!
      • Posted: July 4, 2012 09:02

        JIm

        I have looked and found nothing in the YQL that would allow me to receive the data that is not gzip as in the response header. "Content-Encoding: gzip" Do you know of a way to turn that off ? I need the data raw.
  • Posted: August 6, 2012 12:14

    Guhan

    Thank you very much for this guide Is it possible to search multiple symbols at the same time and return an xml document with all the symbols (using your example, both Apple and lets say MSFT)?
  • Posted: August 6, 2012 12:28

    Guhan

    Sorry, I figured it out - for other newbs - I queried this: http://query.yahooapis.com/v1/public/yql?q=select%20*%20from%20yahoo.finance.quotes%20where%20symbol%20in%20%28%22msft,aapl%22%29&diagnostics=false&env=store%3A%2F%2Fdatatables.org%2Falltableswithkeys
  • Posted: August 16, 2012 09:15

    Fred

    Thank you fot this tutorial, it is exactly what I was searching for ! Great explanations too ! Thanks
  • Posted: October 20, 2012 19:36

    Samuel Mburu

    Thanks for posting that link, I was looking for just that. I don't plan on using it with C#, but with nodeJS as a hackday stock ticker of sorts. Thanks again and keep the great posts coming.
  • Posted: November 6, 2012 01:21

    sotavento bcn

    Hello, Neat post. There is an issue along with your website in web explorer, could check this? IE nonetheless is the marketplace leader and a large part of other people will omit your wonderful writing due to this problem.
  • Posted: December 18, 2012 14:28

    Chris

    A small update: you need to add "&env=http://datatables.org/alltables.env" to your urls now (I found this out from http://developer.yahoo.com/forum/YQL/No-definition-found-for-Table-yahoo-finance-quotes/1252596855000-919237d4-ef6d-397c-97d9-68a7f6336f02). So a query example: http://query.yahooapis.com/v1/public/yql?q=select%20*%20from%20yahoo.finance.quotes%20where%20symbol%20in%20(%22YHOO%22%2C%22AAPL%22%2C%22GOOG%22%2C%22MSFT%22)&format=json&env=http://datatables.org/alltables.env It won't work inside the YQL console
  • Posted: December 18, 2012 14:34

    Chris

    Small correction: you can use the YQL console with finance data, but you should do it via the url: http://developer.yahoo.com/yql/console/?q=show%20tables&env=store://datatables.org/alltableswithkeys
  • Posted: February 3, 2013 22:32

    Shiran

    Really a great site with valuable information. Thank you
  • Posted: February 19, 2013 21:10

    Alex

    This is great stuff. I am loading historical price with a pretty dirty html parser. do you know if ysl could be used for loading historics ? Thanks.
  • Posted: February 26, 2013 07:12

    César

    Hello Kellias. I try to get the spanish index IBEX35 (ticker ^IBEX) and I get no data. The ticker is correct since if I use the URL http://finance.yahoo.com/q?s=%5EIBEX I get the data. Any idea? Thanks in advance.
  • Posted: May 3, 2013 07:54

    John

    Hi, I've managed to download a complete sector, industry, and stock list from Yahoo using the YQL API, but I haven't found a source that lists relationships between a stock symbol and the exchange it's traded on. Also, I'm looking for a source that contains the stock "modifiers", e.g. I can get it to send me RCI for Rogers Communications, but it won't include RCI-A or RCI-B. Ideally, what I'd like to end up with is something that tells me that RCI-A.TO is a valid symbol to look up on Yahoo. Or, taking Bank of Nova Scotia as another example, I can't simply look up BNS. I need to look up BNS.TO (BNS.MX and BKN.DE are also valid symbols for Bank of Nova Scotia. Any thoughts? Thanks.
    • Posted: May 3, 2013 08:21

      Kelly Elias

      I don't know of a nice way to do it. You can get symbol lists for free from http://eoddata.com/symbols.aspx but as you mentioned they might not be the same as what Yahoo uses with the .TO extension for Toronto. Although it's ugly you can use the approach mentioned here: http://stackoverflow.com/questions/5246843/how-to-get-a-complete-list-of-ticker-symbols-from-yahoo-finance
  • Posted: September 3, 2013 14:41

    Rajive Jain

    Hi, Is there a way for me to get fundamental financial statements information for a stock ? For e.g. I would like Balance sheet, Cash flow and Income Statements for WFC (Wells Fargo). Is that possible ? Thank you B Rgds Rajive
  • Posted: February 10, 2014 04:13

    Dave

    Thanks, this is a great tutorial and something I have been looking for! A couple of questions: 1) if I change the interval in the ticker, is it re-fetching the quotes at that interval? I changed it and it was certainly updating the "last updated" field but the quotes were remaining the same. I know it's a small sample but I was surprised none of them changed. (thinking about it though I might be in the wrong time zone and the market closed :) 2)how can I add more info into the cards? I added additional text blocks with e.g. Text="{Binding MarketCapitalization}" but although it left the space in the card it didn't supply the value. I tried several fields using the quote.[ElementName] value for the Binding. what else do I need to do? 3)I realise I would also need to change the UI to make it readable, but would it be possible to rather than picking out specific quotes, pull through an entire exchange e.g. Dow, Nasdaq, FTSE 100?
  • Posted: March 5, 2014 15:08

    TraderJoe

    hi! I'm looking for Yahoo's historical data for year end in this format - stock name, date, adjusted closing price For eg MSFT, 12/3`1/2014, 56.74 MSFT, 12/3`1/2013, 48.82 Can some one please help me how to get this info for ALL the stocks/ETFs/Mutual funds Yahoo is tracking. Thanks much for your help, Joe.
  • Posted: May 10, 2014 09:20

    Andrew

    I would like to utilize the CardStock source code that you've posted. Would it be possible to post an example for the very amateur coder? For example, how would one query the stock price and market cap of AAPL? Thanks!
  • Posted: October 11, 2014 17:01

    matt

    yql is UNUSABLE when trying to get stock info. whoever the moron was hat wrote the code should be shot! Yahoo should simply take the sight down. because the public api is so full of bugs in the tags its unbelieveable.
  • Posted: October 31, 2014 14:36

    Anshul Dupare

    Hi! Fantastic tutorial! Can you please tell me that whether it is possible to query a particular stock exchange via the yahoo finance api?
  • Posted: November 20, 2014 17:36

    NOYB

    Getting old data from yahoo.finance.quotes table. Today is 11/20/2014, but table still has yesterdays data (11/19/2014). It was working yesterday. But today queries just return yesterdays data. Is it broken? Did something change that I need to accommodate? Not pleased that this thing just stops working and doesn't seem to be anyway to get a status. Thanks Here is a query (YQL dev console w/show community tables): select * from yahoo.finance.quotes where symbol in ("YHOO","AAPL","GOOG","MSFT") LastTradeDate>11/19/2014 LastTradeTime>4:00pm Heres my URL: http://query.yahooapis.com/v1/public/yql?q=select%20*%20from%20yahoo.finance.quotes%20where%20symbol%20in%20(%22HPQ+INTC%22)&env=store://datatables.org/alltableswithkeys&format=xml
  • Posted: April 16, 2015 10:43

    Pankaja

    I have 2 questions 1. Using your api, is it possible to get a list of Stock tickers say for e.g. all stock symbols starting with T basically alphabetized but not getting all in one shot as there is a limit in the amount of data fetched. I can provide .Net - javascript code to provide a way to send the query to your api, as long as the api provides like a wild card input such as T%. 2. I would like to know how to be able to serialize the output of for e.g. http://finance.yahoo.com/q/op?s=T - right now after I get the web response as a stream and read to end as webResp = (HttpWebResponse)webReq.GetResponse(); if (webResp != null) { // Read the body of the response from the server strm = new StreamReader(webResp.GetResponseStream(), Encoding.ASCII); var serializer = new JavaScriptSerializer(); var jsonObject = serializer.DeserializeObject(strm.ReadToEnd()); results = jsonObject.ToString(); } .... I get an error that says "Invalid JSON primitive at the line where it is trying to deserialize the stream. FYI - Here for this question I am not using your api. I am just creating a web request with the URL that I mentioned above for getting option chaining results for ticker=T and fetching the web response. Any help is appreciated.
  • Posted: April 16, 2015 12:31

    Pankaja

    Hi Kelias, I have 2 questions 1. With your api, is it possible to get a list of ticker symbols (not the whole list), alphabetized for e.g. if I could give symbol='t%' which will basically give me all the tickers beginning with T. 2. Now without your API, just creating a direct webrequest with the URL = http://finance.yahoo.com/q/op?s= 'T' and looking at the response which I convert to a streamreader and I see the results along with a whole bunch of other stuff. But somehow it just errors out while displaying the results. I realized that I somehow have to extract the OptionData from the result. Any help is appreciated. Thanks,