How to Get Stock Quotes into Apple iWork Numbers ‘08
August 19th, 2007 | by Michael |
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
6 Responses to “How to Get Stock Quotes into Apple iWork Numbers ‘08”
By Marcus on Aug 19, 2007 | Reply
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
By Michael on Aug 20, 2007 | Reply
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
By Lee on Aug 20, 2007 | Reply
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
By Michael on Aug 20, 2007 | Reply
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
By Timo on Aug 21, 2007 | Reply
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!
By Michael on Aug 21, 2007 | Reply
Timo,
Nice fix, I will make the change and include it in the download package.
Thanks!
- Michael