Tip:
Highlight text to annotate it
X
In this video I’ll show you how to open the stock quotes (stockquotes.olz) spreadsheet in SQL Offline.
The spreadsheet uses AJAX which requests data from Yahoo finance.
No stock data is saved in the OL file.
First we’ll load the samples and take a look at the JavaScript. Later in this video I will show you how to create and run your own formulas.
Here I downloaded the OLZ and unzip three offline (spreadsheet) OL documents.
The matching OLJ file contains the JavaScript. To start the script click Enable in the Formula ribbon.
With formulas enabled, requests are made to yahoo finance for each stock symbol.
When the request is complete, the data displays. Data is queued up.
When it is sent back we display it. But we never stop and wait. That’s the A (Asynchronous) part of AJAX.
The NYSE OL file which has does not include columns. Data from YHOO is reviewed, and a column is added for each property.
The OL file is only a small fraction (%3) of a CSV that would contain the data. Later in the video we will write a CSV.
TWTR is not in the OL file so I’ll add it (I added it to wrong exchange). Insert a row and type in the TWTR symbol.
That’s how to use the stock quote samples. Now let’s look at how it works.
This data all comes from YHOO finance using XMLHTTP request. Requests are made as each row is evaluated.
As we browse the spreadsheet, requests are made when the row is displayed.
When we get a response, we update the spreadsheet. If you double-click on the cell the formula dialog displays.
The formula itself returns data if it has it, and makes a new request if it does not.
Click Edit Formula in the Formula ribbon and can see the OLJ JavaScript editor.
The most important part is Microsoft.XMLHTTP object, which is the heart of AJAX.
Browsers now have this object built-in, but you can create it (ActiveXObject) here.
A URL is formed from the Stock symbol for your row and a request is sent.
When the request is complete, readystatechange function is called and we save the stock data.
The more you browse, the more stock data is collected. AJAX is well documented so I won’t go into detail here.
The best way to see what’s going on here is to step through in a script debugger.
In the Debugger we can see the properties provided in the XML response. If you have Visual Studio (the Express version works)
installed all you need to do is set a breakpoint in the formula editor.
Please see product help for more information on debugging formulas.
The data link is to a CSV file, so if you write data, we create a CSV file that you can open in Excel.
In the status bar you can see there are a lot of requests now pending.
As you might have guessed there are side effects from asynchronous requests.
What happens is the FIRST time we write date (CSV file), it causes all the requests to queue up for the entire spreadsheet.
If we look at the data in Excel, only the rows at the top that we browsed have data.
No magic here. We need to wait. I’ll fast forward for the video. It took 15 minutes to get all of the requests back.
After all the requests are complete, I can write the CSV again.
You can stop and start the OLJ formula to refresh the stock data, but for now there will be no additional requests because all stock data is
now in memory. Specifically, it is in a JavaScript array. I’ll load Excel and this time see data for all rows.
SQL Offline formula scripts are a lot like macros in MS office. In fact they use the same automation technology.
To finish this video I want to create some formulas from scratch. AJAX is familiar to web developers, but still a bit tricky.
In the Formulas ribbon, you can select JavaScript or basic. JavaScript is the default.
Click the column button, and a column formula is created which simply returns “Value”.
The value that is returned displays in the spreadsheet.I can create a Cell formula, which is a formula for one cell.
You can list functions and select Date(), which displays Date and Time in the cell.
I select the remainder of the spreadsheet and create a Global formula. I’ll return nRow * nCol which creates a multiplication table.
And that’s how easy it is to create JavaScript formulas in SQL Offline. Finally I want to create a JS file.
SQL Offline is a script host, so you can edit and run JS and VBS files.
Even if you do not use SQL Offline to work with a database,
I hope you find it useful to edit and test Windows JScript and Visual Basic Script files.
You can press a period ( . ) to activate smart- editing for objects like WSH or the file system object.
Click Functions in the Objects drop down to list functions and JavaScript native objects like Math, String and Date.
To run the script, just click run. So that’s how to create and test a simple JS file.
I’ll save the file and exit SQL Offline. In Windows explorer the JS file runs using Microsoft Windows Script host.
This is all familiar stuff for web developers and system admins. But even if you have never worked with scripts,
I hope you will find it surprisingly easy to begin using JavaScript as a spreadsheet formula language.
For tutorials and additional samples please visit product help. Thank you for watching.