When iWork ’08 was released it felt like a half-implemented suite for a whole host of reasons: lack of interoperability between the applications, very basic functionality, performance issues — especially with Numbers ’08 — and lack of scripting. Apple’s new iWork ’09 suite has addressed many of those issues, and the one feature that truly stands out (for me) is the ability to bend the applications to your will via scripting.
This article will focus on showing the scriptability of Numbers by writing a very small script to retrieve data from Yahoo! (s YHOO) Finance and put it into a custom table.
Numbers Scripting Definitions
To get an idea of what you have at your disposal, fire up /Applications/AppleScript/Script Editor and select File->Open Dictionary… and choose Numbers from the dialog:
You will should see a window that looks a bit like this:
You should definitely take some time to peruse all the options available at your disposal but you will find that, while Apple has provided many ways to manipulate the contents of documents, sheets, tables, cells and rows, there is a distinct lack of functions related to charting/graphing. Perhaps an incremental update or Numbers X will correct this oversight.
Directing the Data
Yahoo! Finance has a very nice historical data view for closing stock prices. Even with all of the enhancements, Numbers still lacks one of the nicest features of Excel: the ability to grab data from a URL. To remedy this, we can create an AppleScript that:
- lets us input the symbol of the stock we are interested in
- fetch the data from Yahoo! Finance
- and populate a new table in Numbers with this data
Part’s one and two are pretty straightforward AppleScript:
— get company to display from the user
display dialog “Enter symbol:” default answer “AAPL”
set company to text returned of result
— download the file
tell application “URL Access Scripting”
set theURL to “http://ichart.finance.yahoo.com/table.csv?s=” & company & “&a=00&b=1&c=2008&g=d&ignore=.csv”
download theURL to file tempFile
Here’s where it gets interesting. First, we tell Numbers (the script assumes the app is open with a document ready) to become active and make the most recent document the one we want to work in. We then read in the data from Yahoo! to determine how many rows we need and use the header line to determine how many columns we need and then make a new table with this information:
Finally, we loop through each line (row) and copy the data from each column to the correct cell, then we delete the temporary file we created at the beginning:
When you run the script (download the source), you will be able to watch Numbers create the new table and populate all of the cells.
This particular script duplicates a built-in functionality of Numbers: the ability to read CSV files. However, you could very easily modify it to use AppleScript to tweak the data or make a call out to a Python or Perl script to do even more substantial modifications then bring the information back in. The fundamental purpose of the script is to demonstrate just how easy it is to address cells and rows. Reading data is just as easy —
set v to value of cell x of row y — and that opens up a whole host of possibilities, such as using an active Numbers sheet to send data to a web service via a global-hotkey-enabled AppleScript action.
Make sure to drop a note in the comments with how are you using the new scripting functionality in Apple’s latest iWork suite.