Data (like stocks, weather, and sports scores) is most useful when it's constantly updated with the new information. SpreadJS is a JavaScript spreadsheet component that can easily consume, display, and provide real-time updates via data binding. We'll be using Socket.IO in order to get the real-time data from IEX Trading* and then using basic SpreadJS functionality to showcase the data.

In this tutorial, we'll use Node.JS in order to utilize Socket.IO, so be sure to install the latest version from here. We'll also be using Visual Studio Code, so make sure to run it as administrator, so the NPM commands will work in the terminal.

To download the sample zip for this tutorial, click here: Link to Sample

Figure 1

Application Setup

We can start off by creating a folder for the application. In this case, I gave it the name "Real Time Data." Next, we'll want to create a package.json file in that folder that will be used as the manifest file for our project. This can contain something similar to the following:

{
    "name": "real-time-data",
    "version": "0.0.1",
    "description": "An app that imports real-time data into Spread JS",
    "dependencies": {}
}

For this application, we are going to use Express as the web framework, and Socket.IO for real-time data, and we can install that simply with npm. In the Visual Studio Code terminal, you can type:

npm install --save express@4.15.2 socket.io

Once those are installed, we can create a file called "index.js" that will be used for setting up our application. This should contain the following:

var app = require('express')();
var http = require('http').Server(app);
var io = require('socket.io')(http);

//Add code here

http.listen(3000, function(){
    console.log('Stock Ticker Started, connect to localhost:3000');
});

Now we can add the HTML file that the application is going to serve. In this case, we can name the file "index.html" We can go ahead and add some code to our HTML file, including the script and css references to SpreadJS as well as some basic initialization code:

<!doctype html>
<html>
  <head>
    <title>Real Time Data</title>
  </head>

  <script src=“/socket.io/socket.io.js“></script>
  <script src=“https://code.jquery.com/jquery-3.3.1.min.js“></script>
  <link href=“http://cdn.grapecity.com/spreadjs/hosted/css/gc.spread.sheets.excel2013white.12.0.7.css“ rel=“stylesheet“ type=“text/css“ />
  <script type=“text/javascript“ src=“http://cdn.grapecity.com/spreadjs/hosted/scripts/gc.spread.sheets.all.12.0.7.min.js“></script>
  <script src=“http://cdn.grapecity.com/spreadjs/hosted/scripts/plugins/gc.spread.sheets.charts.12.0.7.min.js“ type=“text/javascript“></script>

  <script>
    window.onload = function() {
      // Initialize spread variables
      var spread = new GC.Spread.Sheets.Workbook(document.getElementById(“spreadSheet“), { sheetCount: 1 });
      spread.fromJSON(stockTemplate);
      var activeSheet = spread.getActiveSheet();
      var dataSheet = spread.getSheet(1);
      activeSheet.clearSelection();
    }
  </script>
  <body>
    <div id=“spreadSheet“ style=“width: 650px; height: 600px; border: 1px solid gray“></div>
  </body>
</html>

In the previous code snippet we used spread.fromJSON() to load a template file. For this project I created that template file to use as the basis for the stock ticker. Using only the SpreadJS Designer, I created data labels and bindings for the data source, formatted cells, removed gridlines and headers, and added an area for the chart to go. I will just provide the file in this tutorial, which is called "stockTemplate.js." To export to JS in the Designer, simply click File>Export and select "Export JavaScript File." For this tutorial, I placed that template file in the same folder as my index.js and index.html files.

Back in the index.js file, we'll need to tell the program to serve the HTML file and the template, by using the following code:

app.get('/', function(req, res){
    res.sendFile(__dirname + '/index.html');
});
// Required to load template file
app.get('/stockTemplate.js', function(req, res){
    res.sendFile(__dirname + '/stockTemplate.js');
});

Going back to the index.html file, we can add the script to load that template file:

<script type=“text/javascript“ src=“stockTemplate.js“></script>

To finish the setup we can initialize the variables we are going to need later on and create a drop down cell to select the stock:

// Initialize variables
var stockSymbolLookup = [{text:'Apple Inc.', value:'AAPL'}, {text:'Microsoft Inc.', value:'MSFT'}, {text:'Google', value:'GOOGL'}];
var dataSource = [],
    openPrice = 0,
    closePrice = 0,
    stockCounter = 0,
    chart = null,
    chartAxisRange = 3,
    lineDataMaxSize = 20,
    lineData = new Array(lineDataMaxSize),
    socket,
    stock;
// Create a drop down for selecting a stock
var stockDropDown = new GC.Spread.Sheets.CellTypes.ComboBox().items(stockSymbolLookup);
activeSheet.getCell(2,1).cellType(stockDropDown);

We can also set specific conditional formatting for the change in the open price: green if it is positive and red if it is negative.

// Set conditional formatting 
function setConditionalFormatting() {
  var ranges = [new GC.Spread.Sheets.Range(8,1,1,1)];
  var lowerStyle = new GC.Spread.Sheets.Style();
  lowerStyle.foreColor = "red";
  activeSheet.conditionalFormats.addCellValueRule(
    GC.Spread.Sheets.ConditionalFormatting.ComparisonOperators.lessThan,
    -0.001,
    -0.001,
    lowerStyle, 
    ranges
  );
  var upperStyle = new GC.Spread.Sheets.Style();
  upperStyle.foreColor = "green";
  activeSheet.conditionalFormats.addCellValueRule(
    GC.Spread.Sheets.ConditionalFormatting.ComparisonOperators.greaterThanOrEqualsTo,
    0.001,
    0.001,
    upperStyle,
    ranges
  );
}

Connecting to the Database

Before actually writing code to connect to the data source, we'll want to add some code to handle when the user selects a stock from the drop-down list in Spread. Only then will we connect and get the data. We can do this by binding to the EditEnded event, looking up the stock symbol from our lookup array, and then connect to that stock:

// Bind an event for changing the stock in the drop down menu
// Set the stock variable to the newly selected stock from the list
activeSheet.bind(GC.Spread.Sheets.Events.EditEnded, function(e, info) {
  if(info.row === 2 && info.col === 1) {
    stock = stockSymbolLookup.find(stockLookup => stockLookup.text === activeSheet.getValue(2,1));
    connectToDatabase();
  }
});

This calls a new function that we can create called "connectToDatabase":

// Handle connecting to the database to get new stock information when the selected stock is changed
function connectToDatabase() {
  // Receive JSON from the IEX Trading server
  // Reset the data source and remove the chart
  socket = io.connect('https://ws-api.iextrading.com/1.0/tops');
  socket.on('connect', function(data) {
    dataSource.length = 0;
    if (activeSheet.charts.get('line') != null)
      activeSheet.charts.remove('line');
    socket.emit('unsubscribe', 'AAPL,MSFT,GOOGL');
    socket.emit('subscribe', stock.value);
  });
}

This code connects to the data source using socket.io and passes in the stock symbol to subscribe to. In addition, there is a section about removing a chart called "line" which is used later to reset the chart once we add it, since this function will be called every time the stock selection is changed.

Figure 2

When the program is connected to the data source and subscribed to a specific stock value, the program will receive updates from that data source in the form of JSON data, which we'll need to parse through to use in Spread. To do this, we can use the socket.on function:


// Parse the JSON and set the data in Spread
socket.on('message', function(message) {
  spread.suspendPaint();
  var obj = JSON.parse(message);
  if (obj.symbol === stock.value) {
    // Set the open price to the last price when the program starts,
    // and the close price to an arbitrary number for testing.
    if (dataSource.length == 0) {
      dataSource = obj;
      openPrice = obj.lastSalePrice;
      closePrice = openPrice - 10;
      // Fill in starting data for the line chart
      lineData.fill({Value:openPrice});
      activeSheet.setValue(2, 1, stock.text)
      activeSheet.setValue(2, 7, openPrice);
      activeSheet.setValue(3, 7, closePrice);
      activeSheet.setFormula(8, 1, "B6-H4");
      addChart();
      setConditionalFormatting();
    } else {
      dataSource = obj;
    }
    addLineData();
    bindData();
  }
  spread.resumePaint();
});

In the above code, I go through the data source and fill in some sample data in the sheet. I also called some functions that will be defined: bindData, addLineData, addChart, and setConditionalFormatting.

Using the Data in Spread

Before going through each function, the main structure of the program should be explained. Essentially, the data is bound directly to the first sheet in the Spread instance, "Stock_Ticker" via cell-level data-binding, an explanation of which can be found here.

The second sheet is a backlog of the open price value of the stock since the program started. Normally it would be best to keep track of the values recorded since a particular date, but to simplify this program it is just based on the program start time, and in this case only the 20 most recent values. This backlog of values is what the line chart will point to, essentially showing the changes in the value since the program started.

When the template is defined in the designer and the format matches that of the data source, then it can simply be set in the sheet using the setDataSource function, which is called in the "bindData" function. In addition, we can set the data source for the second sheet, "Data_Sheet," and let the columns auto generate from the data since we don't care about the formatting on that sheet:

// Bind the data source for both of the sheets
function bindData() {
  activeSheet.setDataSource(new GC.Spread.Sheets.Bindings.CellBindingSource(dataSource));
  dataSheet.autoGenerateColumns = true;
  dataSheet.setDataSource(lineData);
}

Adding Data for the Line Chart

The next function to define is the "addLineData" function, which uses the array defined earlier in this tutorial and adds a value to it every time a new value is received from the data source if that value is different from the one before:

// Add data with each update for the line chart
function addLineData() {
  if (lineData.length >= lineDataMaxSize)
    lineData.shift();
  stockCounter++;
  // Only add the data to the list for the line chart if the data has changed
  if (dataSource.lastSalePrice != lineData[lineData.length-1].Value) {
    lineData.push({ Value:dataSource.lastSalePrice });
  }
}

Adding the Line Chart

For the line chart, we can create it by specifying the cell range in the "Data_Sheet" sheet that is bound to the lineData data source. We can also change the formatting of the title, axes, data labels, legend, and chart area, all inside of the "addChart" function:

// Add the line chart
function addChart() {
  // Define the area to load the chart into
  var startCellRect = activeSheet.getCellRect(11, 1);
  var endCellRect = activeSheet.getCellRect(24, 9);
  var chartStart = {
    x: startCellRect.x,
    y: startCellRect.y
  };
  var chartArea = {
    width: endCellRect.x-startCellRect.x,
    height: endCellRect.y-chartStart.y
  }
  chart = activeSheet.charts.add('line', 
    GC.Spread.Sheets.Charts.ChartType.lineMarkers, 
    chartStart.x, 
    chartStart.y,
    chartArea.width, 
    chartArea.height,
    'Data_Sheet!$A$1:$A$' + lineDataMaxSize
  );

  chart.allowMove(false);

  // Set the title of the chart
  chart.title({
    text: activeSheet.getValue(2,1),
    color: "white"
  });

  // Change the values on the y-axis to show changes easier
  // Hide the x-axis values, we only care about changes, not specific time values
  chart.axes({
    primaryValue: {
      min: openPrice - chartAxisRange,
      max: openPrice + chartAxisRange
    },
    primaryCategory: {
      visible: false
    }
  });

  // Add data labels to the chart
  chart.dataLabels({
    color: "white",
    format: "$#.##",
    position: GC.Spread.Sheets.Charts.DataLabelPosition.above,
    showValue: true
  });

  // Hide the legend; there is only one series used in this chart
  chart.legend({
    visible: false
  });

  // Change the color of the chart
  chart.chartArea({
    backColor: "black",
    color: "white"
  })
}

Running the Program

With all of the code added, running the program is easy. Simply open the terminal in Visual Studio Code and type:

node index.js Then navigate to localhost:3000 in a web browser:

Figure 3

And select a Stock from the drop down menu to load the data:

Figure 1

This is a simple example of using a real time data source in SpreadJS, but it can be done in many different ways. With the added power of charts and data binding in SpreadJS, you can do more than just display the data.

Thanks for following along; please be sure to leave your comments below. Happy Coding!

*Data provided for free by IEX. View IEX's Terms of Use.

Incorporate Real-time Data in your JavaScript Spreadsheet

Download SpreadJS 12 Now!

Download Now!