Knowledge Base

Stock Chart with Google Finance CSV API

NOTE: this demo refers to Google Finance API service, which has been discontinued by Google. It may become unavailable at any point in the future. You are advised to take that into account before making decisions on your data sourcing. If you are looking for a reliable data source, amCharts recommends CloudQuote. [More info]

If you want to use data loaded from Google Finance API CSV, we will have to go through some steps before you can use those on a Stock Chart.

While we can use our own Data Loader plugin to do most of the work for us, there are some nuances. Let me walk you through them.

Dates

Dates in Google’s CSV are formatted like this: “4-May-17”.

The chart can’t parse or understand one-digit days, two-digit years or name-based months.

We’ll use Data Loader’s postProcess callback function to reformat those dates into chart-suitable format before it’s used:

"postProcess": function( data ) {

        // Function that reformats dates
        function reformatDate( input ) {

          // Reformat months
          var mapObj = {
            "Jan": "01",
            "Feb": "02",
            "Mar": "03",
            "Apr": "04",
            "May": "05",
            "Jun": "06",
            "Jul": "07",
            "Aug": "08",
            "Sep": "09",
            "Oct": "10",
            "Nov": "11",
            "Dec": "12"
          };

          var re = new RegExp( Object.keys( mapObj ).join( "|" ), "gi" );
          input = input.replace( re, function( matched ) {
            return mapObj[ matched ];
          } );

          // Reformat years and days into four and two digits respectively
          var p = input.split("-");
          if (p[0].length == 1) {
            p[0] = "0" + p[0];
          }
          if (Number(p[2]) > 50) {
            p[2] = "19" + p[2];
          }
          else {
            p[2] = "20" + p[2];
          }
          return p.join("-");
        }

        // Reformat all dates
        for ( var i = 0; i < data.length; i++ ) {
          data[ i ].Date = reformatDate( data[ i ].Date );
        }
        
        console.log(data);

        return data;
}

This will ensure that “4-May-17” becomes “04-05-2017”.

Now that we have that squared, we can use chart’s native dataDateFormat setting to specify the input date format:

"dataDateFormat": "DD-MM-YYYY"

Data point order

The data points in Google’s CSV come in reversed order – the latest timestamp comes first.

We will need to set Data Loader’s setting reverse to true to accommodate for that.

Wrapping it all together

Here’s the final set of settings, optimized for Google Finance API CSV:

"dataLoader": {

      /**
       * Originally we assume URL:
       * https://finance.google.co.uk/finance/historical?q=MSFT&startdate=Oct+1,2008&enddate=Oct+9,2008&output=csv
       * However, due to CORS restrictions, we are using the copy of the out
       */
      "url": "google_msft.csv",
      "format": "csv",
      "delimiter": ",",
      "useColumnNames": true,
      "skip": 1,

      /**
       * Google Finance API returns dates formatted like this "24-Apr-17".
       * The chart can't parse month names as well as two-digit years, so we 
       * need to use `postProcess` callback to reformat those dates into a 
       * chart-readable format
       */
      "postProcess": function( data ) {

        // Function that reformats dates
        function reformatDate( input ) {

          // Reformat months
          var mapObj = {
            "Jan": "01",
            "Feb": "02",
            "Mar": "03",
            "Apr": "04",
            "May": "05",
            "Jun": "06",
            "Jul": "07",
            "Aug": "08",
            "Sep": "09",
            "Oct": "10",
            "Nov": "11",
            "Dec": "12"
          };

          var re = new RegExp( Object.keys( mapObj ).join( "|" ), "gi" );
          input = input.replace( re, function( matched ) {
            return mapObj[ matched ];
          } );

          // Reformat years into four digits
          input.replace( /\-([0-9])^/, function( matched ) {
            console.log(matched);
            return matched;
          } );

          return input;
        }

        // Reformat all dates
        for ( var i = 0; i < data.length; i++ ) {
          data[ i ].Date = reformatDate( data[ i ].Date );
        }
        
        console.log(data);

        return data;
      }
}