1 2 Previous Next 18 Replies Latest reply: Apr 21, 2009 10:50 AM by KOENIG Yvan
Bob H. Level 4 Level 4 (2,095 points)
I hope this helps anyone trying to use iWork to keep track of the value of their stocks and mutual funds. Anyone with more than a few investments needs a means for getting quotes into an iWork spreadsheet.

To me, it is amazing that Apple makes it so simple to view quotes on the iPhone, but when it comes to iWork, a user has to be quite determined to get either a few or a large number of quotes into a spreadsheet.

Here is how to do it.

Make a copy of your portfolio spreadsheet or generate small sample portfolio spreadsheet to work with to be safe.

Paste the csv file link given below into a cell on your portfolio spreadsheet to give you a Hyperlink cell.



=HYPERLINK("http://download.finance.yahoo.com/d/quotes.csv?s=ABT,ABX,AGG,ARLP,BE GBX,BPT,BSV,BTE,D,DOM,DUK,E,ENB,ERF,VLCCF,VTI,XEL&f=sl1d1t1c1ohgv&e=.csv", "Yahoo")


The stock symbols to be gathered are in caps starting with ABT. Substitute your list separated by commas.

If you then click on this cell (labeled "Yahoo") it will open the browser (empty page) and a file containing the quotes will be downloaded to your "Downloads" folder. Open this downloaded file. It probably has the title "quotes.csv"

You can then copy the quotes column from the downloaded spreadsheet and do a "Paste Values" into your own portfolio spreadsheet containing other information such as number of shares. Keep in mind that things have to be alphabetical, and if you add another stock into your portfolio spreadsheet you will have to insert it into the csv hyperlink formula.

This basis for this approach was posted somewhere on the iWork Discussions a long time ago, but my searches didn't turn it up again. At any rate, I was and still am thankful for the post.

Message was edited by: Bob H.

3.06GHz iMac, Mac OS X (10.5), External firewire drive
  • 1. Re: Getting Stock Quotes Into iWork '09
    Level 8 Level 8 (41,760 points)
    Here I am.

    --[SCRIPT insertQuotes]

    (* Save this script as a … Script in the Folder Actions Scripts folder
    <startupVolume>:Library:Scripts:Folder Action Scripts:

    Attach this Folder Actions script to the folder receiving youd downloaded quotes.csv files.
    If you don't know the way to use Folder Actions Scripts, look at the chapter entitled
    "Running an automation when a folder is changed" in the Finder's Help.

    When the script is attached to the folder, it will be triggered each time a file is added to the folder.
    If some conditions are verified, the script deciphers the file's contents and insert the grabbed values in a Numbers '09 document.

    These conditions are:

    Numbers '09 must be running.
    The added file must be named "quotes.csv".
    A document whose named is defined in the property myDoc must be open in Numbers.
    The document must contain a sheet whose name is defined in the property mySheet.
    The document must contain in this sheet a table whose name is defined in the property myTable.
    The first cell of the destination area is defined by the properties columnNum1 and rowNum1.

    If the table is too small to store the download datas, it's enlarged accordingly.

    CAUTION, only one script may be active when attached to a folder.
    If you attach several of them, only the last attached one will be triggered.

    If you need several active scripts of this kind you must attach a single script
    which will dispatch files to other scripts given the received items.

    It's out of the range of this thread but the script is ready to do that.

    Yvan KOENIG (Vallauris FRANCE)
    29 janvier 2009
    *)
    --=====

    (* Edit these five properties to fit your needs *)
    property myDoc : "My quotes.numbers"
    property mySheet : "quotes"
    property myTable : "quotes"
    property columnNum1 : 5
    property rowNum1 : 8

    --=====

    property quotesFile : "quotes.csv" (* this name is the one use by the Hyperlink *)

    --=====

    (*
    -- for tests
    set this_folder to "Macintosh HD:Users:yvankoenig:Desktop:Téléchargés:"
    set theFile to this_folder & "quotes.csv"
    my itsAquoteFile(theFile, this_folder)
    *)

    on adding folder items to this_folder after receiving added_items
    set theFile to (item 1 of added_items) as text (*Grab the pathname of first added item *)
    tell application "System Events"
    set maybe to name of disk item theFile (* Grabs it's name *)
    set theProcesses to title of application processes
    end tell
    if (theProcesses contains "Numbers") and (maybe is quotesFile) then
    my itsAquoteFile(theFile, this_folder as text) (* Bingo, two conditions are already satisfied *)
    else
    (* here you may add other tasks *)
    end if
    end adding folder items to

    --=====

    on itsAquoteFile(leFichierCSV, leDossier)

    tell application "Numbers"
    activate
    set lesDocuments to name of every document
    end tell
    if lesDocuments contains myDoc then (* if myDoc is open it's really an automated download *)
    tell application "Numbers" to set lesFeuilles to name of every sheet of document myDoc
    if lesFeuilles contains mySheet then (* OK, the document contains the wanted sheet *)
    tell application "Numbers" to set lesTables to name of every table of sheet mySheet of document myDoc
    if lesTables contains mySheet then (* OK, the document contains the wanted table *)
    if character 2 of (0.5 as text) is "." then
    set deci to "."
    else
    set deci to ","
    end if

    set delim to ","
    set liste1 to paragraphs of (read file leFichierCSV from 1)
    set liste2 to {}
    repeat with p in liste1
    if (p as text) is not "" then copy my decoupe(p, delim) to end of liste2
    end repeat

    set rowsCount to count of liste2
    set columnsCount to count of item 1 of liste2
    tell application "Numbers"
    tell document myDoc to tell sheet mySheet to tell table myTable
    set nbRows to row count
    set nbColumns to column count
    if rowNum1 + rowsCount - 1 > nbRows then
    repeat (rowNum1 + rowsCount - 1 - nbRows) times
    add row below row nbRows
    end repeat
    end if
    if columnNum1 + columnsCount - 1 > nbColumns then
    repeat (columnNum1 + columnsCount - 1 - nbColumns) times
    add column after column nbColumns
    end repeat
    end if
    repeat with y from 1 to rowsCount
    tell row (rowNum1 - 1 + y)
    repeat with x from 1 to columnsCount
    try
    set maybe to item x of item y of liste2
    if maybe starts with quote then set maybe to text 2 thru -1 of maybe
    if maybe ends with quote then set maybe to text 1 thru -2 of maybe
    if maybe starts with "+" then set maybe to text 2 thru -1 of maybe
    if (deci is ",") then set maybe to my remplace(maybe, ".", deci)
    set value of cell (columnNum1 - 1 + x) to maybe
    end try
    end repeat -- x
    end tell -- row
    end repeat -- y
    end tell -- table of sheet of document
    end tell -- application
    end if -- lesTables…
    end if -- lesFeuilles
    end if -- lesDocuments contains …
    tell application "Finder" to delete file quotesFile of folder leDossier
    end itsAquoteFile

    --=====

    on decoupe(t, d)
    local l
    set AppleScript's text item delimiters to d
    set l to text items of t
    set AppleScript's text item delimiters to ""
    return l
    end decoupe

    --=====

    on remplace(t, d1, d2)
    local l
    set AppleScript's text item delimiters to d1
    set l to text items of t
    set AppleScript's text item delimiters to d2
    set t to l as text
    set AppleScript's text item delimiters to ""
    return t
    end remplace

    --=====
    --[/SCRIPT]


    Of course, I ask for 10% of the quotes incomes

    Yvan KOENIG (from FRANCE jeudi 29 janvier 2009 20:13:38)
  • 2. Re: Getting Stock Quotes Into iWork '09
    Bob H. Level 4 Level 4 (2,095 points)
    Yvan -- thanks all the way to France. I am not sure what this script does. Could you give me just a broad overview?
  • 3. Re: Getting Stock Quotes Into iWork '09
    Level 8 Level 8 (41,760 points)
    As I responded to your question, I thought that the explanations available at the beginning of the script are sufficient.

    When you double-click the cell containing your HYPERLINK formula, a file named "duotes.csv" is downloaded in your "Download" folder.

    If the script is attached to this folder, it is triggered by the arrival of the file.
    If the listed conditions are filled, it deciphers the file and stores the value in the predefined Numbers document.



    Yvan KOENIG (from FRANCE vendredi 30 janvier 2009 11:24:49)
  • 4. Re: Getting Stock Quotes Into iWork '09
    Bob H. Level 4 Level 4 (2,095 points)
    Yvan -- thanks for the response. I may be wanting to much as my end goal. I have a fairly complex spreadsheet with many columns and rows. One of the columns has stock symbols, and the next has the quotes. All I want to do is frequently replace the the values in just the column of quotes in this spreadsheet.

    I knew how to do the csv file thing, and therefore my approach of pasting the quotes from the retrieved "quotes.csv" file is pretty straightforward. I guess I don't understand how, using the script I could replace a specific column of quotes in the midst of my complicated spreadsheet.
  • 5. Re: Getting Stock Quotes Into iWork '09
    Level 8 Level 8 (41,760 points)
    As is the script may perfectly fit your needs.

    Go to my iDisk
    <http://idisk.me.com/koenigyvan-Public?view=web>

    and download:
    For_iWork:iWork '09:forNumbers09:insertquotes.zip

    You will get the script and a sample document.

    The script insert automatically the datas in the table "quotes :: quotes"
    and formulas using the VLOOKUP() function grabbed them in 'random' columns in "main :: quotes".

    The formulas are really simple.

    They are of this kind:

    =VLOOKUP($B,quotes::quotes :: $A$1:$I$17,3,0)

    Yvan KOENIG (from FRANCE vendredi 30 janvier 2009 18:43:20)
  • 6. Re: Getting Stock Quotes Into iWork '09
    Bob H. Level 4 Level 4 (2,095 points)
    Yvan -- thank you for your additional input. You are really an expert on this stuff. I think I need to learn a bit more about scripts, folder actions, etc before I can know what I am really doing. I will spend some time doing that before proceeding further. At that point I may have another question or two. Best wishes.
  • 7. Re: Getting Stock Quotes Into iWork '09
    Level 8 Level 8 (41,760 points)
    Thanks for the feedback.

    Yvan KOENIG (from FRANCE dimanche 1 février 2009 13:29:34)
  • 8. Re: Getting Stock Quotes Into iWork '09
    Bob H. Level 4 Level 4 (2,095 points)
  • 9. Re: Getting Stock Quotes Into iWork '09
    Level 8 Level 8 (41,760 points)
    I know the jason's script.

    We exchanged about it.

    The last time I saw it, it was a completely external script which must be called from the outside of Numbers.

    What I offered is one which may be triggered from the Numbers document itself.

    It's a matter of taste.

    Yvan KOENIG (from FRANCE mercredi 4 février 2009 18:56:10)
  • 10. Re: Getting Stock Quotes Into iWork '09
    Bob H. Level 4 Level 4 (2,095 points)
    Yvan -- I am glad there are guys like you and Jason around with capabilities in furthering the usefulness of programs like Numbers. I have another question. Would there be anyway of turning the approach either you or Jason is using into a formula that one could just enter in a cell and drag into other cells?
  • 11. Re: Getting Stock Quotes Into iWork '09
    Level 8 Level 8 (41,760 points)
    The response is simple.

    As far as I know, neither jason nor myself are masochists.
    If we wrote scripts to do that its because There is no provision to do the trick with a single formula.

    We are already seriously helped by the ability to use HYPERLINK()

    If the destination cells where contiguous you would be able to use a VLOOKUP() formula which would be simply installed with Fill down and Fill to the right but If I understand well it's not the way your sheet is designed.

    Yvan KOENIG (from FRANCE mercredi 4 février 2009 22:03:45)
  • 12. Re: Getting Stock Quotes Into iWork '09
    Bob H. Level 4 Level 4 (2,095 points)
    Sorry -- I didn't imply anything other than you folks are brilliant, and maybe hoped that you hadn't delved into the details of formula building and could somehow come up with a miracle there. I guess that's why Apple doesn't supply a formula.

    Best wishes...
  • 13. Re: Getting Stock Quotes Into iWork '09
    Level 8 Level 8 (41,760 points)
    Don't worry.

    Sometimes, when I am tired, I'm a bit nervous

    Yvan KOENIG (from FRANCE mercredi 4 février 2009 22:53:44)
  • 14. Re: Getting Stock Quotes Into iWork '09
    Bob H. Level 4 Level 4 (2,095 points)
    Keep up the good work!
1 2 Previous Next