Previous 1 2 3 Next 82 Replies Latest reply: Apr 29, 2015 5:08 AM by Orchardjoan
MikeCavan Level 1 Level 1 (0 points)

A few months back I was scouring this community for instructions on how to get stock information into iWork.

 

Folks like Yan Koenig and Bob H. posted some suggestions and I think I was this close to success when iWork 3.0 came out and Automator got blown out of the water. Now that it's back, I would be hugely grateful for The Complete Idiot's Guide to Stock Quotes in iWork 3.1.  Any takers?

 

Mike


Mac mini (Mid 2011), OS X Mavericks (10.9)
  • SGIII Level 5 Level 5 (5,700 points)

    Hi Mike,

     

    Not an idiots guide, but here is simple script that works well here. 

     

    Paste this into AppleScript Editor or a Run AppleScript action in Automator (the indentation should correct itself after hitting the "hammer"):

     

    -- select symbols in a Numbers column (no blanks!), run script, paste values in desired column

    tell application "Numbers"

              tell front document

                        tell active sheet

                                  tell (first table whose class of selection range is range)

                                            set symbols to my joinList(value of every cell of selection range, ",")

                                            set prices to my getYData(symbols)

      set the clipboard to prices

                                            display dialog "Ready to paste"

                                  end tell

                        end tell

              end tell

    end tell

     

    -- handler to get Yahoo! data

    to getYData(s)

              set base_url to "http://download.finance.yahoo.com/d/quotes.csv?s="

              set quote_properties to "&f=l1" -- use l1 for last trade,  p0 for previous close

              set r to do shell script "curl -s " & "'" & base_url & s & quote_properties & "'"

    end getYData

     

    --handler to convert AppleScript list to delimited string

    to joinList(a_list, list_delimiter)

              set orig_tid to text item delimiters of AppleScript

              set text item delimiters of AppleScript to list_delimiter

              set r to a_list as string

              set text item delimiters of AppleScript to orig_tid

              return r

    end joinList

     

     

    To use the script, select one or more cells in a column of symbols, run the script, click once on the first cell in the range where you want the prices, and command-v or option-shift-command-v to paste.

     

    After putting the script in an "Automator Service" my setup looks like this:

     

    Screen Shot 2014-01-25 at 12.44.25 PM.png

     

    I've selected multiple symbols in column A. After running the script, I put my cursor on B2 and paste.

     

    Screen Shot 2014-01-25 at 1.03.35 PM.png

     

    Caveats: This will only work in Numbers 3.1 and up as it uses properties Numbers 2 doesn't have. Also, it has no error handling. There can be no blanks in the cells you select for the symbols, or the script will behave badly!  It could be gussied up to include error handling and further automation (e.g., paste in a specified range) but there's something to be said for keeping things basic. The script already does the heavy lifting of getting the data from the web quite well.

     

    For further reading on Yahoo! quotes (you can get far more than prices) see:

     

    https://code.google.com/p/yahoo-finance-managed/wiki/csvQuotesDownload

    https://code.google.com/p/yahoo-finance-managed/wiki/enumQuoteProperty

     

    SG

  • SGIII Level 5 Level 5 (5,700 points)

    The previous script just brought one column of prices into Numbers. Here's a script to populate multiple columns, including selected fundamental data if you want:

     

     

    -- select symbols in a Numbers column (no blanks!), run script, paste where wanted

     

    set quote_properties to "l1r0e7e8s6"

    -- see https://code.google.com/p/yahoo-finance-managed/wiki/enumQuoteProperty

    --p0 prev close; l1 last trade; e7 est EPS curr yr; e8 est EPS next yr; s6 revenue

    --p6 p/b; r0 pe; y0 div yield, j4 EBITDA,j2 shares out; n0 name;s0 symbol,  etc

     

    tell application "Numbers" to tell front document to tell active sheet

              tell (first table whose class of selection range is range)

                        set symbols to my joinList(value of every cell of selection range, "+")

                        set paste_str to my csv2tsv(my getYData(symbols, quote_properties))

                        set the clipboard to paste_str

                        display notification "Ready to paste"

              end tell

    end tell

     

    to getYData(symb, q_prop) -- get Yahoo! data

              set base_url to "http://download.finance.yahoo.com/d/quotes.csv?"

              set symb_str to "s=" & symb

              set property_str to "&f=" & q_prop

              set script_str to "curl -s " & "'" & base_url & symb_str & property_str & "'"

              set r to do shell script "curl -s " & script_str

    end getYData

     

    to joinList(a_list, list_delimiter) --convert AS list to delimited string

              set orig_tid to text item delimiters of AppleScript

              set text item delimiters of AppleScript to list_delimiter

              set r to a_list as string

              set text item delimiters of AppleScript to orig_tid

              return r

    end joinList

     

    to csv2tsv(str) -- Numbers 3 wants tsv for pasting

              set orig_tid to text item delimiters of AppleScript

              set text item delimiters of AppleScript to ","

              set l to text items of str

              set text item delimiters of AppleScript to "          " --tab

              set str to l as text

              set text item delimiters of AppleScript to orig_tid

              return str

    end csv2tsv

     

     

    So in the following table select cells A2 through A10, run the script, select cell B2 and command-v (or option-shift-command-v) to paste. That's it.

     

    Screen Shot 2014-01-26 at 9.26.57 PM.png

    The table expands automatically if it's not wide enough. If you want to doublecheck that you've pasted the right data on the right rows, append s0 to quote_properties to fetch the symbol too and delete that column when it's not needed any more. If you want Yahoo to fetch the name it's best to add the n0 last. That way other columns won't be affected if a name contains a comma, causing an occasional spillover into an extra column.

     

    SG

  • SGIII Level 5 Level 5 (5,700 points)

    And if you want to include names, which often have commas within them, replace the csv3tsv handler with this one:

     

     

    to csv2tsv(str) -- Numbers 3 wants tab-delimited for pasting

      --adapted from http://blog.patrick-morgan.net/2005/09/perl-script-to-convert-csv-to-tab.html

              set perl_scpt to "

    while (<>) {

    my @fields = ();

    push(@fields, $+) while $_ =~ m{

    (\"[^\"]*(?:\"\"[^\"]*)*\"),?

    | ([^,\\n\\n]+),?

    | ,

    }gx;

    push(@fields, undef) if substr($_, -1,1) eq ',';

    print join(\"\\t\", @fields);

    print \"\\n

    \";

    }"

              set output to do shell script "echo " & quoted form of str & " | perl -e " & quoted form of perl_scpt

    end csv2tsv

     

     

     

    It sure looks ugly, but it can handle csv files that have commas within a field surrounded by quotes so you don't have to do cleanup after an import.

     

    SG

  • JonSamson Level 1 Level 1 (0 points)

    I have Yvan Koenig's original script which works - however has certain errors under the new Applescript. Perhaps someone with better knowledge could look at it and clean it up? Its slow and also returns a huge error string from the website when it cannot retrieve the quote. Can someone help??

     

    property theApp : "Numbers"

     

    --=====

     

     

    on run

              local texte, rowsCount, trueDoc, trueSheet, trueTable

     

              say "starting"

              set t3 to "http://" & "download.finance.yahoo.com/d/quotes.csv?s="

     

              tell application "Numbers"

      activate

     

                        set trueDoc to name of front document

                        set trueSheet to name of first sheet of document trueDoc

                        set trueTable to name of first table of sheet trueSheet of document trueDoc

                        tell application "Numbers" to tell document trueDoc to tell sheet trueSheet to tell table trueTable

                                  set rowsCount to row count

                        end tell

              end tell

     

              repeat with y from 2 to rowsCount

                        tell application "Numbers" to tell document trueDoc to tell sheet trueSheet to tell table trueTable

                                  tell row (y)

                                            set parms to get value of cell (2)

                                  end tell

                                  set t4 to "&f=l1"

                                  set request to t3 & parms & t4

                        end tell

                        set texte to download(request)

                        try

                                  tell application "Safari" to close window 1

                        end try

                        tell application "Numbers" to tell document trueDoc to tell sheet trueSheet to tell table trueTable

                                  tell row (y)

                                            set value of cell (3) to texte

                                  end tell

                        end tell

     

              end repeat

     

              repeat with y from 2 to rowsCount

                        tell application "Numbers" to tell document trueDoc to tell sheet trueSheet to tell table trueTable

                                  tell row (y)

                                            set parms to get value of cell (2)

                                  end tell

                                  set t4 to "&f=e"

                                  set request to t3 & parms & t4

                        end tell

                        set texte to download(request)

                        try

                                  tell application "Safari" to close window 1

                        end try

                        tell application "Numbers" to tell document trueDoc to tell sheet trueSheet to tell table trueTable

                                  tell row (y)

                                            set value of cell (4) to texte

                                  end tell

                        end tell

     

              end repeat

     

      beep 1

              say "get quotes done"

    end run

     

    on download(t)

              set t to do shell script "curl " & quoted form of t

              return t

    end download

    -----

  • JonSamson Level 1 Level 1 (0 points)

    Your script doesn't really split the numbers up into separate columns. It puts it into one string. Also, it requires selecting the group of symbols every time. The script I just posted, which is from Yvan does it automatically. Perhaps you are able to look at it and make it more efficient?  Thanks!

  • SGIII Level 5 Level 5 (5,700 points)

    Your script doesn't really split the numbers up into separate columns. It puts it into one string.

     

    It puts the data in one string. But it is a tab-separated string.  That means that after running the script all you have to do is click once in a cell of an existing table and command-v to paste. The data will then split automatically into separate columns.  Perhaps you could try again and let me know if it isn't working on your end.

     

    Also, it requires selecting the group of symbols every time. The script I just posted, which is from Yvan does it automatically.

     

    Yes, that is true. But I like the freedom of selecting the symbols for which I want data (wherever I have them in a document) and then pasting the results wherever I want. Yvan's script is less flexible, requiring the symbols to be in a specific place in the first table on the first sheet of a document. It then places the data in a specified place.

     

    Perhaps you are able to look at it and make it more efficient?  Thanks!

     

    I'll have a look at it. Meanwhile, it would be great if you could advise whether you are able to paste the data in multiple columns after running my script.

     

    SG

  • JonSamson Level 1 Level 1 (0 points)

    SG,

     

    It is not splitting automatically into separate columns. See screenshot:

     

    Screen Shot 2014-03-10 at 2.41.31 PM.png

    I think it would also be very helpful to have the option of all quotes being updated automatically rather than selecting and then pasting. However, I understand where you are coming from. I think, for most of us, there is a watchlist of some sort, and then the symbols are static and the results should be pasted automatically either next to it or in a user defined place?

     

    Regards

     

    Jonathan

     

    Message was edited by: JonSamson

  • SGIII Level 5 Level 5 (5,700 points)

    Hi Jonathan,

     

    Some things can get lost when copying pasting from the forum into AppleScript Editor.

     

    I just copied-pasted from my post above and the script didn't do the right thing, until I noticed that the line:

     

    set text item delimiters of AppleScript to "          " --tab

     

    does not paste into AppleScript Editor with a single tab between the paretheses.  Try deleting the white space between the parentheses and (with the cursor still between them) hit the tab key and save.  That should clear up the problem with the columns. Please post if it doesn't, and I'll post a script in a different way.

     

    On the question of where to place the values, where would you want them? Immediately to the right of the column with the symbols?

     

    And about "updating automatically" are you thinking you would like the updates to run at regular time intervals?

     

    SG

  • JonSamson Level 1 Level 1 (0 points)

    Thanks for the fix. That did work.

     

     

    On the question of where to place the values, where would you want them? Immediately to the right of the column with the symbols?

     

    Yes - immediately to the right. So if I want two values (last quote, EPS), then it should go to the 2 columns to the right of the symbols. If I want 4 values, then four columns to the right of the symbols.

     

     

    And about "updating automatically" are you thinking you would like the updates to run at regular time intervals?

     

    No - I should have been clearer. While that is a nifty feature, it is not necessary. I meant that the script should automatically recognize Column A" or "B" as the list of symbols, and therefore return the values requested automatically to the right. It should figure that out without having to

     

    1. Select group of symbols

    2. Run Script

    3. Select cell to paste

    4. Paste results.

     

    Instead , it should be

     

    1. Run script

     

    and that's it. The script will do the rest. Does that make sense? *(which is what the script i pasted does, more or less).

     

    Jonathan

  • SGIII Level 5 Level 5 (5,700 points)

    1. Select group of symbols

    2. Run Script

    3. Select cell to paste

    4. Paste results.

     

    Instead , it should be

     

    1. Run script

     

    and that's it. The script will do the rest.

     

    That would reduce a click, a drag, a click, a click, and a keystroke to one-click at the cost of flexibility, worthwhile if you're constantly refreshing, you have only one portfolio, and you have only one place you will ever want to place your stock information. I usually like more flexibility.  But... shouldn't be hard to do. Will post.

     

    SG

  • SGIII Level 5 Level 5 (5,700 points)

    Hi Jonathan,

     

    Does the following work for you better.  Edit the properties to fit your setup. As written it assumes one Header Row, no blanks in the list of symbols, and no Footer Row.

     

    The table will expand automatically to accommodate the columns and rows in your data. It won't shrink automatically, however. So if you reduce the number of data items you are retrieving you simply delete extra columns and run.

     

    Will be interested in hearing how it works on your end.

     

    SG

     

     

     

    --retrieves stock quotes and other data from Yahoo! and places in Numbers table

    --Usage: place symbols in column starting at row 2; no blanks; no footer row

    --Change properties to fit your setup

    --https://discussions.apple.com/message/25127300#25127300

     

    property quote_properties : "l1r0e7e8s6"

    -- see https://code.google.com/p/yahoo-finance-managed/wiki/enumQuoteProperty

    --p0 prev close; l1 last trade; e7 est EPS curr yr; e8 est EPS next yr; s6 revenue

    --p6 p/b; r0 pe; y0 div yield, j4 EBITDA,j2 shares out; n0 name;s0 symbol,  etc

     

    property target_sheet : "Sheet 9"

    property target_table : "Table 1"

    property symbol_column : 1

    property data_start_cell : "B2"

     

    tell application "Numbers" to tell front document to tell sheet target_sheet to tell table target_table

              tell column symbol_column to set symbols to my joinList(value of cells 2 through (count of cells), "+")

              set paste_str to my csv2tsv(my getYData(symbols, quote_properties))

           set the clipboard to paste_str

              try

                        set the selection range to cell data_start_cell

              on error

                        display alert "You designated a cell that doesn't exist. Check your table." buttons "OK"

                        return

              end try

              tell application "Numbers" to activate

              tell application "System Events" to keystroke "v" using {option down, shift down, command down}

              display notification "Stock data has been updated" with title "Numbers"

    end tell

     

    to getYData(symb, q_prop) -- get Yahoo! data

              try

                        set base_url to "http://download.finance.yahoo.com/d/quotes.csv?"

                        set symb_str to "s=" & symb

                        set property_str to "&f=" & q_prop

                        set script_str to "curl -s " & "'" & base_url & symb_str & property_str & "'"

                        set r to do shell script "curl -s " & script_str

                        if r's text items 1 thru 2 as text is equal to "<!" then error

                        return r

              on error

                        display alert "Had trouble getting data from Yahoo! server. Check symbols! No blanks."

              end try

    end getYData

     

    to joinList(a_list, list_delimiter) --convert AS list to delimited string

              set orig_tid to text item delimiters of AppleScript

              set text item delimiters of AppleScript to list_delimiter

              set r to a_list as string

              set text item delimiters of AppleScript to orig_tid

              return r

    end joinList

     

    to csv2tsv(str) -- Numbers 3 wants tsv for pasting

              set orig_tid to text item delimiters of AppleScript

              set text item delimiters of AppleScript to ","

              set l to text items of str

              set text item delimiters of AppleScript to tab

              set str to l as text

              set text item delimiters of AppleScript to orig_tid

              return str

    end csv2tsv

     

    --end of script

  • SGIII Level 5 Level 5 (5,700 points)

    Here's a script with better coding and error checking. It's faster because it doesn't travel to the Yahoo url twice (not sure how that mistake crept into the previous script).  It places the data in the columns immediately to the right of the columns with the symbols.

     

    SG

     

     

    (*

    Retrieves stock quotes and other data from Yahoo! and places them in a Numbers table.

    Place symbols in specified column starting at specified row number; no blanks; no footer row.

    Retrieved data will be placed in columns immediately to the right of the symbols column.

    By SGIII, 201403 v. 1.1

    *)

     

    --In the following line specify the types of data to retrieve in the order desired:

    property quoteProperties : "l1r0e7e8s6"

    (* 

      See https://code.google.com/p/yahoo-finance-managed/wiki/enumQuoteProperty

      Example: last trade price followed by pe followed by this year's estimated EPS ==> "l1roe7"

    *)

     

    --In the following line change the default values in black as needed to target the desired table:

    property t : {targetDoc:1, targetSheet:1, targetTable:1, symbolsColNum:1, symbolsStartRow:2}

    (* 

     

    Example: To populate a table named "Quotes" in sheet "Portfolio A" in document "Investments" with symbols starting on row 2 of column B==>

       {targetDoc:"Investments.numbers", targetSheet:"Portfolio A", targetTable:"Quotes",symbolsColNum:2, symbolsStartRow:2}

     

    *)

     

    tell application "Numbers" to tell document (t's targetDoc) to tell sheet (t's targetSheet) to tell table (t's targetTable)

              tell column (t's symbolsColNum) to set symbConcat to my joinList(value of cells (t's symbolsStartRow as number) thru (count cells), "+")

              try

                        set the clipboard to my commaToTab(my getYData(symbConcat, quoteProperties))

                        set pasteStr to the clipboard

              on error

                        return --halt script if error getting Yahoo data

              end try

              set the selection range to cell (t's symbolsStartRow) of column ((t's symbolsColNum) + 1)

              tell application "Numbers" to activate

              tell application "System Events" to keystroke "v" using {option down, shift down, command down}

              display notification "Stock data has been updated" with title "Numbers"

    end tell

     

    to getYData(qSymb, qProp) -- get Yahoo! data

              try

                        set baseURL to "http://download.finance.yahoo.com/d/quotes.csv?"

                        set {symbStr, propStr} to {"s=" & qSymb, "&f=" & qProp}

                        set yData to do shell script "curl -s " & quoted form of (baseURL & symbStr & propStr)

                        if yData's text 1 thru 2 is "<!" then error --intercept Yahoo error page

                        if yData's text 1 thru 3 is "0.0" then error --a little more error-checking

                        return yData

              on error

                        display alert "Trouble getting data from Yahoo! Check symbols. Ensure there are no blanks in the column and no footer rows, and that you have the right values in t's properties."

                        return

              end try

    end getYData

     

    to joinList(aList, separator) --convert AS list to delimited string

              set {oTid, AppleScript's text item delimiters} to {AppleScript's text item delimiters, separator}

              set lstStr to aList as string

              set text item delimiters of AppleScript to oTid

              return lstStr

    end joinList

     

    to commaToTab(str) -- Numbers 3 wants tab-separated for pasting

              try

                        set {oTid, AppleScript's text item delimiters} to {AppleScript's text item delimiters, ","}

                        set strLst to text items of str

                        set AppleScript's text item delimiters to tab

                        set tsvStr to strLst as text

                        set AppleScript's text item delimiters to oTid

                        return tsvStr

              on error

                        return

              end try

    end commaToTab

     

    --end of script

  • JonSamson Level 1 Level 1 (0 points)

    That is absolutely fantastic! And super fast.

     

    Great job and it worked beautifully, even when I modified as per your instructions the column and the data I wanted returned.

     

    Kudos to you. Thanks again.

  • SGIII Level 5 Level 5 (5,700 points)

    Thanks for the positive feedback! It's not quite fast enough to beat the HFT (high-frequency traders). But it is working better than I thought it would. Maybe it will help us make money, or at least lose less money.

     

    I noticed a superfluous line (originally for debugging) that doesn't do harm but probably should be removed to save a millisecond or so:

     

                                  set pasteStr to the clipboard

     

     

    Also if you want the quote updates to run automatically at set time intervals you can do this:

     

    add this on a line before the  tell application "Numbers" block:

     

      on idle

     

    and these two lines right after the end tell for that block, just before to getYData():

     


          return 120 --run every 120 seconds (change number as desired)

      end idle



    Then, in AppleScript Editor, option File > Save as > File Format: Application

    with the box checked before 'Stay open after run handler'

     

    Then, when you want the updates to start, just launch it as you would any other app. When you want it to stop, find it on the Dock, right-click, and Quit. If you opt for quite frequent automatic updates, you'll probably want to remove or comment out (by typing leading -- ) the display notification line.

     

    SG

Previous 1 2 3 Next