Scripting iWork: Numbers and Yahoo! Finance

numbers-iconWhen 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:

[sourcecode language=’csharp’]
set tempFile to ((path to temporary items) as string) & “yahoo-quote”

— 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 “” & company & “&a=00&b=1&c=2008&g=d&ignore=.csv”
download theURL to file tempFile
end tell

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:

[sourcecode language=’csharp’]
tell application “Numbers”
tell document (count of documents)
tell sheet 1
— read in the file contents
set quoteLines to paragraphs of (read file tempFile)
set quoteValues to {}
— get number of rows for the table
set nRows to length of quoteLines
— get number of columns for the table
— (grab header row first)
set tempLine to item 1 of quoteLines
— we need to split the line with commas as delimeters
set oldDelims to AppleScript’s text item delimiters
set AppleScript’s text item delimiters to “,”
set headers to every text item of tempLine
set nCols to length of headers
— make a new Numbers table that fits the # rows & cols in the CSV file
make new table with properties {name:company, row count:nRows, column count:(nCols + 1)}

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:

[sourcecode language=’csharp’]
tell table (count of tables) — the one we just made is the last
delete column 1 — labels we don’t need
set nRow to 1
repeat with aLine in quoteLines
set cellValues to every text item of aLine
set nCol to 1
repeat with aCell in cellValues
set value of cell nCol of row nRow to aCell
set nCol to nCol + 1
end repeat
set nRow to nRow + 1
end repeat
end tell
— we need to put the delimeters back
set AppleScript’s text item delimiters to oldDelims
end tell
end tell
end tell
tell application “Finder” to delete file tempFile

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.