SQL Offline

JavaScript Stock Quote Sample (AJAX)

Get started using Asynchronous JavaScript and XML in a spreadsheet. JavaScript formulas are saved in Offline JavaScript (OLJ) files.

Sample files are in the OLZ package:

http://inters.com/samples/stockquotes.olz

After opening an OL file, click Enabled in the Formula ribbon to start the JavaScript formulas.

WARNING: Do not enable formulas from an untrusted source.

AJAX Stock Quote Samples

The SQL Offline - Asynchronous JavaScript and XML (AJAX) video shows you how to run this sample and how to create JavaScript formulas.
A complete list of videos can be found here: http://inters.com/videos.

These samples use the Yahoo Finance APIs. The sample sends requests similar to:
http://query.yahooapis.com/v1/public/yql?q=select * from yahoo.finance.quotes where symbol="YHOO"&diagnostics=true&env=http%3A%2F%2Fdatatables.org%2Falltables.env
You can test the link above to verify the API is available and returns data.

The sample contains three OL documents AMEX, NYSE, NASDAQ.

NASDAQ.ol and NYSE.ol add columns automatically based on the XML result. Only the Symbol column is in the OL file.
SQL Offline formulas are evaluated only when needed (for example: displayed on screen as shown above). Additional AJAX requests are sent as you scroll down.

AMEX.ol includes columns, one of which is a formula that converts market cap to a plain number (instead of using B=Billion and M=Million notation) which can be sorted.

The FormulaMarketCapitalizationNumber formula in AMEX converts MarketCapitalization to a sortable number.

Sorting on a formula column converts the cell value to the column Data Type (for example: CY - Currency). If you want to convert back to a formula select the column (CTRL+SHIFT+A) and set the Default (CTRL+DEL) value. You can use the Columns dialog box to copy and paste the MarketCapitalizationNumber column to the other OL documents (NASDAQ and NYSE). The formula converts the previous ( nCol - 1 ) column to a number.

AJAX Code Sample

The function that creates the XMLHttpRequest is updateStock:

function updateStock(sStock) {

    if (sStock != undefined) { //Request a new stock

        xmlhttpStocks.push(sStock);

    }

    //Return if a stock is currently processing

    if (xmlhttpUpdating != null) return; //Update pending

    //Finished if there are no more requests

    if (xmlhttpStocks.length == 0) {

        xmlhttp = null;

        return;

    }

    //Send a new request

    xmlhttpUpdating = xmlhttpStocks.pop();

    //Prepare a YQL stock quote query

    //http://query.yahooapis.com/v1/public/yql?q=select * from yahoo.finance.quotes where symbol="YHOO"&diagnostics=true&env=http%3A%2F%2Fdatatables.org%2Falltables.env

    var surl = xmlhttpSite + "?q=select * from yahoo.finance.quotes where symbol=\"" + xmlhttpUpdating + "\"&env=http%3A%2F%2Fdatatables.org%2Falltables.env";

    xmlhttp = new ActiveXObject("Microsoft.XMLHTTP");

    if (xmlhttp == null) { //Error

        WSH.Echo("Microsoft.XMLHTTP not supported");

        return;

    }

    xmlhttp.onreadystatechange = readystatechange;

    xmlhttp.open("GET", surl, true);

    xmlhttp.send();

    xmlhttpTime = new Date();

}

You can click Edit Formula (F11) to view the other functions. FormulaColStock is the formula function. The formula function either returns a value from a completed AJAX request, or makes a new request.

JavaScript JS files and Windows Script Host

SQL Offline can be used to edit and run JS or VBS files. You do not need to create an OL (spreadsheet) document to work with script files.

You can edit, run, and debug JS files just as you can OLJ files; the difference is that functions in OLJ files are managed by the OL document and called to evaluate formulas.

You can use SQL Offline to run the same JS files that Windows Script Host (wscript.exe and cscript.exe) runs. SQL Offline supports the File System Object (FSO) and ActiveXObject.

WARNING: SQL Offline hosts JavaScript in the same way that Microsoft Windows Based Script Host (wscript.exe and cscript.exe) do, which provides unrestricted access your computer from the script.