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 3 of 9 last Next
  • by tabbycat14,

    tabbycat14 tabbycat14 Jun 16, 2014 8:00 PM in response to SGIII
    Level 1 (12 points)
    iWork
    Jun 16, 2014 8:00 PM in response to SGIII

    At the end of every day, I manually update, in one column only, about 60 stock prices (and volumes). Each price is linked to (MRQ) P/E, (TTM) P/E and about 20 others. So when I change a price in that one column it triggers change throughout the sheet.

     

    Instead of having the manually automatic update , which is your Jan 15 script (worked fine), I thought if I could get stock prices to change every 2 or 3 minutes in Numbers, that would be great, not imperative.

     

    When I run the Jan 15 script, I have 5 tables, 2 columns each, up to 20 rows, on one sheet. When the numbers appear on this one sheet, they also appropriately appear (linked) on the other sheets with the mass of data.

     

    This whole exercise is a nice convenience, and saves me time after 4PM.

     

    I feel like I have 2 left feet using automator. Applescript seems to be the easier.

  • by SGIII,

    SGIII SGIII Jun 16, 2014 9:57 PM in response to tabbycat14
    Level 6 (10,627 points)
    Mac OS X
    Jun 16, 2014 9:57 PM in response to tabbycat14

    So you have one column of symbols in one table and ideally you want one column of prices in that same table to update automatically if possible? 

     

    If so, are the symbols in column A and prices in column B?  Or is your setup different from that?

     

    SG

  • by tabbycat14,

    tabbycat14 tabbycat14 Jun 17, 2014 7:46 AM in response to SGIII
    Level 1 (12 points)
    iWork
    Jun 17, 2014 7:46 AM in response to SGIII

    Yes, to have the column of prices update automatically is ideal.

     

    Column A has the symbols, column B the prices.

     

    T14

  • by SGIII,

    SGIII SGIII Jun 17, 2014 10:29 AM in response to tabbycat14
    Level 6 (10,627 points)
    Mac OS X
    Jun 17, 2014 10:29 AM in response to tabbycat14

    The on idle code seems to be broken.  There should be way, if it won't update every few minutes as before, to have it update at a specified time every day.   I'll experiment more and post.

     

    SG

  • by SGIII,

    SGIII SGIII Jun 20, 2014 9:29 AM in response to tabbycat14
    Level 6 (10,627 points)
    Mac OS X
    Jun 20, 2014 9:29 AM in response to tabbycat14

    Yes, to have the column of prices update automatically is ideal.

     

    Column A has the symbols, column B the prices.

     

    I posted a script to do this a few days ago but the support communities software update seems to have swallowed it, and a later attempt. Frustrating!  Anyway, the script below should do what you want.

     

    Copy into AppleScript Editor, hold down the option key and Save As a "stay open"  Application. Then just run the application to start the automatic updates.

     

    This assumes you have the symbols starting on the second row of the first column of the first table of the first sheet of the front document. You can change that after property t :

     

    You can change the refresh interval (in seconds) by changing the number after return in  return 120.

     

    To turn off the notifications, delete or "comment out" (with a leading --) this line:

     

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

     

    To stop the refresh, just quit the .app.

     

    SG

     

     

    (*

    Retrieves stock quotes and other data from Yahoo! and places them in a Numbers table at specified time interval when saved as a "stay open" application.

    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, 201406 v. 1.2

    *)

     

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

    property quoteProperties : "l1" --other common choices: "r0e7e8s6"

    (*

      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}

     

    *)

    on idle

      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

      return 120

    end idle

     

    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

  • by SGIII,

    SGIII SGIII Jun 20, 2014 9:35 AM in response to SGIII
    Level 6 (10,627 points)
    Mac OS X
    Jun 20, 2014 9:35 AM in response to SGIII

    Another try to post the script:

     

    (*

    Retrieves stock quotes and other data from Yahoo! and places them in a Numbers table at specified time interval when saved as a "stay open" application.

    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, 201406 v. 1.2

    *)

     

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

    property quoteProperties : "l1" --other common choices: "r0e7e8s6"

    (*

      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}

     

    *)

    on idle

      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

      return 120

    end idle

     

    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

  • by SGIII,

    SGIII SGIII Jun 20, 2014 9:41 AM in response to SGIII
    Level 6 (10,627 points)
    Mac OS X
    Jun 20, 2014 9:41 AM in response to SGIII
    (*
    Retrieves stock quotes and other data from Yahoo! and places them in a Numbers table at specified time interval when saved as a "stay open" application.
    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, 201406 v. 1.2
    *)
    
    --In the following line specify the types of data to retrieve in the order desired:
    property quoteProperties : "l1" --other common choices: "r0e7e8s6"
    (* 
      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}
    
    *)
    on idle
      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
      return 120
    end idle
    
    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
    
  • by msantram,

    msantram msantram Aug 31, 2014 11:33 AM in response to SGIII
    Level 1 (0 points)
    Aug 31, 2014 11:33 AM in response to SGIII

    Hello SGIII

     

    Great Script, thank you.

     

    I followed the thread, and using the latest script you posted, created the Script as an Application, with "StartUp Screen" & "Stay Open After Run Handler" checked off. I even gave it an icon :-)

     

    In my Numbers document, I created a new sheet labeled "Quotes" with a Table named "QUOTES". I added three columns: NAME, SYMBOL, & PRICE. I initially placed the sheet first, but moved it to the 5th spot as I wanted to maintain the existing sheet order.

     

    The script runs, but does not successfully paste the stock values within the sheet. It will be in the clipboard, and in another application that I have open (including this text field!). I cannot seem to figure out why it's not saving, nor adding the additional columns to the sheet.

     

    Any ideas on what I am missing?

     

    Thx!

     

    Mo

  • by SGIII,

    SGIII SGIII Aug 31, 2014 1:45 PM in response to msantram
    Level 6 (10,627 points)
    Mac OS X
    Aug 31, 2014 1:45 PM in response to msantram

    From your description (the script runs, but the values are placed in the expected place in Numbers) it sounds as if you need to adjust the:

     

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

     

    to something like this (substituting your actual document name):

     

      property t : {targetDoc:"NameOfMyDocument.numbers", targetSheet:"Quotes", targetTable:"QUOTES", symbolsColNum:1, symbolsStartRow:2}


    The script assumes symbols are placed in column A. so you might change the order of your table column headers to SYMBOL, NAME, and PRICE.

     

    Post symptoms if this still doesn't work on your setup.

     

    SG


  • by msantram,

    msantram msantram Aug 31, 2014 6:03 PM in response to SGIII
    Level 1 (0 points)
    Aug 31, 2014 6:03 PM in response to SGIII

    Thx SG

     

    Sorry if I was not clear. I have the field values correct, but the issue is that import is not pasting the values into the spreadsheet. I am running OS X Yosemite, so perhaps that may explain why the script may not be pasting the values, but I am not sure.

     

    property t : {targetDoc:"Quotes.numbers", targetSheet:"Quotes", targetTable:"QUOTES", symbolsColNum:2, symbolsStartRow:2}

     

    Thx

     

    M

  • by SGIII,

    SGIII SGIII Aug 31, 2014 9:13 PM in response to msantram
    Level 6 (10,627 points)
    Mac OS X
    Aug 31, 2014 9:13 PM in response to msantram

    Have you double-checked to make sure your accessibility controls are set to allow gui scripting?

     

    In Mavericks that is done via System Preferences > Privacy & Security.  It's easier to do than the explanation makes it seem.  Presumably Yosemite is similar.

     

    SG

  • by JMANTN,

    JMANTN JMANTN Nov 12, 2014 9:19 PM in response to SGIII
    Level 1 (33 points)
    Photos for Mac
    Nov 12, 2014 9:19 PM in response to SGIII

    I too am having the same issue as msantram in that the application will run however I have to manually hit command V to paste the data. 

     

    I have enabled accessibility controls for not just the Script Editor but for the application itself (made from your code).  I actually didn't make any additional changes to your last posted script and it runs just fine except I have to manually paste the information in.  My table is empty except for four stocks that start in row 2 of the first column and I don't have column names as of yet if this helps and as I stated everything works except for the pasting command.

     

    For what it's worth I'm running 10.10.1 Yosemite and have the most up to date version of Numbers installed. 

  • by JMANTN,

    JMANTN JMANTN Nov 12, 2014 10:06 PM in response to JMANTN
    Level 1 (33 points)
    Photos for Mac
    Nov 12, 2014 10:06 PM in response to JMANTN

    Sorry to double post but I couldn't edit my post above to add the following:

     

    Also when calling what aspects of the stock to pull from: https://code.google.com/p/yahoo-finance-managed/wiki/enumQuoteProperty is there a way to break up the output?

     

    I'm trying to recreate a XLS I had in EXCEL 2013 as I no longer use Office and I had as an example the following layout:

     

    Symbol      Stock_Name      #_of_Shares     Last Price .....etc........

     

     

    In that example I'd be pulling Symbol, Stock_Name, and Last Price from Yahoo while # of shares is something I had keyed in.  I'd have several columns where I had formulas built in that didn't pull data from yahoo.  With Excel I could easily have each column specifically pull data from yahoo which in some ways wasn't the best but I didn't require mine to update all that often.

     

    Just wondering if there was an easy way to have it skip specific columns?  If not I could still most likely manipulate the script to fit my needs or at the very least have multiple scripts going simply based off of your excellent work that would populate the columns needed.

     

     

    And lastly what if I wanted to run your last script as a script instead of an application? If that would only require changing a few lines of code could you share that with me?  Sorry learning as I go and when it comes to scripting numbers this is my first time.

  • by SGIII,

    SGIII SGIII Nov 13, 2014 8:42 AM in response to JMANTN
    Level 6 (10,627 points)
    Mac OS X
    Nov 13, 2014 8:42 AM in response to JMANTN

    I have to manually hit command V to paste the data.

     

    It's possible that your system needs time to catch up with AppleScript before AppleScript attempts to paste the values.

     

    Right after:

     

      tell application "Numbers" to activate

     

    try inserting the line:

     

      delay 0.25

     

    SG

  • by SGIII,

    SGIII SGIII Nov 13, 2014 9:16 AM in response to JMANTN
    Level 6 (10,627 points)
    Mac OS X
    Nov 13, 2014 9:16 AM in response to JMANTN

    an easy way to have it skip specific columns?

     

    There are, of course, ways to do that. However, for simplicity and efficiency, you might think in terms of a downloaded "data" table and then separate table(s) that pull(s) values from there and applies formulas, etc.

     

    To retrieve values in columns similar to your example layout you would use something like

     

    property quoteProperties : "s0n0j2l1"

     

    That's assuming by #_of_shares shares you mean shares outstanding, not shares owned. I've found it's often better to put n0 at the end instead of in the middle because the name in Yahoo sometimes has embedded columns that causes it to paste across multiple columns and thus disturb the alignment of the imported values. There are automated ways to deal with embedded commas, but I've found just putting the n0 last is a workable solution, something like:

     

    property quoteProperties : "s0j2l1n0"

     

    If you don't need the automated updating that the "application" provides, then remove:

     

    on idle

    return 120

    end idle


    Then you should be able to save it and run it as a regular script.

     

    If you want to target just one column at a time, then you can put one term in the  quoteProperties and change the 1 in :

     

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

     

    to the number of the column you are targeting.

     

    Or, probably better, you could put the target column number in a property :

     

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

     

    And use:

     

      set the selection range to cell (t's symbolsStartRow) of column (t's targetColumn)

     

    SG

     


first Previous Page 3 of 9 last Next