NEW VERSION ADDED 8/23/2007
One of the most glaring missing features of Apple iWork Numbers ’08 is the ability to get data into the sheet from outside sources. A very common need is to pull in stock quotes, then manipulate the data. While, I have not found a way to do it directly, I have developed a temporary method until Apple fixes this is later versions.
***Introduction***
The fix involves using a predefined stock table, a specifically formated Yahoo Finance url, and an Automator workflow that takes the url and retrieves a comma separated file, that is then opened in Numbers. Got that?
***File Content***
The zip file at the end of the post contains four two files:
* Read Me.rtf – This contains instructions similar to what you are now reading.
* Get-Quotes.app – A ready to run Automator app.
* Get-Quotes.workflow – The Automator workflow, in case you want to modify it.
* Stock Quotes.numbers – The sheet into which you will enter your data.
***Usage***
***Edited 8/23/2007***
1) Enter up to 15 symbols in the stock table.
2) The URL will build dynamically.
3) Click on the URL, which will open in your default browser.
4) Tell your browser to open the CSV file with Numbers, if it is not your default program.
5) Copy the resulting table into your stock table, and clean up any formatting.
6) Close the new window.
Note: If you want to learn more about the Yahoo url format, check out this awesome resource.
You are free to play around with this however you wish. However, since I just started the blog, I would appreciate you linking back to this article if you find it helpful.
Also, I want to keep the content fresh, so if you have any Numbers templates, tips or tricks you would like to share, drop me a line at: contact [at] numberstemplates.com
* Update 8/21/2007: Added pause to Automator action before deleting csv file. Thanks Timo!
* Update 8/23/2007: Made url clickable, Automator action no longer necessary.
Download Get Stock Quotes Template
{ 7 comments… read them below or add one }
Take a look at this post I made to Apple Discussions, maybe it can help out your Stock Quotes example!
http://discussions.apple.com/thread.jspa?threadID=1090948&tstart=45
Marcus,
I had already read your post! I think that it is an excellent idea, and it gives me a reason to learn AppleScript! I hope to explore that method of interaction with Numbers in future posts.
It appears that GUI scripting is the only currently available method for automating Numbers.
Good job!
- Michael
Thanks for the effort you put into this. However, I can’t get it to work. When I try to copy/paste the contents of cell A2 in the URL to Copy table, it will not paste it into the Yahoo URL box that Automator creates. It just gives me a “bonk” sound and nothing appears in the box. Ideas? TIA
Lee,
Try control-clicking and choose paste, or if you have a two button mouse, right click and paste.
Please let me know if this works.
- Michael
I find that the file from yahoo gets moved to trash faster than numbers can open it. I inserted a “click to continue” to allow for the quote.cvs to load.
Looking forward to any improvements you might make to this!
Timo,
Nice fix, I will make the change and include it in the download package.
Thanks!
- Michael
Michael — is there a way to extend the table beyond 15. I have played around with it by inserting rows or adding rows to the end, but these do not in turn appear in the hyperlink URL cell by filling.
The approach I have been using is also a hyperlink cell, but I just edit the URL when I add a new row for a new stock. Incidentally, I use commas in the URL instead of pluses. I guess it makes no difference.