Data, including stocks, weather, and sports scores, is most useful when it's constantly updated with new information. SpreadJS is a very versatile JavaScript spreadsheet component that can also easily consume, display, and provide real-time updates via data binding.
We will use WebSocket to get the real-time data from Finnhub.IO and then use basic SpreadJS functionality to showcase the data. To utilize the Finnhub Stock API, you will need to create a free account and generate your API key, which we will add later in this application.
In this tutorial, we'll use Node.JS Express with WebSocket, so be sure to install the latest version from here. We'll also be using Visual Studio Code, so run it as administrator so that the NPM commands will work in the terminal.
Download the sample zip for this tutorial.
In this blog, we will cover how to incorporate real-time data in a JavaScript spreadsheet following these steps:
- Set Up the Application
- Connect to the Datasource
- Use the Data in Spread
- Add Data for the Line Chart
- Add the Line Chart
- Run the Program
Ready to Test it Out? Download SpreadJS Today!
Application Setup
We can start by creating a folder for the application. In this case, we named it "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.2",
"description": "An app that imports real-time data into Spread JS",
"dependencies": {}
}
For this application, we will use Express as the web framework and WebSockets for real-time data, and we can install that simply with npm, which we will also use to install the SpreadJS files. In the Visual Studio Code terminal, you can type:
npm install --save express@4.18.2 finnhub websocket @grapecity/spread-sheets @grapecity/spread-sheets-charts
Once those are installed, we can create a file called "index.js" to use to set up our application. This should contain the following:
var express = require('express');
var app = express();
var http = require('http').Server(app);
app.use('/node_modules', express.static('node_modules'));
// Add code here
http.listen(3000, function(){
console.log('Stock Ticker Started, connect to localhost:3000');
});
Now we can add the HTML file the application will 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 type="text/javascript" src="stockTemplate.js"></script>
<link href="/node_modules/@grapecity/spread-sheets/styles/gc.spread.sheets.excel2013white.css" rel="stylesheet" type="text/css" />
<script src="/node_modules/@grapecity/spread-sheets/dist/gc.spread.sheets.all.min.js"></script>
<script src="/node_modules/@grapecity/spread-sheets-charts/dist/gc.spread.sheets.charts.min.js" ></script>
<script>
window.onload = function() {
// Initialize spread variables
var spread = new GC.Spread.Sheets.Workbook(document.getElementById("spreadSheet"), { sheetCount: 1 });
spread.fromJSON(stockTemplate);
spread.options.scrollbarMaxAlign = true;
spread.options.showHorizontalScrollbar = false;
spread.options.showVerticalScrollbar = false;
spread.options.grayAreaBackColor = 'rgb(38,38,38)';
var activeSheet = spread.getActiveSheet();
var dataSheet = spread.getSheet(1);
activeSheet.clearSelection();
}
</script>
<body>
<div id="spreadSheet" style="width: 680px; height: 590px; border: 1px solid gray"></div>
</body>
</html>
In the previous code snippet, we used spread.fromJSON() to load a template file. For this project, we created that template file to use as the basis for the stock ticker.
Using only the SpreadJS Designer, we created data labels and bindings for the data source, formatted cells, removed grid lines and headers, and added an area for the chart to go.
The file in this tutorial is provided, which is called "stockTemplate.js." To export to JS in the Designer, click File>Export and select "Export JavaScript File." We placed that template file in the same folder for this tutorial 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 = [],
lastPrice = 0,
timeStamp = 0,
volume = 0,
stockCounter = 0,
chart = null,
chartAxisRange = 0.5,
lineDataMaxSize = 10,
lineData = new Array(lineDataMaxSize),
initialData = true,
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 = Positive
Red = 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 Data Source
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 connecting 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));
connectToDataSource();
}
});
This calls a new function that we can create called "connectToDataSource":
// Handle connecting to the data source to get new stock information when the selected stock is changed
function connectToDataSource() {
// Create a new WebSocket connected to the FinnHub Stock API with a personal token
socket = new WebSocket('wss://ws.finnhub.io?token=<YOUR TOKEN HERE>');
if (socket.readyState !== WebSocket.CLOSED)
console.log("CONNECTED.");
else
console.log("NOT CONNECTED.");
// When the socket first opens, set the length of the data source to zero, remove the line chart if
// it exists, and send a message back to the server with the selected stock
socket.addEventListener('open', function (event) {
dataSource.length = 0;
if (activeSheet.charts.get('line') != null)
activeSheet.charts.remove('line');
socket.send(JSON.stringify({'type':'subscribe', 'symbol':stock.value}));
});
}
This code connects to the data source using a WebSocket and passes in the stock symbol to subscribe to.
Note: When initializing the WebSocket, you would add the token you generated from Finnhub.IO.
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.
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 an event listener to listen for a message from the WebSocket
// Listen for a message from the server
socket.addEventListener('message', function (event) {
spread.suspendPaint();
// Get the data from the server message
var dataArray = JSON.parse(event.data)
console.log(dataArray);
if (dataArray.data && dataArray.data.length != 0) {
// Set the data source and extract values from it
var dataSource = dataArray.data[dataArray.data.length-1];
lastPrice = dataSource.p;
timeStamp = dataSource.t;
volume = dataSource.v;
// Fill in starting data for the line chart
if (initialData) {
lineData.fill({Value:lastPrice});
setConditionalFormatting();
initialData = false;
}
// Set the specific values in the spreadsheet
activeSheet.setValue(4, 1, stock.value);
activeSheet.setValue(5, 1, lastPrice);
activeSheet.setValue(2, 7, lastPrice);
activeSheet.setValue(4, 7, new Date(timeStamp));
activeSheet.setValue(6, 7, volume);
// Add the line chart if one doesn't exist
if (activeSheet.charts.all().length == 0) {
addChart();
}
addLineData(lastPrice);
bindData();
}
spread.resumePaint();
});
In the above code, we go through the data source and fill in some sample data in the sheet. We 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 ten 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, it can be set in the sheet using the setDataSource function 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(lastPrice) {
if (lineData.length >= lineDataMaxSize)
lineData.shift();
stockCounter++;
// Only add the data to the list for the line chart if the data has changed
if (lastPrice != lineData[lineData.length-1].Value) {
lineData.push({ Value:lastPrice });
}
}
Adding the Line Chart
We can create the line chart by specifying the cell range in the "Data_Sheet" sheet 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.line,
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: "0.00",
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. Open the terminal in Visual Studio Code and type:
node index.js
Then navigate to localhost:3000 in a web browser:
Select a stock from the drop-down menu to load the data:
This is a simple example of using a real-time data source in SpreadJS, but it can be done in many ways. With the added power of charts and data binding in SpreadJS, you can do more than display the data.
Thanks for following along. Feel free to leave any comments below. Happy Coding!
Ready to Test it Out? Download SpreadJS Today!
Related Blogs
Try Our JavaScript Spreadsheet Components
SpreadJS Resources
Kevin Ashley