Scripting iWork: Numbers and Yahoo! Finance

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! 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:

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 "http://ichart.finance.yahoo.com/table.csv?s=" & 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:

tell application "Numbers"
	activate
	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:

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.

Related research and analysis from GigaOM Pro:
Subscriber content. Sign up for a free trial.

• Why iPad 2 Will Lead Consumers Into the Post-PC Era
• The Near-Term Evolution of Social Commerce
• Content Farms: The Players, The Benefits, The Risks