Blog Post

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.

14 Responses to “Scripting iWork: Numbers and Yahoo! Finance”

  1. … and the code I was using for the URL was

    set theURL to “” & company & “&a=” & historymonth & “&b=” & historyday & “&c=” & historyyear & “&d=” & todaymonth & “&e=” & todayday & “&f=” & todayyear & “&g=d&ignore=.csv”

    where the variables are previously defined earlier on within the script

  2. First of all, great script, really handy. However I am trying to limit the amount of data gathered so that I can optimise the spreadsheet (i.e. I am plotting 200 day moving averages).

    My first approach involved changing the dates within the URL however for some reason the .csv still seems to be going back further than the dates (and therefore the address) specified.

    The second approach is limiting the number of rows within the table however this returns an error as the n+1 row cannot be written and subsequently returns an error.

    Any ideas??

  3. neuronsong

    I just ran this script and got the following error:

    error “Numbers got an error: Can’t make file \”Neuro HD:private:var:folders:vi:vi+3Y6xzEleHTqvVJVM7KU+++TI:TemporaryItems:yahoo-quote.csv\” of sheet 1 of document 1 into type file.” number -1700 from file “Neuro HD:private:var:folders:vi:vi+3Y6xzEleHTqvVJVM7KU+++TI:TemporaryItems:yahoo-quote.csv” of sheet 1 of document 1 to file

    I modified the script to the following and it worked just fine for getting the initial data in. I’ve removed all comments for brevity:

    set tempFile to ((path to temporary items) as string) & “yahoo-quote.csv”
    display dialog “Enter symbol:” default answer “AAPL”
    set company to text returned of result
    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
    tell application “Numbers”
    open tempFile
    end tell
    tell application “Finder” to delete file tempFile

  4. This script is such a huge help for me, thank you a lot!

    But I have a problem with your script, because numbers gets confused with the cell-format and the decimal seperator.

    Here is a screenshot of what I get:

    So stock quotes always have 2 decimals, but the “.” respectively the “,” (we use) is dropped. Furthermore numbers “thinks that values in the format “##.##” must be calendar items and adds the current year after the values.

    Can you please give me a hint? This might be because my German version uses “,” as decimal seperator and not “.” how can I fix this?

  5. 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

  6. Cult of Frank

    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.

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

  8. 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.

  9. John Doe

    make new table with properties {name:company, row count:nRows, column count <img src=”” alt=”:(” class=”wp-smiley”> nCols + 1)}

    now with < and >