Problem Statement
Sencha Charts has got CandleStick Financial Chart. These charts need historical stock market data to draw the series in their own fashion.
Most of the financial charts need historical data for a stock and shall contain the following fields:
- Close – Closing price of the stock for a given date
- Open – Opening price of the stock for a give date
- High – Highest/Max price of the stock for a given date
- Low – Lowest/Min price of the stock for a given date
- Date/Time – Date/Time of the stock data
One way to get this data is download the csv data from the YAHOO or any other finance services on daily basis, parse, store, and serve the Ext JS financial charts through our own web services. However, this has a dependency on the server-side development, which may not be desirable in all the case.
YQL provides web services to query the historical data of stocks, which can be called from an Ext JS code.
In this article, we will learn:
- What is YQL?
- How to use YQL public APIs?
- How to query YAHOO finance tables?
- How to integrate YQL API calls with Ext JS?
- How can we use YQL web services with Ext JS Financial Charts?
Introduction to YQL
The YQL Web Services enable applications to query, filter, and combine data from different sources/tables. YQL statements have a SQL-like syntax, which shall look familiar to any developer with database experience. Following key capabilities of YQL are of interest to us:
- YQL includes pre-defined tables for popular Yahoo Web services such as Flickr, Social, MyBlogLog, and more
- YQL can access services on the Internet that output data in the following formats: HTML, XML, JSON, RSS, Atom, and microformat.
- YQL has also got financial tables related to stocks, which are available in community tables
YQL has two types of services
- Public API: all community tables can be queried with this service No api key is required to invoke them.
- OAuth API: private tables data can be queried with this service. API key is required. You may read more about it here.
In this article, we will work with finance tables.
Getting Started
YQL financial tables can be queried through public API for Sencha financial charts.
Since, the request to YQL is going to be a Cross-Domain Request, we will use JsonP proxy or Ext.data.JsonP class provided by Ext JS/Touch, which will help us to query the YQL finance data for the charts.
Try Query in YQL Console
- Launch YQL Console in your browser.
- Select Show Community Tables check box on the top left and search for finance.historicaldata table.
- Click on the table, you can see the default query in the YOUR YQL STATEMENT text area.
- Click on the Test button. You shall see the XML/JSON response.
At the bottom you can see the url generated for this query in THE REST QUERY area. In this url you can see three parameters:
- q: query to fetch the data from the table
- format: json/xml
- env: store://datatables.org/alltableswithkeys . Environment variable to fetch data from community tables.
Take inline sample code from CandleStick Series Documentation and generate a chart.
In this sample data is added inline to the store and proxy is not configured.Let us configure a JsonP proxy for the same store now and remove the inline data , as shown below:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
proxy: { type: 'jsonp', url: 'https://query.yahooapis.com/v1/public/yql', /** YQL public api url **/ reader: { type: 'json', rootProperty: 'query.results.quote' /** YQL api returns actual data in quote field **/ }, extraParams: { env: 'store://datatables.org/alltableswithkeys', /** env and format are always same for this kind of queries, add them in extraParams. **/ format: 'json' } } |
Now call store.load with the query as q in parameters as below.
1 2 3 4 5 |
store.load({ params:{ q :'select * from yahoo.finance.historicaldata where symbol = "YHOO" and startDate = "2015-05-01" and endDate ="2015-05-19"' } }); |
This will load the data of YAHOO stock from May 1st 2015 to 19th May 2015.
You can see the sample form below, which can accept the input for Symbol ,StartDate & EndDate and load the store as shown below:
Query built and passed like this.
You may Get the code from Sencha Fiddle. Give it a try and let me know how did it go!
Summary
In this article , we have learned about YQL and how to use YQL services to load the data into a Sencha CandleStick Chart.
References
- YQL Console
- YQL Guide
- Code on Sencha Fiddle
http://sqall.co is another new tool that is similar to yql. It lets you run SQL on APIs, JSON, XML, RSS and then let’s you send the data to a CSV file or database.