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 8 of 9 last Next
  • by Abraham.G,

    Abraham.G Abraham.G Jan 20, 2016 7:52 AM in response to MikeCavan
    Level 1 (48 points)
    iWork
    Jan 20, 2016 7:52 AM in response to MikeCavan

    What is this forum all about?

      As I understand is it a spreadsheet that's picked up data from the Interne, I'm correct?

     

    Since I'm trading stocks and options I want to use this spreadshee can som share with me the templat with Dropbox or iclout,

    not the script since I don't know how to handle it


    Thank you!


    can I use it with a iPa as well? Or iClou.com?

  • by SGIII,

    SGIII SGIII Jan 20, 2016 10:15 AM in response to tabbycat14
    Level 6 (10,622 points)
    Mac OS X
    Jan 20, 2016 10:15 AM in response to tabbycat14

    tabbycat14 wrote:

     

    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.

     

    Hi Tcat,

     

    I'm guessing it may have something to do with that troublesome tab between the quotation marks, which copies from the forum as spaces.

     

    Numbers expects a tab between values on the same row, so the spaces are confusing it.

     

    I've revised the script to try to avoid that problem.  Try this version:

     

    set quote_properties to "gh" --"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

    --g day's low, h day's high

     

    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

  • by SGIII,

    SGIII SGIII Jan 20, 2016 10:21 AM in response to Abraham.G
    Level 6 (10,622 points)
    Mac OS X
    Jan 20, 2016 10:21 AM in response to Abraham.G

    Abraham.G wrote:

     

    Since I'm trading stocks and options I want to use this spreadshee can som share with me the templat with Dropbox or iclout,

    not the script since I don't know how to handle it

     

    A script is needed to retrieve values from the internet and place them on the system clipboard for pasting into a Numbers table.  This won't work without a script and won't work on an iPad.  Sorry.

     

    BTW, running a script is really easy on the Mac.  Copy-paste it into Script Editor (in Applications > Utilities), select cells in Numbers containing symbols, and click the 'run' button.

     

    SG

  • by tabbycat14,

    tabbycat14 tabbycat14 Jan 20, 2016 1:51 PM in response to SGIII
    Level 1 (12 points)
    iWork
    Jan 20, 2016 1:51 PM in response to SGIII

    SG,

    That was the answer. It really simplifies things for me.

    Thank you,

    Tcat

  • by marcusfromdallas,

    marcusfromdallas marcusfromdallas Feb 2, 2016 9:57 AM in response to SGIII
    Level 1 (0 points)
    Feb 2, 2016 9:57 AM in response to SGIII

    any idea what I need to do in order to change the dates to pull from so i could possibly pull prices as of a defined date?

  • by SGIII,

    SGIII SGIII Feb 2, 2016 10:49 AM in response to marcusfromdallas
    Level 6 (10,622 points)
    Mac OS X
    Feb 2, 2016 10:49 AM in response to marcusfromdallas

    You can get historical prices for a range of dates with this one:

     

    SG

     

     

    (*

    Retrieves historical stock quotes for pasting into a Numbers table.

    Run, click once in a cell, and command-v to paste

    By SGIII, rev 201602

    *)

     

    --In the following line specify the symbol and period desired:

    property q : ¬

      {symbol:"GOOG", startMonthNum:1, startDay:15, startYear:2010, endMonthNum:9, endDay:30, endYear:2015}

    (* 

      Example: {symbol:"GOOG", startMonthNum:1, startDay:15, startYear:2010, endMonthNum:12, endDay:15, endYear:2010}

    *)

     

    try

      set qProperties to "s=" & q's symbol & ¬

      "&a=" & ((q's startMonthNum) - 1) & ¬

      "&b=" & q's startDay & ¬

      "&c=" & q's startYear & ¬

      "&d=" & ((q's endMonthNum) - 1) & ¬

      "&e=" & q's endDay & ¬

      "&f=" & q's endYear

      --> s=GOOG&a=0&b=15&c=2010&d=11&e=15&f=2010

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

      set the clipboard to my commaToTab(my getYData(qProperties))

      set pasteStr to the clipboard

    on error

      return --halt script if error getting Yahoo data

    end try

    display notification "Historical stock data for " & q's symbol & " is ready to paste" with title "Numbers"

     

    to getYData(qProp) -- get Yahoo! data

      try

      set baseURL to "http://ichart.yahoo.com/table.csv?"

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

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

      return yData

      on error

      display alert "Trouble getting data from Yahoo! Check the symbol. And make sure the end date is after the start date."

      return

      end try

    end getYData

     

    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

     

    --pasteStr--uncomment this line to see results in Result pane for debugging

  • by marcusfromdallas,

    marcusfromdallas marcusfromdallas Feb 4, 2016 11:14 AM in response to SGIII
    Level 1 (0 points)
    Feb 4, 2016 11:14 AM in response to SGIII

    Good to know but i'm looking to create a spreadsheet for a list of about 500 tickers (columns) on specific dates (rows) so that I can create year to year, month to month, n-period to n-period price changes and % changes.  So i'm looking to pull prices for a list of stock at any specific date in history.

  • by SGIII,

    SGIII SGIII Feb 4, 2016 11:20 AM in response to marcusfromdallas
    Level 6 (10,622 points)
    Mac OS X
    Feb 4, 2016 11:20 AM in response to marcusfromdallas

    Sorry, but I don't know a practical way to do that with the Yahoo API.

     

    SG

  • by Cici Harahap,

    Cici Harahap Cici Harahap Mar 9, 2016 9:44 AM in response to SGIII
    Level 1 (0 points)
    Mar 9, 2016 9:44 AM in response to SGIII

    Thank you so so very much!

  • by Cici Harahap,

    Cici Harahap Cici Harahap Mar 9, 2016 10:26 AM in response to SGIII
    Level 1 (0 points)
    Mar 9, 2016 10:26 AM in response to SGIII

    I ran the script above, but for some reason I cannot get the P/E numbers. Also, how do I go about if I want to add an information such as dividend yield? Please see below for screen shot.Screen Shot 2016-03-09 at 12.24.17 PM.png

  • by SGIII,

    SGIII SGIII Mar 9, 2016 11:27 AM in response to Cici Harahap
    Level 6 (10,622 points)
    Mac OS X
    Mar 9, 2016 11:27 AM in response to Cici Harahap

    What quote_properties do you have in your script?

     

    SG

  • by PurduefanGA,

    PurduefanGA PurduefanGA May 14, 2016 5:55 PM in response to SGIII
    Level 1 (8 points)
    Desktops
    May 14, 2016 5:55 PM in response to SGIII

    HI, me again. You helped me through all my scripting problems with yahoo finance, but now I want to get additional data and I can't find the list of codes for fields that I can get.  I found it months ago when I first used Apple script, but can't find it now. Can you help me?

    I Want to script is ROE, 5 yr avg yield and debt for a given stock. Thanks for all your help.

  • by Orchardjoan,

    Orchardjoan Orchardjoan May 14, 2016 6:18 PM in response to SGIII
    Level 1 (9 points)
    May 14, 2016 6:18 PM in response to SGIII

    CCan you tell me where the code list is for yahoo finance?  I need to download a few more fields and no longer have the list.  Thanks

  • by SGIII,

    SGIII SGIII May 14, 2016 7:06 PM in response to Orchardjoan
    Level 6 (10,622 points)
    Mac OS X
    May 14, 2016 7:06 PM in response to Orchardjoan

    I think the list you are looking for is in my first post in this thread.

     

    SG

  • by PurduefanGA,

    PurduefanGA PurduefanGA May 14, 2016 8:31 PM in response to SGIII
    Level 1 (8 points)
    Desktops
    May 14, 2016 8:31 PM in response to SGIII

    Thanks for your help. It is very kind and generous

    Of you. I tried to look at a link in the

    Post but got a 404 error.

    Can you send it again?

     

    Sent from my iPhone

     

    Danny

    ***********icloud.com

    Danny ***-***-****

    Joan ***-***-****

     

    <Personal Information and Email Edited by Host>

first Previous Page 8 of 9 last Next