Tuesday 31 December 2013

11 RIL stock data from Quandl - Filtered on a range of dates

This is an improvement on the chart shown in the previous post where NSE data was picked up in CSV format from Quandl - an excellent source of global timeseries data -- and shown in a candlestick format. Since the data that we are plotting this time, Reliance share prices, goes back a long time. We will be using a RangeFilter to choose the start and end dates of our plot. The big challenge in this case was to convert the date data obtained from Quandl which was in STRING format into the DATE format that is required by the  RangeFilter. This was done by using the code very kindly provide by ASGALLANT.The final chart is seen on this html page and also in this blog page.

Vizualyse PK

   <title>Vizualyse PK</title>
   <script src="https://www.google.com/jsapi"></script>
   <script src="http://code.jquery.com/jquery-1.10.1.min.js"></script>
   <script src="http://prithwis.x10.bz/charts/jquery.csv-0.71.js"></script> 
<!--   <script src="https://jquery-csv.googlecode.com/files/jquery.csv-0.71.js"></script> -->
   <script type='text/javascript'>
   // load the visualization library from Google and set a listener
   google.load("visualization", "1", {packages:["corechart","controls"]});
   function drawCandlefromQuandlPK(){
   var dashboardPK = new google.visualization.Dashboard(
   var controlPK = new google.visualization.ControlWrapper({
     'controlType': 'ChartRangeFilter',
     'containerId': 'div_controlPK',
     'options': {
       // Filter by the date axis.
       'filterColumnIndex': 0,
       'ui': {
         'chartType': 'LineChart',
         'chartOptions': {
  //         'chartArea': {'width': '90%'},
           'hAxis': {'baselineColor': 'none'}
         // Display a single series that shows the closing value of the stock.
         // Thus, this view has two columns: the date (axis) and the stock value (line series).
         'chartView': {
           'columns': [0, 4]
         // 1 day in milliseconds = 24 * 60 * 60 * 1000 = 86,400,000
         'minRangeSize': 86400000
     // Initial range: 2007-11-31 to 2008-09-31.
     'state': {'range': {'start': new Date(2007, 10, 31), 'end': new Date(2008, 8, 31)}}
   var chartPK = new google.visualization.ChartWrapper({
     'chartType': 'CandlestickChart',
     'containerId': 'div_chartPK',
     'options': {
       title: "Reliance Stock Prices at NSE from Quandl",
       // Use the same chart area width as the control for axis alignment.
//       'chartArea': {'height': '80%', 'width': '90%'},
//       'hAxis': {'slantedText': false},
          'vAxis': {'viewWindow': {'min': 0, 'max': 3500}},
       'legend': {'position': 'none'}
     // grab the CSV
         //  $.get("https://www.quandl.com/api/v1/datasets/NSE/RELIANCE.csv?&trim_start=1998-03-20&trim_end=2013-12-27&collapse=weekly&
         $.get("https://www.quandl.com/api/v3/datasets/NSE/RELIANCE.csv??&auth_token=xxxxxxxxxxxxxxxxxxxxx&trim_start=1998-03-20&trim_end=2013-12-27&collapse=weekly&sort_order=desc", function(csvStringPK) {
         // transform the CSV string into a 2-dimensional array
            var arrayDataPK = $.csv.toArrays(csvStringPK, {onParseValue: $.csv.hooks.castToScalar});
         // this new DataTable object holds all the data
            var dataPK = new google.visualization.arrayToDataTable(arrayDataPK);
         // this view selects only a subset of the data, that is columns 0,3,1,5,2 that is appropriate for the candlestick chart
         // more importantly it converts the 0th column from string to date using the example given in http://jsfiddle.net/asgallant/8RFsB/1/
         // the string to date conversion is very important as otherwise the ControlWrapper will not work
            var viewPK = new google.visualization.DataView(dataPK);
                type: 'date',
                label: dataPK.getColumnLabel(0),
                calc: function (dt, row) {
                    // split the string into date and time
                    var valArr = dt.getValue(row, 0).split(' ');
                    // split the date into year, month, day
                    var dateArr = valArr[0].split('-');
                   // create a new Date object from the data
                   // note that we subtract 1 from the month to convert to javascripts 0-index
                   var date = new Date(dateArr[0], dateArr[1] - 1, dateArr[2]);
                  // return the date and the formatted value as set in the original DataTable
                  return {
                      v: date,
                      f: dt.getFormattedValue(row, 0)
          }, 3, 1,5,2]);
        dashboardPK.bind(controlPK, chartPK);
<div id="div_dashboardPK">
<div id="div_chartPK" style="height: 600px; width: 900px;">
<div id="div_controlPK" style="height: 50px; width: 900px;">

an earlier post on dashboards also talks about filters in some detail. however the code is possibly cleaner here.

1 comment:

  1. I was trying to change

    'vAxis': {'viewWindow': {'min': 0, 'max': 500}},


    'vAxis': {'viewWindow': {'min': dataPK.getColumnRange(4).min, 'max': dataPK.getColumnRange(4).max}},

    Any idea why that wouldn't work?