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



<head>
   <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"]});
   google.setOnLoadCallback(drawCandlefromQuandlPK);
//-------------------------------------------------------------------------------------------------------------------------------------------
//-------------------------------------------------------------------------------------------------------------------------------------------
   function drawCandlefromQuandlPK(){
   
   var dashboardPK = new google.visualization.Dashboard(
       document.getElementById('div_dashboardPK'));
//----------------------------------------------------------------------------------------------------------------------------------------       
   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);
            viewPK.setColumns([{
                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);
        dashboardPK.draw(viewPK);
         });
   }
   </script>
</head>
<body>
<div id="div_dashboardPK">
<div id="div_chartPK" style="height: 600px; width: 900px;">
</div>
<div id="div_controlPK" style="height: 50px; width: 900px;">
</div>
</div>
</body>


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

Sunday 29 December 2013

10 Plotting NSE stock price data from Quandl

Quandl is a really fantastic source for all kinds of really useful financial data. For example you can see historical data on Tata Steel stock prices here. We will use this data to plot a Google Candlestick chart that shows open, close, high, low data for the Tata Steel stock both in a regular HTML page and in the blog below.

Google Chart Example


There are couple of interesting things in the following code that are interesting to know about.

  • While pulling the CSV data from Quandl, you can specify the start/end dates and the level of aggregation, that is daily, weekly, monthly
  • Google Candlestick charts expect the data to be in a certain order. Quandl provides the data in a different order and the columns needs to be rearranged. Hence the data table called "data" is converted into a view called "view" where we select columns 0,1,2,3,5 and reorder them into 0,3,1,5,2
  • This view and not the data is used to draw the chart



<head>
   <title>Google Chart Example</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"]});
   google.setOnLoadCallback(drawCandlefromQuandl);
  
   function drawCandlefromQuandl(){
     // grab the CSV
         $.get("https://www.quandl.com/api/v3/datasets/NSE/TATASTEEL.csv?api_key=xxxxxx.xxxxx&collapse=weekly&start_date=2012-12-31&end_date=2014-12-31", function(csvString) {
         // transform the CSV string into a 2-dimensional array
            var arrayData = $.csv.toArrays(csvString, {onParseValue: $.csv.hooks.castToScalar});
         // this new DataTable object holds all the data
            var data = new google.visualization.arrayToDataTable(arrayData);
         // this view can select a subset of the data at a time
            var view = new google.visualization.DataView(data);
            view.setColumns([0,3,1,5,2]);
         var options = {
         title: "Tata Steel NSE Stock Prices from Quandl",
          hAxis: {title: view.getColumnLabel(0), minValue: view.getColumnRange(0).min, maxValue: view.getColumnRange(0).max},
          vAxis: {title: "Price in INR"},
          legend: 'none'
          };
          var chart = new google.visualization.CandlestickChart(document.getElementById('QuandlCandle'));
          chart.draw(view, options);
         });
   }
   </script>
</head>
<body>
   <div id="QuandlCandle" style="width: 900px; height: 500px;"> </div>   
</body>



Quandl provides a huge amount of data and this makes the chart rather clumsy. Our next step would be to see how to provide a control so that you can specify the dates of your data rather than hardcoding the same into the java script.

Monday 23 December 2013

9 Interactive Dashboard with Google Charts with external CSV data

As an extension of the previous post where we used to data in CSV format available from an externally hosted source like Quandl to draw charts, we now see to inject a measure of interactivity into the same chart. The original CSV file containing data on education in India has lots of data columns. In this example, we will see how we can choose any one column of data and draw the corresponding chart either in an HTML page or in this blog. This example is based on the tutorial given in Economistry.


Google Chart Example


Please do note that in the URL pointing to the CSV file, we have added a sorting criteria as otherwise the graph is drawn in a reversed time scale, with newer data in the left and older data in the right.


<head>
   <title>Google Chart Example</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"]});
   google.setOnLoadCallback(drawChartfromCSV);
  
   function drawChartfromCSV(){
     // grab the CSV
         $.get("https://www.quandl.com/api/v3/datasets/MOSPI/EDU_INST_SCHOOL_EXPENDTR_29_1.csv?api_key=xxxxxx...xxx&trim_start=2001-06-30&trim_end=2011-06-30&sort_order=asc", function(csvString) {
         // transform the CSV string into a 2-dimensional array
            var arrayData = $.csv.toArrays(csvString, {onParseValue: $.csv.hooks.castToScalar});
         // use arrayData to load the select elements with the appropriate options
              for (var i = 1; i < arrayData[0].length; i++) {       // i starts from 1, not 0 because the first column is text
            // this adds the given option to both select elements
              $("select").append("<option value='" + i + "'>" + arrayData[0][i] + "</option");
              }
              
         // set the default selection
            $("#range option[value='1']").attr("selected","selected");
         // this new DataTable object holds all the data
            var data = new google.visualization.arrayToDataTable(arrayData);
         // this view can select a subset of the data at a time
            var view = new google.visualization.DataView(data);
            view.setColumns([0,1]);
         var options = {
         title: "EDUCATIONAL INSTITUTIONS, SCHOOLS AND EXPENDITURE - INDIA",
          hAxis: {title: data.getColumnLabel(0), minValue: data.getColumnRange(0).min, maxValue: data.getColumnRange(0).max},
          vAxis: {title: data.getColumnLabel(1), minValue: data.getColumnRange(1).min, maxValue: data.getColumnRange(1).max},
          legend: 'none'
          };
          var chart = new google.visualization.LineChart(document.getElementById('csv2chart'));
          chart.draw(view, options);
          // this part makes the chart interactive
          
          $("select").change(function(){
          
          // determine selected  range - Vertical Axis
          
           var range = +$("#range option:selected").val();
          // update the view
             view.setColumns([0,range]);
          // update the options
          
             options.vAxis.title = data.getColumnLabel(range);
             options.vAxis.minValue = data.getColumnRange(range).min;
             options.vAxis.maxValue = data.getColumnRange(range).max;
             
          // update the chart
             chart.draw(view, options);
          });
         });
   }
   </script>
</head>
<body>
   <div id="csv2chart" style="height: 500px; width: 900px;">
 </div>
<select id="range">Choose your chart</select>
</body>



The original tutorial in Economistry allows one to choose both the domain (horizontal axis) and the range (vertical axis) but given the nature of the data, having anything other than year on the horizontal axis would make no sense.

8 Google Charts from Public CSV datasets

Quandl is a fantastic source of data collected from various agencies, curated with care and then made available either on their website or as CSV or JSON files that can be downloaded or used directly from your application. On registration you get access to your personal key that allows you to access the data more frequently than what is allowed in unregistered access. Registration is free.

In this example, we see how we can access CSV data about education in India from Quandl and publish it either on an HTML page or in blog. This example is based on the excellent tutorial available at Economistry except that the locations of source data and the JS libraries used have been changed so that one does not need to have a dedicated webserver to serve the files.

Google Chart Example


The Javascript library jquery.csv-0.71.js is required to convert CSV files into a format that is acceptable to the Google Data Table. You can download this from this URL, as explained in the Economistry tutorial and then upload it into your own webserver if you want to be assured of being able to access it whenever. Otherwise, if you are on a blog and cannot upload a file, you can link to it directly over the web as shown in the listing below.


<head>
   <title>Google Chart Example</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"]});
   google.setOnLoadCallback(drawChartfromCSV);
  
   function drawChartfromCSV(){
     // grab the CSV
         $.get("https://www.quandl.com/api/v3/datasets/MOSPI/EDU_INST_SCHOOL_EXPENDTR_29_1.csv?api_key=xxxxxxxxxxx..xxx", function(csvString) {
         // transform the CSV string into a 2-dimensional array
            var arrayData = $.csv.toArrays(csvString, {onParseValue: $.csv.hooks.castToScalar});
         // this new DataTable object holds all the data
            var data = new google.visualization.arrayToDataTable(arrayData);
         // this view can select a subset of the data at a time
            var view = new google.visualization.DataView(data);
            view.setColumns([0,2]);
         var options = {
         title: "EDUCATIONAL INSTITUTIONS, SCHOOLS AND EXPENDITURE - INDIA",
          hAxis: {title: data.getColumnLabel(0), minValue: data.getColumnRange(0).min, maxValue: data.getColumnRange(0).max},
          vAxis: {title: data.getColumnLabel(2), minValue: data.getColumnRange(2).min, maxValue: data.getColumnRange(2).max},
          legend: 'none'
          };
          var chart = new google.visualization.LineChart(document.getElementById('csv2chart'));
          chart.draw(view, options);
         });
   }
   </script>
</head>
<body>
   <div id="csv2chart" style="width: 900px; height: 500px;"> </div>   
</body>


In the next example, we will explore how to make these charts more interactive. Stay tuned.

Friday 20 December 2013

7 Creating a Dashboard - Part II

Here we take the same data used in the previous post and convert it into a dashboard. We have replaced the chart with a chart wrapper. Added three filters. Added a dashboard component and bound the three filters to the chart wrapper. You can see the result both in this blog as well as on this regular HTML page.

The Dashboard on this blog

Data Source

sOne can choose one or multiple states. Also one can specify the range of cow and buffalo milk production and so select only the states that have this production.


<head>
    <script src="https://www.google.com/jsapi" type="text/javascript"></script>
    <script type="text/javascript">
     google.load('visualization', '1', {'packages': ['corechart','controls']});
     google.setOnLoadCallback(drawDashBoardMQ);
    
    function drawDashBoardMQ() {
            var queryMQ = new google.visualization.Query(
      'https://docs.google.com/a/yantrajaal.com/spreadsheet/ccc?key=0AhEX55Pfl1eedExUbS1xNzBuQVAyNDJTeG1weFQxbXc&sheet=MilkProduction&range=B2:H37&headers=1');
      queryMQ.setQuery('select B,E,F'); 
      queryMQ.send(handleQueryResponseMQ);
    };
    
    function handleQueryResponseMQ(responseMQ) {
      if (responseMQ.isError()) {
       alert('Error in query: ' + responseMQ.getMessage() + ' ' + responseMQ.getDetailedMessage());
       return;
     }
  
   var dashboardMQ = new google.visualization.Dashboard(document.getElementById('dash_divMQ')); 
 
 // Create a filter, passing some options
 // to filter out some rows based on value of column Cow Milk Total
   var CowMilkRangeSlider = new google.visualization.ControlWrapper({
    'controlType': 'NumberRangeFilter',
    'containerId': 'cowfilter_MQ',
    'options': {
      'filterColumnLabel': 'Cow Milk Total',
      'label' : 'Cow',
      'minValue': 0.0,
      'maxValue': 5000.0
    }
   });
  
  // Create a filter, passing some options
  // to filter out some rows based on value of column Buffalo Milk
   var BuffaloMilkRangeSlider = new google.visualization.ControlWrapper({
    'controlType': 'NumberRangeFilter',
    'containerId': 'buffalofilter_MQ',
    'options': {
      'filterColumnLabel': 'Buffalo Milk',
      'minValue': 0.0,
      'maxValue': 5000.0
    }
   });
  
   // Create a filter, passing some options
   // to filter out rows based on value of column State
   var StateFilter = new google.visualization.ControlWrapper({
    'controlType': 'CategoryFilter',
    'containerId': 'Statefilter_MQ',
    'options': {
      'filterColumnLabel': 'State',
      'ui': {
          'labelStacking': 'vertical',
          'allowTyping': false,
          'allowMultiple': true,
          'caption' : 'Choose State'
        },
     
    }
   });
  
  // Create a chart, passing some options
  // This is a chart wrapper
  var ColChartMQ = new google.visualization.ChartWrapper({
    'chartType': 'ColumnChart',
    'containerId': 'chart_divMQ',
    'options': {
      'width': 900,
      'height': 600,
      'title' : 'Milk Production',
      'hAxis': {title: 'State', titleTextStyle: {color: 'red'}},
    }
  }); 
  // Connect the three filters to the same chart
     dashboardMQ.bind([CowMilkRangeSlider,BuffaloMilkRangeSlider,StateFilter], ColChartMQ);
     
  // Get the data   
    var dataMQ = responseMQ.getDataTable();
    
  // Publish everything through teh dashboard
   dashboardMQ.draw(dataMQ)
   
}

    </script>
  </head>
  <body>
  <h3>
The Dashboard on this blog </h3>
<div id="dash_divMQ">
    <div id="Statefilter_MQ" style="height: 50px; width: 900px;">
</div>
<div id="chart_divMQ" style="height: 600px; width: 900px;">
</div>
<div id="cowfilter_MQ" style="height: 50px; width: 900px;">
</div>
<div id="buffalofilter_MQ" style="height: 50px; width: 900px;">
</div>
</div>
<a href="https://docs.google.com/a/yantrajaal.com/spreadsheet/ccc?key=0AhEX55Pfl1eedExUbS1xNzBuQVAyNDJTeG1weFQxbXc&amp;usp=drive_web#gid=1" target="_blank">Data Source</a>
  </body>



for some reason, the data from the first row, Andhra Pradesh, cannot be seen on the dashboard even though it is visible in previous normal Column Chart !!

A more sophisticated RangeFilter is shown in this post.

7 Creating a Dashboard - Part 1

When  you have a lot of data to be shown on a page, it makes sense to give the viewer an opportunity to filter some of the data so that he or she gets a cleaner view. In this case, we will first draw a rather clumsy Column Chart and then in the next section. The data for the chart is drawn from this spreadsheet. The chart shown below can also be seen in this regular HTML page.

Basic Column Chart Showing All Data

Data Source

Note how we have specified
  • the  Google Docs spreadsheet : https://docs.google.com/a/yantrajaal.com/spreadsheet/ccc?key=0AhEX55Pfl1eedExUbS1xNzBuQVAyNDJTeG1weFQxbXc
  • the sheet : sheet=MilkProduction
  • range : range=B2:H37
  • headers : headers=1
  • columns : query.setQuery('select B,E,F');
  • chart type : var chartMQ = new google.visualization.ColumnChart(document.getElementById('chart_divMQ'));


<head>
    <script src="https://www.google.com/jsapi" type="text/javascript"></script>
    <script type="text/javascript">
     google.load('visualization', '1', {'packages': ['corechart','controls']});
     google.setOnLoadCallback(drawColChartMQ);
    
    function drawColChartMQ() {
      var query = new google.visualization.Query(
      'https://docs.google.com/a/yantrajaal.com/spreadsheet/ccc?key=0AhEX55Pfl1eedExUbS1xNzBuQVAyNDJTeG1weFQxbXc&sheet=MilkProduction&range=B2:H37&headers=1');
      query.setQuery('select B,E,F'); 
      query.send(handleQueryResponseMQ);
      };
    
    function handleQueryResponseMQ(responseMQ) {
      if (responseMQ.isError()) {
       alert('Error in query: ' + responseMQ.getMessage() + ' ' + responseMQ.getDetailedMessage());
       return;
      }
  
    var options = {
          title: 'Milk Production',
          hAxis: {title: 'State', titleTextStyle: {color: 'red'}}
        };

    var data = responseMQ.getDataTable();
    var chartMQ = new google.visualization.ColumnChart(document.getElementById('chart_divMQ'));
    chartMQ.draw(data, options );
}

    </script>
  </head>
  <body>
  <h3>
Basic Column Chart Showing All Data</h3>
<div id="chart_divMQ" style="height: 500px; width: 900px;">
</div>
<a href="https://docs.google.com/a/yantrajaal.com/spreadsheet/ccc?key=0AhEX55Pfl1eedExUbS1xNzBuQVAyNDJTeG1weFQxbXc&amp;usp=drive_web#gid=1" target="_blank">Data Source</a>
  </body>
<br />



In the next post we will convert this into a Dashboard

Wednesday 18 December 2013

6 Showing Addresses on a Google Map

In this example, we wish to show on a  the locations of certain schools in Calcutta whose addresses are known. We begin with a primary source of data and copy the same into a Google Docs spreadsheet. This base data is then copied into a another sheet for pre-processing. This activity is partially manual where we cut, copy and paste the data for 10 schools into one portion of the spreadsheet so that the school name and school address of the schools in question appear adjacent to each other -- in columns C, D of this sheet.

Converting addresses to latitude and longitude is known as GeoCoding and Google Maps provides APIs for this. However we have used a simple Google Apps Script adapted from one found in Amit Agarwals Programmer's Library to calculate the latitude and longitude corresponding to each school address.

Running this script ( the listing of which is given in this post)  generates the positions and these are visible in columns F and G of the pre-processing sheet.

The final, clean data consisting of the latitude, longitude and school name is now copied into the final GeoCodeData sheet for transmission to the HTML page that will display the map.

From here onward, we use the templates that Google provides for Map Visualisation with the exception that in this case our data is not hard coded into the HTML page but is being picked up from the spreadsheet.

The HTML code for this is shown in the post below. Note that we have specified the

  • spreadsheet - through the value of the key, that can be picked up from the URL
  • sheet - in this case sheet=GeoCodeData
  • range - B2:D11
  • there are no headers
  • we use columns B,C,D where B = lat, C= long and D is a text field, in this case school name


This is the map that is produced


We observe that the location of certain schools have not been shown correctly though they are by and large in the general area. This is because of a lack of precision in geocoding because Google could not identify the exact address and settled for a general area based perhaps on the name of the road or the PIN code. This can be manually improved by going to the real Google Map and zooming repeatedly until the address is visible and noting the precise lat/long of that address.

This is the GoogleScript code used in Google Docs to GeoCode addresses into lat/long positions

function pm_geocode_Addresses() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); 
  var locationInfo = sheet.getRange(2, 4, 10, 1).getValues();
  var geoResults, lat, lng;
 
  for (var i = 0; i < locationInfo.length; i++) {
    geoResults = Maps.newGeocoder().geocode(locationInfo[i]);
 
    // Get the latitude and longitude
    lat = geoResults.results[0].geometry.location.lat;
    lng = geoResults.results[0].geometry.location.lng;
    sheet.getRange(i+2, 6, 1, 1).setValue(lat);
    sheet.getRange(i+2, 7, 1, 1).setValue(lng);    
    Utilities.sleep(1000);    
  }
}


This is the HTML code that is embedded in this blog

<head>
    <script type="text/javascript" src="https://www.google.com/jsapi"></script>
    <script type="text/javascript">
      google.load("visualization", "1", {packages:["map"]});
      google.setOnLoadCallback(drawMapBU);
      function drawMapBU() {
        var query = new google.visualization.Query(
      'https://docs.google.com/a/yantrajaal.com/spreadsheet/ccc?key=xxxxxxxxxx&sheet=GeoCodeData&range=B2:D11&headers=0');
      query.setQuery('select B,C,D'); 
      query.send(handleQueryResponseBU);;
      
      function handleQueryResponseBU(response) {
       if (response.isError()) {
         alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
        return;
       }
 
      var data = response.getDataTable();
      var map = new google.visualization.Map(document.getElementById('map_divBU'));
      map.draw(data, {showTip: true});
       }     
      }
    </script>
  </head>
  <body>
    <div id="map_divBU" style="width: 600px; height: 500px"></div>
  </body>


For some reason, the map on this blog is in black and white even though on the normal HTML page the colours are showing properly. What can be reason for this ? If anyone knows, will appreciate a suggestion.

5 State Data in Google GeoChart



In this map, we use data from States ( see the spreadsheet ) and this too can be mapped into a map of India. However state level maps are not available and the data for a particular state is mapped into a point ( or marker) in the state.




Once again we observe the limitations of not being able to show more than two data points for each state.


<head>
    <script type='text/javascript' src='https://www.google.com/jsapi'></script>
    <script type='text/javascript'>
     google.load('visualization', '1', {'packages': ['geochart']});
     google.setOnLoadCallback(drawMarkersMapTR);
    
    function drawMarkersMapTR() {
            var query = new google.visualization.Query(
      'https://docs.google.com/a/yantrajaal.com/spreadsheet/ccc?key=xxxxxxxxxxxxxx&sheet=MilkProduction&range=B2:H37&headers=1');
      query.setQuery('select B,C,D'); 
      query.send(handleQueryResponseTR);
    };
    
    function handleQueryResponseTR(response) {
  if (response.isError()) {
    alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
    return;
  }
  
  var options = {
        region: 'IN',
        displayMode: 'markers', 
        width : 600,
        height : 400
      };

  var data = response.getDataTable();
  var chart = new google.visualization.GeoChart(document.getElementById('chart_divTR'));
  chart.draw(data, options );
}

    </script>
  </head>
  <body>
    <div id="chart_divTR"></div>
  </body>



Tuesday 17 December 2013

4 Plotting Cities on a Map along with Data

In this example, we have data about certain of India -- data about number of registered vehicles in different years -- in a Google Docs spreadsheet. We use this data to draw a Google GeoChart.



In the code please observe the following points

  1. The spreadsheet doc is identified as https://docs.google.com/a/yantrajaal.com/spreadsheet/ccc?key=0AhEX55Pfl1eedE5mUHN4QVRLNzdldEpwZnAyNy1abUE
  2. The sheets as identified by sheet=CityWise2
  3. The range is identified by range=B2:M23
  4. The columns being selected are identified by query.setQuery('select B,D,E');
  5. The region in the map is 'IN' that is India
  6. The displayMode is "markers" .. which means towns and cities, had this been region we would have got full states



<head>
    <script type='text/javascript' src='https://www.google.com/jsapi'></script>
    <script async defer src="https://maps.googleapis.com/maps/api/js?key=myAPIkeyhere&callback=initMap"
  type="text/javascript"></script>
    <script type='text/javascript'>
     google.load('visualization', '1', {'packages': ['geochart']});
     google.setOnLoadCallback(drawMarkersMap02);
    
    function drawMarkersMap02() {
            var query = new google.visualization.Query(
      'https://docs.google.com/a/yantrajaal.com/spreadsheet/ccc?key=xxxxxxxxxxxxxxx&sheet=CityWise2&range=B2:M23&headers=1');
      query.setQuery('select B,D,E'); 
      query.send(handleQueryResponse02);
    };
    
    function handleQueryResponse02(response) {
  if (response.isError()) {
    alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
    return;
  }
  
  var options = {
        datalessRegionColor : 'FFFF99',
        region: 'IN',
        displayMode: 'markers',
        colorAxis: {colors: ['green', 'blue']}
      };

  var data = response.getDataTable();
  var chart = new google.visualization.GeoChart(document.getElementById('pm_div4'));
  chart.draw(data, options );
}

    </script>
  </head>
  <body>
    <div id="pm_div4" style="width: 600px; height: 500px;"></div>
  </body>


In creating this chart in this blog, we faced an unusual error. When viewed as an independent post, the chart was visible but when seen as a part of the blog, the chart was not drawn ?

What can be the reason ? The reason was that we had the same function name in two different posts and so when two posts were visible together on the same page, the codes were interefering with each other. So we simply placed an arbitrary suffix values to the function names and it worked.

The same code can be embedded in a non-blog web post like this.

A similar map can be created  using (a) public website data and (b) R statistical tool.


Google Map API
Subsequent to the creation of these posts, Google has made it mandatory for all new domains ( or new websites, new blogs) to incorporate a Google Maps API into the HTML code :
<script async defer src="https://maps.googleapis.com/maps/api/js?key=myAPIkeyhere&callback=initMap"
  type="text/javascript"></script>
this API key can be obtained from this Google page.

Wednesday 11 December 2013

3 Specifying Range of Data and Selecting Columns : Google Charts with Google Docs data

In the previous post, we had described how to create a basic Google Chart from data stored in a Google Doc spreadsheet. In that example, the data was picked up from the default first sheet of the spreadsheet and the data was located in the top left corner. In reality, the data could be stored in any of the other sheets and could be located in any portion of the sheet.

In this example, the Google Docs spreadsheet, has four sheets. For the purpose of drawing our chart we would like to specify that

  1. Data to be picked up from sheet named "Demo3"
  2. Within this sheet,  from the range C3:I23
  3. Within this range from the columns C, D, G, H
  4. Given the nature of the data we would like to multiply column G by 1000 before it is plotted
If you look at the spreadsheet in a browser, the URL will show up as 
https://docs.google.com/a/yantrajaal.com/spreadsheet/ccc?key=0AqawrNPy9RHodDNvMXdPX1NqanlyLVdtbEE1dlJ3LUE&usp=drive_web#gid=3

For our purpose the URL will be truncated as follows and used in the program
https://docs.google.com/a/yantrajaal.com/spreadsheet/ccc?key=0AqawrNPy9RHodDNvMXdPX1NqanlyLVdtbEE1dlJ3LUE

This URL can also be obtained by going to the option File=>Publish to the Web

The chart will look as follows

Sheet 2 Chart - Sheet, Range, Cols

this chart has been generated by embedding the following code in this blog. It can also be embedded in any other HTML page.


<head>
<script type="text/javascript" src="https://www.google.com/jsapi"></script>
<script type="text/javascript">
google.load("visualization", '1', {packages:['corechart']});

google.setOnLoadCallback(drawChart01);


function drawChart01() {


 var query = new google.visualization.Query(
      'https://docs.google.com/a/yantrajaal.com/spreadsheet/ccc?key=0AqawrNPy9RHodDNvMXdPX1NqanlyLVdtbEE1dlJ3LUE&sheet=Demo3&range=C3:I12&headers=1');
      query.setQuery('select C,D,1000*G,H'); 
  query.send(handleQueryResponse01);
}

function handleQueryResponse01(response) {
  if (response.isError()) {
    alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
    return;
  }
  
  var options = {
          title: 'Industrial Production Index',
          vAxis: {title: 'Sector',  titleTextStyle: {color: 'red'}},
          width: 400,
          height : 600
        };


  var data = response.getDataTable();
  var chart = new google.visualization.BarChart(document.getElementById('ChartSpan4'));
  chart.draw(data, options );
}



</script>

<title>Sheet 2 Chart - Sheet, Range, Cols</title>
</head>

<body>

<span id='ChartSpan4'></span>
</body>




Sunday 8 December 2013

2 Creating a Google Chart using data pulled from Google Docs

I have data lying in this Google Docs spreadsheet. that I want to display in this blog post or in any other html page using Google Charts.

The first task is to publish this data on the web and make it visible to anyone who has the URL.

Next take the following piece of code and insert it into your website.

<head>
<script type="text/javascript" src="https://www.google.com/jsapi"></script>
<script type="text/javascript">
google.load("visualization", '1', {packages:['corechart']});
google.setOnLoadCallback(drawChart);
function drawChart() {
  var query = new google.visualization.Query(
      'https://docs.google.com/a/yantrajaal.com/spreadsheet/ccc?key=0AqawrNPy9RHodGJBQkRpX0Rrdjc2OXZMZmZmNzk3WGc&usp=drive_web#gid=0');

  query.send(handleQueryResponse);
}

function handleQueryResponse(response) {
  if (response.isError()) {
    alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
    return;
  }

  var data = response.getDataTable();
  var options = {'title':'How Much PIZZA I Ate Last Night',
                       'width':400,
                       'height':600};
  var chart = new google.visualization.PieChart(document.getElementById('columnchart'));
  chart.draw(data, options);
}
</script>

<title>Data from a Spreadsheet</title>
</head>

<body>
<span id='columnchart'></span>
</body>
       
 

Note the URL of the spreadsheet  :  https://docs.google.com/a/yantrajaal.com/spreadsheet/ccc?key=0AqawrNPy9RHodGJBQkRpX0Rrdjc2OXZMZmZmNzk3WGc&usp=drive_web#gid=0    and use this in the function drawChart()

and this what you get :
....................................
Data from a Spreadsheet
....................
the same code can be embedded in a no-blog HTML page and loaded into a webserver as seen here.

How to embed code into an HTML page

Embedding fragments of  program code into a blog post or an HTML page is always a problem because the browser finds it hard to distinguish between the actual code used to render a page and the sample code that you are trying to demo. This causes major mess ups. Here is a simple process that will make this task simpler.

1. If your sample code includes HTML or you have < or > symbols in your code, then you first need to encode your sample code using any of the tools from this list. However the tool that I have used to create this blog post is this HTML encoder from opinionatedgeek.com . This tool will take your raw HTML as an input and generate encoded HTML. 

2. Copy the following code into a text editor.

<pre style="font-family: Andale Mono,Lucida Console, Monaco, fixed, monospace; color: #000000; background-color: #eee; font-size: 12px; border: 1px dashed #999999;line-height: 14px; padding: 5px; overflow: auto; width: 100%">
<code style="color:#000000;word-wrap:normal;">
========================================
   <<<<<<<YOUR encoded CODE HERE>>>>>>>
========================================
</code>
</pre>


3. Insert your encoded sample code into the space indicated to create a block of code. Copy this code from the text editor and place it in your HTML page where you want to display the sample code. If you are using Blogger to post your code, make sure that you are in the "HTML" mode and not the "Compose" mode.

4. That is it. If you are in Blogger, publish your page and your are done.

1 Basic Google Chart


and here i can put some text

====================================================================
       
<!--Load the AJAX API-->
    <script src="https://www.google.com/jsapi" type="text/javascript"></script>
    <script type="text/javascript">

      // Load the Visualization API and the piechart package.
      google.load('visualization', '1.0', {'packages':['corechart']});

      // Set a callback to run when the Google Visualization API is loaded.
      google.setOnLoadCallback(drawChart);

      // Callback that creates and populates a data table,
      // instantiates the pie chart, passes in the data and
      // draws it.
      function drawChart() {

        // Create the data table.
        var data = new google.visualization.DataTable();
        data.addColumn('string', 'Topping');
        data.addColumn('number', 'Slices');
        data.addRows([
          ['Mushrooms', 3],
          ['Onions', 1],
          ['Olives', 1],
          ['Zucchini', 1],
          ['Pepperoni', 2]
        ]);

        // Set chart options
        var options = {'title':'How Much Pizza I Ate Last Night',
                       'width':400,
                       'height':300};

        // Instantiate and draw our chart, passing in some options.
        var chart = new google.visualization.PieChart(document.getElementById('chart_div1'));
        chart.draw(data, options);
      }
    </script>
  

  <body>
    and here i can put some text 
    <!--Div that will hold the pie chart-->
    <div id="chart_div1">
</div>
</body>
          
       
 
See the full gallery of Google Charts and use this playground to get the corresponding codes to copy and paste.

Vizualyse

A place to collect and explore visualizations.