JavaScript Table Example

The following example demonstrates how to use JavaScript Tables.

Purpose

You need to collect stock exchange information from a financial portal for a certain period of time and save this information so that you can use it in QMF as a typical relational table. The table must not be updated too often and store information longer than 30 days.

Preparing table structure

Before you begin writing the script, you need to make sure that the specified table structure corresponds to the data that you need to retrieve.

Planning the script structure

According to the purpose the script must retrieve information from server, parse the data to fill the table, save and check the last modification time, and remove outdated information.

var lastModificationDate = getTableModificationTime();
if (needUpdate(lastModificationDate))
{
	var raw_data = retrieveRawData(url);
	var data = parseData(raw_data);
	table.appendData(data);
	eraseOutdated();
  setTableModificationTime();
}
else
{
	log("Data is already up to date ");
}

The following functions are used:

setTableModificationTime() to attach the last modification time to the table.
getTableModificationTime() to get the last table modification time
needUpdate() to check if update is required depending on the last modification time.
retrieveRawData() to get the required data from the portal.
parseData() to split the received string according to the table structure.
eraseOutdated() to remove old records.
log() to trace the response status and write information to the Output view.

Retrieving data from the Internet

First of all, you need to retrieve the required data from the Internet. For this, RSBIHttpRequest class is used:

function getStockData(url)
{
	var request = new RSBIHttpRequest();
	request.open('GET',url, false);
	var response = '';
	request.onreadystatechange = function()
	{
		if(request.readyState === request.Complete)
		{
			response = request.responseText;
		}
	}
	request.send(null);
	return response;
}

Tip: If you want to use the HTTPS protocol to send HTTP requests for your JavaScript table, you must first configure your Java virtual machine to handle the SSL certificates properly. If you do not want to do the configuration, use the RSBIHttpRequest.ignoreCertificates() method. In this case, your JVM is set to ignore the SSL certificates and the connection that is established is not secure.

The getStockData() function returns the response string with all required data from the portal.

It is called from the retrieveRawData() function that forms an HTTP request and uses log() function to trace response information. You can also use prompted variables (the symbols variable) in script – user will be prompted to specify the values when running the query. The &DSQQW_DQ global variable is used for substitution of double quotes.

function retrieveRawData()
{
	var symbols = &DSQQW_DQ&symbols&DSQQW_DQ;
 	var url = "http://finance.yahoo.com/d/quotes.csv?s="+symbols+"&f=snl1d1t1ohgdr";
 	var raw_data = getStockData(url);
 	log(raw_data);
  	return raw_data;
}

Parsing received data

Secondly, you need to parse the retrieved data according to your purposes so that you can fill the table with the help of appendData() or replaceData() functions. The easiest way is to split the response string:

function parseData(raw_data)
{
	var data = [];
	var rows = raw_data.split('\n');
	for (var i=0; i < rows.length; i++)
	{
		var row = rows[i].trim();
		if (row !== '')
		{
			var cells = row.split(',');
			data.push(cells);
		}
	}
	return data;
}

Specifying data update interval

You can attach modification time (as any additional information in string format) to the table with the help of setTag() function. The getTag() function is used to retrieve this information.

Setting the current time (last modification) as a table tag:

function setTableModificationTime()
{
	var currentDate = new Date();
	table.setTag(currentDate.getTime());
}

Retrieving the last modification time:

function getTableModificationTime()
{
	var tag = table.getTag(); 
	if (tag === null)
	{
		var d = new Date();
		d.setDate(-2); // to force update for the first time use
		tag = d.getTime();
	}
	else
	{
		tag = parseInt(tag);
	}
	return tag;
}

Checking whether update is required:

function needUpdate(lastModificationDate)
{
	var yesterday = new Date();
	yesterday.setDate(-1);
	return lastModificationDate <= yesterday.getTime();
}

Removing outdated information

You can use the eraseData() function to remove outdated information from the table:

function eraseOutdated()
{
	var expirationDate = new Date();
	expirationDate.setDate(-30);
	var day = expirationDate.getDay();
	var month = expirationDate.getMonth() + 1;
	var year = expirationDate.getYear() + 1900;
	var strDate = year + '-' + month + '-' + day;
	table.eraseData('"Last Trade Date"<' + "'" + strDate + "'");
}

Displaying results

When you finish working with the script, save the changes and close the editor.

To run the query, navigate to the saved JavaScript Table in the Repository Explorer view and double-click it. Run the opened query.