14 Comments

Summary:

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 […]

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

open-dictionary_02

You will should see a window that looks a bit like this:

numbers1_02

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.

  1. make new table with properties {name:company, row count:nRows, column count nCols + 1)}

    really?

    Share
  2. make new table with properties {name:company, row count:nRows, column count <img src=”http://s.wordpress.com/wp-includes/images/smilies/icon_sad.gif” alt=”:(” class=”wp-smiley”> nCols + 1)}

    now with < and >

    Share
  3. The Numbers scripting has been a revelation to me. There are some huge gaps in the dictionary, but there is enough room for some fairly powerful additions to Numbers. I’ve written an application that enables Monte-Carlo analysis within Numbers and I think with the power of Cocoa and the ease of use with applescript many other tools could be written for Numbers. I just hope that the gaps in applescript support get plugged with time.

    Share
  4. I converted this “scripting iWork” software / application and turn it into a scripting farm for php, css, html, java and ect…

    ….pretty simple and straight forward script backup!

    Share
  5. Cult of Frank Thursday, March 12, 2009

    Thanks for the script, my first foray into Applescripting so it was very helpful. I tweaked your script to grab the stocks from and update a table with my investment portfolio to have the most recent closed stock price (which in turn updates charts, etc etc). Pretty slick though it would be more so if there was a way to avoid writing the data to a new table and creating a file and so on.

    I would love to be able to take this data from multiple stocks and chart it out, especially against an index like the S&P 500.

    Anyway, thanks again for this post.

    Share
  6. This has made things lsightly better and easier for me to understand. Slowly but surely I am getting there. A damn site closer than I was to start with. Coding has never been one of my strong points so thanks for this. Cheers

    Share
  7. [...] Continued here: Scripting iWork: Numbers and Yahoo! Finance [...]

    Share
  8. Thanks a million! It works fine

    Share
  9. Hi,
    Can you provide more information on this?
    Regards,
    Jane

    Share
  10. Under Snow Leopard 10.6.2 I get an error reading in the file

    read file “Macintosh HD:private:var:folders:pD:pD2LGZXXGUihwyJSXnoAxU+++TI:TemporaryItems:yahoo-quote” of sheet 1 of document 1
    –> error number -10004

    Which can be corrected by telling the “Finder” application instead

    — read in the file contents
    tell application “Finder”
    set quoteLines to paragraphs of (read file tempFile)
    end tell

    Share

Comments have been disabled for this post