MikeCavan

Q: Getting Stock Quotes Into iWork 3.1

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)

Posted on Jan 24, 2014 8:31 AM

Close

Q: Getting Stock Quotes Into iWork 3.1

  • All replies
  • Helpful answers

first Previous Page 7 of 9 last Next
  • by SGIII,

    SGIII SGIII Dec 30, 2015 6:43 AM in response to resullivan
    Level 6 (10,637 points)
    Mac OS X
    Dec 30, 2015 6:43 AM in response to resullivan

    Hi, it's hard to suggest anything without seeing what you are doing.  Have you tried running the script in Script Editor?

     

    SG

  • by resullivan,

    resullivan resullivan Dec 30, 2015 6:57 AM in response to SGIII
    Level 1 (0 points)
    Dec 30, 2015 6:57 AM in response to SGIII

    Here is the script I am using:

    property quoteProperties : "l1r0e7e8s6"

     

    property t : {targetDoc:"Investments.numbers", targetSheet:"Master", targetTable:"Master", symbolsColNum:1, 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))

      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

     

    How should I be saving this to use as a service within numbers?

    Thanks

  • by SGIII,

    SGIII SGIII Dec 30, 2015 7:32 AM in response to resullivan
    Level 6 (10,637 points)
    Mac OS X
    Dec 30, 2015 7:32 AM in response to resullivan

    Is it working for you in Script Editor?

     

    If so you can put the script in the script menu.

     

    If you want it as a Service, try here or many other places (search for: automator service run applescript).  Be sure to set to 'no input' at the top.

     

    SG

  • by resullivan,

    resullivan resullivan Dec 30, 2015 8:00 AM in response to SGIII
    Level 1 (0 points)
    Dec 30, 2015 8:00 AM in response to SGIII

    I am unable to get it to work in Script Editor either, it is displaying the error: Numbers got an error: Can’t get document "Investments.numbers". (-1728)

  • by SGIII,

    SGIII SGIII Dec 30, 2015 8:12 AM in response to resullivan
    Level 6 (10,637 points)
    Mac OS X
    Dec 30, 2015 8:12 AM in response to resullivan

    Do you have that document (with that exact name) open in Numbers?

     

    SG

  • by resullivan,

    resullivan resullivan Dec 30, 2015 8:19 AM in response to SGIII
    Level 1 (0 points)
    Dec 30, 2015 8:19 AM in response to SGIII

    Yeah I do. I can get this script to work after adding it as a service but I want it to paste the data into the cells immediately to the right rather than me doing it manually. Here is the script:

     

    set quote_properties to "l1c1p2e7e8s6"

     

    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 dialog "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

  • by SGIII,

    SGIII SGIII Dec 30, 2015 2:22 PM in response to resullivan
    Level 6 (10,637 points)
    Mac OS X
    Dec 30, 2015 2:22 PM in response to resullivan

    resullivan wrote:

     

    I want it to paste the data into the cells immediately to the right rather than me doing it manually. Here is the script:

     

    This one should do what you want.  It's been a while since I've looked at these but they should still work.

     

    SG

  • by resullivan,

    resullivan resullivan Dec 30, 2015 3:09 PM in response to SGIII
    Level 1 (0 points)
    Dec 30, 2015 3:09 PM in response to SGIII

    Using that I just get the error message: The action “Run AppleScript” encountered an error.

    My document is called "Investments" and the sheet "Master" and the table "Master" so I have used the line

    property t : {targetDoc:"Investments.numbers", targetSheet:"Master", targetTable:"Master", symbolsColNum:1, symbolsStartRow:2}

    but that doesn't seem to work. Is there something else I should be changing?

    Thanks

  • by SGIII,

    SGIII SGIII Dec 30, 2015 6:38 PM in response to resullivan
    Level 6 (10,637 points)
    Mac OS X
    Dec 30, 2015 6:38 PM in response to resullivan

    Sorry to hear you are having trouble. I just tried the script I linked to above (This one ) and it ran without error here in Script Editor. Since it's using "gui scripting" to paste the values into Numbers I'm not sure how well it will work within an Automator Service.  If you get it working in Script Editor you could try placing the script in the script menu (which will appear if 'Show Script menu in menu bar' is checked under Script Editor > Preferences) and running it from there. Note that to simplify the coding the script assumes you have no blanks in the column with the symbols, i.e. the first header row contains something ('Symbols' or something original like that) and there are no blank rows after the symbols either.

     

    SG

  • by tabbycat14,

    tabbycat14 tabbycat14 Dec 30, 2015 7:30 PM in response to resullivan
    Level 1 (12 points)
    iWork
    Dec 30, 2015 7:30 PM in response to resullivan

    Don't know if this will help or not, but I had a horrible time trying to make any scripts work. Finally this one works beautifully, just adjust the tab quotes to a tab space.

    Screen Shot 2015-12-30 at 10.23.48 PM.png

    Good luck

    Tcat

  • by tabbycat14,

    tabbycat14 tabbycat14 Jan 18, 2016 2:39 PM in response to SGIII
    Level 1 (12 points)
    iWork
    Jan 18, 2016 2:39 PM in response to SGIII

    SGIII,

    I use this great, getting quotes script, you created, everyday.

    Is there a way to add "earnings date release" into a column?

    Tcat

  • by SGIII,

    SGIII SGIII Jan 18, 2016 4:51 PM in response to tabbycat14
    Level 6 (10,637 points)
    Mac OS X
    Jan 18, 2016 4:51 PM in response to tabbycat14

    H Tcat,

     

    Sadly earnings release date does not seem to be among the properties available via the Yahoo Finance API.  It would be quite handy.  Just in case you are not aware the dates can be looked up one-by-one here.

     

    SG

  • by tabbycat14,

    tabbycat14 tabbycat14 Jan 19, 2016 2:48 PM in response to SGIII
    Level 1 (12 points)
    iWork
    Jan 19, 2016 2:48 PM in response to SGIII

    Hi SG,

    I've been using this script for a while now, and most of the time would have single properties in quotes, as in "e7" or "s6" etc. Though the script allows multiple quote_properties, when I try to use two or more, it only puts out one.

    I was trying to use "gh" over two columns, which are the day's high and low. I've tried spacing, adding 0's, other properties, all to no avail. By them selves they work perfect.

    Is this something you might know about, or any thoughts on this?

    Tcat

  • by SGIII,

    SGIII SGIII Jan 19, 2016 4:09 PM in response to tabbycat14
    Level 6 (10,637 points)
    Mac OS X
    Jan 19, 2016 4:09 PM in response to tabbycat14

    H Tcat,

     

    Which script is "this script"?

     

    And what exactly are you putting between the quotation marks in the line that sets the quote_properties.

     

    SG

  • by tabbycat14,

    tabbycat14 tabbycat14 Jan 19, 2016 7:08 PM in response to SGIII
    Level 1 (12 points)
    iWork
    Jan 19, 2016 7:08 PM in response to SGIII

    H SG,

    The script that is dated Jan 26, 2014, 6:46pm. Get stock quotes into iworks 3.

    Instead of set quote_properties to "l1r0e7e8s6", I'm trying to go set quote_properties to "gh". The "g" is day's low, and "h" day's high.

    They work individually, but for some reason, not together.

    Tcat

first Previous Page 7 of 9 last Next